Build a sales dashboard for an online store in Superset#

#Objective

This guide walks you through turning the cleaned order lines of an online store into a Superset dashboard: key figures, revenue over time, best-selling products, sales by country and customer loyalty.

It is the second part of Prepare online store orders for a sales dashboard. Superset queries the Lakehouse through Trino, so all SQL on this page is in the Trino dialect.

#Requirements

To follow this guide you need:

• The table online_retail_ii_clean in the dataset default_dataset of your project, produced by the cleaning Custom action in the first part.
• An Apache Superset application connected to your project. If you have not deployed it yet, follow Deploy Apache Superset.
• The Superset dataset online_retail_ii_clean, created in Step 6 of the first part.

#What you will build

At the end of this part, your dashboard looks like this:

Every chart follows the same path in Superset: Charts > + Chart, pick the dataset and the chart type, fill in the settings, click Create chart, then Save with the chart name. Leave Add to dashboard empty: you create the dashboard and add the charts to it in Step 8.

#Instructions

#Step 1: Create the customer datasets in SQL Lab

The customer charts need one row per customer or per return visit, which a chart cannot compute from order lines. You prepare these rows once, with two queries saved as virtual datasets.

#Create the virtual dataset customer_summary

1. Open SQL > SQL Lab.
2. Select your database and the schema default_dataset.
3. Paste the query below and click Run.
4. In the query toolbar, click the Save or Overwrite Dataset icon and save the dataset as customer_summary.

WITH purchase_days AS (
    SELECT DISTINCT customer_id, CAST(invoicedate AS DATE) AS purchase_day
    FROM default_dataset.online_retail_ii_clean
    WHERE customer_id IS NOT NULL
      AND invoice NOT LIKE 'C%'
),
customers AS (
    SELECT
        customer_id,
        COUNT(*)          AS purchase_days,
        MIN(purchase_day) AS first_purchase,
        MAX(purchase_day) AS last_purchase
    FROM purchase_days
    GROUP BY customer_id
)
SELECT
    customer_id,
    purchase_days,
    LEAST(purchase_days, 10)                                   AS purchase_days_capped,
    CASE WHEN purchase_days >= 2 THEN 1 ELSE 0 END             AS is_repeat,
    first_purchase,
    last_purchase,
    date_diff('day', last_purchase, DATE '2011-12-09')         AS days_since_last_purchase,
    CASE
        WHEN date_diff('day', last_purchase, DATE '2011-12-09') <= 90  THEN '1. Active (90 days)'
        WHEN date_diff('day', last_purchase, DATE '2011-12-09') <= 180 THEN '2. At risk (91 to 180 days)'
        WHEN date_diff('day', last_purchase, DATE '2011-12-09') <= 365 THEN '3. Lapsed (181 to 365 days)'
        ELSE '4. Lost (over a year)'
    END                                                        AS recency_segment
FROM customers

The result has one row per identified customer: 5,852 rows.

#Create the virtual dataset purchase_intervals

Repeat the same steps with the query below, and save it as purchase_intervals.

WITH purchase_days AS (
    SELECT DISTINCT customer_id, CAST(invoicedate AS DATE) AS purchase_day
    FROM default_dataset.online_retail_ii_clean
    WHERE customer_id IS NOT NULL
      AND invoice NOT LIKE 'C%'
),
with_previous AS (
    SELECT
        customer_id,
        purchase_day,
        lag(purchase_day) OVER (PARTITION BY customer_id ORDER BY purchase_day) AS previous_purchase
    FROM purchase_days
)
SELECT
    customer_id,
    purchase_day,
    date_diff('day', previous_purchase, purchase_day) AS days_since_previous_purchase
FROM with_previous
WHERE previous_purchase IS NOT NULL

The result has one row per return visit: 27,026 rows.

#Step 2: Add calculated columns to online_retail_ii_clean

Open Datasets, edit online_retail_ii_clean, then go to the Calculated columns tab. For each column below, click Add item, then fill in its name and SQL expression. Start with revenue, the simpler one. Click Save once both columns are added.

#Add revenue

revenue is the amount of each order line. A cancellation has a negative quantity, so its revenue is negative, and a plain sum gives the net revenue. Every amount in the dashboard is built on this column.

#Add country_code

country_code converts the country name to its ISO 3166-1 alpha-3 code, for the world map:

CASE country
    WHEN 'United Kingdom'       THEN 'GBR'
    WHEN 'EIRE'                 THEN 'IRL'
    WHEN 'Germany'              THEN 'DEU'
    WHEN 'France'               THEN 'FRA'
    WHEN 'Netherlands'          THEN 'NLD'
    WHEN 'Spain'                THEN 'ESP'
    WHEN 'Switzerland'          THEN 'CHE'
    WHEN 'Belgium'              THEN 'BEL'
    WHEN 'Portugal'             THEN 'PRT'
    WHEN 'Australia'            THEN 'AUS'
    WHEN 'Italy'                THEN 'ITA'
    WHEN 'Norway'               THEN 'NOR'
    WHEN 'Sweden'               THEN 'SWE'
    WHEN 'Cyprus'               THEN 'CYP'
    WHEN 'Finland'              THEN 'FIN'
    WHEN 'Austria'              THEN 'AUT'
    WHEN 'Denmark'              THEN 'DNK'
    WHEN 'Greece'               THEN 'GRC'
    WHEN 'Japan'                THEN 'JPN'
    WHEN 'USA'                  THEN 'USA'
    WHEN 'Poland'               THEN 'POL'
    WHEN 'United Arab Emirates' THEN 'ARE'
    WHEN 'Israel'               THEN 'ISR'
    WHEN 'Hong Kong'            THEN 'HKG'
    WHEN 'Singapore'            THEN 'SGP'
    WHEN 'Malta'                THEN 'MLT'
    WHEN 'Iceland'              THEN 'ISL'
    WHEN 'Canada'               THEN 'CAN'
    WHEN 'Lithuania'            THEN 'LTU'
    WHEN 'RSA'                  THEN 'ZAF'
    WHEN 'Bahrain'              THEN 'BHR'
    WHEN 'Brazil'               THEN 'BRA'
    WHEN 'Thailand'             THEN 'THA'
    WHEN 'Korea'                THEN 'KOR'
    WHEN 'Lebanon'              THEN 'LBN'
    WHEN 'Bermuda'              THEN 'BMU'
    WHEN 'Nigeria'              THEN 'NGA'
    WHEN 'Czech Republic'       THEN 'CZE'
    WHEN 'Saudi Arabia'         THEN 'SAU'
    ELSE NULL
END

#Step 3: Build the key figures

The four tiles use the dataset online_retail_ii_clean and the chart type Big Number.

#Net revenue

Expected value: £18.9M.

#Orders

Expected value: 39.5k.

#Average order value

Expected value: £497.08.

#Customers

Expected value: 5.88k.

#Step 4: Build the Time tab

#Net revenue by month

#Seasonality by year

You get one area per year. The month number at the start of each label keeps January to December in order.

#Step 5: Build the Products tab

Both charts use the dataset online_retail_ii_clean and the chart type Table. The cleaning action gives each stock code a single description, so a product never appears twice.

#Top 10 products by revenue

In the Customize tab, under Conditional formatting, add a rule:

#Top 10 products by units sold

Apply the same conditional formatting as the previous table, on the quantity column. Placed side by side, the two tables show that the products sold in the largest volumes are not the ones that bring in the most revenue.

#Step 6: Build the Geography tab

#United Kingdom vs rest of world

#Top 10 countries outside the United Kingdom

#Revenue by country

#Step 7: Build the Customers tab

These charts cover identified customers and sale invoices, from December 2009 to December 2011. Recency is measured from 9 December 2011, the last day of the data. A customer comes back when they buy on another day: several invoices on the same day count as one purchase.

#Repeat customer rate

Expected value: 71.4%.

#Median purchases per customer

Expected value: 3.

#Median days between purchases

Expected value: 32.

#Customers by number of purchases

If the bars are unevenly spaced, enable Force categorical on the X-axis.

#Customers by recency

The four bars add up to 5,852 customers. The number at the start of each segment keeps them in order, from active to lost.

#Days between purchases

The filter leaves out the rare returns after more than a year, which would stretch the axis and squeeze every other bar to the left.

#Step 8: Create and assemble the dashboard

1. Open Dashboards and click + Dashboard. The new dashboard opens in edit mode.
2. Click its title and rename it Online store sales.
3. Add a header Key figures, drag the four key figure tiles from the right panel and place them on one row.
4. Add a header Trends and breakdowns, then a tabs component with four tabs: Customers, Time, Products and Geography.
5. Drag each of the other charts from the right panel into its tab. In the Customers tab, put the three tiles on one row, the two bar charts side by side and the histogram at full width.
6. Click Save.

#What you have built

You have a dashboard that answers the questions of an online store manager: how much the store sells and to how many customers, how sales evolve over the year, which products and countries matter, and how often customers come back.

From here you can add dashboard filters on country or on a date range, or apply the same pipeline to your own order data. For another Superset dashboard built on Data Platform, see the FOCUS and Apache Superset use case.

#Go further

For training or technical assistance implementing our solutions, contact your sales representative or visit our Professional Services page to request a quote and have your project analysed by our experts.

Ask questions, give your feedback and interact directly with the team building the Data Platform on the dedicated Discord channel.

If you need support with your OVHcloud services, create a request in our Help Centre.

Join our community of users.