Skip to Content

Retail Sales Analytics


Retail sales analytics in Power BI | KoderXpert

Demonstration build · Power BI on a public e-commerce transaction log

A sales log is a record. A model is an argument.

the charts took a day, the trust took longer

An online retailer with a long tail: thousands of low value transactions, hundreds of small SKUs, customers across twenty-one markets and a pronounced pre-Christmas peak. The log holds everything and explains nothing. This build profiled it, cleansed it and turned it into a single filterable cockpit.

Line art of a retail transaction log becoming a cockpit: a receipt feeding a world map with one dominant market, a seasonal curve peaking late in the year, and two product rankings that disagree
profile, cleanse, argue ✨
concentration!90%of customers sit in a single market
2.5Xpeak to trough swing across the trading year
4373customers ranked across 21 markets
21markets, correctly sized rather than counted
Build
Demonstration, public dataset
Domain
Retail sales analytics
Source
Invoice line transaction log
Platform
Microsoft Power BI
Headline result
Three arguments the log was hiding

01 · Overview

The log records. The model explains.

  • An e-commerce retailer with hundreds of low value SKUs, 4,373 customers across twenty-one markets and a strong seasonal peak. The transaction log holds invoice, product, quantity, price, customer, country and date.
  • The retailer knows exactly what was sold. What they do not know is what the pattern means: whether the international business is real, which product moves in volume but earns nothing, and when the year actually happens.
  • This build profiled the log before trusting it, gave returns and non-product SKUs explicit rules, and surfaced it as one filterable cockpit rather than five static reports. It is a demonstration build on a public dataset, not a client engagement.
most of the work happens before the first chart
a raw log in, three arguments out ✨

02 · The challenge

Every question needed a fresh pivot table, and every pivot table disagreed.

The failures below are not reporting failures. They are definition failures, and they all happen before a chart is drawn.

Revenue was recomputed, never defined

Quantity times price, summed in a spreadsheet. Whether returns were netted and whether postage counted changed with whoever built the sheet.

The cost → no two reports agreed

Geography looked bigger than it was

Twenty-one countries in the customer list creates the impression of an international business. Nobody had checked what share the home market actually held.

The cost → a concentration risk read as a footprint

Volume was mistaken for value

Best seller lists were built on units shipped. The products that earned the most were a different list entirely, and it was never drawn.

The cost → pricing decisions made blind

Seasonality was folklore

Everyone knew autumn was busy. Nobody could say by how much, or when exactly the peak turned, so stock and staffing plans assumed a flat year.

The cost → cash and stock planned against a guess

Returns are negative quantities

Cancellations sit in the same table as sales as negative lines. Sum the column naively and revenue is wrong; net it silently and the return rate disappears.

The cost → the same column, two different answers

Not every SKU is a product

Postage and manual adjustments live in the product column. Left in, top products by revenue reports on shipping fees.

The cost → one look at that list kills the tool

trust in a BI tool is lost once, permanently, on a detail

03 · Before vs after

Same log. One page instead of a pivot table per question.

Left is reporting straight off the raw log. Right is what one cleansed model replaced it with.

BeforeAfter
reading the log
  • Revenue recomputed per report, with returns handled differently each time
  • Twenty-one countries counted, none of them sized
  • Best seller lists built on units shipped alone
  • Seasonality known as folklore rather than quantified
  • Postage and adjustments sitting inside product rankings
  • A fresh pivot table for every question
a record, not an argument
one page, any slice
arguing from it
  • Revenue defined once in DAX, returns netted and rules written down
  • The home market sized at roughly nine customers in ten
  • Units shipped and revenue earned as two separate rankings
  • A 2.5X peak to trough swing, available to stock and staffing
  • Non-product SKUs excluded from product analysis and kept in revenue
  • Date and country filters on one page

drag the orange handle, or use the arrow keys ✨

04 · Goals

What the build had to achieve

Six goals set before the first chart, because in retail analytics the charts are the easy part.

Define revenue once. Returns and non-product SKUs handled explicitly rather than silently, with the rule written down and reused everywhere.

State the concentration. How much of the business one market actually is, placed where it cannot be missed rather than on page four.

Separate volume from value. Units shipped and revenue earned as two rankings, because merged into one they hide each other.

Make seasonality legible. The shape of the year month by month, so stock, cash and staffing plans start from a curve rather than a memory.

Rank the long tail. Hundreds of SKUs with none dominant reduced to the handful that move the number.

Filter, do not fragment. Date and country filters on one page instead of five static reports that drift apart.

05 · What we delivered

One page, five questions, any slice

A single cockpit rather than a suite, because every extra page is another chance for two reports to disagree.

Block 01

Revenue and customer headline

Total revenue and total customers, both responding live to the date and country filters, so any slice of the business can be sized in one click.

any slice, one click
Block 02

Revenue by month

The trading year as a shape: a soft first half, a climb through autumn and a peak in the run up to Christmas. Stock and staffing plans start here.

the year has a shape
Block 03

Customers by country

Twenty-one markets ranked, and the ranking immediately reveals that the home market holds roughly nine customers in ten.

the most important fact, first
Block 04

Units sold by country

The same skew in volume rather than headcount, confirming that international is real but small, a base to build on rather than a growth story to report.

real, but not yet a story
Block 05

Revenue by product against units

Two rankings kept deliberately side by side, because the SKU that ships the most units and the SKU that earns the most revenue are not the same SKU.

the gap is a pricing decision
Block 06

The cleansing rules, visible

Returns netted, non-product SKUs excluded from product analysis, missing customer IDs decided rather than defaulted, and the partial final month flagged.

stated, not assumed
Interactive 01

Twenty-one markets, one business

a long country list flatters, a ranking tells the truth

Customers by country, as a plain ranking. The home market holds roughly nine customers in ten, which on a linear axis flattens every other market into the baseline. Switch to units shipped to confirm the same skew in volume rather than headcount.

Interactive ranking of customers and units by country Customers by country twenty-one markets, top ten shown one market is the business
--'Select a market'
~90%Share held by the home market
Reading

Twenty-one countries on the list. One of them is the company.

That is a concentration risk, not a footprint
Measure

06 · Functional deep dive

The work nobody sees

Four rules that decide whether anyone believes the second chart they look at.

Returns separated from sales

Cancellation lines carry negative quantities in the same table as sales. Summing naively overstates nothing and understates everything, and netting silently destroys the return rate.

  • Cancellations identified explicitly rather than filtered by sign alone
  • Revenue reported net, with the return rate still available as its own measure
  • The rule documented so next month's number is built the same way

Non-product SKUs excluded from product analysis

Postage charges and manual adjustments live in the product column. Left in, they contaminate every top product ranking while still belonging in revenue.

  • Excluded from product rankings, retained in total revenue
  • The exclusion list version controlled rather than hand edited
  • A visible note on the product visual, so the choice is auditable

Missing customer IDs decided, not defaulted

A meaningful share of lines carry no customer ID. Counting them as a customer inflates the base; dropping their revenue understates the business.

  • Customer counts exclude unidentified lines
  • Revenue includes them, because the sale happened
  • The asymmetry stated on the page rather than hidden in a measure

The boundary period flagged

The final month in the log is a partial period. Charted without comment it reads as a collapse rather than a cut off, and someone will present it as a trend.

  • Charted, but annotated as partial
  • Excluded from growth comparisons rather than silently included
  • The same treatment applied to any future partial period
Interactive 02

The SKU that ships is not the SKU that earns

two rankings, deliberately kept apart

Ten products from the long tail, sorted by units shipped. Switch to revenue and the order rearranges, which is the most useful gap in retail: the high-volume, low-margin line sitting beside the quiet one that pays the rent. Merged into one chart, they hide each other.

Interactive product ranking by units shipped, revenue and revenue per unit Product performance sorted by units shipped
--Top of the current ranking
7Products in this sample
What the sort changes

Ranked by units shipped, which is how best-seller lists are usually built.

The gap between the two rankings is a pricing decision
Rank on

07 · How we built it

Six stages from raw log to cockpit

Profiling came before modelling, and modelling before any visual. That order is the build.

01

Data profiling two days

Read the log before trusting it: nulls, negatives, outliers and odd SKUs found first, so the cleansing rules were written against reality rather than assumption.

02

Cleansing rules three days

Returns, postage, manual adjustments and missing IDs each given an explicit rule, documented and version controlled rather than applied in a one off query.

03

Data modelling week two

Product, customer, country and date dimensions built around the invoice line fact, with a proper date table so time intelligence behaves.

04

DAX development four days

Revenue, customer and unit measures defined once and reused across every visual, so two charts on the same page cannot disagree.

05

Cockpit design three days

One page: headline KPIs first, filters on top, rankings below, with the concentration finding placed where it cannot be scrolled past.

06

Validation and templating final week

Every headline figure reconciled back to the raw log, the findings written down, and the model generalised so it deploys against any retail transaction log.

08 · Engineering notes

What makes the model portable

It was built as a template from the start, because the value is the definitions rather than this particular dataset.

A dimensional model over an invoice line fact

The log is a single flat table. Everything useful comes from putting proper dimensions around it rather than pivoting the flat table repeatedly.

  • Product, customer, country and date dimensions built from the log itself
  • Date table marked as a date table so time intelligence is trustworthy
  • Invoice line kept at its natural grain, never pre-aggregated

Power Query doing the cleansing, not the report

Every exclusion and type change happens in the query layer where it is inspectable, rather than inside a visual where the next person cannot find it.

  • Cleansing steps named so the query reads as a description of the rules
  • Query folding preserved where the source supports it
  • Exclusion lists held as parameters rather than hard coded

Measures defined once

Revenue, customers and units are single measures reused by every visual, which is what stops the headline and the chart beneath it disagreeing.

  • Base measures for arithmetic, display measures for formatting
  • DIVIDE with a blank fallback for every rate
  • Naming convention that sorts base and display apart

Built to transfer

The model and the measures move to any retail transaction log; only the data stays behind.

  • Source columns mapped through a single staging query
  • Nothing in the measures depends on this dataset's values
  • Findings documented separately from the model
Interactive 03

What counts as revenue

returns and postage, decided, not defaulted

Gross invoice lines include cancellations as negative quantities and carry postage and manual adjustments in the product column. Move the slider to change how much of the gross is treated as genuine product revenue and watch the headline move. The point is not the number, it is that the rule has to be written down.

Interactive meter showing how return and non-product handling changes headline revenue Gross lines 10.6M gross Counted as revenue -- Excluded -- Net revenue -- revenue defined once, and reused everywhere
--Excluded from product revenue
--Share counted
Reading

At the agreed rule, returns are netted and postage is excluded from product analysis.

Trust in a BI tool is lost once, permanently, on a detail
92%

09 · The impact

Three findings the log was hiding

None of these required new data. All of them required the log to be modelled rather than summed.

90%of customers sit in one market, a concentration risk not a footprint
2.5Xpeak to trough revenue swing across the trading year
21markets correctly sized rather than merely counted
4373customers, counted on a rule that is written down
  • Volume and value pulled apart, so the pricing gap became visible
  • Revenue defined once, with returns netted and postage excluded
  • Seasonality became a plan rather than a memory
  • The long tail reduced to the handful of SKUs that move the number

the log recorded it, the model explained it

10 · The stack

What this build runs on

Five layers, and a model that transfers to any retail transaction log.

Source
Invoice line logProductCustomerCountryDate
Transform
Power QueryReturns identifiedNon-product SKUs excludedTypes enforced
Model
Star schemaProduct, customer, country and date dimensionsMarked date table
Measures
DAXRevenue net of returnsCustomersUnitsRankings
Report
Microsoft Power BISingle filterable cockpitDate and country filters
Governance
Cleansing rules documentedFindings written downDeploys to any log
what the build came down to
The first time a manager finds postage sitting in the top products list, the dashboard is dead. Trust in a BI tool is lost once, permanently, on a detail, and the details that kill it are always the ones in the data, not the ones in the design.
The build outcome, KoderXpert retail analytics demonstration
Built on a public UK online retail dataset, not a client engagement

11 · FAQ

Questions retailers ask before commissioning a build like this

Yes, that is what it was built for. The model and the measures transfer; only the data stays behind. Source columns are mapped through a single staging query, so pointing it at a different log is a mapping exercise rather than a rebuild, and the cleansing rules are parameters rather than hard coded values.

Cancellations are identified explicitly rather than filtered by sign, so revenue is reported net while the return rate remains available as its own measure. Netting silently is the common mistake: the headline looks right and a genuinely useful number quietly disappears.

Because both are true. Postage is real income, so it belongs in revenue. It is not a product, so it does not belong in a ranking of products. Keeping it in both places is what produces a top products list with shipping fees at the top, which is how a dashboard loses its audience.

Customer counts exclude them and revenue includes them, because the sale happened even though the customer is unidentified. The asymmetry is stated on the page rather than buried in a measure, so nobody later discovers it and stops trusting the rest.

Only if it is charted without comment, in which case it reads as a collapse and someone will present it as a trend. We chart it, annotate it as partial and exclude it from growth comparisons, and apply the same treatment to any future boundary period.

Yes, and it is a deliberate design decision rather than a chart trick. The concentration is stated as a headline first, because it is the most important fact about the business, and the smaller markets are then ranked among themselves so they can be compared with each other.

Once the year has a measured shape rather than a remembered one, stock, cash and staffing plans can be built against it. A 2.5X peak to trough swing means a flat plan is wrong in both directions: overstocked in spring and short in November.

Not for this. A single transaction log is enough to build a useful cockpit, and doing so usually clarifies what a warehouse would need to hold later. We would rather prove the definitions on the log you have than wait for infrastructure.

Two to four weeks for a standard transaction log, most of it in profiling and cleansing rather than in visuals. Multiple channels, a loyalty system or a product hierarchy to reconcile will extend it, and we scope that against your data.

Yes, where cost data exists at line level. It is the single most valuable addition, because the volume against value gap becomes a margin gap and the pricing conversation gets sharper. Where cost sits only at product level we model it as an assumption and label it as one.

Yes. All of them export invoice line data with the same essential columns, and the differences are handled in the staging query. Marketplace exports usually need more care around fees and returns, which is exactly the work this build is about.

Because we spend the effort before the first chart, which is where retail dashboards are won or lost. We work across ERP, finance and operational data for clients in manufacturing, retail, healthcare and financial services. See our data analytics services or talk to a consultant.

you already have the log, now read it

Recording every sale. Do you know what the pattern means?

Tell us what your transaction log holds and what you keep having to rebuild in a spreadsheet. We will define the rules once, model it, and hand you a cockpit that transfers.

Read next: a shop floor where the plan said 1.6 hours and the machine took 7

the story continues...

More client stories

All case studies

Your business could be the next story here.

Same process, already proven across 20+ industries.

Your business is not a template. Your ERP should not be either.

Every project on this page started the same way: a conversation about how the business actually runs today, and where the manual hand-offs are. That is a good place to start yours too.