CRM Analytics: Aging between Status

Requirement: to get aging across the record status using object history data. Status is enabled for tracking. In this example, I'll use the following Lead statuses: Unqualified, MAL, MQL, SAL, and SQL. The lead status is not always moving linearly, but can move forward and backward, for example, SAL can move to Unqualified.

In this blog, I am going to get the aging from MQL to SQL only; the same logic can be used to find the aging for other statuses as well.

Solution: using CRM Analytics dataflow. ComputeRelative will play a major role in the solution; however, the ComputeRelative field output is only Numeric or Text, so we will use "sec_epoch" of the created date, which is automatically converted to a number by CRM Analytics.

The idea here is to use ComputeRelative to create multiple helper fields to get the aging for each lead.

Dataflow logic:

1. Get LeadHistory object

2. Filter the data retrieved

'Field' == "Status"

3. ComputeRelative #1: get the MQL_Date_Enter 

case when current(NewValue) == "MQL" then current(CreatedDate_sec_epoch) end

4. ComputeRelative #2: get the Last_MQL_Date

case when current(MQL_Date_Enter) > 0 then current(MQL_Date_Enter) else previous(Last_MQL_Date) end

5.ComputeRelative #3: get the delta between MQL and SQL in seconds

case when current(NewValue) == "SQL" and current(Last_MQL_Date) > 0 and current(CreatedDate_sec_epoch) > current(Last_MQL_Date) then current(CreatedDate_sec_epoch) - current(Last_MQL_Date) end

6. ComputeExpression 

Use this node to convert the result from seconds to days.

If a lead never touches MQL, which means from MAL to SAL, then SQL, it would be excluded from this aging.

Notes:

• You need to use multiple ComputeRelative nodes because if you set the 2nd field to read the 1st field, the system will error because it cannot read the 1st field. Unless the 2nd field does not depend on the 1st field. This is different from computeExpression, where the 2nd field can read the result from the 1st field.
• The _sec_epoch field will be auto-calculated by CRM Analytics; however, if the date field is a result from a computeExpression node, you need to have another computeExpression note to read the date in _sec_epoch value.