How to Build a Cohort Analysis in Adobe ReportBuilder

As a follow-up to Adam’s Cohort Analysis post for SiteCatalyst I wanted to provide an example of how you can easily translate a standard output from Adobe ReportBuilder into the cohort view. I have seen some other posts on how to create a cohort analysis in Adobe ReportBuilder but they all seem to require a lot more work than you should have to put into a dashboard if you use a few more Excel tricks. The following dashboard shows you how you could create a cohort view without having to create a gazillion segments or a bunch of different ReportBuilder requests in the same workbook. Keep in mind you may still have to do some of that extra work if you aren’t implemented correctly but hopefully you have implemented in such a way that doing important analysis like this is easy for you.

What This Report Gives You

I think the coolest thing about this example, and the real value that the data provides, is that you can see the average attrition for each cohort over time. The cohort table below gives you the revenue attrition for each cohort for every month that cohort has been alive. However, I like to end it all with a simple output that is easy to understand. So you’ll notice that I stuck an Average Attrition column at the end which gives a single number representing the cohort’s performance over time. You can see in this example that the Feb-2012 cohort has had the most attrition (click for a larger view).

Once you have identified a bad or good cohort you can then investigate what kind of promotions or programs may have been in place for that group. Those may all contribute to the poor repeat business.

How to Make This Report

Before starting, keep in mind that there is a lot of date recognition going on in this example using custom American dates. The way Excel recognizes dates varies by local so you may have to adjust your classifications to work better for your region if it gives you trouble.

First, insert your ReportBuilder request. In Step 1 of ReportBuilder pick the Original Purchase Month classification and ensure that the time range encompasses all the data you want to look at.

On step 2 add the Month dimension from the “Dimensions” tab and include Revenue from the metrics tab. Insert the request into cell A5 of the worksheet. Notice that I also adjusted the report to include the“Top 1-10000” values. This is much more than I need but shouldn’t hurt if you have your date ranges correct.

With the ReportBuilder request inserted in the workbook and if you are using the same sort of data as shown in the example then that may be all you need to do. Continue reading, though, if you want to learn about the rest of the formulas.

1. Start creating the table by setting up your start date in cell G6. This formula looks at all the dates under Original Purchase Month and takes the minimum date (the oldest date). This will establish the starting point of our table which will update automatically as you pull in different dates. Note that this is an array function which you have to press control+shift+enter to input. I’m using an array function here to evaluate every date individually otherwise the MIN function doesn’t work. If you are using a more standard date format for your classification you might not need the DATEVALUE in the array function.
2. In cell G7 I use this formula to increment the month up for each row as it is copied downward.
3. In cell H6 is where the real magic happens. This is another array function (remember to use control+shift+return) and it will match the Original Purchase Month on the same row with the Month that is X number of months ahead. X is determined by taking the column number that the cell is in and subtracting the column number at the beginning of the table. This is a good trick for making an auto-incrementor right in the formula. It will count up the months as you drag the formula over. The thing that really makes this an array function is the two MATCH criteria we have since we need to look for the right Original Purchase Month and Month.
4. I hate doing manual work so I dragged the formula from cell H6 across the whole table. Then, to account for any cells that generate an error (because there is no data for that month) I applied conditional formatting to make the “#N/A” a super light gray so you know it is there but it isn’t in the way.
5. The last part is the easiest part. You now make a similar table below (cell H22), calculate the change from month to month (see cell I22), stick an average on the end (column T), and apply some quick conditional formatting. As you apply the formatting be sure to apply separately to the body of the table and the averages since those are really different sets of data to evaluate.

Final Thoughts

This was an example around monthly time ranges. Keep in mind that you could do week or other granularities. Just make sure you have a classification in place that matched that granularity.

Another thing I would only do for this example is include the final table on the same sheet as the source data. For a real dashboard I would move the data and intermediate steps to a different tab and just show the final report on the first tab.

We’ll, there you have it…a workbook that easily translates a typical ReportBuilder output into a cohort table. Enjoy!