SQL for E-commerce Performance & Revenue Analysis
1. Website Sessions and Orders by Year and Quarter
Goal: To analyze website session and order data by year and quarter, providing a count of distinct sessions and orders for each time period. This helps in understanding user behavior and sales trends over time.SELECT YEAR(website_sessions.created_at) AS yr, QUARTER(website_sessions.created_at) AS qtr, COUNT(DISTINCT website_sessions.website_session_id) AS sessions, COUNT(DISTINCT orders.order_id) AS orders FROM website_sessions LEFT JOIN orders ON website_sessions.website_session_id = orders.website_session_id GROUP BY 1,2 ORDER BY 1,2 ;
2. Session-to-Order Conversion and Revenue Metrics by Year and Quarter
Goal: To calculate and analyze key e-commerce metrics—session-to-order conversion rate, revenue per order, and revenue per session—broken down by year and quarter. This provides insights into user behavior, sales efficiency, and revenue generation trends over time.SELECT YEAR(website_sessions.created_at) AS yr, QUARTER(website_sessions.created_at) AS qtr, COUNT(DISTINCT orders.order_id)/COUNT(DISTINCT website_sessions.website_session_id) AS session_to_order_conv_rate, SUM(price_usd)/COUNT(DISTINCT orders.order_id) AS revenue_per_order, SUM(price_usd)/COUNT(DISTINCT website_sessions.website_session_id) AS revenue_per_session FROM website_sessions LEFT JOIN orders ON website_sessions.website_session_id = orders.website_session_id GROUP BY 1,2 ORDER BY 1,2 ;
3. Breakdown of Orders by Traffic Source and Campaign Type by Year and Quarter
Goal: To categorize and analyze orders based on traffic sources and campaign types (e.g., Google non-brand search, Bing non-brand search, brand campaigns, organic search, and direct type-in traffic) for each year and quarter. This helps in understanding the effectiveness of different marketing channels and user acquisition strategies over time.SELECT YEAR(website_sessions.created_at) AS yr, QUARTER(website_sessions.created_at) AS qtr, COUNT(DISTINCT CASE WHEN utm_source = 'gsearch' AND utm_campaign = 'nonbrand' THEN orders.order_id ELSE NULL END) AS gsearch_nonbrand_orders, COUNT(DISTINCT CASE WHEN utm_source = 'bsearch' AND utm_campaign = 'nonbrand' THEN orders.order_id ELSE NULL END) AS bsearch_nonbrand_orders, COUNT(DISTINCT CASE WHEN utm_campaign = 'brand' THEN orders.order_id ELSE NULL END) AS brand_search_orders, COUNT(DISTINCT CASE WHEN utm_source IS NULL AND http_referer IS NOT NULL THEN orders.order_id ELSE NULL END) AS organic_search_orders, COUNT(DISTINCT CASE WHEN utm_source IS NULL AND http_referer IS NULL THEN orders.order_id ELSE NULL END) AS direct_type_in_orders FROM website_sessions LEFT JOIN orders ON website_sessions.website_session_id = orders.website_session_id GROUP BY 1,2 ORDER BY 1,2 ;
4. Conversion Rates by Traffic Source and Campaign Type by Year and Quarter
Goal: To calculate and analyze conversion rates for distinct traffic sources and campaign types—Google non-brand search, Bing non-brand search, brand campaigns, organic search, and direct type-in traffic—on a yearly and quarterly basis. This provides insights into the effectiveness of specific marketing channels in driving customer conversions.SELECT YEAR(website_sessions.created_at) AS yr, QUARTER(website_sessions.created_at) AS qtr, COUNT(DISTINCT CASE WHEN utm_source = 'gsearch' AND utm_campaign = 'nonbrand' THEN orders.order_id ELSE NULL END) /COUNT(DISTINCT CASE WHEN utm_source = 'gsearch' AND utm_campaign = 'nonbrand' THEN website_sessions.website_session_id ELSE NULL END) AS gsearch_nonbrand_conv_rt, COUNT(DISTINCT CASE WHEN utm_source = 'bsearch' AND utm_campaign = 'nonbrand' THEN orders.order_id ELSE NULL END) /COUNT(DISTINCT CASE WHEN utm_source = 'bsearch' AND utm_campaign = 'nonbrand' THEN website_sessions.website_session_id ELSE NULL END) AS bsearch_nonbrand_conv_rt, COUNT(DISTINCT CASE WHEN utm_campaign = 'brand' THEN orders.order_id ELSE NULL END) /COUNT(DISTINCT CASE WHEN utm_campaign = 'brand' THEN website_sessions.website_session_id ELSE NULL END) AS brand_search_conv_rt, COUNT(DISTINCT CASE WHEN utm_source IS NULL AND http_referer IS NOT NULL THEN orders.order_id ELSE NULL END) /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_conv_rt, COUNT(DISTINCT CASE WHEN utm_source IS NULL AND http_referer IS NULL THEN orders.order_id ELSE NULL END) /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_conv_rt FROM website_sessions LEFT JOIN orders ON website_sessions.website_session_id = orders.website_session_id GROUP BY 1,2 ORDER BY 1,2 ;
5. Monthly Revenue and Margins by Product and Overall Performance
Goal: To calculate monthly revenue and profit margins for individual products (Mr. Fuzzy, Love Bear, Birthday Bear, and Mini Bear) and aggregate totals for all products. This analysis provides insights into the financial performance of each product and overall profitability on a month-to-month basis.SELECT YEAR(created_at) AS yr, MONTH(created_at) AS mo, SUM(CASE WHEN product_id = 1 THEN price_usd ELSE NULL END) AS mrfuzzy_rev, SUM(CASE WHEN product_id = 1 THEN price_usd - cogs_usd ELSE NULL END) AS mrfuzzy_marg, SUM(CASE WHEN product_id = 2 THEN price_usd ELSE NULL END) AS lovebear_rev, SUM(CASE WHEN product_id = 2 THEN price_usd - cogs_usd ELSE NULL END) AS lovebear_marg, SUM(CASE WHEN product_id = 3 THEN price_usd ELSE NULL END) AS birthdaybear_rev, SUM(CASE WHEN product_id = 3 THEN price_usd - cogs_usd ELSE NULL END) AS birthdaybear_marg, SUM(CASE WHEN product_id = 4 THEN price_usd ELSE NULL END) AS minibear_rev, SUM(CASE WHEN product_id = 4 THEN price_usd - cogs_usd ELSE NULL END) AS minibear_marg, SUM(price_usd) AS total_revenue, SUM(price_usd - cogs_usd) AS total_margin FROM order_items GROUP BY 1,2 ORDER BY 1,2 ;
6. Monthly Product Page Engagement and Conversion Analysis
Goal: To measure engagement and conversion metrics related to the /products page, including the number of sessions that viewed the page, subsequent page clicks, clickthrough rates, and conversion rates from product page views to orders. This analysis provides insights into user behavior and sales performance for the /products page.-- Step 1: Identifying all the views of the /products page CREATE TEMPORARY TABLE products_pageviews SELECT website_session_id, website_pageview_id, created_at AS saw_product_page_at FROM website_pageviews WHERE pageview_url = '/products' ; -- Step 2: SELECT YEAR(saw_product_page_at) AS yr, MONTH(saw_product_page_at) AS mo, COUNT(DISTINCT products_pageviews.website_session_id) AS sessions_to_product_page, COUNT(DISTINCT website_pageviews.website_session_id) AS clicked_to_next_page, COUNT(DISTINCT website_pageviews.website_session_id)/COUNT(DISTINCT products_pageviews.website_session_id) AS clickthrough_rt, COUNT(DISTINCT orders.order_id) AS orders, COUNT(DISTINCT orders.order_id)/COUNT(DISTINCT products_pageviews.website_session_id) AS products_to_order_rt FROM products_pageviews LEFT JOIN website_pageviews ON website_pageviews.website_session_id = products_pageviews.website_session_id -- same session AND website_pageviews.website_pageview_id > products_pageviews.website_pageview_id -- they had another page AFTER LEFT JOIN orders ON orders.website_session_id = products_pageviews.website_session_id GROUP BY 1,2 ;
7. Cross-Sell Performance Analysis by Primary Product
Goal: To evaluate the cross-sell effectiveness of products purchased alongside primary product orders. The analysis identifies the total orders for each primary product, calculates the number of orders with specific products as cross-sells, and determines the cross-sell rates for each product. This helps assess customer purchasing behavior and optimize cross-selling strategies.-- Step 1: Identify Primary Product Orders CREATE TEMPORARY TABLE primary_products SELECT order_id, primary_product_id, created_at AS ordered_at FROM orders WHERE created_at > '2014-12-05' -- when the 4th product was added (says so in question) ; -- Step 2: Join with Cross-Sell Data SELECT primary_products.*, order_items.product_id AS cross_sell_product_id FROM primary_products LEFT JOIN order_items ON order_items.order_id = primary_products.order_id AND order_items.is_primary_item = 0; -- only bringing in cross-sells; -- Step 3: Calculate Cross-Sell Metrics SELECT primary_product_id, COUNT(DISTINCT order_id) AS total_orders, COUNT(DISTINCT CASE WHEN cross_sell_product_id = 1 THEN order_id ELSE NULL END) AS _xsold_p1, COUNT(DISTINCT CASE WHEN cross_sell_product_id = 2 THEN order_id ELSE NULL END) AS _xsold_p2, COUNT(DISTINCT CASE WHEN cross_sell_product_id = 3 THEN order_id ELSE NULL END) AS _xsold_p3, COUNT(DISTINCT CASE WHEN cross_sell_product_id = 4 THEN order_id ELSE NULL END) AS _xsold_p4, COUNT(DISTINCT CASE WHEN cross_sell_product_id = 1 THEN order_id ELSE NULL END)/COUNT(DISTINCT order_id) AS p1_xsell_rt, COUNT(DISTINCT CASE WHEN cross_sell_product_id = 2 THEN order_id ELSE NULL END)/COUNT(DISTINCT order_id) AS p2_xsell_rt, COUNT(DISTINCT CASE WHEN cross_sell_product_id = 3 THEN order_id ELSE NULL END)/COUNT(DISTINCT order_id) AS p3_xsell_rt, COUNT(DISTINCT CASE WHEN cross_sell_product_id = 4 THEN order_id ELSE NULL END)/COUNT(DISTINCT order_id) AS p4_xsell_rt FROM ( SELECT primary_products.*, order_items.product_id AS cross_sell_product_id FROM primary_products LEFT JOIN order_items ON order_items.order_id = primary_products.order_id AND order_items.is_primary_item = 0 -- only bringing in cross-sells ) AS primary_w_cross_sell GROUP BY 1;
STEP 2:
STEP 3:
