Salesforce Configure Price Quote

Introduction

With the Salesforce Configure Price Quote app template you can load raw input data from Salesforce CPQ for the Lead-to-Order process, extracted to either Snowflake or SQl Server using CData Sync. The raw input data is transformed through a series of transformations that take place via dbt (data build tool) and produce the required input data used in Process Mining process apps.

Lead-to-Order process

The Lead-to-Order process is an end to end process which starts with a potential customer's intent to buy a product, up to the fulfillment of this sale. See the illustration below.

Lead Qualification

Once a lead is generated, the lead will go through a qualification process. The objective of this process is to determine whether the lead is most likely to make an actual purchase. Once the lead is qualified this will turn into an actual opportunity for a sale. In Salesforce, this is tracked via the Lead object.

Opportunity Management

As a lead qualifies into an opportunity, the sales team will work on the opportunity together with the potential customer, in order to identify the need and suggest an appropriate product or service which will meet this need. In case the opportunity is won, this will generate one or more orders in order to fulfill the won opportunity. In Salesforce, this is tracked via the Opportunity object, and the Quote object.

Quote Management Once an opportunity moves forward with a customer, products and services will be offered through quotes. In Salesforce CPQ the Quote object contains information about the products and services a customer wants to buy. You can create as many quotes as you want on a given opportunity. However many quotes your opportunity contains, only one can be designated as primary.

Order Management

Within Order management, the order lifecycle is maintained, including order capture, and fulfillment. In Salesforce, the Order object is used to maintain this process. One quote may have multiple orders if the configuration is set in CPQ package.

Contract Management

Contracts are used when orders included at least one subscription product under a contract-based renewal model. Salesforce CPQ creates a contract record for them.

Optionally, this may then extend and generate an invoice and receivables, in what is called the Lead-to-Cash process.

Entity relationship diagram

The following shows all objects that were used for the process. However not all of them would be converted as entities as they were merely used for connecting the entities that actually had activities tracked in the process.

Tags

Tag Description
Amended opportunity Once a contract is amended a new opportunity is created to keep track of that new piece in the contract. These opportunities are automatically created.
Approval process started multiple times Multiple approvals may exist for one entity, however if the same approval process is triggered multiple times, there might be inefficiencies to review. Applies for all entities (Lead, Opportunity, Quote, Order, Contract).
Multiple Owner Changes Applies for all entities (Lead, Opportunity, Quote, Order, Contract), reflects that the owner of that entity has been changed multiple times and that may be justified (vacations, escalations) or may require attention.
Opportunity amount reduced Opportunity Amount was changed to a value less than the original value. Represents an opportunity which is losing value.
Opportunity close date postponed An opportunity is getting delayed according to its original close date, which means the opportunity might require attention.
Primary quote changed multiple times The primary quote in an opportunity is the one that can turn into an order. There might be inefficiencies to review if these changes happen constantly.

Due Dates

Due date Description
Original Opportunity Close Date Identifies original expected close date for the opportunity and verifies against the actual close date.

Salesforce Sales Cloud configuration

Source system

Salesforce Sales Cloud with Salesforce CPQ package. The CPQ package must be installed in the Salesforce Org.

Depending on which fields have field history tracked, different change events will be recorded. Field history tracking can be configured from each object within Salesforce setup. Recommended minimal setup is having the following object and fields tracked:

By default, Field History is disabled per object. In order to setup Field History Tracking for an object, the following Salesforce manual contains a step by step instruction: Salesforce - Track Field History for Standard Objects. Note: if a field is activated for tracking, the values will be tracked from that moment onwards. Any changes before that point in time will not be saved. As of February 2022, Salesforce retains field history data for up to 18 months through the org and up to 24 months via the API, and Field history tracking data doesn't count towards Salesforce org's data storage limits.

In case the history needs to be stored for a longer period of time, Field Audit Trail must be activated. This allows copying of history data into a separate big data object and allows storage for up to 10 years.

Environmental settings

System specific settings

The Salesforce Configure Price Quote app template works on the process starting on the marketing campaign generation, up to the generation of customer orders, if the order includes subscription products the process adds contract to track the subscriptions.

This includes processing of the following Salesforce transactional objects:

The following additional objects which are used to add additional attribute information into the model:

In case they are used, the following tables add Currency conversion to the model:

*CurrencyType, DatedExchangeRate objects are by default disabled in Salesforce orgs.

Configuring CData Sync for Salesforce Sales Cloud CPQ

Below is a description on how to use CData Sync to set up a source connection and load data into a Process Mining Salesforce CPQ Lead-to-Order process app.

In general, you should follow the steps as described in Loading data using CData Sync (Snowflake) or Loading data using CData Sync (SQL Server) to set up data loading using CData Sync. Since specific settings are required when using Salesforce CPQ, pay attention to the steps described below.

Setting up the source connection

Creating the extraction job

Define the following settings in the Replicate Options section in the Advanced tab in the Job Settings panel.

Setting up environment variables in the extraction job

Important: Make sure you edit the Pre-job Event.

CData Sync allows the use of environment variables in order to drive specific extraction logic in each query.

Variable Description Comment
start_extraction_date Defines first date for which data will be extracted. Mandatory
end_extraction_date Last date for which data will be extracted.

In order to setup the environment variables:

Step Action
1 Access the job created in the previous step.
2 Click on the Events tab.
3 Add the following lines to the Pre-Job Event script. 
<!-- Modify environment variables here. -->
<!-- Variable start_extraction_date must be populated.--> 
<api:set attr="out.env:start_extraction_date"  value="2022-01-01" />
<!-- In case a specific end date is needed, replace the value string with the required date in yyyy-MM-dd format. -->
<!-- i.e api:set attr="out.env:end_extraction_date"  value="2022-02-01" -->
<api:set attr="out.env:end_extraction_date"  value="[null | now() | todate('yyyy-MM-dd')]" />
<api:push item="out" />

Important: Do not modify the api:info details that are shown by default.

In order to modify the environment variables, modify the values within the Events tab. By default, end_extraction_date will default to today's date. start_extraction_date must always be populated.

Table Replication

Once the job is correctly setup, click on Add Custom Query under the Tables tab and paste the following queries (each query needs to maintain the semicolon at the end). Make sure you save all changes.

Default Extraction

The following script is prepared for scenarios where Currency Conversions and Person Accounts are not used and are disabled. In case any of these are used, please add the scripts in the Alternative Scenarios section.

REPLICATE [Account] SELECT [Id], [AccountSource], [BillingCountry], [BillingState], [CreatedById], FORMAT([CreatedDate], 'yyyy-MM-dd hh:mm:ss') as [CreatedDate], [Industry], [Name], [OwnerId], [Ownership], [ParentId], [Rating], [Type] FROM [Account];
REPLICATE [Campaign] SELECT [Id], [CreatedById], FORMAT([CreatedDate], 'yyyy-MM-dd hh:mm:ss') as [CreatedDate], [Name], [OwnerId], [ParentId], [Status], [Type] FROM [Campaign] WHERE ([CreatedDate] >= '{env:start_extraction_date}') AND ([CreatedDate] <= '{env:end_extraction_date}');
REPLICATE [CampaignMember] SELECT [Id], [CampaignId], [CreatedById], FORMAT([CreatedDate], 'yyyy-MM-dd hh:mm:ss') as [CreatedDate], [LeadId], [Status] FROM [CampaignMember] WHERE ([CreatedDate] >= '{env:start_extraction_date}') AND ([CreatedDate] <= '{env:end_extraction_date}');
REPLICATE [Lead] SELECT [Id], [ConvertedOpportunityId], [CreatedById], FORMAT([CreatedDate], 'yyyy-MM-dd hh:mm:ss') as [CreatedDate], [Name], [OwnerId] FROM [Lead] WHERE ([CreatedDate] >= '{env:start_extraction_date}') AND ([CreatedDate] <= '{env:end_extraction_date}');
REPLICATE [Opportunity] SELECT [Id], [AccountId], [Amount], FORMAT([CloseDate], 'yyyy-MM-dd') as [CloseDate], [CreatedById], FORMAT([CreatedDate], 'yyyy-MM-dd hh:mm:ss') as [CreatedDate], [ForecastCategory], [IsClosed], [IsWon], FORMAT([LastStageChangeDate], 'yyyy-MM-dd hh:mm:ss') as [LastStageChangeDate], [LeadSource], [Name], [OwnerId], [Probability], [StageName], [Type], [SBQQ__AmendedContract__c] FROM [Opportunity] WHERE ([CreatedDate] >= '{env:start_extraction_date}') AND ([CreatedDate] <= '{env:end_extraction_date}');
REPLICATE [RecordType] SELECT [Id], [Name] FROM [RecordType];
REPLICATE [User] SELECT [Id], [Department], [Name], [UserType] FROM [User];
REPLICATE [ProcessDefinition] SELECT [Id], [Name], [TableEnumOrId], [Type] FROM [ProcessDefinition] WHERE [Type] = 'Approval';
REPLICATE [ProcessInstance] SELECT [Id], [TargetObjectId], [CreatedById], FORMAT([CreatedDate], 'yyyy-MM-dd hh:mm:ss') as [CreatedDate], [ProcessDefinitionId], [Status] FROM [ProcessInstance] WHERE ([CreatedDate] >= '{env:start_extraction_date}') AND ([CreatedDate] <= '{env:end_extraction_date}');
REPLICATE [ProcessInstanceStep] SELECT [Id], [Comments], [CreatedById], FORMAT([CreatedDate], 'yyyy-MM-dd hh:mm:ss') as [CreatedDate], [ProcessInstanceId], [StepStatus] FROM [ProcessInstanceStep] WHERE ([CreatedDate] >= '{env:start_extraction_date}') AND ([CreatedDate] <= '{env:end_extraction_date}');
REPLICATE [SBQQ__Quote__c] SELECT [Id], [CreatedById], FORMAT([CreatedDate], 'yyyy-MM-dd hh:mm:ss') as [CreatedDate], [Name], [SBQQ__Opportunity2__c] FROM [SBQQ__Quote__c] WHERE ([CreatedDate] >= '{env:start_extraction_date}') AND ([CreatedDate] <= '{env:end_extraction_date}');
REPLICATE [Order] SELECT [Id], [CreatedById], FORMAT([CreatedDate], 'yyyy-MM-dd hh:mm:ss') as [CreatedDate], [Name], [OpportunityId], [SBQQ__Quote__c] FROM [Order] WHERE ([CreatedDate] >= '{env:start_extraction_date}') AND ([CreatedDate] <= '{env:end_extraction_date}');
REPLICATE [OrderItem] SELECT [OrderId], [SBQQ__Contract__c] FROM [OrderItem] WHERE ([CreatedDate] >= '{env:start_extraction_date}') AND ([CreatedDate] <= '{env:end_extraction_date}');
REPLICATE [Contract] SELECT [Id], FORMAT([CreatedDate], 'yyyy-MM-dd hh:mm:ss') as [CreatedDate], [ContractNumber], [CreatedById], [OwnerId] FROM [Contract];
REPLICATE [LeadHistory] WITH ReplicateStartDate = '{env:start_extraction_date}', replicateenddate = '{env:end_extraction_date}' SELECT [Id], [CreatedById], FORMAT([CreatedDate], 'yyyy-MM-dd hh:mm:ss') as [CreatedDate], [Field], [LeadId], [NewValue], [OldValue], [DataType] FROM [LeadHistory] WHERE ((((([Field] = 'Status') OR ([Field] = 'Rating')) OR ([Field] = 'Owner')) AND ([DataType] != 'EntityId')));
REPLICATE [OpportunityFieldHistory] WITH ReplicateStartDate = '{env:start_extraction_date}', replicateenddate = '{env:end_extraction_date}' SELECT [Id], [CreatedById], FORMAT([CreatedDate], 'yyyy-MM-dd hh:mm:ss') as [CreatedDate], [Field], [NewValue], [OldValue], [OpportunityId], [DataType] FROM [OpportunityFieldHistory] WHERE (((((([Field] = 'Amount') OR ([Field] = 'StageName')) OR ([Field] = 'Owner')) OR ([Field] = 'CloseDate')) AND ([DataType] != 'EntityId')));
REPLICATE [SBQQ__Quote__History] WITH ReplicateStartDate = '{env:start_extraction_date}', replicateenddate = '{env:end_extraction_date}' SELECT [Id], [ParentId], [CreatedById], FORMAT([CreatedDate], 'yyyy-MM-dd hh:mm:ss') as [CreatedDate], [Field], [NewValue], [OldValue], [DataType] FROM [SBQQ__Quote__History] WHERE ((((([Field] = 'SBQQ__Status__c') OR ([Field] = 'SBQQ__ExpirationDate__c')) OR ([Field] = 'SBQQ__Primary__c')) OR ([Field] = 'SBQQ__SubscriptionTerm__c')) OR ([Field] = 'SBQQ__SalesRep__c')) AND ([DataType] != 'EntityId');
REPLICATE [OrderHistory] WITH ReplicateStartDate = '{env:start_extraction_date}', ReplicateEndDate = '{env:end_extraction_date}' SELECT [Id], [CreatedById], FORMAT([CreatedDate], 'yyyy-MM-dd hh:mm:ss') as [CreatedDate], [Field], [NewValue], [OldValue], [OrderId], [DataType] FROM [OrderHistory] WHERE ((([Field] = 'Status') OR ([Field] = 'Owner')) OR ([Field] = 'TotalAmount')) AND ([DataType] != 'EntityId');
REPLICATE [ContractHistory] WITH ReplicateStartDate = '{env:start_extraction_date}', replicateenddate = '{env:end_extraction_date}' SELECT [Id], [ContractId], [CreatedById], FORMAT([CreatedDate], 'yyyy-MM-dd hh:mm:ss') as [CreatedDate], [Field], [NewValue], [OldValue], [DataType] FROM [ContractHistory] WHERE ((([Field] = 'Status') OR ([Field] = 'Owner')) OR ([Field] = 'ContractTerm')) AND ([DataType] != 'EntityId');

Note regarding CData Salesforce Provider Version 21.0.8097.0 When a table has an _Incremental Check Column_ field predefined in CData Sync, the SQL query cannot use the same date field in both statements (WITH and WHERE) at the same time. If version 21.0.8097.0 (or an older version) of CData is used, the date filtering should be done in the WHERE clause and should be removed from the WITH statement.

Alternative Scenarios

The following scenarios can be used in combination of each other:

Multi Currency and Dated Conversion Rates are enabled in Salesforce Org

In case multi currency is used in the Salesforce org, add the following queries:

REPLICATE [CurrencyType] SELECT [Id], [IsCorporate], [IsoCode] FROM [CurrencyType] WHERE [IsCorporate] = 1;
REPLICATE [DatedConversionRate] SELECT [Id], [IsoCode], FORMAT([NextStartDate], 'yyyy-MM-dd') as [NextStartDate], FORMAT([StartDate], 'yyyy-MM-dd') as [StartDate], [ConversionRate] FROM [DatedConversionRate];

Additionally, the Opportunity query must be changed to the following to add the CurrencyIsoCode field in the query.

REPLICATE [Opportunity] SELECT [Id], [AccountId], [Amount], FORMAT([CloseDate], 'yyyy-MM-dd') as [CloseDate], [CreatedById], FORMAT([CreatedDate], 'yyyy-MM-dd hh:mm:ss') as [CreatedDate], [CurrencyIsoCode], [ForecastCategory], [IsClosed], [IsWon], FORMAT([LastStageChangeDate], 'yyyy-MM-dd hh:mm:ss') as [LastStageChangeDate], [LeadSource], [Name], [OwnerId], [Probability], [StageName], [Type], [SBQQ__AmendedContract__c] FROM [Opportunity] WHERE ([CreatedDate] >= '{env:start_extraction_date}') AND ([CreatedDate] <= '{env:end_extraction_date}');

Person Accounts enabled in Salesforce Org

In Case Person Accounts (field IsPersonAccount) are used - replace the Accounts query with the following query. This query will add the field IsPersonAccount in the Accounts extraction.

REPLICATE [Account] SELECT [Id], [AccountSource], [BillingCountry], [BillingState], [CreatedById], FORMAT([CreatedDate], 'yyyy-MM-dd hh:mm:ss') as [CreatedDate], [Industry], [IsPersonAccount], [Name], [OwnerId], [Ownership], [ParentId], [Rating], [Type] FROM [Account];

Record Types are enabled in Salesforce Org

In case Record Types are used the Opportunity query must be amended to add the RecordTypeId field in the query. The following replicate shows Opportunity with RecordTypeId and CurrencyIsoCode values added.

REPLICATE [Opportunity] SELECT [Id], [AccountId], [Amount], FORMAT([CloseDate], 'yyyy-MM-dd') as [CloseDate], [CreatedById], FORMAT([CreatedDate], 'yyyy-MM-dd hh:mm:ss') as [CreatedDate], [CurrencyIsoCode], [ForecastCategory], [IsClosed], [IsWon], FORMAT([LastStageChangeDate], 'yyyy-MM-dd hh:mm:ss') as [LastStageChangeDate], [LeadSource], [Name], [OwnerId], [Probability], [RecordTypeId], [StageName], [Type], [SBQQ__AmendedContract__c] FROM [Opportunity] WHERE ([CreatedDate] >= '{env:start_extraction_date}') AND ([CreatedDate] <= '{env:end_extraction_date}');

Input fields

The following tables include the list of fields per input table, their description, data type to be used when formatting the input and the filter flag to identify those that are being used to filter data.

Input types

Below is an overview of the different field types and their default format settings.

Field type Description
boolean true, false, 1, 0
date yyyy-mm-dd
datetime yyyy-mm-dd hh:mm:ss[.ms], where [.ms] is optional.
double Decimal separator: . (dot); thousand separator: none
integer Thousand separator: none
text N/A

Account

Represents an individual account, which is an organization or person involved with your business (such as customers, competitors, and partners).

Field Description Data Type Filtering
Id Account Identifier (Primary Key). text
AccountSource The source of the account record. For example, Advertisement, Data.com, or Trade Show. The source is selected from a pick list of available values, which are set by an administrator. text
BillingCountry Details for the billing address of this account. text
BillingState Details for the billing address of this account. text
CreatedById User ID who created the account. text
CreatedDate Date in which the account was created. datetime
Industry An industry associated with this account. text
IsPersonAccount* Indicates whether this account has a record type of Person Account (true) or not (false). This field is not available by default in Salesforce, but is setup by Salesforce when requested for B2C environments. This attribute can be enabled / disabled using the use_is_person_account DBT variable. boolean
Name Name of the account. If the account has a record type of Person Account: This value is the concatenation of the FirstName, MiddleName, LastName, and Suffix of the associated person contact. text
OwnerId The ID of the user who currently owns this account. text
Ownership Ownership type for the account, for example Private, Public, or Subsidiary. text
ParentId ID of the parent object, if any. text
Rating The account's prospect rating, for example Hot, Warm, or Cold. text
Type Type of account, for example, Customer, Competitor, or Partner. text

Campaign

Represents and tracks a marketing campaign, such as a direct mail promotion, webinar, or trade show.

Field Description Data Type Filtering
Id Campaign Identifier (Primary Key). text
CreatedById User ID who created the campaign. text
CreatedDate Date in which the campaign was created. datetime time based filter
Name Name of the campaign. text
OwnerId ID of the user who owns this campaign. text
ParentId ID of the parent Campaign record, if any. text
Status Status of the campaign, for example, Planned, In Progress. text
Type Type of campaign, for example, Direct Mail or Referral Program. text

CampaignMember

Represents the association between a campaign and either a lead or a contact.

Field Description Data Type Filtering
Id Campaign Member Identifier (Primary Key). text
CampaignId Required. ID of the Campaign to which this Lead or Contact is associated. text
CreatedById User ID who created the campaign member record. text
CreatedDate Date in which the campaign member record was created. datetime time based filter
LeadId Required. ID of the Lead who is associated with a Campaign. text
Status Controls the HasResponded flag on this object. In the Salesforce user interface, Marketing users can define valid status values for the Status picklist. text

Contract

Represents the subscription products that customers have purchased. The Contract store information on the subscriptions that sales reps have quoted or ordered.

Field Description Data Type Filtering
Id Contract Identifier (Primary Key). text
CreatedById User ID who created the contract record. text
CreatedDate Date in which the contract record was created. datetime time based filter
ContractNumber Auto Number assigned to the Contract once is created. text
OwnerId ID of the user who owns this contract. text

ContractHistory

History for tracked fields of Contract.

Field Description Data Type Filtering
Id Contract History Identifier (Primary Key). text
ContractId Related Contract Record Id. text
CreatedById User ID who created the history record. text
CreatedDate Date in which the history record was created. datetime time based filter
DataType Data Type for the field which was modified. text <> EntityId
Field Field which was modified on the contract record. text in (Status, Owner, ContractTerm)
NewValue Value after change. text
OldValue Value before change. text

CurrencyType

Represents the currencies used by an organization for which the Multi-currency feature is enabled.

Field Description Data Type Filtering
Id Currency Type record identifier (Primary Key). text
IsCorporate Indicates whether this currency type is the corporate currency (true) or not (false). Label is Corporate Currency. All other currency conversion rates are applied against this corporate currency. boolean = 1
IsoCode ISO code of the currency. Must be one of the valid alphabetic, three-letter currency ISO codes defined by the ISO 4217 standard, such as USD, GBP, or JPY. text

DatedConversionRate

Represents the dated exchange rates used by an organization for which the Multi-currency and the effective dated currency features are enabled.

Field Description Data Type Filtering
Id Dated Conversion Rate record identifier (Primary Key). text
ConversionRate Conversion rate of this currency type against the corporate currency. double
IsoCode ISO code of the currency. Must be one of the valid alphabetic, three-letter currency ISO codes defined by the ISO 4217 standard, such as USD, GBP, or JPY. text
NextStartDate The date on which the next effective dated exchange rate will start. Effectively the day after the end date for this exchange rate. date
StartDate The date on which the effective dated exchange rate starts. date

Lead

Represents a prospect or lead.

Field Description Data Type Filtering
Id Lead Identifier (Primary Key). text
ConvertedOpportunityId Object reference ID that points to the opportunity into which the lead has been converted. text
CreatedById User ID who created the campaign member record. text
CreatedDate Date in which the campaign member record was created. datetime time based filter
Name Concatenation of FirstName, MiddleName, LastName, and Suffix up to 203 characters, including white spaces. text
OwnerId ID of the lead's owner. text

LeadHistory

History for tracked fields of Lead.

Field Description Data Type Filtering
Id Lead History Identifier (Primary Key). text
LeadId Related Lead Record Id. text
CreatedById User ID who created the history record. text
CreatedDate Date in which the history record was created. datetime time based filter
DataType Data Type for the field which was modified. text <> EntityId
Field Field which was modified on the lead record. text in (Status, Rating, Owner)
NewValue Value after change. text
OldValue Value before change. text

Opportunity (Main Object)

Represents an opportunity, which is a sale or pending deal. This will be the main Case Identifier for the Process Mining model.

Field Description Data Type Filtering
Id Opportunity Identifier (Primary Key). text
AccountId ID of the account associated with this opportunity. text
Amount Estimated total sale amount. For opportunities with products, the amount is the sum of the related products. double
CloseDate Date when the opportunity is expected to close. date
CreatedById User ID who created the opportunity record. text
CreatedDate Date in which the opportunity record was created. datetime time based filter
CurrencyIsoCode Available only for organizations with the Multi-currency feature enabled. Contains the ISO code for any currency allowed by the organization. text
ForecastCategory Used to group opportunities for forecasting. It is implied, but not directly controlled, by the StageName field. text
IsClosed Boolean value which states whether the opportunity is closed. boolean
IsWon Boolean value which states whether the opportunity is won. boolean
LastStageChangeDate Shows timestamp in which the stage was changed last. Present starting from Salesforce REST API version 52.0. datetime
LeadSource Source of this opportunity, such as Advertisement or Trade Show. text
Name A name for this opportunity. text
OwnerId ID of the User who has been assigned to work this opportunity. text
Probability Percentage of estimated confidence in closing the opportunity. It is implied, but not directly controlled, by the StageName field. double
RecordTypeId* ID of the record type assigned to this object. In order for this field to appear on the opportunity object, there needs to be at least one record type for opportunities. text
SBQQ__AmendedContract__c ID of the related contract that was amended. The origin for the amendment opportunity. text
StageName Current stage of this record. The StageName field controls several other fields on an opportunity. text
Type Type of opportunity. For example, Existing Business or New Business. text

OpportunityFieldHistory

Represents the history of changes to the values in the fields of an opportunity.

Field Description Data Type Filtering
Id Opportunity History Identifier (Primary Key). text
OpportunityId Related Opportunity Record Id. text
CreatedById User ID who created the history record. text
CreatedDate Date in which the history record was created. datetime time based filter
DataType Data Type for the field which was modified. text <> EntityId
Field Field which was modified on the opportunity record. text in (StageName, Amount, Owner, CloseDate)
NewValue Value after change. text
OldValue Value before change. text

Order

Represents an order associated with a quote, an order record track the products from customers purchases.

Field Description Data Type Filtering
Id Order Identifier (Primary Key). text
CreatedById User ID who created the order. text
CreatedDate Date in which the quote was order. datetime time based filter
Name Name for the order. text
OpportunityId Related Opportunity Record Id. text
SBQQ__Quote__c ID for the quote associated with the order. text

OrderItem

Represents an order product which is a product or service that is provided to a customer according to an associated order for each of the quote lines.

Field Description Data Type Filtering
OrderId ID for the order associated with the order product. text
SBQQ__Contract__c The contract of the subscription that is created from this order product. text

Note: Filter in OrderItem is done by CreatedDate field which is not being extracted

OrderHistory

Represents historical information about changes that have been made to the standard fields of the associated order, or to any custom fields with history tracking enabled.

Field Description Data Type Filtering
Id Order History Identifier (Primary Key). text
OrderId Related order Record Id. text
CreatedById User ID who created the history record. text
CreatedDate Date in which the history record was created. datetime time based filter
DataType Data Type for the field which was modified. text <> EntityId
Field Field which was modified on the order record. text in (Status, Owner, TotalAmount)
NewValue Value after change. text
OldValue Value before change. text

Quote

Represents the Quote object that stores all the details of the products and prices quoted for an opportunity. Quotes are created from and synced with opportunities, and emailed as PDFs to customers.

Field Description Data Type Filtering
Id Quote Identifier (Primary Key). text
CreatedById User ID who created the quote. text
CreatedDate Date in which the quote was created. datetime time based filter
Name System-generated number for referencing the quote. text
SBQQ__Opportunity2__c ID for the opportunity associated with the quote. text

QuoteHistory

Represents historical information about changes that have been made to the standard fields of the associated quote, or to any custom fields with history tracking enabled.

Field Description Data Type Filtering
Id Quote History Identifier (Primary Key). text
ParentId Related quote Record Id. text
CreatedById User ID who created the history record. text
CreatedDate Date in which the history record was created. datetime time based filter
DataType Data Type for the field which was modified. text <> EntityId
Field Field which was modified on the quote record. text in (SBQQ__Primary__c, SBQQ__Status__c, SBQQ__SubscriptionTerm__c) or in (SBQQ__SalesRep__c, SBQQ__ExpirationDate__c) and OldValue not null
NewValue Value after change. text
OldValue Value before change. text When "Field" takes the following values, OldValue cannot be null:'SBQQ__SalesRep__c', 'SBQQ__ExpirationDate__c'

ProcessDefinition

Represents the definition of a single approval process.

Field Description Data Type Filtering
Id Process Definition Identifier (Primary Key). text
Name The unique process name, used internally. text
TableEnumOrId Specifies the object associated with the approval process, such as Account or Contact. text
Type This field shows the type of process. In this case the only type that is of interest for the logic are the Approval types. text = Approval

ProcessInstance

Represents an instance of a single, end-to-end approval process.

Field Description Data Type Filtering
Id Process Instance Identifier (Primary Key). text
CreatedById User ID who created the process instance. text
CreatedDate Date in which the process instance was created. datetime time based filter
ProcessDefinitionId The ID of this approval process instance. This is a relationship field. text
Status The status of this approval process instance, for example Started, Pending, or Approved. text
TargetObjectId ID of the object affected by this approval process instance. This is a polymorphic relationship field. text

ProcessInstanceStep

Represents one work item in an approval process (ProcessInstance).

Field Description Data Type Filtering
Id Process Instance Step Identifier (Primary Key). text
Comments Limit: 4,000 bytes. text
CreatedById User ID who created the process instance step. text
CreatedDate Date in which the process instance step was created. datetime time based filter
ProcessInstanceId ID of the ProcessInstance that this approval step belongs to. This is a relationship field. text
StepStatus The current status of this approval step. Examples are Approved, Fault, Held, NoResponse, Pending, Reassigned, Rejected, Removed, Started. text

RecordType

Represents a record type, which might define a different sales process for opportunities.

Field Description Data Type Filtering
Id Record Type Identifier (Primary Key). text
Name Label of the record type in the user interface. text

User

Represents each user in the Salesforce organization.

Field Description Data Type Filtering
Id User Identifier (Primary Key). text
Department The company department associated with the user. text
Name Concatenation of FirstName and LastName. text
UserType The category of user license. text

Design specifications

Entities

Salesforce already treats each object as an individual entity, however the objects are joined to additional master data in order to provide additional information.

Entity Input Data
Lead Lead, User
Opportunity Opportunity, Accounts, User, RecordType
Quote Quote, User
Order Order, User
Contract Contract, User

Activities

Entity Activity Description
Lead Creation Lead is generated in the system. Identified based on the CreateDate field on the Lead object.
Lead Add to Campaign Lead is added to a marketing campaign for tracking. Identified based on the CreateDate field on the CampaignMember object for each LeadId.
Lead Status Change* Lead moves from different status. Standard status in Salesforce are: Open, Contacted, Qualified, Unqualified. Identified based on the Old and New values for field = Status on the LeadHistory object.
Lead Approvals* Any approval process which was submitted. This may be generated by any custom configuration the customer has.
Lead Additional Field Changes* Depending on fields tracked on the object, the event will be record as a change activity. In the standard connector, Owner and Rating are tracked, but may be expanded depending on customer configuration. Identified based on records on the LeadHistory object.
Opportunity Creation Opportunity is generated in the system. Identified based on the CreateDate field on the Opportunity object.
Opportunity Stage Change* Opportunity moves from different status. Standard status in Salesforce are: Prospecting, Qualification, Needs Analysis, Value Proposition, ID Decision Makers, Perception Analysis, Proposal / Price Quote, Negotiation / Review, Closed Won, Closed Lost. Identified based on the Old and New values for field = StageName on the OpportunityFieldHistory object.
Opportunity Additional Changes* Depending on fields tracked on the object, the event will be record as a change activity. In the standard connector, Owner, Close Date and Amount are tracked, but may be expanded depending on customer configuration. Identified based on values on the OpportunityFieldHistory object.
Opportunity Approvals* Any approval process which was submitted. This may be generated by any custom configuration the customer has.
Quote Creation Quote is generated in the system. Identified based on the CreateDate field on the Quote.
Quote Approvals* Any approval process which was submitted. This may be generated by any custom configuration the customer has.
Quote Status Change* Quote moves from different status. Standard status in Salesforce are: Draft, In Review, Approved, Denied, Presented, Accepted, Rejected. Identified based on the Old and New values for field = Status on the QuoteHistory object.
Quote Additional Changes* Depending on fields tracked on the object, the event will be record as a change activity. In the standard connector, Primary quote, subscription terms, Sales Rep and Expiration date are tracked, but may be expanded depending on customer configuration. Identified based on the values on the QuoteHistory object.
Order Creation Order is generated in the system. Identified based on the CreateDate field on the Order.
Order Status Change* Order moves from different status. Standard status in Salesforce are: Draft, Activated. Identified based on the Old and New values for field = Status on the OrderHistory object.
Order Additional Changes* Depending on fields tracked on the object, the event will be record as a change activity. In the standard connector, Owner, and TotalAmount are tracked, but may be expanded depending on customer configuration. Identified based on the values on the OrderHistory object.
Order Approvals* Any approval process which was submitted. This may be generated by any custom configuration the customer has.
Contract Creation Contract is generated in the system. Identified based on the CreateDate field on the Contract.
Contract Status Change* Contract moves from different status. Standard status in Salesforce are: Draft, In Approval Process, Activated. Identified based on the Old and New values for field = Status on the ContractHistory object.
Contract Additional Changes* Depending on fields tracked on the object, the event will be record as a change activity. In the standard connector, Owner, and Contract term are tracked, but may be expanded depending on customer configuration. Identified based on the values on the ContractHistory object.
Contract Approvals* Any approval process which was submitted. This may be generated by any custom configuration the customer has.

*The actual activity names will depend on the values defined on the customer Salesforce organization.

Fields

Cases_base

Field Data Type Description
Case_ID Text The unique identifier of the case. Mandatory field.
Case Text A user-friendly name to identify the case.
Account* Text Account related to the opportunity.
Account_billing_state* Text Billing State for Account.
Account_billing_country* Text Billing Country for Account.
Account_industry* Text Industry for Account.
Account_owner* Text Account Owner.
Account_ownership* Text Account Ownership (Public, Private, Subsidiary).
Account_rating* Text Rating for Account (Hot, Warm, Cold).
Account_source* Text Source for Account, such as email campaign or advertising.
Account_type* Text Account Type (mid-size, small).
Case_owner Text Opportunity Owner.
Case_status Text The status of the case in the process. For example, 'open', 'closed', 'pending', 'approved', etc.
Case_type Text The categorization of the cases.
Case_value Double A monetary value related to the case. Shown in corporate currency.
Corporate_currency* Text Currency code used to report values across the organization.
Forecast_category* Text Category for Opportunity Forecast.
Lead_source* Text Source for Lead related to Opportunity.
Opportunity_closed* Boolean Whether the Opportunity has been closed.
Opportunity_name* Text Opportunity Name.
Opportunity_probability* Text Probability of Winning the opportunity.
Opportunity_won* Boolean Whether the Opportunity has been won.
Opportunity_creator* Text User who created the opportunity.
Original_amount* Double Value for opportunity in original currency.
Record_type* Text Record Type for the opportunity.
Person_account* Boolean Whether the Account is a person account.
Transaction_currency* Text Original transaction currency.

*Case attributes which need to be configured on top of standard TemplateOne case attributes.

Event_log_base

Field Data Type Description
Case_ID Text The unique identifier of the case. Mandatory field.
Activity Text The name of the event. This describes the step in the process. Mandatory Field.
Event_end Date Time The timestamp associated with the end of executing the event. Mandatory Field.
Activity_type* Text Type of activity.
Activity_order Integer If activities take place on the same time (in parallel), you can define their order here.
Automated Boolean Indicates whether the event is manually executed or automated.
Event_detail Text Information related to the event.
Event_ID Integer A unique identifier of the event.
Team Text The team that executed the event.
User Text The user who executed the event.
User_type* Text User Type – Related to the Salesforce License.

*Event attributes which need to be configured on top of standard TemplateOne event attributes.

Customizing the transformations

Configuration

Automated Users

In order to add data which will be defined by the end user, one additional file is loaded into the model which need to be prepared by the Analyst. This is the Automated_users_raw file, which contains the users that are flagged as automated. If this is not configured, the connector will still work properly but it will not have that extra information.

Amount of events

All contracts that get amended will generate automatically a new Opportunity_id that will be linked to the former. Therefore, all activities happening to the contract will be included in both case_id's: the original opportunity and the amended one. That can be seen when comparing the total amount of records in Events_all table vs Event_log_base table, thus it will be correct to have more records in the Event_log_base table.

Dbt Variables

Below is an overview of the variables that must be configured based on the Salesforce configuration for the end customer. These variables are defined in the dbt_project.yml file.

Variable Type Description
conversion_rate_start_date string Specifies the starting date for currency conversions, when there are no prior historic exchange rates. This replaces the null value that is used in Salesforce.
use_is_person_account boolean Defines whether the IsPersonAccount attribute will be used. This should be disabled in Salesforce Orgs where there are no Person Accounts. Setting this to true will enable the attribute to being queried in the transformations, and will enable associated tests. Default is false.
use_currency_conversion boolean Defines whether the multi currency conversion will be used. This should be disabled in Salesforce Orgs where multi currency is not used. Setting this to true will enable currency conversion. Default is false.
use_record_types boolean Defines whether the record type conversions will be used. This should be disabled in Salesforce Orgs where record types are not used. Setting this to true will enable record types to be queried and added to the transformations. Default is false.

Limitations

CDATA Sync - Salesforce Connector Regarding CData Salesforce Provider Version 21.0.8097.0 When a table has an Incremental Check Column field predefined in CData, the SQL query cannot have both statements (WITH and WHERE) at the same time. If this is the version of CData that is being used, please remove the entire WHERE statement so the date filter done in the WITH statement works properly.

No Event Start

Salesforce does not store the start time for each change activity.

No flag to identify automated users

Salesforce does not have a field or special license for automated users. It does however have a default user named Automated Process which is used for automated processing for Salesforce changes, however any additional users such as interfaces or RPA will use normal licenses and are not differentiated by user type.

Salesforce Retention Policy

As of February 2022, Salesforce retains field history data for up to 18 months through the org and up to 24 months via the API.

In case the history needs to be stored for a longer period of time, Field Audit Trail must be activated. This allows copying of history data into a separate big data object and allows storage for up to 10 years.

Troubleshooting

General

Field not available in object during data extraction

Check Field Level Security in order to confirm the field is available and visible for the user profile extracting data. For CurrencyISOCode, IsPersonAccount, LastStageChangeDate, RecordTypeId check the specific issues in the following section.

Object not available during data extraction

The user profile might not have read access to the object. This can be corrected on the user profile or a different user should extract the data. For Order, Quote, CurrencyType, DatedConversionRate check the specific issues in the following section.

Object Specific

Campaign and CampaignMember objects not available

Check the user record for the user extracting data, and verify the Marketing User checkbox is Active.

CurrencyIsoCode is not available when extracting data from the Opportunity object

Multi-currency is not enabled within the Salesforce org. In case this happens, the conversions should be removed from the data model. This can be achieved by adjusting the variable use_currency_conversion in the dbt_project.yml to false.

CurrencyType object is not available when extracting data

Multi-currency is not enabled within the Salesforce org. In case this happens, the conversions should be removed from the data model. This can be achieved by adjusting the variable use_currency_conversion in the dbt_project.yml to false.

DatedConversionRate object is not available when extracting data

Effective currency rate management is not enabled within the Salesforce org. In case this happens, the conversions should be removed from the data model. This can be achieved by adjusting the variable use_currency_conversion in the dbt_project.yml to false.

IsPersonAccount field not available in Account object when extracting from Salesforce

When extracting data from Salesforce, IsPersonAccount may not be available as the organization may not have B2C customers. In case this happens, the field should be removed from the data model. This can be achieved by adjusting the variable use_is_person_account in the dbt_project.yml to false.

LastStageChangeDate field not available in Opportunity object when extracting from Salesforce

The field LastStageChangeDate was only made available starting with Salesforce REST API 52.0. Make sure the CData extraction or the extraction method used is using API 52.0 onwards.

RecordTypeId is not available when extracting data from the Opportunity object

Record Types are not used within the Salesforce org. In case this happens, the conversions should be removed from the data model. This can be achieved by adjusting the variable use_record_types in the dbt_project.yml to false.