databusiness
BI Report
Design BI reports and analytics views — with metric definitions, data source mapping, filter logic, drill-down paths, and refresh schedules that enable self-service decision-making.
business-intelligencereportsanalyticsSQLself-service
Works well with agents
Works well with skills
customer-retention-dashboard.md
Markdown| 1 | # BI Report: Customer Retention Dashboard |
| 2 | |
| 3 | ## Report Purpose |
| 4 | |
| 5 | This report answers: "Are we retaining customers, and where are we losing them?" It supports the VP of Customer Success and Growth Product team in their weekly retention review. The primary use case is identifying cohorts with declining retention so the team can intervene before churn materializes. |
| 6 | |
| 7 | ## Metric Definitions |
| 8 | |
| 9 | | Metric | Definition | Formula | Unit | Grain | |
| 10 | |--------|-----------|---------|------|-------| |
| 11 | | Monthly Retention Rate | Percentage of customers active at month start who remain active at month end | `(start_customers - churned_customers) / start_customers * 100` | % | Monthly | |
| 12 | | Net Revenue Retention | Revenue from existing customers this month vs. same cohort last month, including expansion and contraction | `(current_month_revenue - contraction + expansion) / prior_month_revenue * 100` | % | Monthly | |
| 13 | | Churn Rate | Percentage of customers who cancelled or did not renew | `churned_customers / start_of_month_customers * 100` | % | Monthly | |
| 14 | | Time to Churn | Median days from signup to cancellation for churned customers | `MEDIAN(cancelled_at - created_at) WHERE status = 'cancelled'` | Days | Rolling 90d | |
| 15 | | Reactivation Rate | Percentage of churned customers who return within 90 days | `reactivated_90d / churned_total * 100` | % | Monthly | |
| 16 | |
| 17 | **Includes/excludes**: Churn excludes customers on seasonal pause. Trial users who never converted are excluded from all metrics. Downgrades count as contraction in NRR but not as churn. |
| 18 | |
| 19 | ## Data Source Mapping |
| 20 | |
| 21 | | Metric | Source Table(s) | Key Columns | Join Logic | Known Issues | |
| 22 | |--------|----------------|-------------|------------|-------------| |
| 23 | | Retention Rate | `analytics.customer_monthly_snapshot` | `customer_id`, `is_active`, `snapshot_month` | None | Snapshot runs at 02:00 UTC; customers cancelling after midnight appear in next month | |
| 24 | | NRR | `billing.invoices`, `billing.subscriptions` | `customer_id`, `amount`, `period_start` | Join on `customer_id` + `period_start` month | Refunds posted mid-month reduce NRR retroactively | |
| 25 | | Churn Rate | `crm.customers`, `billing.cancellations` | `customer_id`, `cancelled_at`, `reason` | Join on `customer_id` | "Reason" field is free-text; ~15% are blank | |
| 26 | |
| 27 | ## Filters and Parameters |
| 28 | |
| 29 | | Filter | Type | Default | Options | Behavior | |
| 30 | |--------|------|---------|---------|----------| |
| 31 | | Date Range | Date picker | Last 12 months | Any range, max 24 months | Applies to all metrics | |
| 32 | | Plan Tier | Multi-select | All | Free, Starter, Pro, Enterprise | Filters underlying data | |
| 33 | | Signup Cohort | Month picker | None | Any month | Overlays cohort curve on retention chart | |
| 34 | | Region | Multi-select | All | NA, EMEA, APAC | Filters all panels | |
| 35 | |
| 36 | ## Layout |
| 37 | |
| 38 | 1. **KPI cards** — Retention Rate, NRR, Churn Rate, Reactivation Rate. Current month value, month-over-month delta, 3-month trend sparkline. |
| 39 | 2. **Retention curve** — Line chart showing month-N retention by signup cohort. X-axis: months since signup (0-12). Y-axis: % retained. One line per cohort, highlight selected cohort. |
| 40 | 3. **NRR waterfall** — Stacked bar showing starting revenue, expansion, contraction, churn, and ending revenue for the selected period. |
| 41 | 4. **Churn reason breakdown** — Horizontal bar chart of top 10 cancellation reasons. Clicking a reason filters the detail table. |
| 42 | 5. **Customer detail table** — Filterable table: customer name, plan, MRR, tenure, health score, last login. Sortable. Export to CSV. |
| 43 | |
| 44 | ## Drill-Down Paths |
| 45 | |
| 46 | ``` |
| 47 | Retention KPI Card → Retention by Plan Tier (bar) → Cohort Curve for Selected Tier → Customer List → Individual Customer Record |
| 48 | Churn Rate Card → Churn by Reason (bar) → Customers with That Reason (table) → Customer Detail Page in CRM |
| 49 | ``` |
| 50 | |
| 51 | ## SQL Example — Monthly Retention Rate |
| 52 | |
| 53 | ```sql |
| 54 | WITH monthly AS ( |
| 55 | SELECT snapshot_month, |
| 56 | COUNT(*) FILTER (WHERE is_active) AS active_end, |
| 57 | COUNT(*) AS active_start |
| 58 | FROM analytics.customer_monthly_snapshot |
| 59 | WHERE snapshot_month BETWEEN :start_date AND :end_date |
| 60 | GROUP BY snapshot_month |
| 61 | ) |
| 62 | SELECT snapshot_month, |
| 63 | ROUND(active_end::numeric / active_start * 100, 2) AS retention_rate |
| 64 | FROM monthly |
| 65 | ORDER BY snapshot_month; |
| 66 | ``` |
| 67 | |
| 68 | ## Refresh Schedule |
| 69 | |
| 70 | | Data Source | Refresh Frequency | Method | SLA | Failure Handling | |
| 71 | |------------|-------------------|--------|-----|-----------------| |
| 72 | | `analytics.customer_monthly_snapshot` | Daily at 03:00 UTC | Incremental | Available by 03:30 UTC | Retry 3x, alert #data-ops on failure; stale-data badge on dashboard | |
| 73 | | `billing.invoices` | Every 4 hours | CDC stream | 15-minute lag | Lag counter shown in footer | |
| 74 | | `crm.customers` | Daily at 02:00 UTC | Full sync | Available by 02:45 UTC | Previous day's snapshot used; warning banner displayed | |
| 75 |