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.

Best WordPress Plugins (Free) 2021

Well, I am cheating a bit here as it is still 2020, but it’s December 22nd so please forgive me.

I don’t have any plugins on this site, but I do on my ecommerce site – NiceMMA.com

I’ve also been asked to look into plugins and basically update myself with WordPress ready for 2021 and a slightly new job role, so a thought a blog post might make a good place to record my findings.

Rank Math SEO – WordPress Plugin

This is similar to Yoast but you get more in the free version. I’m not sure I’ll be using it in my day job though, as it asks for permission to analytics and search console.

You can update URLs, redirects and a bunch of other great things with the free version and it also gives a quick SEO audit, which is nice.

Rank Math SEO allows you to look at ‘focus keywords’ and makes it easy to update the meta details.


Duplicate Page – WordPress Plugin

If you make a specific blog layout or a contact page or anything and you don’t want to remake it from scratch, this plugin with duplicate the page or post for you and keep it as a draft so you can go in and edit it before publishing.


Manage WP – WordPress Plugin

Good for web developers, you can update all the plugins all in one go and do other stuff really easy


Empty Spam Bee

If you have a contact form or email address on your site – this should keep out most of the spam.


iThemes Security

Great security plugin. Turn off some of the notifications in the notifications center or you will get loads of emails. Check – secure the site and activate Network Group Brute Force Protection


Elementor Custom Skin

great for creating custom designs if you build WordPress sites using Elementor

This plugin works great with Custom Post Type UI

To Learn more about Elementor, subscribe to this guy’s Youtube channel –

Live Chat for WordPress

If you are looking for an ai chatbot for free, look at this plugin

For managed live chat, check out these guys

How to Scrape Google SERPs (No Coding)

Add the Chrome Extension – Linkclump – https://chrome.google.com/webstore/detail/linkclump/lfpjkncokllnfokkgpkobnkbkmelfefj?hl=en

Go to the options in Linkclump – click “Edit” in the “Actions” section

Change to Copy links to Clipboard instead of open in new Tabs

Do your search

Zoom out to see more results

Change Google settings to see 100 results per page

Hold down Z and click and drag over results

Paste into a google sheet

Building Your Brand – Why Blog, Why Use Display Ads etc?

“You’ll never get yourself off the treadmill of paid ads, if you don’t build your brand”

Someone on a Search Podcast, 2019


It’s very easy to dismiss online content, blogs, image assets and even display ads as pretty much useless – because you don’t have the instant gratification of seeing leads and/or sales.

This is completely understandable; especially if you have a background in sales – where your job has been to ‘finish off the lead’ and get a sale.

However, if you are in it for the long (or medium) run, then building your brand is a must. Whether you are a tradesman or a giant corporation, your brand’s reputation and the brand-awareness is your safety net when it comes to consistent website traffic, leads & sales.

It takes time to build a brand – but once it is built, those people who come to you direct because they know who you are – are effectively free – or at least very cheap in comparison to some of the cost per click of Google Search Ads these days.

Building a brand is not easy however. Take my other blog for example – Blackbeltwhitehat.com

The blog has over 600 pages of content, lots of it really long, in-depth and time-consuming to produce. The site has 5,000-10,000 visitors per month, but virtually nobody comes to my website via a branded search on Google.

This could be down to one specific reason – the domain name is crap and hard to remember.

I’ve bought a few more memorable domains (like WokeMMA.com “Woke” being an ironic term for self-awareness used in the MMA & Jiu Jitsu communities) and I am currently weighing up the time & effort of re-branding everything like GoogleMyBusiness, TrustPilot etc. – plus all my back-links currently point to blackbeltwhitehat.com (I’m aware of 301s etc. but I’ll still definitely see a drop in rankings).

My blog is ultimately a hobby that I’ve invested less than $50 into over 6 years.  But if I had some more budget – I’d put together a plan to build my brand online…

Logo Design Illustrator
Blackbeltwhitehat.com

How to Build a Brand Online

First make sure you know your target audience & do one of those SWOT analysis. Then make specific goals to establish some brand KPIs.

Here are some ideas on what to do next:

  • Get a relevant, easy to remember domain name!

Learn from my mistake, a short catchy domain name is an easy-win if you are just starting out from scratch. A lot of the best and obvious domain name will be taken however, so you’ll have to do some research first. If you are just starting out, don’t name your business until you secure your domain name!

  • Display Ads

Depending on your niche, you can set tiny max CPC bids in some instances – and they’ll still get thousands of impressions for very little spend. Gmail ads work particularly well for (potential) low CPM (cost per 1000 impressions).

Rotate your display ads’ design & colours to stop people ignoring them due to ‘banner blindness’.

  • Blog & Outreach

Blog are great for reaching people who are researching a potential purchase.

For example, I landed on Perfect Keto’s blog a few times whilst researching Exogenous Ketones. Then ended up buying their branded product on Keto-pro.co.uk; because, for what ever reason, I trusted their brand.

Create great content, with statistics, images and video – and then outreach it – i.e. send it to relevant blogs and websites.

If you can afford it, use “PR-Level” outreach and contact national newspapers etc. This can be done via websites such as gorkana

If you content gets links too – then great – that’s good for Search Engine Optimisation (SEO). Doing some of your own exclusive research and generating tables of statistics are great for generating back-links naturally i.e. passively.

So consider doing some market research using Google surveys etc. These guys calculated RV/Campervan depreciation in value, just by looking at vehicles for sale online and get hundreds of back-links.

To turn blog’s into direct sales, you can also use relevant ‘CTA’ images below your blog.

For example, if you post a blog about the Walking Path’s of Snowdonia on your Snowdonia-based-bed-&-breakfast website; consider adding a relevant & clickable ‘book now’ and/or ‘get your free brochure’ button with eye-catching image at the bottom of the post. Many people now do this with newsletter sign up pop ups, which are a bit annoying but do work.

Before you start a blog, do your keyword research.

  • Create Tools

Content is great – but tools tend to do better than copy. For example, NerdWallet’s top page in terms of organic traffic – is their mortgage calculator.

  • Reviews

As well as brand awareness, you want some social-proofing of your brand. Start with a free account on Trustpilot and GoogleMyBusiness

  • Video & Social Media

The number 1 mistake people make on social media is to harp on about their brand all the time. Be entertaining, provide useful information and insightful comments. If you are over-promotional, people will not follow you. Build some authority by providing helpful insights that your target market will appreciate.

Videos & podcasts can be costly in terms of time. If you don’t want to set up your own podcast, guest-appearance on other people’s podcasts can generate valuable awareness and also back-links to your website (important for Search Engine Optimisation/Rankings).

  • Build an amazing product and/or service

This is your foundation and one of the reasons that Apple is so successful. An LSD-fueled Steve Jobs came up with some amazing ideas and concepts. The brand also turned itself into unique hybrid of tech & fashion thanks to their pioneering products.

The big, light-up apple on the back of Macbooks no doubt was a design aimed at building brand awareness too!

Please note – I realise this blog has a rubbish social media following. But that’s due to lack of time/money investment. I generally just use this blog as somewhere to record my thoughts & to remember how to do all things marketing related. E.g. here are my notes so I remember how to use Screaming Frog to scrape OG tags.

See my 2019 guide to Keyword Research by Clicking Here.

KW Research in 2020

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.

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.