Time Series Analysis through Moving Averages – Statistics in Adobe Analytics
In what has become one of the most read series on this blog I am showing some examples of what Adobe Analytics has to offer in regards to statistical analysis. In the previous posts we took a look at simple averages and standard deviations, regression analysis and even forecasting. In this post we are going to use a variation of the simple mean called moving average.
When dealing with time series data we might encounter what is called “noisy data”. Instead of showing as a steady line our KPIs might go up and down from day to day, making it hard for us to judge where the general trend is headed. One way of solving this is through the regression modeling we did before, which gives us a straight approximation line. But what we can also do is average the data for a defined window along our series, which is a very common technique in stock trading. This gives us a nice application, since we could build a trend detecting Workspace in Analytics like this:
If you ever looked on the charts used in stock trading this should look a little familiar to you. On the left we can see two moving averages together with our KPI and on the difference between them on the top right, which is sometimes called a Momentum Indicator. Below that, we even have a little detector for changes in the trend of our data. Pretty amazing, right? So let’s start building it!
Moving Averages in Adobe Analytics
As always, we are going to start with a simple Table containing our KPI in a Freeform Table and a Line Chart:
Now we create our first moving average in Workspace. Let’s head over to the Metric Builder and drag in the Cumulative Average function from the advanced functions section. We get two slots to fill, one for the number of items and one for the metric. If we wanted to take the moving average for the last 7 days, we would do it like this:
That’s all we need to do! Now we can save this metric (I named it “CA 7” for “Cumulative Average from 7 days of data”) and drag it into our table to see what it is doing:
Looking good already! We can see that our new metric neatly summarizes our data and gives us a smoother line to follow. What we can also see is how our metric behaves when it does have less data than required. In the first line, it only summarizes that first line. But the second line already contains the average of the first two. And as soon as we have enough lines, the average starts moving and always includes seven rows of data, as you can see here:
We are already done with our first moving average! Now we can express seven days of data with only one point on the graph, making it a lot smoother. But this would be a rather short post if we only did this. So let’s have some fun!
Building the MACD in Adobe Analytics
“Building the what?” you might say when reading this headline. I know, it sounds like a fast-food meal. The MACD is a technical indicator used for stock trading to generate what is called signals. This is a small peek behind the curtain of stock trading: Most of the time you just wait for something to happen, so you need a signal to wake you up and take action, like buying or selling stock. We can build this quite easily in Adobe Analytics with just three calculated metrics.
I just quickly created another moving average metric, but this time I included more data into the calculation. Can you guess how many days I included from my naming scheme?
That’s right, it includes 28 days of data. So what we are doing here is comparing the average from the last 7 days with the 28 day average to roughly approximate a week to a month of data. Coming back to our stocks example this would equate to a fast and a slow moving average. We can see that the orange line is the “slower” line because it responds much slower to changes in our data. In stock trading we would take the difference between those two to get a feeling for the “momentum” of a trend. We can do this quite easily by selecting both CA metrics and subtracting them:
This gives us another column and line in our graph like this:
What this new line is supposed to indicate is how strong a trend is. You can see that the moving average columns are equal to each other at the beginning, so the difference is 0. But as time goes by, we see the two lines diverge and cross each other from time to time. The bottom line in the graph now gives us an indication on how strong the trend is: If it’s way above zero, we have a strong upwards trend. If it’s way below zero, we trend downwards. That way we can judge the relative strength of a trend compared to others in the date range.
Now we are able to quantify how strong a trend is compared to past trends in our data. Neat! But let’s go one step further and build a detector for when a trend starts or ends.
Detecting trends in Adobe Analytics
What do we need to do to find the rows in our data that are the start or end of a trend? It’s quite simple on a conceptual level: We need to find every instance of where our Momentum indicator crosses the zero-line. I marked some of those events on this chart, where we can see how nicely it correlates to the beginnings and ends of the trends in the lines above:
So this is what we need to do: We need to create a new calculated metric that tells us when a metric has been above or below zero in the row before and is the opposite now. That’s quite a challenge, since we need to correlate the current row to the previous one, which is not trivial in Analytics.
First, we need a way to get the previous row of data. To do this, we are going to hack the Cumulative function in Analytics. It works like the Cumulative Average function by creating a window and summing up all data within that window. It looks like this:
In the upper slot we can define the size of that window. We will just put “2” here, so we get the sum of the current and the previous row of data. As metric, we put the Momentum metric from before in. From that cumulative, we now only have to subtract the current Momentum, so that it looks like this:
That’s a lot of metric! While it looks complex in the editor, it’s very simple on a conceptual level: If we want the previous value, we just have to sum the current and previous value and subtract the current value. We can validate it’s working by pulling it into our table:
You can see how perfectly this works in the squared are. The right column always contains the previous value of the column left to that. Just what we need! The next step is to compare those rows. We need a Greater-Than and Less-Than function, which we are going to wrap in an And function to combine the criteria. Here is how it looks like in the nice segment view:
If we flip this metric around, we get the same to detect downwards trends. We can create two metrics like those and add them to our table to see if they work:
Success! We see our detectors are able to detect whenever our Momentum metric crosses zero (going from a positive to a negative value or vice versa). If you multiply the downwards detector with -1 like I did, we can just add both together to one rather cool metric:
This metric shows 1 at the beginning of an upwards trend (or the end of a downwards trend) and -1 in the opposite case. Because of the length of this metric I’m not going to bother you with the full definition but only show the summary, which is long enough already:
Putting it all together
Now let’s build the nice Workspace I’ve shown above. In this one I separated the Momentum from the KPI chart to not mix the Y axis ranges. You can also see how the trend detectors indicate whenever we transition from an upwards-trending phase to a downwards-trending phase:
I’m quite happy with that! The Moving Averages were not complicated at all but we managed to make it quite complex in the end. We could do some pretty cool things with the trend detectors. Unfortunately, we can’t use them in alerts because Analytics only evaluates a single row at a time for alerting. If we could define a lookback window for alerts for usecases like this, we could have stock-trading-style alert emails in Analytics when a trend starts!
That’s it for today, have a great time!
German Analyst and Data Scientist working in and writing about (Web) Analytics and Online Marketing Tech.