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.