Data Analytics Applications in Retail Real-World Examples

-- Conversion and visitors per staff member by hour (one store, one month)
SELECT f.hour_of_day,
       SUM(f.visitors)                                   AS visitors,
       SUM(b.bills)                                      AS bills,
       ROUND(100.0 * SUM(b.bills) / NULLIF(SUM(f.visitors), 0), 1) AS conversion_pct,
       ROUND(1.0 * SUM(f.visitors) / NULLIF(SUM(s.staff_on_floor), 0), 1) AS visitors_per_staff
FROM footfall f
JOIN bills_by_hour b ON b.store_id = f.store_id AND b.day = f.day AND b.hour_of_day = f.hour_of_day
JOIN staff_roster s  ON s.store_id = f.store_id AND s.day = f.day AND s.hour_of_day = f.hour_of_day
WHERE f.store_id = 18
  AND f.day BETWEEN DATE '2026-10-01' AND DATE '2026-10-31'
GROUP BY f.hour_of_day
ORDER BY f.hour_of_day;

Excel version (one row per store-day-hour)
Columns: Day | Hour | Visitors | Bills | StaffOnFloor
Conversion %       =IFERROR([@Bills]/[@Visitors],0)
Visitors per staff =IFERROR([@Visitors]/[@StaffOnFloor],0)
PivotTable:
  Rows   = Hour
  Values = Sum of Visitors, Sum of Bills
  Add calculated field: Conversion = Bills / Visitors
Conditional formatting: highlight hours below average

-- ABC classification by cumulative share of sales
WITH item_sales AS (
    SELECT sku, SUM(net_amount) AS sales
    FROM sales
    WHERE sale_date >= DATE '2026-07-01'
    GROUP BY sku
),
ranked AS (
    SELECT sku, sales,
           SUM(sales) OVER (ORDER BY sales DESC) / SUM(sales) OVER () AS cum_share
    FROM item_sales
)
SELECT sku, sales, ROUND(cum_share * 100, 1) AS cum_pct,
       CASE WHEN cum_share <= 0.80 THEN 'A'
            WHEN cum_share <= 0.95 THEN 'B'
            ELSE 'C' END AS abc_class
FROM ranked
ORDER BY sales DESC;

-- Sales per square foot by category
SELECT c.category,
       SUM(s.net_amount)                      AS sales,
       MAX(sp.sq_ft)                          AS sq_ft,
       ROUND(SUM(s.net_amount) / NULLIF(MAX(sp.sq_ft), 0), 0) AS sales_per_sq_ft
FROM sales s
JOIN products c ON c.sku = s.sku
JOIN category_space sp ON sp.category = c.category AND sp.store_id = s.store_id
WHERE s.store_id = 18
GROUP BY c.category
ORDER BY sales_per_sq_ft DESC;

-- Return rate by product and size, top reasons included
SELECT s.sku,
       s.size,
       SUM(s.qty)                                         AS sold,
       COALESCE(SUM(r.qty), 0)                            AS returned,
       ROUND(100.0 * COALESCE(SUM(r.qty), 0) / NULLIF(SUM(s.qty), 0), 1) AS return_pct
FROM sales_lines s
LEFT JOIN return_lines r ON r.sale_line_id = s.sale_line_id
GROUP BY s.sku, s.size
HAVING SUM(s.qty) >= 50
ORDER BY return_pct DESC;

import pandas as pd

ret = pd.read_csv("returns.csv")   # sku, size, reason, qty
top_reasons = (ret.groupby("reason")["qty"].sum()
                  .sort_values(ascending=False)
                  .head(5))
print(top_reasons)

by_size = ret[ret["reason"] == "Size too small"].groupby("size")["qty"].sum()
print(by_size)

Store Manager Dashboard (daily)
KPI cards: footfall, bills, conversion %, avg bill value
Line chart: footfall vs bills by hour (today vs last week)
Table: hours with conversion below store average
Note box: 2-line summary for the morning huddle

Category Dashboard (weekly)
Bar chart: sales per sq ft by category
Table: ABC class counts per category
Scatter: space share vs sales share by category
List: C items with no growth for 12 weeks

Returns Dashboard (weekly)
KPI cards: return rate overall, by channel
Bar chart: top 5 return reasons
Heatmap: return rate by product and size
Table: products with return rate above threshold
Action column: owner + next step