Dynamically Pre-fill Google Forms with URL Parameters

Why? Because, connect all the things! Why else? Because the less information people have to fill in to your form, the more likely they are to complete it – especially if you are asking them to fill in information that they will have to look up! Dynamically pre-filling forms is a great conversion optimization hack to show your prospective respondents a little love. This love will increase form completion rates and response accuracy.

The question originally came to from someone who had read my post about Mailchimp Reporting with Google Spreadsheets (same products but different applications) But hey, sometimes you don’t even know where to start your Google search! His question was this:

I send a reminder email to my customers through MailChimp. The email contains the customer’s account code and some other data unique to the customers. A link on the email sends the customers to a Google Form where they will answer some questions. I want to have the customer’s unique customer code populate a field so I that I know  who the response came from, and in doing so, reduce the number of fields a customer would have to complete.

Do you have a solution for this? MailChimp and Google apps said there isn’t, but Google did suggest it might be resolved with a script (which I have no idea how to do).

It seemed interesting. I thought about the script. I was hoping this would not be the answer because that sounded like trying to breed an ostrich and an alligator. Then I remembered a project I had worked on in tracking Wufoo form submissions in Google Analytics. That was an Ostrigator but hey, it worked!

The key was passing data around on query strings. I knew there was dynamic values in Mailchimp in the form of Merge Tags. Then all we needed was a link to Google Forms that would allow you to pre-populate the forms. Lo and behold: Pre-populate form answers! So all we would have to do is match the right merge tags as to the right form URL query string parameters and we would have pre-populated forms from the dynamic values in the emails. #masscustomize all the things!

Merge Tags in Google Form Link Parameters

Step 1. Get a Pre-Filled Google Form URL

To get started, go to your Google Form editing page and click responses. Select “Get pre-filled URL”

Hubdango Google Forms

Step 2. Pre-fill the Form with Merge Tags

Find the merge tags that you want to use and enter them into the form boxes. *When you do this, make sure to use merge tags that are accurate for your whole mailing list. Missing information is not a problem but if your merge tags are inaccurate, this could cause confusion and/or complaints.

User Validation

Step 3. Copy the Pre-filled Google Form URL

The merge tags have been appended to to the form’s URL as query string parameters. Now you have your link to your Google Form that will automatically fill the values of your form with the values of your Mailchimp Merge Tags.
User Validation 2

How Query String Parameters Work

At this point, you may be wondering how this works. You have a URL that looks like this:

https://docs.google.com/forms/d/1MnRQJ3XZxsRBSVp28M7Sm1M8Gm5jniE/viewform

followed by this:

?entry.1040949360=*%7CFNAME%7C*&entry.271521054=*%7CLNAME%7C* ...

The part after starting with with the question mark (?) is the query string. It is made up of key-value pairs connected with ampersands (&). Query strings are used to pass information to webpages or “resources.” The server that handles the request will know what the query string’s keys mean and depending on each key’s value, will dynamically generate or modify the resource. (See how query strings works with a API’s) In this case, Google’s server just takes the value of each secretly encrypted key and places the value in the form field associated with that key.

But why does the ‘First Name’ Merge tag look like *%7CFNAME%7C* ?

You have seen strings on the internet, often on URL’s that have something like “%20” in them. This is called URL encoding. It is one of those web standards that allows the magic of the web to work. Because the pipe character (|) is not a normal character it is encoded after the percent symbol as “%7C” .

Don’t worry when you paste this into your Mailchimp email, Mailchimp will automatically decode the URL and it will end up looking like this:

?entry.1040949360=*|FNAME|*&entry.271521054=*|CLNAME|* ...

Now go answer every other question you have ever had about URLs.

Now that you’re back, go setup your new email campaign.
Mailchimp Merge Tags in links
Then when your email recipient and prospective form respondent gets their Mailchimp email, the link will be dynamically modified to look like this:

?entry.1040949360=Trevor&entry.271521054=Fox ...

and… Hello World! The Mailchimp has correctly rendered the links with the dynamic values from the merge tags.
Rendered links with Mailchimp Merge Tags

Digital Marketing is Science + Art

When it’s all said and done, an appealing email subject like and effective copy will only get respondents to go to the form page. After that, their is a lot left to optimize. As digital marketers, we are doing much more than trying to persuade. We are delivering a message, removing friction and optimizing experiences. We are measuring and testing all the way! Don’t forget, our job is just as much about perfecting the message as it is the medium.

To build more Digital Marketing Technical Skills checkout my $10k Tech Skills Series.

 

 

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.