11 Salesforce KPIs for your Sales Dashboard (incl. SQL code)

Eleni Markou

As the saying goes “Theory is splendid but until put into practice, it is valueless”. In our previous post 20 Sales KPIs for awesome sales teams we discussed extensively how valuable can KPIs be in  monitoring your sales team performance. From decision making regarding strategic planning issues to employees management, sales key performance indicators can directly affect the complexity of your sales process and the cost of customer acquisition thus affecting the company’s ability to gain profit and, in the case of startups, probably attract investors.

Much ink was also spilled on the different categories these KPIs may belong to: Outbound Reach, Pipeline Metrics, Conversion Rates, Sales Productivity and Revenue Regain. In this post we are going to put all this theory into practice. We are going to use data from Salesforce CRM in order to construct appropriate queries for these metrics and visualize their results. After all, what every manager wants is a dashboard with all these indicators so that he can monitor the performance of his team in the blink of an eye.

Outbound Reach

When the leads generated by inbound sales are not enough, your sales team may turn to outbound prospecting. This approach is about deliberately contacting your best potential customers even in case when they are not aware of your company and its products. Usually this contact attempt is made via email or phone calls and aims primarily in starting a relation with the prospective customer.

In order to increase your chances of luring a potential prospect you need to provide him some real value from the very first email or phone call. But since you do not know a priori what exactly your target audience needs at the very moment you need to experiment with different approaches before identifying what best works for your case. During this attempt you need data and performance indicators. Many carefully selected performance indicators that are going to provide you useful insights.

Emails sent: It represents the absolute number of emails sent during the outbound lead generation.

select left(date_trunc('month', startdate)::text, 7) , sum(numbersent::float)
from "sf1-campaign"
group by date_trunc('month', startdate)
order by date_trunc('month', startdate)

Outbound calls: It represents the absolute number of calls made during the outbound lead generation.

select count(*)
from "sf1-openactivity"
where CallType = 'NumberOfResponses'

emails and calls by month

Number of Opens per Campaign: The email open counter measures the total number of people who open an email from an outbound approach.

SELECT count(emailstatus.TaskID),
       emailmessage.relatedToID
FROM "sf1-emailstatus" AS emailstatus
LEFT JOIN "sf1-emailmessage" AS emailmessage ON emailstatus.TaskID = emailmessage.activityID
WHERE FirstOenDate IS NOT NULL
GROUP BY emailmessage.relatedToID

Open rate per campaign: The email open rate measures the percentage of people who open an email from an outbound approach.

SELECT tmp.relatedToID,
       100 * tmp.cnt/numbersent::float
FROM
  (SELECT emailmessage.relatedToID,
          count(emailstatus.TaskID) AS cnt
   FROM "sf1-emailstatus" AS emailstatus
   LEFT JOIN "sf1-emailmessage" AS emailmessage ON emailstatus.TaskID = emailmessage.activityID
   WHERE FirstOenDate IS NOT NULL
   GROUP BY emailmessage.relatedToID)tmp
LEFT JOIN "sf1-campaign" AS campaign ON tmp.relatedToID = campaign.id

Number of responses received: The number of receivers who replied out of the total number of send emails.

SELECT id,
       NumberOfResponses as "actual responses",
       expectedresponse::float as "expected responses"
FROM "sf1-campaign"

 

Opens and open rate by campaign

Pipeline Metrics & Conversion Rates

As discussed in the previous post too, the sales pipeline is nothing more that a systematic and visual approach of keeping track of your sales progress. It consists of all the consecutive stages a potential customer passes through until he finally becomes a paying customer.

In the simplest form it consists of three stages: Lead, Qualified Lead or Opportunity and Won Opportunity. The velocity of each stage is indicative benchmark of the results your sales teams currently achieves and their future trends.

Furthermore, by making comparisons with results achieved  during past months a manager can quite easily spot and understand both negative and positive signs resulting in the evaluation of the applied strategies and refinement of them if needed.

Total leads created by month: The absolute number of leads generated each month. A lead is generated when an attempt to contact him is made through email or phone. By monitoring this chart every day a manager can evaluate if his team is getting close to the monthly goals being set or if they are falling behind and react appropriately.  He can also compare month by month performance and catch the overall trends.

select left(date_trunc('month', startdate)::text, 7) , sum(numberofleads::float)
from "sf1-campaign"
group by date_trunc('month', startdate)
order by date_trunc('month', startdate)

Total opportunities opened by month: The absolute number of leads that turned into opportunities each month. A lead turns into an opportunity when it has been qualified through email or phone and has met the sales team qualification criteria. Since only a small fraction of them are going to finally convert, the largest the number of the generated opportunities, the better it is for your company.

select left(date_trunc('month', startdate)::text, 7) , sum(numberofopportunities::float)
from "sf1-campaign"
group by date_trunc('month', startdate)
order by date_trunc('month', startdate)

total opportunities openedTotal Opportunities Won by Month: Reflects the number of opportunities that turned into paying customers each month. The reason why this indicators is especially important is because through its monitoring you can identify at which point customers tend to drop off more frequently from the pipeline and thus work with your team towards preventing it.

select left(date_trunc('month', startdate)::text, 7) , sum(numberofwonopportunities::float)
from "sf1-campaign"
group by date_trunc('month', startdate)
order by date_trunc('month', startdate)

Lead to Opportunity conversion rate: Reflects the percentage of leads that turned into opportunities each month. Keep the sales process moving when you qualify a lead and then convert it to a contact.

select left(date_trunc('month', startdate)::text, 7) ,  100 * sum(numberofopportunities::float)/sum(numberofleads::float)
from "sf1-campaign"
group by date_trunc('month', startdate)
order by date_trunc('month', startdate)

Opportunity to Won opportunity conversion rate: Reflects the percentage of opportunities that turned into paying customers each month.

select left(date_trunc('month', startdate)::text, 7) ,  100 * sum(numberofwonopportunities::float)
/sum(numberofopportunities::float)
from "sf1-campaign"
group by date_trunc('month', startdate)
order by date_trunc('month', startdate)

oportunities and won opportunities

Average Contract Value: It represents the average value of each sale. By effectively quantifying this value the sales team can get a grasp about how valuable can a potential opportunity be.

SELECT avg(expectedrevenue::float/expectedresponse::float)
FROM "sf1-campaign"
WHERE expectedresponse::float!=0

To take away

Hope we have already convinced you that adopting a data-driven approach in the way your sales team operates is a “must” in today’s competitive business world. In order to do so you can start by monitoring important KPIs that can help you draw useful conclusion regarding the sales approach that works best for the market in which you operate. Of course there is nothing like “one size fits all” and so the metrics presented in the post are just recommendations that may only partially apply to your business case since no one knows your potential customers better than you.

Help your sales and executive team to take ownership of the insights that live in your Salesforce CRM to transform sales.

Blendo is the easiest way to automate powerful data integrations.

Try Blendo free for 14 days. No credit card required.