Automatically track email signups using Google Scripts, Zapier, and Mailchimp

Tracking Kpis - Where to begin

 

Tracking KPIs (Key Performance Indicators) on a regular basis is a very important part of execution in every startup. However, tracking them manually can be time consuming, prone to error, and difficult to keep current figures up to date in real-time. This can lead to a lax approach to tracking KPIs, and sometimes it can be difficult to get everyone in your team to take them seriously.


One solution is to use softwares and suites to aggregate your data - downside there is that they’re often costly, and unnecessarily complex and in-depth for what is required at an early stage startup. 


Instead, we’ve found it far more effective to create a spreadsheet that's fully customized to only track the KPIs that you need at any given time, and that's setup so you can add more as you scale. Ofcourse, once you outgrow the spreadsheet, you can easily transition to an enterprise analytics SaaS.


(And since I’m a spreadsheet wiz, and have published entire courses on spreadsheets, I just couldn’t resist…)

What we’re about to build

A rather hardcore, completely automated google spreadsheet that:



1. Uses custom embedded mailchimp signup forms to collect email signups from 2 separate landing pages


2. Uses Zapier to push the email signups to the spreadsheet


3. Calculates KPI progress and produces basic analysis and charts

And finally

4. Uses google scripts to Send an automated email out to the entire team when a KPI has been achieved - (even when the spreadsheet is closed).
 

Which means you simply have to set the KPI for the week or month - and the spreadsheet takes care of the rest.

 


Why?



Cause you’re a LEAN MEAN STARTUP MACHINE.

….and because you can customize it with zapier to track many more KPIs - including Twitter followers, facebook likes, even user signups from a database etc …


BUT MAINLY BECAUSE YOU’RE A LEAN MEAN STARTUP MACH- ok you get the point.

 

 

Before you begin

 


There is indeed some coding involved in the google script & mailchimp integration, but if you don’t have coding experience don’t fret - I’ve given you all the code snippets I wrote - which you can just copy and paste.

If you'd like the full experience, I made a short, completely FREE video course which includes the full build of the spreadsheet, and takes you through all the formulas. so go for your life!

COURSE:

https://www.udemy.com/amazing-automations-1-email-signups/

Step 1 - Landing Pages

www.leanstartup.chat : KPI Tracker 1 - set up blogger http://www.leanstartup.chat/index.php/2016/05/09/automatically-track-email-signup-kpis-using-google-scripts-zapier-mailchimp/

First up, we’ll create 2 landing pages to test the spreadsheet and mailchimp integration. If you already have custom landing pages set-up, just skip to Step 2.  Otherwise, the quickest way to do this is to use blogger - which you already have access to if you have a gmail account. If not create one. 


Head over to your blogger account or create one (www.blogger.com)

Go to My Blogs.

Create a new blog with “Landing page 1” in the title

Create a new page and open edit mode.

Leave this page open for now, and open blogger again in a new window.

Create another new blog - this time with Landing Page 2 in the title.

Then create a new page in that one, open in edit mode and leave this page open as well.   

 


Step 2 - Mailchimp Embed

www.leanstartup.chat: KPI Tracker : 2 - Signup Forms http://www.leanstartup.chat/index.php/2016/05/09/automatically-track-email-signup-kpis-using-google-scripts-zapier-mailchimp/

Open mailchimp (create an account at mailchimp.com) and a create new list

Mailchimp - very cool-y - allows you to track where signups to an email list originate from across multiple landing pages. Doing so requires a small hack to the embed code.
(NOTE: this only works for embedded mailchimp signup forms)

Create a mailchimp list -  name it anything you like!

Go to the Lists page (https://us1.admin.mailchimp.com/lists/?_ga=1.96046044.1361281255.1445644863)

Click on the dropdown menu and select Signup forms

Choose General Forms

www.leanstartup.chat: KPI Tracker : 3 - Add text field http://www.leanstartup.chat/index.php/2016/05/09/automatically-track-email-signup-kpis-using-google-scripts-zapier-mailchimp/

Go to the Build it tab


Click on Add a field tab and click Text

In Field settings, add the following

Field label : Signup Location

Field tag: SIGNUP

Then choose the hidden option in the visibility section

Save the field

www.leanstartup.chat: KPI Tracker : 5 - Find Form Action http://www.leanstartup.chat/index.php/2016/05/09/automatically-track-email-signup-kpis-using-google-scripts-zapier-mailchimp/

Next to save time, press CTRL+F or CMD+F and lookup the phrase “form action” (all lower case).

Now you should have located a line of code that looks similar to this:

[cc lang="html"]
form action=”//mailchimp.com.us8.list-manage.com/subscribe/post?u=e5baa4823db5b71c884700f56d&id=d12bb1307f7
[/cc]

At the very end of that line, insert the following:

[cc lang="html"]
&(field label name)=(the name of the landing page your tracking)
[/cc]

So for example: The field name you set was SIGNUP, and the name of the landing page your tracking can be anything - lets say landingpage1

Therefore you would write:

[cc lang="html"]
&SIGNUP=landingpage1
[/cc]



And when you add that to the code it should look like this:

[cc lang="html"]
form action=”//mailchimp.com.us8.list-manage.com/subscribe/post?u=e5baa4823db5b71c884700f56d&id=d12bb1307f7&SIGNUP=landingpage1
[/cc]

Great - now that done - copy the whole embed code - and leave the text editor open for now.

Then go back to your first blogger landing page you opened earlier.

Click the HTML tab

And paste the code in without altering anything.

Click update.

Now go back to the text editor.

Change the code you edited from landingpage1 to landingpage2.

Then copy all the code again.

Open your second blogger page, paste into the html section, then save.

Great - now you have 2 separate landing pages with unique embedded mailchimp signup forms

Next up - let’s get this spreadsheet happening.

 

 

Step 3 - Setup Spreadsheet


The spreadsheet template I created is quite detailed and complex - so to save time (like a lot of time!), start by opening it below. 

https://docs.google.com/spreadsheets/d/1XkVasulaTVanbAfchLUaBB88cxOITXPE6POrP2WQIac/edit?usp=sharing

 

Then go to File -> Make a copy

//
SHORT INTERLUDE:

Now you have your own copy of this powerful little template in googledrive to customize for yourself.

Take a minute to become acquainted to how I’ve set up the dashboard and blog subscription sheets - which are main ones you’ll use.

The blog subscription sheet is a template for a task (for which you want to track KPIs). To create a new task just duplicate this sheet, rename it to be the task you wish to track, then add the necessary components to the dashboard sheet. 

(If you need help at this stage or have any questions just leave comments at the bottom of the post or twitter me @dannyblaker! Alternatively find on leanstartup.chat Slack)

Now - time to get our sheet ready for the Zapier API

END OF INTERLUDE
//

In the LSCHAT Demo Sheet, click on the Incoming Subscribers LIVE ZAPIER work sheet (at the bottom)

Now delete all the entries in every row except the first 2 rows. (The reason is 1. because we want to start with a clean slate, and 2. Zapier requires one line of demo entries to match against the mailchimp fields)

K that done - time to watch Zapier wield the mailchimp API like Zeus.

 

 

Step 4 - Zapier Integration

www.leanstartup.chat: KPI Tracker : 6 - Zapier P1 http://www.leanstartup.chat/index.php/2016/05/09/automatically-track-email-signup-kpis-using-google-scripts-zapier-mailchimp/

Go to www.zapier.com, and create a Zapier account (if you don’t have one yet).

Go to My Zaps

Make a Zap

Set trigger app as Mailchimp

Select New Subscriber as your Mailchimp Trigger

Select your mailchimp account

Select the Mailchimp List we created way back in Step 1 (The same one you used to get the embed code)

Read through the “Things to double check list”.

Proceed to test.

www.leanstartup.chat: KPI Tracker : 7 - Zapier P2 http://www.leanstartup.chat/index.php/2016/05/09/automatically-track-email-signup-kpis-using-google-scripts-zapier-mailchimp/

Next Choose Google Sheets as your Action App

Choose create a Spreadsheet Row for your google sheet action

Connect your google sheets (google) account.

Next Select your target Spreadsheet

And then the worksheet within it that will be the destination sheet for all the mailchimp subscribers (select the “Incomming Subscribers (DONT TOUCH)” worksheet)

www.leanstartup.chat: KPI Tracker : 8 - Zapier P3 http://www.leanstartup.chat/index.php/2016/05/09/automatically-track-email-signup-kpis-using-google-scripts-zapier-mailchimp/

Add the following fields (making sure they’re matched to the right field entries):

Gsx$firstname
1Merges  FNAME
Gsx$emailaddress
1Email
Gsx$signupformlocation
1Merges  SIGNUP
Gsx$date
1Timestamp
Gsx$lastname
1Merges  LNAME

Proceed to the Testing phase - Once “Test Successful” pops up click Finish

Done. Zapier will now push all new subscribers for mailchimp list to your google sheet so you can begin tracking KPIs.

 

 

Step 5 - Automated email notifications (Google script)


Go back to your spreadsheet

Navigate to the worksheet “KPIemailer”

In the first entry cell of the email column (A2) , add the email address that you’d like to send notifications to

(to add multiple emails just separate each one with a comma).

Now drag and copy the cell down to the final row with entries in it - which is in this case row 31

Customize the message you’d like to send for each KPI in column B - or just leave them as they are. 

www.leanstartup.chat: KPI Tracker : 9 - Google Scripts http://www.leanstartup.chat/index.php/2016/05/09/automatically-track-email-signup-kpis-using-google-scripts-zapier-mailchimp/

Open the script editor by going to Tools -> Script editor


Rename the default script “EmailSender”

Copy and paste the code below.

[cc lang="js"]
// This first variable is going to be used when
// we're ticking off which KPI emails have already been sent.
var EMAIL_SENT = "EMAIL_SENT";

function sendEmails2() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("KPIemailer");
var startRow = 2; // Chooses the first row of data to begin looking through
var numRows = 30; // Specifies the number of rows to look through
var dataRange = sheet.getRange(startRow, 1, numRows, 30) // Fetch the range of cells A2:E31
var data = dataRange.getValues(); // Fetch values for each row in the Range.
for (var i = 0; i < data.length; ++i) { // create a for loop that will search through all the rows consequtively until it reaches the end of the data range - which you have already specified in the line above
var row = data[i]; // attach the data loop reference to a new variable called "row"
var emailAddress = row[0]; // First column
var message = row[1]; // Second column
var emailSent = row[2]; // Third column
var result = row[3]; // Forth Column
if (emailSent != EMAIL_SENT && result == "NONE") { // An If statement that prevents sending duplicate emails by checking 2 conditions. If both conditions are met, then it will proceed to the following line.
var subject = "KPI has been demolished"; // the message that will appear as the subject line in the email that is sent.
MailApp.sendEmail(emailAddress, subject, message); // Send an email with the email address, subject and message specified in respective column from each row of the data range
sheet.getRange(startRow + i, 3).setValue(EMAIL_SENT); // Once an email has been sent - this will label the respective cell in column C as "EMAIL_SENT"
SpreadsheetApp.flush(); // I've put this here so it updates the cell immediately incase the script is interrupted
}
}
}

[/cc]


Optional step: Customize the email subject how you desire -  located on line 20 of your code.

Go to Resources -> Current project triggers

Add a new trigger

Select SendEmails2 (should be only option)

Then Under events select From Spreadsheet and run the event On change

Hit done

Run the script (click the play icon up the top)

NOTE if you encounter any errors and are unsure what the problem is, post a copy of your code - along with your error message - either in the comments below, or go to LSChat Slack => Blog post questions channel, and post your code as a code snippet along with the error (recommended). 

And that’s it - you should now receive email notifications each time you reach a KPI that you’ve set in the spreadsheet. 
 

FAQ

How to Set a KPI

To set a KPI, go to the Blog Subscription template worksheet, then add a Kpi to any given week in Row 3.

How to Set the Starting Date

To set the starting date go to the BACKEND worksheet and alter the date in F2

I hope you've found this post useful! If you have any ideas you’d like us to explore re KPI tracking or anything else, head over to LSChat Slack and let us know! Cheers!

If you haven't joined our community of lean startup enthusiasts, you can do so via the link below

www.leanstartup.chat

 

And once again - If you're overwhelmed at all, or want more clarification for any of the steps, or would simply prefer a video tutorial - I highly recommend taking my course below :)

COURSE:

https://www.udemy.com/amazing-automations-1-email-signups/

Danny Baker

Danny Blaker, Melbourne

Danny has a wealth of experience in the start-up and technology sectors spanning over 10 years, and is the founder and co-founder of numerous companies and initiatives, such as Unudge, & Geartooth.

Danny's diverse skill set encompasses disruptive marketing strategy, business strategy, product design, audio production, data analysis (R, R Markdown, Python), graphics design (photoshop, Indesign, Illustrator), communications, social media marketing, project management, growth strategy, UX design, front-end web development (WP, Joomla, JS, Python, HTML, CSS), and corporate law.

Danny is also a spreadsheet expert and an online instructor, teaching at Udemy.com. His courses have amassed over 11,000 students to date.

He also blogs regularly – you can find his posts at www.dannyblaker.com/blog.

You can reach Danny on twitter @DannyBlaker