-
Notifications
You must be signed in to change notification settings - Fork 0
/
customer_analytics_report.sql
145 lines (121 loc) · 4.39 KB
/
customer_analytics_report.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
-- Project: Customer Analytics Report on Q1 and Q2 Period
-- Dataset:
---Table orders_1 contains data related to sales transactions for the quarter 1 period
---Table orders_2 contains data related to sales transactions for the quarter 2 period
---Table customer contains customer profile data who register to become company customers
-- Project Task:
--- 1. Analyze the current sales growth and customer growth.
--- 2. Analyze the number of transactions and most-purchased product category.
--- 3. Analyze customer retention.
--------------------------------------------------------------------------------------------------
-- 1. Calculate total sales percentage
SELECT
quarter,
sum(quantity) as total_penjualan,
sum(quantity*priceeach) as revenue
FROM
(SELECT
orderNumber,
status,
quantity,
priceeach,
'1' as quarter
FROM
orders_1
WHERE
status = 'Shipped'
UNION
SELECT
orderNumber,
status,
quantity,
priceeach,
'2' as quarter
FROM
orders_2
WHERE
status = 'Shipped'
) as tabel_a
GROUP BY quarter
---Insight: There was a decrease in Q1-Q2, -22% in sales and -24% in revenue
-- 2. Calculate number of new customers in Q1 and Q2
SELECT
quarter,
count(distinct customerid) as total_customers
FROM
(SELECT
customerID,
createDate,
quarter(createDate) as quarter
FROM
customer
WHERE
createdate between '2004-01-01' and '2004-06-30'
) as tabel_b
GROUP BY quarter
---Insight: The number of new customers in Q2 is 8 less than in Q1
-- 3. Calculate number of new customer who made transaction in Q1 and Q2
SELECT
quarter,
count(distinct customerid) as total_customers
FROM
(SELECT
customerid,
createdate,
quarter(createdate) as quarter
FROM
customer
WHERE createdate between '2004-01-01' and '2004-06-30'
) as tabel_b
WHERE customerid IN(SELECT distinct customerid
FROM orders_1
UNION
SELECT distinct customerid
FROM orders_2)
GROUP BY quarter
---Insight: The number of new customer who made transaction in Q2 is less than in Q1
-- 4. Inspect the most-frequent product category ordered by customers in Q2
SELECT *
FROM (SELECT
categoryID,
COUNT(DISTINCT orderNumber) AS total_order,
SUM(quantity) AS total_penjualan
FROM (SELECT
productCode,
orderNumber,
quantity,
status,
LEFT(productCode,3) AS categoryID
FROM orders_2
WHERE status = "Shipped"
) tabel_c
GROUP BY categoryID ) a
ORDER BY total_order DESC
---Insight: Top 3 product categories with the most total orders are S18, S24 and S32
---- with total sales > 600 while other product categories received total sales of < 600
-- 5. Calculate percentage of Q1 customer who made repeat order in Q2
SELECT
COUNT(DISTINCT customerid) as total_customers
FROM orders_1 ; -- total customer = 25
SELECT
'1' as quarter,
(COUNT(DISTINCT customerid)/25)*100 as Q2
FROM
orders_1
WHERE customerid IN(SELECT distinct customerid
FROM orders_2)
---Insight: 76% of customers from Q1 did not repeat orders,
----resulting a decrease in sales in Q2
-------------------------------------------------------------------------------------------------------
-- Conclusion:
---1. Company's performance decreased significantly in Q2, as seen from the value of sales and revenue which dropped by 22% and 24%.
---2. The acquisition of new customers is also not very good, and slightly decreased compared to the previous quarter.
---3. The interest of new customers to purchase is still lacking, only about 56% have made transactions.
------It is recommended that the Product team need to study customer behavior and make product improvements,
------so that the conversion rate (register to transaction) can increase.
---4. Product categories S18 and S24 contribute about 50% of total orders and 60% of total sales,
------so company should focus on developing S18 and S24 product category.
---5. Company's customer retention rate is also very low, only 24%, it means that many customers who have transacted in Q1 do not return to order in Q2 (no repeat orders).
---6. The company experienced negative growth in Q2 and needs to make a lot of improvements both in terms of products and marketing business,
------so hopefully can achieve the target and have positive growth in the Q3.
------The low retention rate and conversion rate can be an early diagnosis that customers are not interested/unsatisfied/disappointed in shopping at the company.