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 = 'gsear...