Analyzing trends in search keywords
And here it is, our very first own blogpost! As already mentioned in the announcement of our new site, we will use this blog to share interesting insights, knowledge, work arounds, fixes and scripts that we have created over time. In this very first, we will share and discuss an easy to use BigQuery script to find trending search keywords, products, etc.
Often the problem with analyzing trends is that it is hard to make a distinction between meaningless spikes and impactful trends. A search word that is used a lot on a single day might not mean anything, but if it is used often over a longer period it might be meaningful. To analyze the meaningful trends, we took inspiration from stock trading.
Defining trends in stock trading
In stock trading a frequently used calculation to evaluate the price trend of a stock is based on two moving averages. For a full explanation on how this is used in stock trading, please click here. In short, the average of a value is calculated based on a set timeframe to create the Moving Average. This can then be done for multiple timeframes, let’s say one long and one short timeframe. For example, the average of the last five days (MA5) and the average over the last 20 days (MA20). The longer timeframe (MA20) will move more smoothly and slowly, while the shorter timeframe (MA5) is more susceptible to sudden changes. The difference of these two numbers indicates which items are suddenly spiking and how meaningful this is.
Let’s take a stock and keep track of it over time in a graph (black line in figure 1). We will calculate the MA200 and MA50 and plot these as well in the graph. If the stock declines in value, the MA50 line will follow that decline shortly after, while the MA200 will not show a decline yet. The moment the MA50 line drops below the MA200 line, you should sell your stock (red arrow). Similarly when an upward trend is started and the MA50 line crosses the MA200 again after a price increase, you should buy stock (green arrow). In other words when the index MA50/MA200 is positive then there is a positive trend and vice versa.
The advantage of this approach is that it neglects meaningless single day spikes and looks at the longer term trends. Therefore this same approach can be used in finding trends in search keywords.
Create the calculations in BigQuery
To create the calculations in BigQuery you need an item-set with items you tracked over time. If you have a Google 360 environment where Google Analytics data is offloaded into a BigQuery project, this is already available.
The script we have created makes the same calculations as the chart above but now for search keywords. Next to that, it creates some measures to compare keywords and their trends with each other. These are called the “Power_nr_searchers” and the “Power_share” that indicate how strong the trend is compared to other search keywords in terms as the absolute number of searches or the share of searches that keyword has.
Implement it yourself
To implement it just follow the following steps:
- Enter the correct dataset from Google BigQuery in line 15.
- Enter the correct dataset from Google BigQuery in line 37.
- Choose the correct Short term period at line 90 for the ‘X’ PRECEDING ROWS
- Choose the correct Long term period at line 91 for the ‘X’ PRECEDING ROWS
- Run the script
For the long and short term period we have chosen 5 and 15 since this worked best with our dataset and the business we applied this script to the last time. However, you should always try to understand your business. This means that you should know:
- How fast is the business moving?
- How fast do/can we react?
- How important/expensive is it to be on time/too early/too late?
You can either save the query result to a table for analysis purposes, or run it on a daily basis via a scheduled query. Currently the script is focused on a single run for the complete timeframe. Although it does not require a lot of resources, you can optimize it when you want to do a run daily. Therefore you need to save only the last rows that are applicable for your timeframe (say last day in a daily run) and the timeframe of the datasets needs to be at least the same as the longest timeframe used for the Moving Average.
Creating a dashboard with this data
In our next blog, we will share a dashboard that uses this data to create an interactive chart that looks like the example above. Please feel free to use, alter or add to this code in your own environment or a business environment. We hope it helps you to guide your business and would love to hear from you how it worked and if it gave you any useful insights.