machine learning

How We’re Using Machine Learning to Score Leads in Google Sheets – Part 3

Whoa. It’s the latest article in our trilogy of pieces on using simple machine learning to score your leads. As promised last time, we’re going to help you automate this stuff.

Ever heard of Zapier? Zapier is the jiminy. We’re gonna use us some Zapier to pull leads into our spreadsheet.

We’re going to draw our leads from Intercom, which is a service we can only recommend. (We’re not getting paid for these recommendations, just by the way.) You can use whatever system you like as long as it has a set of Zapier integrations – the principles remain the same.

Let’s do this thing.


Step 1: Set up your sheet

First up, we need to add some headings to a sheet; this is so that we can tell Zapier where to put the data it’s yanking out of Intercom. Let’s lay it out like so, shall we?

Intercom doesn’t give us all the lead-scoring info we want, but it gives us three very useful pieces. Has the lead unsubscribed from mails? Marked our mails as spam? Does their email address bounce? Which is to say, obviously you’ll want a more sophisticated implementation, but shhhhhh. This is just for showsies.

One thing: that Has Upgraded header? We need to come clean and tell you that’s a custom field we’ve created in Intercom; we fill it via API from our own software.

Step 2: Get your Zap on

Now we shall undertake the Zappery. We’ll dispense with the actual setting up of an account and assume you have the killer instinct to get the job done.

Start by, yes, creating a new Zap. In the first view – for choosing your trigger app – we’ve typed in ‘Intercom’ and selected the app from the dropdown, then clicking Continue. (At this point you might need to faff a bit and connect your account to Zapier. We’ll skip that, yes?)

As a trigger, select New User.

Now, select your action app. Much like the first view, we’re typing in Google Sheets and selecting it from the dropdown.

For your action step, click Create Spreadsheet Row. After this you’ll have to link up your Google account. You’ve got this, recruit.

Now’s when you pair up the data Intercom sends you with the sheet you set up. Remember those headers? They’re going to come in handy.

First, select the spreadsheet you want from the dropdown list; then select the specific worksheet you’re looking for.

Now, under each heading that subsequently populates the view, select the Intercom data you’d like to fall under it. This should be pretty straightforward. At the end of it you’ll have a view that looks like this:

Click continue, then test it all out and head over to your worksheet. If the test ran successfully, you should see a new row under your headings.

Smashing! Your spreadsheet will now automatically update every time a new user signs on to Intercom.

Now let’s wire this up to the Bayesian algorithm we set up last time around.


Step 3: Open the pod Bayes door, HAL

Time to get our machine learning thing started. What we’re going to do here is pretty much identical to what we did in part 2 of this series – only we’re going to use real-world data. In fact, go right ahead and copy the old Bayes scorer from last time. You’ll see it’s kinda full of errors:

Also the labels are a bit iffy and we need to simplify matters somewhat. Let’s rename and delete and end up with something like this:

That’s better. Now let’s do something about all those pesky errors.

First, let’s get a count on the number of upgrades in B1:
=COUNTIF(‘Lead List’!G:G,TRUE)

And the converse in D1:

Then we’ll clean up the probabilities just beneath. B2:

Now. Now we’re going to sort out the frequency counts for each condition. Here’s C5:
=(COUNTIFS(‘Lead List’!D:D,B5,’Lead List’!G:G,TRUE)+1)/(B1+1)

And the converse in C6:
=(COUNTIFS(‘Lead List’!D:D,B5,’Lead List’!G:G,FALSE)+1)/(B1+1)

You’ll want to repeat this in the remaining rows.

Okay, nearly there. Let’s work out our relative probabilities. At B9, slot home the following formula:

And under D9:

LAST STEP! Our little recommendation cell is going to take the following formula:

That, as they say, is that. Kinda.


Step 4: Going forward

Every new user will update the Bayes classifier’s frequency table, which means better results over time. But but but – you may have noticed that the spreadsheet rows for each user are static, meaning they won’t update when things change.

The good news? It’s possible to get Zapier to update your spreadsheet rows when things change. The how of this is beyond the scope of this article – which is really just meant to set you on the path – but it’s super straightforward.

This is obviously an extremely limited example, but we hope you can see immediately how, with a little elbow grease and creativity, this could turn into an extremely useful tool for you.