E-Commerce Analytics Dashboard

The Problem

I needed visibility into customer purchasing patterns but couldn't query the production database directly-it would be a security risk and the queries would slow down the main application. I wanted to answer questions like: which countries have the highest returning customer rates? What's the average order value for first-time vs. repeat buyers? Which customers have spent over $300 and might be worth reaching out to? Without this data centralized somewhere accessible, every question meant context-switching out of marketing mode and into SQL.

The Solution

I built this as an internal tool for one of my own e-commerce projects. It syncs order data nightly from the production database into its own local copy, then calculates a suite of e-commerce metrics on demand. The dashboard lets the team filter by date range and instantly see metrics broken down by country, sales platform, and site-things like average order value, customer lifetime value, purchase frequency, and returning customer rates. It distinguishes between first-time and repeat purchases, which turns out to be valuable for understanding acquisition vs. retention performance. There's also a high-value customer export that pulls everyone who's spent over $300, enriched with their platform preferences, payment methods, and days since last order-useful data for the sales team to prioritize outreach.

How It Works

1. 1 Nightly Sync: A Celery scheduled task runs at midnight, SSHing into the production server, exporting the last 5 days of orders to CSV, and transferring it back for processing. The overlap window handles any orders that might have been missed.
2. 2 Customer Tracking: As orders are imported, the system checks if the customer exists. First-time buyers get flagged, which enables efficient filtering later without recalculating on every request.
3. 3 Metrics Calculation: When the dashboard loads, the backend filters orders by date range and runs aggregations-total revenue, order counts, unique customers-then derives metrics like AOV, LTV, and purchase frequency. Each dimension (country, platform, site) gets its own breakdown.
4. 4 Visualization: The Vue frontend renders sortable tables for detailed comparisons and time-series charts showing order volume and revenue trends over the selected period.

Tech Stack

• Backend: Python, Flask, SQLAlchemy, Celery
• Frontend: Vue 3, TypeScript, Tailwind CSS, Chart.js
• Database: SQLite (staging), MySQL (production)
• Task Queue: Redis
• Infrastructure: Linux VPS, Nginx

Results

• Over $5M in order revenue tracked and analyzed
• Fully automated daily data sync requiring zero manual intervention
• 12 calculated metrics across 3 segmentation dimensions (country, platform, site)
• High-value customer export with enriched data for sales outreach
• Sub-second dashboard loads despite aggregating across full date ranges