This script automates adding any AdWords data to a Google spreadsheet

If you’ve ever been frustrated at the amount of time you spend creating PPC reports, you’re not alone. Today, I’ll do my best to help you with a new AdWords script I just finished.

The severity of the reporting problem became very clear to me at a conference I recently attended. Attendees were asked to leave sticky notes describing the most timing-consuming aspects of their jobs. I know it’s not exactly a scientific study, but I found the results fascinating anyway. Here’s what the board looked like:

The orange arrows highlight each note that said “reporting” was the most time-consuming PPC task.

Meetings are another time-consuming task people added to the board several times — but I’m not going to help you fix that with a script, so let me focus on reporting, a topic I’ve covered several times in recent posts here.

When it comes to reporting, you should really try to produce something that gives the stakeholder the data and insights they need, but in such a way that you can easily repeat, and hopefully even automate it. There are plenty of tools that can help with that.

And while automating reports is a reasonable goal, the reality is that there will always be cases where the client wants just one extra piece of data. And because of Murphy’s Law, this will always the one thing your reporting tool doesn’t cover.

Why don’t reporting tools cover everything? By my last count, AdWords has 46 different types of reports available through their API, ranging from common ones like an account performance report to the more obscure, like the keywordless category report:

Reports available through the AdWords API. Screen shot from the Google Developers Site

In all likelihood, the vast majority of people care about just 10 percent of these reports, so engineering resources get assigned to supporting the most commonly requested reports. And even if a tool covers all 46 possible report types, it might not filter the data the way the client wants, or include some of the lesser used metrics, attributes or segments.

So, how do we get custom data from AdWords into virtually any reporting engine? Through a data connector. And one of the most popular places for marketers to store data is in a spreadsheet.

Google Sheets, just like AdWords, can be automated with App Scripts from Google. This means we can write some code to make the spreadsheet connect with a data source of our choosing. Combined with AdWords scripts, which have permission to access your AdWords data, we can create an automation that lets you specify a template for a report, and then automatically add the requested data on a predetermined schedule.

So, here we go! Let’s get you a free tool to put any AdWords data you want into a Google Spreadsheet.

Step 1: Set up the report template in Google Sheets

Copy this spreadsheet into your own Google Drive. This sheet will look very boring when you first load it, but there are about 600 lines of code in the “Script Editor” section where all the magic happens. This will also be moved to your Google Drive when you copy the script into your own account.

Notice that when the spreadsheet finishes loading, you’ll have a custom menu called “AdWords Data Grabber from Optmyzr.”

Select the type of report you’d like to run from this custom menu:

When you select a report type, the spreadsheet connects with AdWords to get the appropriate attributes, metrics and segments for the selected report. It automatically sets up drop-downs in the spreadsheet to let you choose which elements to include in the report and what filters to apply.

Go to the “Settings” tab and choose a date range for your report from the drop-down. Since the goal is to set this report on an automated schedule, the only options are relative date ranges.

Next, select any filters you want to apply to the data. A pretty common one would be to filter for items that have more than 0 impressions. This step is optional.

For things where AdWords expects you to choose from a set list of values, the spreadsheet will present you with a drop-down of valid choices, for example, for campaign status, you could choose “ENABLED.”

Now, go to the “Report” tab and select the data you want to include in each column. Once again, the spreadsheet will already be loaded with the available options for the report type you have selected.

Step 2: Install the AdWords script

Now to automate the data pulls, simply install the following AdWords script code into your account and set it on a reasonable schedule, like weekly or monthly.

In this code, edit lines 17 through 22 with your preferences. The most critical field to update is the one with the URL of the Google spreadsheet we created and modified above.

Now you can preview the script; if your settings are fine, you should see a success message similar to this one:

Your spreadsheet should then contain the requested data and could look something like this:

Note: There are many metrics that cannot be combined in reports, for example, conversion type name cannot be reported at the same type as clicks in the campaign report. Checking for this before attempting to populate the report is tricky, so the script will return an error about your error.

When this happens, simply update the headers in the “Report” tab of the spreadsheet and run the script again until all errors are cleared.

Including the data in a report

You can use the data as-is in Google Sheets, but you can also include it in automatic scheduled reports. In Optmyzr, you would add a reporting widget that points to the URL of the spreadsheet. The tool then pulls in that data and includes it in the report, using the same styling as the native Optmyzr widgets. Your client won’t be able to tell the difference, and you’ll look like a hero for delivering the custom data they want on an automatic schedule.

Conclusion

PPC reporting takes a ton of time. And because clients invariably have some custom requests for their reports, reporting tools often need some custom integrations to deliver the goods. This AdWords script, together with the Google Apps Script in the spreadsheet, lets you bring custom data from AdWords directly into most reporting engines where you can now automate one more thing.

I hope this script helps you get back a little bit of time to do other things. And if you’re anything like the people who left the sticky notes on the board, I hope you’ll enjoy that next meeting!

Opinions expressed in this article are those of the guest author and not necessarily Search Engine Land. Staff authors are listed here.

About The Author

Frederick Vallaeys

Frederick (“Fred”) Vallaeys was one of the first 500 employees at Google where he spent 10 years building AdWords and teaching advertisers how to get the most out of it as the Google AdWords Evangelist. Today he is the Cofounder of Optmyzr, an AdWords tool company focused on unique data insights, One-Click Optimizations™, advanced reporting to make account management more efficient, and Enhanced Scripts™ for AdWords. He stays up-to-speed with best practices through his work with SalesX, a search marketing agency focused on turning clicks into revenue. He is a frequent guest speaker at events where he inspires organizations to be more innovative and become better online marketers. His latest book, Digital Marketing in an AI World, was published in May 2019.