SQL for Marketing Performance & Conversion Analysis

1.	Monthly Conversion Rates Across All Sessions


Goal: To calculate the monthly conversion rate of all website sessions into orders before January 1, 2013, 
providing an overview of session-to-order performance trends.

SELECT
	YEAR(website_sessions.created_at) AS yr, 
    MONTH(website_sessions.created_at) AS mo, 
    COUNT(DISTINCT website_sessions.website_session_id) AS sessions, 
    COUNT(DISTINCT orders.order_id) AS orders, 
    COUNT(DISTINCT orders.order_id)/COUNT(DISTINCT website_sessions.website_session_id) AS conversion_rate    
FROM website_sessions
	LEFT JOIN orders 
		ON orders.website_session_id = website_sessions.website_session_id
WHERE website_sessions.created_at < '2013-01-01'
GROUP BY 1,2;

Table showing months of 2012 with number of sessions, orders, and conversion rates.

2. Monthly Conversion Rates for Google Search Sessions


Goal: To calculate the monthly conversion rate of website sessions originating from Google search 
(utm_source = 'gsearch') before January 1, 2013. This analysis provides insights into how effectively
 sessions from Google search are converting into orders.

SELECT
	YEAR(website_sessions.created_at) AS yr, 
    MONTH(website_sessions.created_at) AS mo, 
    COUNT(DISTINCT website_sessions.website_session_id) AS sessions, 
    COUNT(DISTINCT orders.order_id) AS orders, 
    COUNT(DISTINCT orders.order_id)/COUNT(DISTINCT website_sessions.website_session_id) AS conv_rate
FROM website_sessions
	LEFT JOIN orders 
		ON orders.website_session_id = website_sessions.website_session_id
WHERE website_sessions.created_at < '2013-01-01'
	AND website_sessions.utm_source = 'gsearch'
GROUP BY 1,2;

Kristin Dain's SQL analysis of monthly ecommerce performance, highlighting website traffic, customer orders, and conversion rates to identify business trends.

3. Brand vs Non-Brand Campaign Performance


Goal: To compare the monthly performance of brand and non-brand campaigns 
for website sessions and orders originating from 
Google search (utm_source = 'gsearch') before January 1, 2013.

SELECT
	YEAR(website_sessions.created_at) AS yr, 
    MONTH(website_sessions.created_at) AS mo, 
    COUNT(DISTINCT CASE WHEN utm_campaign = 'nonbrand' THEN website_sessions.website_session_id ELSE NULL END) AS nonbrand_sessions, 
    COUNT(DISTINCT CASE WHEN utm_campaign = 'nonbrand' THEN orders.order_id ELSE NULL END) AS nonbrand_orders,
    COUNT(DISTINCT CASE WHEN utm_campaign = 'brand' THEN website_sessions.website_session_id ELSE NULL END) AS brand_sessions, 
    COUNT(DISTINCT CASE WHEN utm_campaign = 'brand' THEN orders.order_id ELSE NULL END) AS brand_orders
FROM website_sessions
	LEFT JOIN orders 
		ON orders.website_session_id = website_sessions.website_session_id
WHERE website_sessions.created_at < '2013-01-01'
	AND website_sessions.utm_source = 'gsearch'
GROUP BY 1,2;

SQL data analysis by Kristin Dain comparing branded and non-branded search traffic, including monthly sessions and orders, to evaluate search marketing performance.

4. Device Type Performance for Non-Brand Campaigns


Goal: To evaluate the monthly performance of desktop and mobile device types for non-brand campaign sessions and orders originating from 
Google search (utm_source = 'gsearch') before January 1, 2013.

SELECT
	YEAR(website_sessions.created_at) AS yr, 
    MONTH(website_sessions.created_at) AS mo, 
    COUNT(DISTINCT CASE WHEN device_type = 'desktop' THEN website_sessions.website_session_id ELSE NULL END) AS desktop_sessions, 
    COUNT(DISTINCT CASE WHEN device_type = 'desktop' THEN orders.order_id ELSE NULL END) AS desktop_orders,
    COUNT(DISTINCT CASE WHEN device_type = 'mobile' THEN website_sessions.website_session_id ELSE NULL END) AS mobile_sessions, 
    COUNT(DISTINCT CASE WHEN device_type = 'mobile' THEN orders.order_id ELSE NULL END) AS mobile_orders
FROM website_sessions
	LEFT JOIN orders 
		ON orders.website_session_id = website_sessions.website_session_id
WHERE website_sessions.created_at < '2013-01-01'
	AND website_sessions.utm_source = 'gsearch'
    AND website_sessions.utm_campaign = 'nonbrand'
GROUP BY 1,2;

5.	Traffic Sources and Session Types


Goal: To distinguish between different traffic sources 
(gsearch, bsearch, organic search, and direct type-in sessions) and 
evaluate their monthly performance for sessions before January 1, 2013.


/*
Step 1: Identify Traffic Sources
Description: This step extracts distinct traffic-related attributes 
(utm_source, utm_campaign, and http_referer) from the website_sessions table. It provides an 
overview of the available traffic sources to better understand the session data.
*/ 

SELECT DISTINCT 
	utm_source,
    utm_campaign, 
    http_referer
FROM website_sessions
WHERE website_sessions.created_at < '2013-01-01';

/*
Step 2: Monthly Traffic Source Performance Analysis
Description: This step calculates the monthly session counts for different traffic sources 
(gsearch, bsearch, organic search, and direct type-in) by grouping the sessions by year and month.
*/ 
SELECT
	YEAR(website_sessions.created_at) AS yr, 
    MONTH(website_sessions.created_at) AS mo, 
    COUNT(DISTINCT CASE WHEN utm_source = 'gsearch' THEN website_sessions.website_session_id ELSE NULL END) AS gsearch_paid_sessions,
    COUNT(DISTINCT CASE WHEN utm_source = 'bsearch' THEN website_sessions.website_session_id ELSE NULL END) AS bsearch_paid_sessions,
    COUNT(DISTINCT CASE WHEN utm_source IS NULL AND http_referer IS NOT NULL THEN website_sessions.website_session_id ELSE NULL END) AS organic_search_sessions,
    COUNT(DISTINCT CASE WHEN utm_source IS NULL AND http_referer IS NULL THEN website_sessions.website_session_id ELSE NULL END) AS direct_type_in_sessions
FROM website_sessions
	LEFT JOIN orders 
		ON orders.website_session_id = website_sessions.website_session_id
WHERE website_sessions.created_at < '2013-01-01'
GROUP BY 1,2;

STEP 1:

Kristin Dain's SQL analysis comparing desktop and mobile website sessions and ecommerce orders to evaluate customer behavior and performance across devices.

STEP 2:

SQL marketing data analysis by Kristin Dain examining website traffic sources, campaign information, and referral data to support marketing attribution and reporting.

6. Estimating Incremental Orders from Nonbrand GSearch Lander Test


Goal: Calculate the incremental orders generated by the gsearch nonbrand lander test. 
This involves analyzing the difference in conversion rates between `/home` and 
`/lander-1` landing pages during the test period. The analysis includes sessions and 
orders since the test to estimate the overall impact.

USE [DATABASE NAME];

-- Step 1: Identify the first pageview ID for the test landing page `/lander-1`
SELECT
	MIN(website_pageview_id) AS first_test_pv
FROM website_pageviews
WHERE pageview_url = '/lander-1';

-- Step 2: Create a temporary table to store the first pageview for each session during the test period
CREATE TEMPORARY TABLE first_test_pageviews
SELECT
	website_pageviews.website_session_id, 
    MIN(website_pageviews.website_pageview_id) AS min_pageview_id
FROM website_pageviews 
	INNER JOIN website_sessions 
		ON website_sessions.website_session_id = website_pageviews.website_session_id
		AND website_sessions.created_at < '2012-07-28' -- Test period end date (assignment-defined)
		AND website_pageviews.website_pageview_id >= 23504 -- First pageview ID for the test
        AND utm_source = 'gsearch' -- Traffic source is Google Search
        AND utm_campaign = 'nonbrand' -- Campaign is nonbrand
GROUP BY 
	website_pageviews.website_session_id;

-- Step 3: Add the landing page to each session, filtering only for `/home` and `/lander-1`
CREATE TEMPORARY TABLE nonbrand_test_sessions_w_landing_pages
SELECT 
	first_test_pageviews.website_session_id, 
    website_pageviews.pageview_url AS landing_page
FROM first_test_pageviews
	LEFT JOIN website_pageviews 
		ON website_pageviews.website_pageview_id = first_test_pageviews.min_pageview_id
WHERE website_pageviews.pageview_url IN ('/home', '/lander-1'); 

-- Test query: Verify contents of the temporary table
SELECT * FROM nonbrand_test_sessions_w_landing_pages;

-- Step 4: Create a temporary table to link orders to sessions and landing pages
CREATE TEMPORARY TABLE nonbrand_test_sessions_w_orders
SELECT
	nonbrand_test_sessions_w_landing_pages.website_session_id, 
    nonbrand_test_sessions_w_landing_pages.landing_page, 
    orders.order_id AS order_id
FROM nonbrand_test_sessions_w_landing_pages
LEFT JOIN orders 
	ON orders.website_session_id = nonbrand_test_sessions_w_landing_pages.website_session_id;

-- Test query: Verify contents of the orders temporary table
SELECT * FROM nonbrand_test_sessions_w_orders;

-- Step 5: Calculate conversion rates for `/home` and `/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 conv_rate
FROM nonbrand_test_sessions_w_orders
GROUP BY 1; 

-- Conversion rate results:
-- `/home`: 0.0318 (3.18%)
-- `/lander-1`: 0.0406 (4.06%)
-- Incremental conversion rate: 0.0088 additional orders per session

-- Step 6: Find the most recent pageview for `gsearch nonbrand` traffic sent to `/home`
SELECT 
	MAX(website_sessions.website_session_id) AS most_recent_gsearch_nonbrand_home_pageview 
FROM website_sessions 
	LEFT JOIN website_pageviews 
		ON website_pageviews.website_session_id = website_sessions.website_session_id
WHERE utm_source = 'gsearch'
	AND utm_campaign = 'nonbrand'
    AND pageview_url = '/home'
    AND website_sessions.created_at < '2012-11-27';

-- Result: Max website_session_id = 17145

-- Step 7: Count website sessions since the test for `/home` traffic
SELECT 
	COUNT(website_session_id) AS sessions_since_test
FROM website_sessions
WHERE created_at < '2012-11-27'
	AND website_session_id > 17145 -- Last `/home` session
	AND utm_source = 'gsearch'
	AND utm_campaign = 'nonbrand';

-- Results:
-- 22,972 website sessions since the test
-- Incremental orders: 22,972 sessions × 0.0087 conversion rate = 202 incremental orders since 7/29
-- Roughly 50 extra orders per month over 4 months. A positive impact!

STEP 5:

Kristin Dain's SQL analysis of monthly paid search, organic search, and direct website traffic, comparing sessions across digital marketing acquisition channels.

7. Full Conversion Funnel Analysis for Landing Page Test


Goal: Show a full conversion funnel from each of the two landing pages (`/home` and `/lander-1`) to orders, 
using the same test period as before (Jun 19 – Jul 28).

-- Step 1: Retrieve session-level data with flags for each key page in the funnel
SELECT
	website_sessions.website_session_id, 
    website_pageviews.pageview_url, 
    -- Flag for `/home` landing page
    CASE WHEN pageview_url = '/home' THEN 1 ELSE 0 END AS homepage,
    -- Flag for `/lander-1` landing page
    CASE WHEN pageview_url = '/lander-1' THEN 1 ELSE 0 END AS custom_lander,
    -- Flags for key pages in the funnel
    CASE WHEN pageview_url = '/products' THEN 1 ELSE 0 END AS products_page,
    CASE WHEN pageview_url = '/the-original-mr-fuzzy' THEN 1 ELSE 0 END AS mrfuzzy_page, 
    CASE WHEN pageview_url = '/cart' THEN 1 ELSE 0 END AS cart_page,
    CASE WHEN pageview_url = '/shipping' THEN 1 ELSE 0 END AS shipping_page,
    CASE WHEN pageview_url = '/billing' THEN 1 ELSE 0 END AS billing_page,
    CASE WHEN pageview_url = '/thank-you-for-your-order' THEN 1 ELSE 0 END AS thankyou_page
FROM website_sessions 
	LEFT JOIN website_pageviews 
		ON website_sessions.website_session_id = website_pageviews.website_session_id
WHERE website_sessions.utm_source = 'gsearch' 
	AND website_sessions.utm_campaign = 'nonbrand' 
    AND website_sessions.created_at < '2013-01-01'
ORDER BY 
	website_sessions.website_session_id,
    website_pageviews.created_at;

-- Step 2: Create a temporary table with session-level flags for each funnel stage
CREATE TEMPORARY TABLE session_level_made_it_flagged
SELECT
	website_session_id, 
    MAX(homepage) AS saw_homepage, 
    MAX(custom_lander) AS saw_custom_lander,
    MAX(products_page) AS product_made_it, 
    MAX(mrfuzzy_page) AS mrfuzzy_made_it, 
    MAX(cart_page) AS cart_made_it,
    MAX(shipping_page) AS shipping_made_it,
    MAX(billing_page) AS billing_made_it,
    MAX(thankyou_page) AS thankyou_made_it
FROM (
	SELECT
		website_sessions.website_session_id, 
	    website_pageviews.pageview_url, 
	    -- Flags for key pages (same logic as above)
	    CASE WHEN pageview_url = '/home' THEN 1 ELSE 0 END AS homepage,
	    CASE WHEN pageview_url = '/lander-1' THEN 1 ELSE 0 END AS custom_lander,
	    CASE WHEN pageview_url = '/products' THEN 1 ELSE 0 END AS products_page,
	    CASE WHEN pageview_url = '/the-original-mr-fuzzy' THEN 1 ELSE 0 END AS mrfuzzy_page, 
	    CASE WHEN pageview_url = '/cart' THEN 1 ELSE 0 END AS cart_page,
	    CASE WHEN pageview_url = '/shipping' THEN 1 ELSE 0 END AS shipping_page,
	    CASE WHEN pageview_url = '/billing' THEN 1 ELSE 0 END AS billing_page,
	    CASE WHEN pageview_url = '/thank-you-for-your-order' THEN 1 ELSE 0 END AS thankyou_page
	FROM website_sessions 
		LEFT JOIN website_pageviews 
			ON website_sessions.website_session_id = website_pageviews.website_session_id
	WHERE website_sessions.utm_source = 'gsearch' 
		AND website_sessions.utm_campaign = 'nonbrand' 
	    AND website_sessions.created_at < '2013-01-01'
	ORDER BY 
		website_sessions.website_session_id,
	    website_pageviews.created_at
) AS pageview_level
GROUP BY 
	website_session_id;

-- Step 3: Generate final output - Part 1: Sessions and funnel progression counts
SELECT
	CASE 
		WHEN saw_homepage = 1 THEN 'saw_homepage'
        WHEN saw_custom_lander = 1 THEN 'saw_custom_lander'
        ELSE 'uh oh... check logic' 
	END AS segment, 
    COUNT(DISTINCT website_session_id) AS sessions,
    COUNT(DISTINCT CASE WHEN product_made_it = 1 THEN website_session_id ELSE NULL END) AS to_products,
    COUNT(DISTINCT CASE WHEN mrfuzzy_made_it = 1 THEN website_session_id ELSE NULL END) AS to_mrfuzzy,
    COUNT(DISTINCT CASE WHEN cart_made_it = 1 THEN website_session_id ELSE NULL END) AS to_cart,
    COUNT(DISTINCT CASE WHEN shipping_made_it = 1 THEN website_session_id ELSE NULL END) AS to_shipping,
    COUNT(DISTINCT CASE WHEN billing_made_it = 1 THEN website_session_id ELSE NULL END) AS to_billing,
    COUNT(DISTINCT CASE WHEN thankyou_made_it = 1 THEN website_session_id ELSE NULL END) AS to_thankyou
FROM session_level_made_it_flagged 
GROUP BY 1;

-- Step 4: Generate final output - Part 2: Funnel click rates
SELECT
	CASE 
		WHEN saw_homepage = 1 THEN 'saw_homepage'
        WHEN saw_custom_lander = 1 THEN 'saw_custom_lander'
        ELSE 'uh oh... check logic' 
	END AS segment, 
	COUNT(DISTINCT CASE WHEN product_made_it = 1 THEN website_session_id ELSE NULL END)/COUNT(DISTINCT website_session_id) AS lander_click_rt,
    COUNT(DISTINCT CASE WHEN mrfuzzy_made_it = 1 THEN website_session_id ELSE NULL END)/COUNT(DISTINCT CASE WHEN product_made_it = 1 THEN website_session_id ELSE NULL END) AS products_click_rt,
    COUNT(DISTINCT CASE WHEN cart_made_it = 1 THEN website_session_id ELSE NULL END)/COUNT(DISTINCT CASE WHEN mrfuzzy_made_it = 1 THEN website_session_id ELSE NULL END) AS mrfuzzy_click_rt,
    COUNT(DISTINCT CASE WHEN shipping_made_it = 1 THEN website_session_id ELSE NULL END)/COUNT(DISTINCT CASE WHEN cart_made_it = 1 THEN website_session_id ELSE NULL END) AS cart_click_rt,
    COUNT(DISTINCT CASE WHEN billing_made_it = 1 THEN website_session_id ELSE NULL END)/COUNT(DISTINCT CASE WHEN shipping_made_it = 1 THEN website_session_id ELSE NULL END) AS shipping_click_rt,
    COUNT(DISTINCT CASE WHEN thankyou_made_it = 1 THEN website_session_id ELSE NULL END)/COUNT(DISTINCT CASE WHEN billing_made_it = 1 THEN website_session_id ELSE NULL END) AS billing_click_rt
FROM session_level_made_it_flagged
GROUP BY 1;

STEP 3:

Kristin Dain's SQL analysis comparing custom landing page and homepage performance, including sessions, product views, cart activity, checkout progression, and completed orders.

STEP 4:

Marketing conversion funnel analysis using SQL to compare click-through rates across landing pages, product pages, shopping carts, shipping, and billing steps.

8. Quantifying the Impact of the Billing Test


Goal: Analyze the revenue lift generated by the billing page test conducted from Sep 10 to Nov 10. 
Determine the revenue per session for the two billing page versions and calculate the monthly impact 
based on the number of billing page sessions in the past month (Jan 2013).

SELECT
	billing_version_seen, 
    COUNT(DISTINCT website_session_id) AS sessions, 
    SUM(price_usd)/COUNT(DISTINCT website_session_id) AS revenue_per_billing_page_seen
 FROM( 
SELECT 
	website_pageviews.website_session_id, 
    website_pageviews.pageview_url AS billing_version_seen, 
    orders.order_id, 
    orders.price_usd
FROM website_pageviews 
	LEFT JOIN orders
		ON orders.website_session_id = website_pageviews.website_session_id
WHERE website_pageviews.created_at < '2013-01-01' -- prescribed in assignment
    AND website_pageviews.pageview_url IN ('/billing','/billing-2')
) AS billing_pageviews_and_order_data
GROUP BY 1
;
    
-- $22.34 revenue per billing page seen for the old version
-- $30.97 for the new version
-- LIFT: $8.63 per billing page view

SQL data analysis by marketing analyst Kristin Dain comparing billing page variations, website sessions, and revenue per billing page to evaluate ecommerce performance.