Preparing Data for Analysis

When analyzing passive churn performance, it’s critical to first organize your data into the right format: Daily Cohorts.

With this daily view, it’s possible to exclude necessary days, identify natural variance trends, and to aggregate into a variety of metrics covering different time periods.

You’ll also be able to avoid common mistakes:

• Attributing natural variance to recent dunning changes
• Missing a change in one of the four outcomes, like a spike in cancellations
• Contaminating data by counting days that are still In Progress

Example Spreadsheet

• Each row represents a date, with the number of Failed Payments on each date in Column B.
• Columns C through G should always add up to Column B. They are the outcomes that take place during your failed payment recovery window. This is typically 30 days following the date in each row.
• Column D, for example, isn’t showing the number of Card Updates on a given date. It’s showing the number of customers who had a failed payment on that date and went on to be recovered via Card Update.

Example Calculations

As you’ll see below, we’ve excluded August 18th from analysis since recovery efforts are still in-progress for that date.

Recovery Rate for a Single Day:

Excel/Google Sheets Formula:
=(D5+E5)/B5

Calculation:
(# Card Updates + # Successful Retries)
÷ # Failed Payments on August 15th

Result:
322 + 217
÷ 746
= 72.25% Recovery Rate for August 15th

Recovery Rate for a Date Range:

Excel/Google Sheets Formula:
=SUM(D3:E5)/SUM(B3:B5)

Calculation:
(# Card Updates + # Successful Retries)
÷ # Failed Payments between August 15th and August 17th

Result:
(305 + 304 + 322 + 290 + 219 + 217)
÷ (805 + 788 + 746)
= 70.84% Recovery Rate for August 15th - 17th

Cancellation Rate for a Date Range:

This is where this data format starts to really come in handy.

You’re not limited to recovery rate alone. You can analyze the success of retries or card updates alone over time, or even the percent of customers that cancel following a failed payment. This adds depth to your reporting, and can highlight issues in your process, like a sub-optimal cadence of customer outreach, deliverability issues, or ineffective use of retry logic.

Excel/Google Sheets Formula:
=SUM(F3:F5)/SUM(B3:B5)

Calculation:
# Cancellations
÷ # Failed Payments between August 15th and August 17th

Result:
(85 + 117 + 92)
÷ (805 + 788 + 746)
= 12.57% Cancellation Rate for August 15th - 17th

Next Up: Understand the 4 Outcomes

Once your data is formatted in daily cohorts, with the 4 outcomes broken out, you’re ready to learn more about what each outcome represents, and how they fit into passive churn analysis.

Learn more about the 4 outcomes.