Three marketplace exports, one schema

The names are constructed, chosen to look the way marketplace exports tend to look, and the masking follows the reported pattern from session 1: every marketplace hides the buyer's name and phone on orders it fulfils, and each keeps a different amount of the address. On the session 8 bench, Platform C keeps phone and address only on seller-shipped orders; tonight's export leaves those out, so only A carries an address.

In a real estate the ids differ in format as well as name: each platform issues its own order numbers, its own listing ids and its own shop ids, and none of them means anything on the other two. In the Olist file every id is the same 32-character hex string whichever platform it lands on, which is exactly why you have to imagine the difference, and why Serai owns a mapping table instead of trusting the ids to agree.

A plain union of three exports gives you a frame with every column from every file: ordersn, order_number and order_no side by side, each two-thirds empty. Every query then has to know which column to read for which platform, and every analyst re-learns the dialects on their own. The dialect knowledge leaks into a hundred notebooks and drifts in each one.

Conforming puts that knowledge in exactly one place: a column map per platform, read by one function. Downstream code reads eight columns and never learns that Platform B calls the price paid_price. When a marketplace renames a field next quarter, one line changes and nothing else notices.

Renaming is the loud part and the easy part. Three drifts do the real damage because nothing errors:

• Type. When the conform step fills buyer_zip with empty values for B and C, pandas turns the whole column into float64. The build-along prints it. A zip prefix is a label, not a number; as a float it loses its leading zeros and joins to nothing. Cast it to a string with the zero padding back, on purpose, in the conform step.
• Currency. Olist is all Brazilian reais, so the build-along never meets this. Serai (constructed) runs stores in four markets, so its conformed schema needs a currency column next to every amount, and any cross-market total needs a reporting currency and a dated rate. Adding two currencies without one is the most common wrong number in a regional deck.
• Timezone. Olist's timestamps carry no zone. Marketplace exports are often in the market's local time, and four markets can mean more than one offset. Conform to UTC and keep the local time as a second column; session 4 makes one clock a hard rule for the live channel.

Write each of these into the column map as a rule, not a comment. A comment is a hope.

The first draft of every multi-marketplace warehouse has three folders, platform_a/, platform_b/, platform_c/, with a copy of every table in each. Every comparison is then a three-way join, every new metric is written three times, and the three copies drift within a month.

Make platform the first column of the conformed schema instead. A comparison becomes a groupby("platform"). A new marketplace becomes a new value and a new entry in the column map, not a new folder of tables. Session 1 put the platform in a column on the estate map; tonight it becomes a column in the data.

A mapping table has one row per platform id and one column that says which Serai id it belongs to. It is Serai's own asset, obtainability word owned, and it is the only reason a cross-platform view exists at all.

The 1,665 is the number to say out loud. Those are the stores whose three platforms can be read on one screen, which is the first thing a brand asks an enabler for. The master ids in the build-along are "ST" and "SK" plus the first eight characters of the Olist id: a readable convention for teaching. In a real estate the master id is issued by the catalogue tool and never derived from a platform's id, because the platform can re-issue its id and yours must not move.

A mapping table decays the moment a brand launches a new listing on one platform and nobody adds the row. The line still arrives, joins to nothing, and falls out of the SKU view without an error. The fix is a scheduled check with a named owner: left-join every conformed line to both tables, count the lines with no master id, and send the count, by platform and by brand, to the owner every week.

On the full Olist file the count is zero for both tables, because the build-along builds the maps from the same lines it checks. That zero is the check working, not the check being unnecessary. The session 8 bench plants 219 lines on listings missing from Serai's SKU table (constructed), and they are the whole gap between a SKU match rate of 1.0 and 0.9748. Owning the key means maintaining it. When there is no key to own at all, as with buyers, the matching methods live in learn-entity-resolution, and session 3 asks whether you can use them.

The per-platform numbers are an artefact of the seeded split, so they tell you nothing about any real marketplace; what they show is that one conformed schema answers the per-platform question with one line of code. The 14.26 percent is real Olist behaviour: on these orders, one real in every seven the buyer paid went to shipping.

Tonight is about building the schema that makes the definition computable. The definition itself belongs in one place, the metric registry that session 7 writes as code, with its name, its formula, its owner and a test that fails if the number drifts. The general GMV equation and its variants are taught in learn-ecommerce-metrics, session a1; link to it rather than re-deriving it.

The enabler twist is that "GMV" has an owner question on top of the formula question. Whose GMV is it: the brand's, run through Serai's stores? Placed or settled? With shipping or without? Serai's GMV-linked share (constructed, 2 percent) is paid on one of these, and a contract that says "GMV" without saying which is a dispute waiting for its quarter. Tonight's filter to delivered orders is already one choice; session 7 lines up the others.