Case study · E-commerce data engineering
Revenue − everything = the truth.
The business, a US home-goods retailer, sold on a major US retail marketplace, but orders, costs and ad spend sat in three systems that didn’t share an ID. I built a pipeline that pulls all three together into one daily profit figure per product family, with year-over-year comparisons one click away.
Key decisions and results
Decisions
- Single-purpose Node.js sync jobs write with insert-or-update and re-read a rolling window, so re-runs are safe and late changes are picked up.
- All matching happens in the report service: item ID → SKU → product family, with exact rules ahead of prefix rules and unmatched rows kept visible.
- Growth comparisons load only when opened, so the main report stays as fast as a single report.
Results
- One daily profit figure per product family, with ad spend tied to the products it promoted.
- Filter by manager or parent SKU, and compare with last year, the previous period or last month.
The problem
Three systems, three kinds of ID, no profit number
On this marketplace the numbers that decide whether a product makes money are split across tools that were never designed to talk to each other. The order system knows what sold and what it cost. The ad platform knows what was spent, but it counts by the marketplace’s own item ID, not by the seller’s SKU. And the business thinks in parent SKUs: product families with a named category manager, like one rug design sold in eight sizes.
So “did this rug line make money on the marketplace last week?” meant exporting spreadsheets from several dashboards and matching rows by hand.
- Inventory / order platformsWhat sold, at what price, with what COGS, commission and shipping?Keyed by the seller’s SKU, which sometimes carries a marketplace-specific prefix.
- Marketplace ad platformWhat did we spend promoting it, per day?Keyed by the marketplace’s item ID and grouped by campaign, with no SKU anywhere.
- Internal catalogWhich product family is this, and who manages it?Defined as SKU prefix rules, plus exact-match overrides for individual child SKUs.
The architecture
Sync jobs feed the database; the service does the matching
I split the work into two layers. Small, single-purpose Node.js jobs pull each source’s API into its own MySQL table, and safe re-runs are their only job. The Spring Boot service then does the business logic when someone opens the report: match every row to a product family, merge sales with ad spend, and lay the result out day by day.
Inventory platform
- InventoryOrders + P&L API · rate-limited
- Marketplace order P&Lrolling 14 days · one channelAll-channel product P&Lrolling 40 days · every company
- Inventory P&Lline-level and order-level P&L
Order platform
- Order platformOrders API · cursor-paged
- Order platform syncrolling 7 days · orders + lines
- Order linesorders + order lines
Ad platform
- Ad platformCampaign + item perf. · OAuth · JWT grant
- Ad item performanceper campaign · per item · per day
- Ad performancecampaign × item × day
Spring Boot
- MySQL tablesthe three tables above + item ID and SKU prefix lookups
-
Profit report service
- item ID → SKU
- SKU → parent + manager
- filter parent / manager
- merge sales + ad spend
- fill empty days, total
- Daily parent P&L+ lazy growth panels
Ingestion
Four jobs, each built for the API it talks to
Each vendor API breaks in its own way, so each job handles its own failure modes. Every job re-reads a rolling window of recent days instead of only new records, because marketplace data keeps changing after the fact: orders ship late, refunds come in, ad spend gets revised.
Marketplace order P&L
Lists this marketplace’s orders from the last two weeks in the inventory platform, then pulls the full profit and loss for each order and each line item.
- Records each order’s date before the P&L call, so every line gets an order date as well as a ship date.
- Saves line-level and order-level P&L to separate tables.
All-channel product P&L
The wider backfill. It reads the order IDs already stored in the database, groups them by company, and re-fetches P&L for the last 40 days.
- Sends order IDs in batches of 250 per request.
- When the API returns
409, reads the reset time it sends back and waits exactly until then, up to 5 retries. - Pauses between companies so it doesn’t use up the shared rate limit.
Order platform sync
Follows the order platform’s next cursor through every page of orders since a UTC cutoff, then saves orders and order lines separately.
- Splits the typed
charges[]andcosts[]arrays into real columns: price, discount, shipping, COGS, commission. - Keeps order status on each line, so the report can switch between shipped and ordered.
Ad platform performance
Gets a token through the marketplace’s OAuth password grant, using a signed JWT as the credential, then pulls daily search-ad results for each campaign.
- Stores the access token and reuses it until it expires, instead of logging in on every run.
- Handles each campaign separately, so one failing campaign doesn’t stop the others.
- Stores clicks, impressions, spend, units and attributed sales per campaign, per item, per day.
Reconciliation
Turning three kinds of ID into one product family
The core of the project is matching IDs. An ad row has only the marketplace item ID, and a sale row may have both a channel SKU and an internal inventory SKU. Every row has to end up under the right parent SKU and manager before any numbers are added up.
Item ID → SKU. A lookup table maps each marketplace item ID back to the seller’s SKU, so ad spend can go to a product.
Collect every SKU. Channel SKUs, inventory SKUs and the SKUs matched from ads go into one set, so the matching runs once for all of them.
Exact match beats prefix. Prefix rules like “starts with
RUG-” assign most SKUs, but an exact child-SKU rule always takes priority, and a later prefix rule can’t override it.Remove the channel prefix. Marketplace-specific SKUs are matched a second time without their prefix, so they land in the same family as the base product.
Merge by family and day. Sales and ad spend are added into one table of parent SKU × day. Days with no activity get zero rows, so every row has a full set of columns.
// every cost the business actually pays profit = revenue − itemCost // COGS − shipping − taxesAndFees // marketplace commission − adSpend // ad platform, item ID → SKU − unitsSold × $1 // warehouse handling // rows without a match stay visible parent = tag?.parentSku ?? "NO_PARENT_SKU" manager = tag?.manager ?? "NO_MANAGER"
Unmatched rows are never dropped. They’re grouped under NO_PARENT_SKU, so gaps in the catalog show up in the report instead of quietly lowering revenue. The fix is a new prefix rule, not a code change.
The P&L model
One line that counts every cost
Every cell in the report comes from the same formula. Warehouse handling is charged at a flat $1 per unit, so products that look profitable only because nobody counted fulfillment show up for what they are.
Design decisions
Choices that made the report trustworthy and fast
Two order sources, one switch
The report can build its sales from the order platform or from the inventory platform’s P&L, and both go through the same matching and totals.
That made switching order systems a checkbox, not a migration. The team could compare both sources side by side until they trusted the new one.
Order date vs ship date, shipped vs ordered
Finance wants shipped revenue by ship date. Merchandising wants demand by order date. Both are filters on the same report, not two separate reports.
Order platform timestamps are converted from UTC to the business’s Eastern day boundary in SQL, so “Monday” means the same day in every source.
Growth comparisons load on click
Every comparison means building the whole report again for another date range. Instead of making the page wait for three extra reports, the controller only works out the comparison dates, and each growth panel loads its own data when opened.
The main page stays as fast as a single report, and people only wait for the comparisons they actually open.
Re-running a sync is always safe
Every job writes with INSERT … ON DUPLICATE KEY UPDATE using a natural key, and re-reads a rolling window of recent days.
Late refunds, revised ad spend and orders that shipped late get corrected automatically on the next run. A crashed run is fixed by running it again.
- Same dates last yearSep 1–7, 2026 → Sep 1–7, 2025
- Previous periodSep 1–7 → Aug 25–31
- Same dates last monthSep 1–7 → Aug 1–7
Outcome
From exporting spreadsheets to one saved link
- One source of truthMarketplace profit per product family per day, with sales, COGS, fees, shipping, ads and warehouse cost in one row.
- Ad spend tied to productsAd spend, reported by marketplace item ID, now lands on the product families it was spent promoting.
- Answers for each managerFilter by manager or parent SKU and compare against last year, the previous period or last month in one click.
- Catalog gaps show upSKUs without a family get their own row, so fixing the catalog is part of the normal routine.
Stack
Stack
- Java 17
- Spring Boot 2.7
- Spring JDBC
- Thymeleaf
- MySQL
- Node.js
- mysql2
- Luxon
- OAuth 2 + JWT
- REST APIs
The waterfall uses illustrative numbers, not real figures. Architecture, matching rules and API handling are described as built.