Churn analysis in Tableau: reconcile customer losses

Churn analysis in Tableau uses a fixed starting customer base, dated departures, and a separate measure of lost recurring revenue. First prove that customer counts and revenue reconcile to the closing export; then use the report to find where losses concentrate. A cancellation chart alone cannot provide that proof.

Start with a customer-period file

Take the customer list and recurring billing balances at July 1, 2026, the equivalent closing balances at August 1, 2026, and cancellations effective during July. Build one row per customer for that period. Keep the original account identifier so every result can be checked against the source records.

Use the effective service end date rather than the date a representative entered a cancellation request. Define churn here as an opening customer who is no longer active at the closing boundary. A temporary stop followed by a return within the month belongs in a separate event review.

The churn glossary explains the general measure. This workflow concentrates on proving the population used in the report.

For the example, positive monthly recurring revenue, or MRR, identifies an active paid customer. There are no paused accounts, free accounts, reactivations, or foreign currencies. If your business has those cases, add explicit membership fields instead of inferring status from revenue.

Work the same rows as customers and revenue

A fictional US subscription-services company has these billing records. A blank cancellation date means no effective cancellation in the period. Dollar columns contain monthly recurring amounts, rather than the total value of annual invoices.

Customers A through E form the opening base. B and E leave, while F is new. The customer calculation is therefore 2 / 5 = 40.0%. The canceled MRR is 800 + 200 = $1,000, so full-cancellation revenue churn is 1,000 / 3,000 = 33.3%.

Customer C also contracts by $100. Including that contraction gives gross revenue churn of (1,000 + 100) / 3,000 = 36.7%. Percentages are rounded to one decimal place; retain unrounded values in the workbook.

Prepare the spreadsheet checks

Put the table headers in row 1, with customers in rows 2 through 7. Columns A through F follow the table order. Keep the displayed Total row outside the imported customer table so its amounts cannot be counted twice. Enter July 1, 2026, in P1 and August 1, 2026, in P2 as actual spreadsheet dates.

Create OpeningFlag in G, LostFlag in H, NewFlag in I, and ClosingFlag in J. These Excel formulas go in row 2 and fill down:

G2 = --(E2>0)
H2 = --AND(G2=1,F2=0,D2<>"",D2>=$P$1,D2<$P$2)
I2 = --AND(E2=0,F2>0,C2>=$P$1,C2<$P$2)
J2 = --(F2>0)

The first and last flags must agree with the opening and closing customer registers. An opening account with no closing revenue and no cancellation date is an exception to investigate, rather than an automatic retained customer.

Create LostMRR in K and ContractionMRR in L:

K2 = E2*H2
L2 = IF(AND(G2=1,H2=0),MAX(0,E2-F2),0)

LostMRR uses the opening recurring amount, so it remains comparable with its denominator. Full cancellations are excluded from ContractionMRR to prevent double counting. The spreadsheet control rates are:

=SUM(H2:H7)/SUM(G2:G7)
=SUM(K2:K7)/SUM(E2:E7)
=(SUM(K2:K7)+SUM(L2:L7))/SUM(E2:E7)

Build the Tableau view from the checked file

In Tableau Desktop, choose Microsoft Excel under Connect, open the workbook, and select the prepared worksheet or table. Tableau's Excel connection instructions document this route; CSV files use the Text file connection instead.

Create a worksheet showing Opening segment and the sums of OpeningFlag, LostFlag, Opening MRR, and LostMRR. Include the underlying amounts beside the percentages so a reviewer can see which population each rate represents.

Choose Analysis > Create Calculated Field. Create Customer churn as SUM([LostFlag])/SUM([OpeningFlag]) and Full-cancellation revenue churn as SUM([LostMRR])/SUM([Opening MRR]). Tableau documents this aggregate-ratio pattern in its calculated field guide. Format the results as percentages, and leave a zero-denominator group undefined.

This file contains July only. If combining periods, add a reporting-period field on every customer row and filter that field. Filtering the source rows to cancellation dates would remove the retained customers that supply the denominator. Segment controls should use each customer's opening segment so a midmonth reassignment does not move the opening base after the fact.

If an existing workbook uses FIXED calculations, review their filter behavior explicitly. Tableau documents that FIXED calculations precede ordinary dimension filters, which can leave a denominator unchanged when the visible group changes. Test the intended scope rather than assuming every field responds alike.

Covirage is not affiliated with Tableau or Microsoft.

Prove the closing position

The closing customers are A, C, D, and F. The closing MRR equals the August 1 billing export. This is a recurring-revenue reconciliation, not a reconciliation to cash receipts or recognized accounting revenue.

Services opens with three customers and $2,000 of MRR; none leave. Field opens with two customers and $1,000 of MRR; both leave. The Field closing balance of $300 comes entirely from F, so it does not repair retention of the opening group.

Use churn rate analysis for the subsequent diagnosis and the lost customer review template to investigate individual departures. For the equivalent report in another application, see churn analysis in Power BI.

Where it goes wrong

• Counting contracts as customers. Combine related subscriptions under the agreed customer identifier before setting loss flags.
• Using request dates. An early cancellation request can precede the actual loss of service and recurring revenue.
• Filtering to losses. A cancellation-only worksheet drops retained customers and destroys the starting denominator.
• Offsetting churn with acquisition. Newly won revenue belongs in the closing bridge, outside retention of the opening base.
• Treating missing amounts as zero. A missing closing billing record needs investigation before it becomes a departure.

Check your own churn file in Covirage

Bring the prepared customer-period file and a sample of your own opening and closing billing exports to Covirage. Covirage maps the columns; its tools compute and check the customer and revenue movements, and the external AI model explains the results with every figure traced to its rows. Create a free Covirage account and bring a sample of your own data.

See Covirage for renewal monitoring for the business context.