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 companies that I work with.
I usually sell it from anywhere from $3,000 maybe up to $10,000 or so, assuming if the company has a lot of money to spend and this is really important to them. And in this video I'm going to walk through everything from start to finish. It's a real live client build for a project that I'm doing. If that sounds like something you want to learn how to do yourself, stay tuned and let's get into it. Alright, so for an example of 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. 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 Stripe transaction or maybe a Panadoc.
proposal signed or something like that. 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.
Looker Studio is a continuation of Google Data Studio, if you guys are familiar from that 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, trends, you can plot things as sort of a graph or a chart. Then 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 that 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.
I've set up our Google Sheet structure and then I've scaffolded out a couple of the make scenarios just to make sure this video isn't five hours long. But in reality these sorts of builds usually take me I want to say probably about three hours of actual like dev time 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 Actual data base is going to look now database is a fancy and loaded term Most of the time when you're doing coaching agency or info product businesses your database is just the 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 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? 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 my recent videos, you probably think the proposal is like that beautiful seven-page document that I generate.
These proposals are much simpler. They're basically just like a one-pager form and then they just, you know, have payments 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 then a proportion of those are going to go and actually like pay the money. So we're going to track the number of meetings booked, then we're going to track the number of proposals sent because 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 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 We're calling this proposal, it's really a contract. There's a signature and everything. 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. 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 a lot of guys in the e-com space or a lot of guys in the coaching space are like...
I made $45 million in revenue in two years and blah, blah, blah, blah. You're like, damn, man, that guy's crazy. Well, he's probably talking about the contracted revenue, not the actual cash collection.
let alone the actual money that he keeps in his pocket after expenses. So yeah, contractor revenue is the amount basically like at the total on the bottom of the contract the client signs 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.
You really don't need to know any more about finance in order to make like a good high quality dashboard that people like so don't worry too much about that but yeah the way that this is gonna work I just added some example data because I think this is a little bit trickier to get around. 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, you know, revenue and cash collector are gonna be different obviously because you're on a payment plan, right?
So the first transaction with contracted revenue is going to be 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, I'm going to set the contracted revenue to zero and then the cash collected will be some other number, whatever the remaining balances that they're collecting on. And so in this way, we can count up the number of deals that were actually closed by just looking at 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... 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. It's much faster I want to say and easily interpretable for the client and for me.
I use these sorts of dashboards in my own business. So yeah that's how we're gonna be setting this up. Now meetings booked. We're gonna have a date added column. We're gonna have an email of the client or a prospect I should say at this point whose meeting we are booking and then we're gonna have a date booked.
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, then the description and then the customer email. So probably shouldn't say customer email at this point. Let's just say email.
And then for deals closed, we're going to be collecting the date added, the description, just because 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. Contracted revenue column, obviously. We're gonna add that up.
Cash collected column, obviously. We're gonna add that up. 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 gonna be a placeholder since I'm just gonna be putting that data in using Typeform. But you know the specific way that the client is tracking is they have this big click funnel sort of flow and then when somebody buys something in click funnels it heads over to their payment processor for a specific product. And this is important for us to track because we want to...
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, well then we know that that's going to be like payment number one, payment number two. So this whole thing is totally dependent on how your client 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 gonna be doing. Okay great so yeah that's that. Let's actually go in and build it now.
This is the Google Sheet structure. Everything here is good. Let's actually jump over to 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 cal.com booking link.
And then every time a new calendar event gets booked in that calendar, I'm going to be adding a new row here to this meetings booked 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 webhook.
Because I know that Cal.com, my calendar platform, uses webhooks. But if you're using Calendly or something, there's a little Calendly built-in over here where you can just watch events as they come in. But I'm using Cal.com, so I'm using a webhook.
I'm going to call that custom webhook. The webhook name, we're just going to do new. Let's just do new example booking, March 9th, 2024. I'm 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 freelancing. 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 webhook just because that's not necessary, and I'm going to create a new one. So I'm basically just pasting in the make.com server destination, which is this link right here that they provide for you, into the webhook destination field. And cal.com does 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 maybe we only want to send this out when the... meeting ends or something like that. We have a lot of options here, but because I'm only curious about when a new meeting is booked, the only event 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 a payload template because I just want it to be whatever Cal.com's default is.
And then we can also just ping a webhook before continuing. The reason for that is because we just want to establish the data structure. So I'm just gonna 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 there'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, attendees, organizer. Actually I think that's I think that's enough for us to do everything right? 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. 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 in shared with me. Wherever your Google Sheet is.
Just make sure that if you connect to the account, make sure that you connect to the account 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 Share It With Me, and then I have to type in the specific 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 Proposals 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 booked.
So that's what I'm going to be using. And then for values, I'm just going to be pumping in the values. So date added, created at, that sounds reasonable for me. Email, I'm just going to go to attendees and then go to email. And then date booked, 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 AM. And then it's also going to start at March 9th, 2024, 6.20 AM. 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 webhook 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 gonna set my gross monthly revenue to low and then I'm gonna 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 webhook. 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. before it goes to the Google Sheet. The input to the Google Sheet 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...
they're both in this date-time 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 run through the rest of these fields, but this is just a quick and easy way to get data into the system using a webhook 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 slash 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 salespeople 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 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 we're just going to say that we've sent the proposal or in this case the page and then 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 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 naming 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 Typeform design pattern, which I've shown in all of my previous videos.
But if you're new here, basically I just had a sleep between the Typeform watch responses module and then the Typeform 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 gonna leave everything else blank here and then the limit down at the bottom is is one and so in this way I'm only going to be selecting one record. I'll leave this sleep at two and just make sure everything else works. Okay great.
Now I'm gonna 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 but then I'm gonna click Submit and then you'll see that it catches the form. It's gonna wait two seconds and and it's going to go and grab that same form. And if I head over to List Responses here and I go to Mappable Answers, you'll see that it says, what was the outcome of the call?
Sent proposal. And so we have all the data that we need now to build the rest of this out, except for email, actually, it 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, add email, and I'll say, We should make these required as well. If I'm showing you guys something I'd prefer you know it'd be best practices.
Okay let's do that one more time. Let's run this make scenario here and then we're gonna have two thing questions we're gonna have 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. Mappable answers include the outcome, which is sent 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 go back and then grab the same data and map the same spreadsheet is I will always just copy the scenario from the previous flow. Oh, yeah, it's been shared with me. I'll just copy all the scenario or all of the modules from our previous flow, which 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. There are probably a ton of tiny little things that I'm doing here that I don't really consider that important, for instance. So we're gonna say date added.
I'm going to go back here to the actual event and I'm gonna say submitted at. The description is just going to be the title I would say. It doesn't look like there's a title in the list unfortunately, so I have to use the 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 to 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. rest of this. Now Google Sheets has an annoying tendency to bold every new row if you bold the headings so this is just me eliminating that and making sure that all future rows are not going to be bolded.
Okay great so now we have a transformation that enables us to get new line items and proposals sent. This is gonna make it really easy to do math on later. I will say 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 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... salespeople to get in the habit of manually logging their activities but for the next form that I'm going to show you 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 gonna do is I'm gonna go down here to client X dashboard sale form and then show you what that looks like now In this case, we're just using a form to template this out and then ask how much revenue contracted 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 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 data source set up, whether that's Pandadoc or some webhook from Authorize.net or a webhook from ClickFunnels or maybe... Yeah, a webhook from PandaDoc, whatever.
So I'm going to say 3 client X sale form filled. Now we're going to go type form. I'm just going to do the same thing that I did before.
I'm going to go sample client dashboard sale form filled. I'm going to select the sale form. It's going to map the webhook, which is nice. Then I'm going to do my two-step design pattern with a 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 and over again, especially when you're designing these agency flows, just because they tend to use the same stack. A lot of the functions are modular and reusable.
Okay, great. Now all I have to do is actually test this puppy out. I'll probably just use like a flat product ID here as an example, but because we've gone so far with this, 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, 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 select it and then take it out. Okay, for date added, we're just going to use submitted at 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. Shit, I didn't fill out the email. That's annoying.
And then product ID, why don't we just do, why don't 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 form here, I think. And then I'm just going to add another question here 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. We're gonna say 10,000 was contracted. Nick at, oh, did I add this in the middle?
I think I added this in the middle. Yeah, it'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 need to sent it. 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, I can actually connect it to the other. And so now that I have the email field, other.
If you just reconnect it, oftentimes 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 email for instance.
Okay, now that that's all done why don't we just run this one more time. I'm gonna fill this out manually again just because I like doing this live on camera. I think people get value from this. So we're gonna do this one more time. Customer email is nick.leftclick.ai and we selected we collected $5,000 in cash today.
Two seconds sleep. Went through, we relisted the responses, and then we added the row. The values that we added were, we added a date in makes 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 date time format.
I'm not entirely sure if this is going to work natively with the dashboard. I just don't remember off the top of my head, but yeah, that's where we're at right now. For simplicity's sake, let's just make all of the dates here in their format.
So we'll say that we collected a payment on the 3rd, then we collected a payment on the 7th, and then we collected a payment on the 9th. Because I'm actually gonna 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. And then from these three meetings, let's update this as well.
This doesn't really line up because somehow we're going to be getting payments from meetings that are booked in the future, but whatever. Yeah, so from these three meetings, we're gonna close one proposal. 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. But yeah, now that we have everything that we need, now that we've connected the make flow 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, client X 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.
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 because it's just going to take the most recently updated sheet, 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 you've got set some options so you can use the first rows 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 selecting the number of times that email has popped up. I've just copied and pasted the same records the same email and saying that Nick Sarayev 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 and then Then 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 Studio or Google Data Studio or whatever you want to call it can seem really complicated initially. I don't even fully understand it at this point and I build dashboards out for a living and make a fair amount of money doing it. So you don't actually need to know everything and what I'm going to do in this video is show you sort of like where to start with this sort of stuff. I don't actually usually start with just like a blank dashboard here. I usually use a template.
So maybe actually I should just show you to do that. That's probably simplest, eh? Yeah. 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. 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 Client X Dashboard Sheet and then click Add.
This way we just have a styling and everything. It's going to look much prettier. Now, because all of these were previously set up, there are going to be some issues. It's not going to be able to essentially connect to the right data, 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 gonna tell us that there's an invalid missing dimension, don't worry about that. What are we actually curious about? We want to get the number of meetings booked.
So what I'm gonna do is I'm just going to 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... specific data source and then select the field and the data and that sort of thing.
So 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 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. A lot of the time it's 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.
What you can do is... Yeah, I think that should be fine. Yeah, probably. I may actually wonder why is it not showing the record count.
Maybe we should just do all time. The record count is probably not the right field. Let's count the number of dates that were added that would probably work.
That's odd, that's actually not. So we're actually just gonna go create a new create a new field here. To do so you head over to the right, not the right hand side, you head over to the top here and you click add a chart. I'm just gonna select the scorecard, drag this down here because 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 if you click underneath here, you'll see there's a CTD account. Usually we want to use the record count. Yeah, there you go. And what record count is, is it just means the number of rows in this sheet.
And so there's basically one. So we can change one to three records aside from the header, which isn't counted, and that's how we select, 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. There's a style on the right-hand side, set up on the left. 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, 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. sheet and so to do that I think if I click here no I need to add a new connection so I'm gonna go to data and then at the very bottom click add data I think and the Google sheets yeah client X dashboard tracking sheet got a proposal sent it's gonna click add now this is gonna pull out the proposal sent data rather than that other data And if we mouse over this, you'll see it's still connected to the meetings book. So we got to go down and then 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 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 the distance from another chart or something just to help you line it up. And so now that I've dragged this over here, you'll see that there are these equivalent lines, equidistant lines between meetings booked and the proposal sent and the proposal sent and this new module that I've just created. So that's pretty helpful. And we just...
We just have to go through that same process again. We have to add our data. So I'm gonna go down to add data and then I'll go to Google Sheets again, client X dashboard tracking sheet, and then deals closed revenue and cash collecting and then I'm gonna click add.
Now it looks like this is just taking a little bit of time to connect. So I'm just gonna 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 it 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 gonna select the new data source which if I hover over it it looks like a little like info thing pops up that says revenue and cash collected which is what we want.
So I'm gonna I'm gonna select that and then what I'm gonna do for my record name is I'm going to select deals closed I'm gonna write that here and you'll see that it says three and the reason why it says three and instead of 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. So I'm going to go over here, refresh the data. I know this should say two. Good.
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 greater than zero were 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 are a bunch of these default filters here for Google Analytics. 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 of parameters here. What we want to do, sorry Google Data Studio allows you to, Google Looker Studio allows you to connect or set up a number of filters here. What we want to do is we want to include 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 can exclude all rows where the contracted revenue is equal to zero.
There are many different ways you can do this logically, but this makes sense to me so I'm gonna use that. And then I'm gonna click Save. And you see the deals closed 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 gonna drag this over here. And then for my metric, that's the number that's gonna appear underneath the scorecard title. I'm gonna go down a contracted revenue and then I'm I'm also gonna go to sum, or I'm gonna click on this little, after I click out and back into this, because it doesn't let me select it.
I'm gonna go back here to this little pencil, and you'll see that there are 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, or you could select the count, or the min, or the max, the standard deviation. We're interested in the sum, because we just wanna sum up all the contracted revenue in a certain period, basically. And so that's what I'm gonna 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 top line 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 gonna go to cash collected I'm gonna go to some again 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 am going to use this in a moment.
and then I'm going to stretch these out so that we can see the absolute figures and then underneath I'm going to do like percentage figures so if you just 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 There you go. Should be good. And then I'm going to try and move that down just a tiny bit.
It 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. Just nice OCD.
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. It 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 gonna call this like rate. Let's do percentage metrics. Now remember earlier how I said that we wanted to grab like conversion rates and stuff like that and so I want to get two conversion rates specifically.
I want to get the number of meetings booked or the number of proposals sent, the number of meetings booked and then oh I just realized that the proposal sent went down to zero. I wonder why. Why did the proposal sent go down to zero? It 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, eh? Okay, that makes sense. Because the date range dimension field is set here on all of these metrics, basically whatever date is selected up here is going to be the date that's selected everywhere else. So 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 gonna give this a second. 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 data. You'll see that it's taken a second to update, but it actually just went through and updated. The revenue and the cash collected fields, to me, they aren't like dollar figures, which I'll add in a sec, but I'm just gonna do this percent metrics first.
Okay, so we have the absolute metrics, which is presumably gonna 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 successful meeting rate is. 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. I know how to set up the backend of all of this stuff, which is, I think, why my services are... 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 gonna 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 right-click, and then I say blend the 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 booked you can also click into this and then you'll see that there's actually formula that 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 meetings book and just divides the two so that's the that's the metric that I'm curious about the title is a little bit too long for me so I'm just gonna go back and I'll call this 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 metric that I'm interested in is the percentage deals closed to the number of proposals 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'd 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? 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 closed 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 that it take us to make that one how many meetings and take us to make that three so ten thousand divided by three means the value of every meeting to us at the time of this date range is three thousand three hundred thirty three dollars so we should actually be able to do that that would be pretty cool to do so I'm gonna just gonna select revenue and then meetings booked I'm gonna click blend data and we should just get I think we're gonna get that or maybe we'll get a percentage I don't know yeah we're gonna get a percentage so now we have revenue two meetings booked so I'm gonna go over here and then And then this isn't actually going to be a percent. It's going to be, actually, it's going to be 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. 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 meeting.
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. And then why don't we also get the value of a proposal too? 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 I'm going to create another one here and I'm going to scroll that down. Change it from a million percent to dollar 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, it 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 this title. Maybe that's why. Instead of deals closed to proposal sent, we'll say proposals. Proposal to meeting, we'll go deal per proposal.
And this is just the format that I'm doing with this colon in between the two operators. I just find this the simplest sort of to conceptualize. I'll leave that at that and then I'm just gonna change both of these formats to dollars just that they look pretty.
So go to 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. 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. I think that's it. Yeah, okay.
I'm just gonna 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 4 report template makes this just a lot easier.
I think that this already looks reasonably nice. I'm not much of like a dashboard designer. I prefer dark mode for everything, to be honest. I just copied the same dark mode template, but I think this actually looks pretty cool. in this moving forward.
I'm just going to call this sales dashboard. 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 progress is 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 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 to 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 produced this much in revenue. 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 gonna take this and I'm gonna visualize this data. So I'm gonna select all this then I'm gonna put this right over here.
Now you'll see right now it says too many rows. Do the number rows, the chart comprehensive. So I'm just going to create my own. So I'm going to click, actually, I'm going to copy the style. So let me just move this down here first.
If you go down and add a chart, then you'll see there are a bunch of options here for bar charts, time series charts. You can 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 time series chart.
So we're going to head over to You add a chart and go down a time series, give that a quick click, and then that will show us sort of like the rise and the fall in revenues and stuff like that over time, 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 because what's important is you want to grab like the days.
So you want to be able to see the X axis 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 is fine for our purposes. Okay, now obviously this isn't super informative right now because we only have one transaction with revenue. 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 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 it 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 line heights here. There you go. That looks good.
And then I'm going to go into our dashboard now and actually just add a bunch of like fake data just so we can see this 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 date time format.
properly if I do this. No, it's not rendering daytime 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, we'll do 25, let's do 26, 27, and then we'll do 29. So now we have a bunch of meetings booked. Let's add a bunch of proposals. Oops, we do not wanna change our header.
So I'm going to say that this one was on the 11th. This one was on the 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 because we're not really 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 and proposals because usually don't sign more deals than you do proposals. That should be good. We'll say we signed one on the 7th.
We signed one on the 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 4,000 with 2,000 cash collected and then 2,000. And then here might be 6,500. We collected, let's do like a staggered payment schedule for this one.
So 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 so that should be good. I'm going to call this client x. Yeah, Liquor Studio is pretty laggy, which is the unfortunate part, but client X dashboard.
We'll do sales dashboard. Then we'll do example. Guess it just deleted everything.
Client X sales dashboard example. There we go. Okay, great.
So that's what our dashboards look like right now. Now I'm going to go to the right hand side here, click refresh data. 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 booked that were updated. Proposals sent, deals closed, revenue, cash collected in the period. Proposal to meeting percentage, 50%.
Pretty good. Deal to proposal, 50%. Average meeting value, $1,708. Average proposal value, $3,417.
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 a factor of two.
It looks like the conversion rates are 50%, which are 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.
And then now that we have this trend, or now that we have this contracted revenue thing, we can, like we have a couple of different options here, the way that we're going to do it. 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 add it up over time so we could see the growth.
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. Odd. I think we can change this if we go down to style.
Oh, cumulative. There we go. Yeah, yeah.
And then we don't want running sum. My bad. So we want to go back here, change running sum from running sum just to none. And then now we'll get like a cumulative revenue growth graph over time. And then we also want what to do with zeros.
So missing data. So if there isn't any data on that, it just says it will go aligned to zero. We could linearly interpolate the data. That's not really 100% accurate.
We could We could smooth it, looks like, so we could make it a little bit prettier that way. 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's fine to me. I 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 stepped 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, but it's what it is.
You can add as much, you can mark this up or dress this up as much as you like. 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 You know, you can just spend more time figuring this 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 aka in make calm and then to use that to like pump this to some visualization if you know a designer and Their team wants to mock this up make this look a little better than they obviously could Yeah, and we didn't really have too much sales history here, so I think it starts at March 2nd. So let me just set this to my specific date range and this will probably be better.
I think we'll start at March 3rd and then we'll go until March 18th. Let's do that. If this date picker ever loads, that is the real question. Oh, that's pretty.
Okay, well I guess we're going to have to refresh that puppy. Now this is only really going to happen when you are recording 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 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, it grew another $6,500. And so this is sort of like a growth line. So I'm gonna go over here to this and I'll just write growth. Make it a little bit wider. Zoom out just a tad, just so you can see everything of interest.
And then we can play around with styles and stuff. There should be a way to edit the theme. If you go to theme in the layout. I do actually kind of like this theme.
It looks pretty clean to me but let's go around with constellation. Ooh that's spicy. Yeah, that's not bad at all.
Or maybe we could do Insight. 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.
Yeah, that's clean. I love it. A little hack, generally know because I've suffered through Looker Studio for long enough, is if you... Never mind, I take back my hack.
There's a way that you can paste, 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 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 bells and whistles sorts of fields. So I think revenue and cash collector are probably more important than the rest of these. So I'll probably switch them around so that revenue is the beginning.
And then the rest of these are sort of at the end. Yeah. I mean, that sounds pretty important to me, right?
Like I'm mostly interested in how much money my money made, 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 book proposal center deals closed.
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 bigger and a little bit taller here. And then call this like cash growth or something. Just so you can 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 you see how the pattern is a little bit different 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'll see that there are more transactions here, the line's a little bit smoother. There are probably some other insights that can be taken away from this as well. But I'm going to leave it there. 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 costly dashboard there's 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 three or four hours to compile books and stuff like that. So this sort of thing is outsized value. You know, know your worth and know the value that you bring to an agency.
It's one of the big problems that they struggle with 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. I'm 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, please subscribe and hit the...
notification button. Somebody told me that I should tell you that. This is my first video after my first day off in quite a while the other day, which was quite lovely. But I really missed recording this sort of thing.
It's also almost become sort of like a psychological palliative to me. So I'm going to be keeping this stuff up for the foreseeable future. Thanks so much for watching.
Have a great rest of the day.