Snapshot, trend, and forecast your Salesforce pipeline

Data Pipeline

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 PostrgreSQL 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
    

Related Data Pipeline Help Articles

See more