Free Data Analyst Course: Hands-on Curriculum

-- 1. SQL: Customer Lifetime Value (LTV) & Cohort Retention Analysis
WITH monthly_cohorts AS (
    SELECT 
        customer_id,
        DATE_TRUNC('month', MIN(order_date)) AS cohort_month
    FROM sales.orders
    GROUP BY customer_id
),
customer_orders AS (
    SELECT 
        o.order_id,
        o.customer_id,
        o.amount_inr,
        DATE_TRUNC('month', o.order_date) AS order_month,
        c.cohort_month
    FROM sales.orders o
    JOIN monthly_cohorts c ON o.customer_id = c.customer_id
    WHERE o.order_status = 'completed'
)
SELECT 
    cohort_month,
    order_month,
    COUNT(DISTINCT customer_id) AS active_customers,
    SUM(amount_inr) AS net_revenue_inr,
    ROUND(SUM(amount_inr) / COUNT(DISTINCT customer_id), 2) AS arpu_inr
FROM customer_orders
GROUP BY 1, 2
ORDER BY 1, 2;

# 2. Python & Pandas: Automated Cleansing & RFM Customer Segmentation
import pandas as pd
import numpy as np

# Ingest and enforce strict temporal datatypes
df = pd.read_csv('ecommerce_orders_2026.csv', parse_dates=['order_date'])

# Clean null values and isolate valid transactional records
df['customer_id'] = df['customer_id'].fillna('GUEST_USER')
df = df[(df['order_status'] == 'delivered') & (df['amount_inr'] > 0)].copy()

# Calculate Recency, Frequency, and Monetary metrics
snapshot_date = df['order_date'].max() + pd.Timedelta(days=1)
rfm = df.groupby('customer_id').agg(
    recency=('order_date', lambda x: (snapshot_date - x.max()).days),
    frequency=('order_id', 'nunique'),
    monetary=('amount_inr', 'sum')
).round(2)

# Vectorized quantile scoring for customer segmentation
rfm['r_tier'] = pd.qcut(rfm['recency'], q=4, labels=[4, 3, 2, 1])
rfm['m_tier'] = pd.qcut(rfm['monetary'], q=4, labels=[1, 2, 3, 4])
print("High-Value Champions Segment:")
print(rfm[(rfm['r_tier'] == 4) & (rfm['m_tier'] == 4)].head(5))