<!--
Customer Analytics Playbook

Part of the free Claude Code template library from BILT AI — https://biltai.io/claude-templates

These are the actual playbooks used to build and ship production SaaS apps with
Claude Code. They're written to be handed to an agent as-is: point Claude at one
and it has the full pattern, the schema, the gotchas, and the "don't do this"
list. Paths, project refs, and org names have been replaced with placeholders
(<your-app>, <supabase-project-ref>, <your-github-org>) — search for "<" and
fill in your own before running.

Free to use, modify, and ship. No attribution required.
-->

# Customer Analytics Playbook

**Portable spec for first-party analytics in a new SaaS app — no third-party trackers, no GDPR consent gate, no ad-blocker miss, you own the data.**

Built from the BKE / Kompozy implementation. Reference repo: `reference app`.

---

## 0. Use this spec

**Inputs:**
- Supabase project (uses the existing `service_role` key — write path is service-role only)
- App's primary landing-page surface (where you want the live HUD pills)
- Event taxonomy: 5–10 canonical event names per app (see §8)
- Whether the app charges money (churn-risk signal needs `subscriptions` table to be meaningful)

**Outputs:**
- `page_events` + `page_event_visitors` tables with atomic upsert RPC
- `/api/track` POST ingest (rate-limited, bot-filtered, sanitized)
- `/api/metrics/live` GET public counter feed for landing-page HUD (10s cache, monotonic)
- Visitor-id rotation client (cookie + sessionStorage)
- UTM capture (first-touch, immutable)
- Signup attribution (visitor → user_id linkage without breaking visitor stability)
- Admin analytics dashboard (funnel, cohort, per-route, per-UTM)
- Churn-risk signal (orange at 7d zero-usage, red at 14d)

---

## 1. Why first-party (vs GA / Mixpanel / Segment)

- **No consent banner.** First-party cookies on your own domain, no cross-site tracking, no PII in events → GDPR/CCPA lawful-basis is "legitimate interest" for product analytics. Banner becomes optional, not legally required.
- **No ad-blocker miss.** uBlock / Brave / Safari ITP kill ~30% of GA hits. Your `/api/track` endpoint on your own origin is invisible to blocklists.
- **You own the data.** Raw events live in your Supabase. No vendor lock-in, no schema-change-tax, no "we sunset that report" risk. Funnel + retention queries are SQL you wrote.
- **Cheaper at scale.** Mixpanel charges $0.0007/event at volume; Supabase Postgres on Pro plan absorbs 10M events/mo with no per-event cost.
- **One source of truth for admin + product.** Same table that powers the landing-page HUD powers the admin churn dashboard powers the per-UTM conversion report.

Trade-off you accept: no out-of-the-box "session replay" or "heatmap." If you need those, layer PostHog OSS on top — but the ingest stays first-party.

---

## 2. Architecture

```
src/
├── lib/
│   └── analytics/
│       ├── client.ts                  # Browser: getVisitorId, getSessionId, trackPageView, trackEvent
│       ├── user-agent.ts              # isBotUA, deviceTypeFromUA, browserFromUA, osFromUA
│       ├── funnel.ts                  # funnelStepForPath, isTrackablePath
│       └── queries.ts                 # Server: aggregation queries for admin dashboard
│
├── app/
│   ├── api/
│   │   ├── track/route.ts             # POST ingest — public, rate-limited, bot-filtered
│   │   └── metrics/live/route.ts      # GET public HUD counters (10s cache, monotonic)
│   ├── admin/
│   │   └── analytics/page.tsx         # Funnel / cohort / per-route / per-UTM dashboards
│   └── _components/
│       └── PageViewTracker.tsx        # Mounted in root layout; fires page_view on route change
│
supabase/migrations/
├── NNNN_page_events.sql
├── NNNN_page_event_visitors.sql
├── NNNN_upsert_visitor_event_atomic.sql
└── NNNN_check_track_rate_limit.sql
```

Both ingest writes (`page_events` insert + visitor upsert) go through `service_role` only. Browser clients never have RLS read access to these tables.

---

## 3. Schema (Supabase)

```sql
-- One row per tracked event. High-volume; partition by month if you outgrow a single table.
create table page_events (
  id uuid primary key default gen_random_uuid(),
  visitor_id text not null,
  session_id text not null,
  user_id uuid references auth.users on delete set null,   -- nullable; populated once user signs up
  event_type text not null,                                 -- 'page_view' | 'opt_in' | 'signup_complete' | ...
  path text not null,                                       -- '/pricing' (query-stripped)
  funnel_step text,                                         -- 'landing' | 'pricing' | 'checkout' | 'dashboard'
  referrer text,
  referrer_host text,
  utm_source text, utm_medium text, utm_campaign text, utm_term text, utm_content text,
  country text, region text, city text,                     -- from Vercel geo headers
  device_type text, browser text, os text,
  user_agent_hash text,                                     -- sha256(ua).slice(0,32) — for joining without storing raw UA
  is_bot boolean default false,                             -- fingerprint-flagged bots ARE recorded (forensics)
  metadata jsonb,                                           -- sanitized event-specific props
  created_at timestamptz default now()
);

create index page_events_created_at_idx on page_events (created_at desc);
create index page_events_visitor_idx on page_events (visitor_id, created_at desc);
create index page_events_user_idx on page_events (user_id, created_at desc) where user_id is not null;
create index page_events_event_type_idx on page_events (event_type, created_at desc);
create index page_events_path_idx on page_events (path, created_at desc);

-- One row per visitor — first-touch attribution lives here, immutable after insert.
create table page_event_visitors (
  visitor_id text primary key,
  first_seen_at timestamptz not null,
  last_seen_at timestamptz not null,
  first_path text,
  first_referrer text, first_referrer_host text,
  first_utm_source text, first_utm_medium text, first_utm_campaign text,
  first_utm_term text, first_utm_content text,
  first_country text, first_device_type text,
  total_events bigint default 1,
  opted_in boolean default false, opted_in_at timestamptz,
  signed_up boolean default false, signed_up_at timestamptz,
  user_id uuid references auth.users on delete set null     -- backfilled at signup
);

-- RLS: service-role only writes; admins only read.
alter table page_events enable row level security;
alter table page_event_visitors enable row level security;
create policy "admins read events" on page_events for select using (
  exists (select 1 from profiles p where p.id = auth.uid() and p.role = 'admin')
);
create policy "admins read visitors" on page_event_visitors for select using (
  exists (select 1 from profiles p where p.id = auth.uid() and p.role = 'admin')
);
-- No INSERT/UPDATE policy — writes go through service-role only.
```

Reference: `supabase/migrations/00092_upsert_visitor_event_atomic.sql`.

---

## 4. The atomic upsert RPC

Why an RPC and not two client-side queries: two concurrent requests for the same `visitor_id` could both read `existing=null`, both INSERT, lose one update, or double-count `total_events`. Single SQL trip via `ON CONFLICT DO UPDATE` is race-free.

```sql
create or replace function upsert_visitor_event(
  p_visitor_id text, p_now timestamptz,
  p_first_path text, p_first_referrer text, p_first_referrer_host text,
  p_first_utm_source text, p_first_utm_medium text, p_first_utm_campaign text,
  p_first_utm_term text, p_first_utm_content text,
  p_first_country text, p_first_device_type text,
  p_event_type text, p_user_id uuid
) returns void language plpgsql security definer set search_path = public as $$
begin
  insert into page_event_visitors (...) values (...)
  on conflict (visitor_id) do update set
    last_seen_at = excluded.last_seen_at,
    total_events = page_event_visitors.total_events + 1,
    -- first_* columns deliberately NOT in update set — first-touch is immutable
    opted_in = page_event_visitors.opted_in or excluded.opted_in,
    opted_in_at = coalesce(page_event_visitors.opted_in_at, excluded.opted_in_at),
    signed_up = page_event_visitors.signed_up or excluded.signed_up,
    signed_up_at = coalesce(page_event_visitors.signed_up_at, excluded.signed_up_at),
    user_id = coalesce(page_event_visitors.user_id, excluded.user_id);
end; $$;

revoke all on function upsert_visitor_event(...) from public, anon, authenticated;
grant execute on function upsert_visitor_event(...) to service_role;
```

The `first_*` columns are written ONLY on INSERT. Once a visitor has a `first_utm_source`, no subsequent event overwrites it — first-touch attribution survives a 6-month-later conversion.

---

## 5. `/api/track` ingest

Reference: `src/app/api/track/route.ts`.

**Must be in `PUBLIC_API_PREFIXES`** in `proxy.ts` — otherwise the default-deny edge guard 401s every event.

Three-layer bot defense:
1. **UA-pattern bot** (`isBotUA(ua)`): Googlebot, Bingbot, AhrefsBot, GPTBot, ClaudeBot, headless Chrome, missing UA → silent 204, no row written.
2. **Per-IP rate limit** (`check_track_rate_limit` RPC, default 60 events/min): scripted spam → silent 204. RPC failures fall through (don't block real traffic if RPC briefly fails).
3. **Request-fingerprint score**: real browsers send Sec-Fetch-Site/Mode/Dest + accept-language + a non-`*/*` Accept. Scrapers miss several. Threshold ≥2 → row IS written with `is_bot=true` (forensics + dashboard "include bots" toggle), excluded from default aggregations.

Also enforces:
- `visitor_id` + `session_id` required (400 if missing)
- `event_type` in whitelist (400 otherwise)
- `path` stripped of query/fragment, validated against `isTrackablePath` (so a forged client can't spray events for `/admin/*` or `/api/*`)
- `metadata` sanitized: drops nested objects/arrays, caps 16 fields × 512 chars each × 4096 bytes total
- Vercel geo headers (`x-vercel-ip-country`, `-region`, `-city`) auto-populate country/region/city
- IP itself is **never stored** — `sha256(ip + salt)` is used for the rate-limit key

---

## 6. Visitor + session IDs

Reference: `src/lib/analytics/client.ts`.

- **Visitor ID** (`kontent_vid` cookie): `crypto.randomUUID()`, 365-day expiry, `SameSite=Lax; Secure; path=/`. Persists across sessions and tabs. SameSite=Lax preserved on external-click referrers.
- **Session ID** (`kontent_sid` in `sessionStorage`): new on first event of a session; rotated after 30 min idle. Used for "active sessions this hour" metrics.
- **No localStorage fallback for visitor_id.** Cookie is authoritative; if cookies are blocked, generate per-call and accept it'll be a new visitor every page. That's a feature — bot-blockers that strip cookies look like bots in the data, which is correct.

---

## 7. Signup attribution (don't break visitor stability)

When a user signs up:

1. Pass the current `visitor_id` cookie value in the signup form payload.
2. `/api/track` writes `signup_complete` event with `user_id = newUser.id`.
3. The atomic RPC sets `page_event_visitors.signed_up = true`, `signed_up_at = now()`, `user_id = newUser.id`.
4. **Do NOT overwrite the `kontent_vid` cookie with the user.id.** Keep visitor_id stable; the `user_id` pointer is the link.

This lets you query: "for every paying user, what was their `first_utm_source` 6 weeks before they converted?" — answer is one JOIN on `page_event_visitors.user_id → profiles.id`.

---

## 8. Event taxonomy (5–10 events, not 500)

Pick canonical names up front; don't track every click. Bad taxonomies are unrecoverable.

Typical SaaS set:
- `page_view` — every route change (auto, from PageViewTracker)
- `cta_click` — primary above-fold CTA (manual, with `metadata.cta_id`)
- `opt_in` — email captured (lead-magnet form, newsletter)
- `signup_complete` — auth success
- `onboarding_complete` — wizard finished
- `first_generation` / `first_publish` / first-canonical-product-action — activation marker
- `checkout_start` — Stripe session created
- `checkout_complete` — Stripe webhook confirmed paid
- `scroll_depth` — 25/50/75/100 milestones (only on landing/pricing/blog)

Anything more specific lives in `metadata` JSON, not a new event name. `cta_click` with `metadata.cta_id = "hero_primary"` is better than `hero_primary_clicked`.

---

## 9. `/api/metrics/live` (public HUD counters)

Reference: `src/app/api/metrics/live/route.ts`.

Public GET, no auth, 10–30s cache. Powers landing-page pills like "1,247 users · 8.2k generations · 91 published today."

Pattern:
- Read live counts from source tables (`signups`, `generations`, `published_posts`) via service-role admin client
- Read stored high-water mark from a `lifetime_metrics` singleton row
- `Math.max(live, stored)` → return monotonic value (never decrements even if a source row is deleted)
- Fire-and-forget upsert of the new high-water mark
- Cache-Control: `public, max-age=30, stale-while-revalidate=60`
- On error: return zeroes + `pipelineStatus: 'DEGRADED'` rather than 500 — the landing page shouldn't break because metrics are down

**Don't reach into `page_events` for the HUD.** Aggregating 10M-row tables on every landing-page hit kills the DB. The HUD reads pre-counted source tables; deep analytics reads `page_events`.

---

## 10. Admin analytics dashboard

Reference: `src/lib/analytics/queries.ts`.

> **Full spec:** this section is the summary. The complete dashboard surface — ET-anchored range engine, KPI tiles + sparklines, the channel classifier, every conversion table, the Active-Now globe, per-page drill-down, and weekly reports — is its own playbook: [ADMIN-ANALYTICS-DASHBOARD-PLAYBOOK](ADMIN-ANALYTICS-DASHBOARD-PLAYBOOK.md). Build this ingest layer first, then that.

Four canonical views:

**Funnel** — `visits → signups → activations → paid` for a date range. Each step is `COUNT(DISTINCT visitor_id)` filtered by event_type. Drop-off % between steps is the headline number.

**Cohort retention** — weekly cohorts by `signed_up_at` ISO week, retained = had any `page_view` in week N+1, N+2, etc. Standard retention triangle.

**Per-route engagement** — `GROUP BY path` over `page_views`, ranked by unique visitors, with avg scroll-depth metadata aggregate.

**Per-UTM-source conversion** — `GROUP BY first_utm_source` over `page_event_visitors`, with `signed_up_at IS NOT NULL` ratio. This is the table that proves which traffic source is worth more $.

All four hit `page_event_visitors` (small, ~1 row per human) for filters, then JOIN to `page_events` only when needed. Heavy queries belong in a nightly-refreshed materialized view — don't run them live on every dashboard render.

---

## 11. Churn-risk signal

A "churn-risk" column on the admin user list, computed nightly:

```sql
create materialized view user_churn_risk as
select
  p.id as user_id,
  p.email,
  s.status as subscription_status,
  max(e.created_at) filter (where e.event_type = 'first_generation' or e.event_type = 'page_view') as last_active_at,
  case
    when s.status != 'active' then 'na'
    when max(e.created_at) is null then 'red'
    when now() - max(e.created_at) > interval '14 days' then 'red'
    when now() - max(e.created_at) > interval '7 days'  then 'orange'
    else 'green'
  end as churn_risk
from profiles p
left join subscriptions s on s.user_id = p.id
left join page_events e on e.user_id = p.id and e.event_type in ('first_generation','page_view')
group by p.id, p.email, s.status;

create unique index on user_churn_risk (user_id);
-- Refresh nightly:
-- refresh materialized view concurrently user_churn_risk;
```

Trigger: `pg_cron` at 02:00 UTC. Orange = email re-engagement. Red = save call from CSM.

---

## 12. Common pitfalls

**❌ Don't store per-event PII in `metadata`.** Email, full name, raw IP — every event becomes a privacy disclosure surface. `metadata` is event-shape only (`{ cta_id: 'hero', percent: 75 }`); identity lives on `user_id` foreign keys.

**❌ Don't track every click.** Pick 5–10 high-signal events. A taxonomy with 200 event names becomes unqueryable in 6 months.

**❌ Don't trust `visitor_id` for security.** It's identification, not authentication. Anyone can spoof it. Never gate access or read user data by visitor_id alone — always require `auth.uid()`.

**❌ Don't aggregate live on the main DB for big queries.** Funnel/cohort/per-UTM reports go in materialized views refreshed nightly. The landing-page HUD reads pre-counted source tables, not `page_events`.

**❌ Don't expose `page_events` to client RLS read.** Service-role-only write, admin-only read. The browser never SELECTs from this table — leaking the full event log is a privacy bomb.

**❌ Don't skip the bot filter.** Googlebot alone produces 5–10% of inbound page_view rows at scale. AhrefsBot/GPTBot/ClaudeBot can double that. Without filtering, "signups / visits" ratio looks fake-low and you'll redesign the landing page to fix a bot problem.

**❌ Don't backfill `visitor_id` on signup with `setVisitorId(newUser.id)`.** Keep visitor_id stable — link via the `signup_user_id` pointer on `page_event_visitors`. Overwriting the cookie breaks pre-signup → post-signup attribution and creates a fake new visitor in tomorrow's funnel.

**❌ Don't hard-validate recovery-context fields on `/api/track`.** If `referrer` or geo header is missing, write `null` — never 400. The ingest path must be the most permissive surface in the app; a 400 here means data loss forever.

---

## 13. Kickoff prompt

> Execute the Customer Analytics Playbook at `CUSTOMER-ANALYTICS-PLAYBOOK.md` for this new app. Mirror the BKE implementation. Use the BKE codebase at `reference app` as the reference — when in doubt, read the BKE file rather than re-deriving.
>
> Inputs:
> - Supabase project: [URL + service-role key in .env]
> - Landing-page HUD pills: [list of 3–4 counters — e.g. signups, generations, posts published]
> - Event taxonomy: [list 5–10 canonical names for this app]
> - Has paid plans: [yes/no — gates churn-risk view]
>
> Pause after the migrations + `/api/track` route ship for review.

---

**Reference files in BKE:**
- `src/app/api/track/route.ts` — ingest endpoint with 3-layer bot defense
- `src/app/api/metrics/live/route.ts` — public HUD with monotonic counters
- `src/lib/analytics/client.ts` — browser visitor/session/track helpers
- `src/lib/analytics/user-agent.ts` — UA-pattern bot list + device parsing
- `src/lib/analytics/funnel.ts` — funnel-step mapper + trackable-path guard
- `src/lib/analytics/queries.ts` — admin dashboard aggregation queries (ET-anchored buckets)
- `supabase/migrations/00092_upsert_visitor_event_atomic.sql` — race-free upsert RPC