Master retail analytics with Apache Superset. Build dashboards for inventory, margin, and foot traffic. Reduce costs vs. Looker. Expert guide.
Retail analytics has become a mission-critical function. Store operations teams need real-time visibility into inventory levels across locations. Merchandising teams want to correlate foot traffic with sales performance and margin contribution by category. Supply chain leaders track shrinkage, SKU velocity, and replenishment cycles. Finance teams reconcile margin erosion against promotional spend and markdown activity.
For years, retailers have relied on expensive, monolithic BI platforms—Looker, Tableau, Power BI—that require months of implementation, dedicated data teams, and six-figure annual contracts. But the economics no longer make sense for many mid-market and scale-up retailers. You end up paying for features you don't use, waiting in vendor roadmap queues, and struggling to customize dashboards for your specific supply chain or store operations workflows.
Apache Superset changes that equation. It's an open-source, lightweight BI platform that lets you build production-grade retail analytics dashboards in days, not months. When paired with D23's managed hosting and AI integration, you get the speed and flexibility of open-source without the operational burden. This guide walks you through the canonical dashboards that drive retail decision-making: inventory management, margin analysis, and foot traffic intelligence.
Apache Superset is a modern data visualization and business intelligence platform designed for teams that need fast iteration, deep customization, and cost control. Unlike traditional BI tools, Superset connects directly to your data warehouse or operational databases—PostgreSQL, Snowflake, BigQuery, Redshift, Presto—and lets you build interactive dashboards without proprietary data models or semantic layers.
For retail, this matters because your data architecture is already messy. You have POS systems (Square, Toast, Shopify), inventory management tools (NetSuite, SAP), foot traffic sensors (WiFi counters, computer vision), and third-party logistics platforms all pumping data into your warehouse. Superset doesn't force you into a rigid data model. You write SQL against your existing tables, create calculated fields on the fly, and publish dashboards that update in real time.
When you explore data in Superset, you're working with a visual query builder and native SQL editor that feels familiar to anyone who's touched analytics. You can drill down from a regional view to individual store performance, filter by date range or product category, and export results without leaving the dashboard. That interactivity is crucial in retail, where a margin question at 2 PM might require a different cut of data than the same question at 4 PM.
Retail analytics breaks down into three overlapping domains: inventory management, margin and profitability, and foot traffic and customer behavior. Each domain has its own KPIs, data sources, and stakeholder audience. But they're deeply interconnected—foot traffic drives inventory velocity, inventory turns affect margin through markdown rates, and margin pressures force promotional decisions that impact foot traffic.
We'll explore how to build each dashboard in Superset, what metrics matter, and how to structure your data queries for performance and clarity.
Inventory management in retail is fundamentally about velocity: how fast does a SKU move through your stores, and how much working capital are you tying up in stock that isn't selling?
The canonical inventory dashboard tracks:
In Superset, these metrics live in SQL queries that join your inventory transactions table (on-hand counts, receipts, sales) with your product master data (category, supplier, cost) and store location data (region, store type, format).
Start with a fact table that records inventory snapshots daily or weekly:
SELECT
snapshot_date,
store_id,
sku_id,
on_hand_units,
ytd_units_sold,
ytd_units_received,
cost_per_unit,
last_receipt_date
FROM inventory.daily_snapshots
WHERE snapshot_date >= CURRENT_DATE - INTERVAL '90 days'
From this base query, you calculate DIO:
SELECT
store_id,
category,
ROUND(
AVG(on_hand_units) /
NULLIF(AVG(ytd_units_sold) / 365, 0),
1
) AS avg_days_inventory_outstanding,
COUNT(DISTINCT sku_id) AS sku_count,
SUM(on_hand_units * cost_per_unit) AS total_inventory_value
FROM inventory.daily_snapshots
JOIN products.master ON sku_id = product_id
WHERE snapshot_date >= CURRENT_DATE - INTERVAL '90 days'
GROUP BY store_id, category
Once you've created this query in Superset, you can visualize it as a table, a heat map (stores on rows, categories on columns, DIO as color intensity), or a bar chart sorted by DIO descending. Add filters for date range, store region, and product category. The dashboard becomes interactive: a store manager can click on their store, see which categories are turning slowly, and drill into the SKUs driving that performance.
Building scalable dashboards for logistics and supply chain follows the same pattern. You start with a clean fact table, layer on dimension tables (products, locations, suppliers), and let Superset's visualization layer handle the rest.
Shrinkage tracking requires a slightly different data model. You need a monthly or quarterly inventory variance report that captures:
SELECT
period_end_date,
store_id,
category,
SUM(expected_inventory) AS expected_units,
SUM(actual_inventory) AS actual_units,
SUM(expected_inventory) - SUM(actual_inventory) AS variance_units,
ROUND(
100 * (SUM(expected_inventory) - SUM(actual_inventory)) /
NULLIF(SUM(sales_units), 0),
2
) AS shrinkage_pct
FROM inventory.variance_reports
GROUP BY period_end_date, store_id, category
ORDER BY shrinkage_pct DESC
Visualize this as a scatter plot (shrinkage % on Y-axis, sales volume on X-axis) to identify which categories have both high shrinkage and high velocity—those are your priority loss prevention targets.
Margin is where inventory decisions meet pricing strategy. A retailer with perfect inventory velocity can still destroy profitability through aggressive markdowns, excessive promotional discounting, or high shrinkage.
The canonical margin dashboard tracks:
Margin analysis requires transaction-level data: every sale with cost of goods, actual selling price, promotional discount (if any), and date. This typically comes from your POS system:
SELECT
transaction_date,
store_id,
category,
sku_id,
units_sold,
gross_revenue,
promotional_discount,
net_revenue,
cogs,
ROUND(100 * (net_revenue - cogs) / NULLIF(net_revenue, 0), 2) AS margin_pct
FROM sales.transactions
WHERE transaction_date >= CURRENT_DATE - INTERVAL '12 months'
From this, aggregate by week and category to see margin trends:
SELECT
DATE_TRUNC('week', transaction_date) AS week_start,
category,
SUM(net_revenue) AS weekly_revenue,
SUM(cogs) AS weekly_cogs,
SUM(promotional_discount) AS promotional_spend,
ROUND(100 * SUM(net_revenue - cogs) / NULLIF(SUM(net_revenue), 0), 2) AS margin_pct,
COUNT(DISTINCT store_id) AS stores_selling
FROM sales.transactions
WHERE transaction_date >= CURRENT_DATE - INTERVAL '52 weeks'
GROUP BY DATE_TRUNC('week', transaction_date), category
ORDER BY week_start DESC, margin_pct DESC
Create a line chart in Superset with week on the X-axis and margin % on the Y-axis. Add a separate line for each category, or use a filter dropdown to toggle categories. Overlay promotional spend as a bar chart on the secondary Y-axis. This instantly reveals whether promotions are driving margin expansion (incremental volume at acceptable discount rates) or margin compression (heavy discounting with minimal volume lift).
For category mix analysis, use a stacked bar chart:
SELECT
DATE_TRUNC('month', transaction_date) AS month,
category,
SUM(net_revenue) AS monthly_revenue,
ROUND(100 * SUM(net_revenue) / SUM(SUM(net_revenue)) OVER (PARTITION BY DATE_TRUNC('month', transaction_date)), 2) AS revenue_mix_pct
FROM sales.transactions
WHERE transaction_date >= CURRENT_DATE - INTERVAL '24 months'
GROUP BY DATE_TRUNC('month', transaction_date), category
ORDER BY month DESC, monthly_revenue DESC
Stack categories by revenue, and color-code by category margin %. A shift toward lower-margin categories will show as a change in the color distribution—an instant visual cue that profitability is at risk.
Foot traffic is the leading indicator of retail health. You can have perfect inventory and margin management, but if customers aren't walking through your doors, revenue will follow. Modern retailers use multiple data sources to measure and predict foot traffic: WiFi sensors, Bluetooth beacons, computer vision systems, mobile location data, and even POS transaction counts.
The canonical foot traffic dashboard tracks:
Machine learning techniques for predicting foot traffic rely on historical traffic data, external factors (weather, events, holidays), and seasonal patterns. But before you build a predictive model, you need a clean historical dataset.
Assuming you have foot traffic data from WiFi sensors or a third-party provider:
SELECT
traffic_date,
traffic_hour,
store_id,
store_region,
unique_visitors,
repeat_visitors,
avg_dwell_time_minutes,
zone_id,
zone_traffic_pct
FROM traffic.hourly_snapshots
WHERE traffic_date >= CURRENT_DATE - INTERVAL '12 months'
Join this with transaction data to calculate conversion:
SELECT
DATE(traffic_date) AS traffic_day,
store_id,
SUM(unique_visitors) AS daily_visitors,
COUNT(DISTINCT transaction_id) AS transactions,
ROUND(100 * COUNT(DISTINCT transaction_id) / NULLIF(SUM(unique_visitors), 0), 2) AS conversion_rate_pct,
ROUND(SUM(net_revenue) / NULLIF(SUM(unique_visitors), 0), 2) AS revenue_per_visitor
FROM traffic.hourly_snapshots
LEFT JOIN sales.transactions ON
DATE(traffic_date) = DATE(transaction_date) AND
traffic.hourly_snapshots.store_id = sales.transactions.store_id
WHERE traffic_date >= CURRENT_DATE - INTERVAL '12 months'
GROUP BY DATE(traffic_date), store_id
ORDER BY traffic_day DESC
In Superset, create a line chart showing daily foot traffic and conversion rate (on dual Y-axes). Add a filter for store region or individual store. A sudden drop in traffic or conversion is an early warning sign of operational issues or competitive pressure.
For peak hours analysis, aggregate by hour of day:
SELECT
EXTRACT(HOUR FROM traffic_date) AS hour_of_day,
EXTRACT(DOW FROM traffic_date) AS day_of_week,
AVG(unique_visitors) AS avg_visitors_per_hour,
AVG(conversion_rate_pct) AS avg_conversion_rate
FROM traffic.hourly_snapshots
WHERE traffic_date >= CURRENT_DATE - INTERVAL '12 weeks'
GROUP BY EXTRACT(HOUR FROM traffic_date), EXTRACT(DOW FROM traffic_date)
ORDER BY day_of_week, hour_of_day
Visualize as a heat map: hours on the Y-axis, days of week on the X-axis, and color intensity representing foot traffic volume. This instantly shows which days and hours are busiest, informing labor scheduling and promotional timing.
Using foot traffic data to forecast retail demand is the next step. Once you've established the relationship between foot traffic and sales, you can build predictive models that anticipate demand spikes and recommend inventory adjustments. Superset can visualize the output of these models—forecasted traffic and recommended inventory levels—alongside actuals, making it easy for store managers to act on predictions.
The importance of footfall analytics in retail extends beyond operations. Merchandising teams use traffic patterns to optimize product placement. Marketing teams correlate promotional campaigns with traffic lift. Real estate teams use traffic data to evaluate store locations and performance.
These three dashboards—inventory, margin, and foot traffic—aren't isolated. They're part of a closed-loop decision system:
Foot traffic increases → Store manager anticipates higher demand → Coordinates with supply chain to increase replenishment → Inventory velocity improves → Markdown rates drop → Margin expands.
Margin compresses → Finance team investigates → Finds that promotional activity is heavy in low-conversion stores → Recommends reallocating promotional spend to high-traffic locations → Conversion rates improve → Margin recovers without sacrificing traffic.
Inventory turns slowly in a category → Merchandising team checks foot traffic in that category's zone → Finds that traffic is low → Recommends moving the category to a higher-traffic zone or reducing shelf space → Inventory velocity improves → Working capital is freed up.
Superset's strength is making this loop visible and actionable. Because dashboards update in real time (or on a daily schedule, depending on your data refresh cadence), stakeholders see the impact of their decisions quickly. A store manager can adjust staffing based on traffic forecasts, then check the conversion dashboard the next day to see if the adjustment worked. A merchandising team can move a product category, then monitor inventory velocity and margin to validate the decision.
To make these dashboards fast and reliable, you need a thoughtful data architecture. Superset works best with a dimensional data warehouse—fact tables (transactions, inventory snapshots, traffic events) joined with dimension tables (products, stores, dates, suppliers).
Here's a minimal schema:
Fact Tables:
sales.transactions: Every POS transaction (store, SKU, quantity, price, discount, date, time)inventory.daily_snapshots: Daily inventory counts by store and SKUtraffic.hourly_snapshots: Hourly foot traffic counts by store and zoneinventory.variance_reports: Monthly or quarterly inventory variance (expected vs. actual)Dimension Tables:
products.master: Product ID, SKU, category, subcategory, supplier, cost, launch datestores.locations: Store ID, name, region, district, format (flagship, standard, outlet), opening date, square footagedates.calendar: Date ID, calendar date, day of week, week number, month, quarter, fiscal period, holiday flagKeep fact tables normalized and clean. Avoid storing calculated fields (margin %, conversion rate) in the fact table; calculate them in your Superset queries instead. This reduces data redundancy and makes it easier to update calculations as business rules change.
For performance, create indexes on foreign keys (store_id, sku_id, transaction_date) and commonly filtered columns. If you're running Superset against a data warehouse like Snowflake or BigQuery, clustering tables by date and store_id will speed up queries.
D23's managed Superset platform integrates AI capabilities, including text-to-SQL, that accelerate dashboard creation and reduce the need for SQL expertise.
Text-to-SQL lets non-technical users ask questions in plain English, and the system generates the SQL query automatically. For retail, this is powerful:
Text-to-SQL doesn't eliminate the need for well-structured data, but it dramatically lowers the barrier to self-serve analytics. Retail teams spend less time waiting for analytics requests and more time exploring data and making decisions.
MCP (Model Context Protocol) integration adds another layer. MCP allows Superset dashboards to call external APIs and services—your demand forecasting model, your inventory optimization engine, your promotional effectiveness calculator. A dashboard can fetch real-time predictions, compare them to actuals, and recommend actions, all without leaving Superset.
Retail teams often evaluate Superset against Looker, Tableau, and Power BI. Here's how they compare:
Looker is a powerful, enterprise-grade BI platform with a strong semantic layer. It's excellent for organizations with large data teams and complex data models. But Looker requires significant upfront investment (implementation, training, data modeling) and has high annual licensing costs. For a mid-market retailer, you're looking at $200K–$500K annually. Customization for retail-specific workflows (store operations, merchandising, supply chain) often requires Looker consulting, adding cost and timeline.
Tableau is the industry standard for visual analytics. It's intuitive and powerful for exploratory analysis. But Tableau is expensive (similar to Looker), and publishing dashboards at scale requires careful governance. Tableau's strength is in ad-hoc analysis; its weakness is in embedding analytics into operational workflows. If you need dashboards that store managers check daily, Tableau feels heavyweight.
Power BI is Microsoft's BI platform, tightly integrated with Excel and Azure. It's cheaper than Looker or Tableau, and if your organization is already deep in the Microsoft stack, it's a natural choice. But Power BI has a steeper learning curve than Superset, and its underlying data model (DAX, relationships) is less flexible for retail use cases where you need to pivot between different analytical views (by store, by category, by supplier, by geography).
Superset is open-source and lightweight. You connect it to your data warehouse, write SQL, and publish dashboards. No proprietary data model, no licensing fees beyond hosting. For retail teams that want speed, flexibility, and cost control, Superset wins. The tradeoff is that you need SQL expertise (or a text-to-SQL layer like D23 provides) and you're responsible for data quality and query optimization. But for teams that already have a data warehouse and a data engineer or two, Superset's flexibility and low cost make it the best choice.
Building effective dashboards is as much art as science. Here are principles that apply to any retail analytics platform, but are especially important in Superset:
1. Design for the User, Not the Data
A store manager doesn't care about inventory tables or transaction schemas. They care about: "Do I have enough stock of my top sellers?" and "Are my margins healthy?" Design dashboards around these questions, not around your data structure. Use clear, simple visualizations. A single number ("Current DIO: 45 days") often communicates more than a complex chart.
2. Optimize for Action
Every dashboard should have a clear call to action. "Inventory is turning slowly in the Southeast region" is useful. "Inventory is turning slowly in the Southeast region; consider reducing shelf space for category X or moving it to a higher-traffic zone" is actionable. Use dashboard filters and drill-downs to make it easy for users to isolate problems and explore solutions.
3. Update Frequency Matters
Daily dashboards are appropriate for inventory and foot traffic (data changes daily). Weekly or monthly dashboards are appropriate for margin analysis (promotional decisions and markdowns take time to flow through). Real-time dashboards are rarely necessary in retail and often create noise. Be intentional about refresh frequency.
4. Benchmark and Compare
A store with 45 days inventory outstanding might be good or bad, depending on context. Is it better or worse than last year? Better or worse than peer stores? Better or worse than the chain average? Always include comparisons and benchmarks. Superset's filter and drill-down capabilities make this easy.
5. Validate Data Quality
Garbage in, garbage out. Before you publish a dashboard, validate that the underlying data is clean. Are inventory counts reconciled? Are POS transactions properly categorized? Are foot traffic sensors calibrated? A single data quality issue can undermine trust in the entire analytics function. When exploring data in Superset, spend time understanding data lineage and validating calculations against source systems.
Rolling out Superset dashboards across a retail organization doesn't happen overnight. Here's a realistic phased approach:
Phase 1: Proof of Concept (Weeks 1–4)
Pick one high-impact dashboard (inventory management or margin analysis) and one pilot store or region. Build the dashboard in Superset, validate the data, and get feedback from stakeholders. The goal is to prove that Superset can deliver insights faster and cheaper than your current approach.
Phase 2: Expansion (Weeks 5–12)
Expand the pilot dashboard to all stores. Add the second and third dashboards (foot traffic, margin). Train store managers and merchandisers on how to use the dashboards. Establish a cadence for dashboard reviews (weekly store operations calls, monthly merchandising reviews).
Phase 3: Integration (Weeks 13–24)
Integrate dashboards into operational workflows. Store managers check inventory dashboards before placing orders. Merchandisers use foot traffic data to guide promotional placement. Finance teams use margin dashboards to forecast quarterly results. Establish a feedback loop for dashboard improvements.
Phase 4: Optimization and Scaling (Months 6+)
Optimize query performance and data refresh cadences. Add predictive models (demand forecasting, foot traffic prediction) to dashboards. Expand to adjacent use cases (supplier performance, labor productivity, customer lifetime value). Build a self-serve analytics capability so non-technical users can create their own dashboards.
For a mid-market retailer with 50–200 stores, here's a realistic cost breakdown:
Software and Hosting:
Compare to Looker ($250K+/year) or Tableau ($150K+/year). Superset is 10–40x cheaper.
Implementation:
ROI: Retail analytics typically delivers ROI through three mechanisms:
Inventory Optimization: Reducing DIO by 5 days across a 100-store chain with $5M average inventory per store frees up $25M in working capital. At 5% cost of capital, that's $1.25M/year in savings.
Margin Protection: Identifying and fixing markdown and shrinkage issues can recover 1–3% of revenue. For a $500M retailer, that's $5–$15M annually.
Operational Efficiency: Faster decision-making and better data reduce the time finance and merchandising teams spend on manual analysis. A team of 5 people spending 20% of their time on analytics can redirect that effort (1 FTE) to higher-value work, saving $100K–$150K/year.
Conservatively, a mid-market retailer should see $500K–$2M in annual ROI from retail analytics dashboards. The payback period is typically 3–6 months.
Once you've established a foundation with descriptive dashboards (inventory, margin, foot traffic), the next frontier is predictive analytics.
Predictive analysis using foot traffic data improves demand forecasting. Machine learning models can ingest historical foot traffic, weather, events, and promotional calendars to forecast traffic 1–4 weeks ahead. Superset can visualize these forecasts alongside actuals, making it easy for store managers to see when predictions are accurate and when they miss.
Similarly, you can build demand forecasting models that predict unit sales by SKU and store. Feed these forecasts into inventory optimization algorithms that recommend replenishment quantities. Superset becomes the control center where store managers see recommendations and approve or override them.
Text-to-SQL and natural language interfaces (powered by LLMs) make these advanced capabilities accessible to non-technical users. A store manager can ask, "Based on foot traffic predictions, how much inventory should I order for category X next month?" and get a data-driven recommendation.
Retail is a high-velocity, data-intensive business. Decisions about inventory, pricing, and merchandising need to be made daily, sometimes hourly. Traditional BI platforms are too slow, too expensive, and too rigid for this pace. D23's managed Apache Superset platform gives retail teams the speed and flexibility they need to compete.
By implementing the three canonical dashboards—inventory management, margin analysis, and foot traffic intelligence—you create a closed-loop decision system where data flows from operations to analytics to action and back to operations. Store managers see inventory levels and adjust ordering. Merchandisers see foot traffic patterns and optimize product placement. Finance teams see margin trends and adjust promotional spend. The result is faster decision-making, better resource allocation, and improved profitability.
The economics are compelling. Superset costs a fraction of Looker or Tableau, implements in weeks instead of months, and requires less ongoing maintenance. For retailers that want best-in-class analytics without the traditional BI overhead, Superset is the answer. Pair it with expert data consulting and AI-powered insights, and you have a competitive advantage that's hard to replicate.