1

Salesforce – calculate close rates for qualified deals over time

Question

Calculate close rates for qualified deals over time

Sources

Salesforce (see full visual schema here

Results

Query

with
-- get opportunities fields
opportunities as (
select account_id, created_date, close_date, stage_name, infer_3_score_index_c infer
from salesforce.opportunities
 where stage_name not ilike 'Closed Lost - Qualification'
 and stage_name not ilike 'Nurture'
 and stage_name not ilike 'Open'
 and is_closed is true
 -- before this date the data was inconsistently entered
 and close_date > '2014-11-1'
),
-- count opportunities created per week
count_lost_opportunities as (
select date_trunc('month', close_date) date,
 count(distinct account_id)
from opportunities
 where stage_name not ilike 'Closed Won'
 and infer > 74
 group by 1
),
-- count won opportunities per week
count_won_opportunities as (
select date_trunc('month', close_date) date,
 count(distinct o.account_id)
from opportunities o
where o.stage_name = 'Closed Won'
 group by 1
),
-- Calculate the percent closed opps by date interval
closed_opps as (
select w.date as close_date,
 c.count + w.count as total,
 c.count as lost,
w.count as closed,
w.count/(w.count + c.count)::float as close_rate
from count_won_opportunities w
left join count_lost_opportunities c
on c.date = w.date
order by 1 desc
)
select close_date, sum(total) as total_deals, sum(lost) as lost_deals, sum(closed) as won_deals
from closed_opps
where close_date > '2014-11-01'
group by close_date
order by close_date desc;