SEO with the Google Search Console API and Python

The thing I enjoy most about SEO is thinking at scale. Postmates is fun because sometimes its more appropriate to size opportunities on a logarithmic scale than a linear one.

But there is a challenge that comes along with that: opportunities scale logarithmically, but I don’t really scale… at all. That’s where scripting comes in.

SQL, Bash, Javascript, and Python regularly come in handy to identify opportunities and solve problems. This example demonstrates how scripting can be used in digital marketing to solve the challenges of having a lot of potentially useful data.

Visualize your Google Search Console data for free with Keyword Clarity. Import your keywords with one click and find patterns with interactive visualizations.

Scaling SEO with the Google Search Console API

Most, if not all, big ecommerce and marketplace sites are backed by databases. And the bigger these places are, the more likely they are to have multiple stakeholders managing and altering data in the database. From website users to customer support, to engineers, there several ways that database records can change. As a result, the site’s content grows, changes, and sometimes disappears.

It’s very important to know when these changes occur and what effect the changes will have on search engine crawling, indexing and results. Log files can come in handy but the Google Search Console is a pretty reliable source of truth for what Google sees and acknowledges on your site.

Getting Started

This guide will help you start working with the Google Search Console API, specifically with the Crawl Errors report but the script could easily be modified to query Google Search performance data or interact with sitemaps in GSC.

Want to learn about how APIs work? See: What is an API?

To get started, clone the Github Repository: https://github.com/trevorfox/google-search-console-api and follow the “Getting Started” steps on the README page. If you are unfamiliar with Github, don’t worry. This is an easy project to get you started.

Make sure you have the following:

Now for the fun stuff!

Connecting to the API

This script uses a slightly different method to connect to the API. Instead of using the Client ID and Client Secret directly in the code. The Google API auth flow accesses these variables from the client_secret.json file. This way you don’t have to modify the webmaster.py file at all, as long as the client_secret.json file is in the /config folder.

try:
    credentials = pickle.load(open("config/credentials.pickle", "rb"))
except (OSError, IOError) as e:
    flow = InstalledAppFlow.from_client_secrets_file('client_secret.json', scopes=OAUTH_SCOPE)
    credentials = flow.run_console()
    pickle.dump(credentials, open("config/credentials.pickle", "wb"))

webmasters_service = build('webmasters', 'v3', credentials=credentials)

For convenience, the script saves the credentials to the project folder as a pickle file. Storing the credentials this way means you only have to go through the Web authorization flow the first time you run the script. After that, the script will use the stored and “pickled” credentials.

Querying Google Search Console with Python

The auth flow builds the “webmasters_service” object which allows you to make authenticated API calls to the Google Search Console API. This is where Google documentation kinda sucks… I’m glad you came here.

The script’s webmasters_service object has several methods. Each one relates to one of the five ways you can query the API. The methods all correspond to verb methods (italicized below) that indicate how you would like to interact with or query the API.

The script currently uses the “webmaster_service.urlcrawlerrorssamples().list()” method to find how many crawled URLs had given type of error.

gsc_data = webmasters_service.urlcrawlerrorssamples().list(siteUrl=SITE_URL, category=ERROR_CATEGORY, platform='web').execute()

It can then optionally call “webmaster_service.urlcrawlerrorssamples().markAsFixed(…)” to note that the URL error has been acknowledged- removing it from the webmaster reports.

Google Search Console API Methods

There are five ways to interact with the Google Search Console API. Each is listed below as “webmaster_service” because that is the variable name of the object in the script.

webmasters_service.urlcrawlerrorssamples()

This allows you to get details for a single URL and list details for several URLs. You can also programmatically mark URL’s as Fixed with the markAsFixed method. *Note that marking something as fixed only changes the data in Google Search Console. It does not tell Googlebot anything or change crawl behavior.

The resources are represented as follows. As you might imagine, this will help you find the source of broken links and get an understanding of how frequently your site is crawled.

{
 "pageUrl": "some/page-path",
 "urlDetails": {
 "linkedFromUrls": ["https://example.com/some/other-page"],
 "containingSitemaps": ["https://example.com/sitemap.xml"]
 },
 "last_crawled": "2018-03-13T02:19:02.000Z",
 "first_detected": "2018-03-09T11:15:15.000Z",
 "responseCode": 404
}

webmasters_service.urlcrawlerrorscounts()

If you get this data, you will get back the day-by-day data to recreate the chart in the URL Errors report.

Crawl Errors

 

 

 

webmasters_service.searchanalytics()

This is probably what you are most excited about. This allows you to query your search console data with several filters and page through the response data to get way more data than you can get with a CSV export from Google Search Console. Come to think of it, I should have used this for the demo…

The response looks like this with a “row” object for every record depending on you queried your data. In this case, only “device” was used to query the data so there would be three “rows,” each corresponding to one device.

{
 "rows": [
 {
 "keys": ["device"],
 "clicks": double,
 "impressions": double,
 "ctr": double,
 "position": double
 },
 ...
 ],
 "responseAggregationType": "auto"
}

webmasters_service.sites()

Get, list, add and delete sites from your Google Search Console account. This is perhaps really useful if you are a spammer creating hundreds or thousands of sites that you want to be able to monitor in Google Search Console.

webmasters_service.sitemaps()

Get, list, submit and delete sitemaps to Google Search Console. If you want to get into fine-grain detail into understanding indexing with your sitemaps, this is the way to add all of your segmented sitemaps. The response will look like this:

{
   "path": "https://example.com/sitemap.xml",
   "lastSubmitted": "2018-03-04T12:51:01.049Z",
   "isPending": false,
   "isSitemapsIndex": true,
   "lastDownloaded": "2018-03-20T13:17:28.643Z",
   "warnings": "1",
   "errors": "0",
  "contents": [
    { 
    "type": "web",
    "submitted": "62"    "indexed": "59"
    }
  ]
}

Modifying the Python Script

You might want to change the Search Console Query or do something with response data. The query is in webmasters.py and you can change the code to iterate through any query. The check method checker.py is used to “operate” on every response resource. It can do things that are a lot more interesting than printing response codes.

Query all the Things!

I hope this helps you move forward with your API usage, python scripting, and Search Engine Optimization… optimization. Any question? Leave a comment. And don’t forget to tell your friends!

 

Track REST APIs with the Google Analytics Measurement Protocol

Google Analytics got a whole lot more interesting when the Measurement Protocol was introduced. We already knew GA was the industry standard for web analytics but with the Measurement Protocol it has become the analytics platform of anything and everything that can be made digital. With some clever instrumentation, we can now use it to track products through the supply chain or track users interactions in a store. All you need is a way to collect digital data and send HTTP requests to Google Analytics and you can track anything.

I had to try it out for myself. While I could have fitted #rhinopug with a tracking device or instrumented my coffee machine with an Arduino, I took the easier (but equally cool) route to getting data: a Web API. As my proof of concept, I chose to track the SwellPath team’s group chat application called GroupMe.

Google Analytics Measurement Protocol

GA Dashboard Courtesy of Mike Arnesen

Tracking a chat app turned out to be a pretty cool way to walk that physical/digital line. While we are humans working in the same office, its interesting to compare contextual information from what we can see and hear to the very objective measure of communication; desktop and mobile messaging. This concept is similar to other measures of digital communication like Twitter firehose or brand mentions from news API’s. Those are probably much more relevant to, and could actually affect a website’s performance but, let’s be honest, this one’s a lot more fun.

Mapping Data to Google Analytics

Digital messaging is actually pretty appropriate for the Google Analytics reporting interface. The main reason is this: timestamps. We rely heavily on timestamps to analyze everything in Google Analytics which are all time-based hits. We ask Google Analytics how different landing pages perform as seasons change and what time of user’s are most likely to convert (in order to bid intelligently on ads). Likewise, there is also a natural rhythm to work-based communication. Of course, (or hopefully) its pretty quiet on the weekends and generally pretty active as people start each workday.

The other reason that human communication maps to the Google Analytics reporting interface is that message creation is a lot like content consumption. When we really think about what a “hit” schema looks like, it has a few entities what go together something like this:

[actor] did [event] on [location] at [timestamp]

This “hit” schema works equally well for describing message creation as it does content consuming.

With every hit, the [actor] a.k.a. User is assigned some attributes like Device or New/Returning and the [event] a.k.a. Event, Pageview or otherwise, will have attributes like URL and  Page Title for Pageviews or Action and Label in the case of Events. The [location] is an interesting one. For web, its the page that the user is browsing but it’s also the physical location of the user a Lat,Lon pair with appropriate geographic information. The [location] attributes are generally handled by Google Analytics automatically but speaking from experience, the real art of a good collection strategy is mapping the right information to the right attribute of each entity.

To make sense of the idea of mapping information to attributes let’s get back on track and talk about GroupMe. It boils down to this: you have data and you want it to appear in Google Analytics in a way that you can logically sort/filter/analyze it. This is where the mapping comes in.

GroupMe’s API gives you data about a group’s messages like this:

{
  "count": 123,
  "messages": [
    {
      "id": "1234567890",
      "source_guid": "GUID",
      "created_at": 1302623328,
      "user_id": "1234567890",
      "group_id": "1234567890",
      "name": "John",
      "avatar_url": "http://i.groupme.com/123456789",
      "text": "Hello world ☃☃",
      "system": true,
      "favorited_by": [
        "101",
        "66",
        "1234567890"
      ],
      "attachments": [
        {
          "type": "image",
          "url": "http://i.groupme.com/123456789"
        },
        {
          "type": "image",
          "url": "http://i.groupme.com/123456789"
        },
        {
          "type": "location",
          "lat": "40.738206",
          "lng": "-73.993285",
          "name": "GroupMe HQ"
        },
        {
          "type": "split",
          "token": "SPLIT_TOKEN"
        },
        {
          "type": "emoji",
          "placeholder": "☃",
          "charmap": [
            [
              1,
              42
            ],
            [
              2,
              34
            ]
          ]
        }
      ]
    }
  ]
}

If this doesn’t make sense to you, go read up on JSON. But essentially what you get when you ask the GroupMe API for the most recent messages, it returns a list of messages with, among other things, the sender’s name and user ID, the message, the number of likes, and the location. So we have information about each of the “hit” entities. The user, event, place and time are all described. The only thing missing that is critical to web analytics metrics is something similar to Page. For that reason I decided to use Google Analytics Events to describe each GroupMe message. Each hit maps GroupMe data to Google Analytics as follows:

Google Analytics Parameter GroupMe Data / JSON Keys
User ID GroupMe User ID / user_id
Client ID GroupMe Source GUID / source_guid
Custom Dimension (User) GroupMe Username / name
Event Category “GroupMe Chat”
Event Action “Post”
Event Label Truncated Text of Message / text
Event Value Count of Likes / count(favorited_by)
Queue Time Difference between Now and Timestamp /current time – created_at

Then each GroupMe message is sent to Google Analytics on an HTTP request with data mapped to GA parameters as shown above. Collect data for a few days and then it looks like this:

Measurement Protocol Specific Values: Queue Time and Client ID

If you come with a Web analytics frame of mind, there may be two things that are unfamiliar to you: Client ID and Queue Time. These are both a pain to get right but functionally awesome.

The Client ID is something you don’t have to think about for web data collection; it’s automatically collected from a cookie that Google Analytics sets for you. It is very important though. It is the key for differentiating two devices that, by their collectible attributes “look” the same but are not. The CID must follow very specific rules to be valid and lucky for me, GroupMe offers a GUID for each message that fits the specifications.

Queue Time is awesome. This is the single most important factor in getting the time value of at Measurement Protocol “hit” right. It is the delta (a cool way to say difference) between the time that the event occurred and the time that the hit was collected. If you send the hit to Google after the hit took place, Google’s servers calculate the time delta and record the hit at the time that it actually took place.

This was especially important for the method I used to get data from GroupMe and send it to Google Analytics. Because I was only getting the messages from the GroupMe API once an hour. Without the Queue Time, the hit timing would be very low fidelity, with spikes each hour when the data was collected and sent. By calculating the Queue Time when each message was sent, I got accurate timing and didn’t have to worry about burning through API limits or wasting lots of HTTP calls. (Think about it, without Queue Time, your data is only as accurate as the frequency that your hits are sent which was a cron job in this case.)

Google Analytics Measurement Protocol API

Don’t call it a hack. Ok, call it a hack.

Lessons Learned / How I’d Do it Next Time

This ended up working out pretty well thanks to a fair amount of luck and plenty of read the docs, code, debug, repeat. I got lucky when I realized I hadn’t accounted for things like the mandatory Client ID parameter and … the fact that my server doesn’t run Python cron jobs. As a result I ended up writing my first PHP script and here I am sharing 100-some lines of amateur code. But hey, this proof of concept works!

If I were to do this again, I would answer a few questions before I started:

Get to know the API

  • Will the API I want to track give me all the data I need?
  • Are events timestamped or do I have a way to approximate that?
  • How difficult is authentication and how long does it last for?
  • Am I going to operate safely within the API rate limits?
  • What about Terms and Conditions of the API data?

Map the Data to Google Analytics

  • How will I avoid making recording the same hit twice?
  • What type of Google Analytics Hit will I use?
  • How should I map the API’s data to a Google Analytics hit?

Automate!

  • Can I write some code to automate this?

How the Code Works

The code I wrote to automate this is listed below but if you are unfamiliar with PHP or code in general the instructions that are given to the computer are essentially this:

Call the GroupMe API to see if there are any new messages since last time
  If no: stop.
  If yes: continue
Call API to get/make a map of User ID’s to User Names to send with hits
For each message that was returned:
  map it to GA parameters
  send it as an event to GA
  For each like of each message:
    map it to GA parameters
    send it as an event to GA
Write the the most recent message ID to a .txt file (to keep track of what has been sent)

Wait for about an hour and repeat with the next cron job

It was a fun project and luckily a successful proof of concept for tracking non-website data in Google Analytics. If you’re thinking about doing a Measurement Protocol project, leave a comment or tweet me at @realtrevorfaux (don’t worry, I’m not tracking it). If you’re interested in other cool ways to track offline transactions, check out Google Analytics Enhanced Ecommerce, I really look forward to what is to come of the Measurement Protocol with things like IoT. Connect, collect, and analyze all the things!

The PHP code that I used is below. Give me a break, this is the first PHP programming (and maybe last) I’ve ever done.

<?php

// script configuration stuff
$token = "abc123"; // from dev page
$group_id = "1234567";  // from dev page
$memory_file = "last_id.txt";

$UAID = "UA-XXXXXX-XX"; // Google Analytics UA Code
$member_names_map = makeNameMap();

// saved last message id to file
$since_id = file_get_contents("last_id.txt");

// endpoint to get lastest messages
$url = 'https://api.groupme.com/v3/groups/'. $group_id .'/messages?token=' .$token. "&since_id=". $since_id;

// call the groupme api
$ch = curl_init();
curl_setopt($ch, CURLOPT_URL, $url);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
$response = curl_exec($ch);
$http_status = curl_getinfo($ch, CURLINFO_HTTP_CODE);
curl_close($ch);

// check response code and do the rest if no change.
if ($http_status === 304){
  echo "API RETURNED: ". $http_status ." n";
} else {
  $message_count = $response->count;
  echo "API RETURNED ". $message_count ."MESSAGESn";
  handleMessages($response);
}


function handleMessages ($response_obj){

  $json = json_decode($response_obj);
  $messages = $json->response->messages;
  $timestamp = time();

  foreach ($messages as $message) {

    global $UAID;
    $queue_time = $timestamp - $message->created_at;

    $post_hit_params = array (
      'v'=>1,
      'tid'=>$UAID,
      'uid'=>$message->user_id,
      'cid'=>$message->source_guid,
      't'=>'event',
      'ec'=>"GroupMe Chat",
      'ea'=>"Post",
      'el'=>$message->text,
      'ev'=>count($message->favorited_by),
      'qt'=> $queue_time,
      'cd1'=> $message->name,
      'cd2'=> $message->user_id
    );

    sendGAHit($post_hit_params);

    $favorited_by = $message->favorited_by;

    foreach ($favorited_by as $id) {

      $name = $member_names_map->$id;

      $like_hit_params = array (
        'v'=>1,
        'tid'=>$UAID,
        'uid'=>$id,
        'cid'=>$message->source_guid,
        't'=>'event',
        'ec'=>"GroupMe Chat",
        'ea'=>"Like",
        'el'=>$message->text,
        'ev'=>1,
        'qt'=> $queue_time,
        'cd1'=> $name,
        'cd2'=> $id
      );

      sendGAHit($like_hit_params);
    }
  }

  // get last message/id from this call's messges
  $last_message = current($messages);
  $last_message_id = $last_message->id;
  writeMemoryFile($last_message_id);
}

function sendGAHit ($params){

  $query_string = http_build_query($params);
  $url = "www.google-analytics.com/collect?". $query_string;

  // send hit to GA
  $ch = curl_init();
  curl_setopt($ch, CURLOPT_URL, $url);
  curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
  $response = curl_exec($ch);
  $http_status = curl_getinfo($ch, CURLINFO_HTTP_CODE);
  curl_close($ch);

  echo "n";
  var_dump($params);
  sleep(1);
}

function writeMemoryFile ($last_message_id){
  global $memory_file;
  // write last ID to file for next time
  $memory = fopen($memory_file, "w");
  fwrite($memory, $last_message_id);
  fclose($memory);

  echo "LAST ID WRITTEN TO FILE: ". $last_message_id ."n";
}

function makeNameMap(){
  global $token;
  global $group_id;
  $url = 'https://api.groupme.com/v3/groups/'. $group_id .'?token='.$token;
  // call the groupme api
  $ch = curl_init();
  curl_setopt($ch, CURLOPT_URL, $url);
  curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
  $response = curl_exec($ch);
  $http_status = curl_getinfo($ch, CURLINFO_HTTP_CODE);
  curl_close($ch);

  $json = json_decode($response);
  $members = $json->response->members;
  $member_names_map = new stdClass;

  foreach ($members as $member) {

    $user_id = $member->user_id;
    $nickname = $member->nickname;
    $members_names_map->$user_id = $nickname;

  }

  return $members_names_map;
}

?>

 

Learn the Power of API’s for Digital Marketing | $10k Tech Skills 3/4

This is part 3 of 4 of the $10k Technical Skills for Digital Marketing.

You could be so much faster! Right now, you are using your browser to view HTML pages. You go to a page, get some information and move on. Imagine how much more information you could gather if you could tell your browser to look at 10 pages per second, compile it all and give you the executive summary. This is the power that API’s and a little bit of code provide.

Powerful Data Meme

API’s are your key to the Web at scale. An API (Application Programing Interface) allows you to interact with services like social networks, and marketing, advertising, or analytics platforms. With a bit of code or the right tools, API’s can be used to do almost anything that a digital marketer does manually. The real power of marketers who can program comes from using API’s to programmatically execute marketing tactics that would be otherwise impossible for humans to do at the same speed.

Painless Reporting

“I love creating the same report every month”  No one ever.

Reporting is part of being-data driven but unfortunately, it sucks. It is time consuming and takes your valuable time away time away from creating or optimizing marketing strategies. API’s are a very elegant answer to recurring reports. If you have to get data from several places just to format it into a spreadsheet or charts, let APIs help. Google Spreadsheets and Google App Script offer a friendly API to allow you to populate spreadsheets and charts with data programmatically. Just make a call to your analytics platform or email marketing platform or your advertising service’s APIs to create up-to-date dashboards.

Automation: The Key to Growth Hacking

There are also many other tasks from response emails when users sign up to keyword research using Google’s auto-suggest to PPC bidding with Google Adwords that can be automated using API’s.

Keyword Research? Ain't nobody got time for that!Even social media management can be automated using API’s (think of that annoying “growth hack” that people try when you tweet to hashtag and they favorite it or add you to a list 3 seconds later.) I am not saying it should be done, just that it can be done.

All of these digital marketing tasks have one thing in common: they have an input and depending on what that input is, they have a slightly different output. These tasks are perfect candidates to be handled by algorithms. Write the instructions in code to decide when/how these things are done and your work time becomes a lot more efficient and your work will scale infinitely. Think of this like IFTTT (which is all about interconnecting API’s with some logic) for digital marketing.

Web Scraping: Research at Scale

Web scraping is very similar to interacting with API’s but in this case, the API is a collection of HTML pages or files with consistent formatting. Take for example, the indeed.com trend graph images that I have been using; depending on the URL input, their server will respond with a different image. Try it yourself (change {keyword} to a job or skill):

http://www.indeed.com/trendgraph/jobgraph.png?q={keyword}+{keyword}

If you were looking for the newest trending skills, instead of searching their website over and over, you could make a hundreds of requests to their /jobgraph URL extension and you could quickly scan the images to see what skills you need to learn. That might be a trivial example but you get the idea!

As you can see from the last example, APIs can be used to speed up processes at any scale but are absolutely essential to large processes.

Hacking Social for Fun and Profit

Exploring API’s can offer new and creative ways to market your product or service. One especially remarkable tactic that employs the Instagram API was just started by Marais Shoes last year. Marais uses the Instagram API to check, in real-time, for comments on their images that contain the hashtag “sold.” If the comment author has signed up on their site using Instagram. The member is emailed a link to their shopping cart with the featured product conveniently in their cart. This brilliant tactic would not have been possible without the combination of marketing savvy and an understanding of API technology.

REST, JSON, XML, WTF?

Finally, In order to know what an API is capable of, a marketer must know what data can be interacted with and how it is transmitted. This is where transfer protocols and data structures get involved. But don’t let the language scare you, at an operational level these are not that complicated! The large majority of API’s these days are very user friendly thanks to REST (REpresentational State Transfer) and JSON (JavaScript Object Notation) formatted data. JSON is very easy to understand once you try. It has a very simple set of rules about how data is organized and formatted. These rules are much like MLA formatting for research papers (except much simpler). The consistency allows us (computers and humans) to easily understand information because we know what formatting to expect.

Here is an example of JSON formatted data. It comes from the Facebook API and is simplest representation of me in the eyes of Facebook. See, not so bad!

https://graph.facebook.com/trevor.fox.921

Learning How to Use REST API’s

A great way to build an understanding of REST is using a cool tool called PostMan. PostMan is a Chrome App that acts as a “client” to inspect what data is available in what way from different API’s. You enter in the URL of the service you want to work with and PostMan will bring back the data and format it in a way that is easy to understand. From there you can write a little big of Google Apps script or server side code and automate the whole process.

Another cool tool is cURL. This is the nerdy grandfather of PostMan that lives in the Bash Shell. It works in the same way as PostMan except you enter the URL in the command line and then the data is printed to the screen. cURL is definitely as user-friendly as PostMan but it is good to be familiar with because many API’s and services refer to cURL in their documentation. Spend one hour learning cURL. It will save you loads of time banging your head into API error responses.

For more on learning how to you API’s, the next post explains how to make sense of API documentation, how to find the data and make the data work for you.

Read the rest of the $10k Technical Skills for Digital Marketing or…

Get notified when each posts is released!

How to Make a Google Apps Script RESTful API or Service

You know what’s cooler than calling API’s and services with Google Apps Script? Making your own API with Google Apps Script. After playing around with my first REST API with Python and Bottle, I wondered how this could be done even cheaper and quicker; without worrying about the complexities of databases and hosting.

When it’s simple and easy you are looking for, the answer is usually Google Apps Script. REST API’s and web services can be written in Google Apps Script in a matter of a few lines. It can then be easily deployed to the public as a web app. This post shows how to make a RESTful API for interacting with a Google Spreadsheet.

Planning Your REST API


First we’ll need an application, In this case, we are going to use a Google Spreadsheet with a product list as our application. We are going to allow clients to retrieve data from the spreadsheet.Google Spreadsheet

Then we’ll have to decide how people can access and interact with the data from our application based on a URI schema. There is some limitation to request URIs with Google Apps Script. Namely, you cannot use URI extensions like “/products/get/” or “/products/list/”. Instead, everything must be done with query string parameters.  In this case our REST service only has one method so we will be using the following schema to get information about the product:

?action=get&prodid=<product id>

For great insight into forming you URI schema see this StackOverflow question.

The API could also have methods to add or delete products and update quantities but for simplicity, I will let you think about what that looks like.

Finally, we will have to decide how data is structured when it is retrieved from the API. In this case, I will be using JSON, but it could be XML or Plain Text if you wanted. The Google Apps Script ContentService class has methods for formatting the data output in any way that you choose.

For a much more in-depth at designing an API, I would recommend reading these best practices.

Making an Example REST API

doGet(), doPost() and URL Parameters

The most import things to understand are the Google Apps Script doGet() and doPost() methods. These methods take, as an argument, the request URI and decide what your application does with the request. For simplicity, version 1 of our API only accepts GET requests, so there is no need for the doPost() method. But you should know that the doPost() method does have some additional functionality.

When doGet() receives the URI, the Google Apps Script turns it into a request object that, to your application, looks like this:

Request URL:

<script web app url>?action=get&prodid=g1234&prodid=g2434

doGet Request Object:

{
  "queryString": "action=get&prodid=1234&prodid=2434",
  "parameter": {
    "action": "get",
    "prodid": "g1234"
  },
  "contextPath": "",
  "parameters": {
    "action": [
      "get"
    ],
    "prodid": [
      "g1234",
      "g2434"
    ]
  },
  "contentLength": -1
}

For more information about the request object, see https://developers.google.com/apps-script/guides/web#url_parameters.

*Its important to note to things: first is the difference between the parameter and parameters array. The parameters array returns a list of all prodid queries while the parameter object only shows the first in the URI and second is that you can use the query string to change your request, but you cannot, unfortunately, use variations the URL extension.

Application Logic and Plumbing

Now that the request URI is neatly formatted into a JavaScript object, all we have to do is translate that into a query for our spreadsheet so that we can return a JSON formatted response to the client. If this is a service, it may be, that all you have to do is run the request object through some logic or an algorithm and return the result. In this case, I used two helper functions to interact with data in a Google Spreadsheet.

The function, productQuery, takes a product ID as input and return the row corresponding to that product ID. The function formatProduct, takes a data from a spreadsheet row and turns it into an object with spreadsheet headings as object keys.

/* Take a product ID as input and return the
 * row corresponding to that product ID.*/

function productQuery(prodId){
 for (var i = 1; i < data.length; i++){
  if (prodId === data[i][0]){
   return data[i]
  }
 }
}

/* Take a spreadsheet (product) row and turn it into an object
 with spreadsheet headings as object keys. */

function formatProduct(rowData){
 var product = {}
 for (var i = 0; i < headings.length; i++){
  product[headings[i].toString()] = rowData[i]
 }
 return product
}

doGet(): Returning JSON Data


Now that all the intermediate logic is taken care of, all we have to do is write the doGet method so that it takes the request URL and returns our JSON formatted product data.

function doGet(request) {
 // Check for a valid request URI
 if (request.paramter.action !== undefined){
  if (request.paramter.prodid !== undefined){
   prodIds = request.parameters.prodid
 
   // The object to be returned as JSON
   response = {
   products : []
  }
  
  // Fill the products array with requested products
  for (var i = 0; i < prodIds.length; i++){ 
   sheetData = productQuery(prodIds[i])
   product = formatProduct(sheetData)
   response.products.push(product)
  }
 
  if (response.products.length > 0){
   return ContentService.createTextOutput(JSON.stringify(response));
  } else {
   return ContentService.createTextOutput('Invalid Request. Product ID(s) not found.');
  } 
 } else {
  return ContentService.createTextOutput('Invalid Request. Use at least one valid "prodid" parameter.');
 } 
 else {
  return ContentService.createTextOutput('Invalid Request. Use a valid "action" parameter.');
 }
}

Now that everything is connected. The request to

<script web app url>?action=get&prodid=g1234&prodid=h2543

returns:

{
  "products": [
    {
      "id": "g1234",
      "name": "Baseball Glove (right)",
      "price": 60,
      "stock": 35
    },
    {
      "id": "h2543",
      "name": "Baseball Hat",
      "price": 40,
      "stock": 52
    }
  ]
}

The next step could be to add some kind of functionality that would allow the client to add a product as a line to the spreadsheet using a different “action” parameter. I’ll leave that to you to explore.

Testing and Debugging


REST service is a bit different than writing a normal script in that the debugging feedback loop is a bit longer. Anytime you want to change your code and see how it is really working, you will have to:

  1. Save the script
  2. Create a new version of the App
  3. Deploy the App

To deal with this I found that it was a lot quicker to create a test wrapper function that would call the doGet function with a fake request URL object (shown above). This way you can use the Logger for quick inspection into your code.

When you’re ready to test your code in action. Try out PostMan, an HTTP client that is great for building queries and viewing the response.

Deploying your REST Service

[/ultx]

All you have to do is…

  1. Save the script
  2. Click “Publish” in the Google Apps Script Menu
  3. Select “Deploy as web app”
  4. Create a new version of the App
  5. Deploy the App

You will need to specify who has access to your API before you deploy it. Since we are making an API, we must set the access permission to “Everyone (even anonymous).” Otherwise, your client will not be able to access your service unless it is a Google user. For more see the Google Apps Script documentation.

deploy your Google Apps Script API

*Important Note: Because requests to Google Apps Script Web Apps return data from a 302 redirect URL, any client that will be accessing the service, must be capable of following redirects.

Otherwise, Google Apps Script provides a quick, cheap and easy way to create simple REST API’s and REST Services. View the full Script on GitHub or make your own! Planning it out will give you a great idea about how to interact with REST services as a client.
[/ultx]

Automate Mobile Preferred Ad Creation with Adwords Scripts

Scripts Save Lives

I love algorithms and you should too. They provide simple consistency to life like no calendar, routine or habit ever could.  They do exactly what you tell them to do and they do it fast, and most beautiful of all, they make life automatic. (sigh of contentment)

I also love javascript but sadly, there are not a lot of places where javascript and algorithms intersect. Scripts for Adwords is one of those happy unions and this delicious union of medium and method has a little cherry on top called automation. Ain’t it sweet!

Adwords Scripts are great for automating reporting, pulling info from other API’s into Adwords and routine account maintenance. The idea for this one was brought up to me by my Google Adwords Rep. Scott, who it turns out, is rad. He called me up one day mentioning how we could optimize one of my accounts. (To be read: “make more money selling ads for Google.”) He suggested that we add mobile preferred ads to all my adgroups with a click-to-call extension, suggesting that this could potentially increase conversion rates from mobile users who are better at calling then poking their screen.

“Lets try it!” I said then thought, “How can I automate this?”

As it turns out, its not fully automatable, as the Adwords Script API does not allow for extensions to be added to ads in the ad creation process. But we can automate our work most of the way there:

How it Works

This script mirrors the process that I went through with my Adwords rep:

  • Select the ad with highest click through rate in its adgroup
  • Copy the headline and URL’s for the selected ad
  • Make a copy with a historically successful call-to-action
  • Make a copy with a mobile optimized call-to-action
  • Set this ad to be mobile-preferred.

But instead of doing this one by one or in a spreadsheet, the script iterates through all adgroups in a the account, and makes the changes with one click. (Now you get why I love algorithms?)

There is one last manual step though. As I mentioned before, Scripts won’t allow for adding extensions or labels in the ad creation process. This means that you will have to go into the Adwords interface, create your click-to-call ad extension and apply it to all your ad groups. To be safe make sure that the call-to-click extension applies only to mobile ad extensions.

So here is the script the script can also be found on my github.

function main() {
  
  //set the following variables to customize the script to your account 
  
  var IMPRESSIONS_THRESHOLD = 100;       // min. impressions an ad needs to have to be considered
  var DATE_RANGE = "LAST_30_DAYS";       // time frame for impression threshold 
  var ADGROUP_NAME_CONTAINS = 'something';   // word or phrase that eligible adgroup name must contain
  
  var DESC_LINE_1 = "Free Shipping and Free Returns!";   // description line 1 (end with punctuation)
  var CTA_CONTROL = "Shop Latest Cool Styles Today";      // a historically succesful CTA for line 2
  var CTA_MOBILE = "Call Now To Place Your Order";       // new mobilized CTA to test for line 2
      
  // select adgroups that meet criteria defined above
  
  var adGroupSelector = AdWordsApp
  .adGroups()
  .withCondition("Status = ENABLED")
  .withCondition("CampaignName CONTAINS_IGNORE_CASE " + "'" + ADGROUP_NAME_CONTAINS + "'")
  
  var adGroupIterator = adGroupSelector.get(); 
  
  // iterate through all selected adgroups
  
  while (adGroupIterator.hasNext()) {
     
    var adGroup = adGroupIterator.next();
    
    var headline;
    var displayURL;
    var destURL;
    var optArgs = {
        isMobilePreferred: true
    };
    
    // select enabled ads that meet predifined criteria
    // ad with best ctr for each group will be copied
    
    var adSelector = adGroup.ads()
    .forDateRange(DATE_RANGE)
    .withCondition("Status = ENABLED")
    .withCondition("Impressions > " + IMPRESSIONS_THRESHOLD)
    .orderBy("Ctr DESC");    
    
    // this iterator does not really iterate, instead
    // it orders by CTR and selects ad with highest CTR
    
    var adIterator = adSelector.get();  
    while (adIterator.hasNext()) {
      var ad = adIterator.next();
      var stats= ad.getStatsFor(DATE_RANGE); 
      
      // headline and URLS are selected from ad
      
      headline = ad.getHeadline();     
      displayUrl = ad.getDisplayUrl();
      destinationUrl = ad.getDestinationUrl();
      Logger.log(headline +": " + stats.getCtr())
      break;     
    }
    
    // and are copied along with pre-defined description line
    // to create to versions of the mobile-prefered ad
    
    adGroup.createTextAd(
      headline,
      DESC_LINE_1,
      CTA_CONTROL,
      displayUrl,
      destinationUrl,
      optArgs
    )
  
    adGroup.createTextAd(
      headline,
      DESC_LINE_1,
      CTA_MOBILE,
      displayUrl,
      destinationUrl,
      optArgs
    )
  }
}

 

Automate MailChimp Reporting with Google Spreadsheets

I made it! I am finally a nerd. At the top of the MailChimp API there is a clear disclaimer: “Woah, Nerds Only!” Undeterred, I scrolled on to test my nerdy medal. The following is my account of navigating the well-charted waters of the MailChimp API and a tutorial on how to automate a “BARF” report using Google Apps Scripts and Google Spreadsheets.

First, let me tell you how this started. Reporting in digital marketing can be a lot of work. Automate it. Ok done explaining.

The MailChimp API is a Friendly API

MailChimp’s API is nice and clean with great documentation. It has well-defined endpoints and returns JSON objects. Google Apps Scripts is great for parsing JSON objects thanks to the JSON class made specifically for that.

The problem as I defined it was as follows:

  1. MailChimp API has the data I want.
  2. I have to make a call to API using Apps Script’s UrlFetchApp
  3.  The call has to be authenticated by passing along API key.
  4. First I have to make a call to get all the campaigns/list endpoint to get a list of campaigns’ name/id info.
  5. Then I have to iterate through all the selected campaigns and pass their id to the reports/[whichever-stat-I-want] endpoints to get back each campaign’s stats.
  6. Format that data into an array and write it to the spreadsheet.

The Google Apps Script


Yup, so that’s exactly what I did. The interesting part for me was getting a better understanding of the UrlFetchApp class. I had used it before for getting data from APIs or web pages but only the simple way:

UrlFetchApp.fetch(“whateversiteiwant.com/data-from”)

But Mailchimp’s API asks for more. It asks that all requests are made as POST requests and it asks that the “apikey” and additional parameters be passed as JSON objects rather than a query string like: www.site.com/api?param=value.

This meant using the additional “param” parameter in the UrlFetchApp.fetch() method. I am happy that I got it to work. It’s clear in the code how it is used but I still need to know exactly why and how it works because it’s pretty cool. Expect a blog post on that.

Beyond that, the code is pretty self-explanatory. I commented parts that would be dodgy for Codecademy level folks. (I know. I was there not too long ago.)

Writing the report to a Google Spreadsheet

Check it out. It is explained in the comments.

// campaigns list api endpoint docs: apidocs.mailchimp.com/api/2.0/campaigns/list.php
// reports api endpoint: apidocs.mailchimp.com/api/2.0/reports/summary.php
// GAS docs: developers.google.com/apps-script/reference/url-fetch/url-fetch-app#fetch(String,Object)
// mailchimp open tracking calculations: kb.mailchimp.com/article/about-open-tracking
  
// your api key can be found at: admin.mailchimp.com/account/api/
// standard "24 hour format in GMT, eg "2013-12-30 20:30:00" - if this is invalid the whole call fails"
// add formated values for start and end date like: var REPORT_START_DATE = "2013-12-30 20:30:00"

function chimpReport() {
  
  var API_KEY = 'yourapikeygoeshere';
  var REPORT_START_DATE;
  var REPORT_END_DATE;
  
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();  
  
  var dc = API_KEY.slice(-3);
  var api = 'https://'+ dc +'.api.mailchimp.com/2.0';
  var campaignList = '/campaigns/list.json';
  var reports = '/reports/summary.json';
  
  // MC api-specific parameters
  var payload = {
    "apikey": API_KEY,
    "sendtime_start": REPORT_START_DATE,
    "sendtime_end": REPORT_END_DATE
  }; 
  
  // GAS specific parameters: 
  var params = {
    "method": "POST",
    "muteHttpExceptions": true,
    "payload": payload
  };
  
  var apiCall = function(endpoint,cid){
    if(cid){
      payload.cid = cid;
    }
    var apiResponse = UrlFetchApp.fetch(api+endpoint, params);
    var json = JSON.parse(apiResponse);
    return json;
    };
  
  var campaigns = apiCall(campaignList);
  var total = campaigns.total;
  var campaignData = campaigns.data;
  
  for (var i=0; i< campaignData.length; i++){
      
    var c = campaignData[i];
    var cid = c.id;
    var title = c.title;
    var subject = c.subject;
    var send_time = c.send_time;
    
    // send_time values are only present for campaigns that have been sent. otherwise set to null.
    // this if statement will only call for report data and write to the spreadsheet, data from sent campaigns.
    
    if (send_time){
      
      var r = apiCall(reports,cid);
      var emails_sent = r.emails_sent;
      var opens = r.opens;
      var unique_opens = r.unique_opens;
      var clicks = r.clicks;
      var unique_clicks = r.unique_clicks;
      var open_rate = (unique_opens / emails_sent).toFixed(4);
      var click_rate = (unique_clicks / emails_sent).toFixed(4);
      
      // the report array is how each row will appear on the spreadsheet
      var report = [send_time, subject, emails_sent, opens, unique_opens, clicks, unique_clicks, open_rate, click_rate];
      
      Logger.log(report);
      
      // note that this method will append to the bottom of the spread sheet wherever that is.
      // to overwrite a specific range use setValues()
      
      sheet.appendRow(report)
    }    
  }
}

All set!? Next step: make a Google Spreadsheet dashboard with Google Analytics data and Google Spreadsheets Sparklines.