Data Analytics Using SQL: Customer Behavior and Shopping Habits
Outline: Descriptive Analysis, Performance Sales & Growth Analysis, Promotional Cost Efficiency Analysis, Customer Retention Analysis, and Customer Behavior Analysis
Hello! Good morning! Good day! Good evening! Whenever and wherever you are!
I am Hafez Afghan, a data enthusiast. On this occasion, I want to show you my project from SQL Bootcamp that I attended recently, held by GenggamData. This project aims to serve analytical and visualization statistics regarding the data. For more content like this, please follow my profile. Thank you!
Dataset Brief
The Consumer Behavior and Shopping Habits Dataset, a dummy dataset, provides comprehensive insights into consumers’ preferences, tendencies, and patterns during their shopping experience. This dataset, recorded in 2023, covers a wide array of variables, including demographic information, purchase history, product preferences, shopping frequency, and the rating review of the product.
Outline
There are 5 outlines in this project:
- Descriptive Analysis
- Performance Sales & Growth Analysis
- Promotional Cost Efficiency Analysis
- Customer Retention Analysis
- Customer Behavior Analysis
Let’s move on to the case!
1. Descriptive Analysis
In this chapter, we provide an overview of the dataset, including the data source, number of observations, available variables, and data structure. The goal is to understand the basic characteristics of the dataset before starting further analysis.
a. How many transactions occurred?
The number of transactions is the exact number of rows in our dataset. Therefore, we can use COUNT(*), which returns the number of rows.
b. How many unique customers?
In the dataset, each customer did a transaction more than one time. Therefore, we can use COUNT(DISTINCT customer_id) to return the unique count of customer ID. DISTINCT filters unique values within a column by returning each value only once, eliminating duplicates. Then, COUNT returns the count of the DISTINCT output.
c. What was the total revenue earned?
Revenue is determined by purchase amount and discount. Having this in mind, we can use CASE WHEN … THEN … ELSE … END to divide into 2 cases explained. So, if the customer did apply the discount, the particular revenue is purchase amount * (100-discount)/100. In contrast, if the customer did not apply the discount, the particular revenue is the purchase amount. Next, we can use SUM() to sum up all the cases.
d. How many product categories are sold?
Same as point b, we can use DISTINCT to find the unique values of categories. Then, we use COUNT() to count the outputs.
e. How many products are sold?
Same as point b, we can use DISTINCT to find the unique values of products. Then, we use COUNT() to count the outputs.
f. What was the average age of customers?
We can use the statistical calculation, average, to find the mean of customer age. In SQL, we can use AVG() to find such desired values.
All of the questions can be answered using the following codes.
SELECT COUNT(*) AS transaction_count,
COUNT(DISTINCT customer_id) AS customer_count,
ROUND(SUM(CASE WHEN discount_applied = 'Yes'
THEN purchase_amount_usd*(100-discount)/100
ELSE purchase_amount_usd END),2) AS revenue,
COUNT(DISTINCT category) AS category_count,
COUNT(DISTINCT item_purchased) AS product_count,
ROUND(AVG(age),0) AS average_age
FROM customer_transaction;
Sometimes, we utilize ROUND() to round to the nearest value. For example, question 3 is answered by using ROUND(the value, 2). The second order (the value 2) represents the return value, which displays 2 digits after the dot. AS is used to name the column or variable with a preferred name. Running the code, we obtain the result in the following table.
Based on the table above, it can be noted that the total number of transactions is 3900 transactions made by 551 customers, so on average, each customer has made 7 transactions in 2023. However, with the large variation in the data, some customers made transactions below 7 times or above 7 times. The large number of transactions also has an impact on the amount of revenue for the GenggamData Store, which totaled $129,119.92. In addition, the number of product categories offered is 4, including accessories, clothing, footwear, and outwear, with a total of 25 products. Last but not least, the average age of customers is around 44 years old, which indicates that most customers of GenggamData Store are adults.
2. Performance Sales & Growth Analysis
In this chapter, we analyze sales performance and business growth over time. This involves understanding sales trends, factors affecting growth, and identifying opportunities or challenges that may arise.
a. How was the overall performance of GenggamData Store for the number of orders and total sales through the months?
To extract the month value from the order_date variable, we can use EXTRACT(MONTH FROM order_date). This code gives us the month value in its order_date. Unlike revenue, sales are not affected by discounts, so we can just add up the purchase amount. Note that we have to aggregate the number of orders and total sales by month, so we utilized GROUP BY month to aggregate the functions into each month and ORDER BY month to sort the rows by month. Based on these explanations, we use the code below.
SELECT EXTRACT(MONTH FROM order_date) AS month,
COUNT(*) AS jumlah_order,
SUM(purchase_amount_usd) AS total_sales
FROM customer_transaction
GROUP BY month
ORDER BY month;
For ease of understanding, we drew graphs of the output obtained.
It can be noted that neither the number of orders nor the total sales experienced an upward trend (which is the expected condition). Thus, GenggamData Store should intensify the promotion and advertising of its products to boost the number of orders and total sales. In addition, the number of orders and total sales are directly proportional. As can be seen in the figures above, the two plots are related; when the number of orders increases, the total sales also increase, and vice versa. In addition, the peak of the number of orders and total sales was in October, with 357 orders and $21,792 in sales, respectively. Thus, GenggamData Store should prepare for higher operational needs for October next year than in other months.
b. How was the overall performance of GenggamData Store for the number of orders and total sales based on product category through the months?
This time, we do the same thing (as point a) but distinguish by product category. Therefore, we can add the category in the GROUP BY clause.
SELECT EXTRACT(MONTH FROM order_date) AS month,
category,
COUNT(*) AS number_of_order,
SUM(purchase_amount_usd) AS total_sales
FROM customer_transaction
GROUP BY month, category
ORDER BY month;
For ease of understanding, we drew graphs of the output obtained.
It can be noted that neither the number of orders nor the total sales of each category experienced an upward trend (which is the expected condition). Thus, GenggamData Store should intensify the promotion and advertising of their products (both overall and by category) to boost the number of orders and total sales. In addition, the number of orders and total sales are directly proportional for each category. It can be noticed in the 2 figures above that the two plots are interconnected; when the number of orders increases, the total sales also increase, and vice versa.
In contrast to the overall, the peak number of orders and total sales by category varied. In the accessories category, the peak number of bookings was in October at 114 orders, and the peak total sales was in August at $6,760. In the clothing category, both the peak number of orders and total sales were in October, amounting to 159 orders and $9,903. In the footwear category, the peak number of orders was in December at 55 orders, and the peak total sales was in August at $3,386. In the outwear category, the peak number of orders was in November at 34 orders, and the peak total sales was in October at $1,928. Thus, GenggamData Store must prepare higher operational needs (for each product category) for these months than the other months in the following years.
c. How was the growth of orders and sales of GenggamData Store through the months?
The formula for growth order and growth sales are expressed in the following.
Growth order = ((order of the observed period/order of the period before the observed)-1) * 100
Growth sales = ((sales of the observed period/sales of the period before the observed)-1) * 100
Note that we are required to have the value of the period before the observed which will be compared with the value of the observed period. Having this issue, we can utilize the window function: LAG(), which can slide the value of a variable 1-step ahead.
SELECT EXTRACT(MONTH FROM order_date) AS month,
ROUND(((CAST(COUNT(customer_id) as decimal)/(LAG(COUNT(customer_id))
OVER(ORDER BY EXTRACT(MONTH FROM order_date))))-1)*100,2) AS growth_order,
ROUND(((CAST(SUM(purchase_amount_usd) as decimal)/(LAG(SUM(purchase_amount_usd))
OVER(ORDER BY EXTRACT(MONTH FROM order_date))))-1)*100,2) AS growth_sales
FROM customer_transaction
GROUP BY month;Clause OVER(ORDER BY month) should be written after clause LAG() to define the window function. ORDER BY month is to slide the value based on a sequence of months.
For ease of understanding, we drew graphs of the output obtained.
A good growth order and growth sales are at least more than 0%, which means that orders and sales in a particular month increased compared to the previous month. In reality, several months have growth orders and growth sales below 0%, indicating that in those months, both orders and sales have not increased from the previous month. Thus, GenggamData Store must intensify product promotion and advertising to boost growth orders and growth sales.
d. How was the growth of orders and sales of GenggamData Store based on product category through the months?
This time, we do the same thing (as point c) but distinguish by product category. Therefore, we can add the category in the GROUP BY clause.
SELECT EXTRACT(MONTH FROM order_date) AS month,
ROUND(((CAST(COUNT(customer_id) as decimal)/(LAG(COUNT(customer_id))
OVER(ORDER BY EXTRACT(MONTH FROM order_date))))-1)*100,2) AS growth_order,
ROUND(((CAST(SUM(purchase_amount_usd) as decimal)/(LAG(SUM(purchase_amount_usd))
OVER(ORDER BY EXTRACT(MONTH FROM order_date))))-1)*100,2) AS growth_sales
FROM customer_transaction
GROUP BY month, category;
For ease of understanding, we drew graphs of the output obtained.
A good growth order and growth sales are at least more than 0%, which means that orders and sales in a particular month increased compared to the previous month. In reality, in each category, several months have growth orders and growth sales below 0%, indicating that in those months, orders and sales did not increase from the previous month in each category. Thus, GenggamData Store must intensify the promotion and advertising of its products to boost growth orders and growth sales for each category.
3. Promotional Cost Efficiency Analysis
In this chapter, we measure the efficiency of promotional costs and analyze the relationship between promotional costs and sales results. The goal is to evaluate which promotional expenditures can provide additional value proportional to the results achieved.
a. The effectiveness and efficiency of promotions were evaluated by calculating the burn rate of promotions implemented by month.
The formula for the burn rate percentage of promotions is expressed in the following.
Burn rate (%) = (total discount / total sales) * 100%
Based on this formula, we can use the following code.
SELECT EXTRACT(MONTH FROM order_date) AS month,
SUM(purchase_amount_usd) AS sales,
ROUND(SUM(purchase_amount_usd*discount/100), 2) AS promotion_value,
ROUND((((SUM(purchase_amount_usd*discount/100))/SUM(purchase_amount_usd))*100), 2) AS burn_rate_percentage
FROM customer_transaction
GROUP BY month
ORDER BY month;
For ease of understanding, we drew graphs of the output obtained.
Management expects the burn rate percentage to be kept at a maximum of 43%. A burn rate percentage that is too high can result in revenue not being maximized due to excessive discounts. But in reality, almost all monthly burn rate percentages were above 43%. Thus, GenggamData Store needs to re-evaluate the policy of offering discounts on each product they provide.
b. The effectiveness and efficiency of promotions are evaluated by calculating the burn rate of promotions implemented by product category and month.
This time, we do the same thing (as point a) but distinguish by product category. Therefore, we can add the category in the GROUP BY clause.
SELECT EXTRACT(MONTH FROM order_date) AS month,
category,
SUM(purchase_amount_usd) AS sales,
ROUND(SUM(purchase_amount_usd*discount/100), 2) AS promotion_value,
ROUND((((SUM(purchase_amount_usd*discount/100))/SUM(purchase_amount_usd))*100), 2) AS burn_rate_percentage
FROM customer_transaction
GROUP BY month, category
ORDER BY month;
For ease of understanding, we drew graphs of the output obtained.
Including each category, management expects the burn rate percentage to remain at a maximum of 43%. But in reality, almost all monthly burn rate percentages were over 43% in each category. Thus, GenggamData Store needs to re-evaluate the policy of providing discounts on each product category offered.
4. Customer Retention Analysis
In this chapter, we assess how well the business can retain existing customers. This analysis involves customer retention, factors that affect retention, and identifying strategies to improve customer retention.
a. How is customer retention in purchasing items at GenggamData Store by month? (show values in percentage)
Retention rate is quantitative data expressed as a percentage related to the number of customers who become loyal or continue to make transactions in the following periods. The formula for the retention rate of customers is expressed in the following.
Retention rate (%) = ((number of customers at the end of period - number of customers acquired during period) / number of customers at the start of period) * 100%
The easiest way to find the retention rate is by utilizing cohort analysis. Cohort analysis works by measuring how many customers made transactions at the start of the period (the denominator) and how many customers still made transactions in subsequent periods (the numerator). Thus, the algorithm is by setting the cohort table and calculating the rates. I know it’s a bit complicated, so you may want to check my new article about how this works comprehensively using SQL.
WITH
first_purchased_month AS (
SELECT customer_id,
MIN(EXTRACT(MONTH FROM order_date)) AS first_month
FROM customer_transaction
GROUP BY customer_id
),
purchased_month AS(
SELECT DISTINCT customer_id,
EXTRACT(MONTH FROM order_date) AS month
FROM customer_transaction
),
join_12 AS
(
SELECT purchased_month.customer_id, purchased_month.month,
first_purchased_month.first_month,
purchased_month.month - first_purchased_month.first_month AS month_number
FROM purchased_month
JOIN first_purchased_month
ON purchased_month.customer_id = first_purchased_month.customer_id
),
cohort_table AS
(
SELECT first_month,
SUM(CASE WHEN month_number = 0 THEN 1 ELSE 0 END) AS m0,
SUM(CASE WHEN month_number = 1 THEN 1 ELSE 0 END) AS m1,
SUM(CASE WHEN month_number = 2 THEN 1 ELSE 0 END) AS m2,
SUM(CASE WHEN month_number = 3 THEN 1 ELSE 0 END) AS m3,
SUM(CASE WHEN month_number = 4 THEN 1 ELSE 0 END) AS m4,
SUM(CASE WHEN month_number = 5 THEN 1 ELSE 0 END) AS m5,
SUM(CASE WHEN month_number = 6 THEN 1 ELSE 0 END) AS m6,
SUM(CASE WHEN month_number = 7 THEN 1 ELSE 0 END) AS m7,
SUM(CASE WHEN month_number = 8 THEN 1 ELSE 0 END) AS m8,
SUM(CASE WHEN month_number = 9 THEN 1 ELSE 0 END) AS m9,
SUM(CASE WHEN month_number = 10 THEN 1 ELSE 0 END) AS m10,
SUM(CASE WHEN month_number = 11 THEN 1 ELSE 0 END) AS m11
FROM join_12
GROUP BY first_month
ORDER BY first_month
)
SELECT first_month AS cohort_month,
ROUND((m0/CAST(m0 AS decimal))*100,0) AS m0,
ROUND((m1/CAST(m0 AS decimal))*100,0) AS m1,
ROUND((m2/CAST(m0 AS decimal))*100,0) AS m2,
ROUND((m3/CAST(m0 AS decimal))*100,0) AS m3,
ROUND((m4/CAST(m0 AS decimal))*100,0) AS m4,
ROUND((m5/CAST(m0 AS decimal))*100,0) AS m5,
ROUND((m6/CAST(m0 AS decimal))*100,0) AS m6,
ROUND((m7/CAST(m0 AS decimal))*100,0) AS m7,
ROUND((m8/CAST(m0 AS decimal))*100,0) AS m8,
ROUND((m9/CAST(m0 AS decimal))*100,0) AS m9,
ROUND((m10/CAST(m0 AS decimal))*100,0) AS m10,
ROUND((m11/CAST(m0 AS decimal))*100,0) AS m11
FROM cohort_table;As you can see, the code is longer than the previous cases. To gain a cohort table, we need to set up a few other tables. Two of the tables are the customers’ first purchased month and the customers’ purchased months. Then, those tables are integrated, and the difference is calculated, resulting in a new table named join_12. Table join_12 is used to create the cohort table by utilizing the CASE clause (I explained this clause in Descriptive Analysis point c) to segregate the value of each month and to sum up each. Last, we create the retention table with the formula given above.
The higher the retention rate, the more loyal customers are in making transactions at the GenggamData Store. As an example of an explanation of the retention rate in January 2023, it is found that the retention rate is at an average of 47% and does not show any trend. It can be concluded that about 47% of customers who first made transactions in January 2023 returned to make transactions in the following months in 2023.
b. What is the churn rate by month? (show values in percentage)
The churn rate is quantitative data expressed as a percentage of the number of customers who become disloyal or do not make transactions in the following periods. The formula for churn rate is 100% - retention rate. Therefore, we can create a churn rate table by using the formula presented in the table below.
The lower the churn rate, the more loyal customers are in making transactions on the GenggamData Store. As an example of an explanation of the churn rate in January 2023, it is found that the churn rate is at an average of 53% and does not show any trend. It can be concluded that about 53% of customers who first made transactions in January 2023 did not return to make transactions in the following months in 2023.
5. Customer Behavior Analysis
In this chapter, we analyze customer behavior to understand shopping habits and product preferences. The goal is to provide insights about how customers interact with businesses and to support better marketing decision-making.
a. What is the revenue trend through the month?
Revenue is determined by purchase amount and discount. The logic is the same as Descriptive Analysis point c, but we have to add GROUP BY month and ORDER BY month so we can see the trend through the month.
SELECT EXTRACT(MONTH FROM order_date) AS month,
ROUND(SUM(CASE WHEN discount_applied = 'Yes'
THEN purchase_amount_usd*(100-discount)/100
ELSE purchase_amount_usd END),2) AS revenue
FROM customer_transaction
GROUP BY month
ORDER BY month;
For ease of understanding, we drew graphs of the output obtained.
Note that GenggamData Store’s revenue per month does not experience a significant upward trend (which is to be expected). However, there are peaks and valleys in monthly revenue. The peak is in October 2023, with revenue of $ 12,303.98. There was a decrease in revenue in February and June 2023, which amounted to $9,639.66 and $9,451.2, respectively. These months (with high and low revenues) can be related to products that are frequently purchased in October, and in February and June, customers prefer to save their money or use it for other needs.
b. What products are most frequently purchased by customers?
To obtain the result, we can use a window function called DENSE_RANK(). This function gives us the ranking within your ordered partition, but the ranks are consecutive. No ranks are skipped if there are ranks with multiple items.
SELECT DENSE_RANK() OVER(ORDER BY COUNT(item_purchased) DESC),
item_purchased,
COUNT(item_purchased) AS purchase_count
FROM customer_transaction
GROUP BY item_purchased;Of course, we should add OVER() to define DENSE_RANK() and rank in descending order of count of items purchased.
Note that the most frequently purchased products are jewelry, pants, and blouses, with 171 orders each. These products are more frequently purchased than other products, which can be explained by the fact that these products can be purchased from various people, are often used in daily life, are a trend or current fashion, or are often used as a gift to someone. In addition to these three products, other products that are often purchased are shirts, dresses, sweaters, and so on. In contrast, jeans are the least frequently purchased product at 124 orders. By analyzing this, GenggamData Store can make this a reference in preparing operational needs so that it can further boost sales of its products.
c. Are there any products that are preferred by customers based on their gender?
In the dataset, the variable gender consists of male and female. Then, using the same logic as point b, we add the CASE clause to segregate the value purchase count for each gender. After that, we can utilize DENSE_RANK() for ranking.
WITH
count_male_vs_female AS
(
SELECT item_purchased,
COUNT(CASE WHEN gender = 'Male' THEN customer_id END) AS male,
COUNT(CASE WHEN gender = 'Female' THEN customer_id END) AS female
FROM customer_transaction
GROUP BY item_purchased
),
male_rank AS
(
SELECT DENSE_RANK() OVER(ORDER BY male DESC) AS rank,
item_purchased, count_male_vs_female.male
FROM count_male_vs_female
),
female_rank AS
(
SELECT DENSE_RANK() OVER(ORDER BY female DESC) AS rank,
item_purchased, count_male_vs_female.female
FROM count_male_vs_female
)
-- Choose one
SELECT * FROM male_rank
SELECT * FROM female_rankNote in the last 2 rows of code above, we can choose one of the tables that we want to see (male rank or female rank).
It can be noted that the products most frequently purchased by women are blouses at 66 orders and shirts and sandals at 59 orders each. These products are more frequently purchased than other products, which can be explained by the fact that these products are often used in daily life and are a trend or fashion nowadays. In addition to these three products, other products that are often purchased by women are socks, handbags, sunglasses, and so on. In contrast, jeans are the least frequently purchased product, with 29 orders.
It can also be noted that the products most frequently purchased by men are pants at 123 orders and jewelry at 119 orders. These products are more frequently purchased than other products, which can be explained by the fact that these products are often used in everyday life, are a trend or fashion nowadays, or are often used as a gift to someone. In addition to these two products, other products that are often purchased by men are dresses, coats, sweaters, and so on. In contrast, boots are the least frequently purchased product at 94 orders.
Besides that, we can notice that men order more products from GenggamData Store, which indicates that either the majority of this store’s customers are men or men more often buy products for themselves and also as a gift to others. By analyzing this, GenggamData Store can make this a reference in preparing operational needs so that it can further boost sales of its products.
d. Does purchase count affect their rating to GenggamData Store?
The number of orders based on their rating can be calculated by using the COUNT(review_rating) so that we can use the following code.
SELECT COUNT(review_rating) AS purchase_count,
review_rating
FROM customer_transaction
GROUP BY review_rating;
To check the correlation between the number of transactions and rating, we conduct hypothesis testing using Pearson correlation.
As can be seen in the scatter plot above, there is no particular pattern between the purchase count and review rating values. Thus, it indicates that there is no correlation between them. However, it can be seen that the points are in a specific area, which is the review rating between 2.5 and 5, and the purchase count between 130 and 180 transactions. Meanwhile, there are 2 outliers in which only a few transactions give a rating of 5 and 2.5. In addition, Pearson correlation testing was conducted. The correlation between purchase count and review rating is -0.00038, so the two data are negatively correlated, although very small. The larger the purchase count, the smaller the review rating. However, this cannot be used as a conclusion, as the Pearson correlation test shows a p-value = 0.999 > alpha = 0.05. This leads to the result that the two data are not significantly correlated. Thus, it can be concluded that purchase count and review rating are not correlated.
e. How often do customers use discounts?
For this question, we can answer by showing the number of transactions using discount and its percentages.
SELECT COUNT(discount_applied) AS discount_usage_count,
ROUND((CAST(COUNT(discount_applied) AS decimal)/3900)*100,2) AS discount_applied_yes_percent
FROM customer_transaction
WHERE discount_applied = 'Yes';We use 3900 instead of the COUNT function because we add a WHERE clause for a specified condition. It will make a false count of transactions due to WHERE clause intervention.
Note that the number of transactions using discounts is 3883 transactions or about 99.56% of the total number of transactions during 2023. With the large number of transactions that apply discounts, customers can be more loyal to spending their money at the GenggamData Store.
That’s all of my project. If you have interest in this article please give a like and follow for more information like this. It is my pleasure for you who want to drop a comment on this article. Thank you!
