Build This Project · Portfolio Guide

E-Commerce Revenue Analytics — Complete Project Guide

SECTION 01Project overview

Here's what you'll build in this project:

• Business problem: An e-commerce company wants to understand revenue trends, customer retention, and product performance.
• Your solution: A revenue analytics dashboard that tracks key metrics — revenue, AOV, LTV, retention — and includes cohort analysis.
• Tools: SQL, Python (pandas), Tableau/Power BI, or Excel.
• Outcome: A portfolio-ready project that demonstrates e-commerce analytics and business thinking.

SECTION 02Data source

Here are the best data sources for this project:

SECTION 03Key metrics and KPIs

Here are the key metrics your revenue analytics dashboard should track:

SECTION 04Cohort analysis

Cohort analysis is a powerful way to understand customer retention. Here's how to do it:

-- SQL cohort analysis
WITH customer_cohorts AS (
    SELECT 
        customer_id,
        MIN(order_date) AS first_order_date,
        DATE_TRUNC('month', MIN(order_date)) AS cohort_month
    FROM orders
    GROUP BY customer_id
),
cohort_retention AS (
    SELECT 
        c.cohort_month,
        DATE_TRUNC('month', o.order_date) AS order_month,
        COUNT(DISTINCT o.customer_id) AS active_customers
    FROM orders o
    JOIN customer_cohorts c ON o.customer_id = c.customer_id
    GROUP BY c.cohort_month, DATE_TRUNC('month', o.order_date)
)
SELECT *
FROM cohort_retention
ORDER BY cohort_month, order_month;

# Python cohort analysis
import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns

# Assuming df has order_date and customer_id
df['order_date'] = pd.to_datetime(df['order_date'])
df['cohort'] = df.groupby('customer_id')['order_date'].transform('min')

df['cohort_month'] = df['cohort'].dt.to_period('M')
df['order_month'] = df['order_date'].dt.to_period('M')

cohort_data = df.groupby(['cohort_month', 'order_month'])['customer_id'].nunique().reset_index()
cohort_data['cohort_index'] = (cohort_data['order_month'] - cohort_data['cohort_month']).dt.months

cohort_pivot = cohort_data.pivot(index='cohort_month', columns='cohort_index', values='customer_id')

plt.figure(figsize=(12, 8))
sns.heatmap(cohort_pivot, annot=True, fmt='.0f', cmap='Blues')
plt.title('Customer Retention Cohort Analysis')
plt.xlabel('Months Since First Purchase')
plt.ylabel('Cohort Month')
plt.show()

SECTION 05Dashboard design

Here's how to design your revenue analytics dashboard:

• Top section: KPI cards — Total Revenue, AOV, LTV, Retention Rate.
• Middle section: Revenue trends over time, revenue by product category.
• Bottom section: Cohort retention heatmap, customer acquisition trends.
• Filters: Date range, product category, customer segment.

SECTION 06Portfolio presentation

Here's how to present this project to employers:

• GitHub: Upload your code, data preparation scripts, and dashboard file.
• README: Write a clear README with project overview, metrics, and key insights.
• Executive summary: Include a 1-page summary for business stakeholders.
• Screenshots: Add screenshots of your dashboard and cohort analysis.
• LinkedIn post: Share your project with a brief explanation of the business problem you solved.

SECTION 07Interview Q&A — e-commerce revenue analytics

SECTION 08Test yourself — e-commerce revenue quiz

SECTION 09Frequently asked questions

SECTION 10Related reads