How to automatically pull Google Ads data to Spreadsheets

Pulling all marketing metrics into one place is a priority for all digital marketing teams. However, gathering this data is a Sisyphean task, online marketers spend countless hours copy-pasting and importing CSV files from Google Ads, Facebook Ads, Bing Ads… What if you could easily automate this boring stuff with some basic scripts?

I’ve been looking for good articles that could show me how to achieve it, but after stumbling upon hundreds of websites I couldn’t find any decent tutorials. This is my attempt to pen down my learnings on how to pull Google Ads metrics to Google Sheets before tackling the same issue with Facebook Ads, Bing Ads, LinkedIn Ads… Hope you’ll enjoy it!

Building your query

Developers have built their own language to torture data and get the information they’re looking for. Google Ads’s one is called AWQL, standing for Adwords Query Language, a SQL-like language that performs queries in Google Ads database. To figure out how Google Ads stores your ads data you can imagine its database as a huge Excel spreadsheet. This spreadsheet contains several sheets that are specific reports created by Google Ads. Each of these sheets stores the specific report metrics in columns. You can access all the reports that Google Ads offers by clicking on:

https://developers.google.com/adwords/api/docs/appendix/reports/all-reports

To get the data you’re looking for, the first step is to select the metrics from a certain report with the words SELECT and FROM:

SELECT: the metrics you want to get

FROM: the source report in which the metrics are stored

Let’s say you’re building a synthetic dashboard of your campaigns performances and you just need impressions, clicks, cost, cost per click, conversions, cost per conversion and the conversion rate of campaigns. The first part of your query will go:

'SELECT CampaignName, Clicks, Impressions, Cost, Conversions,CostPerConversion, AverageCpc, ConversionRate, Ctr ' +
'FROM CAMPAIGN_PERFORMANCE_REPORT '

If you run this query it will retrieve all gathered data from the beginning of every campaign in your ad account. But this kind of data may not be relevant, you may just need the data from campaigns that spent more than a certain amount this month. You can fine-tune your query with the WHERE and DURING parameters:

WHERE: conditions on the metrics.

DURING: you can specify your time range

In our example:

'WHERE Cost>0 ' +
'DURING LAST_30_DAYS'

You can also choose to order your query results from the highest amount spent to the lowest and then retrieve only top 10 spending campaigns. Even if we won’t do it in our example, this kind of data formatting can be done by specifying ORDER BY and LIMIT parameters:

ORDER BY: the metric by which data can be order

LIMIT: the number of results you want to retrieve

Our query is ready!

'SELECT CampaignName, Clicks, Impressions, Cost, Conversions,CostPerConversion, AverageCpc, ConversionRate, Ctr ' +
'FROM CAMPAIGN_PERFORMANCE_REPORT ' +
'WHERE Cost>0 ' +
'DURING LAST_30_DAYS'

Now we have to write a piece of code to run this query and send the results to a Spreadsheet.

Writing the script

Google Apps Script environment is JavaScript-based (nobody’s perfect), so when you adapt this script, you should keep in mind that JavaScript is a case sensitive language. This means that the devil is in the details, if you forget a capital letter your script won’t run. To avoid headaches, you can just copy-paste this script in Google Ads scripts editor and replace the variables query, spreadsheetUrl, sheetName and reportVersion with your own variables.

function main() {
var query = {'query' : 'YOUR_QUERY'}
var spreadsheetUrl = 'YOUR_SPREADSHEET_URL'

var sheetName = 'YOUR_SHEET_NAME'
var reportVersion = 'V201809'

var yourquery = query.query
var spreadsheet = SpreadsheetApp.openByUrl(spreadsheetUrl);
var sheet = spreadsheet.getSheetByName(sheetName);
var report = AdWordsApp.report(yourquery, {apiVersion: reportVersion});

In this piece of code, you specify:

• What data you want to send: the results of your query, stocked in the variable var query.
• Where to send this data: in a sheet from a certain spreadsheet, respectively stocked in var sheetName and var spreadsheetUrl.

Now we need to tell the script how to write our data in the spreadsheet. Here are two ways to do it:

• Updating rows
• Appending rows

Updating rows can be useful if you want to create a budget monitoring dashboard that summarizes your campaign’s spendings since the beginning of the month for example. It will squeeze old data and replace it with the new one. Lazy people will love this method as it can be achieved with a single line of code:

report.exportToSheet(sheet);

If you want to have a daily breakdown of your campaign’s performance, the following method is preferred. It appends new rows to write new data keeping the old one :

var rows = report.rows();
while (rows.hasNext()) {
         var row = rows.next();
                   sheet.appendRow([row['CampaignName'],
                                    row['Impressions'],
                                    row['Clicks'],
                                    row['Cost'],
                                    row['Conversions'],
                                    row['CostPerConversion'],
                                    row['AverageCpc'],
                                    row['ConversionRate'],
                                    row['Ctr']]);
                          }

You’ll have to adapt this part of the script removing or adding elements depending on the metrics that you’ve entered in the SELECT line of your query.

Finally your script should look like this :

function main() {
var query = {
     'query' : 'SELECT CampaignName, Clicks, Impressions, Cost, Conversions,CostPerConversion, AverageCpc, ConversionRate, Ctr ' + 
               'FROM CAMPAIGN_PERFORMANCE_REPORT ' +
               'WHERE Cost>0 ' +
               'DURING LAST_30_DAYS'
             }
var spreadsheetUrl = 'YOUR_SPREADSHEET_URL'
var sheetName = 'YOUR_SHEET_NAME'
var reportVersion = 'V201809'
var yourQuery = query.query
var spreadsheet = SpreadsheetApp.openByUrl(spreadsheetUrl);
var sheet = spreadsheet.getSheetByName(sheetName);
var report = AdWordsApp.report(yourQuery, {apiVersion: reportVersion});
report.exportToSheet(sheet)
}

Schedule the script

Now you have to upload your script on your Google Ads account and then schedule it to run periodically and update your dashboard.

To access the script editor:

Google Ads account > Tools & Settings > Bulk Operations > Scripts

Then ad a new script, give a little name to it and test it by clicking on Preview. You can check if your script actually worked by having a look at your target spreadsheet. If nothing has changed this means that something went wrong in your script. Open the Logs tab to see which line of your code isn’t working and try to fix it.

If you need help with implementing it, please don’t hesitate to reach out to me in the comments section.