Subscription Usage Context

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

1. 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.

1. 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.

1. Read caveats.md before any query. Mandatory filter: IS_INTERNAL = FALSE on every table

(staff accounts skew every number — C-01).

1. "Active" is ambiguous — always clarify. active_subscribers (paying) vs active_minutes

(engaged) are different questions. Ask which one they mean (C-04).

1. Trial rows are not subscriber rows. SUBSCRIPTION_STATE = 'trialing' is excluded from

subscriber counts by default (C-02).

1. Never SELECT *. Always a date filter on the fact tables. Always LIMIT while exploring.
2. State the grain of the result (per member, per cycle, per session) and any assumption. One line.
3. 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/.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.

Source & license

This open-source skill is cataloged on AgentStack and links to its original source — we do not rehost the code.

• Author: nimrodfisher
• Source: nimrodfisher/data-context-layer-studio
• License: MIT
• Homepage: https://github.com/nimrodfisher/data-context-layer-studio/blob/main/README.md

Install and usage instructions live in the source repository linked above.