Description: This query shows the conversion count and rate of lead-to-opportunity conversion and closed opportunities per active sales rep derived from Salesforce data.
Requirements: Collect the `User`, `Opportunity`, and `Lead` objects with the Panoply Salesforce data source
Usage: This query can be displayed in a tabular form to display the count and rate per active sales rep
Modifications: The table in the `FROM` might need to be changed based on Schema and Destination settings in the data source. The Date Range Filter using the `createddate` in the `WHERE` clause can be changed.
WITH active_user AS (
id, email, firstname || ' ' || lastname AS rep
salesforce_user.IsActive = 1
converted_and_won AS (
COUNT(*) AS "total_leads",
COUNT(CASE WHEN sl.convertedopportunityid IS NOT NULL THEN 1 END) AS "converted_leads",
COUNT(CASE WHEN so.stagename = 'Closed Won' THEN 1 END) AS "won_leads",
ROUND(converted_leads * 100.0 / total_leads, 2) AS "conversion_rate",
ROUND(won_leads * 100.0 / total_leads, 2) AS "win_rate"
ON sl.convertedopportunityid = so.id
date_trunc('quarter', sl."createddate") = date_trunc('quarter', CURRENT_DATE)
au.rep AS "sales_rep",
ON srl.ownerid = au.id
Query Results Dictionary
sales_rep: Opportunity Owner (Sales Rep)
email: Sales Rep Email
total_leads: Total Leads Owned by the Sales Rep
converted_leads: Total Leads Converted to Opportunity by the Sales Rep
won_leads: Total Opportunities moved to the Closed/Won stage
conversion_rate: Rate of conversion from Lead to Opportunity
win_rate: Rate of opportunities being closed/won
Please sign in to leave a comment.