How do you calculate Monthly churn from Google Analytics?

Question

Since cohort analysis is in beta, It does not show the monthly retention for more than 3 months from today's date.(Since for a given month 100-retention% is that month's churn%) I searched for a simple way to calculate churn but there seems to be NO proper source that mentions the calculation of churn using Google Analytics except for this Quora article (https://www.quora.com/How-do-I-measure-churn-rate-in-Google-analytics) which I tried implementing and ended up with negative churn values.()

May-19  Jun-19  Jul-19  Aug-19  Sep-19  Oct-19  Nov-19  Dec-19  Jan-20  Feb-20  Mar-20
99616   96765   97627   96141   94983   86698   78381   73789   65822   67473   103818 users
NA      2851    862     1486    1158    8285    8317    4592    7967    1651    36345  monthly change 
34263   31124   33676   33381   33889   20531   14345   10840   8811    13535   31381  new users 
NA      28273   32814   31895   32731   12246   6028    6248    844    11884    -4964  churn

I understand I am doing something wrong , but cant understand what since I am quite new to Web analytics. My ultimate goal is to calculate the monthly churn rates for each month for the 12 month time frame using any and every metric GA can provide.

Answer 1

The problem with churn in Google Analytics is, that it does not "forget" a user after a certain period (often month). So if you try to combine New Users vs. Users, you can have 100 New Users and 500 total Users in one month and another month 0 New Users but 600 total Users. Because those Users could have visited your site 2 years ago.

Another effect is, that you can have 2 months in a row 500 total Users and no New User. This would be a churn of 0 %. But 50 users in that month could have churned and 50 one-year-inactive users might have come back again. Resulting in 10 % churn. And you can not detect this.

This fact makes it an unreliable source of data. It could be useful in a way, but you will never get the real churn / churn rate out of Google Analytics in their report.

Retention in Cohort analysis only works with new users in the period you are analyzing. Therefore if you see a retention of 5 % from last month, it means that only 5 of 100 new visitors in that month came back again during that month. Is it 95 % churn? No. It does not take into account that you still have 500 loyal customers coming to your site every day.

The calculation and negative numbers

If you still want to look at GA data to come up with churn rate, there are some fixes to your calculation. The monthly change is often negative. In your calculations (3rd row), you have only absolute values. Monthly CHANGE is current Users - last month Users.

The churn is calculated as New Users - CHANGE. If CHANGE is negative, as in the first column, you end up with 31124 -- 2851 = 31124 + 2851 = 33975. In last column, number of churned users is -4964. This value can be negative, because of the effect I desrcibed in first paragraph, where users from previous months are not counted as New Users. They churned in previous months but now came back.

And finally the kind-of-churn rate is calculated as current month CHURN / previous month Users and then multiply it with 100 to have nice percentages. Here you have corrected calculations:

MONTH   May-19  Jun-19  Jul-19  Aug-19  Sep-19  Oct-19  Nov-19  Dec-19  Jan-20  Feb-20  Mar-20
USERS   99616   96765   97627   96141   94983   86698   78381   73789   65822   67473   103818
CHANGE  NA      -2851   862     -1486   -1158   -8285   -8317   -4592   -7967   1651    36345
NEW     34263   31124   33676   33381   33889   20531   14345   10840   8811    13535   31381
CHURN   NA      33975   32814   34867   35047   28816   22662   15432   16778   11884   -4964
RATE    NA      34,11   33,91   35,71   36,45   30,34   26,14   19,69   22,74   18,05   -7,36

To calculate real churn, follow this article but you have to use Google Analytics API to download the data and then do a nontrivial calculations in SQL database.