This is a full article putting together a 3-post series I published on LinkedIn. It explains how I have managed to define visitor retention metrics in Adobe Analytics.
I hope you find it useful.
A while back I got inspired by the way Amplitude put in practice the approach to user growth by Social Capital. Which revolved around the definition of Monthly Active Users (MAU) as being composed of three elements:
MAU(t) = new users(t) + retained users(t) + resurrected users(t)
Where new(t) are the acquired unique users this month; retained(t) are the users this month which were also users last month; and, resurrected(t) are repeat users this month that are not retained from last month but from some month prior to it. The following picture should clarify this visually:
The following identity also stands:
MAU(t-1) = retained users(t) + churned users(t)
Which essentially says that unique users last month either have come back and thus are retained this month or have not come back and hence have churned this month.
The value of breaking down MAU this way is that it allows you to segment your users so that you can regularly target them with marketing campaigns geared at retaining new visitors and resurrecting churned ones. In fact there is no point in making tons of investments in acquiring new users if most of them end up churning the following month: it is a lot of effort that quickly goes down the drain. On the contrary, consistent and healthy growth requires constantly engaging users so that they keep coming back month after month. And, as we know, it costs a lot less to retain than to acquire new.
But this is actually only a starting point for more advanced analytics that you can use to understand product retention cycles and therefore the successful patterns of user stickiness that can be leveraged for further optimization and user growth of your product.
So, I wanted to use this approach and turned to Adobe Analytics Workspace to see how much the tool would help me achieve that. The good news is that it is possible to define these metrics and underlying segments, however it is quite a painful process.
First thing first, this is where you will end up at the end of the journey:
Let me show you how I created all these metrics.
Defining the New Users(t) Metric
I must give tons of credit to my friend Adam Greco for having defined the foundations for this solution in his post Visitor Retention in Adobe Analytics Workspace. By the way, I am the client he mentions in his article!
In his work, Greco introduces the concept of rolling date ranges to define a segment that captures users that have visited your website this month but not in the previous 24 months.
As Greco mentions, this solution has a drawback, which is “this month” in a rolling date range always means the current month. Therefore, if you define a segment to capture users that visit your site this month but not in the previous 24 months, that segment will capture Visitors only for the current month and will be empty for all months prior to the current. Which requires you to have to create one such segment for each previous month you want to include in your analysis. He has created 24 of them.
Apart from that pain, you will always end up with reports which display data on a diagonal, like Greco shows in his post:
While I haven’t been able to find an alternative to the creation of the 24 different segments, I have however managed to aggregate all of them into one single metric. Let’s see how that works step by step.
Step 1. Define New Date Ranges
Say you are interested in a time window that includes the current month plus the last 24 months. You then need to define 25 new date ranges to capture This month, Last month, 2 months ago, 3 months ago, and so on and so forth until 24 months ago. The idea is that the beginning of time for your analysis is 24 months ago. Or in other words that, as the time goes by, you are always interested to know how your website is doing in the last 24 months + current month (i.e. a rolling window by 25 months). However, you can set the length of this window to your liking. Here’s an example for the date range “10 months ago” when the time of writing is 19 October 2017:
Once you’ve defined 25 such date ranges, you will need to define 24 extra date ranges that you need to use to exclude that a user was ever a visitor to the platform before a given month. For example, at some point you will need to define the segment of New users (10 months ago) (see Step 2 below), and because these would be users that visited the website 10 months ago but not in the 14 months prior (remember, 24 months ago is the beginning of time), then you need to define a date range that includes the full month 11 months ago plus all 13 full months prior to it. Here’s how you do it:
Step 2. Define New Segments
Define 25 new segments, one for each set of new users during the last 24 months + current month. Here’s the example for New users (10 months ago):
Step 3. Define Calculated Metric “New Users(t)”
Finally, in order to create the new users(t) metric, use this simple formula:
Defining Repeat Users(t)
Once you’ve defined the new users(t) metric, defining the repeat users(t) is a piece of cake. What are repeat users on a given month t? Well, they essentially are all unique users that are not new users on month t. In other words:
repeat users(t) = MAU(t) – new users(t)
So here’s how you define repeat users(t) in Adobe Analytics:
Defining Retained Users(t) and Resurrected Users(t)
Now, notice that repeat users(t) are also the summation of retained users(t) (those that we retained from last month) and resurrected users(t) (those that we reactivated from a month prior to the last), as shown here:
repeat users(t) = retained users(t) + resurrected users(t)
Therefore, once you’ve calculated repeat users(t), you can either calculate retained users(t) first and then subtract it from repeat users(t) to obtain resurrected users(t)or, vice versa, calculate resurrected users(t) first and then subtract it from repeat users(t) to obtain retained users, as these two derived identities show:
(1) retained users(t) = repeat users(t) – resurrected users(t)
(2) resurrected users(t) = repeat users(t) – retained users(t)
I’ve tried both methods as a validation to one another and both work. In this article I will present the method suggested by identity (2) above, so I will calculate retained users(t) first and then derive resurrected users(t). Here’s how to go about it:
Step 1. Define New Monthly Segments for Retained Users
Like we did for new users, we need to define 25 new segments, one for each month during the last 24 months + current month. Here’s the example for retained users (10 months ago):
Here’s a problem though. You would expect a segment like this to give you visitors only for the full month if you go back 10 months ago from current month. However, given its very definition, Adobe Analytics will make that same set of visitors appear on both full months 11 months ago and 10 months ago. You can see this visually if you define the following metric:
If you add this metric to a free form table, here’s what you would see at the time of writing (29 October 2017):
However, we are only interested to know retained users for the exact full month that stands 10 months ago from the current month. How do we solve that?
Well, we need some sort of month selector so as to show the retained users on the retention month only (Dec 2016 in this case). That’s what we will do in step 2.
Step 2. Define Month Selectors
A month selector is nothing more than a simple metric containing 0’s for all months except for the one we want to select, which needs to contain 1 as value. We will later multiply that metric by the metric calculated earlier for retained users so we will be able to get rid of the double attribution.
Keeping on using the example of 10 months ago, what you need to do is to use the previously created date range 10 months ago in order to build a new segment that only captures the visitors that visited your website on the full month that stands 10 months ago relative to the current month, like so:
Now, apply this segment to the unique visitors metric and here’s what you get:
We are just one step away from the month selector metric we want to build. You see, the value on Dec 2016 needs to become a 1. What’s the easiest way to make that value become 1? Just divide it by itself! However, as easy as this sounds, calculated metrics only allow for operations amongst metrics or between metrics and constant values (called static numbers in AA). So, in order to divide that value by itself, you need a metric that has that exact same value for the month in question. And what is that metric? It is simply Unique Visitors. So, all that we need to do to define the 10 month ago selector is to apply this formula:
And here’s how the result looks like on a free form table:
Exactly what we wanted.
Step 3. Calculate Retained Users(t)
Putting it all together, the formula for the the retained users(t) metric is the following:
Step 4. Define Resurrected Users(t)
As explained above, you now simply calculate resurrected users(t) as:
resurrected users(t) = repeat users(t) – retained users(t)
Defining the Churned Users (t+1) Metric
Last but not least, here’s the process to define the churned users(t+1) metric.
Step 1. Define New Segments
Similar to what we’ve done for new users and retained users, we need to define 24 new segments, one for each month during the last 23 months + current month. You will immediately notice a difference though: we’ve got one month less here, and that’s due to the very nature of this metric. We can’t in fact have churned users at time t. You easily see that as you define one of the segments. Keeping on using the example I’ve used so far, say you want to build the segment of churned users (10 months ago). You would define it this way by making use of date ranges previously defined:
As you can see, while we have defined these visitors as churning 10 months ago, this segment will however contain visitors attributed to the date range 11 months ago but contains no visitors for the date range 10 months ago. Why? Because of the mere fact that the segment definition above excludes users that have appeared on the platform 11 months ago. This means that we are in fact calculating churned users at month t+1 for any given month t.
Step 2. Calculate Churned Users(t)
All new segments having been created, here’s how you then compute the churned users(t) metric:
The multiplication at the end of the formula is required to make churned users a negative number (good for visualisation). It is a bit dodgy: I wish there was a way to multiply by -1 but there isn’t.
While getting to this point requires a heavy lifting, it is worth the effort if you are looking to use these segments and metrics as a basis for more advanced product analytics.
Please bear in mind that the retention cycle I’ve focused on for my analysis is monthly based. Therefore, these metrics will work fine if you break them down by month. But please notice that while you can scale them up to focus on longer intervals of time, such as quarters and years, as well as scale them down to analyze shorter intervals of time, such as weeks, days, hours, etc, you won’t be able to break them by quarters or days straight away; if you want to do so, you will need to redefine them by using longer or shorter intervals of time in your data range definitions.