churn.fyi

From activity events to the churn.fyi CSV

A synthetic-only SQL Server / Azure SQL preparation guide. Run SQL in your own authorized environment, then open only aggregate counts in the browser calculator. All people and events here are invented.

Download the starter kit

These individual files are the complete public query-editor starter kit. No private repository access, package install, credentials, or Node.js is required. Save the SQL files as text; if your browser opens a file instead of downloading it, use Save As.

Try the calculator

Download the reference CSV and matching metadata, open the calculator, and import both. Read its three assertions before confirming them. Importing metadata does not confirm the assertions. Expect baseline 74%, current 67%, total −7 pp, audience mix −12 pp, and within-segment change +5 pp.

Execute the SQL

  1. In a scratch query session on an already authorized SQL Server or Azure SQL database, open core-seed.sql (or tiny-seed.sql).
  2. Append query.sql and execute both together on the same connection. The seed uses temporary tables only; another connection cannot see them. Use a fresh session for each seed.
  3. Export only the final result as UTF-8 CSV with headers and proper CSV quoting. Omit row numbers, SQL messages, and extra columns. Counts must be integer text without separators or percent signs.
  4. Compare against core-expected.csv (or expected.csv for the tiny seed). The exact header is comparison,segment,eligible,returned.
  5. Open the exported aggregate CSV with metadata.json in the calculator and verify the result.

The actual query and generated seeds have been tested on SQL Server 2019 Express LocalDB, including absent-segment behavior and rejection of incomplete observation, non-adjacent windows, blank identities, and empty comparisons. Azure connectivity, permissions, production schemas, and performance are not validated by those fixture tests. The public download files do not include the private repository's CI harness.

3. Define the measurement before counting

metadata.json supplies the authoritative example window boundaries; the seed generator uses those exact values when populating #Windows.

Comparison Base month, UTC Immediately following return month, UTC
baseline [2026-06-01, 2026-07-01) [2026-07-01, 2026-08-01)
current [2026-08-01, 2026-09-01) [2026-09-01, 2026-10-01)

Both return windows are complete through the exclusive watermark 2026-10-01. The two comparisons need not be consecutive; each return month must immediately follow its own base month, and the current base starts later than baseline.

The stable rules are:

  1. Identity: a canonical, case-sensitive person_id, one person rather than one device/session/event. IDs are non-null, nonblank, and consistently resolved upstream. The fixture uses explicit binary collation to avoid silently merging different IDs or segment labels under a database's default case-insensitive collation. Duplicate source event_id values fail the input primary key; repeated activity with distinct event IDs is allowed.
  2. Qualifying activity: activity_type = 'product_action' in both base and return windows. The heartbeat event never qualifies.
  3. Eligibility: at least one qualifying event in the complete base window. The query ranks base events and keeps one row per person.
  4. Segment: use the segment on that person's earliest qualifying base event, breaking identical timestamp ties by the unique event_id. Freeze that assignment for this comparison. This is a deliberate example rule, not a claim to know the segment at calendar-period start. Later base-event changes and return-event segments do not move the person. Return behavior never defines the segment.
  5. Missing segment: map null or space-only labels to a reserved Unknown group. Do not drop those people or silently distribute them. Reserve that label for this purpose and normalize labels upstream using one documented rule. The example trims ordinary spaces with LTRIM/RTRIM; it is not a general Unicode-label cleaning library.
  6. Returned: the eligible person has at least one qualifying event in the return window. EXISTS keeps duplicate returns from multiplying counts. A return-only person is not eligible and cannot enter the numerator. Non-returners stay in the denominator.
  7. Partition: one base assignment per eligible person makes the segments mutually exclusive and exhaustive within a comparison. The same person may legitimately be eligible in multiple comparisons.
  8. Paired rows: use the union of segments observed among eligible people across the two comparisons and explicitly output both rows. If a segment has no eligible people on one side, export 0,0; the calculator shows overall rates only. Do not fabricate a rate, silently omit its row, or include categories absent from both sides.

The watermark is an assertion about upstream completeness, not MAX(event_time) or today's date. A quiet final day does not prove data are missing, and a recent event does not prove older events finished loading. Resolve ingestion lag, late events, identity changes, and backfills before stating the watermark.

Timezone and precision

This SQL example intentionally supports complete calendar months in UTC only. Event timestamps and boundaries are normalized UTC values in datetime2(7); the type itself has no timezone or offset. Predicates are always >= start AND < end. Do not replace them with inclusive BETWEEN or subtract an arbitrary millisecond from an end date.

The JavaScript reference compares normalized seven-fractional-digit timestamp strings, preserving the one-100-nanosecond-tick boundary fixture rather than rounding through JavaScript Date milliseconds.

For a non-UTC analysis, choose and record an IANA timezone, resolve each local calendar boundary to its correct UTC instant using a vetted timezone database, and use those instants against normalized UTC events. DST can make adjacent calendar months/weeks unequal in elapsed hours; do not shift by a fixed UTC offset or assume every calendar week is 168 hours. SQL Server timezone identifiers and the calculator's IANA metadata are not interchangeable by assumption. Change and test the SQL window validation as well as the metadata. Simply changing timezone in JSON does not convert these example events or windows. Weekly adaptations must use complete Monday-to-Monday seven-calendar-day periods.

4. Audit the small fixture by hand

Person Baseline outcome Current outcome What it demonstrates
u1 Segment A, returned Not eligible First base event wins; later Segment B and duplicate return events do not change counts
u2 Segment A, returned Not eligible Events one datetime2(7) tick before both exclusive ends are included
u3 Unknown, returned Not eligible Missing base segment remains Unknown even when the return event says A
u4 Segment B, did not return Segment B, did not return Exactly August 1 is outside baseline return, but inside current base
u5 Not eligible Not eligible July return-only activity does not create June eligibility
u6 Not eligible Segment A, returned September 1 is included in current return; return segment B does not reassign A
u7 Not eligible Segment B, did not return Exactly October 1 is outside current return
u8 Not eligible Unknown, did not return Missing segment is kept in the denominator
u9 Not eligible Segment B, returned Same-timestamp base events choose lower event ID 17 (B), not 18 (A)
u10 Not eligible Not eligible September return-only activity is excluded from the eligible set
ignored Not eligible Not eligible Heartbeat is not qualifying activity

That gives baseline 3 / 4 = 75% and current 2 / 5 = 40%. The exact midpoint decomposition is −26⅔ pp mix plus −8⅓ pp within, totaling −35 pp. This tiny fixture is designed to expose counting mistakes, not mimic a realistic business population.

5. Work through the reference result

Segment Baseline eligible / returned Baseline weight / rate Current eligible / returned Current weight / rate
A 800 / 640 80% / 80% 400 / 340 40% / 85%
B 200 / 100 20% / 50% 600 / 330 60% / 55%

Overall return rates use people-weighted totals, not an unweighted average of segment rates:

For each segment, with eligible weight w and return rate r, the existing method is:

mix    = (w_current − w_baseline) × (r_current + r_baseline) / 2
within = (r_current − r_baseline) × (w_current + w_baseline) / 2

Both segments' rates improved, but the overall return rate fell as more eligible people were in the lower-return segment. These are descriptive contributions under the midpoint convention, not evidence that changing segment membership caused anything. Non-return in one window is not necessarily cancellation or permanent churn.

6. Adapt safely to your own source

Keep this as an offline analyst-preparation guide. Replace the synthetic input in your own authorized environment with a governed view or staging table that provides the same canonical identity, qualifying activity, UTC timestamp, unique tie-breaker, and historically correct segment. Avoid joining a current profile table to historical events: that can rewrite the past or leak return-period information into the base assignment. If your business rule is period-start segmentation instead, use a validated as-of snapshot/history join and update the metadata.

Before exporting:

The raw-event parser and SQL literal generator in fixture.mjs deliberately support only these trusted synthetic fixtures. They are not an ETL service, upload path, production SQL client, or raw-customer-data importer.

Reference semantics

Open the return-rate calculator →