GA4 Product Analytics: dbt on BigQuery & Snowflake
The modern analytics-engineering stack, shipped and tested: GA4 raw events to dbt models on BigQuery and Snowflake to a decision. Headline finding: only 1 in 5 product-viewers add to cart, so that is where the growth is.
The stack
Google's public GA4 obfuscated e-commerce export in BigQuery (real, nested event data, no PII), modelled with dbt: a staging view flattens the raw events, then marts build a session fact table and the funnel, retention-cohort, and RFM-segment models. Sources, tests (not-null, unique, accepted-values) and docs throughout; dbt build passes 15 of 15.
This is the modern London data stack, SQL plus BigQuery plus dbt plus GA4 plus BI, built and tested rather than just listed.
The funnel
The dominant leak is the product view to add-to-cart step: only 20.5% of viewers add to cart. Cart-to-checkout is healthy at 77%, but checkout-to-purchase loses over half. So the recommendation is to fix the product-page and add-to-cart experience first, then reduce checkout abandonment, and A/B test each change rather than assume.
The funnel, in space
Each disc is one funnel stage. Area is proportional to users, and the stages sit on one vertical axis in order, so magnitude is compared along a single scale rather than judged from perspective. Drag to orbit; click a stage to lift it.
User-level purchase funnel
| Step | Users | % of viewers | vs previous step |
|---|---|---|---|
| View item | 61,252 | 100% | n/a |
| Add to cart | 12,545 | 20.5% | 20.5% |
| Begin checkout | 9,715 | 15.9% | 77.4% |
| Purchase | 4,419 | 7.2% | 45.5% |
Segments
RFM scoring splits the 4,419 purchasers into six named segments with a clear action for each: protect Champions and Loyal, nurture New / Promising into repeat buyers, and run win-back on At Risk and Hibernating, who still carry meaningful average value.
RFM segments of purchasing users
| Segment | Users | Avg revenue (USD) |
|---|---|---|
| Loyal | 1,020 | ~105 |
| Champions | 747 | ~90 |
| At Risk | 358 | ~86 |
| Hibernating | 750 | ~81 |
| Needs Attention | 872 | ~65 |
| New / Promising | 672 | ~58 |
Reproducible and tested
Validity is enforced by dbt tests rather than assumed, and the build is fully reproducible: anyone with a BigQuery or Snowflake sandbox can dbt build it from the public dataset and get the same tables.