Zomato SQL Data Analysis

Project Description:

The Zomato SQL Data Analysis project aims to derive meaningful insights from a set of tables related to user information, sales transactions, and product details. The project database, named "zomato," consists of tables such as "users," "sales," "product," and "goldusers_signup," capturing essential data for analysis.

Tables in the Database:

• users: Contains information about user IDs and their signup dates.
• sales: Records sales transactions, including user IDs, transaction dates, and product IDs.
• product: Provides details about different products, such as product IDs, names, and prices.
• goldusers_signup: Tracks users who have signed up for the gold membership and their signup dates.

Key Queries and Analysis:

1. Total Amount Spent by Each Customer:

   Calculates the total amount spent by each customer on Zomato.

2. Number of Days Each Customer Has Visited Zomato:

   Determines the number of days each customer has visited Zomato, considering sales, gold membership signup, and user signup dates.

3. First Product Purchased by Each Customer:

   Identifies the first product purchased by each customer.

4. Most Purchased Item and Purchase Frequency:

   Determines the most purchased item on the menu and the number of times it was bought by all customers.

5. Most Popular Item for Each Customer:

   Finds the most popular item for each customer based on purchase frequency.

6. First Item Purchased as a Gold Member:

   Identifies the item purchased first by a customer upon becoming a gold member.

7. Item Purchased Just Before Becoming a Gold Member:

   Determines the item purchased by a customer just before becoming a gold member.

8. Total Orders and Amount Spent Before Gold Membership:

   Calculates the total orders and amount spent by each customer before becoming a gold member.

9. Total Points Earned by Each Customer and Most Rewarding Product:
   • Calculates total points earned by each customer based on a points system.
   • Identifies the product that has generated the most points.
10. Points Earned in the First Year of Gold Membership:

    Calculates points earned by customers in the first year after joining the gold membership.

11. Ranking of All Transactions for Every Customer:

    Ranks all transactions for each customer based on the transaction date.

12. Ranking Transactions for Gold Members and NA for Non-Gold Members:

    Ranks transactions for gold members and assigns "NA" for non-gold members.

Conclusion:

This Zomato SQL Data Analysis project offers a comprehensive exploration of user behavior, purchase patterns, and loyalty programs, providing valuable insights for strategic decision-making and improving user experience on the Zomato platform.

• GitHub Link
• Back to Portfolio