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