Amazing Automations 1 - Email Signups
4.5 (50 ratings)
Instead of using a simple lifetime average, Udemy calculates a course's star rating by considering a number of different factors such as the number of ratings, the age of ratings, and the likelihood of fraudulent ratings.
3,524 students enrolled
Wishlisted Wishlist

Please confirm that you want to add Amazing Automations 1 - Email Signups to your Wishlist.

Add to Wishlist

Amazing Automations 1 - Email Signups

Automatically track email signups with Googlesheets, Google Scripts, Zapier, & Mailchimp
4.5 (50 ratings)
Instead of using a simple lifetime average, Udemy calculates a course's star rating by considering a number of different factors such as the number of ratings, the age of ratings, and the likelihood of fraudulent ratings.
3,524 students enrolled
Created by Danny Blaker
Last updated 7/2016
Price: Free
  • 2 hours on-demand video
  • 1 Supplemental Resource
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion
What Will I Learn?
  • Use Zapier to push data from Mailchimp to a spreadsheet
  • Create a dynamic, automated spreadsheet using advanced formulas including OR, VLOOKUP, HLOOKUP, and Nested IF statements
  • Customize Embedded Mailchimp Signup form to track email signups from as many landing pages as you want
  • Write and customize a google script that sends automatic emails
  • Create a spreadsheet framework and layout that is scalable and easy to modify
View Curriculum
  • Some prior spreadsheet experience is recommended but not neccessary
  • If you have never used spreadsheets before, I invite you to take my spreadsheet introduction course: Spreadsheets for Business Part 1.


In this Amazing Automation - the first of the series, Danny Blaker - Head of Data Analysis and Marketing at one of Australia's largest enterprise technology firms Icestorm Digital Solutions ( - takes you through automating the process of tracking your Email signup KPIs (Key Performance Indicators) Step-by-step.

In this one-off and invaluable course, you'll learn how to:

  1. create live, dynamic and scallable spreadsheets using advanced techniques and googlesheets functions. 
  2. Write a google script that automatically sends emails based on KPIs being met
  3. Use Zapier to collect email signups via the mailchimp API and push them to a spreadsheet

You'll learn the very same processes and create the very same tools that Danny uses in large firms, as well as startups.

This is a great opportunity to learn how custom marketing automation works for modern businesses.

Everything you create and learn in this course can be immediately applied to your own businesses or projects. 

The course is also filled with helpful advice, hints, tips, tricks, to make your google-sheets workflow faster and more optimized. 

If you're an entrepreneur - from running startups to mammoth, scaling corporations - this is a must do course for you, by an expert in the field!

Who is the target audience?
  • If you like ways to automate your workflow - then this course is for you!
  • This course is for anyone looking to learn about Zapier Integrations, Custom Mailchimp forms, Advanced Googlesheets frameworks, and to be introduced to google scripts.
  • The Course does not require any prior programming knowledge
Students Who Viewed This Course Also Viewed
Curriculum For This Course
12 Lectures
Amazing Automations 1 - Email Signups : by Danny Blaker
12 Lectures 01:50:49

Welcome to the first lecture - where we'll set up all the browser tabs required to begin creating the integration.


In course, I move through the material quite quickly. If you're having trouble following along at any point, I encourage you to pause the lectures - or repeat sections if you have to. You can also activate Closed Captions for each lecture in the video player.

Additionally, you can also access the full transcript of the course - which includes additional material - from the resources section of each lecture.

You can also access a completed demo sheet via the resources section of this lecture.

To edit it, you'll need to create a copy for yourself -  so after opening the google sheets link - go to FILE - then MAKE A COPY.

I hope you really enjoy this course and get a LOT out of it - not just learning about Zapier and Mailchimp integrations, but also a lot of googlesheets techniques, tips, and tricks!

If you have any questions - or get stuck - don't hesitate to post your questions in the discussion threads.

Thanks! & Good luck :) 


Twitter: @DannyBlaker 


You'll need to create 6 worksheets as follows:

1. The Dashboard

2. Task (that you'd like to track. For example: "Blog")

3. Backend


5.Incomming Subscribers LIVE ZAPIER

6.Week Tracker Generator

Once you've done that, you're ready to move onto the next lecture :)

Spreadsheet framework

2 (continued). Use a VLOOKUP formular to find the current week automatically.

3. Add all the KPI (Key Performance Indicator) headers below:


KPI 1 Result

KPI 1 Remaining

KPI 1 % Complete

KPI 1 Type

Then do the same for KPI 2! (see video)

4.  Remove and clean up any un-used rows 

5. Use advanced conditional formatting to color in every second row (see video).

The formula you'll be using is:


Build A Task Worksheet PART 2

1. In columns A and B, fill out the basic details about the task.

Fields include:



Delegated To

Hours per Week

Current Week

Days Remaining

Channel (Traction channel)

Engagement Method

Task Strategy

Follow the video to setup each of the fields above :)

2. Add the weeks and date headers in rows 1 and 2, and follow the video to fill in their associated fields rapidly using some cool tricks!

Build A Task Worksheet PART 1

Formula for colouring in every second row
1 question

1. Go ahead and add a KPI for week 1 in your task tracking worksheet.

2. Go to (and sign-in), then follow the prompts in the video.

When you're up to adding a hidden text field into your sign up form remember to add the following:

for Field label, enter:

Signup form location

and for Field Tag, enter:


(all capitals)

3. In Sublime Text (download from - if you haven't done so already) press ctrl or cmd F - then search for "Form action"

4. Add the correct line of code at the end of the line (see video)

5. Paste the code into your first blogger post, then repeat steps 3-5 for the second post. (see video)

6. Add one subscriber to the mailchimp list you're using (so Zapier can use it in the next step!)

Custom Mailchimp Embeds

Custom Mailchimp Embeds
1 question

Now the real fun begins! Once you get into Zapier, I have a feeling you'll be dazzled and inspired by what it allows to do. And I'm sure you'll be able to think of many other ways you can use it for your projects / ventures / personal productivity!

1. Let's start by adding the following headers in the "Incomming Subscribers" worksheet or your spreadsheet:

Email Address

First Name

Last Name

Signup Form Location

Date Added

2. Setup your trigger app (Mailchimp) - see video

3. Setup your action app (Googlesheets) - see video

In the final part of the action app setup, you'll need to assign the correct mailchimp API field to the correct header in your spreadsheet (see video).

Make sure they are matched as follows:

firstname + Merges Fname

emailadress + Email

signupformlocation + Merges Signup

dateadded + Timestamp

lastname + Merges Lname

(NOTE: If the fields turn up in the wrong (different) order in Zapier don't worry - that's normal!) 

Zapier Integration

In this lecture, we'll fill out the week-tracker generator sheet in a way that allows our spreadsheet to count email signups on a week-by-week basis (so we can later analyse it via some charts!)

Start by dragging in the signup form location data + the Date added data from the Live zapier sheet. (see video)

Then convert all the dates to integers using the =INT( function

Next add week headers in row 1 of the sheet,

And create some OR statements to test which week the date of each email signup falls in.

Week Tracking PART 1

Now back in the Task sheet (the "blog" sheet in the video), add the following formular in D4 for the first KPI result entry.

=IF(D3="","",(COUNTIFS('Week tracker Gen (DONT TOUCH)'!$A:$A,"Landingpage1",'Week tracker Gen (DONT TOUCH)'!D:D,"TRUE"))) (See video for explanation!)

Next finish off the KPI fields in column D - then drag and copy them across to populate the rest of the weeks.

Finally - re-do conditional formatting to colour in every second row.

Week Tracking PART 2

Let's start by setting up the KPIemailer sheet according to the video instructions. This is the calm before the storm! :P

google script (auto emails) PART 1: Getting ready!

PLEASE NOTE: You may or may not find this part of the course challenging - please do not hesitate ask for help in the discussion threads at any point :) !

Begin by opening the script editor in your google sheet (see video), and either type in or paste the following code. (I recommend typing it so you learn it more efficiently!)

NOTE: the notes (or comments as they're called in coding) are indicated by the // symbols. 

I've also attached an image of the code in sublime text to show you the syntax.

Here's all the source code below, along with notes to describe each step:


// This first variable is going to be used when  // we're ticking off which KPI emails have already been sent. var 


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

google script (auto emails) PART 2: THE SCRIPT!

Welcome to the final lecture of the course! And Congratulations on your progress thus far J - you are certainly becoming a spreadsheet wizard integration wizard!
Now we’re going to bring it home by creating a dashboard worksheet where we’ll centralize our KPI data across all the tasks that we’re tracking.
Let’s begin!
1.   Add the following headers in row 1



KPI Type

KPI (Current week)

Total (Current week)

Remaining (Current week)

KPI Total

Total KPI Result



2. delete all unused columns.

3. In the first 4 columns, enter all the information about the first task your tracking manually (see video)

4. In the next 3 columns - Columns E to G - use Hlookup statements to pull all the KPI data of this task from the current week you're in. (See video)

5. In columns H and I, use SUM formulars to calculate the sum of your KPIs. (See video)

6. In columns J and K - use googlesparkline formulars to create mini graphs for each of the required cells  (See video)

7. Create a chart to compare your KPI results with the KPIs you set.

The Dashboard

Congratulations! You've done it. Now you'll be able to use the skills you've acquired to automate your workflow in other ways, as well as apply the spreadsheet tips you learnt to other spreadsheets you create!

For more of my courses, visit my instructor profile at :)

And please do come find me and say hi at:

TWITTER: @DannyBlaker

and connect with me on Linkedin!


Thanks and talk soon!!! :)

9. Conclusion
About the Instructor
Danny Blaker
4.4 Average rating
322 Reviews
13,587 Students
3 Courses
Entrepreneur, Marketing tour-de-force, and Concert pianist

Danny is a data analyst, content marketing expert, growth expert, and entrepreneur based in Melbourne, Australia. He 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, including GearTooth, Unudge, Gighoo, and Leanstartup Chat. Danny has previously worked on enterprise-level online platforms for large-scale corporate clients including ESI Insurance, NCIS Insurance, BE Infrastructure, CorusApp, Monash University, and ARIA amongst others. 

Danny's diverse skill set encompasses disruptive marketing strategy, content marketing, business strategy, product design, data analysis (R, R Markdown, Python), graphics design, communications, social media marketing, project management, growth strategy / growth OPs, UX design,  and corporate law.

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

Danny also blogs regularly – you can find his posts at his website. Read his latest post on email marketing automation using google scripts!

You can reach Danny on twitter @DannyBlaker

He also recently wrote a kids coding book to teach kids datascience. Check it out here on his website aswell! :)