Setting Up Keyword Tracking in Google Ads

How to Use the Excel Template (in Google Ads Editor)

  • Export KW view from Campaign or ad group you want to ad tracking template too
  • Fill in the columns shown in sheet 2
  • Copy & Paste the Formula, as values, into the Tracking template column.
  • Find and Replace “space” with %20 in the Tracking Template column
  • Delete the ‘working out’ columns without headers and the formula column. Copy the populated rows and columns. And import into editor
  • Select all with headers – go to Editor – Account – Import – paste from clipboard.
  • Make sure no campaigns or anything are added (just add the tracking!)

When I used this in 2020, Editor would crash if I tried to upload more than 1 campaign’s tracking at a time. So it took ages!

Google Ads Reporting Script

This script is no longer live, but I had it in my account, I’m pasting it here for safekeeping and future reference as it is pretty handy:

to use the script:

Change the Google sheet/doc URL and the email address

  • Sign into Google Ads
  • Click on “Tools & Settings” on the top menu near the right hand side
  • Click “scripts”
  • Click the addition (+) sign to add a new script
  • Paste in the below script – save – preview and then run

// Copyright 2015, Google Inc. All Rights Reserved.
//
// Licensed under the Apache License, Version 2.0 (the "License");
// you may not use this file except in compliance with the License.
// You may obtain a copy of the License at
//
//     http://www.apache.org/licenses/LICENSE-2.0
//
// Unless required by applicable law or agreed to in writing, software
// distributed under the License is distributed on an "AS IS" BASIS,
// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
// See the License for the specific language governing permissions and
// limitations under the License.

/**
 * @name Account Summary Report
 *
 * @overview The Account Summary Report script generates an at-a-glance report
 *     showing the performance of an entire Google Ads account. See
 *     https://developers.google.com/google-ads/scripts/docs/solutions/account-summary
 *     for more details.
 *
 * @author Google Ads Scripts Team [adwords-scripts@googlegroups.com]
 *
 * @version 1.1
 *
 * @changelog
 * - version 1.1
 *   - Add user-updateable fields, and ensure report row ordering.
 * - version 1.0.4
 *   - Improved code readability and comments.
 * - version 1.0.3
 *   - Added validation for external spreadsheet setup.
 * - version 1.0.2
 *   - Fixes date formatting bug in certain timezones.
 * - version 1.0.1
 *   - Improvements to time zone handling.
 * - version 1.0
 *   - Released initial version.
 */

var RECIPIENT_EMAIL = 'drewgriffiths@live.com';

var SPREADSHEET_URL = 'https://docs.google.com/spreadsheets/d/1mNjc7iJWOIq580DMLf6rYKT8xRAcl2MynnGSY29UiXY/edit#gid=3';

/**
 * Configuration to be used for running reports.
 */
var REPORTING_OPTIONS = {
  // Comment out the following line to default to the latest reporting version.
  apiVersion: 'v201809'
};

/**
 * To add additional fields to the report, follow the instructions at the link
 * in the header above, and add fields to this variable, taken from the Account
 * Performance Report reference:
 * https://developers.google.com/adwords/api/docs/appendix/reports/account-performance-report
 */
var REPORT_FIELDS = [
  {columnName: 'Cost', displayName: 'Cost'},
  {columnName: 'AverageCpc', displayName: 'Avg. CPC'},
  {columnName: 'Ctr', displayName: 'CTR'},
  {columnName: 'Impressions', displayName: 'Impressions'},
  {columnName: 'Clicks', displayName: 'Clicks'}
];

function main() {
  Logger.log('Using spreadsheet - %s.', SPREADSHEET_URL);
  var spreadsheet = validateAndGetSpreadsheet();
  spreadsheet.setSpreadsheetTimeZone(AdsApp.currentAccount().getTimeZone());
  spreadsheet.getRangeByName('account_id_report').setValue(
      AdsApp.currentAccount().getCustomerId());

  var yesterday = getYesterday();
  var date = getFirstDayToCheck(spreadsheet, yesterday);

  var rows = [];
  var existingDates = getExistingDates();

  while (date.getTime() <= yesterday.getTime()) {
    if (!existingDates[date]) {
      var row = getReportRowForDate(date);
      rows.push([new Date(date)].concat(REPORT_FIELDS.map(function(field) {
        return row[field.columnName];
      })));
      spreadsheet.getRangeByName('last_check').setValue(date);
    }
    date.setDate(date.getDate() + 1);
  }

  if (rows.length > 0) {
    writeToSpreadsheet(rows);

    var email = spreadsheet.getRangeByName('email').getValue();
    if (email) {
      sendEmail(email);
    }
  }
}

/**
 * Retrieves a lookup of dates for which rows already exist in the spreadsheet.
 *
 * @return {!Object} A lookup of existing dates.
 */
function getExistingDates() {
  var spreadsheet = validateAndGetSpreadsheet();
  var sheet = spreadsheet.getSheetByName('Report');

  var data = sheet.getDataRange().getValues();
  var existingDates = {};
  data.slice(5).forEach(function(row) {
    existingDates[row[1]] = true;
  });
  return existingDates;
}

/**
 * Sorts the data in the spreadsheet into ascending date order.
 */
function sortReportRows() {
  var spreadsheet = validateAndGetSpreadsheet();
  var sheet = spreadsheet.getSheetByName('Report');

  var data = sheet.getDataRange().getValues();
  var reportRows = data.slice(5);
  if (reportRows.length) {
    reportRows.sort(function(rowA, rowB) {
      if (!rowA || !rowA.length) {
        return -1;
      } else if (!rowB || !rowB.length) {
        return 1;
      } else if (rowA[1] < rowB[1]) {
        return -1;
      } else if (rowA[1] > rowB[1]) {
        return 1;
      }
      return 0;
    });
    sheet.getRange(6, 1, reportRows.length, reportRows[0].length)
        .setValues(reportRows);
  }
}

/**
 * Append the data rows to the spreadsheet.
 *
 * @param {Array<Array<string>>} rows The data rows.
 */
function writeToSpreadsheet(rows) {
  var access = new SpreadsheetAccess(SPREADSHEET_URL, 'Report');
  var emptyRow = access.findEmptyRow(6, 2);
  if (emptyRow < 0) {
    access.addRows(rows.length);
    emptyRow = access.findEmptyRow(6, 2);
  }
  access.writeRows(rows, emptyRow, 2);
  sortReportRows();
}

function sendEmail(email) {
  var day = getYesterday();
  var yesterdayRow = getReportRowForDate(day);
  day.setDate(day.getDate() - 1);
  var twoDaysAgoRow = getReportRowForDate(day);
  day.setDate(day.getDate() - 5);
  var weekAgoRow = getReportRowForDate(day);

  var html = [];
  html.push(
    '<html>',
      '<body>',
        '<table width=800 cellpadding=0 border=0 cellspacing=0>',
          '<tr>',
            '<td colspan=2 align=right>',
              "<div style='font: italic normal 10pt Times New Roman, serif; " +
                  "margin: 0; color: #666; padding-right: 5px;'>" +
                  'Powered by Google Ads Scripts</div>',
            '</td>',
          '</tr>',
          "<tr bgcolor='#3c78d8'>",
            '<td width=500>',
              "<div style='font: normal 18pt verdana, sans-serif; " +
              "padding: 3px 10px; color: white'>Account Summary report</div>",
            '</td>',
            '<td align=right>',
              "<div style='font: normal 18pt verdana, sans-serif; " +
              "padding: 3px 10px; color: white'>",
               AdsApp.currentAccount().getCustomerId(), '</h1>',
            '</td>',
            '</tr>',
          '</table>',
          '<table width=800 cellpadding=0 border=0 cellspacing=0>',
            "<tr bgcolor='#ddd'>",
              '<td></td>',
              "<td style='font: 12pt verdana, sans-serif; " +
                  'padding: 5px 0px 5px 5px; background-color: #ddd; ' +
                  "text-align: left'>Yesterday</td>",
              "<td style='font: 12pt verdana, sans-serif; " +
                  'padding: 5px 0px 5px 5px; background-color: #ddd; ' +
                  "text-align: left'>Two Days Ago</td>",
              "<td style='font: 12pt verdana, sans-serif; " +
                  'padding: 5px 0px 5x 5px; background-color: #ddd; ' +
                  "text-align: left'>A week ago</td>",
            '</tr>');
  REPORT_FIELDS.forEach(function(field) {
    html.push(emailRow(
        field.displayName, field.columnName, yesterdayRow, twoDaysAgoRow,
        weekAgoRow));
  });
  html.push('</table>', '</body>', '</html>');
  MailApp.sendEmail(email, 'Google Ads Account ' +
      AdsApp.currentAccount().getCustomerId() + ' Summary Report', '',
      {htmlBody: html.join('\n')});
}

function emailRow(title, column, yesterdayRow, twoDaysAgoRow, weekAgoRow) {
  var html = [];
  html.push('<tr>',
      "<td style='padding: 5px 10px'>" + title + '</td>',
      "<td style='padding: 0px 10px'>" + yesterdayRow[column] + '</td>',
      "<td style='padding: 0px 10px'>" + twoDaysAgoRow[column] +
          formatChangeString(yesterdayRow[column], twoDaysAgoRow[column]) +
          '</td>',
      "<td style='padding: 0px 10px'>" + weekAgoRow[column] +
          formatChangeString(yesterdayRow[column], weekAgoRow[column]) +
          '</td>',
      '</tr>');
  return html.join('\n');
}


function getReportRowForDate(date) {
  var timeZone = AdsApp.currentAccount().getTimeZone();
  var dateString = Utilities.formatDate(date, timeZone, 'yyyyMMdd');
  return getReportRowForDuring(dateString + ',' + dateString);
}

function getReportRowForDuring(during) {
  var report = AdsApp.report(
      'SELECT ' +
          REPORT_FIELDS
              .map(function(field) {
                return field.columnName;
              })
              .join(',') +
          ' FROM ACCOUNT_PERFORMANCE_REPORT ' +
          'DURING ' + during,
      REPORTING_OPTIONS);
  return report.rows().next();
}

function formatChangeString(newValue,  oldValue) {
  var x = newValue.indexOf('%');
  if (x != -1) {
    newValue = newValue.substring(0, x);
    var y = oldValue.indexOf('%');
    oldValue = oldValue.substring(0, y);
  }

  var change = parseFloat(newValue - oldValue).toFixed(2);
  var changeString = change;
  if (x != -1) {
    changeString = change + '%';
  }

  if (change >= 0) {
    return "<span style='color: #38761d; font-size: 8pt'> (+" +
        changeString + ')</span>';
  } else {
    return "<span style='color: #cc0000; font-size: 8pt'> (" +
        changeString + ')</span>';
  }
}

function SpreadsheetAccess(spreadsheetUrl, sheetName) {
  this.spreadsheet = SpreadsheetApp.openByUrl(spreadsheetUrl);
  this.sheet = this.spreadsheet.getSheetByName(sheetName);

  // what column should we be looking at to check whether the row is empty?
  this.findEmptyRow = function(minRow, column) {
    var values = this.sheet.getRange(minRow, column,
        this.sheet.getMaxRows(), 1).getValues();
    for (var i = 0; i < values.length; i++) {
      if (!values[i][0]) {
        return i + minRow;
      }
    }
    return -1;
  };
  this.addRows = function(howMany) {
    this.sheet.insertRowsAfter(this.sheet.getMaxRows(), howMany);
  };
  this.writeRows = function(rows, startRow, startColumn) {
    this.sheet.getRange(startRow, startColumn, rows.length, rows[0].length).
        setValues(rows);
  };
}

/**
 * Gets a date object that is 00:00 yesterday.
 *
 * @return {Date} A date object that is equivalent to 00:00 yesterday in the
 *     account's time zone.
 */
function getYesterday() {
  var yesterday = new Date(new Date().getTime() - 24 * 3600 * 1000);
  return new Date(getDateStringInTimeZone('MMM dd, yyyy 00:00:00 Z',
      yesterday));
}

/**
 * Returned the last checked date + 1 day, or yesterday if there isn't
 * a specified last checked date.
 *
 * @param {Spreadsheet} spreadsheet The export spreadsheet.
 * @param {Date} yesterday The yesterday date.
 *
 * @return {Date} The date corresponding to the first day to check.
 */
function getFirstDayToCheck(spreadsheet, yesterday) {
  var last_check = spreadsheet.getRangeByName('last_check').getValue();
  var date;
  if (last_check.length == 0) {
    date = new Date(yesterday);
  } else {
    date = new Date(last_check);
    date.setDate(date.getDate() + 1);
  }
  return date;
}

/**
 * Produces a formatted string representing a given date in a given time zone.
 *
 * @param {string} format A format specifier for the string to be produced.
 * @param {date} date A date object. Defaults to the current date.
 * @param {string} timeZone A time zone. Defaults to the account's time zone.
 * @return {string} A formatted string of the given date in the given time zone.
 */
function getDateStringInTimeZone(format, date, timeZone) {
  date = date || new Date();
  timeZone = timeZone || AdsApp.currentAccount().getTimeZone();
  return Utilities.formatDate(date, timeZone, format);
}

/**
 * Validates the provided spreadsheet URL to make sure that it's set up
 * properly. Throws a descriptive error message if validation fails.
 *
 * @return {Spreadsheet} The spreadsheet object itself, fetched from the URL.
 */
function validateAndGetSpreadsheet() {
  if ('YOUR_SPREADSHEET_URL' == SPREADSHEET_URL) {
    throw new Error('Please specify a valid Spreadsheet URL. You can find' +
        ' a link to a template in the associated guide for this script.');
  }
  var spreadsheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
  var email = spreadsheet.getRangeByName('email').getValue();
  if ('foo@example.com' == email) {
    throw new Error('Please either set a custom email address in the' +
        ' spreadsheet, or set the email field in the spreadsheet to blank' +
        ' to send no email.');
  }
  return spreadsheet;
}

Bob’s your uncle

The code was originally on the Google Developer’s site here – https://developers.google.com/adwords/scripts/docs/solutions/ad-performance

Please Google, don’t bankrupt me for using the script on here! I’ll take it down if it upsets thee.

KW Research in 2021

Hello,

Just thought I’d write a post about how I go about doing my SEO & PPC Keyword Research these days.

  1. Add head term to google search bar in chrome

Make a note of the suggested searches & predicted searches

Screen Shot 2019-07-22 at 12.29.22
Predicted Searches in the Address/Search Bar
suggested google searches
Related/Suggested Searches at the Bottom

2. Google ‘Alphabet Soup’

Put in your main head term and then add a

then b, then c and so on

Make a note of the relevant suggestions

Screen Shot 2019-07-22 at 12.33.18
Screen Shot 2019-07-22 at 12.33.21
Screen Shot 2019-07-22 at 12.33.23

For product related KWs, this also works if you go to the desktop version of the Google Play Store.

After each search, check out the related search terms at the bottom of each SERP.

3. Have a Quick Look on Reddit & Amazon

Have a look at any relevant subreddits on Reddit – e.g. https://www.reddit.com/r/jeffbridges/

Do a quick search for “KW” site:reddit.com

Screen Shot 2019-07-22 at 12.41.49.png

Have a look on Amazon, just search your head term and see what products appear

Screen Shot 2019-07-22 at 12.42.40.png

In the example above .- “The Dude and the Zen Master” might be a decent KW

Other forums can help too. For example, when looking for KWs for my MMA blog, I’ll look on sherdog forums for trending & frequent topics.

4. Add Competitor Domains to Ubersuggest

https://neilpatel.com/ubersuggest/ or use the SEMRush plugin.

If necessary, we could export the KWs in this report, and then filter in Excel for those containing “Jeff Bridges”

Screen Shot 2019-07-22 at 12.46.34.png

5. Upload your Final KW List to Keyword Planner

https://ads.google.com/intl/en_uk/home/tools/keyword-planner/

A final note on search volumes.

For some blogs and websites, even keywords with 0 monthly searches may be relevant.

My other blog – blackbeltwhitehat.com has built all of its traffic off KWs that Google KW planner says has 0 searches.

It all depends on how authoritative your website is and your competitors are. You can go after bigger, more popular KWs if you are a huge website with a DA of 90. It’s a different ball game if you are running a personal blog with a DA of 15

Try and include a number of the relevant searches in your articles etc.

Beginner’s Guide to Online Marketing

online marketing

My friend has an interview on Tuesday for a marketing manager role, and has never done any online marketing, so here goes my introduction to it…

About the title-image, sorry it’s a bit sexist – studies suggest that faces looking at you are the best for Calls to Action (CTAs) and for thumbnails on Youtube videos.

The above image is used for my marketing video and thought it would double up to get people to click on my blog post. May as well embed the video now! Here you go:

Internet Marketing Jargon

Online marketing has so many abbreviations, that if you don’t know them, it sounds like another language

What is a “Browser”?

A browser is Internet Explorer, Google Chrome, Firefox, Safari on an Apple device or Opera. It’s a piece of software for navigating the internet and downloading/rendering websites.

DO NOT say that you use Internet Explorer in an online marketing interview. Say that you use Chrome.

The main reason that people use Chrome and Firefox is that you can get plugins/extensions for Google Chrome. The most popular in online marketing is probably the ‘Mozbar’ which helps you see the ‘Domain Authority’ of a website.

What is a CMS?

Content Management System.  A typical CMS is WordPress. This site is made using the free version of wordpress that uses the WordPress hosting platform, a free design template. The wordpress software can also be used when hosted with a different company such as GoDaddy, and the paid WordPress software allows plugins.

What are “WordPress Plugins”?

They are add-ons to your site that allows you to add ‘functionality’. For example you might add a Paypal plugin that allows you to create a shopping cart and payment system on your site to make in an online shop/ecommerce site.

What does “SEO” Stand For?

Search Engine Optimisation. This is the process of ranking as high as possible on Google and other search engines such as Bing.com.

Some terms to be aware of in SEO:
Meta Description – This is the description that you add the theof your website page’s code that shows in the Google Results. It just described what is on the page. Meta Descriptions should be 155 characters or less.

Meta Title .- this shows at the top of the tab in your browser and in the Google results pages.

meta-description
Image Source

What does “CRO” Stand for?

Conversion Rate Optimisation. This is the process of getting people who visit your website to buy things or sign up for a newsletter or submit a form. If you have an online shop, a ‘conversion’ would normally be a purchase on your website.

What does “PPC” Stand for?

Pay Per Click advertising (not Payment Protection Cover as I thought in my interview at the bank I used to work at). The 2 most popular forms of this are Google Adwords and Facebook advertising. Facebook is usually more effective on a smaller budget.

SEOmoz-PPC-vs.-SEO2

Image Source

What is HTML?

HTML stands for hyper text markup language. It structures websites whilst something called CSS – Cascading Style Sheets.

The best place to start learning HTML is w3 Schools. It can be overwhelming at first, but it’s easy when you get the hang of it. It’s just a load of numbers and letters inside angle brackets <> to remember. There’s no working out or formulas to remember, just letters and numbers.

HTML is one of the ways a websites ‘talks’ to a browser, so that the browser can render the website and show you what’s on there.
CSS makes it looks nice, adding colours, styles etc. For example the CSS might tell the browser to make all the main headings red and all the list items italic.

What is DA?

DA stands for Domain Authority. DA is an indication of how ‘powerful’ or authoritative a website is. The higher the DA (score of 1-100), the more valuable a link is from that website.

What is a Link?

A link or a hyperlink is a bit of text or an image, that you click to go to another website. For example this red underlined text is a link to a fitness blog. When you click it, you should be taken to another blog.

The more links a website has, from high quality websites, the more likely it is to rank higher in Google. You can gain links by creating great content, you can create and run marketing campaigns that people will want to write about, or you can be naughty and pay for some links.

What is Guest Posting?

Guest posting is an old school tactic to gain links to your website and rank on Google. You create an article/blog post, send it to a blogger in an email and ask if they would like to use it on their website. The article will contain a link back to your own website. The typical response from a blogger is either “no thanks” or “okay, that will cost $50”.

Now, this is against Google guidelines and you can get penalised by Google for doing this. People still do though. In theory, if a blogger accepts a payment to link to another website, in any form, whether it is via a blog post or sidebar-link or banner, then it should be a ‘no-follow’ link.

What is a NoFollow Link?

A no follow link has the HTML code – rel=”nofollow” added to it.

This tells Google NOT to count this link and not factor it in to it’s ranking algorithm.

What is the Google Algorithm?

It’s the formula that Google uses to work out where to rank websites on their results pages. Apparently nobody knows all of it, but it looks at how many other websites link to yours, the quality of the websites linking to yours, the quality of your website and its content and how fast your website loads.

What are Keywords?

Keywords or KWs, are search terms used in Google. If I search for “Red apples” then the keyword is “red apples”.
Rather confusingly, search phrases are also called keywords. You want to find KWs with lots of monthly searches, that don’t have lots of competition and add them to your content and headings in a natural format.  Don’t stuff them in everywhere.

Use the Keyword Planner Tool to find KWs with lots of monthly searches. And do a Google search to see which websites rank for those terms. If the top 10 websites for that KW are the likes of the BBC, Fox News and Amazon, you probably aren’t going to rank on the first page for that term.

Long Tail Keywords are less popular KWS, with less searches but often ‘convert’ to sales etc. better.

A Head Term might be “Tennis Balls”
A Long Tail Keyword might be “Buy Yellow Slazenger Tennis Balls UK”

“Tennis Balls” will have a much higher volume of monthly searches but will be very hard to rank on the first page of Google for. “Buy Yellow Slazenger Tennis Balls UK” will have less search volume each month, but will be easier to rank for and people searching for that term are more likely to buy/convert.

Tools for Internet Marketing

A CMS – such as WordPress so that you can create a website

A Keyword Tool such as the Google Keyword Planner. So that you can see what keywords to try and rank for or advertise with if you’re using PPC ads.

Google Analytics – to see who is coming to your site, where from and to what pages.

Google Chrome and some extensions such as the Moz Bar

Screaming Frog for Technical SEO. This gets all the information from a website and downloads it into an Excel Spreadsheet.

SEO Mofo – to make sure your meta titles & descriptions are the correct length. Add a CTA – Call To Action such as “Book Now!” or “Learn More” at the end of your descriptions.

Yoast SEO Plugin – This makes it easy to add meta titles and descriptions as you make your webpages and blog posts.