Case Study Interview

Revenue = Visitors × Conversion Rate × Average Order Value

Check changes in each component compared to the previous month

import pandas as pd
import numpy as np
 
DATA_PATH = '/data/'
orders = pd.read_csv(DATA_PATH + 'src_orders.csv', parse_dates=['created_at'])
order_items = pd.read_csv(DATA_PATH + 'src_order_items.csv')
users = pd.read_csv(DATA_PATH + 'src_users.csv')
 
# 1. Daily revenue trend
daily_revenue = order_items.merge(
    orders[['order_id', 'created_at', 'status']], on='order_id'
).query("status == 'Complete'")
 
daily_revenue['date'] = daily_revenue['created_at'].dt.date
daily_summary = daily_revenue.groupby('date').agg(
    revenue=('sale_price', 'sum'),
    orders=('order_id', 'nunique'),
    aov=('sale_price', lambda x: x.sum() / x.count())
).reset_index()
 
print("=== Daily Revenue Trend ===")
print(daily_summary.tail(60))
 
# 2. Channel breakdown (assuming: by traffic_source)
df = order_items.merge(orders, on='order_id').merge(users, on='user_id')
channel_comparison = df.groupby([
    df['created_at'].dt.to_period('M'),
    'traffic_source'
])['sale_price'].sum().unstack()
 
print("\n=== Monthly Revenue by Channel ===")
print(channel_comparison)
 
# 3. Category breakdown
df_products = df.merge(
    pd.read_csv(DATA_PATH + 'src_products.csv')[['product_id', 'category']],
    on='product_id'
)
category_comparison = df_products.groupby([
    df_products['created_at'].dt.to_period('M'),
    'category'
])['sale_price'].sum().unstack()
 
print("\n=== Monthly Revenue by Category ===")
print(category_comparison)
 
# 4. New vs Existing customers
df['is_new'] = df.groupby('user_id')['created_at'].transform('min') == df['created_at']
customer_type = df.groupby([
    df['created_at'].dt.to_period('M'),
    'is_new'
])['sale_price'].sum().unstack()
 
print("\n=== Revenue by New/Existing Customers ===")
print(customer_type)