Cohort analysis is useful to track changes in groups of metrics over time. A good example is user engagement – do users who joined in January stay as engaged as users who joined in October? However, depending on how your data is stored and collected, it can be tricky to run.
Both Google Analytics and Adobe Analytics have inbuilt templates to run cohort analysis. In Adobe, building the report is as simple as selecting two metrics, a date range and clicking ‘Build’. Total build time: 3-5 minutes. The end result is a nicely formatted cohort table which can reveal surprising insights.
These template do have their limitations however:
- You can’t run more than 12 cohorts (or time periods) at one time
- You are limited to running them against the default, out of the box metrics
- The format you are given, while nice, is essentially fixed (Adobe’s last update did allow you to change the colour of the shading, which is cute)
So what do you do if you run up against one of these limitations? You’ll have to download the data and run your own analysis. Below is a quick overview of how you would approach this using Tableau.
1. Download the data from AdobeAnalytics (probably via Data Warehouse) – at a minimum you will want date/time,visits and one other metric of your choice.
2. In a blank excel workbook, make a list of numbers from 1 till the maximum number of time periods you want to compare you cohorts against. In this example, we’ve gone with 15 which is 3 more than Adobe will allow us to do.
3. Add your Adobe data export as well as the blank workbook to Tableau, and join them using a 1 = 1 clause
4. Now for the tricky part! Depending on how you build your cohort analysis, at this stage you’ll need to create one or more calculated fields in Tableau. These will probably include:
- a ‘first visit’ flag
- a ‘last visit’ flag
- a ‘users’ calculation
- a ‘retained user’ flag
- a ‘percentage’ calculation
You will more than likely need to use the ‘fixed’ Level of Detail (LOD) in your Tableau calculations. If you aren’t familiar with these there is more info on LOD calculations from Tableau. There is also an excellent example workbook that has been shared on the Tableau forums if you need a workbook to follow.
5. To build the cohort visualisation, you will want:
- ‘users’ as well as Dates as discrete dimensions in Rows
- The imported list of numbers as a discrete dimension in Columns
- The AGG ‘percentage’ as a colour and label mark
- The SUM of ‘retained users’ as a detail mark
After all that, you can now appreciate that building a cohort analysis in Tableau is a fair bit more work than simply clicking a few buttons in Adobe Analytics. I’d say the total build time for an experienced Tableau user would be at least a few hours for the first run, not including the time needed to export to Data Warehouse and get it into Tableau.
As such, I’d recommend sticking to running cohort analysis in Adobe Analytics unless one of the limitations is an absolute deal break for your analysis.