English | 中文

E-commerce User Behavior Analytics

A full-stack analytics pipeline on 29M real user behavior records from Taobao
_{Data Engineering · SQL Analytics · Statistical Modeling · A/B Testing · Interactive Delivery}

---

Headline

A Polars + DuckDB pipeline processes 29M rows of real e-commerce behavior data, covering the full user lifecycle: retention, conversion funnel, RFM, churn prediction, recommendation, A/B testing, and uplift modeling (CATE). The most important causal finding is not "a significant lift was found", but the opposite: with a post-hoc simulated treatment, ATE≈0 and S-learner AUUC = 12.7 ± 44.6 across 5 random seeds — the variance exceeds the point estimate, so no stable CATE ranking can be claimed. On the engineering side, delivery is guarded by 31 dbt data-quality tests and four GitHub Actions CI gates.

Dimension	Key result
Data scale	29M rows (29,128,402 events) · 287,004 users · 2,584,623 items · 8,788 categories (2017-11-24 ~ 12-03)
ETL performance	A prior local warm-cache run measured ~0.4s; runtime varies with CPU, disk, and cache state — re-measure with the included benchmark script
Uplift (S-learner)	AUUC = 12.7 ± 44.6 · Qini = +1.8 ± 22.3 (5 seeds; no robust CATE found)
Churn prediction	XGBoost AUC 0.73 / Logistic Regression AUC 0.80 (time split by last_active_date)
A/B test	lift +0.44% · p=0.43 (not significant; post-hoc holdout design)
Engineering	31 dbt data-quality tests all green · four CI gates

Uplift curves (S/T-learner) — the deep-dive from ATE to CATE

uplift curve · Qini curve · decile-validation 3-panel; full metrics in reports/uplift_results.json

---

Data

Attribute	Value
Source	Alibaba Tianchi — "Taobao User Behavior" Open Dataset
Scale	287,004 users · 2,584,623 items · 8,788 categories · 29,128,402 records
Time Window	2017-11-24 ~ 2017-12-03 (10 days)
Behaviors	pv (page view) · buy · cart · fav

Second-level timestamps enable fine-grained behavioral sequence modeling; the 29M-row scale provides a solid testbed for benchmarking modern analytics tools like Polars / DuckDB.

---

Quick Start

git clone https://github.com/MeaFew/shoplytics.git
cd shoplytics

# Create and activate a Python 3.10+ virtual environment
python -m venv .venv
# Linux / macOS: source .venv/bin/activate
# Windows PowerShell: .venv\Scripts\Activate.ps1

# Install locked dependencies, the package, and development tools
make setup
# Windows without GNU Make: python -m pip install -r requirements.lock
#                           python -m pip install -e ".[dev]"

# Download the dataset (GitHub Releases, ~264MB)
bash download_data.sh

# Verify the environment (lint + test + format-check + audit)
make verify

# Run the full analysis pipeline
make all
# Windows (no GNU Make):
# python run_all.py

# Launch the interactive dashboard
make dashboard

---

Key Metrics

Metric	Value	Business Insight
Avg. DAU	205,798	Stable active user base during the 10-day core window
PV → Purchase Conversion	2.24%	Typical e-commerce level; optimization opportunity lies in homepage recommendation accuracy
PV → Cart Conversion	6.21%	Product detail page design effectively drives add-to-cart behavior
Cart → Purchase Conversion	36.04%	Cart recovery (SMS / push) is a high-ROI optimization lever
Day-1 Retention	74.6%	Healthy first-week retention for new users
Zero-Conversion Viewed Items	89.3% (2,243,686)	Viewed items that were never purchased; long-tail distribution needs optimization

The metrics above are computed by shoplytics.pipeline.compute_headline_metrics and written to reports/pipeline_summary.json (the headline_business_metrics field), rather than living only as static numbers in this README. Day-1 retention is defined as "the share of users active on their first day who are still active on the next day"; zero-conversion items are "items that were viewed but never purchased", as a share of all viewed items.

Windows note: if sqlfluff lint sql/ fails with an encoding error, run set PYTHONUTF8=1 (CMD) or $env:PYTHONUTF8=1 (PowerShell) first.

Full business insights report: reports/business_insights_report.md

---

Architecture

                  ┌─────────────────┐
                  │  UserBehavior   │
                  │   CSV (29M)     │
                  └────────┬────────┘
                           │ Polars ETL (~0.4s)
                           ▼
┌─────────────────────────────────────────────────────────────┐
│                      DuckDB / Parquet                        │
│  ┌─────────────┐  ┌─────────────┐  ┌─────────────────────┐  │
│  │ SQL Analysis│  │ dbt Models  │  │   PySpark (MLlib)   │  │
│  │ 7 scripts   │  │ staging →   │  │   ALS Matrix        │  │
│  │             │  │ intermediate│  │   Factorization     │  │
│  │             │  │ → marts     │  │   (Distributed)     │  │
│  └──────┬──────┘  └──────┬──────┘  └──────────┬──────────┘  │
└─────────┼────────────────┼────────────────────┼─────────────┘
          │                │                    │
          ▼                ▼                    ▼
   ┌────────────┐   ┌────────────┐      ┌────────────┐
   │  XGBoost   │   │  Streamlit │      │  Jupyter   │
   │ Churn Model│   │ Dashboard  │      │ Notebooks  │
   │ (11 feats) │   │ (KPI/R/F/M)│      │ (EDA/AB/   │
   │ AUC ≈ 0.7* │   │            │      │  Cohort)   │
   └────────────┘   └────────────┘      └────────────┘

---

Analytical Modules (11)

#	Module	Core Technique	Deliverable
1	User Retention	Self-join + window functions (ROW_NUMBER, LAG)	D1/D3/D7 retention curves; identify churn inflection points
2	Conversion Funnel	CTE + conditional aggregation + path classification	Four-step funnel (PV → fav/cart → buy); locate the biggest leak
3	RFM Segmentation	NTILE(5) binning + lifecycle state migration	8 user personas (champions / loyal / new / at-risk, etc.)
4	A/B Test Framework	Unbiased hash grouping · SRM chi-square check · two-proportion Z-test · 95% CI · recommender lift measurement	Post-hoc holdout design: train the recommender on the control group → simulate exposure on the treatment group → symmetric Z-test
5	Uplift Modeling	S-learner / T-learner (CATE) · AUUC / Qini · decile calibration	Moves from average effect (ATE) to conditional effect (CATE): who responds to a recommendation (counterfactual framework)
6	Churn Prediction	XGBoost vs Logistic Regression · 11-feature engineering · ROC-AUC	Time-split AUC ≈ 0.7 (see the correctness-fixes note below)
7	Recommendation	UserCF (cosine similarity) · ALS (PySpark MLlib)	Side-by-side comparison of collaborative filtering + matrix factorization
8	Anomaly Detection	3σ rule + moving average	Automated daily reports + anomaly behavior alerts
9	Cohort & LTV	Cohort retention heatmap · behavior-weighted value estimation	User cohort lifecycle value tracking
10	Dashboard	Streamlit + Plotly · KPI cards · funnel · RFM	Self-service analytics tool for product / ops teams
11	Data Engineering	dbt model layering · 31 data quality tests	Version-controlled analytics data pipeline

---

Tech Stack

Layer	Tools	Rationale
Processing	Polars (Rust core) · Pandas · PySpark	Polars handles single-node columnar processing; an included benchmark makes Polars/Pandas timing reproducible; PySpark provides distributed ALS
SQL Engine	DuckDB	Zero-config columnar OLAP; sub-second aggregation of 29M rows on a single node
Data Engineering	dbt	Model versioning, lineage tracking, automated testing — elevates analytics SQL from scripts to engineered pipelines
Statistical Modeling	scikit-learn · XGBoost · SciPy · statsmodels	XGBoost handles high-dimensional sparse features; statsmodels provides classical statistical inference
Visualization	Matplotlib · Seaborn · Plotly	Plotly interactive charts embed directly into Streamlit
Delivery	Streamlit · Apache Superset · Jupyter	Streamlit for lightweight self-service dashboards; Superset (Docker) for interactive BI demo exploration
Quality Assurance	pytest · ruff · sqlfluff · GitHub Actions	All-green CI = code style + SQL style + Docker build triple validation

---

Limitations & Production Path

Limitation	Impact	Production Approach
~10-day effective window	Cannot observe monthly seasonality; D7+ retention is right-censored	Extend to ≥90 days of data; introduce Prophet / ARIMA forecasting
No monetary field	GMV, ARPU, CLV unavailable; RFM degrades to RF	Join with order / transaction table to complete the monetary dimension
No user attributes	Missing demographics, device, channel segmentation	Join with user profile table for multi-dimensional cohort analysis
A/B test is an offline simulation	Offline behavior logs contain no online experiment events; an item-CF recommender trained on the control group is used to simulate exposure for the treatment group	Plug into a real online experiment platform (Optimizely / internal AB system) that records exposure and conversion events
Uplift treatment is post-hoc simulated	Hash-derived W has no real causal link to outcome (ATE≈0); multi-seed analysis shows uplift metrics degrade to noise (std > point estimate)	Swap in a real online exposure flag + real conversion events and the pipeline transfers as-is
Single-node execution	DuckDB + local Parquet	Hive / Spark on partitioned Parquet + Airflow scheduling

The A/B test uses a post-hoc holdout design to answer "does the recommender lift conversion?": users are split unbiasedly into control / treatment by hash; an item-CF recommender is trained only on the control group's observation window (the model never touches treatment users); recommendation exposure is then simulated for the treatment group; and a symmetric two-proportion Z-test compares the two groups' prediction-window conversion rates. On this data the result is lift=+0.44%, p=0.43 (not significant) — an honest conclusion: a low hit-rate item-CF produces almost no causal effect in a post-hoc simulation. The framework itself (SRM check + symmetric denominators + Z-test) is rigorous and usable, and transfers as-is to scenarios with real online experiment data.

---

Uplift Modeling (CATE Estimation)

An A/B test answers the average treatment effect (ATE): "does the recommendation work on average, for everyone?" The real question in growth / recommendation is the conditional average treatment effect (CATE): "for which kind of user does it work?" — spend marketing budget on users the recommendation can persuade, not on "would-have-bought-anyway" or "won't-buy-anyway" users. src/shoplytics/uplift_modeling.py moves the project from ATE to CATE — a scarce "counterfactual causal learning" signal in recommendation / growth portfolios.

Why it's needed (the methodological limit of A/B tests) — click to expand

Dimension	A/B test (ATE)	Uplift modeling (CATE)
Question answered	Does the recommendation work on average, for everyone?	For which kind of user does it work?
Output	One lift number + a p-value	A predicted increment τ̂(x) per user
Practical value	Decides "whether to ship"	Decides "whom to target" — precision targeting
Limitation	Population average; ignores heterogeneity	Needs an observable treatment signal

How it works (S/T-learner mechanics and feature engineering) — click to expand

                Features X (user-level behavior, observation window)
                Treatment W (hash split 0/1)
                Outcome   Y (purchase in prediction window)
                              │
        ┌─────────────────────┴─────────────────────┐
        ▼                                           ▼
   S-learner                                    T-learner
   ┌─────────────────┐                    ┌──────────────────┐
   │ One model       │                    │ P_T(Y|X) P_C(Y|X)│
   │ P(Y|X,W): W is  │                    │ two models, one  │
   │ a feature column│                    │ per arm          │
   └────────┬────────┘                    └────────┬─────────┘
            │                                      │
   τ̂(x)=f(x,W=1)−f(x,W=0)           τ̂(x)=P_T(Y=1|x)−P_C(Y=1|x)

• S-learner: a single model takes treatment W as one feature column alongside X; at prediction time the same sample is scored with W=1 and W=0, and the difference is the CATE. Pros: one model only, robust on small samples. Cons: W is just one column — a weak signal can be drowned out by strong features (under regularization the treatment dimension may be pruned early).
• T-learner: two independent models fit treatment / control separately; prediction takes P(Y=1) from each and subtracts. Pros: the treatment effect is modeled explicitly. Cons: each model sees only half the data, so variance is higher. Base learner: XGBoost for both.

Features X (15-dim, strictly using only observation-window events < 2017-12-01, no label leakage): behavior counts (pv/buy/cart/fav/total events), active days, active hours, days since the most recent behavior, weekend activity ratio, category diversity, item diversity, per-behavior conversion rates, activity-hour entropy.

Evaluation: uplift curve (cumulative gain), Qini curve (cumulative incremental positives), AUUC (area under the uplift curve), Qini coefficient (difference vs the random baseline); plus a decile check that splits the test set into ten bins by predicted uplift to verify "do the highest-predicted-uplift deciles really show higher realized lift?".

Results (80k sampled / 287k full users, mean±std over 5 split seeds)

Model	AUUC	Qini coef	Top-vs-bottom decile real-lift diff
S-learner	12.7 ± 44.6	+1.8 ± 22.3	+0.017 ± 0.024
T-learner	−9.3 ± 31.7	−9.2 ± 15.8	−0.002 ± 0.028

Metrics are mean±std over 5 different train/test split seeds. Multi-seed is essential: because the treatment is post-hoc simulated (ATE≈0), a single split yields uplift metrics with huge variance and randomly-flipping sign (one run +60, the next −30); a single number is meaningless — only the mean±std across seeds is an honest point estimate.

The most honest — and most important — finding: the two groups' average conversion rates are nearly identical (treatment 38.43% vs control 38.36%, ATE = +0.001, essentially zero) — exactly as expected since the treatment is post-hoc simulated. On data where ATE≈0, the model cannot robustly recover a CATE ranking: every uplift metric's standard deviation exceeds the absolute point estimate, and the decile-calibration curve is not monotone (it wiggles around zero — see the right panel of images/uplift_curve.png). In other words — without a real treatment signal, uplift modeling degrades to fitting noise.

That is precisely this module's value: it honestly demonstrates the full uplift-modeling methodology (feature engineering → S/T-learner → AUUC/Qini → multi-seed uncertainty → decile calibration) and, via rigorous multi-seed analysis, surfaces the conclusion "no real treatment → no recoverable CATE" — rather than mislead with a coincidentally-positive single number. The framework itself (including the multi-seed robustness check) is sound and portable — swap W for a real online exposure flag and Y for a real conversion event, and the pipeline recovers a meaningful CATE ranking on data with a real treatment.

Full metrics: reports/uplift_results.json · Visualization (uplift curve / Qini curve / decile calibration, 3-panel): images/uplift_curve.png

⚠️ Semantic boundary: the treatment is post-hoc simulated (honest caveat)

This must be stated plainly — the result is NOT a claim of a real causal effect. The raw data is an observational log: control / treatment users experienced the same system, no intervention actually happened. This module derives a synthetic binary W via hash_group(user_id) (identical to ab_testing.py), so there is no real causal link between W and the outcome Y — and the multi-seed results above (point estimate ≈ 0, std dominant) are the statistical confirmation of exactly that.

What we estimate is therefore the CATE under a "hypothetical recommendation deployment" counterfactual framework — demonstrating the uplift-modeling methodology (feature engineering, S/T-learner, AUUC/Qini, multi-seed uncertainty, decile calibration), not a discovered causal effect. The framework itself is sound and portable: swap W for a real online exposure flag and Y for a real conversion event, and the entire pipeline transfers to production (Optimizely / internal AB platform). On data with a real treatment signal, this pipeline recovers a meaningful CATE ranking — at which point uplift modeling's ability to "localize responder subgroups" finally materializes.

---

Data & modeling correctness fixes (important — click to expand)

This pipeline fixes several issues that affected the correctness of its conclusions:

• RFM score direction (dbt marts): mart_user_segments and mart_rfm_segments previously had inconsistent score directions across the three columns, so the operational value_tier / rfm_segment labeled "low-value / churned" users as "high-value / important-value". The scoring is now unified to (6 - NTILE(5) OVER (ORDER BY x [A/D]SC)) so that 5 = best, with a new regression test assert_rfm_score_direction.sql locking the invariant. After the fix, high-value users show significantly higher average monetary/frequency and lower recency.
• Cleaning-report counts: removed_invalid_timestamp / removed_invalid_behavior were previously hard-coded to 0, with the entire difference dumped into removed_duplicates. Each step is now counted separately (true values: 4,079 out-of-range timestamps / 0 invalid behaviors / 11 duplicates / 1 null ID) — data_quality_report.json is now trustworthy.
• ID types & silent data loss: ID columns were upgraded from Int32 to Int64 (item_id can exceed the Int32 range), and null-ID rows are now explicitly dropped and counted during cleaning, eliminating the risk of "nulls silently flowing into analysis".
• Headline business metrics: DAU, PV→buy/cart conversion, cart→buy conversion, Day-1 retention, and the zero-conversion item share are now computed by shoplytics.pipeline.compute_headline_metrics and written to reports/pipeline_summary.json, instead of being static numbers in the README.
• Time split for churn prediction: switched to a time-based split on last_active_date (train on early-active users, test on late-active users) — a deployable generalization estimate rather than random resubstitution. Note that modeling runs on a random 100k-user sample (for speed), so the time-split AUC has non-trivial sample variance; remove the sample cap for a full-population estimate. The claimed dbt test count was also corrected from 29 to the actual 31 (dbt test all green).
• Honest reporting of recommendation Precision@10: the previously reported "UserCF hit rate of 0" was actually caused by _usercf_recs treating user-similarity indices as item indices (user-space / item-space confusion), not by sparsity alone; the bug is fixed (scores = sims @ matrix projects the scores into item space, with regression test tests/test_recommendation.py). The reported Precision@10 = 0.02 (popularity fallback) was produced by the pre-fix version and will refresh on the next pipeline run; the evaluator keeps the fallback-to-popularity logic for when UserCF yields zero hits (the purchase matrix is extremely sparse, ~99.8%). This is still a straw-man baseline; for production-grade recommendation use the ALS matrix factorization in src/shoplytics/pyspark/.
• Makefile environment variables: all Python targets now get PYTHONPATH=. injected; dbt targets get absolute DBT_DUCKDB_PATH / DBT_DATA_PATH, so relative paths no longer resolve to the wrong place after cd dbt.

---

Project Structure (click to expand)

shoplytics/
├── src/shoplytics/               # Python package and executable modules
│   ├── preprocess.py             #   Polars ETL (~0.4s / 29M rows)
│   ├── pipeline.py               #   Pipeline orchestrator (load → dispatch modules → summarize)
│   ├── eda.py                    #   EDA visualization (behavior / DAU / activity heatmap)
│   ├── churn_prediction.py       #   Churn prediction (LR + XGBoost + ROC / importance plots)
│   ├── ab_testing.py             #   A/B test (hash grouping + Z-test + SRM check)
│   ├── uplift_modeling.py        #   Uplift modeling (S/T-learner CATE + AUUC/Qini)
│   ├── recommendation.py         #   CF recommendation + cohort retention + LTV estimation
│   ├── run_sql.py                #   DuckDB SQL batch execution
│   ├── validate_data.py          #   Data quality validation
│   ├── benchmark_preprocessing.py #  Polars vs Pandas performance benchmark
│   └── pyspark/                    #  PySpark distributed computing (4 scripts)
│
├── notebooks/                    # Jupyter analysis notebooks (5)
│   ├── 01_eda_and_visualization.ipynb
│   ├── 02_user_churn_prediction.ipynb
│   ├── 03_ab_test_analysis.ipynb
│   ├── 04_recommendation_system.ipynb
│   └── 05_cohort_and_ltv.ipynb
│
├── sql/                          # SQL analysis scripts (7)
│   ├── 01_database_setup.sql     #   Schema + indexes + views
│   ├── 02_user_retention.sql     #   Retention analysis (D1/D3/D7)
│   ├── 03_conversion_funnel.sql  #   Funnel + path analysis
│   ├── 04_rfm_model.sql          #   RFM segmentation
│   ├── 05_ab_test_framework.sql  #   A/B test data preparation
│   ├── 06_anomaly_detection.sql  #   Anomaly detection (3σ rule)
│   └── 07_product_analysis.sql   #   Product & category analysis
│
├── dbt/                          # dbt data models + tests
│   ├── models/staging/
│   ├── models/intermediate/
│   └── models/marts/
│
├── dashboard/                    # Streamlit interactive dashboard
├── superset/                     # Apache Superset BI configuration
│   ├── superset_config.py        #   Custom Superset configuration
│   └── add_duckdb.py             #   DuckDB datasource auto-registration
├── images/                       # Generated charts
├── reports/                      # Analysis reports
├── docs/                         # Architecture Decision Records (ADR)
├── docker-compose.superset.yml   # Superset Docker Compose configuration
├── Makefile                      # Workflow orchestration
└── requirements.lock             # Reproducible dependency pins

---

BI Dashboard (Apache Superset)

In addition to the lightweight Streamlit dashboard, the project integrates Apache Superset (Docker) as an interactive BI demo exploration tool:

# Start Superset (requires Docker)
docker compose -f docker-compose.superset.yml up -d

# First startup takes ~1–2 minutes for initialization
# Open http://localhost:8088
# Login: admin / Password: admin

Pre-configured:

• DuckDB datasource auto-connected (analytics.duckdb)
• SQL Lab for ad-hoc queries on 29M rows
• Explore interface for drag-and-drop chart creation (DAU trends, conversion funnels, category distributions)

Why keep both Streamlit + Superset?

Dimension	Streamlit	Apache Superset
Positioning	Lightweight self-service dashboard	Interactive BI demo exploration
Use Case	Fixed KPI monitoring	Ad-hoc analysis, drill-down, multi-dimensional slicing
Development	Python code	Zero-code drag-and-drop + SQL
Audience	Product managers / Operations	Data analysts / Management

In production, Streamlit suits fixed dashboards embedded in business systems; Superset serves analyst self-service exploration. They complement rather than replace each other.

---

Resources

• docs/ADR.md — Architecture Decision Records: why DuckDB, Polars, dbt, and the flattened directory structure
• CONTRIBUTING.md — Local setup, development workflow, lint rules, and commit conventions
• sql/README.md — SQL analysis module guide and database compatibility reference

---

Related Projects

Project	Repo	Description
Marketing Attribution & MMM	MeaFew/attributor	MMM + multi-touch attribution + budget optimization
Credit Risk Scoring	MeaFew/riskscore	WOE/IV + XGBoost/LightGBM + SHAP interpretability
Multivariate Time Series	MeaFew/foresight	LSTM / Transformer / XGBoost forecasting benchmarks
Graph Fraud Detection	MeaFew/graphguard	GNN illicit transaction detection (Elliptic)

---

License

Dataset provided by Alibaba Tianchi under its usage terms. Code is released under MIT License for educational purposes.