how-to-metric-to-scorecard

Step 1

Input - the clean marts, and the one field that decides everything

This skill reads the marts - the clean tables that are the output of how-to-schema-and-warehouse. The modeling is done; the analytics judgement starts here. The field that quietly decides half the metrics is orders.status: only delivered orders are real revenue.

orders 50,600 rows

order_id · one row per order
merchant_id · fk
customer_id · fk
order_ts · timestamp
status · delivered / cancelled / shipped / paid

order_items 109,154 rows

order_id · fk
product_id · fk
qty · units
unit_price · per unit
discount · fraction off

returns 2,672 rows

return_id · one per return
order_id · fk
reason · category
return_ts · timestamp

merchants 400 rows

merchant_id · pk
category · 8 canonical
tier · standard/premium/enterprise
M0007 = bulk wholesaler

customers 20,000 rows

customer_id · pk
signup_date · acquisition
channel_id · fk
region · SEA market

Step 2

Sample data - reuse the seeded marts, and the traps inside them

No new generator here: the marts already exist (seed 42, from the warehouse build). What matters for a scorecard is knowing the traps a naive metric walks straight into - each one is planted in the data on purpose and shows up in the validation at Step 5.

🚦 Mixed order status21% of orders are cancelled or in-flight - counting them inflates GMV 26%

🐘 The wholesale outlierM0007 orders ~50x the median - it drags the platform AOV up 17%

➗ Wrong denominatoronly delivered orders can be returned - all-orders base understates return rate

📈 November seasonalitythe demand peak hits 1.9x a normal month - a single average hides it

🎯 GMV concentrationone merchant is 15% of GMV - an aggregate would bury the dependency

🔁 Loyalty vs churnrepeat rate on all orders counts cancelled orders as loyalty

Step 3

Objective - a scorecard the board can defend, not a dashboard

A dashboard shows numbers. A scorecard defends them. The objective is not "visualize the data" - it is to choose the few numbers a board decides on, and be able to say exactly what each one means and why it is right.

What are the eight numbers Everrest's board needs this quarter - and can we defend every one against the version that is subtly wrong?

What counts as revenue - and how much do cancelled and in-flight orders inflate it?
What is a typical order worth once the one wholesaler is set aside?
Is the marketplace healthy - active buyers, repeat rate - or just large?
Where is the risk the board should see: concentration, returns, seasonality?
Can every number be traced to a formula and reproduced, so none is hand-typed?

Step 4

Find-skills - the reporting toolbox

A scorecard needs a compute layer, a metric-contract discipline, and one hardened, dependency-free HTML artifact an executive can open anywhere. Assemble those before writing a single tile.

OSS tool

pandas

Every metric is a small, explicit function over the marts - the definition lives in code, not in a BI tool's config nobody can read.

OSS tool

Great Expectations

Promote each metric's validation (status filter, denominator, no-negative checks) to a permanent gate so a definition never silently drifts.

Skill

deploy-html

Inline and harden the scorecard into one self-contained file - no CDN, no build - so it opens in any browser and drops into a board deck.

Skill

dataviz discipline

Tabular-nums heroes, one amber flag for risk, RAG status, period-over-period deltas - the conventions a board reads without a legend.

Pattern

Metric contract

Formula + grain + filter + owner for every metric. If you cannot state all four, it is not board-ready - it is an aggregate.

Skill

phoebe-data-skills EDA

The upstream EDA and warehouse skills surface the outlier and the status field this scorecard has to handle correctly.

Step 5

Build - define, validate, then generate

One python script defines all eight metrics as functions, computes each metric AND its naive twin, writes every value to metrics.json, and injects those values into a self-contained HTML scorecard. Grab the real code below.

Get the code: ▶ build_scorecard.py ✓ metrics.json ◆ Open the scorecard ↗ All files

Define every metric explicitly

Formula, grain, filter - stated up front so a reader can audit each tile. This table ships on the scorecard itself.

Metric	Formula	Grain	Key filter / rule
GMV (Delivered)	`SUM(qty·unit_price·(1-discount))`	order	status = delivered, gross of returns
Platform Net Revenue	`GMV × 11%`	order	the take · rate = assumption
Delivered Orders	`COUNT(orders)`	order	status = delivered
Avg Order Value	`MEAN(net_amount)` + median	order	exclude M0007 wholesaler
Active Buyers	`COUNT(DISTINCT customer_id)`	customer	≥ 1 delivered order this qtr
Return Rate	`returns / delivered orders`	order	denominator = delivered only
Repeat Buyer Rate	`buyers with ≥2 orders / buyers`	customer	delivered, within quarter
Top-Merchant Share	`max merchant GMV / total GMV`	merchant	concentration-risk flag

Validate - what each metric says if you get it wrong

The step every tutorial skips. Compute the naive version too, and show the gap. These are the numbers that separate a defensible board metric from a plausible-but-wrong one.

Bar chart: counting all order statuses reports 1.89M revenue versus 1.50M for delivered-only, a 26 percent inflation

Histogram of delivered order values with the mean including M0007 at 151 dollars versus 129 dollars excluding it

Left: counting cancelled and in-flight orders inflates revenue by 26% ($1.89M vs the true $1.50M). Right: one bulk wholesaler (M0007, ~$7,264/order) lifts platform AOV from $129 to $151 - the board metric excludes it and quotes the median beside the mean.

The scorecard - every number computed, live below

Generated by the script from metrics.json - not one figure is hand-typed. Self-contained HTML: no CDN, no build, drops into a board deck. This is the real artifact, embedded live.

On track Watch / risk flag ▲▼ period-over-period vs prior quarter

Everrest executive scorecard · current period 2026Q2 vs 2026Q1 · seed 42Open full scorecard ↗

The three charts behind the tiles

Line chart of delivered revenue over twelve months with November flagged amber as the demand peak

Plan to the peak. November hits 1.9x a normal month. A single quarterly average would hide the seasonality the inventory plan depends on.

Horizontal bar chart of delivered revenue by the eight clean categories, Grocery highest

Grocery leads. Revenue by the 8 canonical categories - clean because the warehouse normalized 14 dirty labels upstream, so the rollup is whole.

Bar chart of the top fifteen merchants by revenue with M0007 towering in amber

One merchant, 15%. M0007 alone is 15% of quarterly revenue and dwarfs the field - a concentration risk the scorecard surfaces, not an average that buries it.

Trap in the data	Naive metric	Validated metric	Status
Cancelled / in-flight orders	$1,894,948	$1,498,412 (delivered GMV)	✓ 26% inflation removed
M0007 wholesale outlier	AOV $151	$129 mean / $82 median	✓ mean + median shown
Wrong return denominator	5.0% (all orders)	6.3% (delivered)	✓ honest base
November seasonality	flat average	1.9x peak flagged	✓ trend on the card
Revenue concentration	hidden in total	top-merchant 15% tile	✓ risk surfaced
Hand-typed numbers	the usual board deck	0 - all from metrics.json	✓ reproducible

Step 6

Expert review - a panel that changed the metric set

A panel of senior reviewer agents - each 10+ years in analytics engineering, board-facing insight, and commercial strategy - tore into the first draft of the scorecard and its definitions. Every fix below is in the code and the live card above.

Commercial / Board Lead

14 yrs · marketplace P&L

"Your top tile says 'Net Revenue $1.5M'. That is GMV - merchandise flowing through the platform. The board will book it as company revenue and it is not. Everrest earns the take, ~$165k. This is the dangerous kind of wrong: plausible and off by 9x."

Fix applied: tile 1 relabeled GMV (Delivered); tile 2 promoted to Platform Net Revenue (the take) - two distinct signals, not the same number twice.

BI / Analytics-Engineering Lead

13 yrs · semantic layers

"'Active Merchants' is 400 every quarter, flat, green - because all 400 merchants transact every period. It is COUNT(*) of the merchant table wearing a KPI badge, and its validation text claims a safeguard the data contradicts."

Fix applied: dropped it for Active Buyers (7,829, +6.5% QoQ) - the demand side that actually moves and that a two-sided board asks about.

Analytics Methodology Lead

12 yrs · metric integrity

"The AOV validation promises a median 'reported alongside' that the code never computes. And 5.7% ($85k) of your delivered GMV sits on orders that were later returned, while the tile next to it reports a 6.3% return rate. Internally inconsistent."

Fix applied: median ($82) now computed and shown beside the mean; GMV definition states it is gross of returns and quantifies the returned share, so the two tiles tell one story.

Data Governance / Metric-Contract Lead

11 yrs · trust & lineage

"The headline hard-types 'after the March promo'. There is no promo field anywhere in the marts - that is an unverifiable narrative on a deliverable that advertises 'zero hand-typed numbers'. And 'held' is spin on a metric that fell 2.4%."

Fix applied: headline rewritten to sourced, sign-honest language - "down 2.4% QoQ against a seasonally stronger quarter" - no invented causes.

Retention / Growth Analyst

10 yrs · cohorts

"Repeat Buyer Rate ships as a static 67% with no prior period - decoration. A board wants retention moving. And with only 12 months of data, every QoQ delta here is measured against a seasonal high; there is no year-over-year comparator at all."

Fix applied: repeat rate reframed to within-quarter (22.6%, +2.1 pts QoQ) so it has a trajectory; YoY flagged honestly in metrics.json as the next data requirement, not faked.

before → after · the relabel that changed what the board reads

# v1 (before): one number, mislabeled - the board books $1.5M as revenue
net_amount = qty * unit_price * (1 - discount)      # merchandise value
tile("Net Revenue", net_amount.sum())            # -> $1,498,412  (this is GMV!)

# v2 (after): merchandise flow and the take are two different tiles
gmv = net_amount.sum()                          # -> $1,498,412  GMV (Delivered)
tile("GMV (Delivered)", gmv)                     # what flows through the platform
tile("Platform Net Revenue", gmv * TAKE_RATE)   # -> $164,825  what Everrest earns

Input - the clean marts, and the one field that decides everything

Sample data - reuse the seeded marts, and the traps inside them

Objective - a scorecard the board can defend, not a dashboard

Find-skills - the reporting toolbox

pandas

Great Expectations

deploy-html

dataviz discipline

Metric contract

phoebe-data-skills EDA

Build - define, validate, then generate

Define every metric explicitly

Validate - what each metric says if you get it wrong

The scorecard - every number computed, live below

The three charts behind the tiles

Expert review - a panel that changed the metric set

Run this skill on your own marts