Snapshot, Trend, and Forecast Your Salesforce Pipeline


#1

Salesforce is a fantastic CRM tool for managing your prospects and understanding your sales cycles. However, a common issue that people run into is parsing out historical snapshots from Salesforce. For example, questions like ‘What was the value of my quarterly pipeline at the start of this month?’ and ‘How/why has it changed?’ are typically incredibly hard to answer.

Because Chartio uses the SQL that’s native to your underlying data sources, there’s no behind-the-curtain modeling that we do to organize this complex data. Rather, you can use the SQL you’re using already to parse out this information. I’ve shared below a basic template using Redshift as a datasource to get you started - if your Salesforce custom objects change you can just pop those into this query as well.

Because it’s fairly complex, I’ll break down the query into a few parts to help explain what each section is doing.

1. Duplicate the Opportunity History table, Create 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 opportunityhistory
   ),

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

Conclusion & Notes

Now that you’ve got all the pieces to the query, you can just write this into one of Chartio’s Custom Tables so that all your colleagues can use Interactive Mode’s drag and drop against all of your new snapshotted pipeline data.


A couple things to keep in mind with using this query as a template is that it written in Postgresql and using generic table / column names; you'll definitely want to update the table and column names to be specific to your dataset and if your database uses a different type of SQL, you'll want to revise the query accordingly.

Snapshot, Trend, and Forecast Your Salesforce Pipeline with Chartio