Simple automations you can add you to your CRM to keep it clean and looking great.
Academy ↓
Hey there stranger!
CRM Automations
About this Tutorial
Sheet Resources
Video Transcript
00:00 We're going to create some CRM automations. We have a list of leads here and it's a very basic list with names, lead sources, status, we have a sales rep, we have the contact date, deal size, last follow-up, some notes, but we want to make it really cool.
00:18 So let's first do a few things. One, we're going to enter days since the customer entered the pipeline, we're going to identify some high-value deals, we're going to calculate the next follow-up date automatically so we don't have to fill that out by hand, and we're going to track overdue follow-ups,
00:38 which means we can find out what have we left behind, and once things become lost, we may not want to keep them in our leads list, so we'll archive those with Apps Script.
00:48 At the very end, watch out for that, that's going to be an awesome automation, we're going to learn Apps Script along the way.
00:53 Let's first create a dropdown selector for our source, first, let's select all of our sources that we've handwritten here, right-click, scroll down to dropdown, and automatically our, on the right side, the data validation rules will be filled in with these options.
01:11 Now, we're going to go ahead and create a dropdown, and we're going to go ahead and our sources, right-click, scroll down to dropdown, and automatically our, on side, the Again, with lead status, we'll do the same.
01:39 We'll select all of the lead statuses, right-click, and scroll down to dropdown, and again, automatically Google Sheets does this for us.
01:46 We have all of the options here that we have already listed. If we want to add another one, let's say needs follow-up, we just need to add it down here at the bottom.
02:00 So maybe that's right. Red there, qualified, green, good to go. New lead, maybe we'll leave that as gray. Contacted, maybe we'll leave that as gray.
02:07 Cold, closed one, maybe dark green. And closed loss, we'll actually get rid of this eventually. Let's click done. And now this makes it super easy to select this and move it through the pipeline.
02:22 Let's say we have here. So we've made some drop-down selectors. Next, let's create days since the customer entered the pipeline.
02:35 So I have it here in the K column. I'll delete it to show you how we do this. So the contact date is this E column.
02:43 So days in pipeline is going to be date, diff. And our start date is going to be the K contact date, and comma, we'll use a function called today with parentheses.
02:54 And that today will always be changing every single day. So we will find out what is the difference between these two days.
03:02 And the unit in quotes, or double quotes, is D. This is days. If you want months or years, you can use M or Y there.
03:13 Suggest autofill this, yes. And now we have days since this contact. Let's identify some high-value deals next. High-value deals are going to be here in deal size.
03:30 Let's highlight everything that's above $10,000, or $10,000 or above. So we're going to select that. Select this F column format, conditional formatting.
03:41 Now we can simply format cells rules if and greater than, ah, let's say 999. We could also do greater than or equal to and put 10,000 here, because that's a nice pretty brown number.
03:57 We want these to be hot green, maybe, and maybe even a different tan. X color, hot. Done. Those are our high value deals there.
04:05 Next we want to calculate follow-up dates. This is really important if we are using this contact date and a last follow-up.
04:16 We're going to manually fill in here when we are following up with them. When's the last time? But we want to know when's the next time we should follow up.
04:23 Now this is tricky, because we're going do equals this date plus seven, let's say, and have it at that, and maybe we put around here if is blank G2, and now only if there's an actual thing there, we can even delete these hyphens there, and we're have a next follow-up date.
04:50 But what if the last follow-up was on a weekend, and we don't necessarily want to schedule the next follow-up on a weekend as well?
04:58 And we have a very specific number of work days that we want to differentiate them. So we're going to use equal work day, And this function allows us to create a, use a start date, and then select a number of days after.
05:15 So if you want one week, like one full week, we'll do five, because it'll skip the weekend. But maybe we want something like ten work days, business days.
05:26 We'll change that. We have some interesting numbers here because it's blank, so we'll wrap this again with if is blank.
05:37 Thank G2, two commas and end parenthesis. There you go. Now the blank ones go away. But now, this February 17th is not going to be next follow-up until March 3rd, two weeks later, because it's ten working days later.
05:58 Very cool. And again, if you just want one week, but want to use this workday, just do five. And that'll be exactly one week later.
06:06 Again, business days, work days. So let's track overdue follow-ups. What if we had put in the last follow-up was two weeks ago, and this February 11th doesn't really, pop out.
06:26 We have to look at every single follow-up date here and see which ones are past, but let's make it really pop out.
06:32 So we're going to select our H column format, conditional formatting. We can use date is before today. Click done, scroll over and see that it is highlighted.
06:48 We can change that color. Maybe we want it to be deep red with some white text perhaps. Click done and again we can see that that is the overdue one.
07:00 So that's pretty cool to automate that right away. It sticks out instead of having to read each and every one of these calendar dates.
07:08 So now, once we select, let's say, a lead status goes to lead status. Let's look at these completely. We don't want to keep this in our leads list.
07:21 We want to keep this leads list pretty lean to only the ones we're working on. Even one we might want to put somewhere else.
07:29 But the closed loss we'll work on right now and you can always do this again with closed one. Let's create a new sheet called lost.
07:37 And we're going to copy the entire header range and put that there. We can even delete all of the rows if we want, leaving just one row perhaps.
07:49 We're going to go up to extensions app script. We're going to use a built-in function. This built-in function is going to be called onEdit.
08:05 We're going to use e as an event here. This e variable means whenever an action happens that is an edit, meaning the user is editing something in the sheet, we're going to gather some interesting information.
08:17 We can gather the row we're on by doing variable row equals e.range.getRow. e.range.getColumn, with a capital C. We can find out what sheet we're on.
08:34 Equals SpreadsheetApp.getActiveSheet.getName. and see our list that we're looking at is leads.
08:53 So, we're going to start creating an if statement, basically saying, if all of these things are true, if sheetName is equal to leads, then do something.
09:04 But we want to also make sure and row is greater than 1, and with two ampersands, column. Is equal to whichever column we're on the lead list, so C or 3 column.
09:20 We'll use 3 here. We also want to make sure we get the actual value of what we're editing. So, value equals E.value.
09:32 And we want to make sure that this and value is equal. Is equal to closed, lost. Let's make sure that is, oh, there's no hyphen there.
09:48 So, let's make sure that's exactly correct. So, once we edit in column 3, we're editing a row that's underneath the header.
09:58 We're on the leads list, and our value is closed, lost. We're going to do the something here. First thing we're going to do is copy the row we're on.
10:08 So, let's get variable ss equals spreadsheet app dot get active spreadsheet. So, we're going to say ss.getRange, getSheetByName, getRange, we'll start at row we're on, column one, and we'll go for the maximum rows, however many rows there are here.
10:39 So, let's get that as well. Let's call this leads actually, getSheetByName, leads and we can click on this, just leads, and getVariableMaxColumns equals leads.getMaxAmountColumns. So, here Here we need to, say we want one row and max columns. Going to the first column, taking one row, and however many
11:19 columns there are in the sheet, and we're copying to We need a range here. Variable lostRange equals Let's get the variable lost This is lost Lost from a sheet, lost.getRange And we're going to probably want to do one thing. We probably want to insert a row. So let's do that. before two, To column one
12:03 , we're getting the maximum at one row and getting max columns because we'll have the same number of columns on both sheets.
12:13 And we're going to copy to lostRange. So let's see if this works. If we get an error with this, we'll figure it out as we go.
12:21 Let's go to our leads. Let's change this new lead to closedLost. It's William Taylor, and William Taylor is here.
12:33 So that is working. We're copying over the closedLost. Now, we need to delete William Taylor from here. So let's make sure we do that.
12:43 And again, we're only doing it if this value is closedLost. So let's go to leads. leads.getRange, and we're going to the row we're on, starting at column one.
12:57 Actually, we don't even need to do that. We need to delete row. And the row we're deleting is the row we're on.
13:11 And save. Let's double check that this is going to work. Thank you. So I'm going to change this back to contacted.
13:17 And William Taylor, we are going to say, is closed lost. And it's deleted from here. It's disappeared. And we have a second one here, right?
13:31 So let's do another one. Let's go from qualified meal walker to closed lost. It's disappeared from here and it's on our lost right at the top.
13:43 Very cool. So we did that with just this amount of code. We've automated our CRM and archived lost leads. And again, you can do that with any other status.
13:59 You can move basically just changing this value here. And then changing where it's going, which sheet it's going to, maybe it's going to a 1WON and it's closed 1WON. Hope you enjoyed this. Hope you enjoyed automating your CRM. I hope to see you again soon.
00:18 So let's first do a few things. One, we're going to enter days since the customer entered the pipeline, we're going to identify some high-value deals, we're going to calculate the next follow-up date automatically so we don't have to fill that out by hand, and we're going to track overdue follow-ups,
00:38 which means we can find out what have we left behind, and once things become lost, we may not want to keep them in our leads list, so we'll archive those with Apps Script.
00:48 At the very end, watch out for that, that's going to be an awesome automation, we're going to learn Apps Script along the way.
00:53 Let's first create a dropdown selector for our source, first, let's select all of our sources that we've handwritten here, right-click, scroll down to dropdown, and automatically our, on the right side, the data validation rules will be filled in with these options.
01:11 Now, we're going to go ahead and create a dropdown, and we're going to go ahead and our sources, right-click, scroll down to dropdown, and automatically our, on side, the Again, with lead status, we'll do the same.
01:39 We'll select all of the lead statuses, right-click, and scroll down to dropdown, and again, automatically Google Sheets does this for us.
01:46 We have all of the options here that we have already listed. If we want to add another one, let's say needs follow-up, we just need to add it down here at the bottom.
02:00 So maybe that's right. Red there, qualified, green, good to go. New lead, maybe we'll leave that as gray. Contacted, maybe we'll leave that as gray.
02:07 Cold, closed one, maybe dark green. And closed loss, we'll actually get rid of this eventually. Let's click done. And now this makes it super easy to select this and move it through the pipeline.
02:22 Let's say we have here. So we've made some drop-down selectors. Next, let's create days since the customer entered the pipeline.
02:35 So I have it here in the K column. I'll delete it to show you how we do this. So the contact date is this E column.
02:43 So days in pipeline is going to be date, diff. And our start date is going to be the K contact date, and comma, we'll use a function called today with parentheses.
02:54 And that today will always be changing every single day. So we will find out what is the difference between these two days.
03:02 And the unit in quotes, or double quotes, is D. This is days. If you want months or years, you can use M or Y there.
03:13 Suggest autofill this, yes. And now we have days since this contact. Let's identify some high-value deals next. High-value deals are going to be here in deal size.
03:30 Let's highlight everything that's above $10,000, or $10,000 or above. So we're going to select that. Select this F column format, conditional formatting.
03:41 Now we can simply format cells rules if and greater than, ah, let's say 999. We could also do greater than or equal to and put 10,000 here, because that's a nice pretty brown number.
03:57 We want these to be hot green, maybe, and maybe even a different tan. X color, hot. Done. Those are our high value deals there.
04:05 Next we want to calculate follow-up dates. This is really important if we are using this contact date and a last follow-up.
04:16 We're going to manually fill in here when we are following up with them. When's the last time? But we want to know when's the next time we should follow up.
04:23 Now this is tricky, because we're going do equals this date plus seven, let's say, and have it at that, and maybe we put around here if is blank G2, and now only if there's an actual thing there, we can even delete these hyphens there, and we're have a next follow-up date.
04:50 But what if the last follow-up was on a weekend, and we don't necessarily want to schedule the next follow-up on a weekend as well?
04:58 And we have a very specific number of work days that we want to differentiate them. So we're going to use equal work day, And this function allows us to create a, use a start date, and then select a number of days after.
05:15 So if you want one week, like one full week, we'll do five, because it'll skip the weekend. But maybe we want something like ten work days, business days.
05:26 We'll change that. We have some interesting numbers here because it's blank, so we'll wrap this again with if is blank.
05:37 Thank G2, two commas and end parenthesis. There you go. Now the blank ones go away. But now, this February 17th is not going to be next follow-up until March 3rd, two weeks later, because it's ten working days later.
05:58 Very cool. And again, if you just want one week, but want to use this workday, just do five. And that'll be exactly one week later.
06:06 Again, business days, work days. So let's track overdue follow-ups. What if we had put in the last follow-up was two weeks ago, and this February 11th doesn't really, pop out.
06:26 We have to look at every single follow-up date here and see which ones are past, but let's make it really pop out.
06:32 So we're going to select our H column format, conditional formatting. We can use date is before today. Click done, scroll over and see that it is highlighted.
06:48 We can change that color. Maybe we want it to be deep red with some white text perhaps. Click done and again we can see that that is the overdue one.
07:00 So that's pretty cool to automate that right away. It sticks out instead of having to read each and every one of these calendar dates.
07:08 So now, once we select, let's say, a lead status goes to lead status. Let's look at these completely. We don't want to keep this in our leads list.
07:21 We want to keep this leads list pretty lean to only the ones we're working on. Even one we might want to put somewhere else.
07:29 But the closed loss we'll work on right now and you can always do this again with closed one. Let's create a new sheet called lost.
07:37 And we're going to copy the entire header range and put that there. We can even delete all of the rows if we want, leaving just one row perhaps.
07:49 We're going to go up to extensions app script. We're going to use a built-in function. This built-in function is going to be called onEdit.
08:05 We're going to use e as an event here. This e variable means whenever an action happens that is an edit, meaning the user is editing something in the sheet, we're going to gather some interesting information.
08:17 We can gather the row we're on by doing variable row equals e.range.getRow. e.range.getColumn, with a capital C. We can find out what sheet we're on.
08:34 Equals SpreadsheetApp.getActiveSheet.getName. and see our list that we're looking at is leads.
08:53 So, we're going to start creating an if statement, basically saying, if all of these things are true, if sheetName is equal to leads, then do something.
09:04 But we want to also make sure and row is greater than 1, and with two ampersands, column. Is equal to whichever column we're on the lead list, so C or 3 column.
09:20 We'll use 3 here. We also want to make sure we get the actual value of what we're editing. So, value equals E.value.
09:32 And we want to make sure that this and value is equal. Is equal to closed, lost. Let's make sure that is, oh, there's no hyphen there.
09:48 So, let's make sure that's exactly correct. So, once we edit in column 3, we're editing a row that's underneath the header.
09:58 We're on the leads list, and our value is closed, lost. We're going to do the something here. First thing we're going to do is copy the row we're on.
10:08 So, let's get variable ss equals spreadsheet app dot get active spreadsheet. So, we're going to say ss.getRange, getSheetByName, getRange, we'll start at row we're on, column one, and we'll go for the maximum rows, however many rows there are here.
10:39 So, let's get that as well. Let's call this leads actually, getSheetByName, leads and we can click on this, just leads, and getVariableMaxColumns equals leads.getMaxAmountColumns. So, here Here we need to, say we want one row and max columns. Going to the first column, taking one row, and however many
11:19 columns there are in the sheet, and we're copying to We need a range here. Variable lostRange equals Let's get the variable lost This is lost Lost from a sheet, lost.getRange And we're going to probably want to do one thing. We probably want to insert a row. So let's do that. before two, To column one
12:03 , we're getting the maximum at one row and getting max columns because we'll have the same number of columns on both sheets.
12:13 And we're going to copy to lostRange. So let's see if this works. If we get an error with this, we'll figure it out as we go.
12:21 Let's go to our leads. Let's change this new lead to closedLost. It's William Taylor, and William Taylor is here.
12:33 So that is working. We're copying over the closedLost. Now, we need to delete William Taylor from here. So let's make sure we do that.
12:43 And again, we're only doing it if this value is closedLost. So let's go to leads. leads.getRange, and we're going to the row we're on, starting at column one.
12:57 Actually, we don't even need to do that. We need to delete row. And the row we're deleting is the row we're on.
13:11 And save. Let's double check that this is going to work. Thank you. So I'm going to change this back to contacted.
13:17 And William Taylor, we are going to say, is closed lost. And it's deleted from here. It's disappeared. And we have a second one here, right?
13:31 So let's do another one. Let's go from qualified meal walker to closed lost. It's disappeared from here and it's on our lost right at the top.
13:43 Very cool. So we did that with just this amount of code. We've automated our CRM and archived lost leads. And again, you can do that with any other status.
13:59 You can move basically just changing this value here. And then changing where it's going, which sheet it's going to, maybe it's going to a 1WON and it's closed 1WON. Hope you enjoyed this. Hope you enjoyed automating your CRM. I hope to see you again soon.