signals-scout-customer-analytics

Four cheap reads plus the join check cold-start every run:

• scout-scratchpad-search (text=customer_analytics, high limit, then text=account) — your watchlist, per-account baselines, the discovered group-type index, report: / reviewer: pointers (which report covers a risk, who owns an account), and what you've ruled out. Pass a high limit so overdue accounts don't fall out of the round-robin.

• scout-runs-list (last 7d) — what prior runs scored and ruled out; don't re-score an account a recent run already covered.

• scout-project-profile-get — products_in_use (confirm customer_analytics), top_events for fleet-wide volume context, plus existing_inbox_reports.

• inbox-reports-list (ordering=-updated_at, search=the account name / external_id) — the reports already in the inbox. Your own report-channel reports persist their backing signals under source_product=signals_scout (not customer_analytics), so don't filter source_product=customer_analytics — you'd miss every report you authored. A risk on an account you've reported before is an edit, not a fresh report; pull the closest matches with inbox-reports-retrieve before authoring.

• Discover the account group-type index and verify the join. Don't assume an index. Find which $group_N the roster keys to, and how many accounts actually have events:

  Copy

  SELECT countIf(external_id IN (SELECT DISTINCT $group_0 FROM events WHERE timestamp > now() - INTERVAL 30 DAY AND $group_0 != '')) AS g0,
         countIf(external_id IN (SELECT DISTINCT $group_1 FROM events WHERE timestamp > now() - INTERVAL 30 DAY AND $group_1 != '')) AS g1,
         countIf(external_id IN (SELECT DISTINCT $group_2 FROM events WHERE timestamp > now() - INTERVAL 30 DAY AND $group_2 != '')) AS g2,
         count() AS total
  FROM system.accounts WHERE external_id != ''

  The index with meaningful overlap is the account grain — record it as pattern:customer_analytics:group-type so future runs skip rediscovery. ~0 overlap on every index → quick close-out (join-unlinked).

Patterns to watch — starting points, not a checklist. All per-account queries join system.accounts to group-keyed events on the discovered index (shown as $group_1 below).

The classic leading churn indicator: a named account whose engagement drops sharply against its own trailing baseline while still nominally alive. Score the latest complete week vs the prior week(s), scoped to staked accounts above a volume floor so a tiny account's noise can't trip it:

WITH staked AS (
  SELECT external_id, name
  FROM system.accounts
  WHERE external_id != ''
    AND id IN (SELECT account_id FROM system.account_relationships
               WHERE isNull(ended_at) AND isNotNull(user_id))
),
ev AS (
  SELECT $group_1 AS gk,
         countIf(timestamp > now() - INTERVAL 7 DAY) AS wk,
         countIf(timestamp <= now() - INTERVAL 7 DAY AND timestamp > now() - INTERVAL 14 DAY) AS prev,
         count(DISTINCT if(timestamp > now() - INTERVAL 7 DAY, distinct_id, NULL)) AS wau
  FROM events WHERE timestamp > now() - INTERVAL 14 DAY AND $group_1 != '' GROUP BY gk
)
SELECT s.name, e.wk, e.prev, e.wau,
       round((e.wk - e.prev) / nullif(e.prev,0) * 100) AS pct_change
FROM staked s INNER JOIN ev e ON e.gk = s.external_id
WHERE e.prev > 200 AND e.wk < e.prev * 0.5
ORDER BY e.prev DESC LIMIT 25

Who holds the relationship (and which kind — definitions are team-defined, so don't assume "CSM" exists) comes from system.account_relationships joined to system.account_relationship_definitions on definition_id, filtered to isNull(ended_at).

Confirm against a longer baseline (extend to 4–6 prior weeks, same weekday span) before trusting a single week — a one-week dip on an account with a lumpy cadence is not a cliff. The strong shape is a sustained drop, broad across the account's users (not one departing user — see single-threading), with the fleet holding over the same window.

An account that had a steady cadence and has now gone quiet. Find staked accounts with healthy activity in the prior 30–60d window but ~0 events in the last N days:

WITH ev AS (
  SELECT $group_1 AS gk,
         countIf(timestamp > now() - INTERVAL 14 DAY) AS recent,
         countIf(timestamp <= now() - INTERVAL 14 DAY AND timestamp > now() - INTERVAL 60 DAY) AS baseline,
         max(timestamp) AS last_seen
  FROM events WHERE timestamp > now() - INTERVAL 60 DAY AND $group_1 != '' GROUP BY gk
)
SELECT a.name, e.baseline, e.recent, e.last_seen
FROM system.accounts a INNER JOIN ev e ON e.gk = a.external_id
WHERE a.external_id != ''
  AND a.id IN (SELECT account_id FROM system.account_relationships
               WHERE isNull(ended_at) AND isNotNull(user_id))
  AND e.baseline > 300 AND e.recent = 0
ORDER BY e.baseline DESC LIMIT 25

A previously-busy account with an assigned owner at zero for two weeks is the renewal-risk classic. Tune the baseline floor and the silence window to the project's cadence (recorded in scratchpad).

The account is still active in aggregate, but its engagement was concentrated in one or two distinct_ids and those have gone silent — concentration risk even when the totals look fine. For a watched account, compare the prior-period top users by event volume against the current period; a dominant user (e.g. >50% of the account's events) dropping to zero while others continue is the shape. Surface as the human-readable risk ("account X's most active user went dark"), not raw ids.

Customer analytics is CSM/AE-facing, so the positive inverse is in-scope (unlike pure anomaly scouts). A staked account whose usage or active-seat count is climbing sharply vs its own baseline is an upsell opportunity worth surfacing to the AE. Same query shape as the cliff, inverted (e.wk > e.prev * 2, WAU growing), with a volume floor. File at P3 — opportunity, not incident.

Write a scratchpad entry whenever you observe something a future run should know, encoding the category in the key prefix so a future run finds it with one text= search:

• pattern:customer_analytics:group-type — "Account grain is $group_1 (group_type_index 1); 1,438 accounts, ~1,180 join to event group keys. external_id = group key = customer domain."
• pattern:customer_analytics:fleet-baseline — "~600 accounts active in a normal week; fleet WAU steady ~X. Weekend dip is normal."
• watchlist:customer_analytics:account:<external_id> — name, active relationship holders, value tier, baseline weekly volume/WAU, cadence, last_scored + next_due.
• baseline:customer_analytics:account:<external_id> — the learned normal: weekly event-volume / WAU band (median + MAD), so the next run scores cheaply instead of recomputing.
• dedupe:customer_analytics:account:<external_id> — a risk already surfaced, with the condition that should re-escalate it (a further drop, or recovery + relapse).
• noise:customer_analytics:account:<external_id> — "this account is a known sandbox / migrating off / seasonal — its dips are expected."
• report:customer_analytics:account:<external_id> — the report_id of a report you filed for a risk on this account, so the next run edits it (append_evidence with the fresh window) instead of duplicating.
• reviewer:customer_analytics:<area> — a resolved owner (bare lowercase GitHub login) for an account segment / CSM-team surface, so reports route to a human faster.

By run #5 the scratchpad knows the account grain, the join health, the fleet baseline, the handful of accounts worth watching, and who owns each — so a real cliff lands with the right context attached.

The generic report mechanics — search the inbox first (via the report:customer_analytics:account:<external_id> pointer, else an inbox-reports-list search on the account's specific name / external_id, not a broad word like churn), edit-vs-author, the status rules, reviewer routing, non-idempotent dedup, and the priority / repository fields — live in the harness prompt and in authoring-scouts → references/report-contract.md. Do not re-derive them here. This section is only the customer-analytics judgment layered on top:

• Edit when a still-live report already tracks the account — a cliff still deepening, a dormancy still unbroken, a champion still gone. A persistent risk is one report across runs: a new complete week confirming it is ongoing is a re-escalation (append_evidence with the fresh volume/WAU numbers), not a fresh report per tick.
• Author when nothing live covers the account. A report-worthy finding shows the account's engagement dropped clearly below its own seasonality-matched baseline (sustained, not a single lumpy week), the fleet held over the same window (quantify both — "Acme weekly events 4.2k→1.1k while fleet steady at ~600 active accounts"), the account is commercially staked (assigned role or CRM link — name it), and the move isn't one departing user mistaken for an account-wide cliff. Put the account name, external_id, the latest-window numbers, the baseline band, WAU, the assigned owner, and the time window in the evidence. Attach the account's weekly engagement series via charts with the fleet's series alongside (window wide enough to show both baselines) so the cliff-on-steady-fleet shape is verifiable, not just asserted. These are CSM/AE investigations, not code fixes → actionability=requires_human_input. Priority: a confirmed sustained cliff or dormancy onset on a staked, high-value account is P2; a single-segment/suggestive move, an unstaked account, or an expansion signal is P3.
• Remember if suggestive but below the bar, or to refresh a baseline, or to record what you ruled out and why.
• Skip if a noise: / addressed: / dedupe: entry, or an existing inbox report, already covers it.

Sibling courtesy: a fleet-wide move already reported by product-analytics or anomaly-detection is theirs — author only if your per-account angle is materially new, citing the prior report. Revenue / MRR movement belongs to revenue-analytics; honor their dedupe: entries — your unique angle is always the per-account engagement frame weighted by commercial ownership.

One paragraph: which accounts you scored, what you added to the watchlist, which reports you authored or edited, what you ruled out and why. The harness saves this as the run summary; future runs read it via scout-runs-list. Do not write a separate "run metadata" scratchpad entry. "Scored the due staked accounts, all within baseline, fleet steady" is a real outcome.