The Problem
This project rebuilds the purchase funnel from raw event logs to show where users drop off and which segments matter most.
The analysis answers three questions: Where does the funnel break? Which channels bring high-intent traffic? How do device and engagement affect conversion?
Google Merchandise Store via BigQuery
The data comes from the public Google Analytics Sample E-commerce dataset in BigQuery. It tracks anonymized user behavior from the real Google Merchandise Store.
The dataset is stored at the hit level: each row is a single action such as a pageview, product click, add-to-cart, or purchase. Those hits roll up into sessions and users. Key fields include:
- Traffic acquisition (channel, source, campaign)
- Device information (desktop, mobile, tablet)
- Navigation behavior (pageviews, time on site)
- Product interactions (impressions, clicks, add-to-cart)
- Transactions and revenue
Because it contains millions of events, BigQuery was used to aggregate the raw data before analysis in Python.
Because the raw data is event-level, the first step was rebuilding session-level behavior:
Where Users Abandon
The biggest drop happens at the top of the funnel: only 14.8% of sessions reach a product page. Most users leave before any real product interaction. After that, the funnel tightens and about 50% of checkout sessions end in a purchase, so checkout is probably not the main issue.
The main lever is not checkout optimization. It is getting more users to reach and engage with product pages.
Engagement Predicts Purchase
More than half of sessions bounce immediately. The pattern is clear: buyers spend ~19× more time on site than bouncers and view more than 26 pages on average.
| Session Type | Sessions | Avg Pageviews | Avg Time on Site |
|---|---|---|---|
| Bounce | ~37,600 | ~1 | minimal |
| Product Viewer | ~6,900 | ~9 | ~340 sec |
| Cart Abandoner | ~3,000 | ~13 | ~613 sec |
| Checkout Abandoner | ~1,100 | ~19 | ~852 sec |
| Purchased | ~1,070 | ~26 | ~1,146 sec |
Pageviews and time on site are strong proxies for purchase intent and could support targeting or personalization triggers.
Desktop Converts 5× More Than Mobile
Desktop converts at 2.05%, while mobile and tablet stay below 0.41%. That gap is too large to explain by intent alone, which points to meaningful mobile friction.
Both convert below 0.41%. They drive plenty of sessions but almost no revenue.
Converts at 2.05%, over 5× higher. Desktop users also move deeper into the funnel much more often.
Not All Traffic Is Equal
Organic Search brings the most traffic, but not the most value. Referral converts at 5.57%, nearly 4× the next best channel and 185× better than Social. Social adds volume with almost no commercial return.
The bounce data tells the same story: Social bounces at 65.1% and barely converts, while Referral has the lowest bounce rate (29.9%) and the highest conversion.
Three Concrete Opportunities
Increase product discovery at the top of the funnel
With 85% of sessions never reaching a product page, the biggest opportunity is better product discovery through landing pages, navigation, or personalized entry points.
Fix mobile conversion performance
The 5× desktop-mobile gap warrants a focused UX audit. Simplified flows, faster load times, and mobile-specific A/B tests are the top levers.
Reallocate acquisition budget toward high-intent channels
Shifting budget from Social (0.03% conversion, 65% bounce) toward Referral and Paid Search could lift conversion without changing the product.
Tools Used
Google BigQuery handled querying at scale. The analysis was completed in Python with Pandas and NumPy, and visualized with Matplotlib.