We are excited to announce part 3 of our new video series, “Python in a Marketing World,” hosted by Ads Operations Analyst Tanner Riebel. The series provides a jumpstart for marketers interested in learning Python.
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 likewise included:
- A detailed walkthrough of a python script for pulling Google Ads data
- Example python script available for download so you can easily get started pulling your own data
- Additional resources highlighted to help you navigate the Google Ads API
With episode 3, Tanner digs into putting these API connections into action so that you can create your own campaign performance-based alert system to monitor your marketing initiatives.
Episode 3: “Create a Performance-Based Alert System” takes what was learned in episodes 1 and 2 and demonstrates how you can begin developing your own scripts for – in this instance – real-time or scheduled monitoring and alerts across your marketing channels, without needing to make additional martech platform investments.
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!
https://www.youtube.com/watch?v=2v1G2iBqHGM
Read Episode 3: “Create a Performance-Based Alert System”
Tanner:
Hey everyone. My name is Tanner Riebel. I’m an AdOps analyst at AIMCLEAR, and today I’m really excited to get into episode three of our “Python in the Marketing World” video series, which is centered around creating your own performance-based alerts system.
Now, before getting into it, all the resources are going to be available for download so you can mimic exactly what we’re doing throughout this video.
So, let’s just jump right into it and start out with an outline. First, “Why you would create a performance-based alert system.” We’re going to go over what specifically the one we’re creating today is, then we’re going to compare our script to some alternative solutions out there. Go over our reporting infrastructure. So, what we did in episode one and two, catch up to speed on that. And then what we’re doing in episode three will interact with our current structure. Then we’re going to jump into the script, go over high level overview of what the script does, and, also at the end go over an actual script output. So, what happens when we actually run the script?
Starting off, “Why you’d create a script like this?” This is just one example, but I think the primary beneficial reason for doing it is if you have multiple accounts and you’re not able to check it, whether it’s time or there’s just so many accounts, you couldn’t possibly get into it in one day. It’s good to have a safeguard there that will automatically be checking in and updating if there’s any red flags. The one we’re doing, and kind of any performance-based alert system will be doing, is we’ll be sending some type of alert to say, “Hey, something’s up here. You need to check on it.” Specifically for the one that we’ll be creating with this script is it’s going to be an email alert that will be sent to users if a certain performance threshold is hit. So, I have a couple examples outlined at the bottom here. And the one that our script is specifically set up for is we’re using our Google ads campaign data, which we uploaded from the API in episode two, to check on all of our campaigns and if any of the campaigns that we have are down 10%, or their CTR is down 10%, over the last seven days compared to the 90-day average, then it will flag that alert and send an email. Now, our script can also be changed to do different date ranges, like week-over-week or month-over-month, and also do different metrics. Right now it’s set up for CTR, but it can be expanded upon across whatever you need.
So, now let’s talk about some alternatives compared to our script. I’ll just start off with saying that this Python script is not everything you need in certain situations. It’s not a catch-all for everything. I outlined at the bottom some alternatives, or when you should use this script and you should really look closely at these ones, because if any of these criteria isn’t needed or isn’t hit, then you can look into some of these alternatives available. So, I’ll start with alternatives.
The first alternatives I have is, if you’re familiar with Google ads or Facebook ads they have, in their ads managers, set up their own rules systems so you can do some certain checks. Now the caveat with these is they are pretty limited in the rules you can set up, and we’ll go more into it. As far as some more advanced thresholds that you can set, then this might not always be possible in those current alert systems.
There’s also third-party tools, so I listed Optmyzr and AdStage, which is what I’ve used recently in the past, but there’s many, many more. And the big thing with these is they can do a lot of things that this script does for sure, but you just have to pay for them. So that’s the caveat there.
Now, jumping into when you should use this script instead of the alternatives. So, one major reason listed below is if you need an integrated assessment of performance before making that decision. And what I mean by that is if you have multiple channels that you’re following or data sources that need to be combined or aggregated together to make a decision.
For example, you need data from Google ads and data from Google analytics combined together in your rule to make whatever threshold you have, then that’s definitely not possible in the ad channels. For some third-party tools, it might be possible, but that’s just one thing you need to check or one possible use case where it would make sense to use a script.
The second one is advanced thresholds, so, going back to our example of what we’ll be doing. We’re checking the seven-day campaign average of CTR versus the 90-day campaign average of CTR. That’s a dynamic percentage. We can change this trend percent. And it’s relative a lot of times, especially, I believe still in the ad channels, you have to set it at a static value. So, you couldn’t set a 10% decrease in performance, you had to would have to say, “Our CTR is down X percent.” You couldn’t do a dynamic percentage.
Next one, so this is kind of where you would use these other tools for sure is if you need an action done immediately. For example, our scripts won’t be able to pause a keyword or pause a campaign. Now that could be built into it, but that’s just not what the script is built for right now.
Fourth one, unique data sources. So, a lot of the third-party tools and different ad channels that you use don’t have any alert system readily available to set up based on the source. If that’s a use case, then all you need for our script, is for it to be in BigQuery and you can set up that source for performance alert systems.
The last one, I kind of touched with third party tools, but if you’re budget limited for your resources, then this is a really good alternative that doesn’t cost any money.
Now, quickly touching on the reporting infrastructure so far. As you can see with these ones, episode one and two was covered for Google analytics API, and then Google ads API. And we brought that data into Google BigQuery, our data warehouse. Episode three is a little bit different here where we’re going to be pulling the data that’s now in there, in Google BigQuery, to use in our performance alert system.
So that’s kind of how the interaction is setup now.
Now we can start and jump into the script. We have our script pulled up and before actually getting into the script, we have to do a couple of things in BigQuery or data infrastructure to set it up and properly function with the script.
If you went through or completed episode two, then you have the base data that we need from Google, the Google ads campaigns. If you haven’t, then we also have this download of the CSV that you can upload directly to BigQuery. And what’s nice about downloading it from the CSV as well is you can mimic exactly the same data that I’m seeing as well. And after you finish this video, you’d see the same exact thing when you’re running the script.
So, with BigQuery for two and three, we’re going to have to set up some views. The view number one, we’re matching the dimensions and metrics with the correct data type. For both of these, we have the queries provided here that you can just copy and paste into Google BigQuery. The first view that we’re creating is this v_googleads_campaigns view and what we’re doing here, like I said, we’re changing the data types. All the dimensions and metrics are coming in as strings and we need to change those data types to be what we want them to be. So, “date” needs to be changed to a date data type, and then all the metrics need to be changed to floats. Once you have that set up, you can save that view as google_ads_campaigns, and you should be ready to go with that first one.
The second one, we’re going a step further and based on the view that we just created, we’re aggregating the data to what it needs to be for the script to properly function. I’ll actually pop up this one and copy and paste it in here and give you an idea. Once you run this script, you can see that each row is a campaign, and then it’s broken out. Like I said, we’re comparing the 90 day and seven day averages of all these and it’s broken out so we can grab from each of those, depending on which date range we need.
So, once that’s set up, we’ve saved that as google_ads_campaigns_monitoring. Just for organization sake, I put it into a different data set, performance_monitoring. Definitely not required; you can put in wherever you want, but that’s why it’s located there.
Now that we have that stuff, we can start to go through the script. I will pop up the PowerPoint quick and just mention that I have outlined here for reference the authentication, the function for each one of these.
You can see here; I have the lines that need to have user-based input. And what I mean by that is it needs to be changed to interact with your system or your authentication for it to properly function. So, for authentication and just for the general function that’s running, that is set up for you to be able to switch those lines.
The credential setup, or authentication setup, is setting up so you can access the Google BigQuery data and actually query it inside your script. If you went through episode one and two, then this should already be set up from the service account file. If you haven’t, then we’ll also provide resources in that PowerPoint that will outline what you need to do to get there.
Line 17 starts with our function that we have set up. So, what this is doing is grabbing the data from Google BigQuery and changing it to what we need it to be. That base view is where it’s pulling from, the google_ads_monitoring. The second view that we set up and you can see, we have all those measures that we created here.
And then we’re also creating some additional metrics based off of those, like CTR, CPA, and CVR. We’ll just be going through the example on CTR, but these are just set up so you can use that if you want to use it for those specific metrics.
So, once we have that, we’re creating it and sending it to a data frame. And this is where we get to the CTR set up for the performance check-in or task alert system. So, line 52, this is where we’re specifying our CTR threshold. If you remember me mentioning in the PowerPoint, we’re checking if any campaigns, if their CTR decreases by 10% and that’s how this is specified with the 0.1. You can change it too, whatever you change it to would be percentage wise. If you want to set up 0.05, then that’d be 5%, if you want to set it to 15%, 0.15, whatever you need to be. That’s how you change that.
Once we have that specified, then we’re ready to get into this for-loop. What this for-loop is doing is it’s going inside of that performance data frame that we have created and going through each row. Now, if you remember, each row is a campaign, so it’s checking each campaign of your Google ads data.
Now, this if statement is where it actually is setting up to check on if your performance is below that 10% performance threshold that we have, or CTR threshold that we have set. It’s going in and checking for each campaign the 90-day CTR. If it’s that 10% below that seventy-day CTR. And then if it is, then it will, when you’re running the script, it will print “CTR Performance Alert Triggered” and then this is where if it hits that threshold, it starts to create this email that will be sent out to whatever users you specify.
These lines right here, the body detail, footer and header are setting up that email to see what the end user will see. I won’t go into exactly what this is saying, because it will be easier to understand when we do the script output example, but it’s just setting up exactly what you want the email to say.
Now, lines 67 and 68. This is where you need some user-based input. The first one, line 67, you’re setting who this email should be sent from. If it’s your email, then you just put your email in here. The big consideration here, if you’re running Gmail, is if you have two factor authentication set up. I’ll actually get more into that later, but that’s a consideration is if you have two factor authentication set up, then you’re going to need to do what’s called an app password, which again, I’ll get into more a little bit further down the script.
Line 68 is specifying who you want it to send it to. So, putting it in, the emails in a list format, separated by commas, you can put all the users that you want to get this email once the alert triggers.
And then just some more additional formatting of the actual email. This is the subject that you’d see when the email actually happens. Setting what it will say is, “Google Ads CTR Performance Alert,” and then also specify, since we’re doing it at the campaign level, specify what campaign got that alert.
This is just some more formatting for the email, which is set based on whatever text you have out here outlined.
Once we have that set up, really the only other thing that needs to be changed is this line 89, is you need to input either your email password, or if you have two factor authentication enabled, you need to create an app password.
I’ll jump back to the PowerPoint to show where you can find that, if you need to create that. This is this link right here; it walks you through how to set up an app password.
Bringing back up the PowerPoint and this authentication slide is where you can find the resources to not only set up an app password, if you need to, but also set up anything Google BigQuery access-wise that you need to, which we outlined a lot in episode one. There’s also a quick start guide from Google for your reference as well.
And the last thing I wanted to leave you with was a script output example of what it looks like when it actually ran. Let’s pop up this example video we have. We’re running the script. First thing that pops up is the print of what data we’re running, and I’ll pause it here. There are a couple more prints that happen, so, once it hits this is, is it’s actually hitting our for-loop and we have a couple print to outline.
The first one is specifying the data, or the specific campaigns, so it’s printing out the campaign name, the 90 day CTR and seven day CTR of that campaign. And really all that’s used for is just to check real-time to make sure our script is properly functioning in the way it should be.
And you can see the next one is where we get into our if statement. So, whatever our performance threshold is specified, if it gets triggered based on the data that is above here, then we’ll see the print of “CTR Performance Alert triggered.” So, you can see that it actually did get triggered based on this. And if we were to calculate this, I think it’s a decrease in CTR somewhere around 12%. We have a set at greater than or equal to 10%. So, it’s valid that that performance alert got triggered.
Now I’ll unpause it here and then real quickly, the next one that’s going to go through, it’s going to print out, “Alert sent via email,” and it will do that after it goes through and actually sends out the performance alert to whoever you have the sender specify.
And then real quick, it went through the second campaign, did the same print of the campaign name and then the data associated with it. And then this, the difference between this one is the CTR performance alert actually didn’t get triggered and looking at the performance here of the 90 day versus seven day, that makes sense because the seven day CTR is actually higher performing than the 90 day.
So, unpause it again here. And that’s through the whole script. Now, checking to see that the email actually sent and a couple of things to show you how you can format it differently.
So, this is our subject line. Now we have some static text at the beginning of “Google Ad CTR Performance Alert,” and then dynamic text based on whatever campaign got triggered. For us, that was campaign one and then clicking into the actual email, you’ll see a couple more things specified of the task or why it got alerted. So, giving you a description of why it got alerted and then outline a little bit more of the performance. So, comparing the 90 day CTR and the seven day CTR.
And then one more thing, just to consider as you build this out on your own. At the end here, we just have “Check in to perform any optimizations necessary.” If you want to specify the actual things that should be checked, then this is a really good place that you can outline a couple of processes that should be checked first or what you should be checking specifically. Nothing past there, just to give you an idea how you could possibly improve upon it.
And I believe that’s all for that video. So, I just want to leave you with a couple of final thoughts. First of all, this is just one example of the many different use cases that you can apply. We went over CTR performance, comparing the seven day average versus 90 day average for all of our campaigns.
Now you can do many different metrics. Maybe if you want to check CPA or CVR or just straight up conversion volume, then you have the ability to do that with a little reworking. Or if you want different date ranges instead of seven versus 90 day, maybe you want to look at month over month or day over day. Anything can be reworked with this script to make it properly function the way you need to. It just needs to be slightly adjusted for whatever data you’re pulling.
And then the second thing is when, if you’re considering scaling this script and what I mean by that is if you’re considering not only looking at one performance alert, but having the script check five different things, then you’re going to want to consider creating additional functions because a lot of these, a lot of the things when you’re setting up additional performance alerts can be, or will be replicated, between each alert that you set up. So if you can build those within functions, then it will optimize your script and making considerably less lines.
And then, yeah, I just wanted to leave you with another hyperlink to the example script. And then we’ll also be providing all the different resources that we’ve gone over as well. And this PowerPoint presentation specifically.
And that is the end of this video. Feel free to reach out to us if you have any questions and thank you for watching.
