Lead to opportunity conversion for operations

How to create outlook signature

in this video I'm going to show you how to create a stage history report that's actually going to show you the conversion rates of different stages so a lot of different businesses want to run this report so they can understand how deals are moving through their Pipeline and we've got those opportunity stages already set up but many people want to know are the opportunities moving from Discovery into presentation into proposal and at what percentages and oftentimes opportunities can jump around and it can be helpful for management to see you know maybe these opportunities actually skipping the presentation stage all together maybe they're going from Discovery right to proposal and those insights can help refine an opportunity process as well as give a little insight into how to manage the sales team better so to create this report go into Salesforce reports click new report and then look for the report type called opportunity history so this is a unique report type that a lot of people miss this is different than a standard opportunity report because we're actually looking at changes to that deal or multiple deals over time so every time that you make a substantive change to an opportunity on the stage the close date or the amount or probability that triggers Salesforce to save a new record related to that deal and normally you don't see these records they're just in the background but they're accessible to you through this particular report type and with this report type we can see those changes and we can track them and get at the data that we're interested in this case we want to see all of our stage updates to understand how opportunities are moving from one stage to another so once we've got this report type going to filters and then set up your basic filters so in my case I'm going to choose all opportunities and for the close date I'm not concerned with this year last year so I'm just going to look at at all close dates the next thing to do here is to notice that we've got a two-stage column and a from stage column so I think it's helpful to start by grouping on the from stage go ahead and select that and group and then group afterwards on the two stage so now we're getting a little bit of a preview of the data here there's a couple things to note right at the top there's a from stage that's blank but there's still data in the two stage so these records represent New Opportunities an opportunity that was created immediately in prospecting or immediately in value proposition the other thing you might notice is if we look at this prospecting stage you can see that it moved from prospecting to prospecting so it didn't actually change the stage at all so perhaps the change on that opportunity was on the amount or on the close date but something else so those are records that we're going to want to filter out so I'd recommend let's filter out any of the new opportunity records as well as any of the records that are just showing us a non-stage change so the from Filter is pretty easy go to add filters search for the from stage choose no selection and change the operator to not equal to there we go that got rid of our first issue our next issue is from prospecting to prospecting so the way that we're going to filter this out is actually by using a field that Salesforce already gave us so if you look in this Fields drawer here on the left you can see that there's a checkbox field that exists on this opportunity history record called stage change and that's a checkbox so if that opportunity history record represents a stage change then it's checked if it's not a stage change then it's not checked so we can add that as a filter too so we want stage change to be true hit apply and you'll see now that those records that were from prospecting to prospecting have now been eliminated now we're getting somewhere so the next thing we want to take a look at is understand what's the percentage of different stage changes so if I'm looking at opportunities that were in the prospecting stage what percentage go to needs analysis versus closed lost or closed one so to get that number we need to create a formula so to create that formula on the left hand side here click create formula and then let's give it a name so I'm just going to call this percentage the formula output type should be percent and I'm going to change decimal points to zero I don't need that much detail but you can certainly leave the decimal points there if you want so actually let me exit out of this real quick so if you look down here we've got proposal price quote we've got four records that are listed here three of them move from proposal price quote into negotiation and review one of them moved into closed lost so what we want to do is take this number 3 and divide it by our total number of proposal price quote opportunity changes which is four you know that's going to give us 75 percent now obviously we don't want to do that manually so let's put that in a formula so the first option over here on the left says record count go ahead and select that and insert it and then add the division symbol so if we're looking at a specific group of opportunities that move to a specific stage we can count that that's our row count but now we want to get the parent groups total count so we get all of those opportunities that started in the same stage to do that go to functions and scroll all the way to the bottom and you'll see we've got two special functions down here parent group value and previous group value well in this case we want the parent group because we're looking for a percentage of the whole so hit insert for parent group value here's our function and it's asking for two arguments you know two different inputs we need to give it for our function to work one is what's the summary field what are we trying to add up here and the other is at what grouping level are we interested in so the summary field that we want is actually the same as our numerator here it's row count and the grouping level is going to be our from stage so if I start typing from it's going to show up here for me and there's that from stage field and the API name for this is from underscore opportunity underscore stage underscore name this looks good to me and then come to display and you can choose where you want this formula to show up now we don't want this on all summary levels I don't think it really only makes sense at our lowest summary level when we're looking at actually changes from one stage to another so select specific groups and choose the two stage as the summary level where we want this to show up go back to General and hit validate okay our formula is valid so then hit apply let's take a look at it we come back down to the data that we're previewing before we can see this new summary over here on the right hand side where it says percentage and it looks like it's calculating correctly we can see that there were three opportunities that move from proposal into negotiation and that would be 75 because that's three out of the four total then go ahead and hit save and run let's take a look at all the data so in this view it might be a little bit hard to see and compare so you can always hit the toggle at the bottom to collapse the detail rows now we've got a good looking report that's showing us from prospecting the needs analysis you know 20 of the opportunity updates fall into that category sixty percent of opportunities are going from prospecting to closed one so that's pretty interesting people seem to just be skipping the other stages and 20 of them went to close lost and you can see similar data here across all those other stage changes and if you want you can take an extra step to add a chart to visualize this information but for this particular type of report I think it's a little bit better just to look at the percentages here gives you a good idea of how opportunities are moving from one stage to another and you can better understand if there's any gaps in your sales process and review that as needed I hope you found this video helpful if you did please hit like And subscribe and if you like this video and are interested in learning more about reporting take a look at our reporting crash course on academy.r.otive.io thanks for watching