A merchandising team moves a product from Accessories to Gifts. The next morning, last month’s Gifts revenue rises even though no new orders were placed in that month. The dashboard has joined historical order lines to today’s product classification, silently rewriting the category labels attached to old sales.
EcomToolkit treats this as a reporting-policy decision before a database problem. Sometimes the business wants historical sales under today’s taxonomy; sometimes it needs the categories that existed when the orders were accepted. Both views are useful when labeled. This guide shows how to build the distinction without inventing revenue or hiding classification changes.
Table of Contents
- Choose the historical question first
- Understand what a changing dimension stores
- Reproduce the reporting difference
- Join each sale to one valid version
- Handle returns and corrections deliberately
- Make the model usable for trading teams
- The EcomToolkit view
Choose the historical question first
Write the decision in plain language. A buyer reviewing a past category manager’s performance may need the historical classification. A team planning a new Gifts range may want all previous sales of products now assigned to Gifts. One view evaluates the old operating structure; the other evaluates the present assortment using past transactions.
Name these views explicitly, for example category at sale and current category. Do not offer a generic category filter that switches behaviour depending on which report the analyst opens. Include the classification basis in exported files so that spreadsheets do not lose this essential context.
Also decide the transaction grain. An order can contain products from several categories, so assigning the entire order to every matching category duplicates revenue. Begin with order lines and a documented revenue allocation. The existing SQL join fanout guide explains why join cardinality is a separate requirement from historical correctness.
Understand what a changing dimension stores
Microsoft’s star schema guidance distinguishes Type 1 changes, which overwrite attributes, from Type 2 changes, which retain versions. In ecommerce terms, a Type 1 product record might contain only today’s category, while Type 2 records preserve the category valid during each defined interval.
A product identifier represents the continuing product. A version identifier represents one historical state. Store both concepts clearly rather than reusing the same field for the product and its version. Otherwise an analyst can accidentally treat a new classification as a newly launched product.
Effective time also differs from ingestion time. If a category became valid on Monday but the warehouse learned about it on Wednesday, those are two separate facts. Keeping both dates makes late updates explainable and helps distinguish business changes from delayed data delivery.
| Field | Illustrative value | Purpose |
|---|---|---|
| Product identifier | P-104 | Stable business identity |
| Version identifier | P-104-v2 | Specific attribute version |
| Category | Gifts | Classification for this version |
| Valid from | 1 September, 00:00 UTC | Inclusive business start |
| Valid to | 1 October, 00:00 UTC | Exclusive business end |
| Recorded at | 2 September, 09:10 UTC | Warehouse receipt time |
Use an explicit time zone and interval convention. Half-open intervals include the start and exclude the end, allowing adjacent versions to meet without both matching the boundary. That convention must be reflected in the actual join, not merely described in a document.
Reproduce the reporting difference
Suppose a fictional product generated £600 in August while classified as Accessories. It generated another £400 in September after moving to Gifts. Under category at sale, Accessories receives £600 and Gifts receives £400. Under current category, Gifts receives all £1,000.
Neither interpretation changes total revenue. If the grand total changes after switching the classification basis, investigate duplicated joins, dropped lines or a different eligibility filter. A taxonomy change should move amounts between groups, not create or delete eligible sales.
| Reporting basis | Accessories | Gifts | Total |
|---|---|---|---|
| Category at sale | £600 | £400 | £1,000 |
| Current category | £0 | £1,000 | £1,000 |
| Classification-only difference | −£600 | +£600 | £0 |
These are hypothetical amounts, not a merchant case study. Their purpose is to provide a simple reconciliation that stakeholders can reproduce. When explaining a real change, include a similar bridge with the affected product count and the category transfers rather than circulating a chart with unexplained growth.
A category can also split into several new categories. Mapping an old category to two new ones is not enough to allocate its historical sales. You need product-level rules, a defensible allocation method or an explicit unallocated group. Do not divide equally unless that is the agreed analytical policy.
Join each sale to one valid version
For an at-sale view, match the product identifier and the sale timestamp to the version interval. The conceptual condition is that the sale is on or after valid from and before valid to. An open-ended current version needs a consistent representation that the warehouse can query safely.
Test that every eligible order line matches exactly one version. Zero matches indicate missing history, timestamp disagreement or an interval gap. More than one match indicates overlapping versions or an incorrect join. Report these exceptions instead of allowing a dashboard to hide them through aggregation.
Some pipelines resolve the version identifier when loading the fact table. Others apply a temporal join during reporting. The best choice depends on the warehouse and correction workflow, but both need a documented policy for late dimension updates and reproducible results.
Keep an unknown version for genuinely unresolved classifications if your model supports it. Preserve the revenue and expose the unknown amount rather than dropping the sale. A fully reconciled report with visible unknowns is more informative than a polished report that quietly loses money from the total.
Handle returns and corrections deliberately
A refund posted after a category change raises another policy question. Should it reduce the category assigned to the original sale, today’s category, or a separate returns view? For evaluating historical merchandise performance, linking to the original order line is often useful, but the report’s time basis still needs definition.
Distinguish a business reclassification from a correction of an earlier mistake. If a product was wrongly labeled at launch, preserving the erroneous label forever may not meet the reporting objective. Maintain an audit trail and a restatement policy so that corrections are intentional and reviewable.
The late-arriving events guide covers transaction restatements. Dimension corrections add another layer: an unchanged transaction can move between analytical groups because its descriptive history was corrected. Keep those change reasons separate in a reporting log.
Make the model usable for trading teams
Do not expose warehouse terminology as the only explanation in the dashboard. Use short labels such as historical category and current assortment. Add a note stating the effective-date rule and whether the displayed period has been restated since the last export.
Give trading teams a reconciliation view with total revenue, classified revenue, unknown revenue and the value transferred by classification changes. This lets them distinguish a genuine sales movement from a taxonomy movement before changing a buying plan or a marketing budget.
Define who approves retrospective mappings. A category owner may suggest the commercial interpretation, while the data owner validates completeness and the finance owner checks revenue consistency. These responsibilities can be lightweight; the important point is that a routine product edit should not silently redefine an executive KPI.
Plan retention for the mapping history as well as the transaction history. Keeping seven years of order lines will not reproduce old category reports if product versions are discarded after twelve months. Record which exports contain the required attributes and test a restore of a small historical period. This turns the retention policy into a capability the reporting team can actually rely on.
Run a small acceptance fixture before deployment: a product with one category change, a sale on each side, a sale exactly at the boundary, a later refund and an unmatched product. Confirm both report views and the unchanged grand total. Then repeat the reconciliation on the actual affected date range.
The EcomToolkit view
Historical reports need historical definitions, or an explicit decision to restate them. Preserving every old label is not the objective; making the selected interpretation reproducible is. If merchandising changes keep rewriting your sales story, request an EcomToolkit analytics audit to align product history, revenue allocation and the decisions your category reports support.
