End-to-end analytics project transforming raw session-level data into commercial insights and an interactive BI dashboard.
This project demonstrates an end-to-end analytics engineering workflow, transforming 1.6M+ raw behavioural events into structured, reusable data models and commercial KPIs.
Built using a modular data modelling approach (staging → intermediate → marts), the project standardises funnel, revenue, and retention metrics to support scalable analysis and decision-making.
Key focus areas include:
• Event-based data modelling
• Funnel and cohort transformation logic
• Metric standardisation (conversion, AOV, retention)
• Revenue impact modelling
Using session-level revenue aggregation:
- Total Revenue: $6,348,004.87
- Total Orders: 155,617
- Average Order Value (AOV): $40.79
-
+5 percentage-point lift in Cart → Purchase conversion
→ +$812K incremental revenue -
Optimizing checkout friction for high-engagement brand "runail"
→ ~$85K potential uplift -
Reducing extreme cart abandonment for "kapous"
→ $30–50K revenue opportunity
All projections are based on session-level AOV to reflect true order economics.
Tracked 1,639,358 unique visitors across a four-stage funnel:
| Stage | Users |
|---|---|
| Total Visitors | 1,639,358 |
| Product Viewers | 1,597,754 |
| Cart Adders | 398,308 |
| Final Buyers | 110,518 |
-
View → Cart: 75.1% drop-off
→ Indicates potential pricing, positioning, or product page friction -
Cart → Purchase: 72.3% drop-off
→ Suggests checkout friction, payment barriers, or shipping cost sensitivity
Conversion performance varies significantly by product category and brand.
| category_id | brand | viewers | cart_adders | buyers | add_to_cart_rate | purchase_rate |
|---|---|---|---|---|---|---|
| 1602943681873052386 | grattol | 83,670 | 36,398 | 12,594 | 43.5% | 34.6% |
| 1487580013950664926 | None | 69,436 | 11,283 | 2,953 | 16.2% | 26.2% |
| 1487580013841613016 | kapous | 61,251 | 7,463 | 2,431 | 12.2% | 32.6% |
| 1487580005092295511 | None | 52,449 | 31,086 | 11,020 | 59.3% | 35.5% |
| 1487580005134238553 | runail | 27,225 | 20,807 | 6,465 | 76.4% | 31.1% |
| Add-to-Cart Drop-off | Purchase Drop-off |
|---|---|
 |
 |
-
Friction Identification:
Brand kapous exhibits extreme cart abandonment (~88%), indicating major conversion barriers. -
High-Potential Segment:
runail demonstrates strong engagement (76.4% add-to-cart rate) but suffers from 69% payment drop-off — making it a prime candidate for checkout optimization.
Users were segmented by first purchase month and tracked over a 5-month horizon.
- October 2019 cohort: 18.5% Month-1 retention
- December 2019 cohort: 8.5% Month-1 retention
This represents a 54.1% decline, suggesting holiday-driven acquisition generates lower long-term loyalty compared to standard-period customers.
To ensure analytical rigor, the following core metrics were defined:
Conversion Rate = Conversions ÷ Users at Previous Funnel Stage
Examples:
- View → Cart = Cart Adders ÷ Product Viewers
- Cart → Purchase = Buyers ÷ Cart Adders
Drop-off Rate = 1 − Conversion Rate
Or equivalently:
Drop-off Rate = (Users at Previous Stage − Users at Next Stage) ÷ Users at Previous Stage
Example: Cart Drop-off = (Cart Adders − Buyers) ÷ Cart Adders
Because a session may contain multiple purchase events (multiple items per order), revenue was aggregated at the session level.
AOV = Total Revenue ÷ Total Orders
Where:
- Total Revenue = SUM(price) for purchase events
- Total Orders = COUNT(DISTINCT user_session)
In this dataset:
- Total Revenue = $6,348,004.87
- Total Orders = 155,617
- AOV = $40.79
All revenue projections in this project are based on session-level AOV.
Based on data-driven findings:
Deploy abandoned cart email / paid remarketing for high-engagement brands (e.g., runail, grattol).
Reduce payment friction by:
- Guest checkout options
- One-click payment
- Transparent shipping costs
- Streamlined mobile checkout
Implement automated post-purchase onboarding and retention campaigns, especially for Q4-acquired cohorts.
🔗 View Commercial Performance Dashboard
Dashboard capabilities include:
• Channel-level ROAS and CAC monitoring
• Conversion funnel tracking across the customer journey
• Revenue and AOV performance trends
• Budget allocation simulation for marketing optimisation
To extend funnel insights into marketing performance diagnostics, a simulated channel cost layer was introduced.
Based on the analysis, several commercial optimisation opportunities were identified:
• Validate December efficiency compression before expanding marketing budgets
• Reallocate spend toward high-AOV and stable-ROAS acquisition channels
• Avoid aggressive traffic scaling until Cart → Purchase friction is reduced
• Prioritise capital efficiency rather than raw traffic growth
Data Warehouse:
- Google BigQuery (SQL) — Data cleaning, funnel modeling, cohort logic
Analysis:
- Python (Pandas, NumPy) — Matrix operations and retention modeling
Visualization & BI:
- Matplotlib, Seaborn — Funnel visualization and cohort heatmaps
- Google Looker Studio
Environment:
- Google Colab
01_data_preprocessing.sql
02_funnel_analysis.sql
03_cohort_retention_logic.sql
04_calculate_aov.sql
05_Simulated_Channel.sql
06_Simulated_Budget_Allocation.sql
cosmetics_funnel_viz.ipynb cohort_result.csv images/ README.md
This project demonstrates how behavioral event data can be transformed into:
- Funnel diagnostics
- Revenue impact modeling
- Segment-level performance optimization
- Cohort-based retention strategy
- BI dashboard development for ongoing performance monitoring
Bridging Raw behavioral data → Performance insights → Capital allocation decisions
Enabling data-informed growth strategy rather than isolated reporting.