By clicking “Accept All Cookies”, you agree to the storing of cookies on your device to enhance site navigation, analyze site usage, and assist in our marketing efforts. View our Privacy Policy for more information.
Revenue Operations
Flash Icon Decorative

Creating Sales Insights Without a Budget

Scribbles 2

Providing value in revenue operations is easier when you have the ideal technology stack. But the to-do list will always grow. (Job security, right?)

So, what should you do when you’re boot-strapped and looking for ways to build social currency with your sales managers?

There are ways to combine different reports into a scoreboard that (if you use it regularly) will help you identify who needs coaching, who doesn’t have their target audience dialed in, and who is checking out and at risk of attrition.

The good news is that you only need Google Sheets or Excel, access to download CRM reports, and a sales manager willing to give you context to make reading those numbers more meaningful to everyone involved.

The Metrics

There are leading and lagging indicators important to every sales organization, and these should be readily available in your CRM (if not, you have your next project!):

  • Initial Forecast (Snapshot Day 1 of the Quarter - or at the end of the day after the manager has made them clean out the deal pushes that aren’t real 🤐)
  • Bookings
  • Quota
  • Meetings Held
  • Opportunities Created
  • New Pipeline Generated
  • Number of opportunities forecasted
  • Number of opportunities Closed Won
  • Number of opportunities closing in quarter Closed Lost

You’ve probably noticed that you can’t just pull a single report to get to this data. I never said this would be easy the first time you created the report. But if you are familiar with SUMIF formulas (if not, go here to learn about them in Excel and here to learn about them in Google Sheets) and COUNTIF formulas (if not, go here to learn about them in Excel and here to learn about them in Google Sheets), you should be able to use a workbook that you can update simply by pasting the raw report data over your historical data. Just don’t forget to reference columns instead of specific cell ranges to prevent new rows from being ignored.

Report Criteria for SFDC

Hubspot will be very similar, but they use a different taxonomy. They also automatically capture data that most Salesforce admins need to manually configure (like stage benchmark dates, values, etc.).

Initial Forecast & Quota

This should be available in the forecast report if your team uses forecasts or has a tool overlay that populates forecast data. Most sales managers have learned that forecasting in Excel isn’t scalable, but if you still work with that sales manager, you’ll need to get the forecast numbers from them. If this is a Salesforce report, you’ll be able to get your hands on the list of opportunities (with unique IDs) forecasted, which will be really handy later.


This is an Opportunity report type report with filters for Closed Won = True and Close Date equalsTHIS FISCAL QUARTER. You’ll need to determine whether you’re pulling the Amount field, ARR, or whichever custom field your organization uses to determine in-quarter attainment. This report will also be used to count the number of opportunities that were closed by the sales rep.

Opportunities Created

You’ll need to use your judgment when you pull this report. It will be an Opportunity report type report with filters for Created Date equals THIS FISCAL QUARTER or Qualified Date equals THIS FISCAL QUARTER. If your sales reps are the only team members who create opportunities, you’ll use the created date. If your sales reps are partially dependent on an inside sales team, you’ll need to use whichever date stamp you have set up to indicate a transfer of ownership to the sales rep.

And, once again, if you don’t have that benchmark, you have your next big project 🙂

A Note on Meetings

Every organization reports on things differently, and every sales manager has a different attitude about what should and shouldn’t be required from the sales team. If you don’t have your sales team’s calendars integrated with your CRM, do it as soon as possible. Passive logging is the best way to collect information from your sales reps and not incite a rant from the sales reps (or management) about their hatred for “administrative” work.

I also realize that the Event object in Salesforce cannot indicate whether a meeting is held or if the attendee blew off the sales rep. That’s OK! As British statistician George Box said, “Some models are wrong, but some are useful.” We’re looking at how many meetings are set and gauging that against how the rest of the sales team trends for the same metric throughout the quarter.

Calculated Fields In Your Spreadsheet

After you SUMIF and COUNTIF the values mentioned above, you’ll be able to calculate the following:

  • Forecast Integrity: (Amount forecasted at month beginning - Actual bookings)/Amount forecasted at month beginning
  • Quota Attainment: Bookings / Quota
  • Close Win Rate: Number of Won Opportunities / Number of Opportunities Set to Close in Quarter on Day 1
  • Push %: Number of opportunities set to close in quarter on Day 1 where IsClosed is FALSE at the end of the quarter.

Notable Mentions

I firmly believe in calculating velocity and conversion rates - ideally by stage. But in my opinion, these can be monthly or quarterly analyses if you don’t have the tooling in place to make these calculations available in real-time. These numbers are most valuable when looking at several months (if not years) worth of data to understand trends.

The Logic & Scoring

Since this is your workbook, you get to decide how complicated or easy you make scoring. Adding conditional formatting to color code the top and bottom quartile is very useful because you can see how all sales reps perform by data type. Because I’m a visual person, it made it easier to figure out if I should raise a red flag or keep an eye on the rep in future quarters to ensure a data point was just an anomaly.

Over time, you’ll notice patterns linked to attrition, low quota attainment, inexperience on the rep's part, or poor forecasting habits. Poor forecasting habits are something to point out to your sales manager, but they need to be handled differently depending on whether the sales rep is a consistent performer or an inexperienced sales rep. 

At past organizations, I noticed a link between pipeline generation, opportunity creation, and meetings held. If two or three values were in the bottom quartile, I knew the sales rep was probably interviewing for a new job. Getting more context from the sales leader before blurting out someone is at risk of leaving the company is extremely important. They may be on leave or planned a lengthy vacation. These may not apply to other organizations, particularly if your sales leader doesn’t encourage CRM adoption or calendar integration.

The Meeting With a Sales Manager

If you try out this report without being asked for it, you may get an odd reaction from your sales manager. I've worked with leaders who were suspicious of anyone paying too much attention to individual performance – even though I was in sales operations. Even if you have a great relationship with your sales leader(s), it's essential to approach this topic carefully.

I would pitch my first meeting at the beginning of the next quarter (after the board meeting or executive review), and I would position it as an opportunity for you both to learn from each other. Don't pitch it as a scorecard or Deadpool (as one sales manager jokingly put it after figuring out how accurate the predictions were).

Why focus on learning? Have you ever had someone ask you to "just" update a picklist or rearrange account assignments before next week at 5 PM on a Friday? Tap into that emotion, hold it, and consider how you would feel if you were a sales manager and someone came to you with a sales rep scorecard you didn't ask for. The feeling would be very similar!

Even at a director level or above, I preferred to approach this as a learning opportunity for both of us. I knew I didn't have the context about the behavior and mindset of the people on their team. I also knew I spent too much time in the data to be able to ignore meaningful patterns.

The language you choose to use during the meeting also matters. Phrase your observations with a question to get additional context.

For example, saying, "I notice the Southeast is consistently lagging in attainment. Do you think we should look into their territories, or is there something else going on?" is much less offensive than "Well. The Southeast dang near hit a goose egg again."

Another example is if I point out, "It looks like Karen is extremely behind on pipeline compared to normal quarters. Did she have vacation or leave?" I'll learn more than if I said, "Wow, Karen isn't pulling her weight this quarter," only to learn she's on medical leave (yikes).

Once you establish this is both a learning exercise and tool to help you catch coachable behavior, your quarterly meetings should run smoothly – and it truly is an excellent opportunity to learn what's happening beyond the data.

For those of you asking why this isn’t a CRM dashboard…

Some tools can help you combine these stats into a single pane and implement scoring models. Chances are high that your CRM doesn't have this ability out of the box. Plus, the ability to have something locked down so only you and the sales manager can view it builds trust. This report is not a tool to share with Finance, the CEO, or any other leaders.

Looking for more great content? Check out our blog and join the community.

Related posts

Join the Co-op!

scribbles 7 birds
Tail Spin Animation