Customer Analytics Report 🛒

HI! My second project in SQL from DQLab module to analysis of the company's condition in the last month to be presented at the townhall. I using BigQuery as tools of SQL and LucidChart to draw the entity relationship diagram (erd). So, here we go!

Data Preparation

The table that will be used in this project is as follows:

• orders_1 : Sales transactions for the quarter 1 period (Jan – Mar 2004)

• Orders_2 : Sales transactions for the 2nd quarter period (Apr – Jun 2004)

• customer : Customer profiles who register as company customers

Get Familiar with Tables

The first thing is to become familiar with the tables used. It will be handy to know which columns are related to the problem to be analyzed and what data manipulation process should be done for these columns because not all columns in the table need to be used.

-- view the orders_1 tables
SELECT * 
/* this code meaning that I create "sql-project-dqlab" project, use   "project2" database and run query from the "orders_1" table */
FROM `sql-project-dqlab.project2.orders_1` 
LIMIT 5;
-- view the orders_2 tables
SELECT * 
FROM `sql-project-dqlab.project2.orders_2` 
LIMIT 5;
-- view the customers tables
SELECT * 
FROM `sql-project-dqlab.project2.customers` 
LIMIT 5;

from 'orders_1' table

from 'orders_2' table

from 'customers' table

How is sales growth?

Total sales and revenue in Quarter 1 and Quarter 2

First step, write query to know the current sales from quarter 1 and quarter 2 by calculated the revenue with formula:

revenue = quantity * price of each quantity

and condition that status of orders must shipped in each quarter.

Then, using UNION ALL statement to combine all selected columns in both table/quarter

SELECT quarter, total_sales, revenue
FROM (
    SELECT 1 as quarter, sum(quantity) as total_sales,   sum(quantity*priceeach) as revenue
    FROM `sql-project-dqlab.project2.orders_1`
    WHERE status='Shipped'
    UNION ALL
    SELECT 2 as quarter, sum(quantity) as total_sales, sum(quantity*priceeach) as revenue
    FROM `sql-project-dqlab.project2.orders_2`
    WHERE status='Shipped'
) AS data_merge;

This result shows that Quarter 1 (January-March) is the highest total sales and revenu

Calculation of Sales and Revenue Growth

This calculation have duration between Quarter 1 (Q1) and Quarter 2 (Q2) using the growth formula:

sales growth = (current sales (Q2) - past sales(Q1)) * 100/past sales (Q1)

revenue growth = (current revenue (Q2) - past revenue(Q1)) * 100/past revenue (Q1)

So, I decide to use subquery in from statement to join total sales and revenue from two quarter

• First, I make 'Q1' table, it contains total sales and total revenue in Quarter 1 from 'orders_1' table and make 'Q2' table which is contains total sales and total revenue in Quarter 2

• Join both table Q1 and Q2 using inner join, before that I create 'number_merge' as dummy column as a link between two tables

SELECT concat(round((total_sales_q2 - total_sales_q1)*100/total_sales_q1,2),'%') as total_sales_growth,
concat(round((revenue_q2 - revenue_q1)*100/revenue_q1,2),'%') as revenue_growth
FROM (
    SELECT * 
    FROM (
        SELECT 1 as number_merge, sum(quantity) as total_sales_q1, sum(quantity*priceeach) as revenue_q1
        FROM `sql-project-dqlab.project2.orders_1`
        WHERE status='Shipped') as Q1
    INNER JOIN (
        SELECT 1 as number_merge, sum(quantity) as total_sales_q2, sum(quantity*priceeach) as revenue_q2
        FROM `sql-project-dqlab.project2.orders_2`
        WHERE status='Shipped') as Q2
    USING (number_merge)
) AS data_merge;

Total sales and revenue rate has decrease in Quarter 2

Is the number of customers increasing?

The increase in the number of customers can be measured by comparing the total number of customers registered in the current period (Quarter 2) with the total number of customers registered at the end of the previous period (Quarter 1).

SELECT EXTRACT(quarter FROM createDate) as quarter, count(distinct customerID) as customers
FROM `sql-project-dqlab.project2.customers`
GROUP BY 1;

In quarter 2, number of customers has decreased with 8 customers different from

How many of these customers have made transactions?

Continuation of the previous question (problem), from a number of customers who registered in the Quarter 1 and Quarter 2 periods, how many of them have made transactions?

SELECT EXTRACT(quarter FROM createDate) as quarter, count(distinct customerID) as customers
FROM `sql-project-dqlab.project2.customers`
WHERE customerID IN (
    SELECT customerID 
    FROM `sql-project-dqlab.project2.orders_1`
    UNION ALL
    SELECT customerID 
    FROM `sql-project-dqlab.project2.orders_2`
)
GROUP BY 1
ORDER BY 1;

25 of 49 customers who made transacted in Q1. It also shows the correlation between customers register and transaction, that the more total customer register the more they made transacted.

What product categories are most purchased by customers?

Find out which product categories are purchased the most, it can be done by calculating the total orders and the number of sales from each product category. Get information that product categories are obtained from the last 3 digits of the product code. So, the step are:

1. use LEFT statement to get category ID from the last 3 digit of product code

2. select the quarter, category ID, total order and quantity in each tables

3. use CTE that it will be sub query, to define each table or quarter as new temporary tables

4. use UNION ALL to gather the temporary tables from CTE

WITH Q1 AS (
    SELECT 1 as quarter, LEFT(productCode, 3) as categoryID, count(distinct orderNumber) as total_order, sum(quantity) as total_quantity
    FROM `sql-project-dqlab.project2.orders_1`
    GROUP BY 2 
    ORDER BY 3 DESC
    LIMIT 5),
Q2 AS (
    SELECT 2 as quarter, LEFT(productCode,3) as categoryID, count(distinct orderNumber) as total_order, sum(quantity) as total_quantity
    FROM `sql-project-dqlab.project2.orders_2`
    GROUP BY 2
    ORDER BY 3 DESC
    LIMIT 5)
SELECT * FROM Q1
UNION ALL 
SELECT * FROM Q2
ORDER BY 1;

Category S18 and S24 are the most valuabel product category because they have the largest of total order and quantity in each quarter.

How many customers are still active after their first transaction?

Find out how many customers still active to shows whether the company is still popular with customers to order their business needs and get insight for teams for future product and business development.

So, It is using customers retention rate by cohort analysis. The retention can be calculated by number of customers who shop in Quarter 1 and return to shopping in Quarter 2 using formula:

Retention rate = (number of customers from across period) * 100/ (total of customers in previous period)

WITH Q1 AS (
    SELECT count(distinct customerid) as total_customers 
    FROM`sql-project-dqlab.project2.orders_1`
    )

SELECT 'Q1-Q2' as quarter, concat(round((count(distinct customerid)*100)/ (SELECT total_customers FROM Q1),2),'%') as retention_rate 
FROM `sql-project-dqlab.project2.orders_1`
WHERE customerid IN (
    SELECT distinct customerid 
    FROM `sql-project-dqlab.project2.orders_2`);

It shows that retention rate in both quarter very low only 24% of total customers that make repeat order from first purchase in Q1.

Summary

Based on the data that we have obtained through SQL queries, we can conclude that:

1. The company's performance decreased significantly in the 2nd quarter, as seen from the value of sales and revenue, which dropped by 20% 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 shop at the company 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 a transaction) can increase.

4. Product categories S18 and S24 contribute about 50% of total orders and 60% of total sales, so the company should develop categories S18 and S24.

5. Customer retention rate is also shallow at only 24%, meaning that many customers who have transacted in the 1st quarter do not return to order in the 2nd quarter (no-repeat orders).

Overall, the company experienced negative growth in the 2nd quarter and it suggests to diagnosis that customers are not interested/unsatisfied/disappointed in shopping at the company.