Product Analytics + Experimentation Platform

End-to-end product analytics system for a simulated e-commerce company — built as a portfolio flagship demonstrating real analyst skills: SQL, dbt, A/B testing, and Streamlit dashboards.

---

What This Project Demonstrates

Skill	Implementation
Data Modelling	ER diagram, event tracking plan, fact/dim tables
SQL Analytics	10 production-grade queries (DAU/MAU, retention, LTV/CAC, RFM)
dbt Thinking	Staging + mart models with full lineage
A/B Testing	Z-test, Chi-square, Bayesian analysis, power analysis
Dashboard	Interactive Streamlit with 6 analytical views
Business Thinking	Revenue impact quantification, recommendations

---

Architecture

Raw Events (SQLite)
        │
        ▼
┌──────────────────┐    ┌──────────────────┐
│  stg_events.sql  │    │  stg_orders.sql  │  ← Staging Layer (dbt)
└────────┬─────────┘    └────────┬─────────┘
         │                       │
         ▼                       ▼
┌──────────────────┐    ┌──────────────────┐
│   dim_users.sql  │    │  fct_orders.sql  │  ← Mart Layer (dbt)
└────────┬─────────┘    └────────┬─────────┘
         └──────────┬────────────┘
                    ▼
         ┌─────────────────────┐
         │  Streamlit Dashboard │
         │  + A/B Test Report   │
         └─────────────────────┘

---

Project Structure

project1-product-analytics/
├── data/
│   ├── generate_data.py      # Synthetic data generator (3k users, 2.5k orders)
│   ├── ecommerce.db          # SQLite database
│   └── csv/                  # CSV exports
├── sql/
│   ├── 01_dau_mau.sql        # Daily/Monthly Active Users
│   ├── 02_conversion_rate.sql # Funnel conversion
│   ├── 03_retention_cohorts.sql
│   ├── 04_revenue_trends.sql
│   ├── 05_ltv_cac.sql        # Customer LTV & CAC by channel
│   ├── 06_product_performance.sql
│   ├── 07_user_segmentation.sql  # RFM scoring
│   ├── 08_new_vs_returning.sql
│   ├── 09_churn_analysis.sql
│   └── 10_device_channel_attribution.sql
├── dbt_models/
│   ├── staging/
│   │   ├── stg_events.sql
│   │   └── stg_orders.sql
│   └── marts/
│       ├── dim_users.sql
│       └── fct_orders.sql
├── notebooks/
│   └── ab_testing.py         # Full A/B testing system
├── streamlit_app/
│   └── app.py                # 6-page interactive dashboard
└── reports/
    └── business_case_study.md

---

Quick Start

# 1. Install dependencies
pip install -r requirements.txt

# 2. Generate data (3,000 users, 2,500 orders, 31k events)
python data/generate_data.py

# 3. Launch dashboard
streamlit run streamlit_app/app.py

# 4. Run A/B test report
python notebooks/ab_testing.py

---

Dataset Overview

Table	Rows	Description
users	3,000	Acquisition channel, device, country, signup date
products	30	6 categories, price, cost, margin
events	31,524	page_view → product_view → add_to_cart → checkout → purchase
orders	2,500	With seasonal distribution (Christmas peak)
payments	2,500	Card, PayPal, Apple Pay
ab_experiment	3,131	Checkout redesign experiment

---

A/B Test Results

Hypothesis: New checkout UI increases purchase conversion rate.

Metric	Control	Treatment
Conversion Rate	26.3%	34.7%
Relative Lift	—	+31.6%
P-value	—	< 0.0001
Bayesian P(Treatment > Control)	—	100%

** Recommendation:** Roll out to 100% of users → projected +£595,876/year revenue uplift.

---

Dashboard Pages

1. Overview — KPI cards, daily revenue, channel performance
2. Revenue — Daily/monthly trends, MoM growth
3. Retention — Cohort heatmap + retention curves
4. Funnel — Step-by-step drop-off analysis
5. A/B Testing — Interactive frequentist + Bayesian analysis
6. User Segments — RFM treemap + segment actions

---

Business Impact (Resume Bullet)

"Built end-to-end product analytics platform with SQL, Python, and Streamlit. Designed A/B testing system that identified a 31.6% conversion rate improvement (p < 0.0001), projecting £595K in annual revenue uplift for a simulated e-commerce platform."