IFTTT + NFC Tags: a Maker Channel Tutorial

I geeked out recently over the Android task automation app,  Automate. The reason I got so excited was that it provided a very elegant solution for creating a custom NFC sensor for a project I’ve been working on. What I also came to find out was that Automate opened up a lot of possibilities beyond this project.

Automate can be easily extended to prototype a ton of IoT projects. This tutorial shows how your Android phone can easily connect your physical world to any cloud service with a mashup of NFC, Automate and IFTTT.

Android IoT Automation with IFTTT

Example Use Case: NFC Enabled Sustainable Clothing Care

The project I’ve been working on is about improving the quality and sustainability of personal clothing care by instrumenting clothes with NFC tags. This way a clothing owner can be more careful about how they wash their clothes and more mindful about how they wear them. I had been looking for a good way to prototype this solution and my first idea was to create unique ID QR codes for all my clothing but that was a bit cumbersome. I had thought to wire up an Arduino but that seemed like a lot of work just for a prototype. I finally realized that the best way to build this prototype was sitting right in front of me.

Read More

Google Analytics to Google Spreadsheets is Data to Insights

When you reach the limits of Google Analytics custom reports and you still need more, Google Spreadsheets and the Google Analytics Add-On can take you past sampling, data consistency and dimension challenges.

This post is all about the Google Analytics + Google Spreadsheets workflow. It is an end-to-end example of how you can extract more value out of your Google Analytics data when you work with it in its raw(ish) form in Google Spreadsheets.

Blog Post Traffic Growth and Decay with Age

The end goal is to look at how blog post traffic grows or decays with time and ultimately Forecast Organic Traffic to Blog Posts (coming soon).  But this post sets the foundation for that by showing how the data is extracted, cleaned and organized using a pivot table in order to get there.

There is also a bit of feature engineering to make the analysis possible. To do this we will extract date that the post was posted from the URL and effectively turn the “Date Posted ” and “Post Age”  into custom dimensions of each page. But enough setup. Let’s start.

This posts assumes a few minor things but hopefully will be easy to follow otherwise:

  • Google Spreadsheets Google Analytics Add-On already plugged in
  • Basic familiarity with Regular Expressions aka. RegEx (helpful but not necessary)
  • Basic familiarity with Pivot Tables in Google Spreadsheets
  • Blog URLs with dates as subdirectories eg “/2015/08” (or collect post date as Custom Dimension)

Creating Your Custom Report

Once you have the Google Analytics Add-On up and running this is actually pretty simple. It just takes a bit of trial and error to ensure that you’ve queried exactly what you want. From the Report Configuration tab, most of the time there are only a few, but very important, fields that you will need to worry about: Date Ranges, Dimensions, Metrics,  and Filters.

Google Analytics Add-On Report Configuration

The purpose of this is to look at sessions by blog post URLs by month, over the entire history of the site

I chose the Last N Days because I know that is roughly the age of my site and a date range that is too broad is ok. I chose sessions because this relates to how many people will land on a given landing page in a given month of a given year. So that is all pretty straight forward.

Filters can get a bit tricky. A good practice for setting up filters is to first build them using the Google Analytics Query Explorer. That will enable you to rapidly test your queries before you are ready to enter them into your spreadsheet.

There are several filter operators that you can use but I kept this one fairly simple. It consists of three components (separated by semicolons):

ga:medium==organic; Only organic traffic ( == means equals)
ga:landingPagePath=@20; Only landing page URLs that contain 20  (@= means contains)
ga:landingPagePath!@? Only landing page URLs that do not contain a query string because that’s mostly garbage. (!@ means does not contain)

I used ga:landingPagePath rather than page title because that can be less consistent than URLs. They are more likely to change and will sometimes show as your 404 page title. Blog post URL’s are a more consistent unique identifier for a post but it is important to note that sometimes people will change blog posts URLs. We will deal with that later.

Cleaning the Data for Consistency

Even with good data collection practices, cleaning the data is extremely important for accurate analysis. In my case, I had changed a couple blog post URLs over time and had to manually omit a few that my query’s filter did not catch. In this case, data cleansing becomes very important for two reasons: 1. Posts that are not combined by their unique and consistent URL will show as two separate posts in the pivot table which will skew summary statistics and 2. Posts that are not real URLs with a small number of sessions will really skew summary statistics. Consistency is key for Pivot Tables.

So in this case, for small exceptions, I just deleted the rows. For more common exceptions, I used the REGEXREPLACE function. This is a powerful tool for data cleansing and unique to Google Spreadsheets. It allows you to select a part of a string that matches a RegEx pattern and replace with with whatever you might want. In this case, I just searched what I wanted to remove and replaced it with an empty string. eg.

=REGEXREPLACE(A141,"(/blog)?([0-9/])*/","")

I used this to remove ”/blog” from the URLs that were used before I transitioned from Squarespace to WordPress and date numbers because some had been changed when the post was updated.

Extracting Blog Post Posting Dates

Extracting the date that the post was posted is actually pretty simple. Again, I used another RegEx function, REGEXEXTRACT to do it:

=SPLIT(REGEXEXTRACT(cell,"[0-9]{4}/[0-9]{1,2}"))

The RegEx pattern finds any four digits then a slash and any one or two digits. Then the extracted string is split into two cells by the slash. This yields the year in one column and the month in the next. I combined the month and year of the post date and the month and the year of the Google Analytics date into Date objects so that I could use the DATEDIFF function to calculate the age of the blog post as a dimension of the post. Maybe this is too much gory detail but hopefully it’s useful to somebody.

Finally, we end up with is something that looks like this. This allows for pivoting the data about each post by each month of age.

Google Analytics Add-On Report Breakdown

Step 4: Pivot Tables FTW!

Finally, all this work pays off. The result is one table of blog post URLs by Age and one table of URLs by summary statistics.

Google Analytics data pivot table

The blog post age table allows me to see if the posts show a natural growth or decay of traffic over time. The big purpose of the table is to create the chart below that basically shows that growth and decay don’t look that consistent. But this is an important finding which helps frame thinking about modeling blog traffic in 2016.

(Not shown here, the Google Spreadsheets SPARKLINE function is a great way to visualize Google Analytics Data.)

The summary statistic pivot table will be used for forecasting 2016 traffic. This just happens to be the topic of my next post in which the tables are used to answer 1. What is the likelihood of reaching 25,000 sessions in 2016 if things stay the same?  and 2. What needs to change in order to reach 25,000 sessions in 2016?

So in summary, so far, this post has proven a few things:

  1. If you work with Google Analytics and don’t know RegEx, go learn! Its an incredibly useful tool.
  2. My blog posts do not demonstrate consistent growth or decay
  3. I might just be as big of a nerd as my girlfriend thinks I am.

Hope it might be useful in thinking about similar problems or maybe even creating new ones!

How To Add Multiple Columns to Google Spreadsheets

I recently ran into this problem when I was looking at someones Adwords account and wanted to get an idea about all the unique keywords that they were using.  I used the following formula to get an array of all unique keywords:

=UNIQUE(SPLIT(CONCATENATE('Keyword Report'!B2:B)," "))

But then I encountered a problem:

Google Spreadsheets #REF! Error

Error Result was not automatically expanded, please insert more columns ([number-of-columns]).”

Soon, I found that the current best option is to highlight and copy a number of columns and insert the copied columns over and over until there are enough columns. It seemed there should be a better way. After all, you can add an arbitrary number of rows to a spreadsheet. So just like the script to delete multiple sheets, I made the simple functionality.

The script looks like this:

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var lastCol = sheet.getLastColumn();
var maxCols = sheet.getMaxColumns();

function onOpen() { 
 // Try New Google Sheets method
 try{
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Sheet Expander')
  .addItem('Add Columns', 'Add Columns')
  .addToUi(); 
 } 
 
// Log the error
 catch (e){Logger.log(e)}
 
// Use old Google Spreadsheet method
 finally{
  var items = [
  {name: 'Add Columns', functionName: 'addColumns'}
 ];
 ss.addMenu('Sheet Expander', items);
 }
}

function addColumns(){
  // Input box asks how many columns
  var numCols = Browser.inputBox("How Many Columns Would You Like to Add? (Max: " + (maxCols - lastCol) + ")");
  // That number of columns are appended to the right side of the sheet
  sheet.insertColumns(lastCol +1 , numCols);
}

Using the Script

  1. Open the Google Spreadsheet Script Editor from the menu: Tools > Script Editor…
  2. Copy and paste the script into a new script file and save: File > Save
  3. Refresh the Google Spreadsheet window.
  4. Once the pages has refreshed, there should be a new menu called “Sheet Expander”
  5. Go to: Sheet Expander > Add Columns, and enter the number of columns you would like to add.

Note: Google Spreadsheets claims to impose a maximum limit on columns within a spreadsheet. They say it is 256 but it seems like it is possible to add many more. Perhaps this depends on number of rows in the spreadsheet.

Hope this saves you some time.

Bulk Hide, Show, Delete & Copy Multiple Sheets in Google Spreadsheets

Update: This script now allows you to copy multiple sheets within one spreadsheet to either the same spreadsheet or a different one. If you want to copy the sheet to the same spreadsheet just input you current spreadsheet’s id when prompted. The spreadsheet id can be found in the Spreadsheet’s URL eg: docs.google.com/spreadsheets/d/SPREADSHEETID/edit#gid=0

If you are looking for a way to copy sheets from a template, check out the post on GoogleAppScripting.com

Too many sheets! I came across the problem of having a large number of visible or unused sheets in cluttering up my Google Spreadsheets often enough that I decided to solve it. This problem most often comes up when using Google Apps Scripts that auto-generate multiple sheets. If you have ever used the Google Analytics Google Spreadsheet Add-On, you know what I mean.

Too many sheets for one Google Spreadsheets

Using the Script:

Copy the code below into a script file in for the Google Spreadsheet you want to use it on, save the script file and reload the spreadsheet. A new menu will appear called “Spreadsheet Cleanup.” This menu has three options:

Hide Sheets, Show Sheets, Delete Sheets

Google Spreadsheets UI Menu

View the Google Spreadsheet Demo

Select an option and the spreadsheet will prompt you to enter a word or phrase to match the names of the spreadsheets you would like to take action on against. In other words… enter part of the name of the sheets that you would like to take action on into the box and then it will do the job pretty quickly.  Note that you can only use letters in the name. Numbers will not work. Feel free to edit the code though.  (At first I was using the Javascript match method to search for sheet names but the regex would not allow for the prompt box.) Anyway, that’s all you have to do.


Hope you can find it useful too!

You can also make a copy of the script here: http://tfox.us/gscript-on-github

Do not copy the script below. Copy from the Github Repo link.

The HTML formatting will break the Apps Script code.

The Google App Script:

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheetsCount = ss.getNumSheets();
var sheets = ss.getSheets();

function onOpen() { 
 // Try New Google Sheets method
  try{
    var ui = SpreadsheetApp.getUi();
    ui.createMenu('Spreadsheet Cleanup')
    .addItem('Show Sheets', 'showSheets')
    .addItem('Hide Sheets', 'hideSheets')
    .addItem('Delete Sheets', 'deleteSheets')
    .addItem('Copy Sheets', 'copySheets') 
    .addToUi(); 
  } 
 
  // Log the error
  catch (e){Logger.log(e)}
 
  // Use old Google Spreadsheet method
  finally{
    var items = [
      {name: 'Hide Sheets', functionName: 'hideSheets'},
      {name: 'Show Sheets', functionName: 'showSheets'},
      {name: 'Delete Sheets', functionName: 'deleteSheets'},
      {name: 'Copy Sheets', functionName: 'copySheets'},
    ];
   ss.addMenu('Spreadsheet Cleanup', items);
  }
}
 
function deleteSheets() {
  var deleteSheetsContaining = Browser.inputBox("Delete sheets with names containing:"); 
  if (sheetMatch(deleteSheetsContaining)){
    for (var i = 0; i < sheetsCount; i++){
      var sheet = sheets[i]; 
      var sheetName = sheet.getName();
      Logger.log(sheetName);
      if (sheetName.indexOf(deleteSheetsContaining.toString()) !== -1){
        Logger.log("DELETE!");
        ss.deleteSheet(sheet);
      }
    } 
  } else {
     noMatchAlert();
  }
}

function hideSheets() {
  var hideSheetsContaining = Browser.inputBox("Hide sheets with names containing:");
  if (sheetMatch(hideSheetsContaining)){
    for (var i = 0; i < sheetsCount; i++){
      var sheet = sheets[i]; 
      var sheetName = sheet.getName();
      Logger.log(sheetName); 
      if (sheetName.indexOf(hideSheetsContaining.toString()) !== -1){
        Logger.log("HIDE!");
        sheet.hideSheet();
      }
    }
  } else { 
    noMatchAlert();
  }
}

function showSheets() {
  var showSheetsContaining = Browser.inputBox("Show sheets with names containing:"); 
  if (sheetMatch(showSheetsContaining)){
    for (var i = 0; i < sheetsCount; i++){
      var sheet = sheets[i]; 
      var sheetName = sheet.getName();
      Logger.log(sheetName); 
      if (sheetName.indexOf(showSheetsContaining.toString()) !== -1){
        Logger.log("SHOW!");
        sheet.showSheet();
      }
    } 
  } else {
    noMatchAlert();
  }
}

function copySheets() {
  var copySheetsContaining = Browser.inputBox("Copy sheets with names containing:");
  var destinationId = Browser.inputBox("Enter the destination spreadsheet ID:");
  if (sheetMatch(copySheetsContaining)){
    for (var i = 0; i < sheetsCount; i++){
    var sheet = sheets[i]; 
    var sheetName = sheet.getName();
    Logger.log(sheetName); 
    if (sheetName.indexOf(copySheetsContaining.toString()) !== -1){
      Logger.log("COPY!");
      var destination = SpreadsheetApp.openById(destinationId);
      sheet.copyTo(destination);
    }
  }
   successAlert('copied')
  } else {
    noMatchAlert();
  }
}

// determine if any sheets match the user input
function sheetMatch(sheetMatch){
  for (var i = 0; i < sheetsCount; i++){
    var sheetName = sheets[i].getName(); 
    if (sheetName.indexOf(sheetMatch.toString()) !== -1){
      return true
    }
  }
  return false
}

// alert if no sheets matched the user input
function noMatchAlert() {
  var ui = SpreadsheetApp.getUi();
  var result = ui.alert(
   'No Sheets Matched Your Input',
   "Try again and make sure you aren't using quotes.",
   ui.ButtonSet.OK);
}

// alert after succesful action (only used in copy)
function successAlert(action) {
  var ui = SpreadsheetApp.getUi();
  var result = ui.alert(
   'Success!',
   "You're sheets were " + action + " successfully.",
   ui.ButtonSet.OK);
}

If you love Google Spreadsheets, level up your data visualizations with Sparklines. If you are learning how to use Google Apps Script code., checkout out some useful resources on how to learn javascript for Google Apps Script.

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.