Back to articles list Articles Cookbook
11 minutes read

Sales Growth Analysis with the Data Analyst Cheatsheet: Part 2

Author's photo
Scott Davis

Table of Contents

Welcome to Part 2 of our sales data analysis journey. Using the SQL for Data Analysis Cheat Sheet, we’ll dive deeper into key metrics and apply SQL queries to uncover insights about consumers and products. Let’s sharpen those SQL skills!

Building on my previous article, Sales Growth Dataset Exploration - Using the Data Analyst Cheat Sheet on Real Sales Data, this piece explores additional sales metrics using the same dataset. We’ll focus on different use cases involving consumers and products while continuing to follow the guidance from LearnSQL.com’s SQL for Data Analysis Cheat Sheet.

In this article, we’ll dive into three key elements: core SQL topics, identifying metric patterns, and directing you to relevant sections of the SQL for Data Analysis Cheat Sheet for quick reference. Using a Tableau dataset showcasing sales data for home goods, we’ll answer business questions related to customers and products.

The goal is to practice recognizing reporting patterns and applying SQL queries to solve these questions. While the dataset may evolve, the focus should be on the process and the logical steps in crafting queries

What you’ll learn:

  • Recognize patterns in SQL queries, metric analysis, and cheatsheet references
  • Apply these patterns to your everyday reporting and SQL work

Overview

This article is divided into two sections: customer analysis and product analysis, covering KPIs, trends, and various segmentation methods.

To answer the questions, I apply SQL techniques from the previous article, including aggregations, ratios, and common table expressions (CTEs). The analysis focuses on metrics such as KPIs, grouping, trend tracking, ranking, and segmentation. References to the SQL for Data Analysis Cheat Sheet include GROUP BY, ranking, date part extractions, ratios, and integer division. I clearly mark relevant SQL techniques, metric patterns and cheatsheet references in each example.

Examples are organized by difficulty, starting with simple queries and progressing to more advanced techniques, making it easy to apply these methods to other business datasets.

How to prepare the data

As in the previous article,  I use an open dataset from Tableau Public, which contains sales data for home goods like desks and stationery. It's widely used in business reporting and it’s a great resource for practicing SQL queries. Here are the steps to prepare the data.

Download the dataset from Tableau Public.

  • Navigate to Learn → Sample Data → Business → Superstore Sales.
  • Download the Superstore Sales dataset.
Sales Growth Analysis with the Data Analyst Cheatsheet: Part 2
  1. The relevant data is in the "Orders" tab.
  2. Open .xls file
  3. Open the ‘Orders’ tab from .xls file and export it to a CSV file.

For a SQL IDE, I recommend using MySQL Workbench and MySQL Community Server. Upload the  CSV file and into your MySQL database to start querying.

As in the previous article, you can download the code used in this article from my Github account.

Analyzing Customers

Let’s start on the easy level on the customer sub-domain. We will summarize different ways of analyzing customers for financial, order, and segmentation use-cases.

Question 1: How many customers are there per time period?

SELECT
    EXTRACT(YEAR FROM DATE(STR_TO_DATE(order_date, '%m/%d/%Y'))) AS order_year,
    EXTRACT(MONTH FROM DATE(STR_TO_DATE(order_date, '%m/%d/%Y'))) AS order_month,
    COUNT(DISTINCT(Customer_ID)) AS active_customers
FROM `Tableau Superstore`.orders
GROUP BY 1, 2
ORDER BY 1, 2;

Outcome: The query calculates total customers by time period across the entire dataset. It provides a trend of how many customers have made purchases by year and month. A user can see how the customer-base has changed throughout the history of the business.

To learn how to write SQL reports like this one, I recommend the Creating Basic SQL Reports course. It has almost 100 exercises that will teach you how to write complex reports in SQL.

Question 2: Create customer-based finance metrics per product category and sub-category for average revenue per user (ARPU) and average profit per user (APPU) by location

SELECT
      Region,
      State,
      City,
      ROUND(SUM(Sales) / COUNT(DISTINCT `Customer_ID`), 1) AS ARPU,
      ROUND(SUM(Profit) / COUNT(DISTINCT `Customer_ID`), 1) AS APPU
FROM `Tableau Superstore`.orders
GROUP BY 1, 2, 3
ORDER BY 4 DESC;

Outcome: This query calculates ARPU and APPU for region, state, city combination and ranks cities by the ARPU in descending order. The results show cities generating the most ARPU and shows their APPU as well. This query can be used for identifying high-value cities generating the most revenue per customer for strategic marketing and/or sales strategy use-cases.

Question 3: What is the average number of orders per customer? What is the average order value (AOV) per customer?

SELECT
    ROUND(SUM(Quantity) / COUNT(DISTINCT `Customer_ID`), 1) AS AQPU,
    ROUND(SUM(Sales)/COUNT(order_id), 2) as average_order_value
FROM `Tableau Superstore`.orders

Outcome: This query calculates two KPIs related to orders (versus finance related from the last query): Average Quantity Per User (AQPU), which measures the average quantity of products purchased per unique customer, and Average Order Value (AOV), which measures the average sales amount per order. A stakeholder can understand more about customer purchasing behavior by the orders.

Here are a couple of hard questions, both dealing with ranked-style customer segmentations. The first one looks at customers through 4 different metrics, while the last one combines combinations of metrics to find the right segment.

Question 4: What are the top 3 accounts/customers by profit margin, total profit, total sales, and sales frequency?

WITH customer_metrics AS (
    SELECT 
         customer_name, 
         ROUND(SUM(Sales), 1) AS sales_total,
         ROUND(SUM(Profit), 1) AS profit_total,
         ROUND(SUM(Profit)/SUM(Sales), 1) as profit_margin,
         COUNT(order_id) AS sales_frequency
    FROM  `Tableau Superstore`.orders
    GROUP BY 1
),
ranked_customers AS (
    SELECT 
        customer_name, 
        'Profit Margin' AS metric, 
        profit_margin AS amount,
        DENSE_RANK() OVER (ORDER BY profit_margin DESC) AS customer_rank
    FROM customer_metrics

    UNION ALL

    SELECT 
        customer_name, 
        'Total Profit' AS metric, 
        profit_total AS amount,
        DENSE_RANK() OVER (ORDER BY profit_total DESC) AS customer_rank
    FROM customer_metrics

    UNION ALL

    SELECT 
        customer_name, 
        'Total Sales' AS metric, 
        sales_total AS amount,
        DENSE_RANK() OVER (ORDER BY sales_total DESC) AS customer_rank
    FROM customer_metrics

    UNION ALL

    SELECT 
        customer_name, 
        'Sales Frequency' AS metric, 
        sales_frequency AS amount,
        DENSE_RANK() OVER (ORDER BY sales_frequency DESC) AS customer_rank
    FROM customer_metrics
)
SELECT 
    customer_name, 
    metric, 
    amount,
    customer_rank
FROM ranked_customers
WHERE customer_rank <= 3
ORDER BY 2, 4

Outcome: This is more of a bottom-up type of report that shows several metrics (total sales, total profit, profit margin, and sales frequency) at a granular level - individual customers. The results show the top 3 customers in each of the 4 categories. This query can be used to prioritize high-value customers for targeted marketing and sales strategies.

Disclaimer — This method works to segment customers, but you must decide which metric to filter first (revenue, profit, or margin). The order affects your results and isn’t always obvious. Later, we’ll see a query that ranks customers using all metrics together.

To learn more about using WITH in SQL queries, check out the course Recursive Queries.

Question 5: Identify high-priority customers based on metrics like profit, profit margin, purchase frequency, and quantity sold. Segment customers into Low, Medium, and High priority groups, with High representing those with the most profit and highest purchase frequency

WITH customer_metrics AS (
    SELECT 
        customer_name, 
        ROUND(SUM(Sales), 1) AS sales_total,
        ROUND(SUM(Profit), 1) AS profit_total,
        ROUND(SUM(Profit)/SUM(Sales), 1) as profit_margin,
        COUNT(order_id) AS sales_frequency
    FROM `Tableau Superstore`.orders
    GROUP BY 1
),
ranked_customers AS (
    SELECT 
        customer_name, 
        sales_total, 
        profit_total, 
        profit_margin,
        sales_frequency, 
        PERCENT_RANK() OVER (ORDER BY profit_margin DESC) AS profit_rank,
        PERCENT_RANK() OVER (ORDER BY sales_frequency DESC) AS frequency_rank
    FROM customer_metrics
),
segmented_customers AS (
 SELECT 
    customer_name, 
    sales_total, 
    profit_total, 
    profit_margin,
    sales_frequency,
    CASE 
        WHEN profit_rank <= 0.1 AND frequency_rank <= 0.1 THEN 'High Profit & High Frequency'
        WHEN profit_rank <= 0.1 THEN 'High Profit'
        WHEN frequency_rank <= 0.1 THEN 'High Frequency'
        ELSE 'Low Profit & Low Frequency'
    END AS customer_segment
 FROM ranked_customers
)
SELECT 
    customer_name, 
    sales_total, 
    profit_total, 
    profit_margin,
    sales_frequency,
    customer_segment
FROM segmented_customers
WHERE customer_segment = 'High Profit & High Frequency'
ORDER BY 3 DESC;

Outcome: This query segments customers based on profit margin and sales frequency, identifying the top 10% in both categories. The results show the most valuable customers as ‘High Profit & High Frequency’. This is in contrast to the last query that had the 4 categories separated and it was difficult to combine the results to show the most valuable customers. This query can be used to prioritize customers and then target them for marketing and or sales campaigns.

To learn about window functions in SQL, I recommend our interactive course Window Functions.

Analyzing Products

Here are some more easy level questions, this time focusing on products. The first two questions are linear to some of the customers' questions and require trend analysis, but the last one is a different type of segmentation problem.

Question 6: How many different products are ordered in each time period?

SELECT 
    EXTRACT(YEAR FROM DATE(STR_TO_DATE(order_date, '%m/%d/%Y'))) AS order_Year,
    EXTRACT(MONTH FROM DATE(STR_TO_DATE(order_date, '%m/%d/%Y'))) AS order_Month,
    COUNT(DISTINCT product_id) AS unique_items
FROM 
    `Tableau Superstore`.orders
GROUP BY 1, 2
ORDER BY 1, 2 DESC

Outcome: This query shows a trend on the product-side for unique items sold. This metric can be used as an initial check on the type of inventory of products being sold.

Question 7: Create a report of the sales, profit, and profit margin for each product

SELECT 
    product_id,
    category,
    sub_category,
    ROUND(SUM(sales), 1) AS sales_total,
    ROUND(SUM(profit), 1) AS profit_total,
    ROUND(SUM(profit)/SUM(sales), 1) as profit_margin
FROM `Tableau Superstore`.orders
GROUP BY 1, 2, 3
ORDER BY 4 DESC

Outcome:

This query calculates the total sales, total profit, and profit margin for each product and shows the product ID, category, and sub-category for each. The results show product-level performance, highlighting top-selling products and the profit margins for each. The query can be used to find the top-selling products and show their profitability - it is a general report that can be used for pricing and inventory.

This is another type of Hard level question, it is about segmentation, but different from the first two from customers. This is segmenting a grouped result - a metric by a category.

Question 8: What are the 3 items with the lowest profit margin for each location, considering item-location combinations. If there are products with ties, show them as well.

WITH product_metrics AS (
    SELECT 
        product_id, 
        state,
        city,
        ROUND(SUM(Sales), 1) AS sales_total,
        ROUND(SUM(Profit), 1) AS profit_total,
        ROUND(SUM(Profit)/SUM(Sales), 2) as profit_margin
    FROM `Tableau Superstore`.orders
    GROUP BY 1, 2, 3
),
ranked_products AS (
    SELECT 
        product_id,
        state,
        city, 
        sales_total, 
        profit_total,
        profit_margin,
        DENSE_RANK() OVER (ORDER BY profit_margin ASC) AS profit_rank
    FROM product_metrics
   WHERE profit_margin IS NOT NULL
)
SELECT 
    product_id,
    state,
    city,
    sales_total, 
    profit_margin,
    profit_rank
FROM ranked_products
WHERE profit_rank <= 3
ORDER BY 5 ASC;

Outcome: This query calculated product performance metrics by city-state-product_id combinations and shows the lowest profit margins. The results show the least profitable combinations and can be used to show underperforming areas - either to improve or cut certain products in those areas.

Wrap-Up

These 8 examples in this article continue the discussion from the previous one on answering reporting questions in a business context. We started with basic KPI metrics, moved on to trend analysis , and concluded with more advanced ranking and segmentation  calculations.

By working through these queries, you’ve seen common SQL patterns used in business reporting, including aggregations, window functions, and subqueries. Whether you followed along step by step or simply reviewed the examples, these techniques can serve as a practical reference for analyzing sales data in SQL.

I hope this analysis has helped clarify how to apply the Data Analyst Cheat Sheet in practice. If anything is unclear, if you spot any mistakes, or if you have questions about the article or any piece of code, let’s discuss it and improve together! I’d be happy to connect on LinkedIn, and you can find more examples and insights on my Tableau Public profile or my blog. Looking forward to your thoughts!

Tags:

You may also like