Next stopOverview

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.

DataGA4 demo account, 320K+ sessions, Mar to Jun 2026
ToolsGA4, Python, MySQL, Claude Code
Validation13 of 13 metrics matched GA4
Output3 prioritized experiments (proposed, not run)
320,579Sessions90-day scope
$524.5KItem revenuereported total
6.22%View-to-purchasecustom funnel
1.85%Mobile view-to-purchaseclear weak point

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.

ChallengeDetermine which acquisition channels and products were creating valuable customer activity and identify where customers were leaving the ecommerce funnel.
My roleIndependent marketing analyst responsible for data analysis, funnel evaluation, visualization and growth recommendations.

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.

Traffic volume vs traffic quality
Sessions by channel with engagement rate. Direct brings 65% of sessions but only 23% engage; Organic Search engages 68%.
What it means

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.

The next move is to continue investing in SEO and search-aligned landing pages while auditing UTMs, partner links, email links, and redirects.

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.

New-user volume vs key-event rate
Insight 02 graph comparing Direct vs Organic Search new-user volume and user key-event rate

The evidence supports prioritizing Organic Search landing-page and merchandising support while testing smaller high-intent channels through controlled experiments with defined conversion targets.

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.

Custom purchase funnel
Purchase funnel. Only 6.2% of product viewers purchase; 75.3% leave before adding to cart.

04 / Device Insight

Mobile customers experience the largest conversion gap.

Desktop view-to-purchase8.61%

Desktop checkout completion: 55.25%

Mobile view-to-purchase1.85%

Mobile checkout completion: 24.14%

Desktop vs mobile conversion
Desktop vs mobile conversion. Desktop buys at 8.61%, mobile at 1.85%.

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%.

The device gap warrants focused mobile testing: simplifying forms, improving payment usability, surfacing shipping information earlier, and giving proven converters stronger placement.

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.

Sessions320,579
Overall engagement rate26.9%
Revenue$524,509
Add-to-cart rate24.7%
Checkout completion44.3%
View-to-purchase completion6.22%

Channel engagement rate

Direct
23.46%
Organic Search
67.73%

Device view-to-purchase completion

Desktop
8.61%
Mobile
1.85%
Decision signal

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.

  1. 01GA4 data
  2. 02Python cleaning
  3. 03MySQL
  4. 04SQL queries
  5. 05Validation
  6. 06Charts
37data-quality checks
13 of 13metrics validated
3charts generated
CheckCase studyPipelineResult
Total sessions320,579320,579PASS
Direct sessions209,231209,231PASS
Direct share of sessions65.27%65.27%PASS
Organic Search sessions59,57559,575PASS
Organic Search engagement rate67.73%67.73%PASS
Funnel: viewed item46,32646,326PASS
Funnel: added to cart11,43911,439PASS
Funnel: purchased2,8802,880PASS
View-to-purchase6.22%6.22%PASS
Desktop view-to-purchase8.61%8.61%PASS
Mobile view-to-purchase1.85%1.85%PASS
Desktop checkout completion55.25%55.25%PASS
Mobile checkout completion24.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.

TEST 01Mobile CTA experiment
HypothesisA clearer, persistent mobile CTA will increase mobile add-to-cart rate and improve downstream purchase completion.
Existing frictionMobile view-to-purchase completion is 1.85% versus 8.61% on desktop.
Proposed changeUse a sticky CTA, shorter copy, stronger contrast, and clearer payment reassurance.
Primary KPIMobile add-to-cart rate.
Secondary KPIsCheckout start rate, purchase conversion, CTA interaction rate.
TEST 02Shipping transparency experiment
HypothesisShowing delivery timing and shipping cost earlier will reduce checkout hesitation.
Potential friction to validateThe current data does not show whether delivery timing or shipping cost is causing checkout hesitation.
Proposed changeShow delivery timing and shipping cost on product and cart views.
Primary KPICheckout-start rate.
Secondary KPIsPurchase conversion, cart abandonment, support/contact rate.
TEST 03Product visibility experiment
HypothesisFeaturing high-converting products more prominently will increase revenue per session.
Existing frictionThe most viewed product was not the highest-revenue or highest-converting product.
Proposed changePromote proven converters in merchandising modules, search results, and campaign landing pages.
Primary KPIAdd-to-cart rate on featured products.
Secondary KPIsProduct views, purchase rate, revenue, revenue per session.

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.

High impact / Low effort01 Mobile CTA + checkout02 Shipping transparency
High impact / High effort03 Product visibility
Low impact / Low effortNo immediate priority
Low impact / High effort04 Attribution hygiene
PriorityRecommendationWhy now
01Mobile CTA + checkoutLargest observed performance gap
02Shipping transparencyReduces late-stage uncertainty
03Product visibilityTurns proven demand into more exposure
04Attribution hygieneImproves 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.