In 2020, AIMCLEAR Data Operations Specialist, Tanner Riebel produced a 4-part series on using Python to automate aspects of marketing common to all ad ops: performance alerts, reporting, and analysis. He’s kicking off 2021 with a new episode of “Python in a Marketing World” focused on how marketers can access and leverage the Google Trends API in their ongoing client (or brand) support.
Watch the video, download the presentation that includes links to resources, including sample scripts, or follow along with the transcript posted below. Be sure to subscribe to our YouTube channel to catch Tanner’s next episode!
Missed the first 4 episodes? Get caught up now:
Episode 1 “Reporting With The Google Analytics API“Â was a primer on connecting directly to the Google Analytics API and pulling site data into Google BigQuery for advanced data analysis.
Episode 2: “Reporting With The Google Ads API” continued the series and included a detailed walkthrough of a python script for pulling Google Ads data along with additional resources to help you navigate the Google Ads API.
Episode 3: “Create a Performance-Based Alert System” demonstrated how to advance learnings from the first two episodes and create your own campaign performance-based alert system to monitor your marketing initiatives.
Episode 4:Â “Creating a Weekly Executive Summary“, took what was learned in the earlier episodes (especially episode 3) and walks you through setting up automated Executive Summary reporting, without needing to make additional martech platform investments.
https://www.youtube.com/watch?v=jJsBOjFipyc
Read Episode 5: “Reporting with the Google Trends API”
Tanner:
Hey everyone, my name’s Tanner Riebel. I’m an AdOps analyst at AIMCLEAR. And for this video, I’ll be walking you through episode five of our “Python in a Marketing World” video series, where we’ll be covering reporting with the Google Trends API.
Before jumping into anything, let’s start with an outline of the episode. First, we’ll be going over what Google trends is and how people use it: going through the UI and covering the different features you have, whether it’s filtering and what all that data means. Then we’ll be diving in a little bit deeper and understanding when to utilize the script that we’ll be walking through instead of the Google trends UI. Then we’ll jump into the actual script, kind of go over the main points of it: the functions and then the different things that you have to change to make it replicate in your system when you want to use it. Lastly, we’ll be going through a live analysis of some data and just really show you use case of how you would work this script and how it can be useful for you and your team members.
Now an overview of Google trends, and the name is pretty self-explanatory. What this really is, is a platform that allows you to understand trends in Google search data. Whether that’s over a given period of time, it also allows you to look at related things to whatever search term you have, whether that’s queries or topics, and then break down into different things like looking at the interest by region.
It’s a really good, useful tool, and I’ve outlined a couple of things that are specifically useful for marketers. Â Starting out with the seasonality. If you want to look, maybe you have a product that you have, you can look up that product term and it gives you historic data dating back to 15 plus years of trends in the search term; how much people are looking at it. You can begin to understand, not only if your term or whatever thing you’re looking up has seasonality, but when it has seasonality. For example, maybe if you’re looking up “election,” like a search term that we’ll be looking up, as an example later, you’ll see a seasonality in that term increasing greatly every four years, right around the [US] presidential election.
Another use case is keyword research. Say you have a term that you’d be interested in understanding if there’s areas to expand some related keywords and it will give you two options for this: one being related top queries. What’s the queries that are being searched the most and are also really related to whatever term you have and then also related rising queries. What search terms are rising the most in search interest that are also related to whatever term you’re looking at.
And then the last one I have written down here is more of a high-level approach of looking at trending topics. For example, if you are looking at an industry term, then you can begin to understand if there’s topics that are coming up around that term that are beginning to gain some momentum and search volume. Then maybe you should look more into it, or maybe you should just understand what’s going on related to that keyword as topics come up.
Lastly, I just want to highlight a source that really takes a deep dive, not only to what Google trends data is and really explaining that well, but it does another example of how you could use this. One I found really useful at the bottom there.
Moving on into a little bit of the Google Trends API here, you can see that we’ve highlighted in this graphic, all the different methods that are available for this API and what module we’re using is called PyTrends. We have highlighted in yellow two of the methods that, specifically for the Python script that we’re using is what we’ll be going over and not focusing on any of these other ones.
But I wanted to make sure I highlighted that outside of the ones that we’ll be covering in the script. There’s many others that you can look into and utilize as well. If you want a really good understanding of everything this module PyTrends has to do, not only different use cases with which you can see here, but really how to build and use this module, a source in GitHub is right listed below.
The last thing to cover before actually jumping into the script is when to utilize the script and stuff, the actual Google trends UI. So, the most important distinction between our script and what you’ve seen in the UI is when you’re comparing multiple terms at once. Now, before getting into that, it’s really important to understand what the Google trends data represents. We have a box here outlining what the data is. All the data is going to be scaled from zero to 100; zero being the low point, a hundred being the high point relative proportion of search terms.
The numbers represent what geography you’re looking at, what time range you’re in. And then if you’re bringing more data into it, comparing the two, what the relative search volume between the two is. So, I’ll pop open an example of the Google trends UI, or looking at a couple of terms here to get a better idea.
So, first we’ll start out with just looking at electronics. As you can see, electronics were, what I would take from this, relatively flat. Relatively really consistent. As far as its trends month over month or even week over week with the high point being Black Friday week, which makes complete sense.
Now, say we want to compare a different term. Like, we had up here, Walmart, and notice how the data changes. So now it’s bringing in Walmart, which is the red line and our blue line of electronics completely changes the data and has it all set to “one” now, instead of what it had consistently 80 to a hundred before when it was looking at itself, which is really important.
And the main difference in a script, like I said, is what this graph doesn’t do is it doesn’t give you an idea of the answer or the question of, “is Walmart trending the same as electronics is trending?” It just gives you an idea of relative to each other. Obviously, Walmart is much more popular and you can easily see the Walmart trend here because it’s the higher search term, that doesn’t change.
But now you’re just seeing electronics as one and you really can’t understand how they work together. Or if they’re the same trends, going up and down at the same time period. So, what our script does is instead of pulling these two at the same time, it’s pulling each term or however many amount of terms you have individually so you can understand the trends between each other and not necessarily which one’s higher in volume. This way would be better for that, but it’s understanding the actual trend, if they go up together, if one goes up and the other goes down at the same amount. In that respect, you’ll get the questions answered there specifically to how the trend works between the two.
And that question would be answered looking at just Walmart itself here. We noted before when just looking at electronics that the highest point was Black Friday weekend or Black Friday week. Same thing goes here for Walmart, but then going back quickly just really highlighting it. You would not be able to understand that when you’re using the UI here and searching the same search terms together, which are script of voice and just pulls that singular data when just looking at itself for each term.
So, we’ll pop back to the PowerPoint. That’s the biggest reason and really the biggest difference between our Google trends script that we have, and the UI is in the data it’s pulled itself.
The next reason for when you utilize this script is same really for any other data pipeline or script that you’ll use, is it just removes overhead of pulling that reports. It’s just something to consider. If you’re consistently using this Google trends data, or aren’t using it because it takes too long, then this script is perfect for it because once it’s set up there’s relatively, past adding different terms that you want and then running the script, that’s really the only overhead that there is. And then you can leave the rest to just working on the analysis portion of it.
Alright. So, we’ve gone through all the background of the Google trends, and now we’re ready to jump into the actual script.
Here’s a download link that you can follow along and change to however you need it to work in your systems. Yeah, let’s just jump into it. A couple of things that we need to set up before actually jumping into the script is Google sheets. So, we’ll create a Google sheet here and the main reason for creating this, what this Google sheet will accomplish is it’s all the Google trends data that you’ll need for whatever requests that you have.
So, you see, we’ll have all the terms here. And then for each term, what region it wants to be pulled from and the timeframe it wants to be pulled from. Something to note about the formatting here is it’s really important. The API has a very specific way of, for the region, how it wants format. So, you see we’ll have a “US” for the United States and then timeframe, we have it pulling 12 months of data from today. That’s “today 12-m”. So that’s something to consider. And this source here, which I’ll share this, this Google sheet specifically gives you all the different formatting, the different timeframes and how to format that so it actually works through the script.
Another important thing you have to follow is, so your Google cloud project, you need to share the Google sheet with that Google cloud project service account email. And here’s the link for the resource of where that’s located. But if you don’t do that, then you basically won’t have permission to pull the data from the sheet. So that’s what that allows you to do is just opens up some permissions.
Next thing is authentication. So, if you’ve watched any of the previous episodes, authenticate to BigQuery is something that you’ll need to set up, which is outlined really well in episode one of this series. So, use that as a resource, and then there’s also a quick start guide that you can use from BigQuery to use as a resource of setting that up as well.
Next important thing before actually running a script is inside your Google cloud project, you’ll need to enable the Google sheets API. And if you don’t know how to do that or haven’t done so yet, then there’s also a resource here for you to follow along.
Alright. So now we’re able to get into the actual script functions here. Tere’s some main things to highlight here. I’ve highlighted where all your user base input is needed for each function here and then what each function is so you can easily identify that.
And going into the analysis, pull that up here. Alright. So now we have the script pulled up, first things first. Here’s some really good documentation of different resources to how the Google trends API functions and what you need to know there. I would really recommend diving into that pretty deep to really understand what you’re pulling and different ways that you can do this or pull different things.
Next we have the authentication; you’ll need to add your service account json file here. Really that’s the only thing that you need to change from the authentication portion. The next portion that we have is actually specifying the Google sheet that you need to pull the data from. You’ll see here again, specifying the service account json file, same as we did above for the authentication. Really that I believe is the only thing you need to change here as well, because in the actual function, that’s where you’ll call out the specific spreadsheet ID and the range of data that you’re pulling for that spreadsheet. So, that’s the only thing you need changed in this section as well.
And then we’ll get into actually starting to parse out the data. We’re first connecting the interest over time data for each term. This is where we’re using that grab_terms that we had, or the grab_terms function that we had above to pull the information that we need to send to Google trends to pull whatever data that we want.
So, spreadsheet_ID, Google sheet range_name, both are important here. And then this, nothing needs to be changed here. But what this does is actually it’s going through a for-loop and specifying inside the sheet, kind of just matching up for your request. So, it’s saying what the keyword is inside the sheet, what the timeframe is and what the geo is, is, which is needed for every request that we do for Google trends building the payload here and then actually parsing it out and going, “okay, this is the data that you want,” and setting it up in a list.
So, and then we’ll finalize that, we’ll change that list to a dataframe here. And then we have really our base data here. So, we’ll have date, theme, keyword, and value, for whatever requests that we had pulled for whatever keywords we had pulled for.
Last thing needed here is sending it to Google BigQuery. So, we’ll have to change a couple things here as far as whatever data set name you have going to BigQuery. And then also the unique product or project ID that you have going to. Also outlined here, same as any Google BigQuery pull, is what do you want to do with if there is an existing table? Right now we just have it set to replace. So, it will go through, and if there is a table there, replace it with whatever we’re pulling whenever the script is executing.
So, when we’re actually running the script, once it gets this print of “interest over time data upload to GBQ,” then we know that the script has successfully ran through and pulled that interest over time data.
The next portion of data that we want to pull is related queries. So, this is a section of Google trends where based on whatever terms that you’re specifying in the Google sheet, it will give you the related queries for that sheet. And we went over that earlier over the background, but it’s really the similar way of pulling the data as far as what you need. So, we’ll need to specify the spreadsheet ID again, the Google sheet range name. This is all building what we actually have. Same thing here is what was pulling the interest over time data. In fact, you could optimize this script to make it so you don’t have to specify this within the function again. Nonetheless, this works as well.
The important difference here for specifically this related query section is we’re pulling two things. So, we’re pulling the top keywords, or top related keywords and rising related keywords. And then we’re putting them both in their own table.
Same thing for setting up a data frame, and then eventually getting it set up to GBQ for both of those where you’ll need to outline the data set name and the project ID.
And then we’ll get prints here for when both of those are finalizing, or actually have uploaded into Google BigQuery.
Then the final one here again, and you’ll know that the script has fully ran and successfully upload your data to Google BigQuery. That’s really all there is to the script here.
Alright, last but not least, we’re able to go through an analysis example of what it looks when you would run this from start to finish. So, we’ll start off with the Google sheet. And this is where you’re filling out what terms you want to pull in Google trends. This theme we also have outlined here, is the main use for this is it gives you the ability once you have this in a report to be able to filter by the themes. So, we have Walmart as a theme and then gaming with the light keywords for each theme that we want.
Then you also have to fill out the region. We have US, and then time range, we have a 12-month time range. The last thing I’ll mention regarding the Google sheet that you’d want to make sure you have, is sharing with your service account email address that you have set up so you can not only pull it from Google trends, but also place it in BigQuery.
I’ll also go next to BigQuery. So, this is what it would look like after you ran the script. So, for our example here that I have outlined, we have our project and then we have a couple of data sets here. We’re pointing it to the raw data, and then you can see in our past examples we have Google ads, but then you also see the Google trends here, the three tables that we outlined to pull.
I think it would be good to show you, this is the example script that we would be running specifically. We filled out some of these sections that I didn’t show beforehand when we were going through the script.
We have our json that we want pointed to, or where it’s located. We have this spreadsheet ID from Google sheets, along with outlining what tab it is. So, Google trends terms, and what columns that we want pulled. Same here for these two.
And then the last thing that I wanted to mention, so when you’re looking at any of the GBQ pulls, we have our data set outlined, what our table name is, what our project ID is here. So, wanted to point that out, to make it clear where everything’s pointing to and how you’d fill that out for your setup.
And then last thing I just want to highlight and go quickly through is kind of a final example of what it would look like. We have it in Power BI. Now this could work in any type of reporting platform you have; Google data studio would be a great one that’s free. This is what it looks like, final form, here. We have a couple of things that if you’re not familiar with Power BI, this is a fairly simple setup for Power BI, but these top here is just gives you the ability to click inside and dynamically change the report. So, if you want the theme “gaming,” then the entire report changes to only show the gaming keywords. Same for Walmart. And also on the right here, say you want to dig into specific keywords. Let’s look at the trend between PlayStation four versus PlayStation five. Then it can easily pop up.
If you want to look at PlayStation five versus Xbox series X. As you can see here, they almost have identical trends. Which makes sense since they’re both new products coming out, so they kind of, they get the buzz here. It seems like both of them, around their launching. And then I believe that both of them launched around this area. So that’s when they’ve got the biggest jump to interest over them.
And then also in the bottom here. So, the reason we did a couple other tables that we pulled in was not only just trending, or charting the trend of interest over time, but then also let’s see, I’ll just pick PlayStation five as an example. Once you click that, then you can see all the top related keywords and trending keywords I call this but rising related can be the same thing. So, you can find, when you look at “PlayStation five,” these are keywords that are rising related and these are top related. So, it gives you a list based on the specific keyword, which is really nice for keyword research, which I mentioned before.
I think that covers everything I want to go over here for this specific power BI report, and kind of a real-world example of how it looked when you actually want to run something on your own.
So that concludes episode five of our Python in the marketing world video series, where we went over the Google trends API. Below in the description, I just want to highlight a couple of things. So, anything that we’ve shown, as far as scripts or this PowerPoint in itself will be provided for links at the bottom, along with a link or two of some past videos that are helpful. If you haven’t had the chance to look at any the previous episodes, we do some interesting things among Google analytics API and Google ads API as well.
So, with that, I’m going to sign off. I hope you enjoyed the video. Contact us if we can help you with your data and martech needs!
