We are excited to announce part 4 of our new video series, “Python in a Marketing World,” hosted by Data Operations Specialist Tanner Riebel. The series provides a jumpstart for marketers interested in learning Python.
In case you missed the first three episodes, check out:
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.
With Episode 4, “Creating a Weekly Executive Summary“, we take what was learned in the earlier episodes (especially episode 3) and walk you through setting up automated Executive Summary reporting, 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=d8fXR8mR58s
Read Episode 4: “Create a Performance-Based Alert System”
Tanner:
Hi everyone. My name’s Tanner Riebel. I’m a data operations specialist at AIMCLEAR, and today I’m really excited to cover episode four of our Python in a marketing world video series, where we’ll be going over how to create your own weekly executive summary.
With that, let’s just jump right into it and start with an outline of this video. First, we’ll be going over what our executive performance summary Python script is and what it will do. And then we’ll be comparing some alternative solutions versus our script, kind of when and where to use the script versus those other ones. And then we’ll be going over our reporting infrastructure: where we’re at with episodes one through four, kind of give an overview of how that infrastructure looks and why it’s created the way it is. Then we’ll be going over the actual scripts. And then finally ending with the script output example of what it looks like when it’s run start to finish.
So, what our executive summary will do, and if you’ve watched episode three, which was creating your own performance-based alert system, then it’s going to be really similar to what we did in that episode, where we’re sending an email out to users. And I have an example of kind of what that email example would look like below.
So, it’s outlining just high level, weekly performance, for example. You can see it has kind of the general metrics available: Spend, clicks, conversions, and then a couple of calculated metrics with CTR, CVR, and CPA, and finally ending off with just highlighting your highest converting campaign, which in our example is just called “campaign two.” And then the “WOW” you see under each one of these metrics is week over week.
I’ll just dive into the purpose of the script quick of why you’d want it set up this way. So, you can see in the first line here, first metric: spend. Negative 86% when it’s comparing week over week and spend. So the purpose of this would be just to highlight things like that, where you see a drastic decline in performance, or decline in spend for this.
If you don’t know off the top of head why that’s happening, then this should be a red flag to show you that you need to dig deeper and understand why that is happening. Same goes for conversions. You could see negative 91%, almost the exact one-to-one of spend. If you looked at just conversions, then you would see, “Oh, what’s happening? What’s going on here?” Having all these other metrics just available right here for you to see, you can see, “Well, we’re negative 91% in conversion volume. We’re also negative 86% in spend.” So that makes sense; they’re kind of one-to-one. If you’re gonna spend more, you’re usually going to get more conversions. If you spend less, you’re going to get less conversions. So you can easily see right away why the decrease in conversions happened.
So that’s kind of the overall purpose, or it goes into the overall purpose of this email alert is you can just easily see performance on a weekly level right away.
And then quickly, just some alternatives compared to this script, and by alternatives here I mean just different reporting resources, reporting software that’s available and that’s popular in the community, which I listed above is Data Studio, Power BI and Looker. There’re tons of other ones available, but those are three of the most popular ones.
I won’t go into the difference between each alternative, but, just highlighting kind of when to use this script instead of the alternatives listed above is if you don’t need any visualizations. Our example (I’m gonna pop back over on the final output), you don’t see any tables or graphs here, so that’s an obvious one where if you need that, then this current example can accomplish that.
And then another one is if you need a high-level overview of data, sometimes it takes a lot of setup just get a report running in any of these platforms. Where this script, it really doesn’t take any reporting setup as long as you just have that base data already in Google BigQuery that you need to pull from, which we’ll go more into the script. But it’s really easy setup and doesn’t take long time at all to do.
Before I actually go into the script, one last thing I wanted to go over is our reporting infrastructure. So, kind of where we’re at after episodes one through three, and then how episode four fits.
So, if you’ll recall, episodes one and two were really just connecting to the API. The Google analytics API was episode one and then episode two was connecting to the Google ads API, and then streamlining that data into Google BigQuery data warehouse. Episodes three and four are just going to be pulling from Google BigQuery, pulling the data that we need. And in both episodes three and four, it’s just setting up and connecting the data that we need to an email that we send out.
So, that’s kinda where we’re at in our current reporting infrastructure right now.
The hyperlink here has the script actually available for download. We’ll also put the script along with all the other resources we go over in the YouTube description, along with this actual PowerPoint as well, so you can follow along and change to make it mimic your own systems.
And now we’re gonna go through the script. Just before going through the script, there’s a couple of things we need to set up in our infrastructure, specifically in our Google BigQuery to make sure we can pull out the data correctly, how we need it to. And that goes along with creating a lot of these BigQuery views.
Step one here is just making sure our base data is ready to go. If you’ve completed episode two, then you should be set. If not, then there’s a hyperlink that you can go and set up to pull from the Google ads API your own data. If you don’t want to do that or don’t have that set up yet, then we also have a CSV for download [with] test data, but it’s set up exactly the same way I have it set up in my own system or the example that we’re going through right now.
And then the next two, three and four are just setting up BigQuery views. I’ll pop up our BigQuery to go over again what it is: First one, setting up and matching the dimensions and metrics data types because everything’s coming in as string. So, we want to change some of the metrics to floats and whatnot.
Next one is aggregating the data into a weekly view. And then the last one is getting that data into week-over-week view. So, all the queries for these are also provided here to create those views in your BigQuery as well, and I’ll pop up our BigQuery instance, to show how it should look like in the finalized version.
Raw data-wise, this is where we have all our API polls, along with the step two, the first view where you can see we’re pulling from the t_googleads_campaign. That’s the table that we’re using for this example. And you can see they’re all coming in as strings, even all the metrics.
So, what we’re doing with the v_googleads_campaigns view is we’re making “date” date, and then some of the metrics that we’re pulling, changing those to floats. Once we have that stuff, we can save it as v_googleads_campaigns, and then these two are going to be going off that base view that we set up.
The next one that we’ll create is this v_googleads_weekly. And I’ll kind of give you an idea of what it looks like by running it quickly.
What this one is doing, and you can see in the preview below, is it’s aggregating all the data to a weekly view and also pulling all the metrics that we need. And then the last one is v_googleads week over week. And this is setting it up so now we have it broken down to a weekly view, and then also bringing in the past week’s view.
And what this will do is just allow us inside our own script to set up some of the week-over-week metrics that we have pulled into the email.
Once we have all those set up, these are the three views we have here along with the raw data that we have, then we’re ready to start actually moving into the script.
I’m going to go over the function first and I’ll actually pop up the script here. We won’t start at the top. Actually, we’ll get more into the access at the end and we’ll start at the main function here: weekly_performance. Starting right at the top, when you’re running the script, you’ll see an output of “Running Weekly Executive Summary task.”
That means you’re actually inside the function and starting to manipulate and pull some of the data that you need for the final output. The first query that we have here, pulling from BigQuery, is the weekly performance. This is where, as you recall, when we pulled up the last view that we created, we created that v_googleads week over week. We’re going a step further with this and create some of the week-over-week calculations inside here that we can use later to set up the email.
The next one is weekly campaign performance. If you’ll recall the example where we have the highest converting campaign highlighted in the email, this is what this query is doing. It’s aggregating the v_googleads_campaign view to a weekly level by campaign, and then ordering it by the highest converting campaign. And then that way it allows us to pull the first campaign at the top of the list for this query.
And we start to get into some of the email set up. This is where we’re creating the variables and creating the text of how we want to look inside the email once we run the final output.
Lines 80 through 86 are just the same thing, but different metrics for each. You can see in the first one it’s spend, and it’s just specifying spend, what that weekly value is. And then also side-by-side creating that week over week value that we created in that first query and pulling that the same again for every single metric here. And then the last one, line 87, is outlining the highest converting campaign, which I went over before.
Once we hit line 90, then we’re setting up a lot of the formatting. Again, just referencing some of those or all of those variables that we created at the top and getting it formatted, putting some line breaks specifically in here. It’s just every single metric is on its own line. And then, body, header and footer are just all static texts, which you can definitely change on your own if you want to have different wording or just highlight maybe some optimizations that you should do if you see certain things or certain values in whatever performance that you have. But for this, we’re just specifying a generic message, like, “Hey team, this is an overview of last week’s performance,” and then also at the footer, just ending off with the “Enjoy your week! AIMCLEAR.”
Nothing needed to change if you don’t want to change what the actual message output is there. But line 95 and 96 is where we’re actually getting some user-based input needed. I’ll go more into this at the end, but for now, the from address is just specifying who the email is being sent from. So, if that’s your email address, or if you want it to be set up as whatever email address that you want to show who it’s sending from, that’s where you specify here.
And then also, line 96 is who you’re sending it to. So, you can have this in list format here and you can add as many emails that you want to receive the message for this line here.
Another input that you could change here is the email subject line. Right now, we just have it as “Google Ads Performance Summary.” Possible improvement upon this could be specifying the “Google Ads Performance Summary,” and then dynamically putting in a variable where it specifies what the current week is for this performance summary.
And then nothing needed to change here; it’s more formatting for the email. The next thing that could possibly need to be changed on your end is this line 115 where you’re specifying what the email server for your from addresses. So, whoever’s sending the email, what their server is connecting to to be able to send that email. In our example we have Gmail, but it will be different if you have, say, an outlook email. They’ll have a different server that you’ll have to specify. I would just look up this line here and it should outline, and just add outlook at the end and it should outline everything you need to change here. But usually it’ll just be like, changing this to outlook, and then you’ll have different numbers here.
Line 117 also needs user-based input. And this is where, I’ll go more into this in the PowerPoint when I pop it back up, but it’s basically asking for either your email password or your app password. Depending on if you have two factor authentication or not, is whether you put your email password or an app password in there. If you do have two factor authentication enabled for your email, then you’ll need to set up an app password and I’ll go more into that once we pop up the PowerPoint again.
That’ll pretty much do it for all the input needed. And again, when you’re running through the script on PowerShell or wherever you’re running it, then you’ll get a print at the end where it’s saying that it actually sent. And that should do it for all the scripts and everything needed there, just quickly popping up the PowerPoint again.
I haven’t come through this yet, but the PowerPoint kind of references again, the lines that need user-based input.
Then, quickly going over authentication. We didn’t go over lines 23 through 27 at the top of the script. That’s where it’s actually setting up the access to your Google BigQuery project. You’ll need to change specifically line 23 to outline that final path of where you have that authentication set up. And we outlined this in episode one, so that’s definitely an area you can go to reference to set up where we actually create a service account that allows us to connect our data to Google BigQuery, and either put data in BigQuery or pull from it. There’s also a quick start guide available as well for reference.
And then kind of what I highlighted at the end, just bringing it back up. You can find this area if you do have two factor authentication set up for your email, then this is a link to set up an app password, which you’ll need in order to actually be able to send emails from whatever account you have specified.
Next thing I wanted to cover was a script output example. You can see we have a demo video here of the PowerShell, of what it looks like when you actually execute the script. And we’ll get two outputs: The first one signifying, as you can see, that we’re just running the task, we’re inside the function.
And now we’re going through and setting up the email. And then if that works correct, we will see a “Summary sent via email.” And then we can pop open our email right now and we can see the subject line, “Google Ads Performance Summary.” Clicking into it, we can see some of the details of that summary as well with the week over week comparisons.
I just want to really emphasize one final point, brought up a couple of times, but just the fact that you can change this to be whatever you want to be. We have it set up to bring in the general metrics and some calculations as well, but definitely not everything that you could. For example, you could bring in CPM if you wanted, and all that would happen to do that is just slightly be revising the script to break down that calculation or whatever calculation you have. You could do different things like highlighting some of your best performing campaigns like we did at the bottom here with the highest converting campaign. Maybe you want the highest CPA campaign or the lowest CPA campaign, either way.
There’s just a lot of things you can customize with this to get the message that you want. Not only in the performance, but also in the static text texts that you specify at the top and bottom.
So, with that, that pretty much covers everything we wanted. If you have any questions, feel free to reach out to us at AIMCLEAR. And again, thanks for watching. I hope you enjoyed.
