How to Create a CRM in google sheets w/ Dashboard

Most Popular Intermediate

About this Tutorial

Build a Customer Relationship Manager right inside a Google Sheet. Track your customers, your sales progress, and your revenue in an easy to create dashboard.

Sheet Resources

Access Sheet

Copy Sheet

Preview Sheet

Video Transcript

<div> Hello, welcome. So this video is gonna be a little bit different. I have nothing, so I have set up nothing here. You can see a blank page. And here's our roadmap that we're gonna do. We're gonna do a really quick crm. CRM typically is customer relation manager. Here's Salesforce. Definition of a CRM is, 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 do press outreach, or you might have to do outreach to do a quick sale. Like you have existing customers and you wanna send them a sales promotion by email or some. Other thing.

So this is supposed to be pretty quick, pretty dirty, pretty not pretty at all. We're literally just gonna mo 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. 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.

If none, it'll be blank in 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, if a customer came in and purchased something or. There are lots of different ways to close. The three things we're gonna do in this video is we're gonna 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 Tri Boarder, like a Con Bond Board.

We're gonna 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 exists. It'll also have the last action. We'll take this column and sort it so that the idea is you should be able 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 or. 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. I'm gonna pause the video every once in a while and so we'll skip in time if if I'm doing something that is rather wrote, like I'm gonna start entering names and emails in those real quick. So our headers here, and I'll just space transposed. So we have our headers for our columns, and I'm just gonna fill in some information here. I'm gonna set the status.

Let's say there's two email emailed, responded, or required 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 gonna add some names. We can add, I'm gonna add some emails here and be right back with. Okay, so all I did was add some names, add some emails. These statuses, let's say they don't want to do this that's only a couple in progress.

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, some, the funny thing you can do is if you keep all of these without a blank row, you can just start. Typing, you have a little little auto fill here, but let's create a dropdown for these, right? So what I'm gonna do, I'm gonna just add a new sheet, call it dropdowns. 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 here and actually take from the second row so we don't get that status column and you get the same. So we'll use this emailed in progress closed. And how you do a dropdown is you literally go right click and you come down to data validation . And you're gonna use, you can use a list from the items if you absolutely 100% know, you'll never change 'em. You can go here in progress closed. But you can also, this creates this, so we have these. You can also use list from a range .

If you were like, oh no, I need to add to this, what I would do is I would. Take this whole range and add a few more. So you can always add 'em. So a two to 10. 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, 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, safe. Two different paths that you will have to take to get there. And then this one also let me show you, you can copy and paste special past data of validation only. And now see they're all there. If I had copied and pasted this one and changed it, so let's say one. I have to do that now. I have to copy and paste each one, right? So 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 whoop 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 gonna 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 dropdown menu and we'll add, we'll, we might add some, you can add different dropdown menus too. You can have all types of stuff here. All right, so we've done a dropdown menu and now we're gonna build a pipeline and we're gonna use it from, we're gonna 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.

And we want to create pipeline. We just wanna see visually what is everyone, because this list might be a hundred, 200, 300,000, 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 wanna 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 gonna show. Let's say these three columns we're gonna show emailed in progress and closed, and now we wanna see everyone in there. But give me a little bit of room up here cause we're gonna do something interesting. Do one more. So we're gonna do something interesting here, but I didn't plan for that. I wanna show you one second. So we wanna see all of these. So what we're gonna do is a filter equals filter . We want to, let's close the help cause I'm helping you. Filter . We want name, comma. What do we want to filter ?

We wanna filter that crm C equals B, and what that's doing. If you can't, obviously you can't see behind here, but it says we're gonna filter all of the A column. Which are the names, and we're only gonna put the ones that match C that are true for b3. 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 the ca a.

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. So 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 at 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 here, but right now all we have to do is count all from B four to the end of B. That's a really quick and dirty way of getting the count in. Each one can make that not gonna go through any aesthetics here. We're just gonna try to get this done really quick. So now, We can sort , we can look through these, but now we have account 5, 2, 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 or hold those. We want to change that. So there you go, 22%. Our progress, 22% are closed. Really quick way of getting this information, but we really want this information in a dashboard , which we'll do next. So far we've done a dropdown menu and a pipeline. And I've even showed you a few of the things we're gonna put on the dashboard , but let's put, create a dashboard , right? First I'm gonna take a pause and I'm gonna add in some date information. That date information's gonna go here.

It's literally like 2020. 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 date, but start date , we won't wanna change. We want to, we know we're emailing everyone on the fifth and then we'll have some last contact date. And I'll, I'm gonna fill in this information just real quick so you don't have to watch me. Okay. So we have some dates. We have everyone starting on the same date.

Or you might have some cohorts like you might have on the fifth and then ever. Then a hundred people on the sixth, and then another a hundred people on the seventh or week by week, maybe this is a week later, whatever your choice is. What you're gonna do is be basically when you're managing through these people the, you are gonna want to just keep track of when you last cl last contacted them and when your close date is. Why that's important, right? Is because we can calculate how long does it take to close a deal, right?

If you know it's gonna take a week to close a deal, then you might wanna do some things differently, but. We're gonna take some numbers out of here. We're gonna look at these numbers in a different way. But right now we're gonna build a little dashboard from this. And obviously your needs are gonna be very different than what I imagine here. So you're more than welcome to ask me about something if I don't show it. So a dashboard might show let's do some. Total contacts, we'll have that number . We will have a number of closed, closed deals in progress.

That means like they got, they were contacted and we're doing, dealing them. So like total contacts. And then I guess we're gonna have We not applied and then would be some other information like average closed days or days to close. Also going, we wanna have account and a percentage. Then we're also. One thing we want to do from the CRM is we want to know who. We wanna take some action. We want to see, okay, who should I, who is like the most interested party?

And that's probably gonna be the people who we should probably take action on last con. So contacts who they're 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 contact. And we have not replied from them. They're not in all these people who have not replied for some reason, are not interested. And that's a different tactic we'll use for them. So actually we want to know, okay, how many have not replied and who are they? Maybe we want to contact them some other way.

So we'll list those. We'll also wanna list in reverse chronological order or actual 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 wanna take some action. So this is our action and we're gonna go not reply, we're gonna list everyone there. And then we're gonna also have Outreach, our follow up needed and here is gonna be different. We're gonna, this is gonna be probably name email, and this is name 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 gonna make this a little organized here. Let's do center. Okay. For total contacts, we're gonna use Count all, and we want to count everyone in our CRM from a two all the way down. That's all everyone. Let's do that. Closed deals are gonna be a filter . Some, we can do this a couple ways. We can do. Goals count if but I like to do some filter . Filter . Gives us some more options. We wanna filter , just wanna count. So let's go here, let's go.

This not all right, please. So in order to account the date, we just want to know crm. GT G is above zero because if there's any number here, it'll be above zero. And that's a sum we want actually count all. So we wanna count all and we wanna make sure this number 's gonna say three. Oops. All why that is, is because, let's go back to our crm. I'm accounting the first. Let's go to two and two. There we go. If we have the number of closed deals, then we wanna know in progress, and that'll be similar except we want to do, we want last contact.

Now we're gonna have a wrong number , but I'll show you how to fix that in. So we go here 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. Can we go up here? We can actually copy this so equals and we wanna do minus also in progress. And we want to do different column here. We want to start.

So we really wanna say, okay, everyone who has started, not contacted and not closed here, do E two. This to E, just E. And now is this correct? So in total, we have nine here. Go back to see and just double check. We have 1, 2, 3, 4. Sorry, we have everyone we started and not contacted and not closed. We have four. We have three here and two 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 two divided by nine. We can change that to a percentage.

Actually make that a little 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 there, and that makes it super cool. We 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 wanna 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 wanna make it really quick so we don't have to sort anything here. It's automatically sorted. It's over. We wanna know, okay, everyone we last contacted that has not closed date and then sort by date, but smaller date. So I'll show you that really quick. So not replied, it can be a filter . We're gonna filter CRM, filter A and B. And what do we want to filter for? We want to know. One, two, do by status.

So we say crm, C to C equals. And that's everyone that has not replied. And then we're gonna do the same, but for follow up need is gonna 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 gonna be whatever order it is. But we wanna force it. We wanna make sure, we wanna sort it. So we go sort and we want to actually see column C.

So 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 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 . We go false. So we do need to get this filter to f 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, 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 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 dropdown menu, a pipeline, and a dashboard , all with start date , last contact, and close date. If you wanna know more, feel free to email me and let's get you going. Bye.</div>