Marketing Analytics Case Study
GA4 Ecommerce
Growth Audit
Optimizing acquisition quality, product visibility, and the purchase journey
I audited 90 days of Google Merchandise Store data in GA4 to find where shoppers drop off, then rebuilt the analysis as a Python and MySQL pipeline so every number could be re-run and checked. The biggest leak comes before the cart: 75% of product viewers never add to cart, and mobile converts at about one-fifth the desktop rate.
Executive summary
The biggest traffic source was not the best quality source.
I found a familiar ecommerce pattern: the biggest traffic source was not the best quality source, and the biggest conversion opportunity sat in the mobile purchase journey. Organic Search combined meaningful scale with the strongest engagement, while mobile users experienced steep drop-off from product view to purchase.
I recommend improving attribution, making high-converting products easier to discover, and removing mobile friction with a focused experiment sequence rather than chasing more volume first.
Case-study introduction
What I analyzed and why.
01 / Insight
Direct traffic created volume but relatively weak engagement.
Direct accounted for 209,231 sessions, or 65.27% of all traffic, but its engagement rate was only 23.46%. Organic Search generated 59,575 sessions with a much stronger 67.73% engagement rate.

Direct created traffic volume, but Organic Search delivered a healthier balance of scale and engagement. The unusually high Direct and Unassigned shares also make attribution hygiene a business issue, not just a reporting issue.
02 / Insight
Organic Search provided the strongest balance of scale and traffic quality.
First-user acquisition reinforced the same pattern: Direct brought roughly three-quarters of new users, but its user key-event rate was about 10.5%. Organic Search brought about 35K new users and achieved an estimated 40.7% key-event rate.
User key-event rate represents the percentage of users who triggered at least one designated key event.

03 / Insight
The purchase journey loses most users before cart and checkout.
Of 46,326 users who viewed a product, only 11,439 added one to the cart—a 75.31% drop-off at the first major transition. The full funnel ended with 2,880 purchasers, producing a 6.22% view-to-purchase completion rate.

04 / Device Insight
Mobile customers experience the largest conversion gap.
Desktop checkout completion: 55.25%
Mobile checkout completion: 24.14%

Product visibility did not always translate into revenue: the Nano Banana Sweatshirt had the most displayed product views at 6,139, while the Google Recycled Black Hoodie generated the highest displayed revenue at $19,935. The Google Eco Tee White achieved the highest displayed purchase rate at 14.65%.
05 / Executive Dashboard
A manager-ready snapshot.
This is the one-minute readout: the channel opportunity is quality, the device opportunity is mobile conversion, and the next step is focused experimentation.
Channel engagement rate
Device view-to-purchase completion
Protect the acquisition engine, but allocate the next optimization cycle to mobile conversion and product discovery. The largest upside is likely to come from reducing friction, not simply buying more traffic.
06 / Pipeline
Behind the analysis: Python + SQL pipeline
I rebuilt the audit as a repeatable pipeline so every number could be re-run and checked, not just read off dashboards. Python cleans the GA4 data, MySQL runs the summaries, and a validation step compares the results to this case study. The channel, device, and funnel charts on this page are generated by the pipeline.
- 01GA4 data
- 02Python cleaning
- 03MySQL
- 04SQL queries
- 05Validation
- 06Charts
| Check | Case study | Pipeline | Result |
|---|---|---|---|
| Total sessions | 320,579 | 320,579 | PASS |
| Direct sessions | 209,231 | 209,231 | PASS |
| Direct share of sessions | 65.27% | 65.27% | PASS |
| Organic Search sessions | 59,575 | 59,575 | PASS |
| Organic Search engagement rate | 67.73% | 67.73% | PASS |
| Funnel: viewed item | 46,326 | 46,326 | PASS |
| Funnel: added to cart | 11,439 | 11,439 | PASS |
| Funnel: purchased | 2,880 | 2,880 | PASS |
| View-to-purchase | 6.22% | 6.22% | PASS |
| Desktop view-to-purchase | 8.61% | 8.61% | PASS |
| Mobile view-to-purchase | 1.85% | 1.85% | PASS |
| Desktop checkout completion | 55.25% | 55.25% | PASS |
| Mobile checkout completion | 24.14% | 24.14% | PASS |
Sample query
-- Trimmed from the pipeline's device query (full version on GitHub)
WITH funnel_by_device AS (
-- GA4 gives one row per funnel step per device; pivot to one row per device
SELECT
device_category,
MAX(CASE WHEN step_number = 1 THEN active_users END) AS viewed_item,
MAX(CASE WHEN step_number = 3 THEN active_users END) AS began_checkout,
MAX(CASE WHEN step_number = 4 THEN active_users END) AS purchased
FROM funnel
GROUP BY device_category
)
SELECT
device_category,
ROUND(purchased / viewed_item, 4) AS view_to_purchase_rate,
ROUND(purchased / began_checkout, 4) AS checkout_completion_rate
FROM funnel_by_device
ORDER BY view_to_purchase_rate DESC;Trimmed for readability. The full query also adds GA4's Total row as a check. See it on GitHub.
What broke and how I fixed it
- The GA4 demo account blocks exports, so I transcribed three reports by hand and checked all 16 funnel rows against GA4.
- Three sums missed GA4's totals by more than 1% because GA4 estimates sessions and users separately for each row. I kept GA4's numbers and set a rule: overall figures come from GA4 Total rows.
- Validation caught a rounding error in my original case study (8.62% should be 8.61%), so I corrected it.
Built with Claude Code. I defined the checks, reviewed every query, and validated outputs against GA4.
07 / Experiment Roadmap
Three experiments to turn insight into action.
The findings translate into three focused tests that a marketing, UX, and analytics team could launch and measure.
08 / Prioritized Recommendations
Sequence the work by impact and effort.
The sequence starts with the clearest conversion weakness, then moves to changes most likely to reduce decision friction and increase merchandising leverage. Attribution hygiene remains important, but it should not delay higher-impact UX tests.
| Priority | Recommendation | Why now |
|---|---|---|
| 01 | Mobile CTA + checkout | Largest observed performance gap |
| 02 | Shipping transparency | Reduces late-stage uncertainty |
| 03 | Product visibility | Turns proven demand into more exposure |
| 04 | Attribution hygiene | Improves confidence in channel decisions |
09 / Limitations
What this analysis can and cannot claim.
The data was historical or sample ecommerce data. Customer-level qualitative research was not available. Recommended experiments were not implemented. Expected improvements are targets, not achieved results.
These findings are prioritization signals rather than proof of causation. Instrumentation, event definitions, and test baselines should be validated before launch.
Closing point
The strongest opportunity is not simply more traffic. It is a more intentional path from discovery to product confidence to checkout—especially on mobile.