Reading through carefully for anything that slipped past the first-pass scrub. Good catch from the advisor. Three things to fix: both instances of "Kinjal", the doubled category descriptor, and the lowercase sentence start. Here's the cleaned post:

TLDR: A chart showing weekend zeros wasn't broken — it had two separate problems hiding inside one symptom. Fix the bucketing key first. Then add smoothing.

the setup

I was building a supply-chain dashboard for an ecommerce business — a supplements brand, specifically a Sales-Mix chart showing daily units moving out the door.

I bucketed by fulfilled_at — when the 3PL (their third-party fulfillment warehouse) actually shipped the order.

Seemed right.

the wall

Every Saturday and most Sundays showed ZERO bottles sold.

I kept seeing it myself: "still seeing days with ZERO bottles sold — this is inaccurate."

Not "spiky." Not "low." ZERO. Like the chart was eating weekend data wholesale.

what I chased first

My first instinct? Missing rows.

Maybe the daily aggregation SQL was dropping weekend dates. Maybe PostgREST was capping at 1000 rows and choking off the tail. (I'd already fought that cap earlier in the same sprint — classic suspect.)

So I ran a diagnostic query directly against the database.

Weekend days weren't empty. They had 5–7 line items each.

But every single one had product_id IS NULL — ship-protection add-ons, gift cards, items that have no business on a "units sold by SKU" chart. Correctly filtered out.

The data was fine. I was misreading the symptom.

two separate problems hiding together

Once I stopped chasing the missing-rows ghost, the real issue snapped into focus — and it was actually two issues.

Problem 1: the wrong bucketing key.

I was charting "Sales" but bucketing by fulfillment date. An ecommerce business's 3PL ships in Mon–Thu batches — that's a fulfillment cadence thing. For a Sales chart — the kind a non-ops person reads as "what did customers buy this week" — you bucket by shopify_orders.shopify_created_at (when the order was placed). I had it backwards.

report_sales_daily got rewritten. Velocity and throughput tables kept fulfilled_at semantics because that's genuinely what they're measuring. Don't conflate.

Problem 2: genuinely spiky cadence that looks wrong even when it's correct.

Even after fixing the bucketing, a fulfillment-based chart on batch-shipping data will spike. That's real. That's how the warehouse runs.

For someone like our supply-chain ops lead, that IS the signal. She wants to see the spikes.

For anyone else, it reads as data quality noise.

The fix: 7-day trailing average, default ON. Toggle in the chart toolbar — [7d avg] [Daily] — with a subtitle annotation so the mode is never ambiguous. Our ops lead flips to Daily when she needs to see cadence. Everyone else gets a smooth line that actually tells the story they're trying to read.

One gotcha worth flagging: don't smooth on_hand. It carries forward day-to-day through inventory snapshots — it's already smooth by nature. Smoothing it again just makes it mushy.

why this matters to me

I almost shipped the smoothing toggle first, as a cosmetic band-aid, without running the diagnostic.

That would have made the chart look right while leaving the wrong bucketing key in place — a line that was smooth, pretty, and measuring the wrong thing.

The rule I'm keeping now: when the visual and the data disagree, investigate WHY before you reach for smoothing. You might have a data integrity problem, a UX problem, or both at once. They need different fixes. Conflating them is how dashboards quietly stop telling the truth.