# Admin Analytics Dashboard Playbook **Portable spec for the admin-facing analytics *dashboard* — the visualization + reporting layer that sits on top of the first-party `page_events` pipeline. The funnel cards, channel breakdown, conversion tables, live Active-Now globe, per-page drill-down, and weekly reports.** This is the **read/visualize half**. The **ingest half** (schema, `/api/track`, visitor IDs, attribution, RPC) lives in [CUSTOMER-ANALYTICS-PLAYBOOK](CUSTOMER-ANALYTICS-PLAYBOOK.md) — build that first, this consumes its tables. CUSTOMER-ANALYTICS §10 describes these views in four prose paragraphs; **this playbook is that §10 fully expanded** into the real 3,500-LOC surface. Built from the BKE / Kompozy implementation. Reference repo: `reference app`. --- ## 0. Use this spec **Prerequisites (from CUSTOMER-ANALYTICS-PLAYBOOK):** - `page_events` + `page_event_visitors` tables populated by `/api/track` - `referrer_source` / `first_referrer_source` written on ingest (the channel classifier reads these) - A `profiles.role = 'admin'` gate, or whatever admin model the app uses - A billing/subscriptions table to join `visitor → user_id → paid` (the commercial columns; lossy by design — webhook has no visitor cookie) **Inputs:** - App timezone anchor (BKE uses `America/New_York` — every range/bucket is ET-anchored, not UTC) - The app's self-referrer hosts (prod domain(s) + localhost) to strip from referrer tables - The funnel-step taxonomy for this app (the conversion funnel rows) - Self-origin domains for the channel classifier's `SELF_HOSTS` **Outputs:** - `/admin/dashboard/analytics` — the main dashboard (range selector, KPI tiles + sparklines, conversion funnel, channel breakdown + trend, UTM/referrer/campaign/SEO/AI/email conversion tables, CTA leaderboard, top-pages, Direct/Untagged drill-down, assisted conversions) - `/admin/dashboard/analytics/page-detail?path=…` — per-page drill-down - `/admin/dashboard/analytics/reports` — saved weekly LLM-generated narrative reports - `Active Now` modal + 3D globe — live last-5-min visitors plotted by geo - `/api/admin/analytics/active-now` + `/api/admin/analytics/generate-report` + `/api/admin/analytics/journey` API routes --- ## 1. Why a dedicated dashboard (vs piping `page_events` to Metabase / GA UI) - **One commercial join, everywhere.** Every conversion table (`utm`, `referrer`, `campaign`, `channel`, `top-pages`, `cta`) reuses the SAME `visitor → user_id → billing` set. "Which traffic source actually *pays*" is the headline number on every card — a generic BI tool makes you re-author that join per chart. - **First-touch attribution survives 6-month conversions.** The dashboard reads `page_event_visitors.first_*` (immutable since insert) so a paid signup in June still attributes to the January `utm_source`. GA's last-non-direct model loses this. - **The "where is ALL my traffic" invariant.** The channel breakdown maps EVERY active visitor to exactly one coarse channel, so rows sum to ~Unique Visitors and nothing is invisible — including referrer-stripped AI/organic that GA dumps into "Direct." This is bespoke logic; no BI tool ships it. - **Live ops surface.** The Active-Now globe + signup-page counter is a "who's on the site *right now*, are they on the pricing page" view you watch during a launch/livestream. A nightly-batch BI tool can't do it. - **Internal-only, service-role read.** No per-user RLS dance, no seat licensing, no PII leaving Supabase. The dashboard is `force-dynamic` + a 30s `unstable_cache` over one admin-wide aggregation. Trade-off accepted: every new view is hand-authored SQL+TSX, not drag-and-drop. The reuse below (shared range math, shared classifier, shared commercial-join helper) is what keeps that cheap. --- ## 2. Architecture ``` src/ ├── lib/analytics/ │ ├── queries.ts # THE aggregation engine — getAnalyticsSummary + getPageAnalyticsSummary │ │ # ET range/bucket math, every card's query, the commercial join │ ├── referrer-source.ts # classifyChannel + CHANNEL_LABELS + classifyReferrer/Utm + isAi/Search/Email/Partner │ ├── acquisition.ts # getAcquisitionUserSets — the visitor→user_id→billing set builder (the commercial join) │ ├── funnel.ts # funnelStepForPath + FunnelStep type + isAppInternalPath (shared with ingest) │ ├── iso-week.ts # ISO-week keys for cohort/report bucketing │ ├── report-generator.ts # generateWeeklyReport — LLM narrative over a summary snapshot │ └── platform-labels.ts # display labels for engines/platforms │ ├── app/admin/dashboard/analytics/ │ ├── page.tsx # Server: parse range, unstable_cache(getAnalyticsSummary, 30s), render view │ ├── AnalyticsView.tsx # THE dashboard client component (~1,550 LOC) — all cards │ ├── ActiveNowModal.tsx # Live last-5-min visitor list (polls active-now API) │ ├── ActiveNowGlobe.tsx # 3D globe plotting active visitors by lat/lng │ ├── MarketingChannelsModal.tsx # Channel-breakdown deep-dive modal │ ├── country-centroids.ts # country → {lat,lng} for the globe │ ├── region-centroids.ts # region → {lat,lng} fallback when no city geo │ ├── page-detail/ # per-page drill-down (page.tsx + PageDetailView.tsx) │ └── reports/ # saved weekly reports (page.tsx + ReportsView.tsx) │ └── app/api/admin/analytics/ ├── active-now/route.ts # GET live visitors (admin-gated, force-dynamic, service-role read) ├── generate-report/route.ts# POST trigger a weekly report generation └── journey/route.ts # GET a single visitor's full event timeline ``` **The load path:** `page.tsx` (server) parses `?range=&engaged=&from=&to=` → `unstable_cache(getAnalyticsSummary, 30s)` → passes one `AnalyticsSummary` object to `AnalyticsView` (client). The view is pure presentation over that object — no client-side fetching except the Active-Now modal (which polls its own API for freshness). --- ## 3. The ET-anchored range engine (copy this verbatim — it's the load-bearing piece) `page_events.created_at` is UTC `timestamptz`. The admin thinks in their wall-clock ("Today", "7d"). BKE anchors **everything** to `America/New_York` (auto EST↔EDT) so "Today" = ET-today, sparkline buckets are ET hours/days, and the custom date picker treats `2026-05-01` as "ET-May-1 00:00", not browser-locale-noon. Reference: `src/lib/analytics/queries.ts` lines ~55–230. The primitives (all pure, all DST-safe): - `etPartsOf(d)` — `Intl.DateTimeFormat` in the analytics TZ → `{y,m,day,h,min,s}` (handles ICU's hour=24 midnight quirk). - `etStartOfDay(ref)` — UTC instant of ET-midnight starting the ET day containing `ref`. **Refines across DST**: re-checks the offset at the guessed instant vs `ref`. - `addEtDays(etMidnight, n)` — add N ET calendar days, re-snapping to ET-midnight (DST-safe; never `+= n*86400000` naively for day boundaries). - `etOffsetMs(d)` — ET-wall-as-UTC minus actual-UTC (−4h EDT / −5h EST). - `etDayKey(d)` / `etHourKey(d)` — bucket keys for sparklines. - `parseEtDay(s)` / `parseEtDayEndExclusive(s)` — custom-picker `YYYY-MM-DD` → ET-midnight start / exclusive next-ET-midnight end. `RangeKey = 'today' | '7d' | '30d' | 'quarter' | 'year' | 'all' | 'custom'`. `resolveRange(key, customRange?)` returns `{ from, to, key, durationMs }`. `granularity` is `'hourly'` for `today`, `'daily'` otherwise — drives both the bucket key fn and the sparkline labels. **Prior-window deltas:** for every range except `all`, the engine also computes the immediately-preceding equal-length window (`totalsPrior`, `visitorsPrior`, `deltaPct`) so every KPI tile and channel row shows a ▲/▼ vs prior. `range='all'` has no prior → `deltaPct: null` → UI renders "—". > **Port note:** if the new app's operator lives in a different TZ, change `ANALYTICS_TZ` only. Everything downstream is relative to it. Don't switch to UTC "to keep it simple" — "Today" silently means "since 8pm yesterday" for a US operator and every daily number is wrong at the edges. --- ## 4. The commercial join (the number that matters) Every conversion table answers "of these visitors, how many **paid**?" via one shared set-builder, not per-table SQL. Reference: `src/lib/analytics/acquisition.ts` — `getAcquisitionUserSets(range)` returns `AcquisitionUserSets`: the sets of `user_id`s who, **in range**, created an account (`addToCart`), reached Stripe checkout (`reachedCheckout`), and subscribed (`paid`). Each conversion table maps its visitors → `user_id` (via `page_event_visitors.user_id`) → intersect with those sets. - **Lossy by design.** The Stripe webhook has no visitor cookie, so a payment only attributes if the paying user's row carries a `user_id` that some visitor row also carries. Documented in the `CtaRow` comment. Same lossiness across every table → they're mutually consistent even if absolute paid-counts undercount. - **Two reliability tiers.** `signups` on the channel breakdown is **event-based** (`signup_complete` fired in range) — the *reliable* mid-funnel number. The `addToCart/reachedCheckout/paid` columns are the *lossy* billing join. Show both; trust `signups` for funnel math, `paid` for directional "which source is worth more $." --- ## 5. `AnalyticsSummary` — the one object the whole dashboard renders Reference: `src/lib/analytics/queries.ts` lines ~330–560. `getAnalyticsSummary(range, { engagedOnly, customRange })` builds it in one pass. Fields: | Field | Card it powers | |---|---| | `totals` + `totalsPrior` (`AnalyticsTotals`) | KPI tiles: views, uniqueVisitors, optIns, signups, optInRate, new/returning, bounceRate — each with ▲/▼ vs prior | | `sparklines` + `sparklineLabels` (`SparklineSet`) | Inline trend sparkline under each KPI tile (ET hourly/daily) | | `activeVisitorsNow` | The "Active Now" pill that opens the globe modal | | `ctaLeaderboard` (`CtaRow[]`) | CTA leaderboard — clicks + unique clickers + their commercial conversion | | `topPages[]` | Top-10 landing pages — views, optInRate, paidRate, scroll-depth (25/50/75/100) | | `coldEmailFunnel` / `dmFunnel` / `smsFunnel` / `organicFunnel` | Per-channel step funnels (views→optIns→next-step %) | | `utmConversion[]` / `referrerConversion[]` / `campaignConversion[]` | UTM-source / referrer-host / campaign → paid tables | | `channelBreakdown[]` (one `Channel` per visitor) | **The unified channel card** — every visitor in exactly one bucket, rows sum to ~Unique Visitors; signups + new/returning + avgScroll + commercial cols + prior-window delta | | `channelTrend[]` | Stacked per-ET-day channel-trend chart | | `directBreakdown` | Direct/Untagged drill-down — `untaggedSuspects` = visitors who hit a CONVERSION page with no referrer/UTM = the "go tag these links" number; `byCategory` + `topPaths` | | `assistedConversions` | Multi-touch: for in-range signups, credits EVERY channel that touched them; `assisted >> firstTouch` flags an under-valued assister (email, partnerships) | | `sourceConversion` (`ai` / `seo` / `paidSearch` / `email`) | Engine-split SEO/GEO/Paid/Email tables (per-engine rows; routes each visitor to ONE channel by priority) | `PageAnalyticsSummary` (line ~981) is the analogous object for `getPageAnalyticsSummary(path, range)` powering the per-page drill-down. The view (`AnalyticsView.tsx`) is ~1,550 LOC of pure rendering over this object: range selector + custom date picker + engaged-only toggle at top, then the cards in priority order. Shared inline style constants (`SECTION_LABEL_STYLE`, `TILE_LABEL_STYLE`, `HINT_STYLE`) + `fmtNum`/`fmtPct`/`pctDelta` helpers — lift these as-is. --- ## 6. The channel classifier (the secret sauce — port it faithfully) Reference: `src/lib/analytics/referrer-source.ts`. `classifyChannel(input) → Channel` maps one visitor's first-touch signals to exactly one coarse bucket. **Order is load-bearing** (first match wins): ``` unknown → no visitor row at all ai → referrer OR utm is an AI surface (ChatGPT/Claude/Perplexity/Gemini/Copilot/…) paid_search → utm_medium is paid AND (utm OR referrer) is a search engine [must beat organic] partner → partner utm_medium OR a known partner host [BEFORE email, so a partner newsletter ≠ our cold email] email → email/broadcast/newsletter utm sms → sms utm social → social referrer/utm referral → some other real referrer host ai_organic_stripped → conversion-page landing, no referrer, no utm (likely AI/organic that stripped the referrer) direct → everything else with a visitor row ``` `CHANNEL_LABELS` is the display map. `REFERRER_SOURCES` is the fine-grained bucket taxonomy (`ai_chatgpt`, `search_google`, `social_x`, …) — **kept in TS, NOT a DB CHECK constraint** (new AI providers launch monthly; coupling to a migration is the documented `funnel_step` footgun). The 00122-style migration documents current values in a column comment only. Falls back to `classifyReferrer(host)` for pre-cutover rows whose `first_referrer_source` was never set. > **Why this beats GA:** AI assistants and email strip the referrer, so GA dumps them into "Direct." `ai_organic_stripped` + the email/AI engine-split tables *recover* that traffic. On a content/GEO-driven app this is 20–40% of real traffic that's otherwise invisible. --- ## 7. Self-referrer + KPI-exclusion hygiene (don't skip — it silently corrupts every table) Reference: `src/lib/analytics/queries.ts` lines ~31–53. - **`isSelfReferrerHost(host)`** — drop localhost (dev testing) AND prod self-origins from referrer aggregations. Every internal `/a → /b` nav carries a same-origin `Referer`; without this the Referrer Conversion table is dominated by a self-referral artifact. **Set the new app's prod domain(s) in `SELF_REFERRER_HOSTS` on day one.** - **`isCountedOptInPath(path)`** — exclude specific paths from a KPI even though their events stay in `page_events` for audit (BKE excludes `/enterprise/contact` opt-ins from "Signup Form Submits"). Use exact-match-or-slash-boundary so a future sub-path isn't silently swallowed. These are app-specific allow/deny lists. Audit them whenever you add a funnel surface. --- ## 8. Active Now — live globe References: `ActiveNowModal.tsx`, `ActiveNowGlobe.tsx`, `api/admin/analytics/active-now/route.ts`. - `GET /api/admin/analytics/active-now` returns visitors with a `page_view` in the last 5 min — one row per `visitor_id` (latest event) with geo + device + referrer + path + `signedUp/optedIn`, plus `onSignupPage`, `signupsLast5Min`, `countries[]`. - The modal polls this on an interval (`cache: 'no-store'`). The globe plots each visitor by lat/lng; missing city geo falls back to `region-centroids.ts` then `country-centroids.ts`. - This is the only client-fetched surface — everything else rides the server `AnalyticsSummary` snapshot. --- ## 9. Weekly reports (optional — the LLM narrative layer) References: `report-generator.ts` (`generateWeeklyReport`), `reports/ReportsView.tsx`, `api/admin/analytics/generate-report/route.ts`. Takes an `AnalyticsSummary` snapshot for an ISO-week, feeds it to an LLM, stores a markdown narrative (`week_iso`, `range_key`, `provider_used`, `model_used`, `triggered_by`, optional `email_message_id`). Persisted to a `reports` table; `ReportsView` lists + renders them. Wire generation to a Trigger.dev cron if you want it auto-emailed weekly. Skip this whole section for a v1 dashboard. --- ## 10. Auth model Every surface is admin-gated: - **Pages** (`page.tsx`): `force-dynamic` + `revalidate = 0`; aggregation reads the **service-role** admin client (admin-wide data, no per-user cookies) so the 30s `unstable_cache` is correct for every admin. - **API routes** (`active-now`, etc.): `createServerSupabase()` → `auth.getUser()` → look up `profiles.role`; `401` if unauthenticated, `403` if `role !== 'admin'`. Then read via `createAdminSupabase()` (service-role). Match whatever admin model the app already has (BKE uses `profiles.role = 'admin'`; the ADMIN-CONSOLE-PLAYBOOK's JWT-app_metadata model is the more hardened variant — use that if the app adopted it). --- ## 11. Performance discipline - **One aggregation, cached 30s.** `getAnalyticsSummary` is the only expensive call; `unstable_cache(['admin-analytics-summary-v1'], { revalidate: 30 })` keyed by `range + engaged + custom window`. Reloading or toggling back to a range is instant. Numbers lag ≤30s — fine for an internal dashboard. - **`page_event_visitors` is the small table.** ~1 row per human. Filter/group here first; JOIN to `page_events` (high-volume) only for views/scroll/timeline. Never `GROUP BY` the 10M-row events table live on a dashboard render. - **PostgREST 1000-row cap.** `supabase-js` silently caps every query at 1000 rows. Any >1000-row fetch (a busy range's raw events) MUST paginate with `.range()` — this bit BKE's reconciler. For big ranges, prefer a nightly materialized rollup over live raw-event scans (BKE has a "rollup" code path that produces an *identical* shape to the raw path so the two are interchangeable). - **Shared builders for raw vs rollup.** `channelTrend` / `channelBreakdown` are built from a shared helper so the live-raw and rollup paths produce byte-identical series. If you add a rollup, factor the series builder so both call it — divergence here is silent and miserable to debug. --- ## 12. Common pitfalls **❌ Don't anchor ranges to UTC.** "Today" for a US operator becomes "since 8pm yesterday." Use the ET (or app-TZ) helpers in §3 for every boundary, bucket key, and label. **❌ Don't forget `SELF_REFERRER_HOSTS`.** Ship the new app's prod domain in that set or every internal nav pollutes the Referrer table as a self-referral and it dominates the chart. **❌ Don't put `referrer_source` / channel taxonomy in a DB CHECK constraint.** New AI providers launch monthly; keep it in TS (`REFERRER_SOURCES`), document values in a column comment only. Same lesson as the `page_events.funnel_step` constraint footgun. **❌ Don't reorder the channel classifier.** `partner` must precede `email` (a partner newsletter that auto-tags `utm_medium=email` is an earned referral, not our outbound). `paid_search` must precede organic (a paid Google click also carries a `google.com` referrer). The order IS the spec. **❌ Don't trust the lossy billing join for funnel math.** Use event-based `signups` (`signup_complete` in range) for mid-funnel; the `paid` column is directional only (webhook has no visitor cookie). **❌ Don't let the channel rows not sum to Unique Visitors.** The whole point is "nothing invisible." Every visitor lands in exactly one bucket including `direct`/`unknown`/`ai_organic_stripped`. If your rows don't reconcile to ~Unique Visitors, a visitor is being dropped or double-counted. **❌ Don't client-fetch the main dashboard.** It rides one server `AnalyticsSummary` snapshot. Only Active-Now polls. Adding per-card client fetches reintroduces the N+1 + cache-coherence problem the single-pass aggregation was built to avoid. **❌ Don't skip the prior-window for `range='all'`.** It has no prior → `deltaPct: null` → render "—", never a divide-by-zero `Infinity%` or `NaN`. --- ## 13. Build order 1. Confirm CUSTOMER-ANALYTICS ingest is live and `page_event_visitors.first_referrer_source` is populating. 2. Port `queries.ts` ET range engine (§3) + `AnalyticsTotals`/`SparklineSet` + the KPI-tiles-only `getAnalyticsSummary`. Ship a dashboard with just tiles + sparklines + range selector. Verify ET boundaries against known traffic. 3. Add `acquisition.ts` commercial join (§4) + the conversion tables (utm/referrer/campaign/top-pages/cta). 4. Port `referrer-source.ts` classifier (§6) + `channelBreakdown` + `channelTrend` + the Marketing Channels modal. This is the highest-value card — budget the most time here. 5. Add `directBreakdown` + `assistedConversions` + `sourceConversion` engine splits. 6. Add Active-Now API + modal + globe (§8). 7. (Optional) per-page drill-down + weekly reports (§9). Pause after step 2 for review — the ET engine is the part most likely to need app-specific tuning. --- ## 14. Kickoff prompt > Execute the Admin Analytics Dashboard Playbook at `ADMIN-ANALYTICS-DASHBOARD-PLAYBOOK.md` for this app. The CUSTOMER-ANALYTICS ingest layer is already live (page_events + page_event_visitors populating). Mirror the BKE implementation — when in doubt, read the BKE file at `reference app` rather than re-deriving. > > Inputs: > - App timezone anchor: [e.g. America/New_York] > - Self-referrer hosts to strip: [prod domain(s) + localhost] > - Billing/subscriptions table for the commercial join: [table + the paid-status column] > - Admin gate: [profiles.role='admin' | JWT app_metadata] > > Follow §13 build order. Pause after the ET range engine + KPI tiles ship (step 2) for review. --- **Reference files in BKE:** - `src/lib/analytics/queries.ts` — the aggregation engine, ET math, every card's query (3,620 LOC) - `src/lib/analytics/referrer-source.ts` — channel classifier + taxonomy - `src/lib/analytics/acquisition.ts` — visitor→user_id→billing commercial join - `src/app/admin/dashboard/analytics/page.tsx` — server load + 30s cache - `src/app/admin/dashboard/analytics/AnalyticsView.tsx` — the dashboard (1,551 LOC, all cards) - `src/app/admin/dashboard/analytics/ActiveNowModal.tsx` + `ActiveNowGlobe.tsx` — live globe - `src/app/admin/dashboard/analytics/MarketingChannelsModal.tsx` — channel deep-dive - `src/app/admin/dashboard/analytics/page-detail/PageDetailView.tsx` — per-page drill-down - `src/app/api/admin/analytics/active-now/route.ts` — live-visitors API (admin-gated) **Pairs with:** [CUSTOMER-ANALYTICS-PLAYBOOK](CUSTOMER-ANALYTICS-PLAYBOOK.md) (ingest — build first), [ADMIN-CONSOLE-PLAYBOOK](../s-tier/ADMIN-CONSOLE-PLAYBOOK.md) (the console shell this dashboard lives inside).