All right. So let's get started. Welcome, everyone. To today's webinar. We're gonna talk about more complex data metrics in monday.com than what most people are used to using in monday.com. Maybe this will be inspirational for you to try your hand on more advanced KPIs for evaluating your business. Maybe it will be that you're having a problem at the moment, showing a certain kind of data in monday.com, that you want help solving in a dashboard. So all of those are, possible reasons for you to be here. And feel free to use the chat to, already now, say what your hope and expectation would be for this webinar, because then I can just put that right into the agenda. I'm not that very fit on what I have planned. But nevertheless, I have plenty of agenda for today. So here on, my monday.com doc that you can see in front of you on the screen, plan to talk about hit rates of a metric for sales. I will talk about weighted average of and talk about it because, I have prepared an example that you could argue whether it's useful or not. Uh, so it's not to be taken by face value, but I think by talking about it, you can find a bit of, ideas on how you could use weighted averages on your own. And when they are useful and when they are just unnecessary. We will talk about error margin where we use a confidence interval, to give a better prognosis of possible future scenarios. And then lastly, we'll talk about net present value, which is one of those very economical terms that you find when you go to business school or whatever. But it is a useful value that if, of course, used most often in investment when doing investment decisions. But basically when you apply it to sales, which will be the case in this webinar, you can see that I have a CRM here on the left. Then it's actually quite interesting because every decision to go for a lead or an opportunity is also a investment of time. And of course, you want to maximize the potential payout of that investment. So that's why I also included it here for a new use case that you might not have thought of. Okay. So let's start by going through what I prepared for today. So we have a regular monday CRM here with an additional board that we will get into later. And we have something that I call the Advanced Sales Dashboard. So it's a little bit more advanced than the standard one that comes with the monday.com CRM. In this one, I have prepared a few graphs for example, the hit rate for sales rep and month. And then we also have the graph for that we will use later in the webinar. That are focused on predictors, on the error margin or the net present value or NPV for short. And then we also have weighted averages that we will go through later. Okay. So first of all, the hit rate if the percentage of deals won of all closed deals. So in this case. We have that, for sales people. One of them is me. And then we have Erica, Lucas, and Gustav. So what this tells us is that Gustaf, of all the deals that he has closed, that we have in our deals board. He has won. So he never lost the deal at all. And we can see that if we go to the deals board, we can go to the one and, group and we can see that actually, yes, there is one, won opportunity by Gustaf. Another. So he has more than one. Uh, you have three one opportunities and they're all in the one group. He doesn't have any down here, which Lucas me and Erica has. So that makes sense that he has a hit rate of one, which would translate to 100%. Uh, Lucas in turn, he has 66.67 percentage, um, or two thirds one. Uh, of course, that, if also possible, to double check, but instead of going into the board, I will have to show you the other way to visualize it through split mode in a graph. So in here we can see that, okay. We have all the won and lost deals. And, in order to see Lucas, we can just filter by him up here. And we have to have two won deals and one lost, so that's much quicker. In case you haven't discovered that feature yet, it's very useful. But anyway, so the hit rate is quite useful for evaluating a sales performance, right? But it could also be that maybe Gustaf is only going for the easy deals. Or maybe it's super experienced and very good at his job. We don't know that. And maybe Erica is going for these big fish, in the pond, but, she only hits a few of them. So even though she has a hit rate of, 42.9%, maybe the total value that she brings in is much higher than Gustaf. We don't know that. So usually you actually combine hit rates with some kind of deal worth. And we will talk about deal worth later because that is related to net present value. So you can basically, to, it is easier if you have a deal value or you have the margin where you have deal value minus the cost, or you have the net present value, or you could have a forecast value that is based on the probability. That is something that you could add to the board by clicking in that column in the CRM. So that could also be, a value of dealer. So it's up to you really to say, How deal worth is measured in your business, right. But I'm going to remove that one again. Because if you're not going to be used later. Anyway, um. What we saw in the dashboard was the total hit rate per person. but what if we want to see the hit rate per month? We want to see if the sales rep have a increasing or decreasing trend when it comes to the hit rate. We want to evaluate rather how they are improving rather than the actual hit rate today. And maybe they have so many lost deals back half a year ago when they were super new at the job, but now they're closing nearly 100% of the deals as won, maybe we want to then segment this data into different months in order to give a more, fair picture of that. So how do we do that? Um. In a monday.com widget which you might have already encountered. you can select. For example, x axis and y axis. So in this case, I have people, the owner of the deal. And for y axis I have whether it was a hit or miss. So if it's a hit then it gets the value one. If it's a mistake, guess the value zero. So it misses a loss and a hit. If, of course, I, win the deal. And then I have to take the average of that. So that's the simple way. Without any make, without any extra, you just get the a hit rate per person, no matter when they won or lost the deal. But if I want to add the third dimension of having it per dates, then. I need to change it to date, and I probably use the closed date for that. Right? Uh, I try and stack it by people, and I also want to use hit and miss. Perfect. I don't want to use that. Uh, actually, let's do a line graph instead. Uh, wait. Stat line. There we go. So dates, stack by owner and whether it's a hit or miss. So. Now all of a sudden we can see that, um. Okay, let's double check now in the split view mode. Yeah. We have, So I'm going to check my own deals here. All right. Yeah. That's interesting. Okay. Yeah. Because I had a 0.5 hit rate before, uh, and it seems like. All of those one deals they were in January. While all my attempts in both February and March have been, lost. So I didn't make any money back then. So. Okay. And then we have, uh, Erica that I mentioned earlier, she is much she has a much better hit rate for a month like that. And, yeah, you can see the other lines as well. So that gave you another, of a. Dimension today. So you have a third dimension. So earlier we looked at the hit rate no matter what time it was. and now we have the hit rate per month, and person. So we have two dimensions, both person and the time. Right. But now I also figure that I want to add a fourth dimension here. I want to, have individual sales goals for each uh, sales rep. And I don't want to have the benchmark lines and change them all the time, because I could set it 4.7 and have a red one. And I said, okay, this is, uh, my own benchmark line that I should have, and then we can add one for Lucas that he should, do 0.4, for example, that that's his goal. but the downside of that is that I have to go to the setting for the time to change it as the goal changes. and also it will not be per month. So we lost the time dimension here. So I either have to change it each month and if I want to evaluate January but I'm in March, then all of a sudden I have April's goal in the graph. But, that will not be proper to evaluate that last month goal at all. So let's not do that. Let's instead have a monday.com board for hit rate. So in this monday.com board, I have set one item per sales rep and month.  I also set a date of the month, which is just a random date in the month. It should belong to. In this case, it's the first of the month, because that always works. And I set the target, and then I want to see. So I want to go back and see. Okay, so let's check the hit rate for January, for example. So I add a new filter here. And I say that the close date. Has to be between. The first and the last. Of January. And then I can see that. Okay. only me, And Erica. Actually, won any deals. And we can say that Erica won a third of her deals and I won half of my deals. Good. let's not say that I'm good so I can put. Actually, hit rate would be 0.5 for me. Because I want half of them. And for Erica it would be 0.67. Of course I want this to be percentage and average. Oh, sorry. 67 and 50%. There we go. and it's not applicable for Lucas and for Gustav. So that's quite actually of a process. you that I went into a widget, which is since this is not a widget that we're going to use anyway, we're going to pull our data from the hit rate table. Then I just went into widget and filtered it for the month, and then I put down that, same number in here, which if. Not very optimal. I want this to appear automatically. So then I go to make in order to make that happen. so I can specify that I want to get all the items from a board. And. Okay. I'm just gonna see. All right, I'm just gonna fetch the API Code as well. Yeah. That's good for you to practice, though. So you go to the connections because they recently moved it. And in here you can copy it. Okay. Good. If hold it webinar for now. There we go. So, anyway I will just go to the deal board. I think that we have quite a few of them. Yeah. We did. So let's do it the other way where we fetch the. Awarded. Instead. So this is a trick. There we go. And then we get the actual board. So I will fetch all the items in a real board. Let's set the limit for 20,000 for now. I'm gonna say who is the owner of the deal. And if it's. which close date does it have and which Um. Stage is within so that we know whether it's won or lost or neither. Good. So when I do that, I can see that make will. And, for those of you familiar with make, you can see that you will get all the deals. So let's look at the bottom where we have the one and last ones, for example, we have one here. We have Erica as owner. The stage is lost and the close date is 21st of February. And now we want to automatically get the percentage that was won, by Erica in a specific month. So. What we or for all sales reps really. So, what we're going to do is to add a numeric aggregator. Which, Take the, The total number of. Deals. And put that into a variable. So we're going to call this total. Okay. We're going to give it those kind of total number of deals. And then we have going to put the result in now. Okay. So this will not be enough because right now it will just give me all the items in the board. Instead I need to segment it and group it by owner. Let's go. Actually, for the ID of the owner. let's also I'm going to show that later. And let's also say that it will be only those with a closed the eighth for last month. So we're gonna first say that it needs to be this month. Okay. Yeah. Okay, let's just format the date first into year and month. We start there. There we go. And now we also want to deduct one month from now in order to get last month. So let's say ad month on the null variable. And we want to add minus one month and then we close it. So we're going to deduct a month from today. format it. So it's only year. Dash month and do the same for the month. So that way we will get all the the total number of deals from last from last month. that were closed in last month. and then we get it segmented per person. So there will be one bundle here per person and it's total number of deals. So we want to put those into an array. And I usually do it the lazy way. So I put a numeric aggregator and I put the key column result. And then I separated by a comma. That way I get a long string and I can later easily split it, in here into a array. So I'm going to split it by the comma and put in the text. There we go. So that, we can run it once. To give you an idea what did we look like? So what happened here was that it found, in the filter. We saw that a lot of the deals didn't have a closed date for last month. Some of them did. Which nine of them passed through the filter. So this one counted, the number of those deals per person ID. So it's hard to say which person this corresponds to at this stage unless you go back into monday.com and open my profile. And then you have to change this ID in the URL to the one that you fetch, and then you can see that it belonged to Erica. Okay, good. So we know that, Erica she has had a total number of deals. And then we put that together into one quite ugly array. Actually, I want to flip those two. So I want it to be. Oh, wait. No key results. Sorry. Yeah. Key results. I'm not going to flip them. And then that will become an array of the ID of the person and then colon and then how many deals that they are closed, no matter which if they are won or lost. Good. Let me do the same again. So we have copy paste, you know, a regular control C, control V and then we derive this here and we get the same filter. Even though I'm gonna say that this is, this route is total of closed deals last month. And this is going to be total won deals last month. And we're going to add another condition that the deal stage has to be equal to. Let's go here. One. So I'm just going to request that. There we go. let's save it. And, So what that will give us is a Bunch of, Okay, let's just actually, we're not gonna make it into a array this time. So instead. Or are we? Okay, let's not make it into an array at this time. So let's run it again. And, we can see that the result here is that it was one, one deal for this ID, and that was four deals in total for that ID Now I also want to put that data automatically into my monday.com board. So. let's say that each month I duplicate and I do this manually because I'm the sales manager, so I have duplicates the month before I change it to March. This could, of course, be automated in make as well, but I'm not going to do that today, without going over this webinar. And then I change the target, so let's say, 40 for Lucas, because, he needs some slack. 55 for me, because I also need some slack 80. And then Gustaf we can raise him to 90. so we set the target, on the month before that way. And maybe I will change the color to. Which one am I gonna pick? Yeah. That's good. So this one, so that we can, see beforehand what's expected of me as a salesperson and what I need to work towards. Right. And then I want, on the 1st of March, I want this to run automatically in make to fill in the last month or actual hit rate. So I can evaluate also. So let's say that this one runs days of the month and it will not run on the first. And let's have it run on in the middle of the night. I'm gonna save that so that we can get the data fresh in the morning. Good. So the next step here is to basically find the corresponding yeah. We need to find a corresponding row for that ID, and I know that there are better ways to do this, and maybe that would be a future webinar, but I'm going to do it the lazy way for now for just proof of concept for you. I've got when I list what item each time. I'm going to take my webinar connection. I'm going to also do that for the hit rate board. And then because you can't use the search functionality in make yet to search for a user ID, you can only do it to search for the user name. So I could modify this whole scenario to go for the name instead, but that comes with some other difficulties. So I'm going to go with this, and I'm gonna find the person and the date of the month that corresponds. and also with a new GraphQL API, you can just do your own custom query, which is super good. That's what I would do if I had more time on this webinar that you just executed the GraphQL query in order to find the correct item. But anyway. For each of them, I'm going to do a list board item, and then I'm going to update the column values of that one item. so it's going to be the hit rate board. And it's gonna be the item ID. Let's map it from the previous bundle. And, I nearly forgot to fetch this variable. So we need to get that one. Okay, I'm going to put it here. Again, there are better ways to do this, but. Let's do it. Just make it work for now and then. It's quite easy to polish it. If you have more than a one hour webinar to do it. So what will happen is that we get this One, this array each time. And we can then input that in the actual hit rate column so we take basically the won deals. Divided by the total number of deals. Okay. And in here you will, then have the filter of this ID equal to the ID from the list item module. Let's run that once actually. Good so that we can get some metadata in here. And we want the date formatted to again equal the month. Actually, I have a copy pasted from here. There we go. Let's replace it with this date. Good. That's why it will only find the correct person and the correct month for that person. So you have to make sure that you have only one row per month, otherwise it will not work. Anyway, I'm going to leave it at that and just, let you do some homework, because what you want to do here is not to divide by the whole, array. What you want to do is to actually fetch only the one item in the array that corresponds to the item ID here, which you would do with the map functionality, which you will find here. There you go. So that one is super useful if you've done your make certification, you already know about it, so then you can. Just do it that way. possibly that you want to change this one to a array aggregator instead In order to do that. All right. So let's leave that for now and pretend that this is running once per month. We are filling in these actual heat rates. Good. so since we're running a little bit out of time, because I'm having such fun building stuff in make, then I'm gonna just put some values in here to give us an idea of what it will look like in our dashboard. So we now have both the goal and the target and the actual in another board. That sure it gets its data from the deals board. And what it does is basically getting a snapshot from here each month and putting it over here so we can compare the values. What we need is to change so that the graph on top goes on source of its data from this, board instead. So go to the settings, go to the board, and then we add a board for the widget and select Hit rate and click done. And it takes a little while. And we can now. So let's go back actually to the bar chart. We can now select to have hit rate in here. Good. And we can deselect the deals. There we go. Let's go for. Oh, sorry. We have to have a regular chart. Now we're going to go with, stacked line. Okay. Anyway, so let's go for date of the month, grouped by a month, stacked by person. And for y axis, we're going to have the actual hit rates perfect. Good. And then we can always. Ah, there we go. The date of month. There we go. So we have the actual. And so we are the average of the actual and the target. There we go. Here we go. So we had the target average hit rate of 66.25 in March so far at 56.75. So we're getting closer. But we still need some work on. really get to the target hit rate, and we can have an individual follow up by having a split mode like this where we talk to each person in turn. And we can also, of course, click on the have to see my hit rate my target or Erica's. Okay. All right. So that was a lot regarding that first bullet point. So I'm going to skip weighted averages and instead go to error margin. so what we do with error margin is that we, For example, when it comes to, the growth probability of a deal, the sales rep, the gut feeling is usually quite okay on the probability of winning the deal. So really it's a win probability because it closes any way. and if we have, for example, a value of $122,000. And we know that is a 23% chance of winning it. Then we have kind of a very rough estimate of that one deal. So if we go to the main table where I have a couple of more columns, we can see that, we actually could say that in worst case scenario, based on a probability, that would be 81,000 In average from that deal and 152 in best case scenario. But how could you have more money from that deal? so that doesn't make any sense unless you aggregate this data into a actual dashboard. So that's why I also hid them from here. Because they're not meant to be looked at individually. But you still need to have these calculations. In order to show the graph that I'm going to show you, or that your theme as well. So the upper limit, it's calculated. Basically it's take the deal value plus the deal value minus the win probability times the deal value. Or you could do it instead of D value for all that you can do the MPV where you can have the deal minus the cost, for example. And the same goes for a lower limit, except that you use a minus instead. That means that the more uncertain you are. Let's say that you have a low win probability. Then the lower limit goes further down and the upper limit goes further up. So we have more uncertainty. But if we are more certain, let's not look at the ones with 100. But this 195, then we are quite sure. Then we have a lower limit. That is quite this 122,000. So where the lower limit is quite close to 22,000 and also the upper limits. So when we look at the advanced sales dashboard, we can see that. By looking at the width of the span that we have here. We can see that the deals that we have for the deals that we have forecasted for January. They are quite uncertain. And it gets even worse in February, where it's very uncertain whether we will win it or not. But in March, we are very certain that about our deals, whether they will close or not. So then we can have a very short span. If and if we want to know the actual value, then we look at the middle line. So we are hoping for to close $646,000 in January, but it's, a little bit uncertain in February. We have a high number, but it's more uncertain, so it might close further down than January and January might close, maybe even a little bit further up. And then we have a look at March, and we're pretty sure that it's going to land around $314,000. So it's quite nice to have these, prognosis charts with an error margin in order to handle that kind of data, and it's commonly used as well in prognosis when it comes to economic conditions and sales, and not as much in sales that we have here. Good. I'm just gonna check that I haven't missed anything on that. Yeah. Oh yeah, that is quite good. Also to know that if you know the relative certainty and uncertainty, then you can also make better decisions because you know that, okay, in March I'm quite sure what we're gonna....of the deals that we're going to close there or the money that we're going to, get. So then I can make some decisions then with that as a benchmark, while for January, where it was quite uncertain, maybe I want to be a bit conservative when it comes to new investments. All right. I'm still going to skip weighted average for now. And the reason for that is that, it is actually what I went through with hit rate. The thing with weighted averages is or the thing with hit rate is that it is a weighted average if you want it to be, because you could say that it's not going to be one, and it's not going to be the number of deals divided by the total number of deals. So not won number of deals divided by total number of deals. Instead, maybe I want to take the total amount from my won deals divided by the total amount of all the deals that were closed, both won and lost. All of us had to have a weighted average. And that's basically what the weighted average is about, that, and that is also more difficult to show in a monday.com dashboard, which is why you want to create a make scenario to give you that data and give you maybe two hit rates, one based on number one based on value. And then you can also have total, won, total lost, for example, so that you can see the breakdown of the whole structure. So basically that's what I wanted to show you that was of value. And then I wanted to talk a little bit about another data metric that you could use, but I am not certain whether it would be useful or not. It's something that I just thought of that I think that, okay, the use case for this is so slim. So that's why I'm not going to bring it up today. We can leave it We can leave it since we're running out of time. All right. Let's move on to the net present value. And that's quite interesting calculation that is, as I said, quite common in finance, that what you do. So the net present value is the difference between the cash inflow that you're going to have in the future. So let's say that you know that you're going to get the payment in August. But today it's March. So you basically have around five months in between. For those five months, you could have had the money that you're going to get in August. You could have had that already in your bank account and get the interest rate of maybe 1%. Or you could have placed it in government bonds and get an interest rate of, well, nowadays maybe 6%, but let's say 2%. So you're actually losing money by not getting that money in August now. Because it could have been used in your business. And another way to think about it actually, is to take that interest rate and think of what could that money that you don't have now, but you know you're going to get in August. What could you have invested in today in your business to get a certain uh, percentage of profit on that, until August. So that could also be the interest rates to calculate with. So that's, so let's move on to the formula for that. So basically the net present value is the cash flow. So that's the money in August that I talked about, divided by one plus the interest rate. So that's to make it a positive number to the power of T. And T is the time period, in this case, it was month. And of course you take all this minus the initial investment. So that's why, when we go back to the webinar board. The deal board. And go to the main table. That's why we have an internal cost here. so if I change that one to a lower amount, you can see that the net present value increased quite a lot. And if I bring it quite close to, let's say, 121,000 instead of 122,000, you can see that the net present value of that $1,000 profit is actually worth $575 today. because I assume that the actually, this is the invoice deadline date, because I assume that the deadline for the payment, that's when I got the money, which would be the last of May. So. And that's quite interesting also. because I built this in mind with us thinking of a scenario where we have our CRM or any production board or a finance board, but we actually have it tied to our accounting system. So that's why I could put the invoice deadline date here, because this is, well, when it comes to the new deals, then it will be an estimate. But for the ones down here, then we can know the deadline date for the invoice, because we maybe we actually sent it. Anyway. And the interest rate. So you can see here that, 1.03. So I have a 3% interest rate and I put it plus one. So, 1.03 and I have it to the power of, I should say I had a 3% annual interest rate, and I put it to the power of. And just changed the because I have a hidden column called invoice length, which is the number of days that put that prioritized to the year and minus the internal cost to get the net present value. So it's when you look at it, it's the exact same formula just modified for my purpose. Good. And this is, of course, for short term and for longer term projects and multiple cash flows. Then you need to use the expanded formula. Not gonna, get into that today. What I'm going to talk a little bit more about is by the way, Investopedia is of course a great source of that kind of calculations, but you could just Google it and you find it on Wikipedia or any other site as well. So you don't have to go back to the recording of this webinar to find it. but, what is interesting here are the variables in it. Right? So if we have a higher interest rate, then the money would be worth less. So I had a 3% annual interest rate, let's say that, it would be a 3% monthly interest rate. Well, maybe it would be a 20%. Maybe I will calculate with a 20% interest rate, because I believe that an investment in my business at this point in time would generate a kind of cash flow in the future. I mean, that is, of course, a very positive way of calculating your money, but still possible. Right? So the higher interest rates that you calculate with, the less worth the money will be that you get, sorry, the higher the interest rate, the less worth of the money that you will get at a future point in time. -Okay. -Same goes with the amount of. In my case, it was days or month that I calculate with, but really the time it takes. So I've had March and August, so that gives me a certain sum of money, let's say NPV of $500. But if I calculate it, because I get the money in December, maybe it's only $200 because I have increased the time because the the person was going to pay me. They don't have the money in August, so they have to move it to December. And if I don't have a penalty on that payments, for example, if I don't give them a fee, then, and I just accept that, then I get less money because of the net present value of moving the date to the future. Okay. I hope it's not too academic for you. I mean, the overall. And so the summary of it is that, if you don't get your money now, it will be less worth because you get it in the future. so we want to be paid sooner, if possible. We want deals that have a low cost, especially in initial cost. Because the same goes for whatever you need to spend to get the money, you want to spend that money as far into the future as you can, and you want to have as little of that spend in the beginning. so, you need to -also have that in mind. -Good. So I think that was it for today. We are quite close to the finish now. I urge you to do the make certification to understand the map function a bit, because then you can finish the scenario, especially if you switch this one to an array aggregator that you can find in here. I also urge you to maybe look at previous, make webinars to get an introduction to make, what it can do, and how you can use it because we have both webinars on our YouTube channel, and we have them also on Omnitas .com. Super useful tool where you can connect to anything that has an API. so let me know. I think you have my contact details on the Omnitas home page, if you have any follow up questions, but otherwise, I thank you very much for listening to me today, talking about a few advanced metrics when it comes to evaluating numbers in and how you can visualize them in monday.com. If you have an idea for another advanced metric, don't hesitate to send it to me and I can try and help you out. Maybe it will become a new webinar. Okay. So thank you very much for today and see you around. Bye.