E-Commerce Traffic & Conversion Analysis Using SQL

SQL MySQL Workbench E-Commerce Analytics Project Walkthrough A/B Testing

In this project I show exactly how to analyze e-commerce data using SQL — covering traffic trends, conversion rates, A/B test results, and funnel analysis using a real MySQL database.

Dataset Maven Fuzzy Factory (E-Commerce MySQL Database)

Tool MySQL Workbench

Techniques JOINs, CTEs, CASE WHEN, Funnel Analysis, A/B Testing

GitHub Repository View on GitHub →

The Business Context

Maven Fuzzy Factory is a growing e-commerce company that sells stuffed animal products online. The business runs paid search campaigns on Google (Gsearch) and Bing (Bsearch) and is actively optimizing its website to convert more visitors into paying customers.

In this project, I acted as the in-house data analyst. The CEO and Marketing Director sent me 8 business questions that needed answers from the database. My job was to write the SQL, pull the data, and translate the findings into business insights they could actually use.

This is exactly the kind of work a data analyst does day-to-day — you get a business question, you figure out which tables to query, you write the SQL, and you tell the story the data is revealing.

The Database

The mavenfuzzyfactory database contains transactional data from the company’s website and order system. The key tables I worked with:

website_sessions — every user session with UTM tracking parameters, device type, and referrer data
website_pageviews — every page viewed within each session, with timestamps
orders — completed purchases with revenue and product information
order_items — individual product line items within each order
order_item_refunds — refund records linked back to order items

Question 1 — Monthly Gsearch Sessions and Orders

The Business Question

Can you pull overall session and order volume for Gsearch, trended by month, so we can see if the paid campaigns are growing?

The SQL

SELECT
    YEAR(ws.created_at)  AS year,
    MONTH(ws.created_at) AS month,
    COUNT(DISTINCT ws.website_session_id)                              AS sessions,
    COUNT(DISTINCT o.order_id)                                         AS orders,
    COUNT(DISTINCT o.order_id) / COUNT(DISTINCT ws.website_session_id) * 100 AS cvr
FROM website_sessions ws
    LEFT JOIN orders o USING (website_session_id)
WHERE ws.utm_source = 'gsearch'
GROUP BY 1, 2;

📊 Finding

Gsearch sessions and orders grew steadily throughout 2012. Conversion rates improved in later months, confirming that ongoing bid and landing page optimizations were having a measurable effect.

Question 2 — Brand vs Nonbrand Campaign Split

The Business Question

Could you pull monthly Gsearch sessions and orders, splitting out nonbrand and brand campaigns separately?

The SQL

SELECT
    YEAR(ws.created_at)  AS year,
    MONTH(ws.created_at) AS month,
    COUNT(DISTINCT CASE WHEN utm_campaign = 'nonbrand'
        THEN ws.website_session_id END) AS nonbrand_sessions,
    COUNT(DISTINCT CASE WHEN utm_campaign = 'nonbrand'
        THEN o.order_id END)            AS nonbrand_orders,
    COUNT(DISTINCT CASE WHEN utm_campaign = 'brand'
        THEN ws.website_session_id END) AS brand_sessions,
    COUNT(DISTINCT CASE WHEN utm_campaign = 'brand'
        THEN o.order_id END)            AS brand_orders
FROM website_sessions ws
    LEFT JOIN orders o USING (website_session_id)
WHERE ws.utm_source = 'gsearch'
GROUP BY 1, 2;

📊 Finding

Nonbrand campaigns drive the vast majority of traffic. Brand sessions are small but growing — a positive signal that the business is building real brand recognition beyond paid acquisition.

Question 3 — Sessions and Orders by Device Type

The Business Question

Could you pull monthly sessions and orders for Gsearch nonbrand, split by device type?

The SQL

SELECT
    YEAR(ws.created_at)  AS year,
    MONTH(ws.created_at) AS month,
    COUNT(DISTINCT CASE WHEN device_type = 'desktop'
        THEN ws.website_session_id END) AS desktop_sessions,
    COUNT(DISTINCT CASE WHEN device_type = 'desktop'
        THEN o.order_id END)            AS desktop_orders,
    COUNT(DISTINCT CASE WHEN device_type = 'mobile'
        THEN ws.website_session_id END) AS mobile_sessions,
    COUNT(DISTINCT CASE WHEN device_type = 'mobile'
        THEN o.order_id END)            AS mobile_orders
FROM website_sessions ws
    LEFT JOIN orders o USING (website_session_id)
WHERE ws.utm_source = 'gsearch'
  AND ws.utm_campaign = 'nonbrand'
GROUP BY 1, 2;

📊 Finding

Desktop converts at significantly higher rates than mobile every month — pointing to a clear mobile UX problem that deserves attention and investment.

Question 4 — Channel Mix Analysis

The Business Question

How does Gsearch volume compare to other channels? How dependent are we on paid search?

The SQL

SELECT
    YEAR(created_at)  AS year,
    MONTH(created_at) AS month,
    COUNT(DISTINCT CASE WHEN utm_source = 'gsearch'
        THEN website_session_id END) AS gsearch_sessions,
    COUNT(DISTINCT CASE WHEN utm_source = 'bsearch'
        THEN website_session_id END) AS bsearch_sessions,
    COUNT(DISTINCT CASE WHEN utm_source IS NULL
        AND http_referer IS NOT NULL
        THEN website_session_id END) AS organic_sessions,
    COUNT(DISTINCT CASE WHEN utm_source IS NULL
        AND http_referer IS NULL
        THEN website_session_id END) AS direct_sessions
FROM website_sessions
WHERE created_at < '2012-11-27'
GROUP BY 1, 2;

📊 Finding

Gsearch dominates all channels. Organic and direct sessions are growing steadily — early signs of brand equity that doesn’t require paid spend to sustain.

Question 5 — Overall Conversion Rate by Month

The Business Question

Is our overall conversion rate improving over time, across all traffic?

SELECT
    YEAR(ws.created_at) AS year, MONTH(ws.created_at) AS month,
    COUNT(DISTINCT ws.website_session_id)                              AS sessions,
    COUNT(DISTINCT o.order_id)                                         AS orders,
    COUNT(DISTINCT o.order_id) / COUNT(DISTINCT ws.website_session_id) * 100 AS cvr
FROM website_sessions ws
    LEFT JOIN orders o USING (website_session_id)
WHERE ws.created_at < '2012-11-27'
GROUP BY 1, 2;

📊 Finding

Overall conversion rate improved steadily throughout 2012, reflecting the cumulative impact of landing page tests, bid optimizations, and funnel improvements.

Question 6 — Revenue Impact of the Lander-1 A/B Test

The Business Question

We ran an A/B test replacing /home with a custom /lander-1 page for Gsearch nonbrand traffic. Can you estimate the revenue impact?

The SQL

WITH first_pv AS (
    SELECT wp.website_session_id, MIN(wp.website_pageview_id) AS min_pv_id
    FROM website_pageviews wp
        INNER JOIN website_sessions ws USING (website_session_id)
    WHERE ws.created_at < '2012-07-28'
      AND wp.website_pageview_id >= 23504
      AND utm_source = 'gsearch' AND utm_campaign = 'nonbrand'
    GROUP BY 1
),
session_lp AS (
    SELECT fps.website_session_id, wp.pageview_url AS landing_page
    FROM first_pv fps
        LEFT JOIN website_pageviews wp ON wp.website_pageview_id = fps.min_pv_id
    WHERE wp.pageview_url IN ('/home', '/lander-1')
)
SELECT landing_page,
    COUNT(DISTINCT website_session_id) AS sessions,
    COUNT(DISTINCT order_id)            AS orders,
    COUNT(DISTINCT order_id) / COUNT(DISTINCT website_session_id) AS cvr
FROM session_orders GROUP BY 1;

Landing Page	Sessions	Orders	CVR
/home	~2,000	~58	~2.9%
/lander-1	~2,000	~75	~3.8%
Lift	—	—	+0.87 pp

💰 Revenue Impact

Lander-1 outperformed the homepage by 0.87 percentage points — translating to approximately 50 extra orders per month at zero additional ad spend.

Question 7 — Full Conversion Funnel Analysis

The Business Question

Build a full conversion funnel from each landing page through to order, showing click-through rates at every step.

The SQL

-- Step 1: Flag each pageview with binary indicators
WITH pageview_flags AS (
    SELECT
        ws.website_session_id,
        wp.pageview_url,
        CASE WHEN wp.pageview_url = '/home'
            THEN 1 ELSE 0 END AS homepage,
        CASE WHEN wp.pageview_url = '/lander-1'
            THEN 1 ELSE 0 END AS custom_lander,
        CASE WHEN wp.pageview_url = '/products'
            THEN 1 ELSE 0 END AS products_page,
        CASE WHEN wp.pageview_url = '/the-original-mr-fuzzy'
            THEN 1 ELSE 0 END AS mrfuzzy_page,
        CASE WHEN wp.pageview_url = '/cart'
            THEN 1 ELSE 0 END AS cart_page,
        CASE WHEN wp.pageview_url = '/shipping'
            THEN 1 ELSE 0 END AS shipping_page,
        CASE WHEN wp.pageview_url = '/billing'
            THEN 1 ELSE 0 END AS billing_page,
        CASE WHEN wp.pageview_url = '/thank-you-for-your-order'
            THEN 1 ELSE 0 END AS thankyou_page
    FROM website_sessions ws
        LEFT JOIN website_pageviews wp USING (website_session_id)
    WHERE ws.utm_source = 'gsearch'
      AND ws.utm_campaign = 'nonbrand'
      AND ws.created_at BETWEEN '2012-06-19' AND '2012-07-28'
),
-- Step 2: Collapse to session level using MAX()
session_flags AS (
    SELECT
        website_session_id,
        MAX(homepage)      AS saw_homepage,
        MAX(custom_lander)  AS saw_lander,
        MAX(products_page)  AS saw_products,
        MAX(mrfuzzy_page)   AS saw_mrfuzzy,
        MAX(cart_page)      AS saw_cart,
        MAX(shipping_page)  AS saw_shipping,
        MAX(billing_page)   AS saw_billing,
        MAX(thankyou_page)  AS saw_thankyou
    FROM pageview_flags
    GROUP BY 1
)
-- Step 3: Calculate click-through rates at each funnel step
SELECT
    COUNT(DISTINCT website_session_id)                       AS sessions,
    COUNT(DISTINCT CASE WHEN saw_products = 1
        THEN website_session_id END)                          AS to_products,
    COUNT(DISTINCT CASE WHEN saw_mrfuzzy = 1
        THEN website_session_id END)                          AS to_mrfuzzy,
    COUNT(DISTINCT CASE WHEN saw_cart = 1
        THEN website_session_id END)                          AS to_cart,
    COUNT(DISTINCT CASE WHEN saw_shipping = 1
        THEN website_session_id END)                          AS to_shipping,
    COUNT(DISTINCT CASE WHEN saw_billing = 1
        THEN website_session_id END)                          AS to_billing,
    COUNT(DISTINCT CASE WHEN saw_thankyou = 1
        THEN website_session_id END)                          AS to_thankyou
FROM session_flags
GROUP BY saw_homepage, saw_lander;

📊 Finding

The biggest drop-off for both segments occurred between the product page and the cart — the single clearest opportunity for future optimization.

Question 8 — Billing Page A/B Test Revenue Impact

The Business Question

We tested a new billing page design (/billing-2). What was the revenue per session for each version?

The SQL

SELECT
    seen_billing_page,
    COUNT(DISTINCT website_session_id)                          AS sessions,
    ROUND(SUM(price_usd) / COUNT(DISTINCT website_session_id), 2) AS rev_per_session
FROM (
    SELECT wp.website_session_id, wp.pageview_url AS seen_billing_page,
           o.order_id, o.price_usd
    FROM website_pageviews wp
        LEFT JOIN orders o USING (website_session_id)
    WHERE wp.created_at BETWEEN '2012-09-10' AND '2012-11-10'
      AND wp.pageview_url IN ('/billing', '/billing-2')
) AS billing_data
GROUP BY 1;

Billing Page	Sessions	Revenue / Session
/billing	~657	$22.83
/billing-2	~654	$31.34
Lift	—	+$8.51 / session

💰 Revenue Impact

The new billing page generated $8.51 more per session. Over 1,193 billing sessions in the past month = approximately $10,153 in incremental monthly revenue from a single page redesign.

Key Findings

Gsearch Is the Backbone — and a Risk

Gsearch nonbrand drives the majority of sessions and orders. This dependence on a single paid channel is both a strength and a vulnerability. Diversification into SEO and content marketing should be a strategic priority.

Desktop Converts 4–5× Better Than Mobile

Mobile users are visiting but not buying. A mobile-specific landing page or streamlined checkout could unlock significant revenue without any increase in ad spend.

A/B Testing Is Generating Real Money

The lander-1 test = ~50 extra orders/month. The billing-2 test = ~$10,153 extra monthly revenue. A culture of continuous testing is clearly working and should expand to every high-traffic page.

Brand and Organic Channels Are Growing

Slow but steady growth of brand, direct, and organic sessions signals the business is building equity beyond paid acquisition — future growth becomes progressively less dependent on ad spend.

What I Would Do Differently

Add visualizations — A Power BI dashboard would make trends far more presentable to non-technical stakeholders.
Deeper mobile analysis — Pinpoint exactly which funnel step loses mobile users to know where to fix it.
Automate monthly reporting — Build stored procedures so queries don’t need manual date changes each month.
Statistical significance testing — Add chi-square tests to the A/B results to make findings more rigorous before recommending permanent changes.

The Business Context

The Database

Question 1 — Monthly Gsearch Sessions and Orders

The Business Question

The SQL

Question 2 — Brand vs Nonbrand Campaign Split

The Business Question

The SQL

Question 3 — Sessions and Orders by Device Type

The Business Question

The SQL

Question 4 — Channel Mix Analysis

The Business Question

The SQL

Question 5 — Overall Conversion Rate by Month

The Business Question

Question 6 — Revenue Impact of the Lander-1 A/B Test

The Business Question

The SQL

Question 7 — Full Conversion Funnel Analysis

The Business Question

The SQL

Question 8 — Billing Page A/B Test Revenue Impact

The Business Question

The SQL

Key Findings

Gsearch Is the Backbone — and a Risk

Desktop Converts 4–5× Better Than Mobile

A/B Testing Is Generating Real Money

Brand and Organic Channels Are Growing

What I Would Do Differently

Leave a Comment Cancel Reply