How to create a cohort analysis in Google Sheets – part 2

Welcome back to the second part of the tutorial of creating a cohort analysis in Google Sheets. In the previous chapter we’ve seen what kind of data we need to input and started doing the calculations. 

I’ve filmed a video tutorial about what we’re going to do today, so check it out:

Pivot LTV tab

Basically, that is how the table on this tab looks like. 

The first column is the first purchase month. In fact, it is our cohort. Here I use UNIQUE formula that is:

=UNIQUE(‘1_1_Cohort_work_LTV’!G2:G)

This formula picks up unique data from the G column in the tab Cohort work LTV. 

Then we need to find how many new users we got each month using the formula:

=if(A2=””,””,COUNTUNIQUEIFS(‘1_1_Cohort_work_LTV’!C:C,’1_1_Cohort_work_LTV’!G:G,A2))

The main part of the formula is COUTUNIQUEIFS, and the beginning of the formula, as you may already know, is for avoiding errors if the data is missing. Actually, the formula means that if the sale month is the same as the first purchase month, count user IDs. 

The next step we do is finding a spend. To do this I’ve used the formula:

=ARRAYFORMULA( if(A2:A=””,””, sumif(‘0_input’!$G:$G,A2:A,’0_input’!$K:$K)))

In other words, we wrote here, if the month in the spend column is the same as in the First purchase month, we just summarize the total spend. 

Next thing we need to calculate is the revenue, and we find it with the help of the following formula:

=ARRAYFORMULA( if(A2:A=””,””,sumif(‘1_1_Cohort_work_LTV’!$E:$E,A2:A,’1_1_Cohort_work_LTV’!$D:$D)))

Our main formula starts from sumif. It can be interpreted this way: if the Year-Month is the same as the date of the same purchase, please count the revenue. NOTE: it is not a revenue from the new customers, it’s a total monthly revenue. 

Then we have Lifetime Value. Here we use maximum from the months columns using the formula:

 =if(D2=””,””, max(F2:Z2))

We’ve already counted revenues for each month for each User ID. Now, using the following formula we are going to find the total revenues for each month:

=if($A2=””,””,iferror(sumifs(‘1_1_Cohort_work_LTV’!N:N,’1_1_Cohort_work_LTV’!$G:$G,$A2),””))

We can explain it as: make a summary of the revenues of the month X, if the First Sale Month is Equal to First Purchase month. More easy – we count the total revenue from the new users for whom the month of the purchase indicated in the first column was the first month. Consequently, in the following month columns we will see the revenue from the same each following month, after the month 0.

Looker Studio LTV tab

This tab looks like this. Using curly brackets I simply duplicated the tabs A-D from the previous tab to this one.

To count the correct LTV we find the maximum from each month. 

Month 0 is also duplicated to this table from the previous tab, so it is fully correct. But what was wrong with the other ones on the previous tab?

So, as we know, LTV is a cumulative value and to make it right, we need to add the data from month 0 to month 1 from the previous tab so that we have a correct LTV for month 1. To calculate month 2, we could add to the total LTV of month 1, the revenue from month 2 from the previous tab. But it’s not that easy, because we may have nulls. The formula to count month 1 looks like this:

=if($A2=””,””,if(‘1_2_Pivot_LTV’!H2=0,””,sum(‘1_2_Pivot_LTV’!$F2:G2)))

It can be explained like, if the Month 0 from the tab Pivot LTV has no data, so leave it empty, but if it contains any data, summarize the data from the month 0 to month 1. 

For the month 2 we use the formula:

 =if($A2=””,””,if(‘1_2_Pivot_LTV’!H2=0,””,sum(‘1_2_Pivot_LTV’!$F2:H2)))

In this case we calculate the summary not only of month 0 and 1, but 0, 1 and 2. 

But if you want to calculate a total LTV per user, you need to divide a total monthly LTV by the number of users. 

I apply these formulas to the rest of the columns.Now this Google Sheets can be connected to Looker Studio!

If you don’t want to spend time creating everything manually, you can buy a Cohort Analysis report template in my template store! 

Hope you enjoyed the article! Subscribe to the newsletter not to miss the second part!

Check out more articles of mine in my Medium.