BUSINESS ANALYSIS (SQL QUERY) FOR E -COMMERCE - PROJECT
OUERY 1 - FINDING TOP TRAFFIC SOURCES
SELECT
utm_source,
utm_campaign,
http_referer,
COUNT(website_session_id) AS count_of_sessions
FROM website_sessions
WHERE created_at < '2012-04-12'
GROUP BY
1,2,3
ORDER BY 4 DESC;
OUTPUT - 
QUERY 2 -TRAFFIC SOURCE CONVERSION RATES
SELECT
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 sess_2_ordr_cnvt
FROM website_sessions
LEFT JOIN orders
ON orders.website_session_id = website_sessions.website_session_id
WHERE website_sessions.created_at < '2012-04-14'
AND website_sessions.utm_source = 'gsearch'
AND website_sessions.utm_campaign = 'nonbrand';
OUTPUT- 
OUERY 3 - TRAFFIC SOURCE TRENDING
SELECT
MIN( DATE(created_at)) AS week_start_date,
COUNT(website_session_id) AS sessions
FROM website_sessions
WHERE created_at < '2012-05-10'
AND utm_source = 'gsearch'
AND utm_campaign = 'nonbrand'
GROUP BY YEAR(created_at),
WEEK(created_at);
OUTPUT -
OUERY 4 - BID OPTIMIZATION FOR PAID TRAFFIC
SELECT
device_type,
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 sess_2_ordr_cnvt
FROM website_sessions
LEFT JOIN orders
ON orders.website_session_id = website_sessions.website_session_id
WHERE website_sessions.created_at < '2012-05-11'
AND utm_source = 'gsearch'
AND utm_campaign = 'nonbrand'
GROUP BY website_sessions.device_type;
OUTPUT -
QUERY 5 - TRENDING WITH GRANULAR SEGMENTS
SELECT
MIN(DATE(created_at)),
COUNT(DISTINCT CASE WHEN device_type = 'desktop' THEN website_session_id ELSE NULL END) AS dtop_sessions,
COUNT(DISTINCT CASE WHEN device_type = 'mobile' THEN website_session_id ELSE NULL END) AS mob_sessions
FROM website_sessions
WHERE created_at >'2012-04-15'
AND created_at < '2012-06-09'
AND utm_source = 'gsearch'
AND utm_campaign = 'nonbrand'
GROUP BY
YEAR(created_at),
WEEK(created_at);
OUTPUT - 
OUERY 6 - FINDING TOP WEBSITES PAGES
SELECT
pageview_url,
COUNT(DISTINCT website_pageview_id) AS sessions
FROM website_pageviews
WHERE created_at < '2012-06-09'
GROUP BY pageview_url
ORDER BY sessions DESC;
OUTPUT - 
QUERY 7 - FINDING TOP ENTRY PAGES
CREATE TEMPORARY TABLE url_find
SELECT
website_session_id,
MIN(website_pageview_id) AS first_pv
FROM website_pageviews
WHERE created_at < '2012-06-12'
GROUP BY website_session_id;
SELECT
website_pageviews.pageview_url,
COUNT(website_pageviews.website_session_id)
FROM url_find
LEFT JOIN website_pageviews
ON url_find.first_pv = website_pageviews.website_pageview_id
GROUP BY website_pageviews.pageview_url;
OUTPUT - 
QUERY 8 - CALCULATING BOUNCE RATE
CREATE TEMPORARY TABLE frst_pge_vw
SELECT
website_session_id,
MIN(website_pageview_id) AS fp_view
FROM website_pageviews
WHERE created_at < '2012-06-12'
GROUP BY website_session_id;
CREATE TEMPORARY TABLE sessions_w_landing_page
SELECT
frst_pge_vw.website_session_id,
website_pageviews.pageview_url AS landing_page
FROM frst_pge_vw
LEFT JOIN website_pageviews
ON website_pageviews.website_pageview_id = frst_pge_vw.fp_view
WHERE website_pageviews.pageview_url = '/home';
CREATE TEMPORARY TABLE bounced_session
SELECT
sessions_w_landing_page.website_session_id,
sessions_w_landing_page.landing_page,
COUNT(website_pageviews.website_pageview_id) AS count_of_pages_viewed
FROM sessions_w_landing_page
LEFT JOIN website_pageviews
ON website_pageviews.website_session_id = sessions_w_landing_page.website_session_id
GROUP BY sessions_w_landing_page.website_session_id,
sessions_w_landing_page.landing_page
HAVING count_of_pages_viewed = 1;
SELECT
COUNT(DISTINCT sessions_w_landing_page.website_session_id) AS total_session,
COUNT(DISTINCT bounced_session.website_session_id) AS bounced_session,
COUNT(DISTINCT bounced_session.website_session_id)/COUNT(DISTINCT sessions_w_landing_page.website_session_id) AS bounced_rate
FROM sessions_w_landing_page
LEFT JOIN bounced_session
ON sessions_w_landing_page.website_session_id = bounced_session.website_session_id;
OUTPUT - 
OUERY 9 - LANDING PAGE TREND ANALYSIS
SELECT
website_session_id
FROM website_sessions
WHERE created_at < '2012-08-31' AND created_at > '2012-06-1' AND utm_campaign = 'nonbrand';
CREATE TEMPORARY TABLE first_view_page
SELECT
website_session_id,
MIN(website_pageview_id) AS first_pview
FROM website_pageviews
WHERE created_at < '2012-08-31' AND website_session_id IN (SELECT
website_session_id
FROM website_sessions
WHERE created_at < '2012-08-31' AND created_at > '2012-06-1' AND utm_campaign = 'nonbrand'
)
GROUP BY website_session_id;
CREATE TEMPORARY TABLE landing_session_page
SELECT
website_pageviews.created_at,
first_view_page.website_session_id,
website_pageviews.pageview_url
FROM first_view_page
INNER JOIN website_pageviews
ON first_view_page.first_pview =website_pageviews.website_pageview_id
WHERE website_pageviews.pageview_url IN ('/home','/lander-1');
CREATE TEMPORARY TABLE bounced_session
SELECT
website_pageviews.created_at,
landing_session_page.website_session_id,
landing_session_page.pageview_url,
COUNT(website_pageviews.website_pageview_id)
FROM landing_session_page
LEFT JOIN website_pageviews
ON website_pageviews.website_session_id = landing_session_page.website_session_id
GROUP BY landing_session_page.website_session_id
HAVING COUNT(website_pageviews.website_pageview_id) = 1;
SELECT
MIN(DATE(landing_session_page.created_at)) AS week_start_date,
COUNT(CASE WHEN landing_session_page.pageview_url = '/home' THEN landing_session_page.website_session_id ELSE NULL END) AS home_sessions,
COUNT(CASE WHEN landing_session_page.pageview_url = '/lander-1' THEN landing_session_page.website_session_id ELSE NULL END) AS lander_sessions,
COUNT(bounced_session.website_session_id)/ (COUNT(CASE WHEN landing_session_page.pageview_url = '/home' THEN landing_session_page.website_session_id ELSE NULL END)+
COUNT(CASE WHEN landing_session_page.pageview_url = '/lander-1' THEN landing_session_page.website_session_id ELSE NULL END)) AS bounce_rate
FROM landing_session_page
LEFT JOIN bounced_session
ON landing_session_page.website_session_id = bounced_session.website_session_id
GROUP BY YEAR(landing_session_page.created_at),
WEEK(landing_session_page.created_at);
OUTPUT - 
QUERY 10 - BUILDING CONVERSION FUNNELS
SELECT
website_pageviews.website_session_id,
website_pageviews.pageview_url
FROM website_pageviews
LEFT JOIN website_sessions
ON website_pageviews.website_session_id = website_sessions.website_session_id
WHERE website_sessions.utm_source = 'gsearch'
AND website_sessions.utm_campaign = 'nonbrand'
AND website_sessions.created_at > '2012-08-05'
AND website_sessions.created_at < '2012-09-05'
AND website_pageviews.pageview_url IN ('/lander-1', '/products','/the-original-mr-fuzzy','/cart','/shipping','/billing','/thank-you-for-your-order');
CREATE TEMPORARY TABLE second_level
SELECT
website_session_id,
CASE WHEN pageview_url ='/lander-1' THEN 1 ELSE 0 END AS to_lander,
CASE WHEN pageview_url ='/products' THEN 1 ELSE 0 END AS to_product,
CASE WHEN pageview_url ='/the-original-mr-fuzzy' THEN 1 ELSE 0 END AS to_mr_fuzzy,
CASE WHEN pageview_url ='/cart' THEN 1 ELSE 0 END AS to_cart,
CASE WHEN pageview_url ='/shipping' THEN 1 ELSE 0 END AS to_shipping,
CASE WHEN pageview_url ='/billing' THEN 1 ELSE 0 END AS to_billing,
CASE WHEN pageview_url ='/thank-you-for-your-order' THEN 1 ELSE 0 END AS to_order
FROM (SELECT
website_pageviews.website_session_id,
website_pageviews.pageview_url
FROM website_pageviews
LEFT JOIN website_sessions
ON website_pageviews.website_session_id = website_sessions.website_session_id
WHERE website_sessions.utm_source = 'gsearch'
AND website_sessions.utm_campaign = 'nonbrand'
AND website_sessions.created_at > '2012-08-05'
AND website_sessions.created_at < '2012-09-05'
AND website_pageviews.pageview_url IN ('/lander-1', '/products','/the-original-mr-fuzzy','/cart','/shipping','/billing','/thank-you-for-your-order')
) AS first_level;
SELECT
website_session_id,
MAX(to_lander) AS lander,
MAX(to_product) AS product,
MAX(to_mr_fuzzy) AS mr_fuzzy,
MAX(to_cart) AS cart,
MAX(to_shipping) AS shipping,
MAX(to_billing) AS billing,
MAX(to_order) AS thank_u
FROM second_level
GROUP BY website_session_id;
CREATE TEMPORARY TABLE third_level
SELECT
COUNT(website_session_id) AS sessions,
COUNT(CASE WHEN lander = 1 THEN website_session_id ELSE NULL END) AS to_lander,
COUNT(CASE WHEN product = 1 THEN website_session_id ELSE NULL END) AS to_product,
COUNT(CASE WHEN mr_fuzzy = 1 THEN website_session_id ELSE NULL END) AS to_mr_fuzzy,
COUNT(CASE WHEN cart = 1 THEN website_session_id ELSE NULL END) AS to_cart,
COUNT(CASE WHEN shipping = 1 THEN website_session_id ELSE NULL END) AS to_shipping,
COUNT(CASE WHEN billing = 1 THEN website_session_id ELSE NULL END) AS to_billing,
COUNT(CASE WHEN thank_u = 1 THEN website_session_id ELSE NULL END) AS to_order
FROM(SELECT
website_session_id,
MAX(to_lander) AS lander,
MAX(to_product) AS product,
MAX(to_mr_fuzzy) AS mr_fuzzy,
MAX(to_cart) AS cart,
MAX(to_shipping) AS shipping,
MAX(to_billing) AS billing,
MAX(to_order) AS thank_u
FROM second_level
GROUP BY website_session_id) AS next_level;
SELECT
to_product/sessions AS lander_click_rt,
to_mr_fuzzy/to_product AS product_click_rt,
to_cart/to_mr_fuzzy AS mr_fuzzy_click_rt,
to_shipping/to_cart AS cart_click_r,
to_billing/to_shipping AS shipping_click_rt,
to_order/to_billing AS billing_click_rt
FROM third_level;
OUTPUT - 
QUERY 11 - ANALYSIS CONVERSION FUNNEL TEST
SELECT
MIN(created_at),
MIN(pageview_url)
FROM website_pageviews
WHERE pageview_url = '/billing-2';
SELECT
website_pageviews.website_session_id,
website_pageviews.pageview_url AS billing_version_seen,
orders.order_id
FROM website_pageviews
LEFT JOIN orders
ON orders.website_session_id = website_pageviews.website_session_id
WHERE website_pageviews.pageview_url IN ('/billing', '/billing-2')
AND website_pageviews.created_at > '2012-09-10'
AND website_pageviews.created_at < '2012-11-10'
GROUP BY website_pageviews.website_session_id ;
SELECT
billing_version_seen,
COUNT(website_session_id) AS sessions,
COUNT(order_id) AS orders,
COUNT(order_id)/ COUNT(billing_version_seen) AS order_conv_rate
FROM(SELECT
website_pageviews.website_session_id,
website_pageviews.pageview_url AS billing_version_seen,
orders.order_id
FROM website_pageviews
LEFT JOIN orders
ON orders.website_session_id = website_pageviews.website_session_id
WHERE website_pageviews.pageview_url IN ('/billing', '/billing-2')
AND website_pageviews.created_at > '2012-09-10'
AND website_pageviews.created_at < '2012-11-10'
GROUP BY website_pageviews.website_session_id ) AS count_level
GROUP BY billing_version_seen
ORDER BY orders;
OUTPUT - 
Comments
Post a Comment