Coolest way to trend % change on Adobe Analytics
Let's get started!
With this post I want to answer a question that many analysts have been asking for: 'Is there a clever way to build up a trended % change TimeOnTime without creating thousands of segments and custom data ranges?' -Well, yes! It is possible.-
I began playing with advanced functions, many of them are pretty pointless but some are really useful and underestimated, in this case our hero is the Cumulative function! Briefly, this function returns the sum of a metric for the last N rows (as ordered by the dimension), below I will explain you why is so valuable.
Calculated Metrics on Analytics Workspace
The starting point for comparing data with the previous range (hour, day, week and so on) is to set up a Calculated Metric [uv period -1 on fig.1] that always returns the last element of a column. Now, we can easily create a % last period uv metric comparing Unique Visitors and uv period -1 (I've chosen UV but any other event, instance or metric would be appropriate). As we will see in more detail later, one of the coolest things is that it gets automatically adapted to the Time dimension we are using.
Refining the solution
Defined in this way the CM we have just created will always return an Infinity% value for the first position, making it hard to use, right? So in order to clean it I used two simple functions -> IF and GREATER THAN. In a few words % last period uv [DEF] is being defined like this: IF % last period uv GREATER THAN 10000000 (a super high number) RETURN 1, ELSE RETURN % last period uv; in this way we will always have as first item 100% [fig.2].
Now is where things get finally really interesting. We can refine the solution even further by creating % change uv [DEF] (% last period uv [DEF] LESS 1) which gives us a more intuitive number (in percentage) of the UV changes between periods. Also, for example you could even define an average change between the periods selected (avg % change uv [DEF]) by using the Mean function in order to get additional information for the analysis [fig.3].
As anticipated at the beginning of the post we can change the Time dimensions (or add/remove segment on the report) and this CM will work properly adapting itself to the new configurations [fig.4].
However, there are a few things we need to pay attention to...
First, we need to check the reference data range, it's important that the end isn't in the future (it's not recommended to use 'This year' for example) and, for a proper data valorisation, the Time dimension should always be entirely included in the data range selected.
We also have to make sure that there are no 'holes' in the report (no zero values in the UV column) or it will return 0% due to the if.. greater than condition. To debug this really quickly I suggest you to use this simple CM which returns the minimum element which does not have to be zero [fig.5].
If you're practical enough with building Calculated Metrics and Advanced functions, this solution could be really useful thanks to its adaptability and the possibility of just applying any kind of segment on top of it. Once you have built the framework of % change uv [DEF], it's possible to replace UV with any other event or metric and save it as a different CM without repeating all the steps.Analytics Adobe Trend