A marketplace operator could report revenue and order counts. It could not answer three operational questions: where in the order journey the customer experience breaks down, which sellers create a disproportionate share of late deliveries and poor reviews, and which categories are actually growing rather than merely being counted differently. Each question had a dashboard on the executive floor. None of them reconciled.
The decision
The operations and commercial teams needed to agree on who to intervene with and where. That meant one view of the journey, seller metrics that were comparable across different-sized baskets, and a revenue baseline that did not change depending on which report someone opened. The first problem was not analytical. It was agreeing what an order, an item and a payment actually are.
What we assembled
Nine relational tables covered the marketplace:
- Orders: purchase, approval, carrier handoff, delivery and estimated-delivery timestamps, plus order status.
- Order items: product, seller, price, freight value and shipping deadline.
- Payments: payment type, instalments and payment value.
- Reviews: review score, comment text and response timestamps.
- Products and categories: category name, dimensions and listing attributes.
- Customers and sellers: location identifiers and state.
- Geolocation: zip-prefix coordinates, used for distance and regional views.
The join looked routine and was not. Order, item and payment are different grains, and cancelled or unavailable orders behave differently at each stage. Joining first and defining later is how marketplace revenue gets double-counted.
The traps we designed around
- Grain inflation. An order with three items and two payment rows becomes six rows under a naive join. Revenue calculated there is not revenue; it is a row count with a currency sign.
- Late does not mean dissatisfied. A late delivery often produces a poor review, but not always. Treating every late order as a service failure overstates the problem and sends the team after sellers whose customers were never complaining.
- Small sellers and single claims. A seller with ten orders and one bad review is not comparable to a seller with a thousand orders. Ranking them on the same scale produces confident nonsense.
- Missing timestamps. Cancelled and unavailable orders lack handoff or delivery dates. Silently dropping them removes the very cases the operations team most needs to see.
How the view was built
- Fix the grain before any metric. Revenue at the payment grain, product and seller metrics at the item grain, delivery metrics at the order grain. Each metric carries its grain in the definition, not in a footnote.
- Build a star schema. Orders as the fact table, with dimensions for customer, seller, product, category, date and geography. This made every downstream number reproducible and every disagreement traceable.
- Define the funnel explicitly. Purchase → approval → carrier handoff → delivery. Elapsed time was measured at each step, and lateness was defined against the promised date rather than a fixed number of days.
- Separate delay from dissatisfaction. Review scores were compared for on-time and late orders, then split by seller response and compensation. The gap between the two is the part of the problem the operator can actually manage.
- Make seller comparisons fair. Volume, on-time rate, freight ratio, review distribution and cancellation share were reported with confidence intervals. Sellers were compared within category and region, where delivery economics are similar.
- Build for operations. The dashboard opens on the funnel, drills through to category, seller and region, and carries a data-quality panel showing which records were excluded and why. A number nobody can audit is a number nobody will use.
What this changes
- The revenue baseline was corrected before any performance conversation. Several earlier reports had double-counted revenue by joining payments to items. Fixing the grain changed the starting point for every later comparison.
- The seller review list became shorter and more credible. Genuine outliers were separated from small-volume noise and reporting artefacts, which made it easier for the commercial team to act on the list.
- The journey replaced the league table as the main view. The operator could see which stage was failing — approval, handoff or delivery — instead of inferring it from seller rankings.
What you receive
- A star-schema data model with documented metric definitions and grains.
- A delivery funnel with stage-level timings and late-delivery rates.
- A seller scorecard with volume-adjusted quality metrics and confidence intervals.
- Category and regional views for commercial planning.
- A dashboard with drill-through and a data-quality panel.
Where this applies
This approach fits marketplaces, e-commerce operators and any business that stitches orders, items, payments and reviews together from separate systems. The minimum inputs are a stable order identifier, item and payment detail, promised and actual delivery dates, and a seller identifier. The first deliverable is usually a grain audit and one reconciled revenue definition — before a dashboard is designed, because a dashboard built on the wrong grain simply distributes the error faster.
This is an illustrative case study built on an anonymized dataset; it does not report results from a specific client engagement. It reflects our standard method for marketplace and e-commerce analytics.
