Note: This content is automatically generated by the ETL tool. Last build time is 2024-03-19 13:44:37. Please contact MWS Insights Data Infrastructure for the query.

OpportunityVelocityCalculated

Data source: MWSData

set notruncation;
set norequesttimeout;
set maxmemoryconsumptionperiterator=68719476736;
let now=format_datetime(now(),"yyyy-MM-dd");
let opps = database('MWSData').vwFactOpportunityPipeline()
| where strcmp(FiscalYear,"FY22")>=0
| distinct CRMOpportunityID;
database('MWSData').Opportunity
| where CRMOpportunityID in (opps)
| where ETLDate >= startofday(OpportunityCreatedDate)
| project CRMOpportunityID, ETLDate, MSXStatus, SalesStage, OpportunityCreatedDate, OpportunityCloseDate
| sort by CRMOpportunityID asc, ETLDate asc
| extend StageChangeStartEndTag = iif(prev(CRMOpportunityID)!=CRMOpportunityID or prev(SalesStage)!=SalesStage or (next(SalesStage)!=SalesStage or next(CRMOpportunityID)!=CRMOpportunityID),1,0)
| extend StageChangeFlag = iif(prev(CRMOpportunityID)!=CRMOpportunityID,1,iif(prev(SalesStage)!=SalesStage,1,0))
| extend StageChangeIndex = row_cumsum(StageChangeFlag,prev(CRMOpportunityID)!=CRMOpportunityID)
| extend
StageStartDay = ETLDate-1d,
StageEndDay = iif(MSXStatus == "Open", ETLDate-1d, iif(OpportunityCloseDate>datetime(1900-01-01), min_of(OpportunityCloseDate, ETLDate-1d), ETLDate-1d))
| where StageChangeStartEndTag == 1
| summarize StageStartDay = min(StageStartDay), StageEndDay = max(StageEndDay) by CRMOpportunityID, SalesStage, StageChangeIndex, OpportunityCreatedDate
| extend DaysInStage = iif(StageChangeIndex == 1, (StageEndDay-OpportunityCreatedDate)/1d, (StageEndDay-StageStartDay)/1d)
| extend DaysInStage = iif(DaysInStage>0,DaysInStage,0.0)
| extend SalesStage = case(
SalesStage contains "20%", "Stage20",
SalesStage contains "40%", "Stage40",
SalesStage contains "60%", "Stage60",
SalesStage contains "80%", "Stage80",
"N/A")
| where SalesStage != "N/A"
| summarize DaysInStage = sum(DaysInStage) by CRMOpportunityID, SalesStage
| evaluate pivot(SalesStage, sum(DaysInStage))
| join kind=inner(
database('MWSData').vwOpportunity()
| project CRMOpportunityID, OpportunityCreatedDate, OpportunityCloseDate, LatestETLDate = ETLDate-1d, MSXStatus
) on CRMOpportunityID
| project-away CRMOpportunityID1
| extend DaysSinceCreatedCappedAtClosed = iif(MSXStatus != "Open" and OpportunityCloseDate > datetime(1900-01-01), (OpportunityCloseDate-OpportunityCreatedDate)/1d, (LatestETLDate-OpportunityCreatedDate)/1d)
| extend DaysSinceCreatedCappedAtClosed = iif(DaysSinceCreatedCappedAtClosed>0,DaysSinceCreatedCappedAtClosed,0.0)
| project-away LatestETLDate
| extend ETLDate=datetime(now);