Forecast your Salesforce pipeline in Data Explorer

A common issue that people run into with Salesforce is parsing out historical snapshots from Salesforce; for example, people ask questions like “What was the value of my quarterly pipeline at the start of this month?” and “How and why has it changed?”

You can use SQL to find information about your underlying data source. Note: This example is a template written using PostgreSQL and using generic table and column names; update the table and column names to be specific to your dataset, and if your database uses a different type of SQL, revise the query accordingly.

  1. Duplicate the Opportunity History table and create a row of data for each change.

     with oppty_snapshot AS
    
     (SELECT opportunityhistory.*,
    
     COALESCE(LEAD(opportunityhistory.createddate,1) OVER(PARTITION BY opportunityid
    
     ORDER BY opportunityhistory.createddate), CURRENT_DATE) AS stage_end
    
     FROM public.opportunityhistory AS opportuntyhistory
    
     ),
    
  2. Generate a list of dates and business date buckets (week, quarter, etc.).

     dates AS
    
     (SELECT A.stage_date,
    
     CONCAT(CONCAT(TO_CHAR(A.stage_date, 'YYYY'), '-Q'), TO_CHAR(A.stage_date, 'Q')) AS snapshot_qtr,
    
     MIN(A.stage_date) OVER (PARTITION BY CONCAT(CONCAT(TO_CHAR(A.stage_date, 'YYYY'), '-Q'), TO_CHAR(A.stage_date, 'Q'))
    
     ORDER BY A.stage_date ASC ROWS UNBOUNDED PRECEDING) AS snapshot_qtr_start,
    
     MAX(A.stage_date) OVER (PARTITION BY CONCAT(CONCAT(TO_CHAR(A.stage_date, 'YYYY'), '-Q'), TO_CHAR(A.stage_date, 'Q'))
    
     ORDER BY A.stage_date DESC ROWS UNBOUNDED PRECEDING) AS snapshot_qtr_end,
    
     CASE
    
     WHEN A.stage_date = MIN(A.stage_date) OVER (PARTITION BY CONCAT(CONCAT(TO_CHAR(A.stage_date, 'YYYY'), '-Q'), TO_CHAR(A.stage_date, 'Q'))
    
     ORDER BY A.stage_date ASC ROWS UNBOUNDED PRECEDING) THEN 1
    
     ELSE 0
    
     END AS is_snapshot_qtr_start,
    
     CASE
    
     WHEN A.stage_date = MAX(A.stage_date) OVER (PARTITION BY CONCAT(CONCAT(TO_CHAR(A.stage_date, 'YYYY'), '-Q'), TO_CHAR(A.stage_date, 'Q'))
    
     ORDER BY A.stage_date DESC ROWS UNBOUNDED PRECEDING) THEN 1
    
     ELSE 0
    
     END AS is_snapshot_qtr_end,
    
     TO_CHAR(A.stage_date, 'Day') AS snapshot_day_of_week,
    
     ROW_NUMBER() OVER (PARTITION BY CONCAT(CONCAT(TO_CHAR(A.stage_date, 'YYYY'), '-Q'), TO_CHAR(A.stage_date, 'Q'))
    
     ORDER BY A.stage_date ASC) AS day_of_qtr
    
     FROM
    
     (SELECT (getdate()::date + 730 - row_number() over (
    
     ORDER BY TRUE))::date AS stage_date
    
     FROM public.opportunityhistory AS opportunity_history LIMIT 3000) AS A)
    
  3. Bring the two previous sections together.

     SELECT dates.stage_date AS snapshot_date,
    
     dates.snapshot_qtr,
    
     dates.snapshot_qtr_start,
    
     dates.snapshot_qtr_end,
    
     dates.is_snapshot_qtr_start,
    
     dates.is_snapshot_qtr_end,
    
     dates.snapshot_day_of_week,
    
     oppty_snapshot.\*
    
     FROM oppty_snapshot
    
     LEFT JOIN dates AS dates ON dates.stage_date >= oppty_snapshot.createddate
    
     AND dates.stage_date <= oppty_snapshot.stage_end
    
     WHERE dates.stage_date <= CURRENT_DATE