How-to use Google Sheets as your CRM (tutorial + template) — for freelancers & agencies

today we're going to walk through how you can use google sheets as your customer relationship management software or crm we'll start out by looking at the dashboard see everything you're going to get at the end when you've set this all up this dashboard gives you a really simple overview of your customers and active projects you can see how much you've booked and build and the number of projects you have for this month your target goals for this month and how you what your earnings compare to that your bookings and buildings broken up by month and by type of project as well as by type of project and month and the sources from which you close all these projects so you have all this nifty information in one centralized place when you're finished setting up your crm in google sheets so let's jump right into it you want to start by defining your sales stages and we've already gone through and done that for you here in the legend but you also have your client ratings which is defining how good a certain client is going to be to continue working with and a short list of deal breakers to help you define the ones that fall within this d or f rating and finally some payment codes to define status of a project and you're going to use all of these over on the customer sheet which is where you track everyone that you work with so every crm is going to start with a prospect sheet and a customer sheet prospect sheet has those people that you've spoken to that may be interested in working with you whereas the customer sheet has those that have agreed to work with you or paid you for some work already so we'll start on the prospect sheet you can see you have a few different prospects on different status and you also have their source when you spoke to them in their email here to copy this over to the customer sheet it's actually completely automated if you use our template you just change this over to close and you hit refresh data update closed and when we come over here to the customer sheet it's going to shortly appear so while that happens so scroll through the customer sheet and see some of the information you have to fill in you're going to be able to fill in the deal category source how much it's worth as well as some other information about the customers such as the country industry and the rating so you can see the customer that i had marked as closes now moved over here we can add some more information about them let's say this is a marketing project that's worth 500 and the customer has already paid a deposit on it half that so here we go we have this customer in here you can just copy this over and now it will show up in your dashboard something that has been just closed so once you've done this for a customer you might want to set up a couple extra things and that's when we get into the integrations which are available again with our google sheets crm template and one of those is the toggle integration so toggle allows you to do time tracking this is a really nifty tool you have we you can see we have some uh placeholder data in here which gets referenced uh in the google sheet itself so to use toggle all you have to do is copy this this over into any section just set the name of the uh of the toggle event that you're going to be tracking and you want to put your start and end date here and that should appear so for example if i were to put the start and end date for this acme 2 project you can see the data from here appears in the crm itself so you can easily track your hourly rate and also how much you've earned with your time in any given period using this crm template so for all this to work you'll need to do some minor configuration to connect your accounts and you only need to do this one time so start out by going to tools script editor and that will bring you here the first thing you're going to want to fill out is the sheet id which you can just grab from the url make sure you don't grab what comes after edit or even the word edit just copy that paste that in here and you'll be all set that's just to make sure that all of the scripts are running on the correct sheet once you've done this if you want to use our gmail auto tag feature you can go into this email status function and what you want to do is just edit this with the name of your filter so let's say this is a filter called a prospect or a label called prospect um you can mark those automatically as lead and let's say you have another label called fire you can automatically mark those as fired so on and so forth so you just update these with and if you have more just copy and paste them so let's say i have another one called closed just copy and paste that like this so you can create as many of these as you want and once you've done so when you click refresh data and update status from gmail labels it will go and check your gmail inbox and automatically set the status of these prospects based on your latest email correspondence with them so that's the way to set up the gmail component of our automatic integrations for the toggle component you just have to copy two things you see we already give you the link here so it's really easy for you to go and grab your information what you're going to want to do is just copy two different values over from toggle the first one is your workspace id which you can see up top again in the url anytime you click on reports or anywhere else really in toggle you're just going to copy and paste that here and then secondly your api key which will show up when you go to your profile information you see it right here you can click to reveal that copy that into here and finally you want to set your agent this just lets toggle know who is using the api and once you've done this you can use the get toggle function which you see here and you really don't have to do any messing with it just copy it into any new place that you have so for instance we have this new project and you'll be all set just make sure you have a start and end value set to use those functions so again this is a one-time setup but once you've done that you're able to use these automatic integrations with toggle in gmail so finally when you are using this crm regularly you're going to want to duplicate your dashboards every few months and every year just so that you have a new one so for instance you can see this example dashboard doesn't have q2 20 21 so let's just set that up real quick you're going to want to duplicate the sheet just like you saw here but next other than just replacing these months you also want to replace all these values and you can do that really simply by following the instructions on the readme basically you're going to want to start by replacing the final date and then replacing the start date in all styles and here's how we're going to do it you start by going to find and replace and you can see in here so the old end date was 2021.1 we want to change that to 0.701 make sure to check also search within formulas and only the sheet you don't want to mess with any of the other sheets and you definitely need it to just check within formulas so we're going to replace all real quick and once we've done that we're going to do the same with the start date and switch that and so i've switched the start date and the end date now we can go over here changes to april may and you can copy that paste that transposed and you're all set you have a fully fresh dashboard for the new quarter now you can do the same thing for the annual dashboard you just want to switch the start and end of the year instead of the quarter and you can see this dashboard has a lot more detail in terms of both the projects and sources but also some geographic data on where your projects are coming from but most notably on here you can set an annual goal for the year which will then get pulled into both the quarterly and the live dashboard and you'll get to see exactly how much you should have closed in business on any given day to date and so this lets you keep track of how far ahead or behind your goals you might be on any given day so as you can see this crm template gives you a ton of actionable data really intuitive dashboards to manage your customers we've personally used this at our own agency to manage over 200 plus clients and 3 000 prospects so this is a really scalable solution and the best part is it's not a subscription or software that you have to pay for you can just use google sheets it's as easy as a spreadsheet to copy your data in or you can use something like zapier or no code tool to automatically pull your data into here and if you'd like to jump start the whole process you can go ahead and download the template from vip graphics and follow along with the tutorial here i hope you found this helpful

Channel: VIP Graphics

Views: 284

Keywords: google sheets, google sheets template, google sheets tutorial, crm, customer relationship management, crm tools, crm software, crm spreadsheet, spreadsheet, spreadsheet template, spreadsheet tutorial, tutorial, template, excel, google sheets video, google sheets crm, how to create a CRM in Google Sheets

Id: NQ6D2OM2Id0

Length: 9min 0sec (540 seconds)

Published: Sun Nov 14 2021