Zomato Data Analytics: How Food-Tech Uses Data to Drive Growth

# User order history
{
  "user_id": "U12345",
  "order_history": [
    {"restaurant_id": "R001", "cuisine": "North Indian", "order_time": "13:15", "rating": 4},
    {"restaurant_id": "R045", "cuisine": "Chinese", "order_time": "20:30", "rating": 5},
    {"restaurant_id": "R122", "cuisine": "Italian", "order_time": "21:00", "rating": 4}
  ],
  "search_queries": ["pizza near me", "biryani", "healthy salad"],
  "filters_used": ["veg_only", "rating_4_plus", "delivery_time_30min"]
}

# Restaurant metadata
{
  "restaurant_id": "R001",
  "name": "Punjab Grill",
  "cuisine": ["North Indian", "Mughlai"],
  "avg_rating": 4.2,
  "avg_delivery_time": 35,
  "price_for_two": 800,
  "location_lat_lon": (12.9716, 77.5946),
  "popular_dishes": ["Butter Chicken", "Dal Makhani", "Naan"]
}

-- Collaborative filtering: Users who ordered from Restaurant A also ordered from Restaurant B
WITH user_restaurant_pairs AS (
  SELECT
    o1.user_id,
    o1.restaurant_id AS restaurant_a,
    o2.restaurant_id AS restaurant_b,
    o1.order_date
  FROM orders o1
  JOIN orders o2
    ON o1.user_id = o2.user_id
    AND o1.restaurant_id != o2.restaurant_id
  WHERE o1.order_date >= CURRENT_DATE - INTERVAL '90 days'
),

restaurant_similarity AS (
  SELECT
    restaurant_a,
    restaurant_b,
    COUNT(DISTINCT user_id) AS users_ordered_both,
    -- Confidence: If user orders from A, probability they also order from B
    COUNT(DISTINCT user_id) * 100.0 /
      (SELECT COUNT(DISTINCT user_id) FROM orders WHERE restaurant_id = restaurant_a)
      AS confidence_pct
  FROM user_restaurant_pairs
  GROUP BY restaurant_a, restaurant_b
  HAVING COUNT(DISTINCT user_id) >= 20  -- Minimum support
)

SELECT
  ra.name AS restaurant_a_name,
  rb.name AS restaurant_b_name,
  rs.confidence_pct,
  rb.avg_rating,
  rb.avg_delivery_time,
  rb.price_for_two
FROM restaurant_similarity rs
JOIN restaurants ra ON rs.restaurant_a = ra.restaurant_id
JOIN restaurants rb ON rs.restaurant_b = rb.restaurant_id
WHERE rs.restaurant_a = 'R001'  -- Punjab Grill (user's favorite)
ORDER BY rs.confidence_pct DESC
LIMIT 10;

import pandas as pd
import numpy as np
from datetime import datetime

def rank_restaurants(user_id, user_context, restaurant_list):
    """
    Rank restaurants based on collaborative filtering + contextual factors

    Args:
        user_id: User identifier
        user_context: {'hour': 13, 'day_of_week': 'Friday', 'weather': 'rainy'}
        restaurant_list: List of candidate restaurants (from collaborative filtering)

    Returns:
        Ranked restaurant list with scores
    """

    results = []

    for restaurant in restaurant_list:
        score = 0

        # Base score: Collaborative filtering confidence (0-100)
        score += restaurant['cf_confidence']

        # Contextual adjustments

        # Time of day preference (lunch: quick delivery, dinner: quality)
        if 12 <= user_context['hour'] <= 14:  # Lunch
            if restaurant['avg_delivery_time'] <= 30:
                score += 20  # Prioritize fast delivery
        elif 19 <= user_context['hour'] <= 22:  # Dinner
            if restaurant['avg_rating'] >= 4.0:
                score += 20  # Prioritize high-rated

        # Weather context (rainy day: comfort food)
        if user_context['weather'] == 'rainy':
            if restaurant['cuisine'] in ['North Indian', 'Chinese', 'Italian']:
                score += 15  # Comfort food cuisines

        # Price sensitivity (Friday/weekend: less price-sensitive)
        if user_context['day_of_week'] in ['Friday', 'Saturday', 'Sunday']:
            score += 5  # All restaurants benefit (users order more expensive on weekends)
        else:
            if restaurant['price_for_two'] <= 400:
                score += 10  # Prioritize budget options on weekdays

        # Distance penalty (farther = longer delivery = lower score)
        distance_km = restaurant['distance_km']
        if distance_km <= 2:
            score += 10
        elif distance_km <= 4:
            score += 5
        else:
            score -= 5  # Penalize distant restaurants

        results.append({
            'restaurant_id': restaurant['restaurant_id'],
            'name': restaurant['name'],
            'final_score': score,
            'avg_rating': restaurant['avg_rating'],
            'delivery_time': restaurant['avg_delivery_time'],
            'price_for_two': restaurant['price_for_two']
        })

    # Sort by score descending
    results_sorted = sorted(results, key=lambda x: x['final_score'], reverse=True)

    return results_sorted

# Example usage
user_context = {
    'hour': 13,
    'day_of_week': 'Wednesday',
    'weather': 'clear'
}

candidate_restaurants = [
    {'restaurant_id': 'R001', 'name': 'Punjab Grill', 'cf_confidence': 85, 'cuisine': 'North Indian',
     'avg_rating': 4.2, 'avg_delivery_time': 35, 'price_for_two': 800, 'distance_km': 3.2},
    {'restaurant_id': 'R002', 'name': 'Chinese Wok', 'cf_confidence': 75, 'cuisine': 'Chinese',
     'avg_rating': 3.9, 'avg_delivery_time': 25, 'price_for_two': 350, 'distance_km': 1.8},
    {'restaurant_id': 'R003', 'name': 'Wow! Momo', 'cf_confidence': 70, 'cuisine': 'Tibetan',
     'avg_rating': 4.0, 'avg_delivery_time': 20, 'price_for_two': 300, 'distance_km': 2.5}
]

ranked = rank_restaurants('U12345', user_context, candidate_restaurants)

print("Ranked Restaurants for Lunch (Wednesday):")
for i, r in enumerate(ranked, 1):
    print(f"{i}. {r['name']} (Score: {r['final_score']}, Rating: {r['avg_rating']}, "
          f"Delivery: {r['delivery_time']}min, Price: ₹{r['price_for_two']})")

# Output:
# 1. Wow! Momo (Score: 105, Rating: 4.0, Delivery: 20min, Price: ₹300)
# 2. Chinese Wok (Score: 105, Rating: 3.9, Delivery: 25min, Price: ₹350)
# 3. Punjab Grill (Score: 95, Rating: 4.2, Delivery: 35min, Price: ₹800)
#
# Reason: Lunchtime prioritizes fast delivery + budget-friendly (Wow! Momo, Chinese Wok win)
# Punjab Grill ranked lower despite higher CF confidence because slower delivery + expensive

-- Cohort analysis: Users who ordered in Jan 2026, retention over next 3 months
WITH jan_cohort AS (
  SELECT DISTINCT user_id
  FROM orders
  WHERE order_date BETWEEN '2026-01-01' AND '2026-01-31'
),

monthly_activity AS (
  SELECT
    jc.user_id,
    DATE_TRUNC('month', o.order_date) AS order_month,
    COUNT(o.order_id) AS orders_count
  FROM jan_cohort jc
  LEFT JOIN orders o ON jc.user_id = o.user_id
    AND o.order_date >= '2026-01-01'
    AND o.order_date < '2026-05-01'
  GROUP BY jc.user_id, DATE_TRUNC('month', o.order_date)
)

SELECT
  order_month,
  COUNT(DISTINCT user_id) AS active_users,
  SUM(orders_count) AS total_orders,
  SUM(orders_count) * 1.0 / COUNT(DISTINCT user_id) AS avg_orders_per_user
FROM monthly_activity
GROUP BY order_month
ORDER BY order_month;

-- Churn prediction: Users likely to churn (ML feature engineering)
SELECT
  u.user_id,
  u.email,
  u.signup_date,
  CURRENT_DATE - MAX(o.order_date) AS days_since_last_order,
  COUNT(o.order_id) AS total_orders,
  AVG(o.order_value) AS avg_order_value,
  AVG(o.delivery_rating) AS avg_delivery_rating,
  -- Churn risk flag
  CASE
    WHEN CURRENT_DATE - MAX(o.order_date) > 30 AND COUNT(o.order_id) >= 3 THEN 'HIGH_RISK'
    WHEN CURRENT_DATE - MAX(o.order_date) > 45 THEN 'MEDIUM_RISK'
    ELSE 'ACTIVE'
  END AS churn_risk_segment
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
GROUP BY u.user_id, u.email, u.signup_date
HAVING COUNT(o.order_id) > 0  -- Exclude never-ordered users
ORDER BY days_since_last_order DESC;

# Win-back campaign: Personalized discount based on user value
def generate_winback_offer(user_segment, user_ltv):
    """
    Generate personalized discount offer to win back churned users

    Args:
        user_segment: 'HIGH_RISK', 'MEDIUM_RISK', 'ACTIVE'
        user_ltv: Lifetime value (total spent) ₹

    Returns:
        Discount offer dictionary
    """

    offers = {
        'HIGH_RISK': {
            'ltv_0_1000': {'discount': 100, 'min_order': 199, 'message': 'We miss you! ₹100 OFF on your next order'},
            'ltv_1000_5000': {'discount': 150, 'min_order': 299, 'message': 'Come back! ₹150 OFF on ₹299+'},
            'ltv_5000_plus': {'discount': 250, 'min_order': 499, 'message': 'Special offer! ₹250 OFF on ₹499+'}
        },
        'MEDIUM_RISK': {
            'ltv_0_1000': {'discount': 50, 'min_order': 199, 'message': '₹50 OFF your next order'},
            'ltv_1000_5000': {'discount': 75, 'min_order': 249, 'message': '₹75 OFF on ₹249+'},
            'ltv_5000_plus': {'discount': 100, 'min_order': 299, 'message': '₹100 OFF on ₹299+'}
        }
    }

    # Determine LTV bucket
    if user_ltv < 1000:
        ltv_bucket = 'ltv_0_1000'
    elif user_ltv < 5000:
        ltv_bucket = 'ltv_1000_5000'
    else:
        ltv_bucket = 'ltv_5000_plus'

    # Return personalized offer
    return offers[user_segment][ltv_bucket]

# Example
offer = generate_winback_offer('HIGH_RISK', 3500)
print(offer)
# Output: {'discount': 150, 'min_order': 299, 'message': 'Come back! ₹150 OFF on ₹299+'}

# Scenario A: Focus on acquisition (no retention fix)
new_users_per_month = 100000
month_1_retention = 0.25  # 75% churn
month_3_retention = 0.15
cac = 150  # Cost to acquire one user (ads)
ltv = 500  # Lifetime value per user

total_cost = new_users_per_month * cac  # ₹1.5 crore
total_revenue = new_users_per_month * month_3_retention * ltv  # ₹75 lakh
roi = (total_revenue - total_cost) / total_cost * 100  # -50% (losing money!)

# Scenario B: Fix retention first, then scale acquisition
new_users_per_month = 100000
month_1_retention = 0.40  # Improved from 25% → 40% (win-back campaigns)
month_3_retention = 0.25  # Improved from 15% → 25%
cac = 150
ltv = 800  # Higher LTV (retained users order more)

total_cost = new_users_per_month * cac  # ₹1.5 crore
total_revenue = new_users_per_month * month_3_retention * ltv  # ₹2 crore
roi = (total_revenue - total_cost) / total_cost * 100  # +33% (profitable!)