Project Data Analysis for B2B Retail : Customer Analytics Report

Measuring B2B Retail Customer Analytics with SQL

Source: freepic

Background Project

This project provided by DQLab, xyz.com is a B2B startup that sells various products not directly to end users but to other businesses/companies. As a data-driven company, every decision making at xyz.com is always based on data. Every quarter xyz.com will hold a townhall where all or division representatives will gather to review the company’s performance during the last quarter.

What the task given?

Assume the current year is 2004,the manager of xyz.com wants to know :
1. How sales growth at this time ?
2. Is the amount of customers xyz.com has been increased ?
3. And how many customers did transactions ?
4. What most of the products bought by customers ?
5. How many customers active transact?

Dataset Brief

Table will be used on project this time was as follows :

fig 1 : table used on project

1. Table orders_1 : Contains data relating to transactions sales period quarter 1 (Jan — Mar 2004)

SELECT * FROM orders_1 limit 5;

Output :

fig 2 : Table orders_1

2. Table orders_2 : Contains data relating to transactions sales period quarter 2 (Apr — Jun 2004)

SELECT * FROM orders_2 limit 5;

Output :

fig 3: Table orders_2

3. Table Customer : Contains data profile customer who sign up to be xyz.com customer

SELECT * FROM customer limit 5;

Output :

fig 4: Table customer

Let’s solving this project !

Sales Growth

1A. Total sales and revenue in Quarter-1 (Jan, Feb, Mar) and Quarter-2 (Apr,Mei,Jun)

SELECT
 sum(quantity) US total_sales,
 quantity (amount * price each) AS income
FROM
 order_1
WHERE
 status = 'Sent';

Output :

fig 5: Total Sales and Revenue Quarter-1

SELECT
 sum(quantity) US total_sales,
 quantity (amount * price each) AS income
FROM
 order_2
WHERE
 status = 'Sent';

Output :

fig 6: Total Sales and Revenue Quarter-2

Total sales and Revenue got decreasing from Quarter 1 to Quarter 2

1B. Counting the percentage of total sales

SELECT quarters,
       sum(quantity) AS total_sales,
       sum(quantity * priceeach) AS revenue
FROM (
     SELECT orderNumber, status, quantity, priceEach, '1' AS quarter
     FROM orders_1
     UNION
     SELECT orderNumber, status, quantity, priceEach, '2' AS quarter
     FROM orders_2
) AS table_a
WHERE
 status = 'Shipped'
GROUP BY 1;

Output :

fig 7: Total Sales and Revenue Quarter-1 and Quarter 2

To find out sales growth, it can be done by calculating the percentage of sales last month with the number of previous sales and the difference between the last month’s income and the previous period’s income:

% Growth Sales = Sum(Present Sales) - Sum(Past Sales) / Sum(Past Sales) * 100 (%)
% Growth Revenue = Sum(Present Revenue) - Sum(Past Revenue) / Sum(Past Revenue) * 100 (%)

%Growth Sales= (6717–8694)/8694 = -22,74%

%Growth Revenue = (607548320–799579310)/ 799579310 = -24.02%

Based on the calculation of above , Sales Growth decreased by -22.74% and also Revenue Growth decreased by -24.02%.

Customer Analytics

2. Is the amount of customers xyz.com has been increased ?

Increasing the number of customers can be measured by comparing the total number of customers that registration in current period with the total number of customers who registered at the end of the previous period.

SELECT quarters,
       count(DISTINCT customerID) AS total_customers
FROM (
     SELECT customerID, createDate,
QUARTER(createDate) AS quarter
     FROM customers
     WHERE createDate BETWEEN '2004-01-01' AND '2004-06-30'
) AS table_b
GROUP BY 1;

Output :

fig 8: Total customer by quarter

Based on data, there was a decrease number of new customers in the second quarter.

3. how many customers did transactions ?

SELECT quarters,
       count(DISTINCT customerID) AS total_customers
FROM (
      SELECT customerID, createDate,
  QUARTER(createDate) as quarter
 FROM customers
      WHERE (createDate BETWEEN '2004-01-01' AND '2004-06-30')
      AND customerID IN (
          SELECT DISTINCT customerID
          FROM orders_1
          UNION
          SELECT DISTINCT customerID
          FROM orders_2
)
) AS table_b
GROUP BY 1;

Output :

fig 9: Total customer did transactions by quarter

% Customer Did Transactions = Sum(customer did transaction by quarter) / sum(customer by quarter) * 100 (%)

% Customer Did Transactions= (25 + 19) * 100 / (43 + 35) = 56.41%

Based on the calculation of above, percentage customer did transaction is 56.41% or more than half of customers have made transactions.

4. What most of the products bought by customers ?

for this we only focus on the second quarter, To 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.

SELECT * FROM (
       SELECT categoryID,
       count(DISTINCT orderNumber) AS total_order,
       sum(quantity) AS total_sales
       FROM (
            SELECT substr(productCode, 1, 3) AS categoryID, orderNumber, quantity, status
            FROM orders_2
            WHERE status = 'Shipped'
       ) AS table_c
GROUP BY 1
) US c
ORDER BY 2 DESC;

Output :

fig 10: most product bought by customer

From the data above, products with categoryID S18 and S24 contributed 50% of the total orders for the 2nd quarter. In terms of sales quantity, the highest sales in the second quarter were also occupied by S18 and S24 products, which amounted to 60.89% of total sales.

5. How many customers active transact?

Knowing how many customers remain active shows whether xyz.com is still popular with customers to order their business needs. This can also be the basis for product and business teams for future product and business development.

First we need calculate customers who made transactions in first quarter

SELECT COUNT(DISTINCT customerID) as total_customers FROM orders_1;

Output :

fig 11: total customer first quarter

and now we calculate how many customer who remain active in transactions after the first transaction

SELECT '1' US quarter,
COUNT(DISTINCT customerID)*100/25 US q2
FROM orders_1
WHERE customerID IN (
      SELECT DISTINCT customerID
    FROM orders_2
);

Output :

fig 12: percentage customer still active buying after the first transaction

Based on the data , from the 25 customers who made transactions in the first quarter, 24% remained active in the second quarter.

SUMMARY

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

1. Xyz.com 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 xyz.com 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. Products categories S18 and S24 contribute about 50% of total orders and 60% of total sales, so xyz.com should focus on developing the S18 and S24 categories.
5. xyz.coms customer retention rate is also very low 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).
6. xyz.com experienced negative growth in the 2nd quarter and needs to make a lot of improvements both in terms of products and marketing business, if it wants to achieve its target and positive growth in the 3rd quarter. The low retention rate and conversion rate can be an early diagnosis that customers are not interested/unsatisfied/disappointed in shopping at xyz.com.