SQL queries to analyse canceled subscriptions managed with EDD Recurring Payments

canceled-subscriptions.sql

# Basic queries to analyse canceled subscriptions using EDD Recurring Payments pre EDD 3.0
# Use EXPLAIN if you have large stores since the "notes" column is not indexed and queries can take some time
# Some of these reports might be worth storing as Views and look at the graphs PHPMyAdmin can also create from them

# List subscription cancelation with creation and cancelation date, as well as the user who canceled
# good as a basis for further queries

SELECT ID,
  created,
  REGEXP_SUBSTR( REGEXP_SUBSTR( notes, '.* Status changed from active to cancelled .*' ), '^[a-z]+ [0-9]+, [0-9]+' ) AS 'date',
  REGEXP_REPLACE( REGEXP_SUBSTR( notes, 'by (.*)' ), 'by ', '' ) AS 'user'
FROM  `wp_edd_subscriptions` 
WHERE  `notes` LIKE  '%Status changed from active to cancelled by%'

# Same as above, but with proper date format for the cancelation date

SELECT ID,
  created,
  STR_TO_DATE( REGEXP_SUBSTR( REGEXP_SUBSTR( notes, '.* Status changed from active to cancelled .*' ), '^[a-z]+ [0-9]+, [0-9]+' ), '%M %e, %Y' ) AS 'canceled',
  REGEXP_REPLACE( REGEXP_SUBSTR( notes, 'by (.*)' ), 'by ', '' ) AS 'user'
FROM  `wp_edd_subscriptions` 
WHERE  `notes` LIKE  '%Status changed from active to cancelled by%'


# Show subscriptions based on the time between creation and cancelation in days
# good to find accounts that cancel early and ask them why,
# or those who have their subscription for the longest period – and ask why they cancel now

SELECT ID,
  created,
  STR_TO_DATE( REGEXP_SUBSTR( REGEXP_SUBSTR( notes, '.* Status changed from active to cancelled .*' ), '^[a-z]+ [0-9]+, [0-9]+' ), '%M %e, %Y' ) AS canceled,
  REGEXP_REPLACE( REGEXP_SUBSTR( notes, 'by (.*)' ), 'by ', '' ) AS user,
  ( SELECT( TIMESTAMPDIFF( DAY,created, canceled ) ) ) as daystocancel
FROM  `wp_edd_subscriptions` 
WHERE  `notes` LIKE  '%Status changed from active to cancelled by%'
ORDER BY `daystocancel`


# After how many days are your users normally canceling?
# expects spikes at the beginning of a subscription, when you send out renewal reminders, and after subscriptions renew
# e.g., day 0, day 358 (7-day renewal reminder), or 365
# good to visualize in a graph

SELECT
  COUNT( * ) as count,
  ( SELECT( TIMESTAMPDIFF( DAY, created, STR_TO_DATE( REGEXP_SUBSTR( REGEXP_SUBSTR( notes, '.* Status changed from active to cancelled .*' ), '^[a-z]+ [0-9]+, [0-9]+' ), '%M %e, %Y' ) ) ) ) as daystocancel
FROM  `wp_edd_subscriptions` 
WHERE  `notes` LIKE  '%Status changed from active to cancelled by%'
GROUP BY daystocancel
ORDER BY `daystocancel`

# On which specific days are most licenses canceled?
# if users cancel around Black Friday then you should be fine
# if other days are in the top 10 then you might want to ask yourself if you did something that triggered this

SELECT
  COUNT( * ) AS count,
  STR_TO_DATE( REGEXP_SUBSTR( REGEXP_SUBSTR( notes, '.* Status changed from active to cancelled .*' ), '^[a-z]+ [0-9]+, [0-9]+' ), '%M %e, %Y' ) AS 'canceled'
FROM  `wp_edd_subscriptions` 
WHERE  `notes` LIKE  '%Status changed from active to cancelled by%'
GROUP BY canceled
ORDER BY count DESC

# Who canceled the most?
# the result could tell you where most subscriptions are canceled: by your support staff,
# by the gateway, which includes also programmatically canceled subscriptions when upgrading to a higher package,
# or directly by the customer (through their account)

SELECT
  COUNT( * ) AS count,
  REGEXP_REPLACE( REGEXP_SUBSTR( notes, 'by (.*)' ), 'by ', '' ) AS user
FROM  `wp_edd_subscriptions` 
WHERE  `notes` LIKE  '%Status changed from active to cancelled by%'
GROUP BY `user`
ORDER BY `count` DESC
LIMIT 0, 100;

# Who canceled and still stayed a customer?
# the query below can be used as a basis to find canceled subscriptions from customers who still had another subscription
# or purchased one afterwards
# this is especially useful to see that some or even many cancelling customers still have other products, e.g., because they upgraded

SELECT subs1.ID, subs2.ID, subs1.created, subs2.created, subs1.customer_id
FROM  `wp_edd_subscriptions` AS subs1
LEFT JOIN `wp_edd_subscriptions` AS subs2
  ON subs1.customer_id = subs2.customer_id
  # AND subs1.product_id = subs2.product_id /* same product ID indicates an upgrade */
WHERE  subs1.status = 'cancelled'
 AND subs1.created < subs2.created
 # AND subs1.created > '2021-01-01' /* set date limit */
LIMIT 0 , 30

# ANALYSE SUBSCRIPTION RETENTION BY YEAR

# gather number of subscriptions grouped by year
SELECT COUNT(*), YEAR(created)
FROM  `wp_edd_subscriptions`
GROUP BY YEAR(created)

# Number of years to expiry of a subscription by year of when they started
SELECT YEAR(created) as year, TIMESTAMPDIFF(YEAR, created, expiration) as years_to_expiry, COUNT(*)
FROM  `wp_edd_subscriptions`
WHERE DATEDIFF( expiration, created ) > 0 /* shouldn’t be needed if table doesn’t have any errors */
GROUP BY year, years_to_expiry

# Number of renewals with percentage compared to all subscriptions purchased in a given year
# I haven’t found a simple solution to calculate the renewal rate from this, but one can make an export into CSV and calculate it in a spreadsheet
# the last `years to expiry` value for a given year is often very high since it gathers all active subscriptions
# if `years to expiry` is 0 then this means it was a renewal payment, which expires with the original purchase date
SELECT
  YEAR(created) as `year of first purchase`,
  TIMESTAMPDIFF(YEAR, created, expiration) as `years to expiry`,
  COUNT(*) as subscriptions,
  ( SELECT COUNT(*) FROM `wp_edd_subscriptions` as sub2 WHERE YEAR(wp_edd_subscriptions.created) = YEAR(sub2.created) GROUP BY YEAR(sub2.created) ) as `total subscriptions`
FROM  `wp_edd_subscriptions`
WHERE DATEDIFF( expiration, created ) > 0 /* shouldn’t be needed if table doesn’t have any errors */
GROUP BY `year of first purchase`, `years to expiry`

# get all subscriptions that started in a given year and ran x times
# for control purposes
SELECT *,
  YEAR(created) as `year of first purchase`,
  TIMESTAMPDIFF(YEAR, created, expiration) as `years to expiry`
FROM  `wp_edd_subscriptions`
WHERE YEAR(created) = 2017
AND TIMESTAMPDIFF(YEAR, created, expiration) = 0;

### HOW MANY CUSTOMERS CANCEL WITHIN THE FIRST 30 DAYS

# BY YEAR
SELECT
  YEAR(created) as `year of first purchase`,
  COUNT( * ) as `canceled subscriptions`,
  `total subscriptions`
FROM  (SELECT *,
       STR_TO_DATE( REGEXP_SUBSTR( REGEXP_SUBSTR( notes, '.* Status changed from active to cancelled .*' ), '^[a-z]+ [0-9]+, [0-9]+' ), '%M %e, %Y' ) AS 'canceldate',
       ( SELECT COUNT(*) FROM `wp_edd_subscriptions` as sub2 WHERE YEAR(wp_edd_subscriptions.created) = YEAR(sub2.created) GROUP BY YEAR(sub2.created) ) as `total subscriptions`
       FROM `wp_edd_subscriptions`
       WHERE  `notes` LIKE  '%Status changed from active to cancelled by%' ) as subs
WHERE TIMESTAMPDIFF( DAY, created, subs.canceldate ) < 31
GROUP BY `year of first purchase`

# by month
# compared to subscriptions started in that month
# export to CSV to calculate early cancelations by date of starting a subscription
SELECT
  YEAR(created) as `year of first purchase`,
  MONTH(created) as `month of first purchase`,
  COUNT( * ) as `canceled subscriptions`,
  `total subscriptions`
FROM  (SELECT *,
       STR_TO_DATE( REGEXP_SUBSTR( REGEXP_SUBSTR( notes, '.* Status changed from active to cancelled .*' ), '^[a-z]+ [0-9]+, [0-9]+' ), '%M %e, %Y' ) AS 'canceldate',
       ( SELECT COUNT(*) FROM `wp_edd_subscriptions` as sub2 WHERE YEAR(wp_edd_subscriptions.created) = YEAR(sub2.created) AND MONTH(wp_edd_subscriptions.created) = MONTH(sub2.created) GROUP BY YEAR(sub2.created), MONTH(sub2.created) ) as `total subscriptions`
       FROM `wp_edd_subscriptions`
       WHERE  `notes` LIKE  '%Status changed from active to cancelled by%' ) as subs
WHERE TIMESTAMPDIFF( DAY, created, subs.canceldate ) < 31
GROUP BY `year of first purchase`, `month of first purchase`

# List domain activations by license state and month.
# Helps to see how many sites are really and actively using the plugins.
SELECT
  YEAR(date_created) as `year of purchase`,
  MONTH(date_created) as `month of purchase`,
  status,
  COUNT( * ) AS activations
FROM  `wp_edd_license_activations`
LEFT JOIN `wp_edd_licenses` AS licenses ON licenses.id = license_id
WHERE  `activated` = '1'
GROUP BY `year of purchase`, `month of purchase`, status
LIMIT 0, 100;