Scalable Business Intelligence. Processing millions of e-commerce events to drive ROI Optimization.
Drawing on my background managing Financial Operations Risk at State Street and advanced technical training from my MSc in Data Science at MMU, I architected this Scalable Business Intelligence engine. It transcends simple metric tracking by transforming millions of raw e-commerce events into precise financial levers, specifically targeting Customer Lifetime Value (CLV) and funnel velocity. By engineering a high-performance pipeline, this system directly empowers data-driven ROI optimization and strategic decision-making at scale.
Utilizing logic from 08. Cohort_analysis.sql, this module defines a cohort by the user's first purchase date. It dynamically tracks the percentage of users returning on Day 1, Day 2, etc., mapping critical Customer Lifetime Value (CLV) metrics via high-performance TEMPORARY TABLES.
Calculates user intent velocity using TIMESTAMPDIFF logic from 06. advanced_analytics.sql. By extracting the exact minutes elapsed between a user's first view and their first purchase, it models funnel friction through median distribution histograms.
Engineered to process 1M+ rows in seconds using LOAD DATA LOCAL INFILE and bypassing heavy global checks.
Query speed optimized via targeted idx_event_category and event_type indexing for sub-second latency.
Complex aggregations run on TEMPORARY TABLES to ensure memory safety on massive behavioral logs.
Automated data integrity checks for missing category_code values and pricing anomalies.
Interactive visualization simulating the dynamic Python/SQL filtering logic.
| Product ID | Category | Revenue ($) |
|---|
@app.get("/api/v1/analytics/dashboard")
def get_dashboard_data(category: str = "All"):
# Base logic utilizing idx_event_category
query_condition = ""
# 1. Fetch Funnel using TEMPORARY TABLES
# 2. Execute TIMESTAMPDIFF for Speed-to-Lead
# 3. Aggregate Top Products by Revenue
# 4. Extract Hourly Conversion & Cohort Matrix
return db.execute(query_condition)