Salesforce Lead-to-Order


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.

Tag Description
Opportunity amount reduced Opportunity Amount was changed to a value less than the original value. Represents an opportunity which is losing value.
Opportunity won without a Quote An opportunity was won without sending a formal quote. Potentially arriving to a tentative price or list of products could have improved chances.
Multiple Owner Changes Applies for all objects, reflects a back and forth between team members which may be justified (vacations, escalations) or may require attention.
Approval process started multiple times Multiple approvals may exist for one object, however if the same approval process is triggered multiple times, there might be inefficiencies to review.
Opportunity close date postponed An opportunity is getting delayed according to it's original close date, which means the opportunity might require attention.
Multiple users for same activity An opportunity has the same activtiy executed multiple times by different users.

The following due dates are available by default.

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

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:

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:

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

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:

*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

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.

Advanced job settings

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

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 Go to the Events tab in the Job Settings panel of the job you are creating
2 Edit the Pre-Job Event section to add the code displayed below after <!-- Code goes here -- >. Do not modify the api:info details that are shown by default.
3 Click on Save Changes.
<!-- 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:

Step Action
1 Go to the Events tab in the Job Settings panel of the job you are creating
2 Edit the Post-Job Event section to add the code displayed below after <!-- Code goes here -- >. Do not modify the api:info details that are shown by default.
3 Fill out the End of Upload API with the value provided.
4 In case you need a debugging log for this call, uncomment the two lines provided in the code below and provide a location for the log file.
5 Click on Save Changes.
<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, click on Add Custom Query under the Tasks 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 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.

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

Input tables & fields

The following tables are extracted from the source system:

Account

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

Field Type Description
Id text Account Identifier (Primary Key)
AccountSource text The source of the account record. For example, Advertisement, Data.com, or Trade Show. The source is selected from a picklist of available values, which are set by an administrator.
BillingCountry text Details for the billing address of this account.
BillingState text Details for the billing address of this account.
CreatedById text User ID who created the account.
CreatedDate datetime Date in which the account was created.
Industry text An industry associated with this account.
IsPersonAccount boolean 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 .
Name text 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.
OwnerId text The ID of the user who currently owns this account.
Ownership text Ownership type for the account, for example Private, Public, or subsidiary.
ParentId text ID of the parent object, if any.
Rating text The account's prospect rating, for example Hot, Warm, or Cold.
Type text Type of account, for example, Customer, Competitor, or Partner.

Campaign

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

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

Filtering: CreatedDate is used for time-based filtering.

CampaignMember

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

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

Filtering: CreatedDate is used for time-based filtering.

CurrencyType

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

Field Type Description
Id text Currency Type record identifier (Primary Key).
IsoCode text 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.
IsCorporate boolean 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.

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.

Field Type Description
Id text Dated Conversion Rate record identifier (Primary Key).
IsoCode text 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.
StartDate date The date on which the effective dated exchange rate starts.
NextStartDate date The date on which the next effective dated exchange rate will start. Effectively the day after the end date for this exchange rate.
ConversionRate double Conversion rate of this currency type against the corporate currency.

Lead

Represents a prospect or lead.

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

Filtering: CreatedDate is used for time-based filtering.

LeadHistory

History for tracked fields of Lead.

Field Type Description
Id text Lead History Identifier (Primary Key)
LeadId text Related Lead Record Id
CreatedById text User ID who created the history record
CreatedDate datetime Date in which the history record was created.
DataType text Data Type for the field which was modified
Field text Field which was modified on the lead record
OldValue text Value before change
NewValue text Value after change

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.

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

Filtering: CreatedDate is used for time-based filtering.

OpportunityFieldHistory

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

Field Type Description
Id text Opportunity History Identifier (Primary Key)
OpportunityId text Related Opportunity Record Id.
CreatedById text User ID who created the history record.
CreatedDate datetime Date in which the history record was created.
DataType text Data Type for the field which was modified.
Field text Field which was modified on the opportunity record.
OldValue text Value before change.
NewValue text Value after change.

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.

Field Type Description
Id text Order Identifier (Primary Key).
CreatedById text User ID who created the order.
CreatedDate datetime Date in which the quote was order.
Name text Name for the order.
OpportunityId text ID for the opportunity associated with the order.

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.

Field Type Description
Id text Order History Identifier (Primary Key).
OrderId text Related order Record Id.
CreatedById text User ID who created the history record.
CreatedDate datetime Date in which the history record was created.
DataType text Data Type for the field which was modified.
Field text Field which was modified on the order record.
OldValue text Value before change.
NewValue text Value after change.

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.

Field Type Description
Id text Record Type Identifier (Primary Key).
Name text The unique process name, used internally.
TableEnumOrId text Specifies the object associated with the approval process, such as Account or Contact.
Type text The type of this process. Approval Process: used to control the action taken for a record. State-based Process: Used internally to track various control processes, such as for developing Salesforce Knowledge articles.

Filtering: Type = 'Approval'.

ProcessInstance

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

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

Filtering: CreatedDate is used for time-based filtering.

ProcessInstanceStep

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

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

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.

Field Type Description
Id text Quote Identifier (Primary Key).
CreatedById text User ID who created the quote.
CreatedDate datetime Date in which the quote was created.
Name text Name for the quote.
OpportunityId text ID for the opportunity associated with the quote.

Filtering: CreatedDate is used for time-based filtering.

RecordType

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

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

User

Represents each user in the organization.

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

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.

Field Type Description
Activity Text Display name for the activity
Event_cost Double Cost associated with the activity
Event_processing_time Integer Processing time associated with the activity (in milliseconds)

Due_dates_configuration_raw

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

Field Type Description
Due_date Text The name of the due date
Due_date_type Text The Due date type
Fixed_costs Boolean An indication whether costs are fixed or time based
Cost Double Fixed costs: The amount of costs. Variable costs: The amount of costs per Time and Time_type
Time Integer A number indicating the amount of time in case of time-based costs
Time_type Text Type of time period for cost calculations. This can be any of the following values: day, hour, minute, second or millisecond

Raw_automated_users

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

Field Type Description
Automated_users Text Name of the user that is considered automated.

Dbt variables

Variable Type Description
starting_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.
order_object_enabled Boolean Defines whether the order object will be used. This should be disabled in Salesforce Orgs where orders are not used. Setting this to true will enable any transformations related to orders. Default is false.
quote_object_enabled Boolean Defines whether the quote object will be used. This should be disabled in Salesforce Orgs where quotes are not used. Setting this to true will enable any transformations related to quotes. Default is false.

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.

Object Input Data
Lead Lead, User
Opportunity Opportunity, Accounts, User, RecordType
Quote Quote, User
Order Order, User

Activities

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

Activity Object Description
Create Lead Lead Lead is generated in the system. Identified based on the CreateDate field on the Lead object.
Add Lead to Campaign Lead Lead is added to a marketing campaign for tracking. Identified based on the CreateDate field on the CampaignMember object for each LeadId.
Change Lead Owner Lead Owner of the Lead is changed. Identified based on the Old and New values for field = Owner on the LeadHistory object.
Change Lead Rating Lead Rating of the Lead is changed. Identified based on the Old and New values for field = Rating on the LeadHistory object.
Change Lead Status to X Lead Lead is moved to 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 Approval - X - Y Lead Any approval process which is submitted. This may be generated by any custom configuration.
Create Opportunity Opportunity Opportunity is generated in the system. Identified based on the CreateDate field on the Opportunity object.
Change Opportunity Stage to X Opportunity Opportunity moves to different stage. Standard status 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.
Change Opportunity Amount Opportunity Amount of the Opportunity is changed. Identified based on the Old and New values for field = Amount on the OpportunityFieldHistory object.
Change Opportunity Stage to X Opportunity Opportunity is moved to different stage. Identified based on the Old and New values for field = StageName on the OpportunityFieldHistory object.
Change Close Date Opportunity Close data of the Opportunity is changed. Identified based on the Old and New values for field = CloseDate on the OpportunityFieldHistory object.
Change Opportunity Owner Opportunity Owner of the Opportunity is changed. Identified based on the Old and New values for field = Owner on the OpportunityFieldHistory object.
Opportunity Approval - X - Y Opportunity Any approval process which is submitted. This may be generated by any custom configuration.
Create Quote Quote Quote is generated in the system. Identified based on the CreateDate field on the Quote object.
Quote Approval - X - Y Quote Any approval process which is submitted. This may be generated by any custom configuration.
Create Order Order Order is generated in the system. Identified based on the CreateDate field on the Order object.
Change Order Status to X Order Order is moved to different status. Standard status are: Draft, Activated. Identified based on the Old and New values for field = Status on the OrderHistory object.
Change Order Amount Order Amount of the Order is changed. Identified based on the Old and New values for field = TotalAmount on the OrderHistory object.
Change Order Owner Order Owner of the Order is changed. Identified based on the Old and New values for field = Owner on the OrderHistory object.
Order Approval - X - Y Order Any approval process which is submitted. This may be generated by any custom configuration.

Design details

Known limitations

Troubleshooting