How To Create a CRM in Google Sheets
You can see a blank page and here's our roadmap that we're going to do. We're going to do a really quick CRM. Customer relation manager. Here's Salesforce definition of a CRM. It's a technology to manage your company's relationships. It helps you focus on what is a task at hand. You can use it for sales, customer service, business development, recruiting. And very often I find myself on a project and need to reach out to people. And I start in Google Sheets. And this is not necessarily meant to look good. It is meant to be fast. It is meant to get this data in and work with it and then keep track of what you're doing. And it's really meant for sort of small projects that you might have to How to Create a CRM in google sheets w/ Dashboard Build a CRM in Google Sheets in Under 20 Minutes (No Code Needed) Launching SheetOps
do press outreach, or you might have to do outreach to do a quick sale. Like you have existing customers and you want to send them a sales promotion by email or some. So this is supposed to be pretty quick, pretty dirty, pretty not pretty at all. We're literally just going to work with names, emails that's our, all of our contact information that I have. If you have more information you can add to it. You can just add columns as you wish. We're gonna have a status column. We're gonna have some notes . And, again, that notes is like for you to say whatever you want. The main thing we're gonna keep track of are three dates. We're gonna start date when we initiate our contact with them. The last contact date in which we last had actual contact with them. How to Create a CRM in google sheets w/ Dashboard Build A Business: PR Agency in a Google Sheet Create a PR Agency From Scratch Upgrade Your Outreach LIVE COWORK SESSION
If none, it'll be blank. And a close date, this will be blank unless it we close the deal, if it's a PR we get, or if it's a customer came in and purchased something, or Lots of different ways to close. The three things we're going to do in this video is we're going to create a dropdown menu. So if you have not seen how to do that's pretty simple. And you'll see that right away. And we'll place that on the status column and then we'll actually take that dropdown menu. I have another video called create a Trello board or like a Kanban board. We're going to create that here as well. Do it super quick. And then. From these dates, we are going to create a dashboard where we can see, count the number of closes, how many exist. It'll also have the last action. We'll take this column and sort it so that, The idea is you should be able How to Create a CRM in google sheets w/ Dashboard Organize Your Data! - Build a Kanban Board / Trello Board Simple Task Management Automations
to add names here and emails as you wish and not have to deal with sorting. Absolutely, you can if you want to remain in the CRM. Portion you don't have to create the pipeline. You don't have to create a dashboard if you can sort through that, but sometimes we find ourselves Wanting to add names and then sort it in some other way and I'll show you that so I'm gonna pause the video every once around and so we'll skip in time If I'm doing something that is rather Wrote like I'm gonna start entering names and emails and notes real quick So our headers here, and I will just paste, transpose, so we have our headers , or our columns, and I'm just going to fill in some information here, I'm going to set the status, let's say How to Create a CRM in google sheets w/ Dashboard Messy Sales Pipeline CLEANED UP
there's to email, emailed, responded, or replied, actually let's say in progress. And then closed. So we can even leave this one blank. Actually. We can say emailed in progress and closed pretty simple status, pretty quick status, and maybe there's a few here. We're going to have, add some names. We can add I'm gonna add some emails here and be right back with you. Okay, so all I did was add some names, add some emails, these statuses. Let's say They don't look too good. Let's do this. Let's only put a couple Now, as we do this, we might want to have a dropdown menu here, right? We have emailed in progress and closed, but we don't want to have to copy and paste it. And, a funny thing you can do is if you keep all of these without a How to Create a CRM in google sheets w/ Dashboard Email When Cell Changes
blank row, you can just start typing. You have a little little autofill here. But Let's create a drop down for these. Alright, so what I'm gonna do is add a new sheet, call it drop downs. I'm gonna add our three things. If you have more than this, I have another video about how to use Unique here. You can also go Unique , CRM, up here and actually take from the second row. So we don't do that, just status column and you get the same. So we'll use this email in progress close. And how you do a dropdown is you literally go right click and down to data validation . And you're going to use, you can use a list from the items. If you absolutely 100 percent know, you'll never change them. How to Create a CRM in google sheets w/ Dashboard How To Use a 1-Cell Google Sheet Basic CRM - Add a Powerful Script To Move Row Based on Status Move Row Based on Dropdown
You can go here in progress. Closed, but you can also this Creates this So do we have these you can also use? List from a range if you're like, oh, no, I need to add to this What I would do is I would take this whole range but add a few more so you can always add them you know a two to ten so that gives us nine Options And what's good is that these, it doesn't show the blank one. So these two look exactly the same. Except in order, if I'm like, Oh no closed. I want to put like lost. Like I didn't get the deal. They said no or negated us. You can just go here and say closed, lost. How to Create a CRM in google sheets w/ Dashboard
And now go back to our CRM. And our this one has it already here this one I have to right click Data validation to go here closed lost Save so two different paths that you will have to take to get there and then this one also let me Show you can copy and paste Special a data validation only And now, see they're all there. If I had copied and pasted this one, and changed it to let's say, one, I'd have to do that. Now, I have to copy and paste each one. How to Create a CRM in google sheets w/ Dashboard Copy Paste Data Validation to Other Spreadsheet File in Google Sheets
If I go here, it's not here, but I have to copy and paste it. Whereas, if I wanted to go here, and go here. One. Then I go back to CRM, and all of these have it. So I don't have to do anything. So that gives you two options. If you absolutely know you're going to only use something usually list of items . If you want to make a few changes as we go through this, you can you can do this drop down menu. And we'll add, we'll, we might add some, you can add different drop down menus too. You can have, all types of stuff here. All right. So we've done a dropdown menu and now we're going to build a pipeline and we're going to use it from, we're going to do it from that dropdown menu. We have these let's say, let's go back to our simple one closed. We haven't gotten these yet. How to Create a CRM in google sheets w/ Dashboard Simple Task Management Automations
And we want to create a pipeline. We just want to see visually. What is everyone because this list might be a hundred two hundred three hundred thousand and Right now we can see oh we can even sort this we can Pull this down We can sort this A to Z But sorting it A to Z gives us closed first, so it doesn't necessarily tell us exactly what we want to do, right? We don't know. We want to see in progress first, emailed, and then closed. And one trick I did a while ago is figure out a way to name these alphabetically and also in order, but that is something you don't necessarily want to do. So a pipeline is we're going to show. Let's say these three columns we're going to show emailed in progress and closed. How to Create a CRM in google sheets w/ Dashboard Organize Your Data! - Build a Kanban Board / Trello Board Messy Sales Pipeline CLEANED UP
And now we want to see everyone in there. But give me a little bit of room up here because we're going to do something interesting. Do one more. So we're gonna do something interesting here that I didn't plan for. I want to show you one second. So we want to see all of these. What we're going to do is a filter . Equals filter . We want to, let's close the help, because I'm helping you filter . We want name, comma, what do we want to filter ? We want to filter that CRM, CDC, equals B3. And what that's doing, if you can't, obviously you can't see behind here, but it says, we're going to filter all of the A column, Which are the names, and we're only going to put the ones that match C that are true for B3. How to Create a CRM in google sheets w/ Dashboard Filter a Database in Google Sheets based on Dates, Checkboxes, and Dropdown selections! Automation is not Magic
So we have all these people. And now, what we can do is we can use the dollar sign in front of the A to hold it, meaning as we copy and paste this to the right, we only want this B3 to change. This stuff. We don't want to, so when we copy paste now we see who's in progress and see A and C have stayed the same. And C3 is different. That makes it really easy to copy and paste. And so now we see our pipeline. We see, okay, we have five people here, two people here, but again, we might have 50, 30, a hundred enclosed so what we could do is put a count here equals count now. If you were doing this on a dashboard , which we'll do later, we might want to do a count if. But right now, all we have to do is count all from B4 to the end of B. Make a Quick Pipeline (For Sales and More) Run 100 ChatGPTs at One Time Highlight More than 1 Duplicate
That's a really quick and dirty way of getting the count in each one. You can make that. I'm not going to go through any aesthetics here. We're just going to try to get this done really quick. So now. We can sort , we can look through these, but now we have a count 5 to 10. We can even do a percentage say what percentage are in each one. And that might, that's a really good way of knowing, how good is our pipeline. And so you see here, we need to use the dollar sign , all those, we want to change that. So there you go. 22%. Our progress, 22 percent are closed, really quick way of getting this information, but we really want this information in a dashboard , which we'll do next. How to Create a CRM in google sheets w/ Dashboard Organize Your Data! - Build a Kanban Board / Trello Board Messy Sales Pipeline CLEANED UP
So far we've done a dropdown menu and a pipeline. And I've even shown you a few of the things we're gonna put on the dashboard , but let's put create a dashboard But first I'm gonna take a pause and I'm gonna add in some date information That date information is gonna go here. It's literally like 20 and once you enter a date like this Copy paste you have a selection so it makes it really a really nice picker here to easily change this date once you're starting to work through your customers. You can change the state but start date We won't want to change we want to we know we're emailing everyone on the 5th And then we'll have some last contact data . Now i'm going to fill in this information just real quick. So you don't have to watch me Okay, so we have some dates. How to Create a CRM in google sheets w/ Dashboard Google Sheets Interface Changes Add Pop Up Calendar Date To Cell
You We have everyone starting on the same date or you might have some cohorts like you might have on the 5th and then 100 people on the 6th and then another 100 people on the 7th or week by week. Maybe this is a week later, whatever your choice is. What you're going to do is be basically when you're managing through these people the you're going to want to just keep track of when you last contacted them. And when your close date is why that's important, right? It's because we can calculate how long does it take to close a deal, right? If it's going to take a week to close a deal, then you might want to do some things differently. But yeah. We're going to take some numbers out of here. We're going to look at these numbers in a different way. But right now, we're going to build a little dashboard from this. How to Create a CRM in google sheets w/ Dashboard Messy Sales Pipeline CLEANED UP
And obviously your needs are going to be very different than what I imagine here. So you're more than welcome to me about something if I don't show it. So a dashboard might show, let's do some, Total contacts have that number . We will have a number of closed, closed deals in progress. That means like they got, they were contacted and we were doing, dealing them. So like total contacts. And then I guess we're gonna have, and then maybe some other information like average closed days or days to close. Also going to want to have a count and a percentage. Then we're also, one thing we want to do from the CRM, Is we want to know How to Create a CRM in google sheets w/ Dashboard Messy Sales Pipeline CLEANED UP
who we want to take some action we want to See, okay, who should I who is like the most Interested party and that's probably going to be the people who we should probably take action on last con so contacts who their last contacted is the furthest away so This person is probably the most person the person we should reach First, they are not closed, and they're the least contacted, and we have not replied for them. They are not, all these people who have not replied, for some reason, are not interested, and that's a different tactic we'll use for them. Actually, we want to know, okay, how many have not replied, and who are they, maybe? Maybe we want to contact them some other way. So we'll list those. And then we'll also want to list in reverse chronological order, or actual How to Create a CRM in google sheets w/ Dashboard
chronological order, date for, the first date of those who are not closed. So I'll show you how to do that. So we'll do average days to close. And then we want to take some action. So this is our action. And we're going to say go not reply. We're going to list everyone there. And then we're going to also have outreach. Our follow up needed. And here is going to be different. This is going to be probably name and email and this is name and email. Again, this is really quick. I'm not really working with any aesthetics here. I don't really we just want to get this going. But I am going to make this a little organized here. Let's do center Okay, for total context we're going to use count And we want to count everyone in our crm from a two all the way down That's all Everyone How to Create a CRM in google sheets w/ Dashboard Messy Sales Pipeline CLEANED UP
let's do that closed deals are going to be a filter so we can do this a couple ways we can do equals count if But I like to do some filter . It gives us some more options. We want to filter just want to count. So let's go here. Let's go. This not all right. So in order to count the date, we just want to know. CRM. g is above zero. Because if there's any number here, it'll be above zero. And that's a sum, but we want to actually count all. We want to count all, and we want to make sure this number is going to say three. All. Why that is, is because, let's go back to our CRM, I'm counting the first row. Let's go to two How to Create a CRM in google sheets w/ Dashboard Messy Sales Pipeline CLEANED UP
and two. There we go. If we have the number of closed deals, then we want to know in progress. And that'll be similar, except we want to do CRM. We want last content. Now we're going to have a wrong number , but I'll show you how to fix that in a jiffy. So we go here, that's two. Now we have five. But what is the real number ? The real number is it is, we have five that we've last contacted but we have two that have closed. One quick, very quick way we can do this is take this number and minus the number enclosed. There you go. We have three. Now not replied is going to be similar to We can go up here. We can actually copy this. I want to do minus also in progress How to Create a CRM in google sheets w/ Dashboard Messy Sales Pipeline CLEANED UP
and we want to do different column here. We want to start. So we really want to say, okay, everyone who has started not contacted and not closed here. I'm going to do E2, change this to E, change this to E, and now, is this correct? So in total we have 9 here, let's go back to our CM and just double check. We have 1, 2, 3, 4, wait sorry, we have everyone we started and not contacted and not closed, we have 4. We have 3 here and 2 here, so is that what we have? 4, 3, 2. Perfect. Now percentage wise, we don't need a percentage for total contacts. We do. All we need to do is do 2 divided by 9. We can change that to a percentage. How to Create a CRM in google sheets w/ Dashboard Messy Sales Pipeline CLEANED UP
Actually make that a little, yeah, 22. Now to hold C. Three. We put the dollar sign in front of the three and all we have to do is copy and paste that makes it super can also check our percentages. Let's sum it up here. We can see a summit over here in the bottom, right? 100%. So that's our quick pipeline and dashboard . But we want to, last thing we want to put together is some action, right? We want to know which ones should we reply to, which ones do we need to follow up on, and we want to make it really quick so we don't have to sort anything here, it's automatically sorted it's over, we want to know, okay, everyone we last contacted that has not closed date, and then sort by date, but smaller date. How to Create a CRM in google sheets w/ Dashboard Organize Your Data! - Build a Kanban Board / Trello Board Messy Sales Pipeline CLEANED UP
I'll show you that really quick. Not replied. It's going to be a filter . We're going to filter . CRM. We're going to filter A and B. And what do we want to filter for? We want to know. We want to do by status. We say CRM C to C equals email. And that's everyone that has not replied. And then, we're going to do the same, but for follow up need, it's going to be in progress, right? So let's take this, over here, and do in progress. But we don't know the dates, right? We don't know, we want we know we want to say, okay, this person should be first, right? And in, if I move this around, let's say this is here. It's going to be whatever order it is, but we want to force it. We want to make sure we want to sort it. How to Create a CRM in google sheets w/ Dashboard Quick Follow Up Email
So we go sort , and we want to actually see column C. We want to sort by three, the third index . Let's do, So we want to sort by the third column, but we need three two things. We need to know, True. And is that correct? It's probably not correct, because it is saying it is going from Z to A . So we do need to get this filtered to F, so we know we have the date over here, and we want to sort by the F column, which is 1, 2, 3, 4, 5, 6 index , so it's a six row, six column, and this true and false is saying do we want to go from highest to lowest or lowest to highest. Right now we're going highest to lowest is false, and if we change How To Sort A Filter Figure Out Frequency of Numbers Auto Sort By Priority
it to true, It's the lowest to the highest, and that's what we want. So we want Edith, Frank, Gary in that order, no matter how they are sorted here. And so just to fill this out, we can put the, we can put the extra columns here. And now we have this really quick and dirty dashboard that shows us Who's not replied so we can reply to them. We can just grab these right away. We don't have to go to the CRM and we can see, oh, Edith needs to follow up and because we last contacted her. This has been a very quick CRM and we've done drop down menu, a pipeline, and a dashboard , all with a start date , last contact, and close date. How to Create a CRM in google sheets w/ Dashboard Checkbox or Dropdown?
If you want to know more, feel free to email me and let's get you going. Bye.