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

EXCITING!!

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 :) 

Danny.

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

4.KPIemailer

5.Incomming Subscribers LIVE ZAPIER

6.Week Tracker Generator

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

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

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

KPI 1

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:

=iseven(row())

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

Fields include:

Task

Priority

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!

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

2. Go to mailchimp.com (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:

SIGNUP

(all capitals)

3. In Sublime Text (download from sublimetext.com - 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!)

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!) 

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.

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.

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

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 

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
    }
  }
}

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
 
Channel

Task

KPI

KPI Type

KPI (Current week)

Total (Current week)

Remaining (Current week)

KPI Total

Total KPI Result

KPIS

Results

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.

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 https://www.udemy.com/user/dannyblaker/. :)

And please do come find me and say hi at:

TWITTER: @DannyBlaker

and connect with me on Linkedin!

LINKEDIN: https://au.linkedin.com/in/dannyblaker

Thanks and talk soon!!! :)