Alfikri Ramadhan eCommerce Analysis with SQL
eCommerce Database Analysis with SQL
1. Project Background
You've been hired as an eCommerce Database Analyst for Maven Fuzzy Factory, an online retailer which
has just launched its first product.
Maven Fuzzy Factory has been live for ~8 months, and your CEO is due to present company performance
metrics to the board next week.
You will extract and analyze website traffic and performance data from the Maven Fuzzy Factory
database to quantify the company’s growth and tell the story of how you have been able to generate
that growth.
2. Data Preparation
2.1. Entity Relationship Diagram (ERD)
1
Alfikri Ramadhan eCommerce Analysis with SQL
2.2. Tables
a. Orders
Records consist of customers’ orders with order id, time when the order is
created, website session id, user id, product id of item ordered, number of items
purchased, the price of the product (revenue) and COGS (cost of goods sold) in
USD. There are 32,313 rows in this table.
b. Products
Records consist of customers' orders with order id, time when the order is created,
website session id, user id, product id of item ordered, number of items purchased, the
price of the product (revenue), and cogs (cost of goods sold) in USD. There are 4 rows in
this table.
c. Website_sessions
Records consist of each website session. This table shows where the traffic is coming
from. There are 472,871 rows in this table.
d. Website_pageviews
The table that shows website pageviews and url of each pageview. There are 1,188,124
rows in this table.
e. Order_items
2
Alfikri Ramadhan eCommerce Analysis with SQL
f. Order_item_refunds
3. Project Goal
Cindy Sharp, the Maven Fuzzy Factory CEO just sent you an email. She needs your help to prepare a
presentation for the board meeting.
3.1. Objectives
• Tell the story of the company's growth, using trended performance data.
• Use the database to explain some of the details around the company's growth story.
• Analyse current performance and use the data available to assess upcoming
opportunities.
3.2. Problem Questions
1. Gsearch seems to be the biggest driver of our business. Could you pull monthly trends
for gsearch sessions and orders so that we can showcase the growth there?
2. Next, it would be great to see a similar trend for gsearch, but this time splitting out
nonbrand and brand campaigns separately. I am wondering if brand is picking up at all. If
so, this is a good story to tell.
3. While we're on gsearch, could you dive into nonbrand, and pull monthly sessions and
orders split by device type? I want to flex our analytical muscles a little and show the
board we really know our traffic sources.
3
Alfikri Ramadhan eCommerce Analysis with SQL
4. I'm worried that one of our more pessimistic board members may be concerned about
the large % of traffic from gsearch. Can you pull monthly trends for gsearch, alongside
monthly trends for each of our other channels?
5. I'd like to tell the story of our website performance improvements over the course of the
first 8 months. Could you pull session to order conversion rates, by month?
6. Do a comparison for /home and /lander-1 landing page test. For the gsearch lander test,
please estimate the revenue that test earned us (Hint: Look at the increase in CVR from
the test (Jun 19 - Jul 28), and use nonbrand sessions and revenue since then to calculate
incremental value)
7. For the landing page test that you analysed previously, it would be great to shows a full
conversion funnel from each of the two pages to orders. You can use the same period
you analysed last time (Jun 19 - Jul 28).
8. I'd love for you to quantify the impact of our billing test (billing and billing-2), as well.
Please analyse the lift generated from the test (Sep 10 - Nov 10), in terms of revenue per
billing page sessions, and then pull the number of billing page sessions for the past
month to understand monthly impact.
4. Analysis and Visualization
Q1. Gsearch seems to be the biggest driver of our busienss. Could you pull monthly trends for
gsearch sessions and orders so that we can showcase the growth there?
1. SELECT
2. EXTRACT(YEAR_MONTH FROM website_sessions.created_at) AS yearmonth,
3. COUNT(DISTINCT website_sessions.website_session_id) AS sessions,
4. COUNT(DISTINCT order_id) AS orders,
5. ROUND(COUNT(DISTINCT order_id) /
6. COUNT(DISTINCT website_sessions.website_session_id) * 100.0, 2) AS
conversion_rate
7. FROM website_sessions
8. LEFT JOIN orders
9. ON website_sessions.website_session_id = orders.website_session_id
10. WHERE utm_source = 'gsearch'
11. AND website_sessions.created_at < '2012-11-27'
12. GROUP BY 1;
Result:
4
Alfikri Ramadhan eCommerce Analysis with SQL
Insight:
Gsearch traffic shows steady growth of sessions and orders.
Q2. Next, it would be great to see a similar trend for gsearch, but this time splitting out
nonbrand and brand campaigns separately. I am wondering if brand is picking up at all. If so,
this is a good story to tell.
1. SELECT
2. EXTRACT(YEAR_MONTH FROM website_sessions.created_at) AS yearmonth,
3. COUNT(DISTINCT CASE WHEN utm_campaign = 'nonbrand' THEN
website_sessions.website_session_id ELSE NULL END) AS nonbrand_sessions,
4. COUNT(DISTINCT CASE WHEN utm_campaign = 'nonbrand' THEN orders.order_id
ELSE NULL END) AS nonbrand_orders,
5. ROUND(COUNT(DISTINCT CASE WHEN utm_campaign = 'nonbrand' THEN
orders.order_id ELSE NULL END) /
6. COUNT(DISTINCT CASE WHEN utm_campaign = 'nonbrand' THEN
website_sessions.website_session_id ELSE NULL END) * 100.0, 2) AS
nonbrand_cvr,
7. COUNT(DISTINCT CASE WHEN utm_campaign = 'brand' THEN
website_sessions.website_session_id ELSE NULL END) AS brand_sessions,
8. COUNT(DISTINCT CASE WHEN utm_campaign = 'brand' THEN orders.order_id
ELSE NULL END) AS brand_orders,
9. ROUND(COUNT(DISTINCT CASE WHEN utm_campaign = 'brand' THEN
orders.order_id ELSE NULL END) /
10. COUNT(DISTINCT CASE WHEN utm_campaign = 'brand' THEN
website_sessions.website_session_id ELSE NULL END) * 100.0, 2) AS
brand_cvr
11. FROM website_sessions
12. LEFT JOIN orders
13. ON website_sessions.website_session_id = orders.website_session_id
14. WHERE utm_source = 'gsearch'
15. AND website_sessions.created_at < '2012-11-27'
16. GROUP BY 1;
5
Alfikri Ramadhan eCommerce Analysis with SQL
Result:
Insight:
For brand campaign during April, the conversion rate is very high at 9.23%, though the number
of sessions and orders are still considered low compared to nonbrand campaign. The brand
sessions and orders do increase steadily every month, and while still lower than nonbrand, in
November its conversion rate shows higher number than nonbrand (4.44% vs 4.19% for brand
and nonbrand, respectively).
Q3. While we're on gsearch, could you dive into nonbrand, and pull monthly sessions and orders
split by device type? I want to flex our analytical muscles a little and show the board we
really know our traffic sources.
1. SELECT
2. EXTRACT(YEAR_MONTH FROM website_sessions.created_at) AS yearmonth,
3. COUNT(DISTINCT CASE WHEN device_type = 'desktop' THEN
website_sessions.website_session_id ELSE NULL END) AS desktop_sessions,
4. COUNT(DISTINCT CASE WHEN device_type = 'desktop' THEN orders.order_id
ELSE NULL END) AS desktop_orders,
5. ROUND(COUNT(DISTINCT CASE WHEN device_type = 'desktop' THEN
orders.order_id ELSE NULL END) /
6
Alfikri Ramadhan eCommerce Analysis with SQL
6. COUNT(DISTINCT CASE WHEN device_type = 'desktop' THEN
website_sessions.website_session_id ELSE NULL END) * 100.0, 2) AS
desktop_cvr,
7. COUNT(DISTINCT CASE WHEN device_type = 'mobile' THEN
website_sessions.website_session_id ELSE NULL END) AS mobile_sessions,
8. COUNT(DISTINCT CASE WHEN device_type = 'mobile' THEN orders.order_id
ELSE NULL END) AS mobile_orders,
9. ROUND(COUNT(DISTINCT CASE WHEN device_type = 'mobile' THEN
orders.order_id ELSE NULL END) /
10. COUNT(DISTINCT CASE WHEN device_type = 'mobile' THEN
website_sessions.website_session_id ELSE NULL END) * 100.0, 2) AS
mobile_cvr
11. FROM website_sessions
12. LEFT JOIN orders
13. ON website_sessions.website_session_id = orders.website_session_id
14. WHERE utm_source = 'gsearch'
15. AND utm_campaign = 'nonbrand'
16. AND website_sessions.created_at < '2012-11-27'
17. GROUP BY 1;
Result:
Insight:
Majority of traffic sources are coming from users on desktop. Both desktop and mobile shows
increased conversion rate from March to November 2012. Investigate why there are less
session and orders from users who access through mobile, look into the the mobile webpages
user interface and experience.
7
Alfikri Ramadhan eCommerce Analysis with SQL
Q4. I'm worried that one of our more pessimistic board members may be concerned about the
large % of traffic from gsearch. Can you pull monthly trends for gsearch, alongside monthly
trends for each of our other channels?
First, find the various utm sources and referers to see the traffic we're getting.
1. SELECT DISTINCT
2. utm_source,
3. utm_campaign,
4. http_referer
5. FROM website_sessions
6. WHERE website_sessions.created_at < '2012-11-27';
Result:
• If utm_source and utm_campaign IS NULL and http_referer IS NOT NULL, it means the
sessions come from organic search sessions.
• If utm_source and utm_campaign IS NULL and http_referer IS NULL, it means the sessions
come directly from the web / users directly type the website link.
1. SELECT
2. EXTRACT(YEAR_MONTH FROM website_sessions.created_at) AS yearmonth,
3. COUNT(website_sessions.website_session_id) AS sessions,
4. COUNT(DISTINCT CASE WHEN utm_source = 'gsearch' AND http_referer IS NOT
NULL THEN website_sessions.website_session_id ELSE NULL END) AS
gsearch_paid_sessions,
5. COUNT(DISTINCT CASE WHEN utm_source = 'bsearch' AND http_referer IS NOT
NULL THEN website_sessions.website_session_id ELSE NULL END) AS
bsearch_paid_sessions,
6. 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,
7. 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_sessions
8. FROM website_sessions
9. LEFT JOIN orders
10. ON website_sessions.website_session_id = orders.website_session_id
11. WHERE website_sessions.created_at < '2012-11-27'
12. GROUP BY 1;
8
Alfikri Ramadhan eCommerce Analysis with SQL
Result:
Insight:
Number of sessions keep growing every month. Large portion of sessions come from gsearch,
starting at 99% at March though it starts to decreased and contribute to 70% of total sessions
in November. Bsearch traffic starts to grow in August, contributing to 12% of total and reach its
highest in November at 22% of total sessions.
Q5. I'd like to tell the story of our website performance improvements over the course of the
first 8 months. Could you pull session to order conversion rates, by month?
1. SELECT
2. EXTRACT(YEAR_MONTH FROM website_sessions.created_at) AS yearmonth,
3. COUNT(DISTINCT website_sessions.website_session_id) AS sessions,
4. COUNT(DISTINCT order_id) AS orders,
5. ROUND(COUNT(DISTINCT order_id) / COUNT(DISTINCT
website_sessions.website_session_id)*100.0, 2) AS conversion_rate
6. FROM website_sessions
7. LEFT JOIN orders
9
Alfikri Ramadhan eCommerce Analysis with SQL
8. ON website_sessions.website_session_id = orders.website_session_id
9. WHERE website_sessions.created_at < '2012-11-27'
10. GROUP BY 1;
Result:
Insight:
The conversion rate has been growing steadily, from 3.19% in March to 4.40% in November.
Q6. For the gsearch lander test, please estimate the revenue that test earned us (Hint: Look at
the increase in CVR from the test (Jun 19 - Jul 28), and use nonbrand sessions and revenue
since then to calculate incremental value)
First, we search the first website pageview id for /lander-1 url.
1. SELECT
2. MIN(website_pageview_id) AS first_test_pv
3. FROM website_pageviews
4. WHERE pageview_url = '/lander-1';
10
Alfikri Ramadhan eCommerce Analysis with SQL
Result:
The first website pageview id for /lander-1 test page is 23504.
1. SELECT
2. website_pageviews.pageview_url AS landing_page,
3. COUNT(DISTINCT website_sessions.website_session_id) AS sessions,
4. COUNT(DISTINCT orders.order_id) AS orders,
5. ROUND(COUNT(DISTINCT orders.order_id)/
6. COUNT(DISTINCT website_sessions.website_session_id) * 100.0,2) AS
conversion_rate
7. FROM website_sessions
8. INNER JOIN website_pageviews
9. ON website_sessions.website_session_id =
website_pageviews.website_session_id
10. LEFT JOIN orders
11. ON website_sessions.website_session_id = orders.website_session_id
12. WHERE website_pageviews.website_pageview_id >= 23504
13. AND website_sessions.created_at < '2012-07-28'
14. AND website_sessions.utm_source = 'gsearch'
15. AND website_sessions.utm_campaign = 'nonbrand'
16. AND website_pageviews.pageview_url IN ('/home', '/lander-1')
17. GROUP BY website_pageviews.pageview_url;
Result:
• Homepage lander conversion rate is 3.18%, while new test lander page conversion rate is
4.06%. The conversion rate is increased by 0.88%.
• To calculate estimate revenue generated by new test lander page, first we find the last
time /home page appeared, then we count the total sessions since that.
1. SELECT
2. MAX(website_sessions.website_session_id) AS
most_recent_gsearch_nonbrand_home_pageview
3. FROM website_sessions
4. LEFT JOIN website_pageviews
5. ON website_sessions.website_session_id =
website_pageviews.website_session_id
6. WHERE utm_source = 'gsearch'
7. AND utm_campaign = 'nonbrand'
8. AND pageview_url = '/home' -- Home landing page
9. AND website_sessions.created_at < '2012-11-27';
11
Alfikri Ramadhan eCommerce Analysis with SQL
Result:
• Max website_session_id for /home is 17145.
• After this session, there are no more /home landing page, and all landing page has been
replaced with /lander-1.
1. SELECT
2. COUNT(website_session_id) AS sessions_since_test
3. FROM website_sessions
4. WHERE
5. created_at < '2012-11-27'
6. AND website_session_id >= 17145 -- last home session
7. AND utm_source = 'gsearch'
8. AND utm_campaign = 'nonbrand';
Calculate Average Revenue:
• Conversion rate difference: 0.88%
• Total of sessions using /lander-1 = 22,973
• 22,973 x 0.88% = estimated at least 202 incremental orders since July 29 using /lander-1
page for roughly 4 months
• 202/4 = 50 additional orders per month. Awesome!!
Q7. For the landing page test you analyzed previously, it would be great to shows a full
conversion funnel from each of the two pages to orders. You can use the same time period
you analyzed last time (Jun 19 - Jul 28)
12
Alfikri Ramadhan eCommerce Analysis with SQL
1. SELECT
2. MIN(website_pageview_id) AS first_test_pv
3. FROM website_pageviews
4. WHERE pageview_url = '/lander-1';
5.
6. -- First test lander-1 pageviews is 23504
7.
8. SELECT
9. website_sessions.website_session_id,
10. website_pageviews.pageview_url,
11. website_pageviews.created_at AS pageview_created_at,
12. CASE WHEN pageview_url = '/home' THEN 1 ELSE 0 END AS home_page,
13. CASE WHEN pageview_url = '/lander-1' THEN 1 ELSE 0 END AS lander1_page,
14. CASE WHEN pageview_url = '/products' THEN 1 ELSE 0 END AS product_page,
15. CASE WHEN pageview_url = '/the-original-mr-fuzzy' THEN 1 ELSE 0 END AS
mrfuzzy_page,
16. CASE WHEN pageview_url = '/cart' THEN 1 ELSE 0 END AS cart_page,
17. CASE WHEN pageview_url = '/shipping' THEN 1 ELSE 0 END AS
shipping_page,
18. CASE WHEN pageview_url = '/billing' THEN 1 ELSE 0 END AS billing_page,
19. CASE WHEN pageview_url = '/thank-you-for-your-order' THEN 1 ELSE 0 END
AS thankyou_page
20. FROM website_sessions
21. LEFT JOIN website_pageviews
22. ON website_sessions.website_session_id =
website_pageviews.website_session_id
23. WHERE
24. website_sessions.utm_source = 'gsearch'
25. AND website_sessions.utm_campaign = 'nonbrand'
26. AND website_pageview_id >= 23504
27. AND website_pageviews.created_at < '2012-07-28'
28. AND website_pageviews.pageview_url IN ('/home', '/lander-1',
'/products', '/the-original-mr-fuzzy', '/cart', '/shipping', '/billing',
'/thank-you-for-your-order')
29. ORDER BY
30. website_sessions.website_session_id,
31. website_pageviews.created_at
32. ;
33.
34. -- next we will put the previous query inside a subquery (similar to
temporary tables)
35. -- we will group by website_session_id, and take the MAX() of each of the
flags
36. -- this MAX() becomes a made it flag for that session, to show the
session made it there
37.
38. CREATE TEMPORARY TABLE session_level_made_it_flags
39. SELECT
40. website_session_id,
41. MAX(homepage) AS saw_homepage,
42. MAX(custom_lander) AS saw_custom_lander,
43. MAX(product_page) AS product_made_it,
44. MAX(mrfuzzy_page) AS mrfuzzy_made_it,
45. MAX(cart_page) AS cart_made_it,
46. MAX(shipping_page) AS shipping_made_it,
13
Alfikri Ramadhan eCommerce Analysis with SQL
47. MAX(billing_page) AS billing_made_it,
48. MAX(thankyou_page) AS thankyou_made_it
49. FROM(
50. SELECT
51. website_sessions.website_session_id,
52. website_pageviews.pageview_url,
53. website_pageviews.created_at AS pageview_created_at,
54. CASE WHEN pageview_url = '/home' THEN 1 ELSE 0 END AS homepage,
55. CASE WHEN pageview_url = '/lander-1' THEN 1 ELSE 0 END AS
custom_lander,
56. CASE WHEN pageview_url = '/products' THEN 1 ELSE 0 END AS product_page,
57. CASE WHEN pageview_url = '/the-original-mr-fuzzy' THEN 1 ELSE 0 END AS
mrfuzzy_page,
58. CASE WHEN pageview_url = '/cart' THEN 1 ELSE 0 END AS cart_page,
59. CASE WHEN pageview_url = '/shipping' THEN 1 ELSE 0 END AS
shipping_page,
60. CASE WHEN pageview_url = '/billing' THEN 1 ELSE 0 END AS billing_page,
61. CASE WHEN pageview_url = '/thank-you-for-your-order' THEN 1 ELSE 0 END
AS thankyou_page
62. FROM website_sessions
63. LEFT JOIN website_pageviews
64. ON website_sessions.website_session_id =
website_pageviews.website_session_id
65. WHERE
66. website_sessions.utm_source = 'gsearch'
67. AND website_sessions.utm_campaign = 'nonbrand'
68. AND website_pageviews.created_at < '2012-07-28'
69. AND website_pageviews.created_at > '2012-06-19'
70. ORDER BY
71. website_sessions.website_session_id,
72. website_pageviews.created_at
73. ) AS pageview_level
74. GROUP BY 1;
75.
76. SELECT *
77. FROM session_level_made_it_flags;
78.
79. -- then this will produce the final output (part 1)
80.
81. SELECT
82. CASE
83. WHEN saw_homepage = 1 THEN 'saw_homepage'
84. WHEN saw_custom_lander = 1 THEN 'saw_custom_lander'
85. ELSE 'uh oh... check logic'
86. END AS segment,
87. COUNT(DISTINCT website_session_id) AS sessions,
88. COUNT(DISTINCT CASE WHEN product_made_it = 1 THEN website_session_id
ELSE NULL END) AS to_products,
89. COUNT(DISTINCT CASE WHEN mrfuzzy_made_it = 1 THEN website_session_id
ELSE NULL END) AS to_mrfuzzy,
90. COUNT(DISTINCT CASE WHEN cart_made_it = 1 THEN website_session_id ELSE
NULL END) AS to_cart,
91. COUNT(DISTINCT CASE WHEN shipping_made_it = 1 THEN website_session_id
ELSE NULL END) AS to_shipping,
14
Alfikri Ramadhan eCommerce Analysis with SQL
92. COUNT(DISTINCT CASE WHEN billing_made_it = 1 THEN website_session_id
ELSE NULL END) AS to_billing,
93. COUNT(DISTINCT CASE WHEN thankyou_made_it = 1 THEN website_session_id
ELSE NULL END) AS to_thankyou
94. FROM
95. session_level_made_it_flags
96. GROUP BY 1;
97.
98. -- then this is the final output part 2, click rates or conversion rates
99. -- click rates or conversion rates is percentage of click rate from
certain page divided by total sessions
100.
101. SELECT
102. CASE
103. WHEN saw_homepage = 1 THEN 'saw_homepage'
104. WHEN saw_custom_lander = 1 THEN 'saw_custom_lander'
105. ELSE 'uh oh... check logic'
106. END AS segment,
107. ROUND(COUNT(DISTINCT CASE WHEN product_made_it = 1 THEN
website_session_id ELSE NULL END) /
108. COUNT(DISTINCT website_session_id) * 100.0, 2) AS products_click_rt,
109. ROUND(COUNT(DISTINCT CASE WHEN mrfuzzy_made_it = 1 THEN
website_session_id ELSE NULL END) /
110. COUNT(DISTINCT website_session_id) * 100.0, 2) AS mrfuzzy_click_rt,
111. ROUND(COUNT(DISTINCT CASE WHEN cart_made_it = 1 THEN
website_session_id ELSE NULL END) /
112. COUNT(DISTINCT website_session_id) * 100.0, 2) AS cart_click_rt,
113. ROUND(COUNT(DISTINCT CASE WHEN shipping_made_it = 1 THEN
website_session_id ELSE NULL END) /
114. COUNT(DISTINCT website_session_id) * 100.0, 2) AS shipping_click_rt,
115. ROUND(COUNT(DISTINCT CASE WHEN billing_made_it = 1 THEN
website_session_id ELSE NULL END) /
116. COUNT(DISTINCT website_session_id) * 100.0, 2) AS billing_click_rt,
117. ROUND(COUNT(DISTINCT CASE WHEN thankyou_made_it = 1 THEN
website_session_id ELSE NULL END) /
118. COUNT(DISTINCT website_session_id) * 100.0, 2) AS thankyou_click_rt
119. FROM
120. session_level_made_it_flags
121. GROUP BY 1;
Result:
• Output 1: Sessions Funnel
• Output 2: Click-rates Funnel
15
Alfikri Ramadhan eCommerce Analysis with SQL
Q8. I'd love for you to quantify the impact of our billing test, as well. Please analyze the lift
generated from the test (Sep 10 - Nov 10), in terms of revenue per billing page sessions, and
then pull the number of billing page sessions for the past month to understand monthly
impact.
1. SELECT
2. billing_version_seen,
3. COUNT(DISTINCT website_session_id) AS sessions,
4. ROUND(SUM(price_usd) / COUNT(DISTINCT website_session_id), 2) AS
revenue_per_session
5. FROM
6. (
7. SELECT
8. website_pageviews.website_session_id,
9. website_pageviews.pageview_url AS billing_version_seen,
10. orders.order_id,
11. orders.price_usd
12. FROM website_pageviews
13. LEFT JOIN orders
14. ON website_pageviews.website_session_id = orders.website_session_id
15. WHERE
16. website_pageviews.created_at BETWEEN '2012-09-10' and '2012-11-10'
17. AND website_pageviews.pageview_url IN ('/billing', '/billing-2')
18. ) AS billing_pageviews_and_order_data
19. GROUP BY 1;
Result:
• /billing page generates 657 sessions, with average USD 22,83 revenue per session
• /billing-2 page generates 654 sessions, with average USD 31,34 revenue per session
• INCREASE: USD 8.51 per session
1. SELECT
2. COUNT(website_session_id) AS billing_sessions_past_month
3. FROM website_pageviews
4. WHERE created_at BETWEEN '2012-10-27' AND '2012-11-27'
5. AND pageview_url IN ('/billing', '/billing-2');
Result:
• /billing page USD 22,83 revenue per session and new /billing-2 page generates USD 31,34
revenue per session. The lift is USD 8.51 per session.
16
Alfikri Ramadhan eCommerce Analysis with SQL
• Over the past month there has been 1,193 sessions. The new page has generated USD
10,153 increase in revenue.
5. Insight and Recommendation
5.1. Insight:
1. The website performance has seen improvements over the course of the first 8 months.
The conversion rate starts at 3.19% in March and reached 4.40% in November.
2. Most of our traffic comes from users who access from desktop, almost 3/4 of traffic
comes from desktop, while the rest comes from mobile.
3. In March, 99% of our traffic comes from gsearch. From August to November, our traffic
sources are more diverse; 70% of it comes from gsearch, 22% comes from bsearch, and
8% comes from direct and organic.
4. The new c test shows better conversion rate compared to original /home page. The
conversion rate rose from 3.18% to 4.06%, adding an increase of 0.88%. The /lander-
1 page generated additional 50 orders per month and shows better click rates funnel.
5. The new /billing-2 test page also shows better result than the previous /billing page,
which brought additional USD 8.51 revenue per session. For the past month, there has
been 1,193 session total, and the new billing page brought a total of USD 10,153
increase in revenue.
5.2. Recommendation:
1. With most of our users coming from desktop, we can focus our campaign and budget to
desktop users. Additionally, evaluate the mobile webpage and find why the traffic is low,
then create better user interface and experience for mobile users.
2. For paid marketing campaigns, most of the sessions come from gsearch than bsearch.
We can focus our budget and campaign to gsearch for higher sessions reach in the
future.
3. Based on A/B test on landing page and billing test, the new /billing-2 landing page and
/billing-2 billing page shows better conversion rate and revenue. We can continue use
these page for our website.
17
