Exporting PPC data for reporting or analyses is just horrible! For many of us, it’s a daily task that is fueling nightmares.
It takes way too many clicks and causes high blood pressures because of endlessly refreshing the slooooow loading interface.
Good news:
Exporting a single data report, only once, can easily be done via the Google Ads interface: you just need to find the download button at the right side of the report, then select “Google Sheets” to start the export.
Bad news:
If you need to export data from many different reports and different accounts every day, repeating this simple action over and over becomes tedious and can easily cost you countless hours per month.
But what if you had the data delivered to you automatically every day, via a link to a Google Sheet, one click away from within your daily mail? You could be spending all those wasted data-export-generation-hours on strategy or other tasks to help you grow your business.
Thankfully, you can automatically export almost all Google Ads data to Google Sheets by using a Google Ads script.
Here’s how:
For illustration and learning practice, we’ll use the sample script at the bottom of this page. The script exports keywords with 100 clicks and 0 conversions.
Don’t worry if you aren’t familiar with JavaScript or coding in general–we’ll take this step-by-step in this guide.
Prep Work: The Scripts Page and Setting Up a Google Sheet
Before we begin, make sure that you’re on the right page to set up Google Ads scripts. Simply navigate your way to “Tools&Settings” -> “Bulk Actions” -> “Scripts” — it should bring you to the Scripts page.
Once on the “Scripts” page, click the round blue button with a white plus sign on the left.
This will create a blank script that we’ll fill out slowly in this guide.
You may also need to name, save and authorize the script when running it for the first time.
Next, you create a new Google Sheet. Tip for chrome users: simply type ‘sheets.new’ in the address bar. Keep the url of the newly created sheet at hand.
NOTE: If you’ve never copy-pasted, previewed, and scheduled a script before, go do so right now before you continue. Here’s my beginner’s guide on installing, running and scheduling Google Ads scripts.
Step 1: Connect Google Ads to the Google Sheet
Now that you have a blank script file to work with, let’s start building the export.
First up is to create a connection between Google Ads and the Google Sheet you made earlier. This can be done by inserting these two lines of code, after
function main() {
but before the closing braces
}
function main() {
//Step 1: Connect Google Ads to the Google Sheet
var spreadsheetUrl = 'YOUR URL GOES HERE';
var spreadsheet = SpreadsheetApp.openByUrl(spreadsheetUrl);
var ss = spreadsheet.getSheetByName('Sheet1');
ss.clear();
}
In the first line, simply replace the text YOUR URL GOES HERE between the parenthesis with the URL of your Google Sheet. NOTE: The Google spreadsheet URL must be completed with a slash.
In the third line with the .getSheetByName portion, replace Sheet1 with the name of the specific tab you’re exporting the data to.
By calling the function ss.clear(); we are simply telling Google to clear any alerts in the Google Sheet that might have been added in a prior run of the script.
Step 2: Create an Array to Store the Data
Next, we’ll need to create an array to store all the data we’re going to pull out of Google Ads. This is simple enough–you only need to add another line of code, below the previous spreadsheet.getSheetByName one, but before the closing braces:
| var sheetarray = [[‘keyword’, ‘clicks’, ‘conversions’]]; |
You can view the sheetarray as a list of the rows for your spreadsheet, with each item in the list being another list that contains the columns of your spreadsheet. So sheetarray is sort of a list-of-list; an array of arrays.
You’d probably notice the words ‘keyword’, ‘clicks’, and ‘conversions’ in between the brackets. These are actually going to be the headers of the columns in your Google Sheet. So if you’re exporting a different set of data fom Google Ads, you can simply replace the words inside the single quotation marks ” or even add your own columns.
Keep in mind that changing these headers won’t have any impact on the data you’ll be exporting from Google Ads. It’s just text for the Google sheet. Hence, it’s important to always double-check this line–you might end up exporting campaign names under the “keyword” header if you change some of the code to get campaign data instead of keyword data.
Step 3: Collect the Keywords You Need from Google Ads
Now it’s time for the most important step in this script: actually collecting the keywords from Google Ads.
To do this, we’ll need to add the following block of code:
var keywords = AdsApp.keywords()
.withCondition("Clicks > 100")
.withCondition("Conversions = 0")
.forDateRange("THIS_MONTH")
.get();
First off is var keywords = AdsApp.keywords(). Like the previous lines of code that have var in them, it just basically tells Google Ads (AdsApp) to get the keywords (.keywords()) and store all the collected keywords in a variable named “keywords.”
Of course, for the purposes of this script, we don’t want to collect all keywords that are available in Google Ads. That’s where the second two lines for .withCondition comes in. As the line suggests, you’ll insert the criteria for narrowing down your data pull in these lines. So in the script we’re currently writing, we’re basically telling Google Ads to get only the keywords that have more than 100 clicks and 0 conversions:
| .withCondition(“Clicks > 100”) .withCondition(“Conversions = 0”) |
If you want to add or remove conditions for your data pull, you can just add, delete the .withCondition lines. You can change conditions by editing the values in the parentheses. More information about available conditions can be found in the Google Developers documentation here.
Next line in the code is another self-explanatory line: .forDate. This allows you to specify the date range to pull the data from.
In our example, we’re using one of Google’s preset time ranges: THIS_MONTH.
Other preset options include the following:
-
- TODAY
- YESTERDAY
- LAST_7_DAYS
- THIS_WEEK_SUN_TODAY
- LAST_WEEK
- LAST_14_DAYS
- LAST_30_DAYS
- LAST_BUSINESS_WEEK
- LAST_WEEK_SUN_SAT
- THIS_MONTH
- LAST_MONTH
- ALL_TIME
For example, to pull the data for all time periods, you will need the following line:
| .forDateRange(“ALL_TIME”) |
You can also use the following format: (“YYYYMMDD”, “YYYYMMDD”). The first date inside these parenthesis is the start date, while the last one is obviously the end date. This option is used for retrieving data in a specific time window. But if you want recurring reports, then the presets are a better option, as the presets do adjust dynamically based on today’s date.
It’s also important to note that unlike .withCondition, which is optional, .forDateRange is required. Your script will display an error message when you try to run it without this line.
After specifying the date range, it’s possible to add a limit: .withLimit. This basically just tells Google Ads to only get the first x number of entries that match this criteria and discard the rest.
Let’s create a variation of the code below, for illustration purposes:
var keywords = AdsApp.keywords()
.withCondition("Impressions > 2000")
.withCondition("Ctr < 0.05")
.forDateRange("LAST_30_DAYS")
.withLimit(10)
.get();
In this example, we select the first 10 keywords that have had over 2000 impressions in the last 30 days, with a Ctr of less than 5%. To adjust the number of entries that we should get per data pull, simply change the number inside the .withLimit parenthesis.
And now that we’ve outlined all the criteria we have for the data pull, we shouldn’t forget the most important part: .get();. As the code implies, it just tells Google Ads to get the keywords that meet all the criteria we’ve listed above.
Step 4: Add the Collected Data into the Array
Once we’ve identified and pulled the keywords we need out of Google Ads, we’ll need to get the keyword data and store it somewhere. That’s where the sheetarray array we created earlier comes in.
Add this block of code next:
while (keywords.hasNext()) {
var keyword = keywords.next();
sheetarray.push([
keyword.getText(),
keyword.getStatsFor("THIS_MONTH").getClicks(),
keyword.getStatsFor("THIS_MONTH").getConversions()
]);
}
Basically, this block of code says that as long as there are still some keywords from the keywords pull we did earlier from Google Ads (while (keywords.hasNext()), we’ll need to add them into the array (.push) called sheetarray.
In Step 3’s data pull, we just identified all the keywords that have more than 100 clicks and 0 conversions. Now, in this step, it’s time to get the actual data for these keywords : its keyword text (keyword.getText()), clicks (keyword.getStatsFor(“THIS_MONTH”).getClicks()) and conversions (keyword.getStatsFor(“THIS_MONTH”).getConversions()), then add them all in the array (sheetarray.push).
Step 5: Display Contents of the Array
Just to verify if we were actually able to pull any data out of Google Ads and if it’s properly added to the array, we’ll add this line right after:
| Logger.log(sheetarray); |
This line basically tells Google Ads to show all the contents of sheetarray — the array that now contains all the data we pulled earlier. Convenient, right?
The contents will show up in the Logs section of Google Ads scripts, which pops up when you preview the script.
But why do we need this step? It helps us verify if Google Ads was able to pull any valid data based on our requirements. If no other message shows up, then it means that Google Ads wasn’t able to collect any data at all. So it’s just a check for our piece of mind to see if the script is working.
Step 6: If we have some keywords to report , send the Array Data to the Google Sheet
If there are some keywords that meet our criteria and want to report, let’s do so by first putting all the contents of the array into the Google Sheet.
To do so, we’ll need to add this line of code:
| ss.getRange(1, 1, sheetarray.length, sheetarray[0].length).setValues(sheetarray); |
Let’s break this down part-by-part.
ss.getRange() basically tells Google to select the range of cells contained in the spreadsheet’s sheet that is stored in the variable ss, based on the instructions inside the parentheses. If you can recall, in Step 1 we used ss to point to the exact tab in Google Sheets that we want to use for storing the data.
So what’s inside the parentheses?
The first two items, 1, 1, simply say that you need to select the cells starting from the first row and the first column of the sheet. The last two items sheetarray.length and sheetarray[0].length mean that the number of rows in the range of selected cells should be as large as the length (number of items) in the array.
Take note that the information stored in the array is similar to a table with rows and columns. The sheetarray.length refers to the number of rows, while the number in sheetarray[0].length refers to the number of columns.
Finally, the last part .setValues(sheetarray); simply tells Google Ads to place the values we stored in the array into the range of cells we’ve selected in .getRange earlier.
Step 7: Send the email
As a final step, we need to send the email:
MailApp.sendEmail("INSERT YOUR EMAIL ADDRESS HERE", "Keywords with ZERO conversions", "Here's the link: "+spreadsheetUrl);
Again, simply replace the text INSERT YOUR EMAIL ADDRESS HERE between the parenthesis and you are good to go.
And we’re done! You can now run the script and see what it exports in the Google Sheet. If there’s no available data, double-check if you have any keywords that match the criteria–you may need to reduce the threshold for the number of clicks required.
To recap, scroll down below to see the completed script. read it line by line and see if you understand what is happening.
ACTION:
Copy-paste the script, save, hit preview and then start tinkering with the code to customize your alerts!
Happy scripting!
Full Sample Script – Export Keywords with 100 Clicks and 0 Conversions
function main() {
//Step 1: Connect Google Ads to the Google Sheet
var spreadsheetUrl = 'YOUR URL GOES HERE';
var spreadsheet = SpreadsheetApp.openByUrl(spreadsheetUrl);
var ss = spreadsheet.getSheetByName('Sheet1');
ss.clear();
//Step 2: Create an array to store the data
var sheetarray = [['keyword', 'clicks', 'conversions']];
//Step 3: Collect the data you need from Google Ads
var keywords = AdsApp.keywords()
.withCondition("Clicks > 20")
.withCondition("Conversions = 0")
.forDateRange("THIS_MONTH")
.get();
//Step 4: Add the data you got from Google Ads into the array
while (keywords.hasNext()) {
var keyword = keywords.next();
sheetarray.push([
keyword.getText(),
keyword.getStatsFor("THIS_MONTH").getClicks(),
keyword.getStatsFor("THIS_MONTH").getConversions()
]);
}
//Step 5: Display the contents of the array
Logger.log(sheetarray);
if (sheetarray.length > 0) {
// Step 6: Send the array's data to the Google Sheet
ss.getRange(1, 1, sheetarray.length, sheetarray[0].length).setValues(sheetarray);
// Step 7: Send email with link to Google Sheet
MailApp.sendEmail("INSERT YOUR EMAIL ADDRESS HERE", "Keywords with ZERO conversions", "Here's the link: "+spreadsheetUrl);
}
}
Your website is a treasure!
"Thank you Nils, yesterday I was reading your blog and it's simply amazing. I work with Google Ads since 6-7 years, last year I managed 2M only in Italy but it's very hard to meet expert google ads specialist and it's impossible to meet someone talking about scripts. Your website is a tresure!"
Luigi Virginio, SEO & SEM Specialist - Milan, Italy
Join thousands of PPC geeks who already have access:
If the button above isn’t working for you, you can sign up here to get access.