📖 Dashboard Logic & Rules

1Data sources & precedence

Sales actuals can come from the DSR sheet or SQL (the “Actuals” toggle), but targets always come from the DSR sheet.

2Walk-ins — strict audit-only server.js:525 getAuditedWalkins()

Walk-ins are read only from the AI-camera audit sheet (tabs named <Month>-<Year>, e.g. August-2026). We never fall back to the DSR walk-in column — a missing audit tab means “not captured yet”, not “use the old number”.

2a · Camera-outage gap-fill server.js:590

A store's camera occasionally drops its feed (internet / device outage), producing a day with bills > 0 but walk-ins = 0 — which would make conversion% infinite (bills ÷ 0).

• For each store, compute monthAvgWi = average walk-ins across that store's days that had walk-ins > 0.
• Any day with bills > 0 && walk-ins == 0 is filled with monthAvgWi and flagged gap_filled.
• A day with bills == 0 && walk-ins == 0 is a genuinely closed day — left untouched.
• gap_filled_days / gap_filled_walkins are surfaced in audit_meta so the UI can badge it.

2b · T-2 cutoff (current month only) server.js:495 tMinusTwoCutoff()

Audit data lags reality by ~2 days. For the current month we only count days up to today − 2; later days are ignored (not yet audited). Past months have no cutoff.

2c · When a day is still zero after gap-fill server.js:619

Only happens if the store had no positive-walk-in day all month (camera down the whole month). We don't invent a number — we count it:

• current month → awaiting_fill_days
• closed month → unfilled_post_close_days

2d · Source states server.js:826 / :901

audit_meta.source tells the UI exactly what it's looking at:

3Conversion % server.js:857

conversion% = (bills on walk-in days) ÷ (walk-ins on walk-in days) × 100

Days with zero walk-ins are excluded from both numerator and denominator, so a cam-down day can't distort the ratio. Network conversion is recomputed the same way from the audit totals server.js:939, matching how the audit team computes it.

4Store cohort server.js:734 resolveStoreCohort()

Cohort is derived from STORE MAPPING col K, by substring match (tolerates the emoji prefix 🔄 LFL / 📆 LY / 🆕 NEW):

Blank cells become Unknown (red badge) — never silently LFL. Resolution order: exact name → normalized name → Unknown. Routing every endpoint through this one helper is what stopped cohort counts drifting between pages (was 97 vs 100).

5Store-name normalization server.js:473 normStoreName()

Every cross-sheet join runs names through this so the same store matches everywhere:

• uppercase + trim
• strip trailing store-code suffix incl. inner spaces — "X (ERDPRD )" → "X"
• unicode dashes (– —) → -
• canonical hyphen spacing — "X-Y", "X- Y", "X -Y" → "X - Y"
• collapse repeated whitespace

Store codes have their own normalizer (normStoreCode, server.js:489): strip all whitespace + unify dashes, so KFJ-127 / KFJ - 127 / KFJ – 127 collapse to one key.

6KPI value formatting bh/js/common.js

fmtKpiMain(n, kind) — every KPI-tile main value common.js:429. Wraps ₹ and the unit (Cr/L/%) in muted spans so the number is visually dominant.

fCompact(n) common.js:412 — compact ₹ for dense tables/mobile: ≥1Cr → Cr, ≥1L → L, ≥1K → K, else grouped. Zero/empty → —.

ABV = net_sales ÷ total_bills server.js:933.

7Color bands — canonical thresholds bh/js/common.js

Every cell that colors by a number uses these — no hardcoded ternaries at call sites.

achBand(pct) — achievement vs target common.js:453

deltaBand(pp) — pp deltas, e.g. conversion TY-vs-LY common.js:466: ≥3 strong green, >0 green, 0 neutral, ≥−3 amber, <−3 red.

growthBand(pct) — YoY growth common.js:477: ≥10 strong green, >0 green, ≥−10 amber/red, <−10 red. (10% is the “big deal” retail benchmark.)

8Day pro-rating bh/js/common.js:489

daysInfo carries {done, total} for the selected month.

• dayProrate(target, di) = target × done ÷ total — MTD-expected portion (what target should be met by today). Used to judge pace mid-month.
• dayTarget(target, di) = target ÷ total — flat per-day target.

Missing di → 0 (callers don't crash mid-render).

9Sheet-read conventions server.js

• MW - <MONTH> tabs: row 1 title, row 3 network totals, row 6+ store rows.
• MW ranges auto-widen to A6:AD500 inside fetchRange() so every caller shares one cache entry — read wide, ignore extra columns.
• Caches: month/sheets short TTL, store mapping 30 min, walk-ins own TTL, SQL 5 min.
• SQL freshness: /api/sql/overview returns data_through = MAX(bill_date), shown as a chip.

10Data-hygiene flags — surfaced, not silently fixed

These are sheet problems the app deliberately makes visible instead of papering over:

• Blank cohort in col K → Unknown red pill (fix = fill col K in STORE MAPPING).
• MUM - NEXUS SEAWOOD MALL typo in the walk-in sheet (should be SEAWOODS) → ⚠ UNMAPPED in Conversion until the sheet is corrected.
• Store in DSR but not in audit tab → unmatched, walk-ins 0.

11Banned patterns — why numbers stay trustworthy

• No silent fallbacks. Missing cohort/walk-in data surfaces (Unknown / awaiting / pending), never defaults to LFL or a stale DSR value.
• Never mapping[name]?.cohort || 'LFL' — always resolveStoreCohort().
• Never a local fmtCr / achShade / hardcoded >= 70 — use the canonical helpers above.
• Walk-ins never fall back to the DSR column — strict audit-only.