SQL Report Builder for Non-Technical Managers

=== SQL REPORTING PLAN FOR NON-TECHNICAL MANAGERS ===

Business question
How many customers churned last month by plan?

Plain-English interpretation
The user wants a count of customers whose subscription ended during the previous calendar month, grouped by subscription plan.

Decision supported
Understand which plans had the highest customer churn last month.

Metric definition

Metric name
Customer churn count by plan

Business definition
Number of customers whose subscription ended during the reporting period and should be counted as churned.

Numerator
Count of distinct customers with a subscription status of canceled or ended during last month.

Denominator
Not required for churn count. If churn rate is needed, denominator should be active customers at the start of the month.

Date logic
Use subscription ended/canceled date, not signup date.

Inclusions
Paid customer subscriptions that ended during the period.

Exclusions
Trial users, test accounts, internal accounts, and customers who reactivated within the defined grace period if the business uses one.

Grouping dimensions
Plan name.

Expected grain
One row per plan.

Required tables and fields
subscriptions.subscription_id — identify subscription
subscriptions.customer_id — identify customer
subscriptions.plan_id — connect to plan
subscriptions.status — identify canceled/ended subscriptions
subscriptions.ended_at or canceled_at — churn date
plans.plan_id — join to subscriptions
plans.plan_name — group by plan
customers.customer_id — identify customer
customers.is_test — exclude test accounts if available

Assumptions

1. subscriptions.ended_at represents the churn date.
2. subscriptions.status = 'canceled' indicates churn.
3. customers.is_test can identify test accounts.
4. Plans are stored in a plans table.
5. SQL dialect is PostgreSQL. Adjust date functions if using another database.

Safety check

• Query type: read-only SELECT
• Sensitive fields avoided: Yes
• Destructive operations: None
• Date range included: Yes
• Row limit recommended: Not required because output is aggregated

SQL reporting plan

1. Start from subscriptions.
2. Join plans to get plan names.
3. Join customers to exclude test accounts.
4. Filter subscriptions that ended last month.
5. Count distinct customers.
6. Group by plan name.
7. Sort by churn count descending.

Readable SQL draft
[The skill returns a read-only SELECT query using CTEs, date filtering, grouping by plan, and no destructive operations.]

Plain-English explanation
This query looks for subscriptions marked as canceled during the previous calendar month. It excludes test customers, connects each subscription to its plan, counts unique customers, and shows the result grouped by plan.

Validation queries

1. Check total churn count before grouping.
2. Check whether ended_at has null values for canceled subscriptions.
3. Check whether test accounts are correctly excluded.
4. Compare the total with the existing churn dashboard if available.

Data quality risks

• Some churn may be stored as status = 'ended' instead of 'canceled'.
• Reactivated customers may need to be excluded depending on business definition.
• Trial cancellations may be mixed with paid churn.
• Timezone may affect month boundaries.

Privacy/sensitivity notes
The report uses aggregate customer counts and does not expose names, emails, or account details.

Manager-friendly report summary
For last month, the report shows how many customers churned in each plan. The highest churn plan should be reviewed for onboarding, pricing, product fit, or support issues. This result depends on the chosen churn definition and whether reactivations or trials are excluded.

Analyst handoff notes
Confirm the correct churn date field, churn status values, plan table name, and test-account flag before running in production reporting.