
<p>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.</p>



<p>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.</p>



<h2 class="wp-block-heading"><strong>Common Salesforce Reports</strong></h2>



<h3 class="wp-block-heading"><strong>Current Vs. Target</strong></h3>



<p>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).</p>



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



<pre class="wp-block-code"><code>select sum(amount) 
from opportunities 
where 
  stage = 'Sold' and date_trunc('month',close_date) 
    = date_trunc('month',current_date)</code></pre>



<p>To compare this to a target, we can union the monthly amount with the monthly target:</p>



<pre class="wp-block-code"><code>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</code></pre>



<p>And we can plot the results to see that comparison like so:</p>



<figure class="wp-block-image fancybox"><img decoding="async" src="https://cdn.sisense.com/wp-content/uploads/bar.png" alt="MTD revenue target chart" class="wp-image-76504"/></figure>



<h2 class="wp-block-heading"><strong>&nbsp;Account Representative Stack Ranks</strong></h2>



<p>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.</p>



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



<pre class="wp-block-code"><code>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</code></pre>



<p>Next we’ll join this to the users table to get our reps’ names:</p>



<pre class="wp-block-code"><code>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</code></pre>



<p>And we can plot the results to see that comparison like so:</p>



<figure class="wp-block-image fancybox"><img decoding="async" src="https://cdn.sisense.com/wp-content/uploads/table5.png" alt="Revenue target chart" class="wp-image-76499"/></figure>



<h2 class="wp-block-heading"><strong>Over Time Metrics</strong></h2>



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



<p>Here we review our opportunities generated by each lead source over time:</p>



<pre class="wp-block-code"><code>select 
  date_trunc('month',created_date) as month
  , lead_source
  , count(1)
from opportunities
group by month, lead_source</code></pre>



<h2 class="wp-block-heading"><strong>Joining With Application Data</strong></h2>



<p>Once we have our salesforce data in a database we can create all of our key reports in our <a href="https://www.sisense.com/reporting/">favorite reporting tool</a>. We can also join salesforce data with our other data sources to get a fuller view of our leads, opportunities, and customers.</p>


Common Salesforce Reports in SQL

curve-image-unique-image-unique