Analytics engineering · SQL · DuckDB
Where do users drop off?
A reproducible SQL ELT for product conversion-funnel analytics, built on a bronze, silver and gold model and running on DuckDB.
The questions
Which funnel step loses the most people, which device converts best, how much traffic is bots.
What I built
One idempotent SQL pipeline: raw tracker JSON in, cleaned tables, then dashboard-ready metrics.
Quality
Six explicit data-quality rules, each run as a live check.
The layers
Bronze
raw_sessions and raw_pages: the tracker JSON loaded as it arrived.
Silver
clean_sessions and clean_pages: device names normalised, impossible durations nulled, timestamps parsed, missing countries set to UNKNOWN, sessions under 5 seconds flagged as suspicious, and a load timestamp for audit.
Gold
gold_funnel_metrics: sessions, average duration, CTA clicks, conversions and conversion rate per funnel step, from Awareness to Decision.
gold_device_metrics: click-through and conversion rate per device.
Data-quality rules
| ID | Risk | Mitigation |
|---|---|---|
| DQ-1 | Null primary keys | Drop rows with a null session or user id |
| DQ-2 | Inconsistent device casing | lower(trim(device)) in Silver |
| DQ-3 | Unrealistic durations | Null out values below 0 or above 7,200 s |
| DQ-4 | Unparseable timestamps | try_cast to NULL instead of failing |
| DQ-5 | Sessions with no matching page | LEFT JOIN and monitor the unmatched rate |
| DQ-6 | Duplicates on re-runs | Idempotent rebuild and a load-time audit column |
The pipeline is idempotent: every section starts with DROP ... IF EXISTS, so it can run any number of times with the same result.