# Founder funnel — ready-to-run analytics queries

Events are documented in **[FOUNDER-ANALYTICS.md](./FOUNDER-ANALYTICS.md)**. Adjust table/column names for your Postgres schema (`analytics_events`, `event_name`, `metadata` JSON, `session_id`, `user_id`, `created_at`).

---

## Core funnel (same `session_id`)

```sql
-- Pack → test message (activation intent)
SELECT COUNT(DISTINCT p.session_id) AS sessions
FROM analytics_events p
JOIN analytics_events t ON t.session_id = p.session_id
WHERE p.event_name = 'founder_pack_created'
  AND t.event_name = 'agent_test_message_sent'
  AND t.created_at > p.created_at;
```

```sql
-- Pack → embed copy
SELECT COUNT(DISTINCT p.session_id) AS sessions
FROM analytics_events p
JOIN analytics_events e ON e.session_id = p.session_id
WHERE p.event_name = 'founder_pack_created'
  AND e.event_name = 'activation_embed_copied'
  AND e.created_at > p.created_at;
```

```sql
-- Embed → publish (deeper activation)
SELECT COUNT(DISTINCT e.session_id) AS sessions
FROM analytics_events e
JOIN analytics_events pub ON pub.session_id = e.session_id
WHERE e.event_name = 'activation_embed_copied'
  AND pub.event_name = 'activation_published'
  AND pub.created_at > e.created_at;
```

```sql
-- Any session with a saved agent schedule (quick actions)
SELECT COUNT(DISTINCT session_id) AS sessions
FROM analytics_events
WHERE event_name = 'activation_agent_schedule_created';
```

```sql
-- Eval baseline saved (trust loop)
SELECT DATE(created_at) AS day, COUNT(*) AS n
FROM analytics_events
WHERE event_name = 'eval_baseline_saved'
GROUP BY 1 ORDER BY 1 DESC LIMIT 30;
```

```sql
-- Post-signup Autopilot tip exposure
SELECT COUNT(*) FILTER (WHERE event_name = 'post_signup_autopilot_tip_shown') AS tip_shown,
       COUNT(*) FILTER (WHERE event_name = 'account_registered') AS registrations
FROM analytics_events
WHERE created_at > NOW() - INTERVAL '30 days';
```

```sql
-- Doc links (demo / checklist) from builder
SELECT metadata->>'doc' AS doc, COUNT(*) AS clicks
FROM analytics_events
WHERE event_name = 'product_doc_link_clicked'
  AND created_at > NOW() - INTERVAL '30 days'
GROUP BY 1 ORDER BY 2 DESC;
```

```sql
-- Baseline nudge (shown after 3 successful test chats per agent — client-side threshold)
SELECT DATE(created_at) AS day, COUNT(*) AS n
FROM analytics_events
WHERE event_name = 'eval_baseline_nudge_shown'
GROUP BY 1 ORDER BY 1 DESC LIMIT 30;
```

```sql
-- Trust path rail clicks (href fragment, truncated server-side if stored)
SELECT metadata->>'href' AS target, COUNT(*) AS n
FROM analytics_events
WHERE event_name = 'trust_path_nav_click'
  AND created_at > NOW() - INTERVAL '30 days'
GROUP BY 1 ORDER BY 2 DESC;
```

```sql
-- Copy CI curl (nightly eval adoption)
SELECT DATE(created_at) AS day, COUNT(*) AS n
FROM analytics_events
WHERE event_name = 'eval_ci_curl_copied'
GROUP BY 1 ORDER BY 1 DESC LIMIT 30;
```

```sql
-- Embed share baseline nudge (once per agent per browser tab until baseline exists)
SELECT DATE(created_at) AS day, COUNT(*) AS n
FROM analytics_events
WHERE event_name = 'embed_share_baseline_nudge_shown'
GROUP BY 1 ORDER BY 1 DESC LIMIT 30;
```

```sql
-- First ship checklist manual toggles (baseline row is not tracked here)
SELECT metadata->>'step' AS step,
       COUNT(*) FILTER (WHERE (metadata->>'checked') = 'true') AS checked,
       COUNT(*) FILTER (WHERE (metadata->>'checked') = 'false') AS unchecked
FROM analytics_events
WHERE event_name = 'first_ship_checklist_toggle'
  AND created_at > NOW() - INTERVAL '30 days'
GROUP BY 1 ORDER BY 1;
```

```sql
-- Founder pack created — typical metadata: packType, goldenEvalPresets, safeDefaults
SELECT metadata->>'packType' AS pack_type, COUNT(*) AS n
FROM analytics_events
WHERE event_name = 'founder_pack_created'
  AND created_at > NOW() - INTERVAL '30 days'
GROUP BY 1 ORDER BY 2 DESC;
```

```sql
-- Embed copy by platform (includes embed_link from Copy link)
SELECT metadata->>'platform' AS platform, COUNT(*) AS n
FROM analytics_events
WHERE event_name = 'activation_embed_copied'
  AND created_at > NOW() - INTERVAL '30 days'
GROUP BY 1 ORDER BY 2 DESC;
```

---

## Tips

- **Authenticated** events include `user_id` when the client sends a Bearer token; anonymous users still have `session_id`.
- For **per-agent** server-side funnels, join through your `agents` / `users` tables as needed.
- Export results to a spreadsheet weekly to watch **pack → test → embed → publish → baseline** movement.