Author Archive

Easy to Implement AdWords Scripts

September 24, 2015 Leave a comment

If you are looking for a handful of scripts to quickly and easily implement in your AdWords account to help with automation today then you are in the right place. Before you get started I recommend that you download a free tool like Notepad++ to make it easier to make the few edits you’ll need to make before the scripts can execute. It also makes the “Find” function work a bit easier!

Elements to Update In Pre-Written Scripts

Many of the scripts have a few variables that you’ll want to update so that your performance data is written within your own Google Docs or so that you can receive the email status updates. If you use a tool like Notepad++ you can do a quick “Find” in script to identify the following variables and update the blank placeholders with your information. Search for the following:

  • Spreadsheet_URL
  • Email or Recipient_Email

The next thing that is quick and easy for you to update in a given script is the date range or time frame that the script is pulling data or information. What you can look for in the scripts is the date range selector forDateRange(‘LAST_WEEK’). You can edit and replace the date range ‘LAST_WEEK’ for any of the following pre-specified ranges: 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. Just remember if you choose ALL_TIME and you have an account with a detailed history it might cause the script to time out because there is too much data. If you get a time out error, choose a shorter timeframe and rerun the script!

Pre-Written AdWords Scripts:

  • Search Query Manager Script

    Marcela De Vivo of shared this script that allows you to insert some specific criteria, such as threshold minimums for impressions/clicks/spend, and then pulls queries meeting criteria that are not keywords within your current account. It makes negative keyword audits and keyword expansions quick and easy. If you only implement one script, this should be it!

  • Find Your Best And Worst Search Queries Using N-Grams

    Most digital marketers haven’t heard of “N-Grams”, but what you can take away from this script is that it breaks apart keywords into 1 – 3 word phrases (N-Grams) and aggregates their performance data to help you identify wasted ad spend and areas for potential keyword expansions. This delightfully helpful script was Written by Daniel Gilbert of Brainlabs Digital. This is a brilliant script to help simplify some very difficult coding from XLS to get similar results.

  • Broken URL Checker

    There are two Broken URL scripts that are both great that do very similar tasks: look at the destination URLs, at the keyword or ad copy level, and see if they point to a page that is providing a 301, 302, 404 error or that might have an “Out Of Stock” message. This will save you time, $$ money $$, and potential embarrassment from sending traffic to a broken page. The Google URL Checker was developed by the Google Developers team and is part of the Script Solutions. The second is the Broken URL Checker by Russ Savage of

    The caveat is that the FreeAdWordsScripts will need minor editing to change the Javascript code updating the “Destination URL” to the “Final URL” thanks to Google’s Upgraded URL project in July.

  • Keyword Campaign Performance Script

    Written by Russ Savage of it creates a quick and simple report into 4 tabs that give you the all up campaign performance, last 7 days campaign performance, all up keywords performance and the keyword performance by keyword for the last 7 days. It’s a quick and easy way to automate high level reporting.

  • Quality Score Performance Scripts

    There are two quality score scripts that do similar types of tracking, but you might like one over the other so I’m listing them both for your reference. The first and my favorite is from Martin Roettgerding, aka @BloomArty, Script to Track Keyword Quality Score Over Time. If you’ve ever been frustrated trying to show that the optimizations you’ve been making are making an impact, you can leverage this script to track your quality score over time.

    The second is the Google Developers QS Script that looks at Quality Score by Rank and by Keyword Position.

    There is no harm in implementing both scripts and they’ll give you slight different variations of information to look at for reporting and digging in for insights in the future.

  • Anomaly Detector

    This script was written by the AdWords Developers and it sends an email alert to you when your account is behaving differently than the historical performance. Use it as a once a day sanity check to make sure that you’re accounts haven’t had any major swings or schedule it to run hourly for mid-day check ins.

  • Advertisements
Categories: Uncategorized

Updating Destination URL to Final URL in AdWords Scripts

September 4, 2015 Leave a comment

A colleague, Sam James, sent a great reminder earlier this morning that if you’ve been using AdWords scripts to monitor broken URLs that the process in migrating to “Upgraded URLs” most likely broke the script.

In order to fix your scripts so that they’ll pull from the Final URL field instead of the Destination URL field make the following quick substitution in the code:

Find instances of :  getDestinationUrl

and replace with:  urls().getFinalUrl

Here is an example of the fix in place for [Russ Savage’s] Broken Link Tracker Script:

* Find Broken Urls In Your Account
* Version 1.1
* ChangeLog v1.2
* Changed by Christi Olson to update to Final URLs instead of Destination URLs
*  - Updated to only see Text Ads
* Created By: Russ Savage
function main() {
  // You can add more if you want:
  var BAD_CODES = [404,500];
  var TO = [''/*,''*/];
  var SUBJECT = 'Broken Url Report - ' + _getDateString();
  var HTTP_OPTIONS = {
  //Let's look at ads and keywords for urls
  var iters = [
    //For Ad Level Urls
      .withCondition("Status = 'ENABLED'")
      .withCondition("AdGroupStatus = 'ENABLED'")
      .withCondition("CampaignStatus = 'ENABLED'")
      .withCondition("Type = 'TEXT_AD'")
    //For Keyword Level Urls
      .withCondition("Status = 'ENABLED'")
      .withCondition("DestinationUrl != ''")
      .withCondition("AdGroupStatus = 'ENABLED'")
      .withCondition("CampaignStatus = 'ENABLED'")
  var already_checked = {}; 
  var bad_entities = [];
  for(var x in iters) {
    var iter = iters[x];
    while(iter.hasNext()) {
      var entity =;
      if(entity.urls().getFinalUrl == null) { continue; }
      var url = entity.urls().getFinalUrl;
      if(url.indexOf('{') >= 0) {
        //Let's remove the value track parameters
        url = url.replace(/\{[0-9a-zA-Z]+\}/g,'');
      if(already_checked[url]) { continue; }
      var response_code;
      try {
        Logger.log("Testing url: "+url);
        response_code = UrlFetchApp.fetch(url, HTTP_OPTIONS).getResponseCode();
      } catch(e) {
        //Something is wrong here, we should know about it.
        bad_entities.push({e : entity, code : -1});
      if(BAD_CODES.indexOf(response_code) >= 0) {
        //This entity has an issue.  Save it for later. 
        bad_entities.push({e : entity, code : response_code});
      already_checked[url] = true;
  var column_names = ['Type','CampaignName','AdGroupName','Id','Headline/KeywordText','ResponseCode','DestUrl'];
  var attachment = column_names.join(",")+"\n";
  for(var i in bad_entities) {
    attachment += _formatResults(bad_entities[i],",");
  if(bad_entities.length > 0) {
    var options = { attachments: [Utilities.newBlob(attachment, 'text/csv', 'bad_urls_'+_getDateString()+'.csv')] };
    var email_body = "There are " + bad_entities.length + " urls that are broken. See attachment for details.";
    for(var i in TO) {
      MailApp.sendEmail(TO[i], SUBJECT, email_body, options);
//Formats a row of results separated by SEP
function _formatResults(entity,SEP) {
  var e = entity.e;
  if(typeof(e['getHeadline']) != "undefined") {
    //this is an ad entity
    return ["Ad",
  } else {
    // and this is a keyword
    return ["Keyword",
//Helper function to format todays date
function _getDateString() {
  return Utilities.formatDate((new Date()), AdWordsApp.currentAccount().getTimeZone(), "yyyy-MM-dd");

Categories: adwords

Excel Function to Convert a Date to the Month

Today I was attempting to pull a PPC report for historical by month over the last three years and wanted to easily convert the date into a month format so that i could show the YoY trendlines for each full calendar year as a unique line within the chart. It frustrates me that Excel only has a function that converts a date to a number that represents the month. Below is a the quick and dirty XLS formula to take that number representation and convert it to the actual month name.

Access a Notepad++ Version of the XLS code here.

Replace B1 in the example with the date based reference cell and voila!


Happy chart making Thursday to you!

Categories: Uncategorized

Comprehensive PPC Audit Checklist and Scorecard Wijnand Meijer

In March I posted links to Wijnand Meijer’s PPC Audit guide which contains a lot of great insights about auditing your paid search campaigns. I had the pleasure of meeting with Wijnand at the 2015 PPC Hero Conference in Portland, OR last month. If you are still looking at best practices for completing your own PPC Account Audit I’d recommend checking out his presentation Re-evaluating PPC Success, the Value of PPC Audits.

The biggest takeaway from his presentation was his sharing of a new PPC audit scorecard and checklist that takes the best practices from his Certified Knowledge blog series and places it into a single word document.

Happy Account Auditing!

Categories: Uncategorized

Updates to How Bing Matches Keywords

Bing announced two items today. First is that Bing will be removing the opt-out functionality for Close Variant Matching on May 21st, 2015. The second announcement was related to how they match keywords to search queries. On May 21st, 2015, Bing will be adjusting how they normalize queries by adding back in what they called “Stop Words” which means terms like “is, a, an, what, the, and, at, to, of, or, about, for, with, on, from, by, ‘ve” to the search query. Additionally with the normalization it means that Bing will also start to show queries with accent characters like “á, ä, é, ә, í, ö, ó, ú, ü, ñ, ß, ӕ, и.”

Review your keyword list and SQRs to add in normalized queries and special characters by May 21st!

What is the action item? If you are currently bidding on broad match, it might not be much. However if you have a majority of phrase and exact match keywords make sure you’ve evaluated your SQR to add in phrase and exact match keywords with special characters and Stop Words.

Categories: Bing, SEM

The Most In-depth PPC Audit Guide

I spend alot of time reading blogs and coming up with projects and tasks for my PPC team. Earlier this year I started a project of using various audit guides to create an extensive PPC Audit Checklist. As I was combing through hundreds of blogs I came across the following series on Brad Geddes (@bgtheory) Certified Knowledge blog and I was blown away. It was not only the most indepth series I have come across, but it also goes to great detail to explain the WHY’s behind why a specific setting or feature should be used. I am in love! I had been planning on writing my own series, but instead I’m going to piggy back off of this amazingly in-depth series to include some additional insights or information of what else you can include in your account audits.

1. Goal Setting
2. Measurement
3. Campaign Settings & Bid Adjustments
4. Ad Extensions
5. Impression Share and Auction Insights
6. Quality Score
7. Account Structure
8. Keywords and Match Types
9. Ad Copy
10. Testing
11. The Lin Rodinsky Ratio

XLS formula to remove Characters from the Keyword column

January 23, 2015 Leave a comment

One of the projects I am working on is to make a template to help standardize the Search Query Report (SQR) process for adding positive and negative keywords. I’ve been racking my brain for the last two days on how to automate some aspects of the SQR from Adwords so that we can more easily identify words to add as negatives.

I kept on getting stuck on something fairly simple: how to remove the match type identifiers from the keyword column. (ie “, +, [,]). I knew that i could use a substitute function to remove the identifiers, but as I embedded the functions together I kept on receiving an Error value.

So before i get started into the solutions, I want to start with what is the substitute function?

Excel Substitute Function

The excel substitute function is a simple function that allows you to identify a cell and find -something-, replace it with something else, and specify if you want it to happen a certain number of times or in every instance.
=substitute(text, old_text, new_text, [instance number])

So in my example, I want to use substitutes on a phrase match keyword to find the quotation marks surrounding the keyword and replace them with “” (NOTHING.) For broad modified keywords, i.e. +keyword, i want to find the + symbol and again replace it with “” nothing. For exact match keywords i want to identify the brackets [] and again replace them with “” nothing. This will give me just the keyword.

The next step is how to use the substitute function to do this, and where I went wrong…

Remove PPC Keyword Match Type Identifiers from the Keyword Column using Embedded Substitute Functions

A really really smart co-worker, Sam James, came up with the following solution that involves embedded substitutes with right and left trim functions. This works and is 100% functional and it removes all of the match type identifiers from the keyword column.

In this example the KEYOWORD column is B. So we’re going to be referencing cell B2 because it is the first row in column B with a keyword.


As I mentioned this is 100% accurate. However, my brain has a really hard time processing the logic because of its complexity. I knew that there had to be an easier more straight forward method. I also decided that instead of hardcoding in the characters like quotation mark, i.e. “”””” four quotes in a row identifies a single text quotation mark, “[” identifies the left bracket in text, i would use the ASCII character identifier so I would be less likely of screwing up the substitutes.

My original embedded substitute was failing because I kept on leaving a additional comma after the new_text. i.e. =SUBSTITUTE(SUBSTITUTE(B2,CHAR(34),””,),CHAR(91),””,)). My logic was that I wanted to substitute for all instances, however it was expecting a value after the comma so it caused my formula to give me the dreaded #VALUE symbol.

After pulling my hair, screaming bloody murder and wanting to cry. I used Sam’s logic above and removed the extra comma.


Trim removes extra spaces including a proceeding space incase your first word in the keyword phrase was broad modified.
This works! Wohoo. Now on to creating a formula to idenifty when a search query term matches exactly to a exact match term so I know where to add NEGATIVES. 🙂

When I’m done with all of this goodness it’ll be posted to the blog.

Happy Friday!

Categories: Uncategorized