E-Commerce Funnel Analysis

Project Overview

Built an end-to-end e-commerce funnel dashboard using GA4 data integrated with SQL in BigQuery. This project visualized the complete customer journey—from initial session to final conversion—using Looker Studio to create an interactive, real-time dashboard for stakeholders.

The analysis identified critical drop-off points in the conversion funnel and provided data-driven recommendations for UX improvements.

Business Challenge

The e-commerce platform was experiencing high cart abandonment rates and unclear visibility into the customer journey. Marketing and product teams needed a comprehensive view of user behavior across all touchpoints to identify friction points and optimize the conversion path.

Solution Approach

Integrated GA4 data with BigQuery for advanced SQL analysis
Designed funnel stages: Session → Product View → Add to Cart → Checkout → Purchase
Built custom SQL queries to calculate drop-off rates at each stage
Created Looker Studio dashboard with drill-down capabilities
Segmented analysis by traffic source, device type, and user demographics
Implemented automated daily data refresh for real-time insights

Critical Insights

Cart Abandonment: 68% of users who added items to cart didn't complete checkout

Mobile Gap: Mobile conversion rate was 40% lower than desktop

Checkout Friction: Users spent average of 4.2 minutes at checkout—2x the industry benchmark

Traffic Source: Organic traffic converted 2.3x better than paid ads

SQL Implementation

Key query example for calculating funnel drop-offs:

WITH funnel_stages AS (
  SELECT 
    user_pseudo_id,
    MAX(CASE WHEN event_name = 'session_start' THEN 1 ELSE 0 END) AS session,
    MAX(CASE WHEN event_name = 'view_item' THEN 1 ELSE 0 END) AS product_view,
    MAX(CASE WHEN event_name = 'add_to_cart' THEN 1 ELSE 0 END) AS add_to_cart,
    MAX(CASE WHEN event_name = 'begin_checkout' THEN 1 ELSE 0 END) AS checkout,
    MAX(CASE WHEN event_name = 'purchase' THEN 1 ELSE 0 END) AS purchase
  FROM `project.analytics.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20250101' AND '20250131'
  GROUP BY user_pseudo_id
)
SELECT 
  SUM(session) AS sessions,
  SUM(product_view) AS product_views,
  SUM(add_to_cart) AS cart_adds,
  SUM(checkout) AS checkouts,
  SUM(purchase) AS purchases,
  ROUND(SUM(product_view) / SUM(session) * 100, 2) AS view_rate,
  ROUND(SUM(add_to_cart) / SUM(product_view) * 100, 2) AS add_rate,
  ROUND(SUM(checkout) / SUM(add_to_cart) * 100, 2) AS checkout_rate,
  ROUND(SUM(purchase) / SUM(checkout) * 100, 2) AS conversion_rate
FROM funnel_stages;

Results & Impact

The dashboard and subsequent optimizations led to significant improvements:

Overall conversion rate increased by 12%
Cart abandonment reduced from 68% to 54%
Mobile checkout completion improved by 28%
Average checkout time reduced to 2.1 minutes
Revenue impact: $450K additional quarterly revenue

UX Recommendations Implemented

Simplified mobile checkout flow from 5 steps to 3
Added guest checkout option (increased conversion by 15%)
Implemented progress bar in checkout process
Added abandoned cart email campaign (recovered 23% of abandoned carts)
Optimized page load speed on product pages (reduced bounce rate by 18%)

Lessons Learned

This project reinforced the importance of combining quantitative data with qualitative insights. While the funnel analysis identified where users dropped off, understanding why required additional user research and A/B testing.

The real-time dashboard became an essential tool for the product team, enabling rapid iteration and data-informed decision-making.