Build a subscription churn dashboard

Northwind runs on recurring retainers, so the single number that matters most is churn — how many clients are leaving each month, and how that compares to the base still paying. The raw answer is buried in the Subscriptions sheet: one row per client, with a start date and an end date that is blank while the account is live. Reading churn off that by eye is slow and easy to get wrong.

This script rolls the whole sheet up into a twelve-month dashboard. For each of the last twelve months it counts the accounts that were active, the accounts that ended, and the churn rate between them, then writes a clean table you can chart or drop into a board update. It is built to run on a schedule, so the dashboard is always current without anyone touching it.

What you’ll need

• A Subscriptions sheet with a header row and one row per client, with columns named client, startDate, and endDate. Leave endDate blank for accounts that are still active.
• Dates stored as real dates, not text — type them into date-formatted cells so the comparisons work.
• A second spreadsheet (or a dedicated tab) to hold the dashboard output. The script clears and rewrites its first sheet on every run.

The script

// The spreadsheet that holds one row per subscription.
const SUBS_SHEET_ID = '1abcSubsSheetId';

// The spreadsheet the dashboard is written to. Its first sheet is
// cleared and rebuilt on every run.
const CHURN_SHEET_ID = '1abcChurnSheetId';

// How many months of history the dashboard covers.
const MONTHS_BACK = 12;

/**
 * Rolls the Subscriptions sheet up into a month-by-month churn
 * dashboard: active count, churned count, and churn rate.
 */
function buildChurnDashboard() {
  // 1. Read every subscription row as an object keyed by header.
  const subs = readSheet(SUBS_SHEET_ID);
  if (!subs.length) {
    Logger.log('No subscriptions found — nothing to roll up.');
    return;
  }

  // 2. Build the list of month-start dates we report on.
  const months = lastMonths(MONTHS_BACK);

  // 3. For each month, count who was active and who churned.
  const rows = months.map((monthStart) => {
    // The first day of the following month — the exclusive upper bound.
    const monthEnd = new Date(monthStart);
    monthEnd.setMonth(monthEnd.getMonth() + 1);

    // Active: started before the month ended and had not ended by then.
    const active = subs.filter((s) =>
      s.startDate < monthEnd && (!s.endDate || s.endDate >= monthEnd)
    ).length;

    // Churned: the end date falls inside this month.
    const churned = subs.filter((s) =>
      s.endDate && s.endDate >= monthStart && s.endDate < monthEnd
    ).length;

    // Churn rate as a percentage of the active base.
    const churnPct = active === 0 ? 0 : (churned / active) * 100;

    return [
      Utilities.formatDate(monthStart, 'GMT', 'MMM yy'),
      active,
      churned,
      churnPct,
    ];
  });

  // 4. Clear the dashboard sheet and write the fresh table.
  const sheet = SpreadsheetApp.openById(CHURN_SHEET_ID).getSheets()[0];
  sheet.clear();
  sheet.getRange(1, 1, 1, 4)
    .setValues([['Month', 'Active', 'Churned', 'Churn %']]);
  sheet.getRange(2, 1, rows.length, 4).setValues(rows);
  Logger.log('Dashboard rebuilt for ' + rows.length + ' months.');
}

/**
 * Returns the first-of-month date for the last `count` months,
 * oldest first, including the current month.
 */
function lastMonths(count) {
  const d = new Date();
  d.setDate(1);
  return Array.from({ length: count }, (_, i) => {
    const m = new Date(d);
    m.setMonth(m.getMonth() - (count - 1 - i));
    return m;
  });
}

/**
 * Reads the first sheet of a spreadsheet and returns its rows as
 * objects keyed by the header row.
 */
function readSheet(id) {
  const [header, ...rows] = SpreadsheetApp.openById(id)
    .getSheets()[0]
    .getDataRange()
    .getValues();
  return rows.map((r) =>
    Object.fromEntries(header.map((key, i) => [key, r[i]]))
  );
}

How it works

1. buildChurnDashboard reads the Subscriptions sheet through readSheet, which turns every row into an object keyed by the header — so s.startDate and s.endDate work directly.
2. If the sheet is empty, it logs a message and stops rather than writing an empty dashboard.
3. lastMonths builds twelve first-of-month dates, oldest first, ending with the current month.
4. For each month it works out monthEnd, the first day of the next month, and uses it as an exclusive upper bound. Active accounts started before the month ended and had not ended by then. Churned accounts have an end date that lands inside the month.
5. The churn rate is churned divided by the active base, as a percentage — guarded so an empty month does not divide by zero.
6. It clears the dashboard sheet and writes the header plus one row per month, so the table always reflects the latest data.

Example run

Say the Subscriptions sheet holds rows like these:

client	startDate	endDate
Harlow & Co	2024-02-10	2025-03-04
Pinebrook	2023-11-01
Drayton Mills	2024-06-15	2025-03-22
Castlewood	2025-01-08

After a run, the dashboard sheet shows the rolling twelve months:

Month	Active	Churned	Churn %
Apr 24	38	1	2.63
…	…	…	…
Feb 25	44	0	0
Mar 25	43	2	4.65

The churn spike in March is now a single glance instead of a manual count.

Trigger it

Run the dashboard on a monthly schedule so it refreshes itself:

1. In the Apps Script editor, open Triggers (the clock icon).
2. Add a trigger for buildChurnDashboard, time-driven, month timer, on day 1, early in the morning.

Day 1 means the dashboard always shows last month fully closed out before anyone looks at it.

Watch out for

• Reactivated accounts only have one start and end date in this model. A client who leaves and comes back needs a second row, or the gap is invisible. If reactivation matters, give each subscription term its own row.
• Counts depend on real date values. A date typed as text will sort and compare as a string and quietly skew every month — format those cells as dates.
• The active count is taken at month end. A client who joined and left within the same month counts as churned but never as active, which can push the churn percentage above what feels intuitive.
• The dashboard sheet is cleared on every run. Do not keep notes or extra columns on it — put them on a separate tab.