How to Create High-ROI Dashboards You Can Charge For Using Make.com

what's going on everyone it's Nick and in this video I'm going to show you how to make an extremely simple but very high Roi dashboard for finances in make. com Google Sheets and looker Studio this is something that makes a massive difference to the companies that I work with I usually sell it from anywhere from 3,000 maybe up to $110,000 or so assuming if the company has a lot of money to spend and this is really important to them uh and in this video I'm going to walk through everything they from start to finish it's a real live client build for a project that I'm doing and if that sounds like something you want to learn how to do yourself stay tuned and let's get into it all right so for an example what this dashboard is going to look like just skip over to the end of the video I'm going to run through that in detail uh but for those of you guys that just want to dive right into it the way that this is going to work is I'm going to break this video into three parts the first part is going to be us going over what an example database or Google sheet structure might look like for this the second part is going to be us building out the automations to get the data into that sheet so the finance data is usually entered due to some transformation whether it's like a striped transaction or maybe a padoc proposal signed or something like that uh because I'm respecting my client privacy here I've set up a bunch of example type forms and I'm just going to be using these type forms as our data input but you can imagine how you could simply hook this up to stripe or pandadoc or anything else as the trigger all you would have to do is just replace the field so in question and then the third part is going to be us actually building out the dashboard in looker Studio is the continuation of Google data studio if you guys are familiar with that uh from a couple years ago and basically it just allows you to use Google products and services like YouTube data or Google analytics data to design pretty simple and pretty beautiful dashboards in my humble opinion this is an example of one using Google analytics 4 so you can see in this case it's doing active users new users uh Trends you can plot things as sort of a graph or a chart and you also have one of my favorite features which is the ability to change things based off time and the real strength in building a dashboard on this platform is just that you basically have access to real-time data at any point you know you no longer have to call up your Finance team or your finance department or try and manually compile a report like you can do all of that yourself just by moving the date range so very powerful for companies and one of the reasons why they're typically you know happy to pay a large amount of money to make sure this problem is solved also just think about like the ROI on having Total Access to your finances at any point in time it's crazy so that's what we're going to cover today um I've set up our Google sheet structure and then I've scaffolded out a couple the make scenarios just to make sure this video isn't 5 hours long uh but in reality these sorts of builds usually take me I want to say probably about three hours of actual like Dev time uh unless there's a major bug or unless I misunderstood sort of how the client wants to track things that's usually what I'm looking at but okay first things first let's cover the way that the um actual data base is going to look now database is a fancy and loaded term uh most of the time when you're doing coaching agency or info product businesses your database is just the Google sheet that you have set up it's about as close to a database as you can get anyway obviously if you're a developer you're going to be raging at me right now because Google Sheets isn't a database but you know it has rows has freaking columns good enough for me so uh what we're going to be collecting specifically is I want to get the total number of meetings booked like this is a sales company they do coaching right uh so I want to get the total number of like closing meetings that are booked because I think that's an important metric then I want to get the number of proposals that are sent from those meetings now these proposals proposal is sort of a loaded term if you watch the re my recent videos you're probably think my the proposal is like that beautiful seven-page document that I add generate these proposals are much simpler they're basically just like a one-pager form and then they just you know have payments uh attached to them if somebody clicks yes then they just pay their thing with a credit card and then it's done but just keep in mind it's like this is a one call close where there's a big sales meeting and then a proportion of those are going to go through a proposal transformation and actually like pay the money so we're going to track the number of meetings booked then proposals sent cuz presumably these two are good pieces of information to have and at the end we're going to track the number of deals that were closed as a result of those proposals sent and when the deal is closed there are two things that are that we're also going to be collecting one is going to be called contracted Revenue which is the amount of money that the client basically promises to pay it's the total amount at the bottom of the proposal or in our case like we're calling this proposal it's really a contract there's a signature and everything um this is the amount of money that the client is obligated to pay they've been contracted now to pay this money at some point between now and the end of time but I want you guys to know that that's a big difference between the amount of money that was actually collected and that term is called Cash collected um this is something I see a lot of novice entrepreneurs trip up on by the way the difference between revenue and cash collected and this is why you know a lot of guys in the Ecom space or coaching space are like I made $45 million in Revenue in two years and blah blah you're like damn man that guy's crazy well he's probably talking about the contracted Revenue not the actual cash collected let alone the

actual money that he keeps in his pocket after expenses um so yeah contractor revenue is the amount basically like the total on the bottom of the contract the client signed saying they will pay you this at some point in time and the cash collected is how much money actually hits your bank account today when that deal goes through very important difference to understand there um you really don't need to know any more about Finance in order to make like a good highquality dashboard that people like so don't worry too much about that but yeah the way that this is going to work I just added some example data because I think this is a little bit trickier to get uh when the transaction happens obviously there will be contracted Revenue because that's like when the first proposal is signed that's when you're Contracting that Revenue right and if you're on a payment plan or something like maybe you only collect 5,000 today and then you collect 5,000 in like a month or something um you know revenue and cash collector are going to be different obviously because you're on a payment plan right so the first transaction with contracted revenue is going to be uh basically like the deal that was closed and then any other rows for that specific person which I'm just tracking with a product ID which is hypothetical um I'm going to set the contracted Revenue to zero and then the cash collected will be some other you know number whatever the remaining balances they're that they're collecting on and so in this way uh we can count up the number of deals that were actually closed by just looking at any um any rows that have their contracted Revenue column set to above zero and then I can look for cash collected just by summing up the uh you know the cash collected column with dates and stuff like that so that's how I'm setting this up there are obviously millions of different ways to do so but this is a pretty reliable way that's what I personally use uh it's much faster I want to say and easily interpretable for the client and for me I use these sech of dashboards in my own business so yeah that's how we're going to be setting this up now uh meetings booked we're going to have a date added column an email of the client or Prospect I should say at this point whose meeting We are booking and then we're going to have a date booked um and we can select way more information here if we want but these are just the fields that I'm going to keep maybe I'll add some more I don't know for proposal sent we're just going to be including the date added uh then the description and then the customer email so probably shouldn't say customer email at this point um let's just say email and then for deals closed we're going to be collecting the date added the description just because uh we're going to presume that the platform that we're using to track this stuff has some type of like title or name column or something uh contracted Revenue column obviously we're going to add that up cash collected customer email because they are a customer at this point so I can actually say that and then we'll have like a product ID and product ID here for our purposes is just going to be a placeholder since I'm just going to be putting that data in using type form uh but you know the specific way that the client is tracking is they have uh this big clickfunnel sort of flow and then when somebody buys something and clickfunnels it heads over to their payment processor for a specific product and this is important for us to track because we want to oops um attribute you know if like the client has several payment plans that are specific to a product and so if there are two payments that relate to the same product ID for instance uh well then we know that that's going to be like payment number one payment number two uh so this whole thing is totally dependent on how your uh client uh sets up their payments and sort of what you're working with but that's how they set up theirs that's what we're going to be doing okay great so yeah that's that um let's actually go in and build it now this is the Google sheet structure everything here is good let's actually jump over into make and then put things together so first thing we're going to do is I'm actually going to use a cal. com example with my own booking link then every time a new calendar event gets booked in that calendar I'm going to be adding a new uh row here to this uh to this meetings book sheet and so I've set a scaffold up here just called client X meeting booked and what I'll do is I'm going to be awaiting a web hook because I know that cal. com my calendar platform uses web hooks but if you're using calendly or something there's a little calendly built-in over here uh where you can just watch events as they come in but I'm using cal. com so I'm using a web hook going to call that custom web hook the web hook name uh we're just going to do new let's just do new example booking March 9th 2024 going to copy that address then I'm going to head over to cal. com jump into my account I have so many different event types here I'm just going to use freelancer demo call this is for an offer that I ran quite a while ago where I was teaching people how to get to $30,000 a month free laning uh worked reasonably well I would say but I didn't end up scaling it into a product or anything I'm going to delete this old web hook just cuz that's not necessary and I'm going to create a new one so um I'm basically just pasting in the make. com server destination which is this link right here that they provide for you into the web hook destination field and cal. com doeses this by letting you choose which event triggers you want like maybe we only want to send this out when the booking is created or meeting ends or something like that we

have a lot of options here uh but because I'm only curious about when a new meeting is booked the only event that I'm Cur that I really want to track is when a booking is created so I've just deleted the rest of these I'm setting everything else to default here I'm not setting a secret I'm not setting um a payload template because I just want it to be whatever cal. com default is and then we can also just ping a web hook before continuing the reason for that is because we just want to establish the data structure so I'm just going to go ping test I'll go back here and you'll see now it's a successfully determined that's because it just received the event that was sent but now I'm actually going to go and run it because I want to see what the data is and you'll see that we just got that data because this's this little Cloud icon that popped up and now we actually already have access to a few fields that we're going to need like start time end time um attendees organizer actually I think that's enough for us to do everything right uh yeah I think that's actually enough for us to do everything so I'm not even going to like actually run this on real data I'll just uh go ahead and start adding rows the way that I'm going to do that is I'm going to go over to the Google Sheets module uh my bad I can't actually select that because I've already added it to my favorites list here so I'm just going to click on that and then I'm going to go to add a row and let me just check which account this is on I should have access to this I'm not entirely sure so it's probably un shared with me uh wherever your Google sheet is um just make sure that if you connect to the account make sure that that you created the Google sheet in you should see it under my drive now I've sent this over to a different account so I have to go to shared with me and then I have to type in the specific uh let's see yeah title and then find it yours will probably be in my drive though sheet name now I've created three different sheets one for meetings booked another for proposal sent another for deals closed revenue and cash collected so the one that I'm curious about the one that I want to interact with now is meetings spook that's I'm going to be using and then for values I'm just going to be pumping in uh the values so date added uh created at that sounds reasonable for me um email I'm just going to go to attendees and then go to email and then date booked uh we're just going to use a start time of this date looks like this hypothetical calendar event here the Ping test that we sent was created on March 9th 2024 at 6:20 a. m. and then it's also going to start at March 9 2024 6:20 a. m. but in reality these are obviously going to be different because you can't book a calendar event for the same time that it is taking place and now what I'm going to do is actually go in I'm going to create the web hook actually first I'm going to go into this and then I'm going to book an example meeting I'm going to say I'm booking this is my example phone number I'm going to set my gross monthly Revenue to low and then I'm going to go ahead and make sure this is on good click confirm what's happening now is it's being sent to the back end we caught the web hook we see that the event type the trigger event is booking created which is the only one that we're actually watching and then there's a bunch of information here coming in including the start time the created ad all the information that we need uh before it goes to the Google sheet the input are these three values it looks like there's a difference in the formatting between date added and date booked this is pretty ugly so in practice I might change this if I go back to the Google sheet let's see yeah oh no actually it looks like they're all uh they're both in this datetime format so that's fine I'll just leave it as is but yeah we've now added a meeting to this sheet and now we can visualize this data in looker Studio I'm not going to do that yet I'm going to wait until we've run through the rest of these fields but this is just a quick and easy way to get data into the system using a web Hook from cal. com and I've probably showed you guys this before now it's time to actually go through proposal sent the way that I'm going to do this is I've set up an example type form and I've called it client X dashboard Discovery call form so there's a pretty common design pattern when you're doing these sorts of things for agencies and for coaching products or coaching info products and that design pattern is anytime that A salesperson does anything with a prospect they need to log it somehow and so I've just set up an example logging form here and the only thing it asks for is hey what was the outcome of the call answer in US Dollars and so the reason for this is because if you don't at least have salese do some type of sales admin or logging you just can't grow your organization because they will make like backdoor deals and they'll you know text the client at uh 9:23 p. m. to get a deal closed you basically just need some way to log all the interactions sales is having with prospects if you want any chance of optimizing it later on or scaling the company so getting in the habit of something like this early is probably your best bet as a sales

organization now in this case we're just asking for the outcome so we just want to see hey this call happened was it good if it's good then I've sent a proposal we're just presuming that the salesperson goes in and then you know in this case they're like crafting Little clickfunnels Pages themselves and sending it which I personally think is not a very good structure but I don't really have too much control over that at the moment so uh we're just going to say that we've sent the proposal or in this case the page and then uh we're going to click submit and then when we click submit what we want to do is we want to go back and actually catch that event and when we catch that event we want to add that to the proposal sent page so I'm going to go to example builds and then I'm going to call this client X proposal sent let's actually do client X um Discovery form filled let's do that what I'm going to do is I'm going to go to type form and this is going to be our transformation this is how we're going to know that the meeting actually occurred so I'm going to click add and then the event here is I'll just go example dashboard Discovery call form I really need to get a name and Convention for this stuff okay there we go and then we're going to click save now I'm going to be using my two-step type form design pattern which I've showed in all of my previous videos but if you're new here basically I just add a sleep between the type form watch responses module and then the type form list responses module and this just allows me to do very quick and easy testing on the form when I need to saves me a bunch of time later on as you'll see so I have the form in mind the form ID that I'm picking is my client X dashboard Discovery call form I'm going to leave everything else blank here and then the limit down at the bottom is one and so in this way I'm only going to be selecting one record I'll leave the sleep at two and just make sure everything else works okay great now I'm going to click run once and I'm going to actually fill out this form which is really just one click so not that big of a deal uh but then going to click submit and then you'll see that it catches the form it's going to wait 2 seconds and it's going to go and grab that same form and if I head over to list responses here and I go to mapable answers you'll see that it says what was the outcome of the call sent proposal um and so we have all the data that we need now to build the rest of this out uh except for email actually looks like I didn't ask email yeah so maybe I should ask the client email here just as an example let's just go back here at email and I'll say we should make these required as well if I'm showing you guys something I prefer you know it' be best practices okay let's do that one more time let's run this make scenario here and then we're going to have two thing questions a proposal and then we're going to have what's the email and now we should have all the information that we need for this specific example so okay great we got it map answers include the outcome which is send proposal and then the email address great that's all we need now we're just going to put this into our Google sheet and then go to add row once again in practice what I'll do just to avoid having to like go back and then grab the same data and map the same spreadsheet as I will always just copy the scenario from the previous flow um oh yeah it's in shared with me I'll just copy all the scenario or all of the modules from a previous flow uh which you know usually have all the setup for me I just wanted to actually go through and build it because I think that people find it valuable to do so they're probably a ton of tiny little things that I'm doing here that I don't really consider that important for instance um so we're going to say date added um I'm going to go back here to the actual event and I'm going to say submit it at the description is just going to be the title I would say doesn't look like there's a title in the list unfortunately so I have to use the um the watch responses module and then the email is just going to be here awesome that looks pretty good now we're just going to run that one more time and then I'm going to fill this type form manually just so that we can really give it a good test make sure everything's good send proposal what's the client email let's run this puppy looks like it was filled and now we filled out the rest of this now Google Sheets has an annoying tendency to bold every new row if you bold The Heading so this is just me eliminating that and making sure that all future rows are not going to be bolded um okay great so now we have um a transformation that enables us to get new line items in proposal sent this is going to make it really easy to do math on later I will say um make sure that you're not just building this out you know just to reiterate that you're not just building out these forms as is I'm just using these forms as placeholders for transformation so I mean in this case the discovery call form I probably would have a form for that because I want the sales people to get in the habit of

manually logging their activities but for um the next form that I'm going to show you know this is probably something you just like collect from stripe instead okay now the only thing we need to do is add deals closed and as mentioned I created a specific form just to log that and what I'm going to do is I'm going to go down here to client X dashboard sale form and then show you what that looks like now in this case uh we're just using like a form to template this out and then ask how much revenue contra Ed and then how much cash collected today but again you would probably just be collecting this data from stripe or something it's just difficult to do that in practice on camera when you're dealing with like stripe tokens and that sort of thing so I'm going to use this type form as an example in reality you would probably have your own um data source setup whether that's pandadoc or some web Hook from authorized. net or a web Hook from clickfunnels or maybe yeah pandadoc whatever so I'm going to say three client X sale form filled now we're going to go know type form and I'm just going to do the same thing that I did before where I'm going to go example client dashboard sale form filled I'm going to select the sale form it's going to map the web hook which is nice then I'm going to do my two-step design pattern with as sleep I'll set this to two I'll go back down to list responses add that in I'm going to select the same form that I had before or this form here yes and then I'm going to leave everything as default set this to one so you can see a lot of the exact same things over and over again especially when you're designing these agency flows just because they tend to use the same stack and a lot of the functions are modular and reusable okay great now all I have to do is actually test this puppy out um I'll probably just use like a flat product ID here as an example but because we've gone so far with us I'm just going to continue and then map out the rest of it so I'm going to add this row to a Google sheet and then I think I have to select the second I really have to go in and then um like what you should do in practice is if you keep on getting like error file not found or error unauthorized you should really go in and then just remove these connections from your make account if you don't know how to do that you have to go down to connections and then manually um select it and then take it out okay for date added we're just going to use submitted ad again description of this form is just going to be title and now we can get to the contracted revenue and cash collected so Revenue contracted should be here cash collected should be here I didn't fill out the email that's annoying uh and then product ID why don't we just do let we just do form ID that makes sense and then customer email I still need to fill out so I'm actually going to go back and then fill out the form we're going to unlink this and then I'm going to run and what I'm going to do now is just grab this uh form here I think and then I'm just going to add another question here with the uh with the email so you're going to go to email and then I'm just going to say customer email we're going to make this required I'm going to publish this and then I'm actually going to go out and then fill it out uh we're going to say 10,000 was contracted Nick at oh did I add this in the middle I think I added yeah that's sort of annoying then we'll say 5,000 cash collected I'm running this right now so it's waiting for the type form submission so I just sent it has now grabbed it doing my two-step design pattern I disconnected this because I didn't want this to continue before I knew what Fields I was working with and so now that I have the email field I can actually connect this and if a variable is ever inaccessible from one module to the other if you just reconnect it often times you'll be able to get that make just uses that to sort of like refresh the fields that were created so now I have access to the form um email for instance okay now that that's all done why don't we just run this one more time I'm going to fill this out manually again just because I like doing this live on camera I think people get value from this so we're going to do this one more time customer emails nick. left click. we selected we collected $5,000 in cash a day 2 second sleep went through we relisted the responses and then we added the row values that we added were we added a date in Mak stake date time format we added a description which is just the title of the form we added the contracted Revenue cash collected customer email and then some product ID which is just the ID of the form and you can see everything that was added in seems to be added in correctly yeah which is quite nice we may have to muck around with this datetime format I'm not entirely sure if this is going to work natively uh with the dashboard I just don't remember

off the top of my head but yeah that's where we're at right now um for Simplicity sake let's just make all of the dates here in their format so we'll say that we collected a payment on the 3 and 7th and then we collected a payment on the 9th because I'm actually going to go out and actually like do this now and then why don't we just add a few more rows here to make our lives easier let's say that we booked three meetings the first meeting we booked on the third or on the second the third meeting we booked or whatever was on the third and then the fourth meeting we booked was on the fifth then from these three meetings oh let's update this as well this is doesn't really line up because somehow we're going to be getting payments from meetings that are booked in the future but whatever um yeah so from these three meetings we're going to close one proposal uh so we should be getting a rate of 33% and then from this one proposal we should close a deal so our technically our closing rate should be 100% probably not accurate you're probably not going to get 100% closing rate unless you're me no unless you're a really good salesperson uh but yeah now that we have everything that we need now that we've uh connected the make flow uh to the Google sheet we have everything that we need to actually go out and then produce this in looker studio and so that's what I'm going to do now and I'm just going to set all these to run hypothetically as if our system is now on and then I'm going to go back to scenarios and then go to example bills just to see them all client X meeting book client X Discovery form fill CeX sale form fill beautiful now how do we actually turn this into some pretty visualization well just head over to looker Studio on the account that you're integrating which actually is not the account that I have right now I think so I have to head over to this account click blank report and this will allow you to jump in easily and then Google connectors is basically just your data source and so this is just going to be where you're pulling stuff from so we're going to be using Google Sheets for this obviously and so what I'm going to be selecting it already knows CU it's just going to take the most recently updated sheet uh this client X dashboard tracking sheet now I'm going to actually be using all three of these but because I've separated them into different sheets I need to select them all individually so the very first thing we're going to be doing is we're going to be looking at meetings booked you'll see that it allows you to set some options so you can use the first Row's headers which makes sense include hidden and filtered cells which we don't really care about but I'm just going to leave that and include specific range I'm not interested in a specific range I just want to select everything so I'm going to click add okay and as you can see it's actually gone through and it's collected some data and created an example report for us which looks to be just a table or a chart that they've added and then what it's doing is it's selecting or it's listing the email and then it's SE selecting the number of times that email has popped up and because I've just copied and pasted the same record it's the same email it's saying that Nick Sur has booked three meetings so just to show you guys what the refresh looks like I'll just pretend that we've actually booked three meetings with Nick Peter and Sally um and then uh we can click this refresh data and it'll go and it'll basically just pull that Google sheet again and now you'll see that it's basically just counting up or trying to list all the different email addresses in their record count Google looker or uh looker Studio or Google data Studio or whatever you want to call it can seem really complicated initially um so like I don't even fully understand it at this point and I build dashboards out for a living and make you know a fair amount of money doing it so you don't need to know everything and what I'm going to do in this video just show you sort of like where to start with this sort of stuff um I don't actually usually start with just like a blank uh dashboard here I usually use a template so maybe actually I should just show you to do that that's probably simplest I yeah why don't we start with a template just so that you guys can see like when you start with a template it's really easy um I like this ga4 report template so I'm just going to use that better that we start with something where you guys can see how the data fits in so I'm going to click use my own data it's going to ask me to connect my own data but I'm just going to use Google Sheets instead go back to CeX dashboard sheet and then click add this way we just have a styling and everything it's going to look much prettier now uh because all of these were previously set up uh there going to be some issues it's not going to be able to essentially connect to the right data um and that's okay we're just going to click edit and share up here and then we're going to run through every chart and sort of like manually change it so what are we really curious about we are curious how do I actually edit this thing oh I think I'm In Like A View mode or something yeah which is sort of annoying let me go back and then click edit okay so what are we actually curious about it's going to tell us if there's an invalid missing Dimension don't worry about that uh what are we actually curious about we want to get the number of meetings booked so what I'm going to do is I'm just going to um go to my so at the top right hand corner here is basically like a selector where you can go through and then you can select the specific data

source and then select the field in the data and that sort of thing so um what I'm going to do is I'm going to select this very first chart here which says that this needs some work that's what the wrench uh is for and then I'm going to go and select the date range Dimension you'll see that it's actually automatically selected the date range dimension for us which is date added so that's what we care about date added um a lot of the time just automatically going to select that for you which is nice the metric that we're curious about is indeed record count which is nice uh what you can do is um yeah I think that should be fine yeah probably let me actually get uh wonder why is it not showing the record count maybe we should just do all time yeah record count is probably uh not the right field let's count the number of dates that were added that would probably work H yeah it's a that's actually not so we're actually just going to go create a new uh create a new field here to do so you head over to the right uh not the right hand side you head over to the top here and you click add a chart I'm just going to select the scorecard drag this down here CU that's uh that's what our field is a scorecard the data source we're going to use client X dashboard tracking sheet the date range Dimension we're going to use is date added and then um if you click underneath here you'll see there's a ctd account um usually we want to use the record count yeah there you go and what record count is it just means the number of rows in this uh in this sheet and so there's basically one two three records aside from the header which isn't counted and that's how we select uh that's how we get three as a result and so you can change the name of this and stuff like that if you want um there's a style on the right hand side and then there's a setup on the left uh I'm just going to leave it as is and then I'll like worry about dressing it up and making it look pretty and stuff like that at the very end so yeah uh basically we have three meetings booked and I can change this name if I click on this little thing from record count it is not cooperating it looks like let me just jump back here and click that again to meeting meetings booked and that's going to change the title which is what we want just so that we know what this is and then I'm just going to delete these other ones because clearly it's a little bit buggy and we're probably not going to be able to get the specific thing that we want and now I'm going to go in and then I'm going to say proposal sent so to do that I need to add another data source right now we have the meetings booked tab of our Google sheet we don't have the proposal sent tab of our Google sheet and so to do that I think if I click here no I need to add a new connection so I'm going to go to data and then at the very bottom click add data I think and then Google Sheets yeah client x-board tracking sheet go to proposal sent and then click add now this is going to pull out the proposal sent data rather than that other data and if we Mouse over this um you'll see it's still connected to the meetings book so we got to go down and we have to select the proposal sent which does not appear to be populating as you see some of this stuff is pretty slow and pretty buggy which is unfortunate um but this is still the platform that best integrates with Google Sheets and so that's what we're using okay great so now we have three meetings booked we have one proposal it's called record count so we can change that name again to proposals sent press enter and then now what we want is we want the deals closed so I'm going to copy this record again I'm going to move this over you'll see that Google or looker Studio allows you as you drag things to sort of tell um the distance from another chart or something just to help you line it up and so now that I've dragged the server here you'll see that there are these equivalent lines equidistant lines between meetings booked and the proposal sent and this new module that I've just created uh so that's pretty helpful and we just have to go through that same process again we have to add our data so I'm going to go down to add data and then I'll go to Google Sheets again client X dasboard tracking sheet and then deals closed revenue and cash collect and then I'm going click add now it looks like this is just taking a little bit of time to connect so I'm just going to give it a second I'm going to remove the data source that I had previously and then I think I what did I do last time I deleted it and then I created again I think that's what I did I don't actually remember oh no I guess

I just needed to refresh it okay and then I'm going to select the new um data source which if I hover over it looks like a little uh like info thing pops up that says revenue and cash collected which is what we want so I'm going to select that and then what I'm going to do for my record name is I'm going to select um deals closed I'm going to write that here and you'll see that it says and the reason why it says three and instead of uh what do we really want here actually we should probably delete one of these it's probably not accurate okay let's just delete this last record here because I want to show you how we're going to track the number of deals closed versus the number of line items in here um so I'm going to go over here refresh the data and now this should say two good um right now it's saying two realistically if you remember earlier in the video I said that I wanted to have a rule where only line items with a contracted Revenue uh greater than Zer or technically counted as a deal because logically speaking you can't really contract Revenue unless a new deal is signed right this just allows us to get this into three sheets instead of four and I think just makes it more interpretable for myself and the client so in order to do that we're going to set a filter and so we're only going to filter for rows with a revenue higher than zero in order to do that we're going to go down to the bottom here where it says filter click add a filter and you'll see there a bunch of these default filters here for Google analytics um since we're using that template I'm just going to create a new one and I'll say revenue is greater than zero equals deal and then Google Sheets allows you to set a couple parameters here what we want to do sorry Google data Studio allows you to Google looker connect or set up a number of filters here what we want to do is we want to include only the rows where this field contracted revenue is greater than zero you can also imagine that you could set it so that this is not equal to or whatever or we could exclude all rows where the contracted revenue is equal to zero uh there are many different ways you can do this logically but this makes sense to me so I'm going to use that and then I'm going to click save and you see the deals close has now gone down to one which is quite nice now what I want to do is I also want to get a measure of how much the revenue collected was and how much cash collected so I'm just going to drag this over here and then for my metric that's the number that's going to appear underneath the scorecard title I'm going to go down to contracted revenue and then I'm also going to go to sum or I'm going to click on this little um after I click out and back into this because it doesn't let me select it I'm going to go back here to this little pencil and you'll see that there a bunch of aggregator features you could collect the sum of all of the rows where this filter applies or you could select the average count or the Min or the max the standard deviation uh we're interested in the sum because we just want to sum up all the contracted Revenue in a certain period basically and so that's what I'm going to leave it as but I don't like the fact that this title is a little long so I'm just going to call this Revenue instead of contracted revenue and that's just going to make the title a little bit smaller for us a little bit more visible okay so that's the Topline revenue and then what I'm going to do is copy this again and then I'm going to go over here and then I'm going to do the same thing but instead of Revenue what I'm curious about now is Cash collected right so I'm going to go to cash collected sum again look looks good awesome and then this doesn't look super pretty so what I would like to do is just to make this look a little hotter looks like the way that these guys are doing is they have like some overlays going on so I'm just going to move this down because I I am going to use this uh in a moment and then I'm going to stretch these out so that we can see the absolute solute figures and then underneath I'm going to do like percentage figures so if you just dra uh select all of them and then drag it you're able to make them a little bit wider and you'll see that these aren't all at the same Heights either it looks like the revenue is just a tiny bit higher so I'm going to move that down I think it's going to no there you go should be good and then I'm gonna try and move that down just a tin a bit looks like proposal scent is a little bit out of whack so there we go okay so now all these are on the same level which is nice um just nice OCD uh it's nice to have OCD it is not it's nice to just sort of make them all line up and then what I'm going to call this is just for Simplicity I'll call this absolute then I wonder if I make this longer if I can yeah there you go that looks nice we'll call this absolute metrics and then I'm just going to copy this whole thing paste this down here and then I'm going to do you can set this up however you want

I'm just going to call this like rate uh let's do percentage metrics I remember earlier how I said that we wanted to grab um like conversion rates and stuff like that and so I want to get two conversion rates specifically the number of meetings book or the number of proposals sent the number of meetings booked and then oh I just realized that the proposal scent went down to zero I wonder why did the proposal sent go zero looks like the date is March 2nd to March the 8th so maybe the proposal sent date was outside of that yeah it's March the 9th e okay that makes sense um because the date range dimmension field is set here on all of these um metric basically whatever date is selected up here is going to be the date that's selected everywhere else so uh we're just following the date range Dimension basically now we can manually change this I don't want this to be last seven days unfortunately it looks like this is a little laggy so I'm GNA give this a second uh for hypothetical I just want to do like maybe 25th of February up to 31st of March that should allow us to get all of the uh all the data you'll see that it's taking a second to update but it actually just went through and updated the revenue and the cash collected Fields uh to me they aren't like dollar figures which I'll add in a sec uh but I'm just going to do this percent metrics first okay so we have the absolute metrics which is presumably going to be the most important thing for people but then we also want to see some percent metrics so we want to see what the conversion rate is between the number of proposals sent and then the total number of meetings booked we'll divide the two and then we can either multiply it by 100 or we can use percentage format that'll just show us what the uh what like the successful meeting rate is um and then the deals closed to proposal sent that'll show us what the conversion rate is and then those are probably the two most important metrics now there are many different ways to do this in looker Studio as I mentioned I'm not really an expert um I know how to set up the back end of all of this stuff which is I think why my services are uh considered as valuable as they are but there are two different ways to do this or three different ways to do this and I'm just going to use the simplest one the one that works for me and the simplest one that I found is I basically just select these two charts then I rightclick and then I say blend data and then this creates a third chart which I now have and I think if you select it properly then you just automatically get the ratio between the two which is what I'm interested in but if you Mouse over on the right hand side under setup you'll see the title proposal sent divided by meetings book you can also click into the and then you'll see that there's actually a formula was created where it sums up all of the proposals that were sent so the number of proposals sent and then it sums up the number of meeting boken just divides the two um so that's the metric that I'm curious about um the title is a little bit too long for me so I'm just going to go back and I'll call this um let's just do this at like proposal to meeting percentage and then I'm going to click apply and because all I did is change the title we should have that there okay great and now the next uh metric that I'm interested in is the percentage deals closed to the number of proposal sent so I'm going to do the same thing I'm going to click on deals closed right click on proposal sent and then go down over to blend data it's going to create a summary field in the middle and should be the same thing as before yeah and now that I have my metrics I'm just going to delete all of these because I'm not really oh actually you know what I think I can make some more yeah why don't we get like value of a proposal too that would be interesting okay great I'm not actually going to delete all these I'm going to create a few more summary metrics so um you know what I want to get the value of a meeting this is an important metric to get so basically uh let's pretend that we made $10,000 in Revenue off of our one deal closed our one deal Clos came from one proposal sent which came from three meetings you can actually assign a value to each of these actions so that you can plot how much time energy and money you should be willing to spend on people and so if we're willing to spend or if we make $10,000 how many deals did it take us to make that one how many proposals meetings did it take us to make that three so 10,000 divided by 3 means the value of every meeting to us at the time of this date range is $3,333 so we should actually be able to do that would be pretty cool to do so I'm just going to select revenue and then meetings booked I'm going to click blend data and we should just get I think we're going to get that or maybe we'll get a percentage I don't know yeah we're going to get a percentage um so now we have Revenue to meetings booked so I'm going to go over here and then this isn't actually going to be a percent it's going to be actually currency so why don't we just go down and do currency it's going to be US dollar so I'm going to scroll down here and it should just have a dollar sign now um this looks way too big though maybe I need to apply the change first before it converts yeah there you go and then we're going to say value of or let's just say meeting

value and then instead of percentage metrics we're going to just call this relative metrics that should be clear there you go that looks nicer um and then why don't we also get the value of a proposal too that'll be uh that'll be valuable so we're going to take the revenue first and then the proposal sent second and then we're going to right click and go down to blend data so going to create another one here and I'm going to scroll that down change it from a million percent to dollar bills y'all go to US dollar here and then I'm just going to call this proposal value and then I'm going to add a bunch of data here just so you guys can see what this looks like when it looks like nice and it's all touched up and we're actually using it in reality cool we don't need these anymore so I'm just going to exit out of that I'm going to move these over here these should line up reasonably well yeah okay looks like they line up and then I'm going to do proposal value will be second and then meeting value will be first I don't think I can I do not seem to be able to make it line up which is sort of annoying but there you go I think that's good I don't know this looks much later than me but I need to change his title maybe that's why uh instead of deals closed to proposal sent we'll say proposals uh proposal to meeting we'll go deal per proposal now this is just a format that I'm doing with this colon in between the two operators um I just find this the simplest sort of to conceptualize but I'll leave that at that and then I'm just going to change both of these formats to Dollars just so that they look uh pretty so got a currency here scroll down to US Dollars looks good to me and then we'll do the same thing for cash collected so here and then the data type is going to be currency we'll scroll down to US Dollars okay beautiful awesome um thinking if there are any other metrics that I might be able to pull out of my ass that would provide value I think that's it yeah it yeah okay I'm just going to leave sort of an empty space here unfortunate but I think that this will imply that there is certainly some more valuable data that I'm sure we could add and just the fact that I'm using this Google analytics for report template uh makes this just a lot easier I think that this already looks reasonably nice uh I'm not much of like a dashboard designer um I prefer dark mode for everything to be honest I just copy the same dark mode template but I think this actually looks pretty cool so I may be using this moving forward I'm just going to call this sales Das board and then where it says default data click to select Google analytics I'm going to delete that because I don't actually need this now we have a bunch of chart metrics or scorecards as they're called but we don't have like a way to visualize how our sales progresses over time and I would say that's really the only thing missing from this dashboard before we can just put a pin on it um and you know like after this video I'll probably go in and I'll add a couple of additional bells and whistles and maybe um maybe I'll find a way to like break this down by salesperson uh what I'll probably do is because you can see the recipient on the yeah I'll be able to log the recipient or not the recipient the salesperson through the form that they create the proposal that they create to send out the client so I'll probably be able to break this down by person and I'll say person X produced this much in Revenue person y uh and that starts to get into like the cool parts of this where you can start tracking profitability and average cogs and start making higher decisions but yeah for now I'm just going to take this and I'm going to visualize this data so I'm going to select all of this and I'm going to put this right over here and you'll see right now it says too many rows do the number of rows the chart canop be Runner I'm just going to create my own so I'm going to click uh actually I'm going to copy the style so let me just move this down here first uh if you go down and add a chart you'll see there are a bunch of options here for bar charts time series charts you could do like vertical bar charts pie charts right I think what makes sense for us what we're going to do is we're going to add a contracted Revenue um time series chart so we're going to head over to add

a chart and then go down to time series give that a quick click and then that will show us sort of like the rise and the fall and revenues and stuff like that over time um which is quite valuable I've tried doing this before with a bunch of different types of charts it doesn't really seem to work um because what's important is you want to grab like the days um so you want to be able to see the xais with like every single day of relevance and I think you can select whether you want this to be days or weeks or months but days uh days is fine for our purposes okay now obviously this isn't super informative right now because we only have one transaction with Revenue um so what I'd like to do is I'd like to first I'm just going to copy this I'm going to paste uh special and then paste style only and just see if there's any difference yeah there is a difference I don't like that I was trying to borrow the style from the previous chart but doesn't actually look very good so I'm just going to grab this whole thing now move it up a little bit just a tiny bit and go up to maybe a little further just because I noticed that there's like a disparity between the uh the line Heights Here There You Go that looks good and then I'm going to go into our dashboard now actually just add a bunch of like fake data just so we can see this um a little bit more granularity I would say so let me go over here to meetings booked and then I'm just going to copy and paste this a few times and then all I'm going to do is I'm going to change the date and I think if this is like rendering datetime format properly if I do this no it's not rendering datetime format here so I need to do let's do like 11 we'll do like 15 we'll do like 18 I'll do 21 I'll do 25 let's do 26 27 and then we'll do 29 so now I have a bunch of meetings booked let's add a bunch of proposals oops we do not want to change our header so I'm going to say that this one was on the 11th 14th this one was on the 15th this one was on the 19th this one was on the 21st I'm not going to worry about changing the emails or whatever just cuz we're not tracking that we're only curious or the only thing that's actually relevant for us is the record count and then let me actually just make sure that we have fewer deals closed than proposals because you usually don't sign more deals than you do proposals uh that should be good uh we'll say we signed one on the 7th and 10th next payment came in on the 11th we signed one on the 13th and the next payment came in on the 17th and then we're just going to change these figures so maybe it's four 4,000 was 2,000 cash collected and then 2,000 and then here might be 6,500 we collected uh let's do like a staggered payment schedule for this one say we collected that much and then on the 18th we collected randomly the other 1500 yeah that looks good okay great so now I think we actually have a fair amount of data like we'll see we have three Deals closed here we have what looks like five proposals sent six proposals sent we have a bunch of different cash collected rows here not all of them line up perfectly uh so that should be good I'm going to call this client X yeah Licor studio is pretty laggy which is the unfortunate part but uh client X dashboard we'll do sales dashboard then we'll do example guess it just deleted everything client X sales dashboard exam Le there we go okay great so that's what our dashboards looking like right now and I'm going to go to the right hand side here click refresh data uh this can take a fair amount of time if you have a lot of data coming in so just be wary of that and there we go yeah we have our meetings book that we're updated proposal sent deals closed Revenue cash collected in the period proposal to meeting percentage 50% pretty good deal to proposal 50% average meeting value $178 average proposal value $ 3,47 this makes sense twice as many proposals sent as deals closed and then twice as many meetings booked as proposals sent so these just change by Factor two uh it looks like the conversion rates are 50% which is pretty good realistically in practice your proposal to meeting booked percentage is probably going to be somewhere around there if you're good your deal to proposal percentage might be like 20 30% maybe 15 20% for really high ticket service but yeah uh and then now that we have this trend or contracted Revenue thing we can like we have a couple of different options here the way that we want to show this we could do like an

accumulation I think so we could show how it grows over time we could do a running calculation oops I didn't want running Delta I wanted running sum which should just add it up over time so we could see the growth no that did not um might be in data type number yeah first of all let's change this to currency so let's go down to US dollar and then weird how the running sum was different than this huh odd I think we can change this if we go down to style um oh cumulative there we go yeah yeah uh and then we don't want running sum my bet so we want to go back here change running sum for running some just to none and then now we'll get like a cumulative Revenue growth graph over time and then we also want uh what to do with zeros so missing data so if there isn't any data on that it just says go line to zero um we could linearly interpretate the data that's not really 100% accurate we could smooth it looks like so we could make it a little B prettier that way um keep in mind this isn't accurate if you do it that way as well because you see technically this is saying 14,000 this is lower than 14,000 still says 14,000 so maybe I wouldn't smooth it but yeah I'd probably just go line to zero that that's fine to me just kind of wish that we could see the data down here oh you know we can probably do this by adding like a little check mark or something I think there's a way to do that let me look for this show points I think show Ste lines that's probably it uh no that's not it okay anyway I clearly don't know enough about the Styles here for this to be meaningful um but is what it is you can add as much you can mark this up or dress this up as much as you like um yeah hopefully this is actually educational indicative that you do not need to be a looker Studio expert in order to make money off it uh you know you can just spend more time figuring the stuff out on your own your value here isn't really your ability to like mock up a pretty looking dashboard it's just your ability to like take the data source set up a bunch of infrastructure to like funnel that into a dashboard or something okay in make. com and then to use that to like pump this to some visualization if you know a designer on their team wants to mock this up make this look a little better than they obviously could um yeah you know and we didn't really have too much sales history here so I think it starts at Mar just a second so let me just set this to my specific date range and this will probably be better I think we'll start at March the 3D and then we'll go until March the 18th let's do that if this date picker ever loads and that is the real question oh that's pretty okay well I guess we're going to have to refresh that puppy yeah this is only really going to happen when you are recording um with abs which is notoriously computationally expensive when you have your own camera but whatever okay let's start at the second then so we can see the increase in from zero until where we are now and so okay great you see that on March 2nd contractor Revenue was zero and then it was $10,000 and then March 9th to 10th it grew another 4,000 and then March 12th to 13th grew another 6,500 and so this is sort of like a growth line so I'm going to go over here to this great growth make it a little bit wider zoom out just a Ted just so you could see everything of interest and then we can play around with Styles and stuff um there should be a way to edit the theme if you go to theme and layout I do actually kind of like this theme it looks pretty clean to me but let's scw around with constellation oo that's spicy yeah that's not bad at all maybe we could do Insight looks the same ocean oh it's just changing the colors that's lame Lagoon you know what I like Lagoon you'll see how the title here clearly has a white background so I need to select this color background is this which is secondary so I think we can just jump in here and then change the background to our secondary color no that's lame that looks dumb we need to change this to a darker color I think that's our secondary maybe this is our primary oh yeah that's clean I love

it little hack generally know because I've suffered through looker studio for long enough is if you um never mind I take back my hack there's a way that you can paste uh copy Styles and then paste the styles of text Fields but I think they have to be a little bit bigger because you have to be able to select the background not the text okay great this looks pretty good I'm uh pretty happy with this yeah what I'm going to do is I'm going to dive in here afterwards and then do a bunch of uh bells and whistles sorts of fields so um I think revenue and cash collector are probably more important than the rest of these so I'll probably switch them arounds that revenue is the beginning and then the rest of these are sort of at the end uh yeah I mean that sounds pretty important to me right like I'm mostly interested in how much money my money made I'm interested in that too but I'm mostly interested in how much money my business made more so than I am the meetings B proposal center deals closed um I'll probably jump in there and maybe make it a little bit more visually interesting as well I'll add a couple of additional Fields like this one's Revenue growth it might be worthy to have a second one here if I just paste this in and then call this try and line this up exactly make this a little bit uh a little bit bigger and a little bit taller here and then call this like cash growth or something just so you could see how that's growing over time which I think is important and then if I just go down here and then I just change this contracted Revenue over to cash collected then uh you see how the the pattern is a little bit different uh because Revenue only counts when the transaction when the primary transaction occurs it tends to be pretty stepwise whereas cash collected typically you drip that out a little bit more over time so you see that there are more transactions here the line's a little bit smoother uh there probably some other insights that can be taken away from this as well but I'm going to leave it there uh you now have everything that you need to charge anywhere from I want to say $3,000 all the way up to maybe $10,000 or more I will say that the more um costly dashboards the usually an expectation that you do significantly more than I did here I just wanted to make it simple and you know palatable for you guys that are doing this for the first time but you could totally build this for probably about $3,000 I think that already has enough informational value to the client that they'd be able to make decisions using that data on a day-to-day basis that would net them way more than $3,000 over the course of the year keep in mind that most clients don't know how much money they made in the preceding month and to get that information would take them like 3 or 4 hours to compile books and stuff like that so this sort of thing is outsized value um you know your worth and know the value that you bring to an agency it's one of the big problems that they struggle with uh and coaching companies as well if you guys have any questions about anything that I've done so far just drop a comment down below more than happy to answer personally if you guys like the video please obviously like it if you're a subscriber thank you very much and if you're not uh please subscribe and hit the notification button somebody told me that I should tell you that uh this is my first video after my first day off in quite a while the other day which was quite Lov uh but I really missed recording this sort of thing it's also almost become sort of like a psychological paliative to me so uh I'm going to be keeping this stuff up for the foreseeable future thanks so much for watching have a great rest of today