Salesforce Lead Management

Introduction

With the Salesforce Lead Management app template you can load raw input data from Salesforce with Pardot package enabled 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 Management process

The Lead Management process is an end-to-end process which starts with a potential customer's interest in a service or product, up to the closure of the opportunity. This process mainly focuses on the interactions between the potential customer and Pardot's automated marketing tools.

The following funnel chart shows the Process for Lead Management.

Prospect Acquisition Pardot will automatically acquire potential prospects through different mediums, such as Webpages, Forms, Social Media and Emails, and assign them to a designated campaign. The objective of this stage of the process is to generate as many Prospects as possible, which can be later qualified and converted into an opportunity in Salesforce. Prospects are what Leads are called in Pardot.

Marketing Qualified Leads (MQL) Once a Prospect is incorporated into Pardot, by completing the Prospect Acquisition stage, it is qualified and assigned to a user (Sales Rep). Once this action is executed, the Prospect will synchronize with Salesforce, generating a Lead. The Assigned user will continue working on that Lead in order to convert it into an Opportunity; Pardot amplifies the Lead conversion process by executing automated actions, such as sending targeted promotional content through email, and tracking webpage interactions of the related Prospect.

Sales Qualified Leads (SQL) After going through the MQL stage, the initial Lead is converted into an Opportunity in Salesforce. This prospect is now considered a Sales Qualified Lead, and ready for a direct sales push. Pardot tracks changes in the Prospect's opportunity, such as status, owner and status changes, and also provides support through automated actions in order to finalize the sale.

Entity relationship diagram

The following shows all entities that were used for the process.

Note: CampaignMember was included in the ERD even though it is not considered as an Entity. This table is only used to connect the entities Lead_id and Campaign_id.

Tags

Tag Description
Failed first-touch prospect conversion Identifies cases where Visitors were not converted into Prospects in the first contact with a Pardot Campaign. This is a measure of inefficiencies in a Campaign.
Failed visitor activity All cases where there was at least one activity that failed will be tagged. These errors should not be happening and must be avoided.
First-touch prospect conversion In this case, only successful Prospect conversions in the first touch are flagged. This can be used to contrast with cases where the first-touch conversion failed.
Lost prospect Identifies cases where a Prospect lost its associated opportunity.
Multiple lead owner changes Applies for Leads, and reflects that the owner has been changed multiple times. That may be justified (vacations, escalations) or may require attention.
Prospect did not reach SQL Sales Qualified Lead status was not granted, meaning that the Lead was not converted into a Opportunity. The expectation is that all Leads eventually get converted into Opportunities, thus, this tag shows unsuccessful cases
Prospects within multiple campaigns Identifies cases where a prospect is being added to more than one Campaign, signalling a possible issue in the handling of said prospect.
Unsynchronized Prospects Refers to Prospects that failed to sync into Salesforce CRM, in turn preventing the completion of process.

Salesforce Sales Cloud configuration

Source system

Salesforce Sales Cloud with Salesforce Pardot package and the Lightning App enabled. The following permissions are needed:

The user should also be configured as a System Administrator in Salesforce, in order to generate a Security Token for extraction in CData. A domain service should be configured in Pardot to view the full scope of the process.

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

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.

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

In order 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.

System specific settings

The Salesforce Lead Management app template requires the following Salesforce CRM and Pardot transactional objects:

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

*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.

Configuring CData Sync for Salesforce Pardot

Below is a description on how to use CData Sync to set up a source connection and load data into a Process Mining Salesforce Lead Management 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 Pardot, 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.

Be mindful of choosing a start_extraction_date that encompasses the data that you want to capture, as orders are the starting point of the order to cash process.

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 [ProspectAccounts] SELECT [Id], [BillingCountry], [BillingState], FORMAT([CreatedAt], 'yyyy-MM-dd hh:mm:ss') as [CreatedAt], 
[Industry], [Name], [Rating], [Type] FROM [ProspectAccounts] WHERE ([CreatedAt] >= '{env:start_extraction_date}') AND ([CreatedAt] <= '{env:end_extraction_date}');
REPLICATE [LifecycleStages] SELECT [Id], [Name] FROM [LifecycleStages];
REPLICATE [LifecycleHistories] WITH ReplicateStartDate = '{env:start_extraction_date}', replicateenddate = '{env:end_extraction_date}' SELECT [Id], FORMAT([CreatedAt], 'yyyy-MM-dd hh:mm:ss') as [CreatedAt], [NextStageId], [PreviousStageId], [ProspectId] FROM [LifecycleHistories] WHERE [NextStageId] IN ('25114', '25117', '25120', '25123');
REPLICATE [Visitors] SELECT [Id], FORMAT([CreatedAt], 'yyyy-MM-dd hh:mm:ss') as [CreatedAt], [ProspectId] FROM [Visitors] WHERE ([CreatedAt] >= '{env:start_extraction_date}') AND ([CreatedAt] <= '{env:end_extraction_date}');
REPLICATE [VisitorActivities] WITH ReplicateStartDate = '{env:start_extraction_date}', replicateenddate = '{env:end_extraction_date}' SELECT [Id], FORMAT([CreatedAt], 'yyyy-MM-dd hh:mm:ss') as [CreatedAt], [CampaignName], [ProspectId], [Type], [TypeName], [VisitorId] FROM [VisitorActivities] WHERE ([TypeName] IN ('Custom Redirect', 'Email', 'Email Tracker', 'File', 'Form', 'Form Handler', 'Form Tracker', 'Landing Page', 'Landing page tracker', 'Multivariate Landing Page', 'Paid Ad Search', 'Site Search Query', 'Visit Tracker')) AND (([ProspectId] IS NOT NULL) OR ([VisitorId] IS NOT NULL));
REPLICATE [Prospects] SELECT [Id], FORMAT([CreatedAt], 'yyyy-MM-dd hh:mm:ss') as [CreatedAt], [FirstName], [LastName], [CampaignId], [Country], [City], [CrmLeadFid], [CrmContactFid], [CrmOwnerFid], [CrmAccountFid], FORMAT([CrmLastSync], 'yyyy-MM-dd hh:mm:ss') as [CrmLastSync], [Department], [ProspectAccountId], [State], [Source], [Score] FROM [Prospects] WHERE ([CreatedAt] >= '{env:start_extraction_date}') AND ([CreatedAt] <= '{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.

Configuring CData Sync for Salesforce Sales Cloud

Below is a description on how to use CData Sync to set up a source connection and load data into a Process Mining Salesforce Lead Management 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, 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.

Be mindful of choosing a start_extraction_date that encompasses the data that you want to capture, as orders are the starting point of the order to cash process.

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 the scenario where Currency Conversions is not used and is disabled. In case is being used, please add the scripts in the Alternative Scenario section.

REPLICATE [Campaign] SELECT [Id], [CreatedById], FORMAT([CreatedDate], 'yyyy-MM-dd hh:mm:ss') as [CreatedDate], [Name], [OwnerId], [Status], [Type] FROM [Campaign];
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], [ConvertedContactId], [pi__conversion_object_type__c], [pi__url__c], [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], [Amount], FORMAT([CreatedDate], 'yyyy-MM-dd hh:mm:ss') as [CreatedDate], FORMAT([CloseDate], 'yyyy-MM-dd') as [CloseDate], [StageName] FROM [Opportunity] WHERE ([CreatedDate] >= '{env:start_extraction_date}') AND ([CreatedDate] <= '{env:end_extraction_date}');
REPLICATE [User] SELECT [Id], [Department], [Name], [UserType] FROM [User];
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], [Field], [DataType], [OldValue], [NewValue] FROM [LeadHistory] WHERE (((([Field] = 'Status') OR ([Field] = 'Rating')) OR ([Field] = 'Owner')) AND ([DataType] != 'EntityId')) AND ([NewValue] IS NOT NULL);

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 Scenario

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], [Amount], FORMAT([CreatedDate], 'yyyy-MM-dd hh:mm:ss') as [CreatedDate], FORMAT([CloseDate], 'yyyy-MM-dd') as [CloseDate], [StageName], [CurrencyIsoCode] 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

ProspectAccounts

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
BillingCountry Details for the billing address of this account. text
BillingState Details for the billing address of this account. text
CreatedAt Date in which the account was created. datetime time based filter
Industry An industry associated with this account. text
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
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
CreatedDate Date in which the campaign was created. datetime
CreatedById User ID who created the campaign. text
Name Name of the campaign. text
OwnerId ID of the user who owns this campaign. 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 pick list. 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 lead.

Field Description Data Type Filtering
Id Lead Identifier (Primary Key). text
ConvertedContactId Object reference ID that points to the Contact into which the converted lead has been associated. text
pi__conversion_object_type__c Indicates the medium through which the lead was acquired. text
pi__url__c Indicates the specific URL through which the lead was acquired. 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 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 not null
OldValue Value before change. text

LifecycleHistories

History for tracked Lifecycle fields.

Field Description Data Type Filtering
Id Lifecycle history Identifier (Primary Key). text
ProspectId Related Prospect Record Id. text
PreviousStageId The initial stage in the lifecycle the Prospect was in. text
NextStageId The stage in the lifecycle the Prospect was updated to. text in ('25114','25117','25120','25123')
CreatedAt Moment of the registers creation. datetime time based filter

LifecycleStages

Represents the different stages in a standard Pardot process.

Field Description Data Type Filtering
Id Lifecycle Stage Identifier (Primary Key). text
Name Name of the Stage associated to the identifier text

Opportunity

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
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
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
StageName Current stage of this record. The StageName field controls several other fields on an opportunity. text

Prospects (Main object)

Represents a Prospect tracked by Pardot.

Field Description Data Type Filtering
Id Prospect identifier (Primary Key). text
FirstName First name of the Prospect. text
LastName Last name of the Prospect. text
CampaignId ID of the associated campaign to which the Prospect is assigned. text
Country Country in which the prospect resides. text
City City in which the Prospect resides. text
CrmLeadFid Id of the associated Salesforce Lead related to the Prospect. text
CrmContactFid Id of the associated Salesforce Contact related to the Prospect. text
CrmOwnerFid Id of the associated Salesforce Owner that handles the Prospect. text
CrmLastSync Datetime of the last sync between Salesforce and Pardot objects. datetime
Department Prospects department in the company. text
ProspectAccountId ID of the Prospects associated Account. text
CreatedAt Datetime of the register creation. datetime time based filter
State State in which the Prospect resides. text
Source Pardot source which attained the prospect. text
Score Pardot Score of the Prospect. number

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

Visitors

Represents a Visitor tracked by Pardot.

Field Description Data Type Filtering
Id Visitor identifier (Primary Key). text
CreatedAt Datetime the Visitor was created. datetime time-based filter
ProspectId Identifies associated Prospect. text

VisitorActivities

History of activities for tracked Prospects.

Field Description Data Type Filtering
Id Activity identifier (Primary Key). text
VisitorId Related Visitor record ID. text not null
ProspectId Related Prospect record ID. text not null
Type Specifies the type of action executed. text
TypeName Specifies the source where the action was executed text in ('Custom Redirect', 'Email', 'Email Tracker', 'File', 'Form', 'Form Handler', 'Form Tracker', 'Landing Page', 'Landing page tracker', 'Multivariate Landing Page', 'Paid Ad Search', 'Site Search Query', 'Visit Tracker')
CampaignName Name of the associated Campaign. text
CreatedAt Datetime the register was created. datetime time-based filter

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
Prospect Prospect, Account, Lead, Opportunity, User, Currency_type, Dated_conversion_rate
Visitor Visitor,Visitor_activities
Lead Lead
Campaign Campaign

The prospects are used as input for the Cases_base table containing the following fields:

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.
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 based on the source of the prospect.
Case_value Double A monetary value related to the case. Shown in corporate currency.
Transaction_currency* Text Original transaction currency.
Case_owner* Text Owner of the associated case.
Account* Text Account associated to the Prospect.
Account_billing_country* Text Billing country of the cases account.
Account_billing_state* Text Billing state of the cases account.
Account_industry* Text Industry of the cases associated account.
Account_rating* Text Rating of the cases associated account.
Account_type* Text Classification of the cases associated account.
Campaign_id* Integer Identifier of the cases associated Campaign.
City* Text City of the associated case.
Converted_from* Text Medium through which the case converted into a Prospect.
Corporate_currency* Text The Salesforce selected standard currency.
Country* Text Country of the associated case.
Crm_contact_fid* Text Identifier of the cases associated Contact.
Crm_lead_fid* Text Identifier of the cases associated Lead.
Department* Text Department of the associated case.
Original_amount* Double Original valuation of the case.
Prospect_account_id* Integer Identifier of the cases associated account.
Score* Integer Pondered rating of the Prospect.
State* Text The State in which the case is located.
Unsynchronized_prospect* Boolean Identifies cases that haven't synched to CRM.

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

Activities

Entity Activity Description
Visitor Create Visitor Visitor is generated in Pardot. Identified based on the CreatedAt field on the Visitor object.
Lead Create Lead 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 ProspectId.
Lead Status Change* Lead, associated to the Prospect, 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.
Visitor/Prospect Visitor activity* The Prospect interacts with the Pardot process. The actual name of the activity varies from the type of action committed and the source of the interaction, which are defined in the seed file based on Pardot standards.
Prospect Assigned Prospect (MQLs) The Prospect is assigned to a Sales Rep, thus advancing to the MQL stage.
Prospect Assigned Opportunities (SQLs) The Prospect is converted into an Opportunity, thus advancing to the SQL stage.
Prospect Opportunity Won The Opportunity assigned to the Prospect is closed successfully, thus completing the process through an Opportunity Won stage.
Prospect Opportunity Lost The Opportunity assigned to the Prospect is closed successfully, thus completing the process through an Opportunity Lost stage.

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

Customizing the transformations

Seeds

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_visitors boolean CDATA may have issues extracting Visitors table from Salesforce. If it comes empty, set this variable to false. When it is false, the connector recreates the Visitors table from Visitor_Activities table.
use_campaign_create_events boolean There can be clients where Campaigns are created too long before the Prospects are generated; or it can also happen that there are Campaigns that last too long. Adding the Create Campaign activity to the case will generate throughput times that are not representative of the actual process, and for those cases it is suggested to turn it off by setting the variable to 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.
delta_time_variation float Time in minutes that can be added or subtracted from Pardot table's timestamps. The current version of Pardot stores timestamps in their tables based on the timezone of the Pardot user. Therefore, it could happen that Pardot tables have a different timezone than Salesforce tables. Pardot also has a known issue where timestamps are updated in history tables without any reason. This variable can be used for fixing this inconsistencies.

Limitations, known issues, common problems

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.

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. However, the connector needs to be adapted to use these new objects.

Known issues

Updated timestamps in Pardot tables Pardot may overwrites information previously recorded. Timestamps from records created in a certain date are updated to the current date. This is an error and it is not the intended functionality of Pardot, but it poses a issue when trying to recreate the actual sequence of the process.

Visitor entity Currently the CData Sync extractor SalesforcePardot Provider Version: 21.0.8137.0 is not retrieving records for the Visitor table. This connector has the option to use the Visitor table if a new version retrieve records or generate the entity using the VisitorActivities table.

Common problems

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 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.

Timezone discrepancy

Pardot uses the User's timezone to record timestamps in their tables. Should the user have a different timezone than Salesforce itself, then a discrepancy is generated between events generated in Pardot vs those coming from Salesforce. Please refer to the Connector Setup section to understand how to use delta_time_variation variable to accommodate for this issue.

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.