Payment Recovery dashboard Payment Recovery dashboard

Payment Recovery dashboard

Tyler Yannes Tyler Yannes

 

Article Outline:

  • Dashboard Overview
  • Dunning Metrics
  • Billing Attempt Metrics
  • Helpful Resources

Dashboard Overview

  • This dashboard covers dunning, the recovery process a subscription enters when they have a failed payment
  • Use this dashboard to understand your risk of passive churn, defined as subscriptions that were cancelled because of failed payments and not actively by the subscriber

Data questions this dashboard answers

  • Which subscriptions entered dunning by date?
  • What happened to those subscriptions that entered dunning (ie. what’s their current status)?
  • What are the reasons why a subscription enters dunning?
  • What are the total amount of failed and successful billing attempts?

Things to keep in mind:

1. Multiple products can be grouped together under 1 subscription id if they are purchase together at checkout or migrated from another platform and have the same shipping frequency
2. A subscriber can have multiple subscriptions; this most often occurs when migrated from another platform OR if a subscriber checks out again with a new subscription
3. The number of failed billing attempts per dunning period can be changed in your store's dunning settings
4. Failed attempts could exceed successful attempts for some stores with high dunning rates paired with high dunning retry attempts

Dunning

screenshot-2023-05-31-at-10-12-49-pm.png

Metrics:

Entered Dunning: The count of all unique subscriptions that entered dunning with at least 1 failed billing attempt in the date range selected.

Dunning Recovery %: The % of subscriptions that had a failed billing attempt in the date range selected and now has an active subscription status.

Dunning Recovered Revenue: The estimated revenue recovered from subscriptions that were recovered after failing at least 1 failed billing attempt in the date range selected. This revenue total excludes taxes, shipping, and discounts.

Passively Cancelled: The count of subscriptions that were cancelled after reaching their max number of failed billing attempts in the date range selected.

Lost Passively %: The % of subscriptions that entered dunning and were cancelled after reaching their max number of failed billing attempts in the date range selected.

Passively Lost Revenue: The estimated revenue lost from subscriptions that were cancelled after reaching their max number of failed billing attempts in the date range selected. This revenue total excludes taxes, shipping, and discounts.

Actively Cancelled: The count of subscriptions that entered dunning and were cancelled manually by the subscriber after at least 1 failed billing attempt in the date range selected.

Actively Lost Revenue: The estimated revenue lost from subscriptions that entered dunning and were cancelled manually by the subscriber before reaching their max number of failed billing attempts in the date range selected. This revenue total excludes taxes, shipping, and discounts.

Lost Actively %: The % of subscriptions that entered dunning and were cancelled manually by the subscriber before reaching their max number of failed billing attempts in the date range selected.

Still in Dunning: The count of all unique subscriptions that are still in the dunning process with at least 1 failed billing attempt in the date range selected and have yet to reach their max failed billing attempts.

Remaining %: The % of subscriptions that are still in the dunning process compared to all subscriptions that entered dunning with at least 1 failed billing attempt in the date range selected and have yet to reach their max failed billing attempts.

Pending Revenue: The estimated revenue lost from subscriptions that are still in the dunning process with at least 1 failed billing attempt in the date range selected and have yet to reach their max failed billing attempts. This revenue total excludes taxes, shipping, and discounts.

How to Use the Export
1. Each row is a product subscription line (product included in a subscription) that is in dunning in the date range selected
2. subscriptionID is how we group together product subscription lines into each subscription; use the unique count of subscriptionID to find the number of subscriptions
3. subscriptionLatestStatus is used to identify the subscription's current status
4. subscriptionLatestStatusContext is paired with the subscriptionLatestStatus metric to define whether a cancelled subscription was done passively (max attempts reached) or actively by the subscriber
5. Use subscriptionLineRevenue to calculate the revenue; to calculate per status, use the subscriptionLatestStatus as a filter
6. Here are the Status and Status Context definitions per metric:
- Recovered Subs: subscriptionLatestStatus= Active
- Passively Cancelled Subs: subscriptionLatestStatus= Cancelled; subscriptionLatestStatusContext= Churned
- Actively Cancelled Subs: subscriptionLatestStatus= Cancelled; subscriptionLatestStatusContext= Dunning
- Still in Dunning Subs: subscriptionLatestStatus= Failed

Dunning Subscriptions Graph

screenshot-2023-05-29-at-5-54-45-pm-1.png

Graph Description:

  • This graph provides daily metrics on subscriptions that entered dunning on a specific date; a subscription will only enter dunning once in the date range
  • The different colors indicate the current status of the subscription and thus whether it was recovered, actively cancelled, passively cancelled, or is still in the dunning process
  • The data in this graph can be replicated with the dunning export found above the dunning metrics
  • Hovering over a daily column provides the value for that day

Billing Attempt Failure Reasons Graph

screenshot-2023-05-31-at-10-13-45-pm.png

Graph Description:

  • This graph provides the sum total of Error Messages for all failed billing attempts in the date range
  • Both Error Code and Error Messages are included per subscription line in the Billing Attempts export (see more below)
  • The number of failure reasons per error message are per failed attempt per subscription; subscriptions will typically have multiple failed attempts and almost always the error reason stays the same for each failed attempt in that dunning cycle

Billing Attempts

screenshot-2023-06-01-at-5-41-19-am (1).png

Metrics:

Successful Attempts: The total number of successful billing attempts for recurring orders in the date range selected. This total does not include checkout billing attempts.

Successful % of Total: The percentage of this scorecard's metric out of total metric.

Successful Avg Per Day: The total number metric divided by the number of days in the date range filter.

Failed Attempts: The count of failed billing attempts for recurring orders in the date range selected compared to all billing attempts. This total does not include checkout billing attempts.

Failed % of Total: The percentage of this scorecard's metric out of total metric.

Failed Avg Per Day: The total number metric divided by the number of days in the date range filter.

How to Use the Export
1. Each row is a product subscription line (product included in a subscription) that is in dunning in the date range selected
2. subscriptionID is how we group together product subscription lines into each subscription; use the unique count of subscriptionID to find the number of subscriptions
3. billingAttemptErrorCode is used to identify the general category that caused the payment to fail
4. billingAttemptErrorMessage is used to provide a more specific reason that caused the payment to fail
5. Error Code and Error Messages are provided by Shopify Payments and are not defined by Skio
6. billingAttemptCreatedAt is the date of each billing attempt
7. To isolate failed payments, filter out blank billingAttemptErrorCode or billingAttemptErrorMessage since both must have a non-null value to be a failed billing attempt

Successful vs Failed Charge Attempts Graph

screenshot-2023-05-29-at-5-55-42-pm-1.png

Graph Description:

  • This graph provides daily metrics on successful vs failed billing attempts
  • The data in this graph can be replicated with the dunning export found above the dunning metrics
  • Hovering over a daily column provides the value for that day

Helpful Resources

 

Add comment

Please sign in to leave a comment.