Common Salesforce Reports in SQL

At Sisense, we depend on our sales team to bring in revenue from the leads our marketing team generates. Like many sales teams, ours uses Salesforce to manage the processes involved in making a sale.

In this blog post, we’ll go over how to calculate some standard Salesforce reports using SQL, and how you can join that data to customer data to give you a more complete view of your customers.

Common Salesforce Reports

Current Vs. Target

It’s very common for sales teams to look at reports in Salesforce that compare their current performance to their goals. In our example, we will look at monthly recurring revenue (MRR).

Using close date, amount, and stage from the opportunity table, we can quickly calculate our sales performance for the month, quarter, or year:

select sum(amount) 
from opportunities 
where 
  stage = 'Sold' and date_trunc('month',close_date) 
    = date_trunc('month',current_date)

To compare this to a target, we can union the monthly amount with the monthly target:

select 'MTD' as metric, sum(amount) as amount 
from opportunities 
where 
  stage = 'Sold' and date_trunc('month',close_date) 
    = date_trunc('month',current_date)
union all
select 'Target' as metric, 1000000 as amount

And we can plot the results to see that comparison like so:

Account Representative Stack Ranks

Another common Salesforce report is the stack rank of individual members of the sales team. This calculation is more advanced as we need to pull team member names from the users table and sales data from opportunities.

We will use a modified version of our query above to pull sales by rep:

select opportunity_owner, sum(amount) 
from opportunities 
where 
  stage = 'Sold' and date_trunc('month',close_date) 
    = date_trunc('month',current_date) 
group by opportunity_owner

Next we’ll join this to the users table to get our reps’ names:

select 
  users.first_name || ' ' || users.last_name as rep
  , sum(opportunities.amount) as revenue
from opportunities
join users on opportunities.opportunity_owner = users.user_id
where 
  stage = 'Sold' and date_trunc('month',close_date) 
    = date_trunc('month',current_date)
group by rep
order by revenue desc

And we can plot the results to see that comparison like so:

Over Time Metrics

Along with snapshot metrics such as year-to-date and rankings, over-time metrics are also critical.

Here we review our opportunities generated by each lead source over time:

select 
  date_trunc('month',created_date) as month
  , lead_source
  , count(1)
from opportunities
group by month, lead_source

Joining With Application Data

Once we have our salesforce data in a database we can create all of our key reports in our favorite reporting tool. We can also join salesforce data with our other data sources to get a fuller view of our leads, opportunities, and customers.

Tags: data team