# Spec 051 — KPI Query Pack (T6.3)

Fixture-tested SQL for every PRD Success Metrics KPI plus the stalled-store
list query, run against the launch analytics tables (`kiosk_buykiosk.onboardingEventLog`,
`onboardingCampaignCohort`, `systemAlerts`/`systemAlertAcknowledgments`, and
`kiosk_users.userInvites`/`inviteMessages`/`userStoreAssignments`).

**Status:** every query below was ACTUALLY EXECUTED against the real dev
database (`kiosk_buykiosk`/`kiosk_users`) with a purpose-built fixture
dataset (campaignId `kpi-pack-2026`, stores `ob01`/`ob02`/`ob03`) on
2026-07-22. Real output is captured under each query. The fixture rows have
since been deleted — this doc is a durable reference for the launch team,
not a live report.

## How to use this pack at launch

1. Replace `'kpi-pack-2026'` with the real `ONBOARDING_CAMPAIGN_ID` value.
2. Replace the hardcoded `alertId = 6` in KPI 1 with the real System Alert's
   `id` (recorded in the baseline artifact's `campaignRegistry.alertId` once
   the announcement publishes — see `scripts/onboarding-baseline-snapshot.php`
   and `contracts/campaign-alert-contract.md` §4).
3. Run weekly (platform team cadence per PRD Success Metrics: "measurement
   owner and cadence assigned at launch, platform team, weekly").
4. **Known gotcha (found while building this pack):** `onboardingCampaignCohort.typeNum`
   and `kiosk_users.userStoreAssignments.typeNum` have DIFFERENT collations
   (`utf8mb4_uca1400_ai_ci` vs `utf8mb4_unicode_ci` respectively — a schema
   drift between when the 051 tables were created and the older `kiosk_users`
   schema). Any cross-DB join between them needs an explicit
   `COLLATE utf8mb4_unicode_ci` on both sides of the join predicate or MySQL
   raises `ERROR 1267 (HY000) Illegal mix of collations`. KPI 1 below shows
   the pattern; apply it to any NEW query joining these two tables.

---

## KPI 1: Announcement → hub conversion

**Definition:** Stores with ≥1 `onboarding.hub_viewed(source=alert)` ÷ stores
where ≥1 currently-assigned user acknowledged the campaign alert, within 7
days of publish. Target: ≥ 60%. Numerator is measured by hub visits, NEVER
by acknowledgments directly (D-3/M2-02 — see `campaign-alert-contract.md` §4).

```sql
SELECT
  (SELECT COUNT(DISTINCT typeNum) FROM onboardingEventLog
   WHERE eventKey = 'onboarding.hub_viewed' AND campaignId = 'kpi-pack-2026'
     AND JSON_UNQUOTE(JSON_EXTRACT(properties, '$.source')) = 'alert'
     AND occurredAt BETWEEN (SELECT createdAt FROM systemAlerts WHERE id = 6)
                         AND DATE_ADD((SELECT createdAt FROM systemAlerts WHERE id = 6), INTERVAL 7 DAY)
  ) AS numerator_stores_with_alert_hub_view,
  (SELECT COUNT(DISTINCT occ.typeNum) FROM onboardingCampaignCohort occ
   WHERE occ.campaignId = 'kpi-pack-2026'
     AND EXISTS (
       SELECT 1 FROM systemAlertAcknowledgments saa
       JOIN kiosk_users.userStoreAssignments usa
         ON usa.userId = saa.userId
        AND usa.typeNum COLLATE utf8mb4_unicode_ci = occ.typeNum COLLATE utf8mb4_unicode_ci
        AND usa.isActive = 1
       WHERE saa.alertId = 6
     )
  ) AS denominator_stores_with_acking_active_user;
```

**Captured output (fixture: ob01 hub-viewed w/ source=alert; 2 users
acknowledged, both active-assigned to ob01):**

```
+--------------------------------------+--------------------------------------------+
| numerator_stores_with_alert_hub_view | denominator_stores_with_acking_active_user |
+--------------------------------------+--------------------------------------------+
|                                    1 |                                          1 |
+--------------------------------------+--------------------------------------------+
```

Conversion = 1/1 = 100% for this fixture slice (expected: the fixture was
built to prove exactly one store on both sides of the ratio).

---

## KPI 2: Activation (30d / 90d)

**Definition:** Stores with `onboarding.activated` ÷ BK-native-eligible
stores (cohort = 'eligible', EXCLUDING grandfathered), within 30 / 90 days of
campaign publish. Targets: ≥ 40% (30d) / ≥ 60% (90d). Anchor: this pack uses
`onboardingCampaignCohort.snapshotAt` as the publish-adjacent anchor per
store, since campaign publish itself isn't a queryable column anywhere (it
lives in the baseline JSON artifact / launch runbook per
`campaign-alert-contract.md` §4) — `snapshotAt` and publish happen minutes
apart in the real launch sequence (baseline snapshot runs FIRST, then the
campaign env var is set, then the alert publishes), so this is accurate to
within that gap.

```sql
SELECT
  (SELECT COUNT(*) FROM onboardingCampaignCohort WHERE campaignId = 'kpi-pack-2026' AND cohort = 'eligible') AS eligible_cohort_size,
  (SELECT COUNT(DISTINCT oel.typeNum) FROM onboardingEventLog oel
   JOIN onboardingCampaignCohort occ ON occ.typeNum = oel.typeNum AND occ.campaignId = 'kpi-pack-2026' AND occ.cohort = 'eligible'
   WHERE oel.eventKey = 'onboarding.activated' AND oel.campaignId = 'kpi-pack-2026'
     AND oel.occurredAt <= DATE_ADD(occ.snapshotAt, INTERVAL 30 DAY)
  ) AS activated_within_30d,
  (SELECT COUNT(DISTINCT oel.typeNum) FROM onboardingEventLog oel
   JOIN onboardingCampaignCohort occ ON occ.typeNum = oel.typeNum AND occ.campaignId = 'kpi-pack-2026' AND occ.cohort = 'eligible'
   WHERE oel.eventKey = 'onboarding.activated' AND oel.campaignId = 'kpi-pack-2026'
     AND oel.occurredAt <= DATE_ADD(occ.snapshotAt, INTERVAL 90 DAY)
  ) AS activated_within_90d;
```

**Captured output (fixture: 3 eligible stores; ob01 activated day5,
ob02 day15, ob03 day70 post-publish):**

```
+----------------------+----------------------+----------------------+
| eligible_cohort_size | activated_within_30d | activated_within_90d |
+----------------------+----------------------+----------------------+
|                    3 |                     2 |                     3 |
+----------------------+----------------------+----------------------+
```

---

## KPI 3: Setup completion

**Definition:** Stores with `onboarding.flow_completed` ÷ activated stores,
within 14 days of THEIR OWN activation (per-store window, not a campaign-wide
one). Target: ≥ 50%.

```sql
SELECT
  COUNT(*) AS activated_stores,
  SUM(CASE WHEN EXISTS (
        SELECT 1 FROM onboardingEventLog fc
        WHERE fc.typeNum = act.typeNum AND fc.eventKey = 'onboarding.flow_completed'
          AND fc.occurredAt <= DATE_ADD(act.activatedAt, INTERVAL 14 DAY)
      ) THEN 1 ELSE 0 END) AS completed_within_14d
FROM (
  SELECT typeNum, MIN(occurredAt) AS activatedAt
  FROM onboardingEventLog
  WHERE eventKey = 'onboarding.activated' AND campaignId = 'kpi-pack-2026'
  GROUP BY typeNum
) act;
```

**Captured output (fixture: ob01 completed at day10 post-activation
[counts]; ob02 completed at day45 post-activation [too late]; ob03 never
completed):**

```
+-------------------+-----------------------+
| activated_stores  | completed_within_14d  |
+-------------------+-----------------------+
|                 3 |                     1  |
+-------------------+-----------------------+
```

---

## KPI 4: Invite efficacy

**Definition:** `invite.activated` ÷ invites with ≥1 channel reaching
`delivered` (fallback `sent` where the channel reports no delivery state),
within 7 days of send. Target: ≥ 70%. Computed directly from the
`kiosk_users.userInvites`/`inviteMessages` state machine (not from the event
log) since these tables ARE the authoritative per-message delivery state.

```sql
SELECT
  SUM(CASE WHEN ui.status = 'activated' AND ui.activatedAt <= DATE_ADD(ui.createdAt, INTERVAL 7 DAY) THEN 1 ELSE 0 END) AS activated_within_7d_of_send,
  COUNT(*) AS reachable_denominator
FROM kiosk_users.userInvites ui
WHERE EXISTS (
    SELECT 1 FROM kiosk_users.inviteMessages im
    WHERE im.inviteId = ui.inviteId AND im.state IN ('delivered', 'sent')
  );
-- Production version: add "AND ui.createdAt >= <window start>" to scope to
-- the reporting period; the fixture run below omitted it since these were
-- the only two invite rows present at test time.
```

**Captured output (fixture: invite #1 delivered via SMS, activated within
7d; invite #2 sent via email, never activated):**

```
+------------------------------+------------------------+
| activated_within_7d_of_send  | reachable_denominator  |
+------------------------------+------------------------+
|                            1 |                       2 |
+------------------------------+------------------------+
```

72% target check: 1/2 = 50% in this tiny fixture slice — illustrative only
(n=2); real launch volume will have a meaningful denominator.

---

## KPI 5: App adoption

**Definition:** Members with `onboarding.app_adopted(app=team)` ÷ members who
activated an account (`invite.activated`), within 7 days of account
activation. Target: ≥ 50%. Both events are **user-scoped** (`typeNum` NULL on
`app_adopted` per SDD — a login/device-token signal isn't tied to one store),
so this KPI is member-based, not per-store, matching the SDD's explicit note
that `app_adopted` never fans out per-store.

```sql
SELECT
  COUNT(DISTINCT act.userId) AS activated_accounts,
  COUNT(DISTINCT CASE WHEN aa.userId IS NOT NULL THEN act.userId END) AS adopted_team_within_7d
FROM onboardingEventLog act
LEFT JOIN onboardingEventLog aa
  ON aa.userId = act.userId AND aa.eventKey = 'onboarding.app_adopted'
  AND JSON_UNQUOTE(JSON_EXTRACT(aa.properties, '$.app')) = 'team'
  AND aa.occurredAt <= DATE_ADD(act.occurredAt, INTERVAL 7 DAY)
WHERE act.eventKey = 'invite.activated' AND act.campaignId = 'kpi-pack-2026';
```

**Captured output:**

```
+---------------------+--------------------------+
| activated_accounts  | adopted_team_within_7d   |
+---------------------+--------------------------+
|                   1 |                        1 |
+---------------------+--------------------------+
```

---

## KPI 6: First publish

**Definition:** Stores with `schedule.week_first_published` ÷ activated
stores, within 14 days of activation. Target: ≥ 60%.

```sql
SELECT
  COUNT(DISTINCT act.typeNum) AS activated_stores,
  COUNT(DISTINCT CASE WHEN wp.typeNum IS NOT NULL THEN act.typeNum END) AS published_within_14d
FROM onboardingEventLog act
LEFT JOIN onboardingEventLog wp
  ON wp.typeNum = act.typeNum AND wp.eventKey = 'schedule.week_first_published'
  AND wp.occurredAt <= DATE_ADD(act.occurredAt, INTERVAL 14 DAY)
WHERE act.eventKey = 'onboarding.activated' AND act.campaignId = 'kpi-pack-2026';
```

**Captured output (fixture: ob01 published day5; ob02 published day10;
ob03 never published):**

```
+--------------------+------------------------+
| activated_stores   | published_within_14d   |
+--------------------+------------------------+
|                   3 |                      2 |
+--------------------+------------------------+
```

---

## KPI 7: AI usage

**Definition:** Stores with ≥1 `ai.fill_applied` ÷ stores with ≥1 published
week, within 30 days of activation. Target: ≥ 40%.

```sql
SELECT
  COUNT(DISTINCT pubStores.typeNum) AS published_stores,
  COUNT(DISTINCT CASE WHEN ai.typeNum IS NOT NULL THEN pubStores.typeNum END) AS ai_used_within_30d_of_activation
FROM (SELECT DISTINCT typeNum FROM onboardingEventLog WHERE eventKey = 'schedule.week_first_published' AND campaignId = 'kpi-pack-2026') pubStores
LEFT JOIN (SELECT typeNum, MIN(occurredAt) AS activatedAt FROM onboardingEventLog WHERE eventKey = 'onboarding.activated' AND campaignId = 'kpi-pack-2026' GROUP BY typeNum) act
  ON act.typeNum = pubStores.typeNum
LEFT JOIN onboardingEventLog ai
  ON ai.typeNum = pubStores.typeNum AND ai.eventKey = 'ai.fill_applied'
  AND ai.occurredAt <= DATE_ADD(act.activatedAt, INTERVAL 30 DAY);
```

**Captured output (fixture: ob01+ob02 published; only ob02 has an
ai.fill_applied event, at day20 post-activation):**

```
+--------------------+-----------------------------------+
| published_stores   | ai_used_within_30d_of_activation   |
+--------------------+-----------------------------------+
|                   2 |                                 1 |
+--------------------+-----------------------------------+
```

---

## KPI 8: Durable adoption

**Definition:** Stores publishing a week in ≥3 of weeks 3-6 post-activation ÷
activated stores. Target: ≥ 40%. Week N post-activation is defined as
`DATEDIFF(publishedAt, activatedAt)` in `[(N-1)*7, N*7-1]`; "weeks 3-6" is
therefore the day range `[14, 41]`.

```sql
SELECT
  COUNT(DISTINCT act.typeNum) AS activated_stores,
  COUNT(DISTINCT CASE WHEN weeksPublished.weekCount >= 3 THEN act.typeNum END) AS durable_stores
FROM (SELECT typeNum, MIN(occurredAt) AS activatedAt FROM onboardingEventLog WHERE eventKey = 'onboarding.activated' AND campaignId = 'kpi-pack-2026' GROUP BY typeNum) act
LEFT JOIN (
  SELECT wp.typeNum, COUNT(DISTINCT FLOOR(DATEDIFF(wp.occurredAt, a2.activatedAt) / 7)) AS weekCount
  FROM onboardingEventLog wp
  JOIN (SELECT typeNum, MIN(occurredAt) AS activatedAt FROM onboardingEventLog WHERE eventKey = 'onboarding.activated' AND campaignId = 'kpi-pack-2026' GROUP BY typeNum) a2
    ON a2.typeNum = wp.typeNum
  WHERE wp.eventKey = 'schedule.week_first_published'
    AND DATEDIFF(wp.occurredAt, a2.activatedAt) BETWEEN 14 AND 41
  GROUP BY wp.typeNum
) weeksPublished ON weeksPublished.typeNum = act.typeNum;
```

**Captured output (fixture: ob02 published at post-activation offsets
10/14/21/28 days — 3 distinct week-3/4/5 buckets qualify; ob01 published
once at offset 5 [outside the window]; ob03 never published):**

```
+--------------------+------------------+
| activated_stores   | durable_stores   |
+--------------------+------------------+
|                   3 |                1 |
+--------------------+------------------+
```

---

## KPI 9: Support load

**Definition:** Scheduling-setup-tagged tickets per activated store ≤
pre-launch baseline rate (D-10). Target: ≤ baseline. **No live ticket table
exists** (D-10: no helpdesk API) — this KPI is NOT a live SQL query. It is
recomputed periodically by re-running the SAME CSV-driven process
`scripts/onboarding-baseline-snapshot.php` uses for the baseline
(`--tickets-csv`), against a fresh trailing-window export, and comparing the
resulting `ticketsPerActivatedStorePerWeek` figure to the baseline artifact's
value. See that script's `ticketBaseline` computation for the exact formula
(attributed tickets ÷ BK-native-eligible cohort size ÷ (windowDays/7)).

---

## Stalled-store list

Not a KPI ratio — an operational list for proactive outreach: stores
activated ≥7 days ago that have never completed the `invites` step.

```sql
SELECT act.typeNum, act.activatedAt, DATEDIFF(NOW(), act.activatedAt) AS daysSinceActivation
FROM (SELECT typeNum, MIN(occurredAt) AS activatedAt FROM onboardingEventLog WHERE eventKey = 'onboarding.activated' AND campaignId = 'kpi-pack-2026' GROUP BY typeNum) act
WHERE NOT EXISTS (
    SELECT 1 FROM onboardingEventLog s
    WHERE s.typeNum = act.typeNum AND s.eventKey = 'onboarding.step_completed'
      AND JSON_UNQUOTE(JSON_EXTRACT(s.properties, '$.stepKey')) = 'invites'
  )
  AND DATEDIFF(NOW(), act.activatedAt) >= 7
ORDER BY daysSinceActivation DESC;
```

**Captured output (fixture: ob02 and ob03 both never completed `invites`;
ob01 excluded since it has an invites step_completed event):**

```
+---------+---------------------+----------------------+
| typeNum | activatedAt         | daysSinceActivation  |
+---------+---------------------+----------------------+
| ob02    | 2026-04-28 15:36:18 |                   85 |
| ob03    | 2026-06-22 15:36:18 |                   30 |
+---------+---------------------+----------------------+
```

Generalize this pattern (swap the `stepKey` filter) for any other step-level
stalled-progress report the platform team wants (e.g. "activated but no
roster after 3 days").

---

## Fixture dataset used to produce the captured output above

For reproducibility: `campaignId='kpi-pack-2026'`, `alertId=6` (a throwaway
`systemAlerts` row), 3 cohort stores (`ob01`/`ob02`/`ob03`, all `eligible`),
publish anchor 100 days before the run. Per-store event timeline:

| Store | Activated (days after publish) | invites step | flow_completed (days post-activation) | Publishes (days post-activation) | AI applied (days post-activation) |
|---|---|---|---|---|---|
| ob01 | 5 | yes | 10 | 5 | — |
| ob02 | 15 | no | 45 | 10, 14, 21, 28 | 20 |
| ob03 | 70 | no | never | never | — |

All fixture rows were deleted after this pack's queries were captured —
`kpi-pack-2026` leaves no residue in the dev database.