Subscription Usage — Context Skill (example)
This file is a map, not an encyclopedia. It points at files; it doesn't explain things.
If you're tempted to explain a metric here, it belongs in data_context/metrics.yml.
Keep this file under ~90 lines.
Status: 🟡 example — definitions draft, pending sign-off · see POPULATING.md
| Piece |
State |
data_context/verified_queries/ |
🟢 3 queries (T1, A1, C1), signed 2026-07-18 by dana@lumenfit.example |
data_context/metrics.yml |
🟡 4 metrics, all decisions captured — pending flip draft → agreed |
data_context/caveats.md |
🟢 6 caveats (C-01–C-06), measured 2026-07-17 |
data_context/semantic_layer/ |
🟢 3 tables mapped — _index.md + one .yml per table |
data_context/table_profiling/ |
🟡 index + per-table stubs — run scripts/profile_table.sql to fill |
What this domain owns
- Metrics:
trial_conversion_rate, active_subscribers, active_minutes, monthly_churn_rate
— defined in data_context/metrics.yml.
- Tables:
FACT_MEMBER_SESSION, FACT_SUBSCRIPTION_CYCLE, DIM_MEMBER — mapped in
data_context/semantic_layer/_index.md.
- Boundary: owns subscription status, trial conversion, in-app engagement, and churn. Does
not own revenue/MRR (Finance domain), acquisition/ad spend (Growth domain), or
content performance (Content domain). For "did engagement drive revenue?", route to Finance.
Non-negotiables
- Check
data_context/verified_queries/ before writing any SQL. T1/A1/C1 are signed —
swap a date or a plan filter, don't rewrite joins or grain.
- Use the metric definition from
metrics.yml verbatim. Metrics are draft — flag answers as
provisional until an owner flips them to agreed. Never invent a metric.
- Read
caveats.md before any query. Mandatory filter: IS_INTERNAL = FALSE on every table
(staff accounts skew every number — C-01).
- "Active" is ambiguous — always clarify.
active_subscribers (paying) vs active_minutes
(engaged) are different questions. Ask which one they mean (C-04).
- Trial rows are not subscriber rows.
SUBSCRIPTION_STATE = 'trialing' is excluded from
subscriber counts by default (C-02).
- Never
SELECT *. Always a date filter on the fact tables. Always LIMIT while exploring.
- State the grain of the result (per member, per cycle, per session) and any assumption. One line.
- If it isn't defined here, stop and ask. A correct "X is undefined" beats a confident wrong
number.
Routing map
| If the question is about… |
Read |
| What this product is, who uses it, how it earns |
product_context/overview.md |
| "What changed recently", "why did X move" |
recent_updates/_index.md, then the month file |
| What a named metric means |
data_context/metrics.yml |
| Anything needing SQL — start here |
data_context/verified_queries/verified_queries.yml |
| Which table, per-member vs per-cycle grain, how to join |
data_context/semantic_layer/_index.md |
| What a column's values actually mean |
data_context/table_profiling/<table>.md |
| Known gotchas that change the number |
data_context/caveats.md |
| Rules for writing new SQL |
guardrails.md |
Load one row. Not the folder.
SQL preflight
1. verified_queries.yml → exact match? run verbatim, stop.
→ near match? swap date/plan filter ONLY, load caveats.md, stop.
→ no match? ↓
2. guardrails.md · caveats.md · semantic_layer/_index.md
· the table yamls you need · their table_profiling files
→ write SQL → propose it back as a verification candidate
Worked examples
"How do we decide which plans to promote?" → product_context/overview.md. No data files.
"Trial-to-paid conversion for the annual plan last quarter?" → verified_queries.yml → T1,
swap the plan filter. Exact-ish match.
"How many active subscribers right now?" → A1. But first confirm: paying subscribers
(A1), or engaged members (that's active_minutes, C-04)?
"Who's about to churn?" → C1 flags at-risk cycles; this domain owns the risk signal, not
the revenue impact. For "how much revenue is at risk", name the Finance domain too.
"Did more workouts drive higher revenue?" → Route. This domain owns engagement; Finance
owns revenue. Surface active_minutes and hand off.
1---2name: subscription-usage-context3description: Complete context for the Subscription Usage domain at LumenFit (fictional) — trial conversion, active subscribers, engagement minutes, and churn for a consumer fitness subscription. Use when a question is about who is subscribed, whether trials convert, how much members use the app, or who is at risk of cancelling. Example skill — definitions still draft, pending owner sign-off.4---56# Subscription Usage — Context Skill (example)78> **This file is a map, not an encyclopedia.** It points at files; it doesn't explain things.9> If you're tempted to explain a metric here, it belongs in `data_context/metrics.yml`.10> **Keep this file under ~90 lines.**1112## Status: 🟡 example — definitions draft, pending sign-off · see `POPULATING.md`1314| Piece | State |15| --- | --- |16| `data_context/verified_queries/` | 🟢 3 queries (T1, A1, C1), signed 2026-07-18 by dana@lumenfit.example |17| `data_context/metrics.yml` | 🟡 4 metrics, all decisions captured — pending flip `draft` → `agreed` |18| `data_context/caveats.md` | 🟢 6 caveats (C-01–C-06), measured 2026-07-17 |19| `data_context/semantic_layer/` | 🟢 3 tables mapped — `_index.md` + one `.yml` per table |20| `data_context/table_profiling/` | 🟡 index + per-table stubs — run `scripts/profile_table.sql` to fill |2122## What this domain owns2324- **Metrics:** `trial_conversion_rate`, `active_subscribers`, `active_minutes`, `monthly_churn_rate`25 — defined in `data_context/metrics.yml`.26- **Tables:** `FACT_MEMBER_SESSION`, `FACT_SUBSCRIPTION_CYCLE`, `DIM_MEMBER` — mapped in27 `data_context/semantic_layer/_index.md`.28- **Boundary:** owns subscription status, trial conversion, in-app engagement, and churn. Does29 **not** own **revenue/MRR** (Finance domain), **acquisition/ad spend** (Growth domain), or30 **content performance** (Content domain). For "did engagement drive revenue?", route to Finance.3132## Non-negotiables33341. **Check `data_context/verified_queries/` before writing any SQL.** T1/A1/C1 are signed —35 swap a date or a plan filter, don't rewrite joins or grain.362. **Use the metric definition from `metrics.yml` verbatim.** Metrics are `draft` — flag answers as37 provisional until an owner flips them to `agreed`. Never invent a metric.383. **Read `caveats.md` before any query.** Mandatory filter: `IS_INTERNAL = FALSE` on every table39 (staff accounts skew every number — C-01).404. **"Active" is ambiguous — always clarify.** `active_subscribers` (paying) vs `active_minutes`41 (engaged) are different questions. Ask which one they mean (C-04).425. **Trial rows are not subscriber rows.** `SUBSCRIPTION_STATE = 'trialing'` is excluded from43 subscriber counts by default (C-02).446. **Never `SELECT *`. Always a date filter on the fact tables. Always `LIMIT` while exploring.**457. **State the grain of the result** (per member, per cycle, per session) and any assumption. One line.468. **If it isn't defined here, stop and ask.** A correct *"X is undefined"* beats a confident wrong47 number.4849## Routing map5051| If the question is about… | Read |52| --- | --- |53| What this product is, who uses it, how it earns | `product_context/overview.md` |54| "What changed recently", "why did X move" | `recent_updates/_index.md`, then the month file |55| What a named metric means | `data_context/metrics.yml` |56| **Anything needing SQL — start here** | `data_context/verified_queries/verified_queries.yml` |57| Which table, per-member vs per-cycle grain, how to join | `data_context/semantic_layer/_index.md` |58| What a column's values actually mean | `data_context/table_profiling/<table>.md` |59| Known gotchas that change the number | `data_context/caveats.md` |60| Rules for writing **new** SQL | `guardrails.md` |6162Load one row. Not the folder.6364## SQL preflight6566```671. verified_queries.yml → exact match? run verbatim, stop.68 → near match? swap date/plan filter ONLY, load caveats.md, stop.69 → no match? ↓702. guardrails.md · caveats.md · semantic_layer/_index.md71 · the table yamls you need · their table_profiling files72 → write SQL → propose it back as a verification candidate73```7475## Worked examples7677**"How do we decide which plans to promote?"** → `product_context/overview.md`. *No data files.*7879**"Trial-to-paid conversion for the annual plan last quarter?"** → `verified_queries.yml` → **T1**,80swap the plan filter. Exact-ish match.8182**"How many active subscribers right now?"** → **A1**. But first confirm: paying subscribers83(A1), or engaged members (that's `active_minutes`, C-04)?8485**"Who's about to churn?"** → **C1** flags at-risk cycles; this domain owns the *risk signal*, not86the revenue impact. For "how much revenue is at risk", name the Finance domain too.8788**"Did more workouts drive higher revenue?"** → **Route.** This domain owns engagement; Finance89owns revenue. Surface `active_minutes` and hand off.
nimrodfisher