Traffic Source Analysis is finding insights about from where and how the website is getting traffic which is viewers. And finding how many of the viewers have purchased something through the paid ads and referrals etc..
For this analysis, I am going to use the website_sessions table and the orders table data.
website_sessions table:
A website session is referred as a user viewing the website and viewer will be assigned a session_id. utm_content is the urchin tracking module content which helps us to track the paid ads performance. utm_source is the urchin tracking module source from where our viewers are coming. these utm_content and utm_campaign will be embedded at the end of the website url from where we are getting viewers.
orders table :
For each order which is placed by the viewer who came from a paid ad or any other website, we can track in this orders table with the website_session_id column and then derive insights from it.
So, now we can derive some insights for the mostly done analysis.
1. How many users viewed the website from which websites/paid ads.
SELECT DISTINCT utm_content,
COUNT(DISTINCT website_session_id)
FROM website_sessions
GROUP BY utm_content;Here, each viewer will be assigned a session_id, so it is easy to track the number of viewers. This query show how many viewers has viewed the different utm_content.
The utm_content which has null value can be some website which didn't mention the utm_content in their url.
We can clearly see that, g_ad_2 got us more viewers than other sources.
2. How many viewers have turned into customers by purchasing something.
SELECT DISTINCT website_sessions.utm_content,
COUNT(website_sessions.website_session_id) AS sessions,
COUNT(orders.website_session_Id) AS Orders,
(COUNT(orders.website_session_Id)/COUNT(website_sessions.website_session_id))*100 AS Cvrt_Rate
FROM website_sessions
LEFT JOIN orders
ON orders.website_session_id = website_sessions.website_session_id
WHERE website_sessions.website_session_id BETWEEN 1000 AND 2000
GROUP BY utm_content
ORDER BY COUNT(website_session_id) DESC; Note : Conversion Rate = No. of. Orders / No. of. Viewers
In the above query, I am joining website_sessions and orders table, to get the conversion rate which tells us the number of orders placed by the viewers through the paid ads/ referral websites.
We can see that, g_ad_1 has 3.5% of its viewers turned into customers and others has no orders in our context. So we can focus more on g_ad_1 utm_content to increase its performance much higher.
3. Where the bulk of our website sessions(viewers) are coming from, sort before 2014-04-12?
SELECT utm_source, utm_campaign, http_referer, COUNT(website_session_id) AS sessions
FROM website_sessions
WHERE created_at < '2014-04-12'
GROUP BY utm_source,utm_campaign,http_referer
ORDER BY sessions DESC; Here, we have grouped by the utm_source, utm_campaign and the http_referer to get the number of viewers for each one of them.
We can see that, there are more viewers from the gsearch source and nonbrand campaign.
3. Find how many times a product has been ordered 1 item and 2 items.
SELECT primary_product_id, order_id,items_purchased,
COUNT(DISTINCT CASE WHEN items_purchased=1 THEN order_id ELSE NULL END) AS one_item_ordered,
COUNT(DISTINCT CASE WHEN items_purchased=2 THEN order_id ELSE NULL END) AS two_item_ordered,
COUNT(DISTINCT order_id) as total_orders
FROM orders
WHERE order_id BETWEEN 31000 AND 32000 # arbitrary
GROUP BY 1;For each product, I have counted the number of items ordered by a customer who viewed our website via some utm source.
There are only four products in our website. We can see the number of orders with 1 item and number of orders for 2 item for each product by using the above query.
4. Find the week vise data for how many viewers viewed the website in desktop and mobile separately for gsearch nonbrand between 2012-04-15 and 2012-06-09. So that we can focus more on improving the performance.
SELECT
YEAR(created_at),
WEEK(created_at),
MIN(DATE(created_at)),
COUNT(DISTINCT CASE WHEN device_type='mobile' THEN website_session_id ELSE NULL END) as mobile_sessions,
COUNT(DISTINCT CASE WHEN device_type='desktop' THEN website_session_id ELSE NULL END) as desktop_sessions
FROM website_sessions
WHERE website_sessions.created_at < '2012-06-09' AND website_sessions.created_at>'2012-04-15' AND
utm_source='gsearch' AND utm_campaign='nonbrand'
GROUP BY YEAR(created_at),WEEK(created_at);I have used the Date functions: YEAR() and WEEK() for extracting the insight required. Here, we are counting the number of viewers viewing the website with desktop and mobile seperated.
We can clearly see that desktop sessions are higher than mobile sessions. So we can try to improve the desktop performance much more to get more views on it. And also try to check why mobile has less views than desktop.
Commenti