Answer: How to look at more trend data?

Little tricks and techniques to know... 

Scales to get relative and absolute weights.
P/C Jean Poussin, from Wikipedia. (CC BY-SA 3.0)

As I mentioned last week, sometimes the tools just don't quite let you do the analysis you'd like to do. 

The key question was how to create a chart like this, with more than five variables with data from Google Trends?  (The built-in graphing tool of Google Trends only allows you to compare 5 different queries!)  

As you can see, here I'm showing 6 different holidays on the same chart. What if I wanted to compute 8 different Google Trends search volumes.  (Here the chart shows 6 different US holiday search volumes.)  

So... the obvious Challenge is this: 

1.  How can you get more than 5 search volumes compared side-by-side?  

As you probably noticed, Google Trends has a lovely built-in graphing tool... that's limited to 5 trend lines. 

Fortunately, there's a way to export the CSV of the data and that will let you compare everything side-by-side.  

If you look at this Google Trends display for searches of cranberries, oranges notice the Download symbol just to the left of the angle brackets ( <> ) to the upper right of the chart.  Clicking on that will download a CSV of the data.  If you've selected multiple Trends, then you'll get a CSV.  (And notice the lovely seasonality effects of cranberries and oranges.)   

Here's what that CSV looks like in Google Sheets: 

The thing we have to figure out is how to get 6 (or more) variables into our chart. 

Suppose we want to understand the search relationships between cranberries oranges walnuts figs blueberries.  Here's that chat on Google Trends for the past 5 years.  

Now, to add in another seasonal fruit, we have to figure out which fruit scores 100 somewhere in this chart.  If you look carefully, it's a toss-up between blueberries and figs.  BUT.. by scrubbing your mouse over the data (or looking at the CSV) you can find that the high point is figs for the period Aug 25-31, 2024, so figs will become our common baseline.  Every Trends analysis we run has to include figs so they will be comparable.   

What we'll do is to run another Trends analysis, but keeping figs in the set so we have a consistent basis to compare to.  So now, let's add in raspberries.  Here's what I get: 

It's fairly simple to then get the CSV for this data set.  Here I'm looking at it in Google Sheets: 

Then copy-paste the raspberries column from one spreadsheet to the other, and use the built-in graphing ability of the spreadsheet to give you what you want.  

That's a complicated chart, but notice the seasonal effects of cranberries and oranges.  Figs have a seasonality, but it's more complicated.  (I leave that to you to figure out why!)  

One thing to notice: If you add in a fruit that has values larger than the fig numbers, you'll have to choose THAT fruit as your baseline.  Figure out ahead of time which numbers are the largest so you can use that one as your basis of comparison. 

 

2. Since these are all relative values, are these BIG numbers, or relatively small ones?  How would you know?  

This is a ongoing puzzle for all Trends users--the numbers are all percentages with respect to each other.  That is, of the six fruits shown above, the values are all percentages of the maximum value.  (That is, 100% is figs for the period Aug 25-31, 2024.)  So, the value of 25 for blueberries on March 8, 2020, means that the "Relative Search Volume" (RSV) for blueberries on that day was 25% of the volume for figs on Aug 25.  All of the numbers are RSV values for each of the search queries.  

So, how do you get a sense of what's big or small? 

The best way to do this is to compare the RSVs to something you already know about AND is pretty flat.  

Here's a Trends chart for raspberries, blueberries and aspirin.  I used aspirin because it's fairly constant throughout the year.  Everyone has headaches all the time...   

Depending on what you're trying to analyze, you might have to find other fairly flat queries (try pharmacy or time as other queries that are fairly unchanging).  

By comparing your "fruit query RSVs" to other queries that you know about (and are fairly flat by comparison) you can get a sense of (but not the exact values for) the size and sweep of changes over time.  

SearchResearch Lessons 

1. When the tool you're using doesn't give you what you need, consider exporting the data and using a tool that DOES work for your analysis.  That's the big lesson here.  Trends won't give you more than 5, but other tools do.  Export and align the data to see what you want. 

2. Remember to compare apples to apples.  Or, in Trends case, remember that everything is an RSV (i.e., a percentage of the max).  Do NOT create two non-comparable data sets--you have to have one column in common--the value that has the max value for your data.  

Hope some of you find this useful when doing your own work.  

Keep Searching!