Salesforce Lead-to-Order

• Process description
  • Available tags & due dates
• Extraction
  • System configuration
  • Setting up the source connection in CData Sync
  • Setting up the destination connection in CData Sync
  • Creating the extraction job in CData Sync
• Input data
  • Input types
  • Input tables & fields
• Configuring transformations
  • Seed files
  • Dbt variables
• Design specifications
  • Objects
  • Activities
  • Design details

Process description

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.

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 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, to identify the need and suggest an appropriate product or service which will meet this need. Part of the process may involve generating a quote to the customer, but this is not always the case. In case the opportunity is won, this will generate one or more orders to fulfill the won opportunity. In Salesforce, this is tracked via the Opportunity object, and optionally, the Quote object.

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 opportunity may have multiple orders.

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

Available tags & due dates

The following tags are available by default.

The following due dates are available by default.

Extraction

This app template uses Salesforce Sales Cloud as source system.

Extraction for the system is done using CData Sync. To set-up the extraction, make sure you have a valid license for CData Sync and have installed CData Sync.

System configuration

To be able to extract data from Salesforce the following configuration in Salesforce is required.

Field history tracking

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:

• Opportunity: Stagename
• Lead: Status
• Order: Status

By default, Field History is disabled per object. In order to setup Field History Tracking for an object, the following 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.

Campaign information

The user must be marked as "Marketing User" in the user settings in order to extract Campaign information.

API access

In order to connect via CData Sync, the user establishing the connection must be API Enabled, and must have appropriate read permissions on each of the objects which are being extracted. All fields included in the input tables must be visible for the user extracting the data. In case any fields are not visible, and may not be made visible, permissions should be granted to the user, or a profile with permissions should be used to extract the data.

Additionally, a security token should be generated for the user. This can be done by resetting the current security token in your personal settings, see Salesforce - Reset Your Security Token. The security token is required to establish the connection to Salesforce via CData Sync.

Salesforce objects

This app template works on the process starting on the marketing campaign generation, up to the generation of customer orders. This includes processing of the following transactional objects:

• Campaign
• CampaignMember
• Lead
• LeadHistory
• Opportunity
• OpportunityFieldHistory
• Quote*
• Order*
• OrderHistory*
• ProcessInstance
• ProcessInstanceStep

Additionally, the following additional objects are used to add additional information:

• Account
• RecordType
• User
• ProcessDefinition

In order to allow Currency conversion and to calculate amounts using historical exchange rates, Effective dated currency should be enabled in the Salesforce org, and historical exchange rates should be loaded into the system. The following object will become available after the feature is enabled:

• CurrencyType*
• DatedExchangeRate*

*Quote, Order, OrderHistory, CurrencyType, DatedExchangeRate objects are by default disabled in Salesforce orgs. By default, these will not be considered in the transformations. If these are used, the transformation and related logic can be enabled by using DBT variables. Alternatively, the information for these feature can also be loaded manually using seed files.

Setting up the source connection in CData Sync

• In CData - Connections, add a new source connection for Salesforce. If this option is not in the list, deselect Only Installed and install the connector
• Select Basic as the Auth Scheme.
• Enter the correct User, Password, Security token and Login URL for the account.
• Select API version to be at least 52.0.
• Depending on the Salesforce org settings, the connection may need to be configured to use the Sandbox environment. If needed set Use Sandbox to True.
• Create and test the connection.

Setting up the destination connection in CData Sync

To set up the destination connection, follow the steps as defined in Create a destination connection (Automation Cloud). Note: If you are using Automation Suite, make sure to select AUTOMATION SUITE as the DELIVERY option and select the correct version of Automation Suite you are using.

Creating the extraction job in CData Sync

To create the Job in CData make sure to follow the steps below.

• In CData - Jobs, create a new job.
• Select the source and destination connections created for the Salesforce Account Management app template and create the job.

Advanced job settings

In the Advanced tab in the Job Settings panel, edit the following settings:

• Enable the Alter Schema option.
• Enable the Drop Table option.

If you are using Automation Suite, set the Destination Schema in the Settings panel on the overview tab. Use the schema name you retrieved when you created the destination connection.

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.

In order to setup the environment variables:

<!-- 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" />

By default, end_extraction_date will default to today's date. start_extraction_date must always be populated.

Setting up the data ingestion call

After the data has been successfully loaded, the Process Mining platform needs to start the data ingestion process. This is done by calling the End of Upload API. In order to set this up, follow the steps below:

<api:set attr="http.url" value="END_OF_UPLOAD_API"/> 
<!-- <api:set attr="http.verbosity" value="5"/> -->
<!-- <api:set attr="http.logfile" value="D:/mydir/cdata_log.txt"/> -->
<api:call op="httpPost" in="http"/>

Table replication

Once the job is correctly setup, go to Task tab, click + Add Tasks, enable the Custom Query option 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 set of queries is prepared for scenarios where Orders, Quotes, Multi Currency, Dated Conversion Rates, Person Accounts and Record types 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 [LeadHistory] WITH ReplicateStartDate = '{env:start_extraction_date}', replicateenddate = '{env:end_extraction_date}' SELECT [Id], [LeadId], [CreatedById], FORMAT([CreatedDate], 'yyyy-MM-dd hh:mm:ss') as [CreatedDate], [DataType], [Field], [OldValue], [NewValue] FROM [LeadHistory] WHERE ((([Field] = 'Status') OR ([Field] = 'Rating')) OR ([Field] = 'Owner')) AND ([DataType] != 'EntityId');
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] FROM [Opportunity] WHERE ([CreatedDate] >= '{env:start_extraction_date}') AND ([CreatedDate] <= '{env:end_extraction_date}');
REPLICATE [OpportunityFieldHistory] WITH ReplicateStartDate = '{env:start_extraction_date}', replicateenddate = '{env:end_extraction_date}' SELECT [Id], [OpportunityId], [CreatedById], FORMAT([CreatedDate], 'yyyy-MM-dd hh:mm:ss') as [CreatedDate], [DataType], [Field], [OldValue], [NewValue] FROM [OpportunityFieldHistory] WHERE (((([Field] = 'Amount') OR ([Field] = 'StageName')) OR ([Field] = 'Owner')) OR ([Field] = 'CloseDate')) AND ([DataType] != 'EntityId');
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}');

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:

Order object is enabled in Salesforce org In case the order object is enabled, add the following scripts to the default extraction:

REPLICATE [Order] SELECT [Id], [CreatedById], FORMAT([CreatedDate], 'yyyy-MM-dd hh:mm:ss') as [CreatedDate], [OpportunityId],[Name]  FROM [Order] WHERE ([CreatedDate] >= '{env:start_extraction_date}') AND ([CreatedDate] <= '{env:end_extraction_date}');
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] IN ('Status', 'Owner', 'TotalAmount') AND [DataType] != 'EntityId';

Quote object is enabled in Salesforce org In case the quote object is enabled, add the following scripts to the default extraction:

REPLICATE [Quote] SELECT [Id], [CreatedById], FORMAT([CreatedDate], 'yyyy-MM-dd hh:mm:ss') as [CreatedDate], [Name], [OpportunityId] FROM [Quote] WHERE ([CreatedDate] >= '{env:start_extraction_date}') AND ([CreatedDate] <= '{env:end_extraction_date}');

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 amended to add the CurrencyISOCode field in the query.

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.

Input data

Input types

The following table provides an overview of the different field types and their default format settings.

Input tables & fields

The following tables are extracted from the source system:

• Account
• Campaign
• CampaignMember
• CurrencyType
• DatedConversionRate
• Lead
• LeadHistory
• Opportunity
• OpportunityFieldHistory
• Order
• OrderHistory
• ProcessDefinition
• ProcessInstance
• ProcessInstanceStep
• Quote
• RecordType
• User

Account

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

Campaign

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

Filtering: CreatedDate is used for time-based filtering.

CampaignMember

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

Filtering: CreatedDate is used for time-based filtering.

CurrencyType

Represents the currencies used by an organization for which the multicurrency feature is enabled.

Filtering: IsCorporate = 1

DatedConversionRate

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

Lead

Represents a prospect or lead.

Filtering: CreatedDate is used for time-based filtering.

LeadHistory

History for tracked fields of Lead.

Filtering: CreatedDate is used for time-based filtering, DataType <> 'EntityId' and Field in ('Owner','Status','Rating').

Opportunity

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

Filtering: CreatedDate is used for time-based filtering.

OpportunityFieldHistory

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

Filtering: CreatedDate is used for time-based filtering, DataType <> 'EntityId' and Field in ('StageName','Amount','Owner','CloseDate').

Order

Represents an order associated with a contract or an account.

Filtering: CreatedDate is used for time-based filtering.

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.

Filtering: CreatedDate is used for time-based filtering, DataType <> 'EntityId' and Field in ('Status','Owner','TotalAmount').

ProcessDefinition

Represents the definition of a single approval process.

Filtering: Type = 'Approval'.

ProcessInstance

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

Filtering: CreatedDate is used for time-based filtering.

ProcessInstanceStep

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

Filtering: CreatedDate is used for time-based filtering.

Quote

The Quote object represents a quote, which is a record showing proposed prices for products and services. Quotes can be created from and synced with opportunities, and emailed as PDFs to customers.

Filtering: CreatedDate is used for time-based filtering.

RecordType

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

User

Represents each user in the organization.

Configuring transformations

Seed files

Automation_estimates_raw

This seed file is used to add automation-related properties to each activity, used for the automation potential dashboard. For more information, see Simulating Automation Potential.

Due_dates_configuration_raw

This seed file is used to define properties for the due dates. For more information, see Due Dates.

Raw_automated_users

The Raw_automated_users file contains a list of users which are considered automated.

Dbt variables

Design specifications

Objects

The following diagram shows all objects that were used for the process.

Objects are joined to master data to provide additional information.

Activities

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

Design details

Known limitations

• Quote object field history tracking: As of January 2022, this is a current limitation on the quote object in Salesforce, as it currently does not allow for field history tracking.
• 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

• 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.
• 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: Multicurrency 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: Multicurrency 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.
• Order object not available: The Order object may not be enabled in the Salesforce org. In case this happens the table must be loaded empty to allow for all the rest of the transformations to occur. This can be achieved by adjusting the variable order_object_enabled in the dbt_project.yml to false.
• Quote object not available: The Order object may not be enabled in the Salesforce org. In case this happens the table must be loaded empty to allow for all the rest of the transformations to occur. This can be achieved by adjusting the variable quote_object_enabled in the dbt_project.yml to false.
• 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.