Events Processed
18.4M
6-month analysis window
Data analytics · BigQuery · AI · experimentation
AI-Assisted E-commerce
Funnel Analytics
How fragmented behavioral and commercial data became one decision system — revealing a hidden delivery-information barrier, validating the fix through an A/B test and turning the winner into measurable revenue impact.
Decision intelligence pipeline validated workflow
GA4 RAWevents + items
HOTJARfeedback + notes
ORDERSrevenue + refunds
CATALOGstock + delivery
↓
WarehouseBigQueryIdentity stitching, event deduplication, order reconciliation and daily scheduled models.
fct_sessionsession × date
fct_ordertransaction
fct_experimentuser × exposure
dim_product_dailySKU × date
↓
ReportFunnel dashboardOne KPI definition and segment drilldowns.
AI-assistedSignal synthesisAnonymized text clustering with analyst validation.
Causal proofA/B experimentExposure-level readout and business guardrails.
Detected frictiondelivery timing shown too late
Test result+12.8% purchase conversion
Rollout result+8.6% revenue / session
Order Reconciliation
98.7%
GA4 ↔ backend match
A/B Test Purchase CR
+12.8%
p < .001
Revenue per Session
+8.6%
6-week rollout readout
Decision-level frameworkReliable data first.
Reliable data first.
Commercial action last.
01
Unify
Connect behavior, orders, products, feedback and test exposure.
02
Diagnose
Find the segment, step and mechanism behind lost revenue.
03
Validate
Separate a plausible pattern from a causal experiment result.
04
Operationalize
Roll out the winner with tracking, owners and guardrails.
The Business Problem
Traffic and add-to-cart volume were growing, but purchase conversion and revenue did not scale with them. GA4 showed where users exited, the order system showed what was actually paid and refunded, and Hotjar showed visible friction — but none of those sources could answer the commercial question together.
The decision: identify one fix capable of increasing completed orders without using a discount, lowering AOV or creating more cancellations.
End-to-End Analysis
- Audited GA4 events, ecommerce items and transaction IDs
- Matched analytics purchases to paid backend orders
- Joined product stock and delivery promise by SKU and day
- Modeled session, funnel, order and experiment grains in SQL
- Built a decision dashboard with segment drilldowns
- Used AI to cluster anonymized feedback and analyst notes
- Converted one evidence pattern into a testable hypothesis
- Read the A/B test, rolled out the winner and monitored impact
Data & Tools
GA4 / GTM
behavioral data
behavioral data
BigQuery
& advanced SQL
& advanced SQL
Hotjar
feedback & UX
feedback & UX
Looker Studio
decision report
decision report
Vertex AI
theme clustering
theme clustering
Python
statistics & QA
statistics & QA
Order backend
revenue & refunds
revenue & refunds
VWO
experiment data
experiment data
Portfolio-safe data. The retailer, markets and exact commercial totals are anonymized. Volumes and financial values are normalized while the data model, analysis logic, experiment design and relative results preserve the original decision-making workflow.
01 · Measurement foundation
The first deliverable was not a dashboard. It was a trustworthy analytical grain.
Before analyzing drop-off, I made the data answer the same question at session, product, order and experiment levels. A purchase in GA4 was not treated as revenue until its transaction ID matched the backend order and final status.
GA4 raw export
Events, session parameters, ecommerce items, source / medium and device context.
event + itemOrder backend
Paid status, gross and net revenue, refunds, cancellations and reason codes.
transactionProduct feed
SKU category, stock state, delivery range, price and promotion history by date.
SKU × dayHotjar evidence
Anonymized feedback text plus structured notes from a sampled recording review.
response / noteExperiment log
Stable user assignment, first exposure, variant, eligibility and conversion window.
user × testBigQuery analytical modelScheduled daily
one KPI definition
one KPI definition
Raw
ga4_events_*orders_rawproducts_dailyvoc_raw
Staging
stg_eventsstg_itemsstg_paid_ordersstg_exposure
Facts
fct_sessionfct_funnel_stepfct_orderfct_experiment
Mart
mart_funnel_dailymart_sku_frictionmart_test_readout
Data quality gates
Transaction reconciliation98.7%
Duplicate purchase events removed0.6%
Daily model completion within SLA99.4%
Alerts stopped dashboard refresh when transaction match, late-arriving data or experiment assignment checks breached tolerance.
-- one row per eligible session with product and order context
WITH sessions AS (
SELECT
user_pseudo_id,
(SELECT value.int_value FROM UNNEST(event_params)
WHERE key = 'ga_session_id') AS session_id,
device.category AS device,
MAX(event_name = 'begin_checkout') AS began_checkout,
MAX(event_name = 'purchase') AS ga_purchase
FROM analytics.events_*
GROUP BY 1,2,3
)
SELECT s.*, o.net_revenue, p.delivery_days,
e.variant
FROM sessions s
LEFT JOIN paid_orders o USING (transaction_id)
LEFT JOIN product_daily p USING (sku, event_date)
LEFT JOIN experiment_exposure e USING (user_pseudo_id)
What the model prevented
- ✓Double-counting repeated purchase events after page reload
- ✓Treating cancelled or refunded transactions as final revenue
- ✓Joining a current stock state to a historical user session
- ✓Counting users who converted before their first test exposure
- ✓Mixing user-level randomization with session-level denominators
E-commerce decision reportLooker Studio
daily refresh
daily refresh
Date · Last 6 monthsDevice · MobileMarket · AllCategory · AllStock · All
Sessions742K+11.2% traffic
Add-to-cart9.8%+0.4 pp
Purchase CR2.64%−0.21 pp
Revenue / session€3.84−6.1%
Funnel completion by stepDesktop vs mobile
Friction matrixPurchase CR
Desktop
Mobile
Tablet
0–3 days
3.21%
2.71%
2.66%
4–7 days
2.57%
2.04%
2.18%
8+ days
1.64%
1.14%
1.28%
02 · AI-assisted diagnosis
AI accelerated evidence synthesis. It did not make the business decision.
The model clustered anonymized Hotjar feedback and structured recording-review notes into recurring themes. SQL then tested whether those themes aligned with measurable funnel behavior. Only patterns supported by both qualitative and behavioral evidence entered prioritization.
✦
1,710 qualitative signals turned into an auditable issue taxonomy
1,284 feedback responses and 426 analyst-written recording notes were stripped of direct identifiers before processing. AI proposed labels; the analyst validated the taxonomy, recoded edge cases and connected the themes to BigQuery segments.
01 · Prepare
Anonymize & structure
Remove personal data, normalize language and retain source IDs for QA.
02 · Cluster
Generate themes
Group semantically similar comments and return evidence snippets.
03 · Validate
Human-coded sample
Compare AI labels with 200 analyst labels; merge or reject weak themes.
04 · Quantify
Test in SQL
Measure each theme against device, delivery, stock and funnel outcomes.
Responsible AI boundary: AI never received names, emails, addresses or payment data; it did not calculate KPIs, declare statistical significance or choose the winning variant. Those decisions remained reproducible in SQL and Python.
01Primary mechanism
Delivery timing appeared after commitment, not before it.
Mobile users could add an item to cart without seeing an arrival range. The delivery estimate became visible only inside checkout, where the largest incremental exit occurred.
43.6%mobile exits after shipping step
38%of validated feedback themes referenced delivery certainty
show postcode-aware delivery range beside the PDP CTA and repeat it in cart.
02Media efficiency
High-intent traffic was landing on slow-delivery inventory.
Paid campaigns optimized for product demand, while product availability changed daily. SKUs with an 8+ day promise received disproportionate traffic and converted at less than half the rate of fast-delivery items.
29%of paid PDP entrances
1.14%mobile purchase CR on 8+ day delivery
export a daily delivery / inventory segment for bidding and merchandising rules.
03UX evidence
The shipping panel attracted repeated taps and backtracking.
In a stratified sample of 186 checkout recordings, 69 sessions included repeated opening, closing or revisiting of shipping information before exit. The pattern was strongest on smaller mobile screens.
69 / 186reviewed sessions showed backtracking
2.3×higher exit rate with repeated shipping interaction
remove the need to discover core delivery information inside a collapsed element.
04Segment opportunity
Returning cart users responded to certainty, not urgency.
Returning mobile visitors with a saved cart converted materially better when their selected item already had a visible delivery estimate. Countdown messaging did not show the same pattern.
2.1×conversion when delivery was known
+4.8%revenue opportunity in eligible sessions
prioritize delivery transparency over another promotional urgency treatment.
Evidence-to-decision matrixCorrelation used for prioritization
not claimed as causation
not claimed as causation
| Segment | Sessions | Checkout start | Purchase CR | Revenue / session | Decision |
|---|---|---|---|---|---|
| Mobile · delivery 0–3 days | 214K | 6.4% | 2.71% | €4.16 | Protect as benchmark |
| Mobile · delivery 4–7 days | 128K | 6.1% | 2.04% | €3.22 | Set expectation earlier |
| Mobile · delivery 8+ days | 83K | 5.9% | 1.14% | €1.86 | Test transparency + adjust traffic |
| Returning mobile · promise seen | 46K | 8.2% | 3.46% | €5.38 | Use as hypothesis evidence |
03 · Causal validation
One clear hypothesis. One primary metric. No discount.
The analysis showed a strong pattern, but it could not prove that moving delivery information would cause more purchases. A user-level A/B test isolated the treatment while monitoring revenue, cancellations and site performance.
Earlier delivery certainty will increase completed purchases.
Users need to know whether an item can arrive in time before they invest in checkout. The change should improve qualified checkout progression rather than simply inflate clicks.
If a postcode-aware delivery range and stock state are visible beside the PDP CTA and repeated in cart, purchase conversion will increase without reducing AOV or increasing cancellations.
Control · A50%
Late disclosure
- Generic “In stock” label on PDP
- Delivery range first shown in checkout
- Collapsed shipping information in cart
- No change to price or promotion
Variant · BWinner
Visible delivery promise
- Postcode-aware arrival range beside CTA
- Stock state tied to the selected SKU
- Promise repeated above cart checkout CTA
- Fallback copy when the API was unavailable
Experiment specification & resultUser-level randomization
intention-to-treat
intention-to-treat
Duration21 days
Eligible users126,482
Primary metricPurchase / exposed user
Decision rule95% confidence
SRM checkPassed · p=.72
| Variant | Users | Purchases | Purchase CR | Relative lift | Revenue / user |
|---|---|---|---|---|---|
| Control A | 63,208 | 1,618 | 2.56% | — | €3.71 |
| Variant B | 63,274 | 1,827 | 2.89% | +12.8% | €4.06 |
+12.8%purchase conversion
variant vs control
variant vs control
The variant won on the business outcome, not a proxy click.
The absolute lift was +0.33 percentage points. The relative-risk 95% confidence interval was +5.6% to +20.5%, with p<.001. Checkout-start rate remained stable, suggesting the treatment improved decision quality rather than creating more low-intent starts.
Checkout start−1.0% · NS
Revenue / user+9.4%
AOV−2.9% · NS
Page latency+18 ms
04 · Implementation & impact
The winning variant became a monitored product capability.
The rollout included the delivery service, analytics events, fallback behavior, merchandising rules and a permanent KPI view. This kept the result from disappearing after the experiment ended.
01
Product implementation
Released delivery promise on eligible PDPs and cart with SKU, postcode and market logic.
02
Data contract
Documented event names, parameters, experiment ID, fallback state and QA ownership.
03
Commercial rule
Sent slow-delivery inventory to merchandising and paid-media segments each morning.
04
Six-week monitoring
Tracked conversion, revenue, cancellations, delivery errors and latency after full release.
Permanent measurement layer
delivery_promise_viewpostcode_submitdelivery_statepromise_errorexperiment_idvariant_idtransaction_idcancellation_reason
Every event was validated in DebugView, BigQuery intraday export and the daily mart before the business dashboard was updated.
2.72% → 2.99% adjusted post-rollout rate.
Six-week matched readoutCommercial gain persisted after test traffic ended.
Adjusted for channel and category mixFewer sessions first discovered delivery timing in checkout.
Eligible mobile sessionsFewer post-order cancellations citing arrival expectations.
Directional operational resultHow impact was interpretedCausal evidence separated
from operational validation
from operational validation
01 · A/B test
Causal conversion result
Randomized exposure supported the claim that the delivery treatment increased purchase conversion for eligible users.
02 · Rollout analysis
Sustainability check
A matched pre/post model controlled for device, market, channel, category and promotion mix; it confirmed persistence, not a second causal estimate.
03 · Guardrails
No hidden commercial cost
AOV, page performance and refunds remained within tolerance; delivery-related cancellations moved in the expected direction.
From disconnected signals to a repeatable growth loop
GA4, Hotjar, orders, product state and experiment exposure unified in BigQuery
→AI-assisted themes and SQL segmentation produced one testable mechanism
→A/B proof, tracked rollout and permanent commercial monitoring
Results apply to eligible products, markets and users in the study window. Exact absolute totals are normalized for confidentiality. AI outputs were used for synthesis only and remained subject to analyst review.