Salesforce Opportunities Part 3 – Opportunity Reporting

In this installment on our series on Salesforce Opportunities, Shell dives into Opportunity reporting – a common task for Salesforce Administrators and folks in Sales Operations. First he looks at some of the unique fields that we have available with Opportunity Reporting such as Stage Duration and Age. Then Shell gives some tips about how you can report on the average number of days between each Stage, calculate the average number of days to close an Opportunity, as well as how to derive your win rate. Next he covers some of the “out-of-box” reports available to you as a System Administrator and how to leverage the Role Hierarchy along with the “Running User” on Dashboards to efficiently write reports on sales teams without having to hardcode the names of specific Users. Lastly Shell discusses some helpful reports to identify Opportunities that need to be updated (i.e. neglected Opportunities that require maintenance).

View this video on YouTube: https://www.youtube.com/watch?v=0PMNXQdQTIo

Other blog posts on this topic:

Transcript of video:

Hello everyone, and welcome to another segment of ShellBlack Whiteboard, where we help you get the most out of Salesforce platform. I’m your host, Shell Black, President and founder of ShellBlack.com and Salesforce MVP, and this segment today is about Opportunity reporting. As a Systems Administrator or maybe you work in sales operations, you’re going to do a lot of reporting around Opportunities for reports and dashboards. So I want to dedicate a segment on what you can do “out-of-the-box” and maybe some things that you didn’t know you could do with Opportunity reporting. So with that, I’m going to slide over here, I’m going to talk about a couple of fields that are available on Opportunity reports that are not on the page layout. These are just available on reporting, that’s why we got these green asterisk, these two are available on Opportunity reports.

Age is just simply if you have an open Opportunity, Salesforce can look at when an Opportunity was created and today’s date, it will express a number field . . . number of days an Opportunity has been open. If you flip an Opportunity to closed, whether closed loss or closed won, you’re going to get a zero on that. Stage Duration; another number of days field. It is the number of days an Opportunity has been in the current Stage. So if you go from Stage one, and you’ve been in that Stage for 47 days and you move it to Stage 2, this number is going to reset back to zero. So let’s say that you have the instance where you want to track the duration of every Stage in your sales process, you can do that but you’re going to have to build some custom fields to pull that off and leverage something like Workflow.

So to do that, you’re going to have to have a date field that corresponds with every Stage and you have to use Workflow to populate that day. So you’re just going to have a workflow with a criteria that basically says if Stage equals this, as soon as a record hits that criteria of a new Stage, you’re going to use a field update on your Workflow action to populate today’s date; today just being today, with a parenthesis like that. And you’re going to do that every time the Stage changes, you’re going to populate all those date fields. Then you just use another custom formula field to say do the math between this date and this date, the date that this Opportunity went to this Stage and the day this Opportunity went to this Stage, will give you the number of days.

And now you can start tracking it down to the Stage level. Another custom field . . . it’s not actually a custom field, this is actually a custom report summary formula. So a lot of clients want to know, “What’s my win rate, of all the Opportunities that we got out there, we’d really like to know as a company,” or maybe a win rate by a salesperson. So when you’re in the report wizard, you have to actually create these custom summary formulas, you create one with the output of the percentage and the way you do that is; there’s a field called won. Just all your closed won Opportunities and you summarize that to get a count of won Opportunities, and you divide that by the row count, or basically all your Opportunities.

So number of won Opportunities divided by all Opportunities gives you your win rate, and you can show that as a percentage summarized group by that win rate on your reports. Something else I like to do with clients and they seem to like is; I want to know how many days on average or how many days does it take for us to win a deal. And it’s nice if you want to do an average, you can take all the days . . . for all your Opportunities, how many days it takes to close, and get your average either for the company or again, by a salesperson. It’s a custom field, it’s a formula, it’s a little long but it’s not that bad. I’m going to walk you through it real quick.

It’s an IF statement, so “IF this is true, then do this, otherwise, comma, do that.” So IF is won (the Opportunity is won), the true statement would be, I want to take the Close Date and subtract the Created Date, so the date we won the deal minus the date we created the Opportunity, and give me a number. Now with Created Date, that’s actually a date-time syntax, you have to actually wrap the Created Date in what’s called a date value to take it from a date-time down to a date. Then you can do a date minus a date. So hopefully, not too bad, if an Opportunity is won, do the math between the two dates, give me a number.

If it’s not won, the else statement of that is a zero. So if it’s won, it’s going to express as a zero. All right, so let’s switch to this side of the board and talk about a couple of other things. There is a folder that just comes with Salesforce that’s just called “Opportunity Reports.” If you’ve never poked around there, there’s quite a few report that come “out-of-the-box.” I encourage you to run them all and see what they do. If you like some of them, you can always do “Save As,” modify the criteria, and throw it into a custom report folder. So the ones you should look at; stuck Opportunities in Aging reports. Stage duration, what we were just referring to just a second ago, pipeline trend, take a look at those I think you’re like them.

If you’re trying to get used to Opportunity reporting, go into this report folder, steal shamelessly, Salesforce has got a lot of pre-canned out-of-the-box reports you can run. OK, another thing with Opportunity reporting; there is a filter that you will see on Opportunity reports called the Show filter and there’s some picklist values that you can choose from. And if you’re using the Role Hierarchy, this could have a dramatic difference on the output of your reports. So you’ll see a filter on the Show filter for values, you can pick MY, MY TEAM, or ALL. So let’s see how this applies if we’re looking at our Role Hierarchy.

So in this scenario, you got a V.P. of sales, I’ve got four regional managers and below those regional managers, I have groups of salespeople. If I have filter my report on MY, and I am the V.P. of Sales and I run the report, I’m only going to see Opportunities that I as the V.P. of Sales are the Opportunity owner. If that person runs that MY report, same thing, only Opportunities owned by that regional manager can be seen, not including this person, not including their subordinates. If you change that report to MY TEAM, the data is going to change dramatically because now you’re grabbing all the records that I own, but also all the records owned by my subordinates below me in my Hierarchy.

So if this regional manager runs a filter that has the MY TEAM filter, they’re going to see Opportunities that they own plus all the records owned by the folks below them. Same thing if V.P. of Sales runs a MY TEAM report, they’re going to see all the Opportunities they own plus all the records of everybody below them in the Role Hierarchy. If a salesperson runs a MY TEAM report, it basically just gives them their Opportunities. Of course, if you don’t want to worry about the Role Hierarchy, then you just want to grab ALL Opportunities in the database. You can set it to ALL, so if you’re doing large analysis; companywide, just set it to ALL.

So why would you have filters like this? When you’re doing dashboards, a lot of times you’ll have a dashboard per salesperson. So you can write one set of reports, all filtered on my team. Then you can set something called the “Running User” on the dashboard and depending on which person that is, whether it’s a salesperson, the regional manager, this regional manager or that regional manager, you don’t have to hard code the names of all those salespeople. You just let Salesforce use the Role Hierarchy to understand which reports they want to get.

So I can write one set of reports for my team, change the Running User from this regional manager to that regional manager, and that’s going to grab their set of Opportunity reports. Pretty cool. OK, so I’m going to wrap up Opportunity reporting with a little section called maintenance and my thought here is we want to look for Opportunities in the database that hasn’t been touched in a while, that needs some “loving.” So a couple of fields that we can leverage; one is Last Activities. So Last Activity meaning when was the last time we have logged an activity, whether it’s a task, a meeting, log or call.

The other one is Last Modified, meaning when was the last time we’ve updated a field in the Opportunity, so the Stage, Close Date, Amount, maybe the Probability. Between those, you can start running reports and start filtering it, as Salesforce to bring you back . . . “Show me all last Opportunities with the Last Modified that is greater than 90 days.” Or a Last Activity within a certain amount of time, you can then stack-rank it and see your oldest to newest and do it by salesperson if you want, and let them know, “Hey, these are the ones you need to go back and work.”

Another one I like to do is run a report and set the report with a criteria that says where the Close Date is less than “Today.” And what I’m trying to look for is a Close Date of an open Opportunity that’s in the past. So that tells me, really, two things; either we need to push the Close Date forward — if it’s still open, because we haven’t won it — or we need to close it out. We need to either win the deal or lose the deal. So it’s really kind of, “Tell me all the ones that just needs to go back and be updated.” This is a good report to run at the end of the line.

OK, so that wraps up our segment on Opportunity reporting, we would love to hear your feedback and you can reach us a couple of different ways. You can hit me at twitter @Shell_Black or you can email me at whiteboard@shellblack.com. Love to hear your comments, thanks for tuning in and we’ll talk to you soon.