How to quickly format retention data in less than 5 minutes to maximize learning

Formatting a retention data set is critical whether it’s for yourself or in a situation like a job interview. Once you’ve asked all of the questions necessary to understand the data set, you should format it to maximize your ability to analyze it. Here’s my step by step process:

Export the raw data from your analytics system

It should look like the below. The cohorts are in the first column, the size is next, and then the number from the original cohort that retained in each subsequent period.

Create another table that computes the percentage values of the original cohort size

Add a conditional formatting element to show the size of the cohorts over time (conditional formatting -> data bars)

Apply conditional formatting to the table that contains the percentages. It should now look like this:

Adding summary rows

Then I create two new rows at the bottom of the percentage table. The first is the average of each column, and the second is the percentage decrease for each additional week.

Wrap it up

Now you have a nicely formatted retention table that shows you:

1. The size in cohorts over time

2. At a glance how your cohorts retain over time

3. Where there are good pockets and bad pockets of retention

4. The average of cohort retention over time

5. Which weeks have the biggest drops in retention

6. Whether your retention levels off over the long term

Where there are many more graphs you could create off of this data set, I think this sets the right foundation for how to quickly look at the data and answer some important questions.

Are there other ways you visualize this information? Do you recommend doing this another way or formatting it differently? Let me know in the comments below.

Disclaimer: all of the data in these screenshots was made up for this example.