How to Create an Automated Client Tracking Spreadsheet (using Google Forms, Google Sheets, & Zapier)

in today's video we're going to be talking about how to set up a simple and easy automated client tracking system within your business using Google Forms and Google Spreadsheets and free tool that I love called zapier it's a three step process and it's really simple and easy but it can really help to streamline and make your business more efficient and more organized if we have not met before I am Stephanie Rona an online business coach and brand strategist helping you show up online with the confidence the clarity and the right strategies needed for a lasting success and true freedom in your business if you have not already subscribed to my channel make sure that you like this video and then subscribe to my channel and hit the bell notifications because I upload new videos every week to help you succeed in your business online so we are gonna go ahead and dive in I'm gonna switch over to my computer and I'm gonna walk you through these three steps to get this automated and easy system set up okay so first thing you want to do is log into your Google Drive I am in my clients folder and what we're gonna do in here first is create a Google Form and for this demonstration we're going to set the system up for existing clients but set this entire thing up for potential new clients as well so this form is going to be any time you enroll a new client and you send them a welcome email I can send them a link to this form and have them fill this out as well oh I did do a video on how to enroll a new client and watch through that entire process I will link to that video in the description to this video so you can go ahead and watch that too but this is the form you would send to them in your welcome email once you've enrolled that new client so we're gonna do inside this client folders come here to new and say more in Google Forms if you haven't used Google Forms they are extremely easy and really useful in your business okay so client enrollment questionnaire and then you want to click up here it'll pull that name up to the name of the form up there as well form description your clients are gonna see this so you want to enter something here something simple like please complete this form before a first session is fine and now we want to add our questions so this is anything you want to ask your clients I'm gonna keep it really simple for this form but you want to think about what you want to know from your clients before that first session so if you're a health coach you know what important information do you need to know or what do you want them to start thinking about before your first session anything like that so for the first question what you want to make sure you do for the way I'm having to set this up is to ask for their first and their last name together in one question and this is gonna be important in one of the steps later on but we don't want to do is separate their first and their last name you'll see why and a little bit you'll also notice that Google Forms kind of auto-detects what kind of question this is and what kind of answer it needs to be you know if you want to change the type of question we have a drop-down menu here and you can pick which type you want it to be but short answer is perfect for their name we want to make this required you can add a description things like that and then you can also just duplicate this if your next question is similar or you can come here and click this plus sign and say add a new question and you have other menu options over here you can add images and videos and things like that so you can really you know make this an elaborate form or keep it simple I'm gonna go ahead and duplicate this question and we are going to ask for some basic information here this one we are going to make a multiple-choice question some things I like to ask my clients are just like what they already have in place in their business so I know where we're starting is it from the ground up or have they already done a few things so this really helps me to know our jumping-off point when we get started this is just a couple of things that you know you might want to know if you are a business coaches well or just what other things but you want to know from your clients and then one question that is pretty simple that you can ask is just what are your expectations for our time together so that you guys can be really clear and on the same page even though you may have talked about it in a Discovery call or free consultation it may be laid out in the terms of your service agreement it's always good to hear it in your clients words too so I just suggest always throwing in something like that like what are your expectations what are your big goals you may want to ask more things like that like you know what how much time do you have each week to dedicate to the what are your fears and your concerns like you can really go deep you can keep it simple whatever you want is fine and then once you have all of your questions in place you're going to come over here to responses hover over this little green icon and say create spreadsheet we're gonna create a new spreadsheet now this auto populates this name of the spreadsheet to be the same name as the form but what I suggest is calling this something like master client list general Client List complete client list I like the master client list we're gonna say create okay so our form is set up and now our spreadsheet is set up as well so anytime somebody fills out that Google Form their responses are gonna automatically come in to this master client list spreadsheet which is great and you can see here all of the column headers have already been pre-populated as well and I'm just gonna select on the ones that are kind of long and I'm gonna double click to make it show the entire column header you can see down here at the bottom that the tab here is called forum responses one that's automatically populated from your Google Form but we can rename this we're gonna call it all clients because what we're also gonna do is create a tab along the bottom so that every one of your clients or potential clients has a their own worksheet within this spreadsheet you want to have one for all clients and then each individual client will have one that is their name oh we're gonna go back into our Google Drive and if i refresh the screen you will see our form and our spreadsheet pop up there they are for step two we're gonna actually use something called zapier if you haven't heard of them it's a really great tool that connects different programs and automates different processes within your business we're gonna go ahead over to zapier and you can get started with that free account and that's all you need for this process so sign up for a free account and then once you do that you're going to click on this make a zap orange button and we're gonna choose the event that zapier is going to look for and then we're gonna tell it what to do when that event occurs we're gonna say anytime a Google form is filled out and a new entry is created in that spreadsheet we just set up we want it to create that new client tab for their their own worksheet at the bottom event is a Google Form event that we want it to look for the trigger event is a new response in that spreadsheet we're gonna continue it's going to connect our Google account and it's gonna ask you to do this every time the spreadsheet or looking for is our master client list right and the worksheet is the all clients worksheet because that's where all those Form entries those form responses are going so we're gonna say continue and one thing I should point out is it's going to look for data in that spreadsheet and we don't have any data in there so in order for this to work we've got to go in and either fill out our own form or enter some fake data into our spreadsheet either way works it's up to you let's fill out the form you can see how this works back into my Google Form and I'm gonna come here to this little eyeball and say preview and we're just gonna go ahead and fill out some fake information and once you have all of your fake answers filled out you can go ahead and hit submit okay arson response has been recorded we can close out of this and you can go and check this out in the spreadsheet okay so in the spreadsheet now you can see first and last name address checking all that apply what are your expectations so all the data is in here now we can go back to zapier and let it look for this data we can say test and review that pulled in our response you can click on it and you can see all of that information that we just submitted was pulled over into zapier so now i can see that there is data in that spreadsheet and we can say we are done editing that to now is to create that new worksheet for that client so we want to now choose sheets the action event is to create a worksheet so we've got our main spreadsheet and then the tabs at the bottom are the individual worksheets within the bigger spreadsheet and that's what we're creating is a worksheet we're gonna pick our account again we're not gonna pick our drive we're just gonna come down here to spreadsheet and pick that master client list again alright so the title of the worksheet we're gonna actually come over here to the right and click on this insert a field plus sign and we can come to these responses in the spreadsheet and pull data from the responses so I'm gonna pick this first and last name response and that's now going to be the name of our worksheet tab at the bottom and now we get to enter our headers these are our column headers so it's gonna automatically set this up for you for every client so since this is a you know a newly enrolled client that we want to track in here these are some headers that you might want to set up for your new clients so name we can again pull over their name again okay so this is some example column headers that you might want to have there their name again and then what type of package or program did they sign up for with what's the start date and the end date the payment information for that particular client and then any time you have a session with them this in the session notes so let's say this client resigns with you you already have like a place to come and update their package type and payment and session information because you'll just be able to keep adding to this column that is all set up and we can say continue right we're gonna go ahead and test and review okay so it says we've created a worksheet so that this was successful so we can go and check our spreadsheet and see what this looks like okay so here we are and you can see we have a tab now at the bottom with my first name in it if we click on that tab here are all of our column headers that we chose for this so for example let's say this is a three month one on one so then their next session you can come in and let's say it's every week and so let's say they resign with you you know they go through all 12 sessions or whatever it is with you in this package you can then come down here and say you know we signed one on one three months so now you've got one place where you can keep track of all the information for that particular client in your business now the third step is just something that I like to do you may not need to do it at the beginning in your business but as you get more and more clients this next step can really help you to stay more organized so what we're gonna do is go back to our all clients tab and on the first and last name part we're gonna select that cell and we're gonna right-click and we're going to say insert link we are going to link to a sheet in this spreadsheet so we're gonna find that matching client name and we're gonna link that together and apply now this is really just one little step that can you know really make you more efficient in your business so whoever whatever client session you just had or that you're in you can click on that name now click on this link and I'll take you directly to that clients worksheet within this spreadsheet so it's really simple I like this system there's a lot of systems out there there's Trello and asana and things like that but they can get really complicated and I just like this it's simple and it's easy I like that everything is in Google Drive within your business so that you're not always checking into different platforms for everything you need in your business okay you can always format your spreadsheet one thing I like to do is come here and just you know add some grid lines to make this easier to read and then oops make each column wider and you'd have to do this once every time for every new client manually but that's okay we want to resize the columns and if it's at 1/16 let's just make it a little bit bigger at like 200 perfect and then you can come in and make your rows bigger as well just select those and then pick one and right click on it and we're gonna resize the rows we don't want it to fit to data we'll make it actually let me get triple about triple what it was okay so now it's easier to see and use the other thing you know if your notes get long as you'll want to come in here and then just like choose this text wrapping and wrap your text you can actually do that for the whole column okay so some gridlines some formatting of the cells and wrapping of the text and now you've got a really functioning simple and easy to use client tracking system in your business and like I said you can use this for existing clients so you can stay on top of their packages and their notes and things like that but it's also great for new potential clients so anytime somebody books a call with you you can set up a zap depending upon your calendar system calendly acuity zapier will work with that and Google sheets to automatically create a spreadsheet just like this so that you can keep track of you know when they had their call what the follow-up was was any notes from that call things like that so this works great for so many different things in your business I hope you found this really really helpful please like this video if you and leave a comment below let me know what you thought about this um did you find this easy and helpful and useful for your business and make sure you subscribe if you haven't already so you don't miss any of my new videos thank you for watching and I'll see you soon [Music]

Channel: Stephanie Wrona

Views: 19,306

Keywords: google forms, google sheets, customer database google docs, how to create a customer database, easy customer database, how to track customers, create customer database, simple customer database, customer database for small businesses, free customer database, google forms customer database, google sheets customer tracker, client tracking spreadsheet, how to track clients with a spreadsheet, how to keep track of clients, zapier google sheets, zapier tutorial, zapier

Id: 5F99SuB_q-g

Length: 14min 40sec (880 seconds)

Published: Thu Jan 30 2020