What this prompt does
This prompt casts the AI as a senior analytics engineer and makes it generate a complete set of runnable KPI queries with documented assumptions, not loose fragments. You supply [business_type], [db_engine], your [tables], and the [product_or_feature] to rank in the top-N. It returns daily active users over a configurable range, weekly revenue with a week-over-week delta, a conversion funnel, a retention cohort table by signup week, a top-N usage query, and inline comments stating each KPI's definition, source tables, and grain.
The structure works because it pins definitions in the SQL itself. The hardest part of a metrics layer is rarely the chart — it is agreeing what "active" and "retained" mean. By forcing an inline comment on each query stating the assumed grain and source tables, the prompt makes those definitions explicit and reviewable. [business_type] shapes which activation event the funnel targets, [tables] grounds the queries in your real schema, and [db_engine] governs window-function syntax.
When to use it
- You are drafting a metrics layer and need every KPI defined before anyone builds a chart.
- You and your team disagree on what "active" or "retained" should mean in SQL.
- You want DAU, weekly revenue, a funnel, cohorts, and top-N in one consistent pass.
- You need each query's grain and source tables documented to prevent later disputes.
- You want window functions used where they genuinely simplify the logic.
- You are standing up a dashboard and want the SQL before the visualisation work starts.
Example output
You get one labelled SQL block per KPI: daily active users over a configurable date range, weekly revenue with a WoW delta column, a conversion funnel from first touch to the key activation event, a retention cohort table by signup week, and a top-N of your chosen entity by usage. Each block carries inline comments stating the KPI's definition, source tables, and assumed grain.
Pro tips
- List your real
[tables]accurately — the queries reference them directly, so a wrong table name means rework rather than a paste. - Set
[business_type]so the funnel's activation event matches your model; a B2B subscription funnel differs from an e-commerce one. - Use the inline grain-and-source comments as the contract; they prevent most "the numbers look wrong" arguments later.
- Pin every metric definition with your team before anyone builds a chart on top of it.
- Pick
[product_or_feature]deliberately so the top-N ranks the entity you actually care about, whether features, products, or pages, and the ranking answers a real question. - Treat the configurable date range on the DAU query as a parameter to wire into your dashboard rather than a hardcoded window, so the same SQL serves multiple views.
- Confirm the
[db_engine]matches your warehouse, since window-function syntax and date handling vary between engines and a mismatch produces queries that look right but fail to run.
