The Best Google Sheets CRM | tutorial + template (2024)

yo how's it going guys this video is going to be super dense and actionable all about how to create your own track and sheets to track leads I'll show you guys how to create your own track and Sheet how to connect it with AI and all the necessary formulas for coaches agencies or anyone else who need to track leads and not only that I'll also give you guys some of my personal templates I use the scale past 10K month and I still use to this day to add tens of thousands of dollars to my client's monthly recurrent Revenue so stay tuned okay so just to make sure that we're on the same page here tracking sheets allow us to leverage data and being more specific they allow us to optimize cuz that's the main premise of a tracking sheet you have a tracking sheet in order to collect data and then take that data and optimize and then eventually you will scale okay so that's the main premise uh of leveraging data and I'll show you guys how to do it specifically with tracking sheets which is very helpful cuz what ends up happening you'll have more data you'll implement it back in funnel you'll convert more people more data more data in the F more convert more people and you will keep on optimizing and scaling and that's what we want cuz the TRU is what gets measured gets improved and you probably no this cuz you're watching this video and I'll show you guys um how to leverage that the more tracking sheets you have the more people you have in your business to do optimization and analytics for you or you can do them themselves uh the better your product will be your offer your services and business in general right so in this V video more specifically I'll be going into how to use calculations this going to be for Google Sheets uh there's also Excel I think Excel is paid but I think the same formulas and the same principles apply for Excel but you're better off just using Google Sheets cuz that's what tutorial is for if you want all the sheets I show in this video you can DM me sheet on Instagram it's camel WTA you'll have it in the and yeah that's prettyy much it I'll also have this document for you I'm going to be going over with a script but if you want the exact same script in the doc it will be in the bio for free so if you don't want to DM me you can just uh go ahead and steal this cool okay so just to make sure this video isn't all over the place we're going to have a track and sheet for a new messages will allow you to calculate the conversation and test outbound angles okay uh we're going to have one for crats for people who actually respond and are interested in what you have to offer and this is very crucial you won't lose a single lead ever again okay I'll show you guys how to track your ads and also how to track your closing rate I structured this video in a way that by the end of it you should know how to use a tracking sheet how to create your own tracking sheet and how to actually take my templates and tailor them for your own situation cuz let's be honest your business is probably way different than my one is either positively or negatively that doesn't matter it is different and you will have to tailor your tracking sheets for your own business and this video will cover all of that okay so the first thing you want to do is create a tracking sheet you can do that either by searching it in the search bar Google Sheets or you can go ahead and open your Google Drive and create a Google Sheets from here either way when you create one go to file and make sure that it's moved somewhere where you can find it so click on move and move it somewhere uh where you can find it or copy the link however you prefer okay the second thing you want to do is you want to install um the AI itself so go here go extensions add-ons get add-ons search GPT this should be enough and then download this one okay so this one right here you want to install it to your Chrome as an extension uh because this will be useful for us later on this video okay so you got your rows here and then you got your columns here and how this works is that each cell uh is is combined from a column and a row so this is like B18 this is cell c18 and then of the basic the most basic formulas that we'll be using is um adding so equals each formula has to start with equal sign and you're going to add this plus this and click on enter right and this should add up and then another one we're going to be using out of the basic ones obviously uh is this and then we're going to do divided by this okay and this will allow us to track the stats and then the percentage cuz all we do is we go to format then we go numbers and then we go to percent boom and then we got like 66% that will allow us to track our closing rate our open rate or whatever it is okay or whatever you need Okay so let's get to our first tracking sheet the first track and sheet is for new messages and this may not be useful for coaches okay this is more for agencies who are doing outbound okay but even if you don't need this type of tracking sheet I recommend stick around cuz I'm going to go over some features uh which may be very useful for you so um either way let me get right into it right so how this sheet works is you have they contacted right so let's say you contact someone on the 15th you use the account okay you place in their profile URL okay and then you choose the angle so the script that you used and then what you do is you choose the date. contacted okay then you're going to want follow up two days later you're going to do the next follow-up and then you're going to do the third follow-up and so on and so on until you get to the last followup up um in your list okay so how do we create something like this and ultimately how do we operate in this cuz even if you copy my template if it breaks if you change some them you won't know how to fix this so uh how this all this works how to create something like this so the first thing would be to type out all those stuff already there right here okay and the first thing is cool right here what we have is the date that pops up the main thing when it comes to the date popup is data validation so if you choose the column that you want to added in so you can click on a here and you type in data validation you can add a rule and this should choose your column and the thing is you don't want to choose the first cell right you want to change here to A2 okay so this actually selects from the second one and what we want to do in criteria is we want to choose is valid date and we want to choose reject input and then we want accept done and what this will Le is you can literally put in the date however you like where you want okay the next thing you can do um I recommend do it in the data validation section cuz this helps so go ahead and choose um your column and it's going to be for the toggle list so how this works if you choose your tracking sheet and you click on here and you have an account and how would actually create something like this is also data validation so choose your column go to data validation it should be something like this add a rule but this time you want to click on drop down it should be a normal and again you want to choose from B2 okay and you can add in your um accounts so account one boom boom boom change color boom boom account one add another option account two boom boom change color you can change how this looks so you can go to advance options and go to Arrow clip plain text however you like it uh it doesn't really matter like this right so you can ultimately choose the the account that you want to do right same thing for D and for the angle that you use okay so that's pretty simple just for placing the data how to actually create something like this right so you can actually track each day when you do the outbound so in order to get a similar result like this first thing you have to do is you actually have to uh do the same thing thata validation and you want to add Rule and you want to add rule for this specific cell right so the one right here okay and this should be chosen optionally and you want to go to is valid date reject output input and done okay uh you want to choose the date that it is today is the 770th time of recording this video and you can change the color why not let's just do it it's kind of gray and in order didn't change okay interesting uh but in order of actually get the whole timeline right up here we want to go like this right so we're going to scroll it to the very back and I should change the date um and it did okay if you need more you can right click insert column right and you can insert more columns if you need to okay and in order to put in the follow-ups uh you want to drag it like this optionally H click on ADD Rule and you want to tailor this rule to E2 so from here but you have to check which is your last column in my case is AF so you're going to type in af and I think I have 1,000 rows here so it's like this if I click enter yeah it does and what you're going to do is choose option so I'm going to choose follow-up one follow-up two okay or how many follow-ups you want to okay and if you go to advanc options you can actually change this to Arrow Arrow which I recommend doing for this option and this is pretty much how you do with this right so does it work yep it does work cool so we got that out of the way now how are we actually leverage some calculations to actually get some stuff automated for us so in order for us to track different kind of angles uh we have one formula so as you can see which counts all the messages sent in this tracking sheet and what also it does it divides the messages sent by the CRM ads so we actually have a CRM ad percentage the formula that allows us to do this is this formula you can copy it it will also be in this Doc and I have it scripted in Des script of this video and what this does it counts all the messages sent in a given angle so if I type in value angle one it should pop up value angle one as you can see and what you can do and this is super useful cuz you can count how many messages you send for a given angle and if you actually do uh track the crat so if you let's say we got one crat it does calculate this C percentage right and how this formula Works initially goes from column D and it searches for Value angle one right as simple as that and then this has the divide by um formula as you can see and divides two cells so that's pretty simple and this is the simplest way to actually track your outbound so you can go ahead and build this out or seal my template however you like uh super simple so yeah if you want to leverage AI to actually help you um I should have mentioned this earlier but if you go to AI it should be uh right here okay so you can open it up whenever you want okay and you can click on generate formula and you can say count if in column e appears the phrase yes for example right or you can type in whatever form you need um in this um box right here and it should generate UF formula I'll show you guys a tip because this doesn't always work as as far as this video is recording and the reason for is because there's a mark light here it should be something like this right and then if this highlights and that means that it's working and you can uh copy and paste this so you can also lared bot if you need some help as you go but just to make sure that um everything is highlighted so again you might get a script like this but this won't work because uh there's this Mark here and Dr to be this Mark right here okay so uh just to make sure okay so here you have it that's how you do your whole outbound tracking sheet as far as the CRM so this is the probably the most important part for you if you're an agency owner if you're a coach especially if you're a coach so what you want to do is you want to have a track and sheet in which you can actually track your current leads in order for not to not to lose them okay so how does this work so first you got your account if you have multiple accounts and this may help okay then you use your angle this is mainly for outbound but if you look at my inbound CRM also have one for inbound uh you choose the Ang the guy Ed to come in and B to you so whether they follow they like their comment they message they came from ad or they like your story or they came from YouTube okay uh this is why I also have an inbound one right but for outbound you choose your angle you want to type in the name either way so let's say camel I'm not sure why I spelled it like this and for profile URL you want to paste it here and just to make sure that the URL doesn't spread all over the whole uh sheet you want to click on right click uh I mean you want to go to format you want to click on wrapping and make sure that it's clip right so it's not overflowing it's clipping right so that's what you want so in order and you can actually paste the whole link and it will clip in the cell right day added you already know how to do a DAT in my previous steps last message you want to choose your last messages this also helps for contact so let's just say um convo we had a convo perfect and then what you want to do is you want to click on follow-ups and this helps uh wi your next message so let's say you have the convo uh with someone and then you want to make sure that you don't miss out on him but you don't want to be too needy and you don't want to forget him you just can say today's the 17th as the time of recording this video so let's just say I want to follow up with this lead uh next week right just to make sure so I'm not losing the lead and he doesn't get lost in my DMs or I don't ban him by accident uh you can have a whole list of your whole leads and you can schedule when you want to follow up them right cuz if you're a business coach you don't want to lose leads and in order for you to not lose leads you want to stay on top of your project mind right so uh whether that's going to be a follow-up if they're not responding whether that's going to be some more value so let's say in two weeks I'm going to send them my training in three weeks I'm going to send my scripts right in order to actually convert as much people from your lease as you have right and then status uh this helps and this is also for um my automations right here okay and I have it right here if you want to check the convos you have to choose convo or report right so if I choose convo uh it will count in my convos right here okay if I choose report it will also count in my convos right here so in order we can actually track um the convos also it is selected for C send booked call Post call lead right so we can actually track the colvo it doesn't track when you click on ghost not interested or lead okay so as you can see if I choose lead um there's no convo started and this will actually allow us to calculate the convos if we need to okay right I also have ghost uh but ghost I only use only if the person just responded on the first messages and they don't respond anymore right I'll get back to that in a second but also for the date right if you notice if I choose a date like the 2 13 it actually turns red so you know that you're not losing any lead right and if you actually want to do something like this you want to click on format uh conditional formatting and you want to have these two stuff selected right so if you don't have them selected make sure that you click on add another rule make sure you choose day is and select today okay and this will allow us to actually select the DAT if I choose to dat it will go green right as you can see and the second one you need to have in order to have the same result as I do uh go format conditional formatting and go to this red thing right and what you want to do is click on C custom formulist and paste in this stuff here uh I'll have it in my sheet somewhere if I open it up real quick yeah here you have it uh paste this in there and you'll actually have the red um thing here okay so it actually does uh turn up red which is super helpful so in order to actually track the stats which you want to do I do have a guide here so uh you can reach through this if you want but initially what you want to do is you want to have the first sell for all the leads so every person that you add in again in the CRM you add people who responded okay or you have some contact with and our potential clients right so if you're doing outbound you're going to want add into the CRM everyone who responded to your message same with ads everyone who interact you're at if you're doing DMS right so uh the first cell all the leads I have a count all formula right here so uh click on count all boom and it should count all everyone in the first cell uh this helps and this actually does count the lead so if I uh click on some random numbers like this boom why not it should count three leads and it did okay uh if something doesn't work it may be because you do have multiple sheets here okay and in order for this uh to fix if this doesn't work for some reason which should work but if it doesn't work uh you want go to want tap paste in let me see some like this right so uh what you're want going to do is you want to go right here and you want to click on this thing right here and you want to choose the page that it's on this is on outbound CRM if you look down here so if I click on outbound boom boom boom CRM and if I click that dot again and then I have to do a exclamation work boom it should have the same effect does it yeah it does right you don't have to add in all of the stuff here but you can if some breaks but initially it shouldn't break and if it doesn't break just keep it like this okay uh this is just a thing so actually doesn't work for you okay and in for the convos what I have here I selected everyone I want to have a convo with so like I said uh the convo counts for everyone who clicked on convo report build can send booked call Post call offer right so in order for us to actually have them all in one sell what I did is if I copy one of them I had um I made a formula where it's like this so it's a normal count if formula so count if um H2 to h900 H is this one so in this lead uh there is the word cono okay pretty simple I explained this early on uh but you don't actually have to have the name of this Ur here right this is an additional part uh so it should be like this right this should work the same way if I um click on enter and if I choose convo it should click on one right and what I did to actually track all the convos you want to copy the whole thing boom control file instead of the equals Mark you want to give a plus and you're going to want to choose the word that you want to count so I could just say uh leads right if I wanted to and what this will do is if you click on leads it will also sum up the leads okay my bad it's not leads it's lead make sure it's the same uh as the value that you paste it as you see too and I did this for like I said everyone from here and then it doesn't count uh the people like uh ghost not interested or lead okay I don't count them because that's just status and helps okay and this is this may be very complicated right but that's a thing that you can do to actually track the convos and you can actually uh have a percentage right here okay so for the calls I have the same thing as I said right now before but the only difference is I only have post call lead or you can also have reoffer and closed okay you can also have a close section right so if you click on post call lead um it should be right here okay and the last Treck actually calculate the percentage and the performance of your whole CRM management okay which is super crucial you can actually see how you're going and you can text it with other stuff and people for the least to convos I divided the convos by leads for the calls convo to call divided by the calls by convos uh so it's a simple equals divided by Mark for the leads to call I divide the call by leads that gives us the percentage that we want okay simple mat if you uh paid attention in class and primary school or high school then you probably know how to do this okay so that's pretty much for the outbound CRM it's pretty simple um you also have some formals that I use you can do it inbound CRM based off of that so if you go to angle used you the only difference will be you track something like this okay and you can track the same thing um as you go I recommend have an outbound and inbound if you don't have an outbound you can just go ahead and have one you can just go and delete this one if you like okay if you don't need any of these columns you can also go ahead and delete them um As You Wish okay I also have an ny for you and it's more of a bot and this is more to show that you can actually play around with all this stuff so another way to uh track your performance I use this on outbound spot scraper so initially how this works is you post in paste in the date uh DM send so let's say 34 DMS let's say we got five responses let's say we got three CRM ads so three people um actually want to see the video or whatever you have in your outbound and then you put a name you can optionally type in you the different follow-ups or you can do it from outbound or you don't have you can delete this if you want and then you choose the angle that you choose and what this does so how does this work you have a formula sum if FF right has angle one so what it was to this point and you want to sum actually the BB right cuz that's where we have the numbers and that's how I have this also in my doc you can check it out I do have it somewhere in my script right right here and you want to paste in this if you actually want to sum all those stuff right here and what this allows us to do is I have a big CRM tracking thing here right and what this does is you can track the messages as you can see have a sum of messages for each angle we have the responses because it's the same thing here but the only difference is that we count uh the CC um column this was the BB where I have all the messages sent then we count all the responses and we do count all the CRM ads the same thing here and we do have another section calls and closed this supposed to mean close and if you go to cuse booked uh we do have same thing for Gigi and it just sums everything up same thing with closes okay and then we have some calculations where you don't have to you don't have to paste anything here this all for itself so as you can see we have um reply rate we have 9% we got the CRM mod uh we have 5% okay message to call 1% response to call 12% CRM add 20% and there is a bunch of stuff you can track you can even track your closing rate right here uh but initially uh you have if you have multiple angles uh this will help you and like I said this is for bot you can also do something similar for outbound if you don't want to use something like this uh this will help you split this variations or you can take this and you can literally paste this into your outbound CRM like I said your business is definitely different from mine and you have different needs for your business you have to track different stuff but initially uh this is the main thing you can paste this wherever you want how you want it's all in the uh formulas that you have okay same thing I do have EOD end of day report section right here this is something you can play around with also this is for my Setter uh my VA scraper but either way you can do something like this you have the dates right we choose the account you can type in the messages manually however you like and then you do have like um CR let's say two crads and this does divide for you other than any notes and this can help for accounts either accounts you have it actually track the end of the day reports okay and you may get this sheet for free if you message me on Instagram which I do highly recommend because you you can actually see the frame formulas I went through in this video and you can actually tailor copy and paste whatever you want you can actually copy and paste this and paste in a different Dragon sheet okay but if you want to tailor uh the dates and this is for everything else well let's say you want a different date all you have to do is actually select the date that you got this sheet so let's say you do select it on Monday and then what you do I mean that's Sunday uh you do just paste in the DAT that you want and you're going to drag this little dot right here um not this dot it should be a different one pop up right here yeah right here uh you just going to drag it and it should update the date for you so you can actually adjust that as you want same thing here I recommend doing the same for yourself if you don't send any messages in a given day you can also delete it however you like uh just saying that you can actually tailor everything as you go so like I said I do have this tracking sheet for you I created this template for you guys uh so it's right to use um you have a tracker sheet you got end of the day outbound inbound bot and then you have some test that I built out going live right but other tracking sheets you can build and these are based off of those principles not in various this is like the foundation what you need to know and I recommend trying all this for yourself you can play around on this but you may want also want ads Google forms normal ads DM ads follower ads whatever it is I'll give you guys the idea I'll start with the high one so follow ads uh this is probably the hard one do we have anything else here yeah but basically we track out performance um you can also conversation from ads if it's DMS I'll show you that in a second and I'll just give you guys a quick walk through right here so when it comes to follower ads what you want to do for follow ads you want to add each month here okay so you want to create each sheet for each month and this is pretty simple so you just the day followers you paste in the followers that you have this will calculate the new followers okay for you you click in the ad spend this will create the cost per lead a CB cash collected Revenue um this will also Calculate cost per call uh names notes whatever you have and this is what you do and you fill out this every month okay and what this do allow us to do is this actually comes back to this page right here where you have all the summary right so if you go to new followers what this does some similar to what I showed you guys previously all it does is sums up each cell and the specific page right so as you can see it's January 23 which is the sheet and it sums up cell 34 cuz if you go to January 23 and you go to cell 34 it's the total amount of followers okay and you can basically structure your whole as CRM however you like here you have a template okay new followers ad spend cper follower whatever it is it's a simple uh dividing okay you can track the conversations from the followers as you can see this goes back to the conversation page so we have a conversation page and you can paste a name profile conversation if you choose agency it will add and you also have all the conversation summed up here and this also uses that okay another thing you can track and I guess it's very beneficial is sales calls I do have itth right here as you can see you can track the sales calls pretty simple you put in the date close Revenue payment type cash collected payment uh name needs followup payment method and you can do payment one uh payment two payment three pay4 and think this could be super beneficial you can track all your payments here this will also sum up your cash collected revenue and you can do this for every month or you can have one simple like this so just summarize everything that you collected in your business which is super beneficial and can work okay another type of ads I can show you guys uh right here is DM ads template okay and how this works is you type in the date okay you can use the same formula um I'll show you guys to choose a date name link okay coners uh conversation type I do have option so that depends on your call to action on your DM ad or however you want to differentiate differentiate it okay and whether they're qualified if it's yes it also um counts in the qualify lead section right here then Coach agency this select for you call booked and this will also calculate um in the call booked as you can see and you can have different uh V variables for different adsets different stuff that you test for your business okay and just will you just type in the amount spend you have it and then everything else calculates itself okay pretty simple and this is just some ideas on how what you can do to actually cheack your ads if you have normal ads where it's Google forms or whatever you can have a simple um DM ads template s similar to this if not you can do a template like this and this is for follower to actually see that what you're doing with your business and if it's performing okay guys so here you have it this this is super valuable I don't think that anyone on the internet has a video like I did today for you guys but initially you can track new messages is super useful CRM ads and this will allow you to not lose a single lead okay and ads and that's obviously track ad performance testing uh whether your money that you're spending on as is actually useful right and then closing rate and whatever else you need to track in your business I structured this video in a way that you should go ahead and create any tracking sheet however you want okay I may put some timelines for you guys so you can go back in this video whever you want and like I said this stock right here from the video it does have action steps that I went through my whole script so you can go ahead and take it it's for free uh in my bio for nothing just go ahead and steal it and I also have this um outbound sheet for you guys that I use for myself to do outbound and for steer ads okay this is super useful just DM me on Instagram I'm not going to give you guys the ad scripts cuz these are from my clients but I structured this video in a way that you should with no problem create your any sheet or whatever sheet you want okay so now you know everything about tracking leads but this video is totally useless if you don't have lead detract okay so if you still have a problem with lead generation you can go ahead and check out this video and it's on your screen right now I went over what we do how we do it to actually scale and then you can use this track and sheet to track the leads that you generate for yourself or for your clients so yeah it's just come and see you soon

Channel: Kamil W

Views: 2,390

Keywords: google sheets, google sheets crm, google sheets tutorial, best google sheets crm template, how to create a crm in google sheets, google sheets tips, crm google sheets template, google sheets crm template, best crm software for small business, crm, google crm, google sheets tutorial for beginners, google sheets email marketing, google sheets template, google sheets video, google sheets tutorials, how to use google sheets, google sheets dashboard, best crm software

Id: 7enrFmshtEk

Length: 26min 16sec (1576 seconds)

Published: Tue Feb 20 2024