BI for retail: the reports you actually need

A retail chain can easily end up with hundreds of reports that nobody opens, while the owner still asks the same question every week: how are we actually doing? BI gives an answer only when the reports are built around decisions rather than around the data that happens to be available.

This article covers the reports a retailer really needs and the calendar, tax and currency details that often distort the numbers.

Start with the decisions

Before building a dashboard, write down who decides what, and how often:

• Owner or CEO: which stores, formats and categories to invest in or close.
• Commercial team: which products to keep, what to promote, how to set prices.
• Supply chain: what to order and where to move stock.
• Store manager: what to do today in this particular store.

Each of these people needs a different report, not one huge dashboard with filters.

The reports that matter

1. Sales overview

Revenue, number of receipts, average basket and items per receipt, by day, store and category. Compare with last year and with the plan. This is the report people open every morning, so it must load quickly and be correct.

2. Margin by category and product

Revenue alone can hide problems: a category may grow while earning less. Margin should take discounts, promotions and returns into account, and everyone needs to use one definition of it. If finance and the commercial team calculate margin differently, the report will be argued over rather than used.

3. Stock and out-of-stocks

Stock on hand, days of cover, out-of-stock items and slow movers by store. Lost sales from empty shelves do not appear in any sales report, so they have to be estimated separately. For most retailers, this is where BI pays off fastest.

4. Promotion effectiveness

What did a promotion really bring: extra sales or just discounted sales you would have made anyway? Compare with a similar period and with stores outside the promotion. Also check whether sales of neighbouring products fell at the same time.

5. Channels

Stores, your own online shop, delivery apps and marketplaces. Compare channels after commissions and delivery costs, not on revenue. An order through an app may look like growth and still earn far less than the same order in a store.

6. Store comparison

Comparable stores side by side: sales per square metre, per employee and per receipt. Compare only stores open for the whole period, otherwise new openings will distort the picture.

Calendar, tax and currency traps

Holidays move every year

Ramadan and Eid, Easter, Lunar New Year and Diwali fall on different dates of the Gregorian calendar each year. Comparing March with last March may therefore set a holiday period against an ordinary one, and the growth or decline on the screen will be false. Add holiday periods to your calendar dimension, and compare equivalent days relative to each holiday.

Sales tax differs by country

VAT, GST and sales tax rates and rules differ between countries and sometimes within one country. If you operate in several markets, decide whether reports show revenue with or without tax and apply this consistently. Otherwise, comparisons between countries will be misleading.

Weekends are not the same

In most countries the weekend is Saturday and Sunday, but in some it is Friday and Saturday. A report that compares "weekend sales" across countries needs to reflect that, and so do week definitions. Agree on when the reporting week starts.

Several currencies

A business in several countries needs one reporting currency and one rule for the exchange rate: daily, monthly average or a fixed budget rate. Otherwise, currency moves will look like sales growth or decline.

Names in several languages

The same product or supplier may appear in one language in one system and in another language elsewhere, with several spellings. Without a single product reference (MDM), sales of one item are split across several lines and no report can be trusted.

Before you build: check the data

Most BI projects stall not because of the tool but because the numbers do not match the accounting system. Before building dashboards:

1. Agree on the definitions of revenue, margin, returns and KPIs.
2. Reconcile POS, ERP and accounting totals. A short data quality audit will show where they differ.
3. Clean the product and store references.
4. Choose the tool last, using clear criteria.

Summary

Good retail BI starts small: a daily sales view, honest margin, stock and channel comparison after costs. Build in the calendar, tax and currency details from the start, because they are expensive to add later. Once people trust these reports, adding more depth is easy.

If you are planning a BI project, see our BI and analytics service. We start with your decisions and your data, and only then build the reports.