webzunft · October 4, 2022 13:15 · Jul 29, 2022 · Jan 4, 2022 · Jan 4, 2022 · Jan 4, 2022
diff --git a/canceled-subscriptions.sql b/canceled-subscriptions.sql
@@ -154,4 +154,17 @@ FROM  (SELECT *,
        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`
+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;
diff --git a/canceled-subscriptions.sql b/canceled-subscriptions.sql
@@ -123,4 +123,35 @@ SELECT *,
   TIMESTAMPDIFF(YEAR, created, expiration) as `years to expiry`
 FROM  `wp_edd_subscriptions`
 WHERE YEAR(created) = 2017
-AND TIMESTAMPDIFF(YEAR, created, expiration) = 0;
+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`
diff --git a/canceled-subscriptions.sql b/canceled-subscriptions.sql
@@ -90,12 +90,7 @@ WHERE  subs1.status = 'cancelled'
  # AND subs1.created > '2021-01-01' /* set date limit */
 LIMIT 0 , 30
 
-# show the renewal rate for users grouped by the year of their first purchase
-# - gather all subscriptions purchased
-# - count renewed or canceled – or times it was renewed
-# - calculate the rate between all subscriptions and canceled / active
-# - group by year
-# - group by year and month
+# ANALYSE SUBSCRIPTION RETENTION BY YEAR
 
 # gather number of subscriptions grouped by year
 SELECT COUNT(*), YEAR(created)

diff --git a/canceled-subscriptions.sql b/canceled-subscriptions.sql
@@ -82,10 +82,50 @@ LIMIT 0, 100;
 
 SELECT subs1.ID, subs2.ID, subs1.created, subs2.created, subs1.customer_id
 FROM  `wp_edd_subscriptions` AS subs1
-LEFT JOIN `advads_edd_subscriptions` AS subs2
+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
+LIMIT 0 , 30
+
+# show the renewal rate for users grouped by the year of their first purchase
+# - gather all subscriptions purchased
+# - count renewed or canceled – or times it was renewed
+# - calculate the rate between all subscriptions and canceled / active
+# - group by year
+# - group by year and month
+
+# 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;
diff --git a/canceled-subscriptions.sql b/canceled-subscriptions.sql
@@ -87,5 +87,5 @@ LEFT JOIN `advads_edd_subscriptions` AS subs2
   # AND subs1.product_id = subs2.product_id /* same product ID indicates an upgrade */
 WHERE  subs1.status = 'cancelled'
  AND subs1.created < subs2.created
- # subs1.created > '2021-01-01' /* set date limit */
+ # AND subs1.created > '2021-01-01' /* set date limit */
 LIMIT 0 , 30
diff --git a/canceled-subscriptions.sql b/canceled-subscriptions.sql
@@ -73,4 +73,19 @@ FROM  `wp_edd_subscriptions`
 WHERE  `notes` LIKE  '%Status changed from active to cancelled by%'
 GROUP BY `user`
 ORDER BY `count` DESC
-LIMIT 0, 100;
+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 `advads_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
+ # subs1.created > '2021-01-01' /* set date limit */
+LIMIT 0 , 30
diff --git a/canceled-subscriptions.sql b/canceled-subscriptions.sql
@@ -0,0 +1,76 @@
+# 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;
No results found