Create a Calculated Field to Find the Duration of a 6sense Buying Stage in Salesforce

The duration of a 6sense buying stage field is a calculated field based on subtracting the current date of the buying stage with its previous stored date.

This explanation uses the Purchase stage as an example. Here’s a depiction of which days an account is in the Purchase stage:

The calculated field for days in Purchase is [3/17] – [3/ 1] = 17 days.

To create the calculated field that shows a 6sense buying stage duration, you create fields and workflow rules in Salesforce in these three broad steps:

1. Create fields

2. Create workflows

3. Set the value for the duration of days in a particular buying stage

Create Fields

1. Create a field that stores the first day an account is in a stage (day 1). Name it PurchaseStageDate.

2. Create a field that stores the last day an account is in a stage (day 17, in the above example). Name it PurchaseStageEndDate.

3. Create a field to store the days in a stage (in this case, Purchase). Name it Days in Purchase.

4. Create a field to store the prior buying stage to properly evaluate the Days in Purchase. Name this field PriorBuyingStage6Sense.

The four (4) fields that must be created in the SFDC Account table are:

Set Field Values with Salesforce Workflow Rules

To set the values in each of these fields, you must implement some Salesforce workflow rules.

In SFDC Classic view, select Setup from the top menu item and then navigate to the lefthand search box and type “workflow” in the search box. Select Workflow Rules listed under Workflow & Approvals in the left pane.

Create three workflow rules, referring to the image below:

Workflow 1: Update Purchase Stage Date

This workflow updates the field PurchaseStageDate when the Account buying stage = ‘Purchase’. We assume the SFDC APP is installed and the SFDC Account table already has the 6sense buying stage created for new updates. This field checks the 6sense buying stage when it changes to ‘Purchase’ and then sets the date to TODAY().

Rule

Field Update

Workflow 2: Store Previous Buying Stage

This rule stores the previous buying stage when the buying stage changes on the SFDC Account buying stage record. Its main purpose is to determine the last date for the Purchase buying stage.

Rule

Field Update

Workflow 3: Set Purchase Stage End Date

This workflow updates the PurchaseStageEndDate field. The rule evaluates if the buying stage changes from Purchase to another buying stage and records the last current date when the account is in Purchase.

Rule

Field Update

Set the formula to:

IF( ISBLANK(PurchaseStageEndDate__c) ,TODAY (), PurchaseStageEndDate__c )

Set Value for the Days in a Particular Buying Stage

Set the value for Days in Purchase (in this example). Go to the calculated field “Days in Purchase” and change the formula to:

IF( accountBuyingStage6sense__c = ‘Purchase’ , Today() – PurchaseStageDate__c + 1, PurchaseStageEndDate__c – PurchaseStageDate__c + 1)

Replace the Salesforce account 6sense buying stage to match the field name in your Salesforce account table.

To create calculated fields for all the buying stages, repeat this process for the Decision, Consideration, and Awareness buying stages by creating fields with the proper prefixes to denote these different buying stages.