Help with finding first instance of an Opportunity (CRM) and subsequent instances (as it shifts)

[Anonymous]

I have a date table.   And a CRM table (coming out of Salesforce).    We have a bare bones version of SF so we do not have the luxory of having snapshot type of data built into Salesforce. 

So in plain English, here is what I am trying to do.   

As an Opportunity shifts from one stage or another, or even stays in the same stage but gets moved further out in time, I need to be able to trace the original "close" date, to any subsequent close dates.   I do have historical monthly "snapshots" that I combine each month from SF exports that automatically come via email.   So in theory

Excel Snapshot file date  (comes at end of each month)       Opportunity Name    Create Date   Estimated Close Date   

Ultimately we are trying to find out the days between the create and estimated close date, but also the days elapsed between each change.  

Excel Snapshot file date  (comes at end of each month)       Opportunity Name    Create Date     Estimated Close Date   

Historical File

Dec       (assume this is the first time it shows up)                      Big Opp 1                Dec 9

Jan                                                                                               Big Opp 1                                           March  3

Feb                                                                                               Big Opp 1                                           March 23  

Mar                                                                                              Big Opp 1                                           April 6

So, using the example above, our salesperson estimated in the Jan Forecast (which comes on the last day of December)  that the opportunity would close on March 3.   When the next Feb forecast came out (on Jan 31), the date had shifted to a close date of March 23.    Then in the March forecast (sent on last day of Feb), the estimated close date shifted again to April 6.  

As you can see, the forecast file is sent on the last day of each month but is for the following months.

We are trying to determine the amount of days between each shift, starting from the original instance of the opportunity.   And the days between the estimated close dates on each snapshot file.  Any ideas to get us started in the right direction are appreciated.   

[Anonymous]

@lbendlin    Well...you may have just blown my mind.   We at our company are all new to SF and the various tables that are in the schema.   Are you saying that there is perhaps some sort of time stamp, for each particular opportunity (and the stages it went through)?    I do see it in our dataflows, and have pulled that into Power Bi just now.   Just not sure what I'm looking for?

[lbendlin]

This table is the best thing since sliced bread. It allows you to reconstruct the status of any opportunity at any point in time, without the need for expensive snapshots. This is what event based reporting is all about.

It requires a little bit of work outside of SFDC (we pull the data into a SQL server table and run a small scalar function over it) but it gives you maximum flexibility and happy users. Can show the code if interested.

[Anonymous]

@lbendlin    Very interesting, and yes I've played around a bit with that table since you helped me discover it.   We bring it into PBi from a dataflow but have never used it.  I'm curious if you have any DAX code or maybe M code that you may have used as an example for how to count the days between Opportunities and their stages?   

[lbendlin]

We implemented it as a scalar-valued function in SQL, after pulling the table incrementally from SFDC into SQL.

CREATE FUNCTION [SFDC].[OpportunityStatus] 
(
	@ID as varchar(20) -- opportunity id. Case sensitive, use CS_AS collation for storage
	,@TimeStamp as datetime  -- point in time for which the status is requested
	,@CreatedDate as datetime -- opportunity created date (in case the request is for a date where the opportunity did not exist)
	,@Field as varchar(50) -- attribute that the status is requested for ( for example "Forecast Category" )
	,@CurrentValue as varchar(255) -- current value of the requested attribute (in case there was no change)
)
RETURNS varchar(255)
AS
BEGIN
	DECLARE @TimeStampValue as varchar(255)  -- computed value of the attribute at the requested time
	SELECT @TimeStampValue=
	                -- return NULL when the opportunity was not alive at the time
	                case when @TimeStamp < @CreatedDate then NULL 
	                -- any change data before the timestamp? [Edit Date] desc or [History ID] ?
						else  isnull((SELECT TOP 1 [New Value] FROM [sfdc].[OpportunityFieldChanges]
					WHERE [Opportunity Id] =  AND [Field   Event] =  AND [Edit Date] <= @TimeStamp
						order by [Edit Date] desc, [History ID] COLLATE SQL_Latin1_General_CP437_BIN ) 
					-- any change data after the timestamp?
					   ,isnull((SELECT TOP 1 [Old Value] FROM [sfdc].[OpportunityFieldChanges]
					WHERE [Opportunity Id] =  AND [Field   Event] =  AND [Edit Date] > @TimeStamp
						order by  [Edit Date],[History ID] COLLATE SQL_Latin1_General_CP437_BIN desc) 
					-- use the current data from SFDC if no changes have been recorded	
						,@CurrentValue)) 
					end
	RETURN @TimeStampValue
END

Not sure if trying this in DAX or M is a good idea - performance might suffer. But the basic idea would be the same.

Once that is in place you can do amazing analytics - conversion probabilities, time spent per sales stage,  "nervousness"  of close date changes etc etc.

[lbendlin]

Are you saying your SFDC instance doesn't have Opportunity Field History enabled?  That would be most unfortunate.