Salesforce Lead Management

Process description

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.

Available tags & due dates

The following tags are available by default.

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.
Multiple users for same activity A prospect has the same activtiy executed multiple times by different users.

There are no due dates defined for this app template.

Extraction

This app template uses Salesforce Sales Cloud as source system with the Salesforce Pardot package and the Lightning App enabled.

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.

Source system

To be able to extract data from Salesforce the Salesforce Pardot package and the Lightning App should be enabled. 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 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.

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

For the user, the following permissions are needed:

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

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

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, DatedConversionRate objects are by default disabled in Salesforce orgs. By default, these will not be considered in the transformations. If the feature Effective dated currency is enabled in the Salesforce org, the transformation and related logic can be enabled by using DBT variables. Alternatively, the information for this feature can also be loaded manually using seed files.

Setting up the source connection in CData Sync

In order to extract the data from Salesforce, two connections need to be defined in CData Sync, one for Salesforce Pardot and one for Salesforce.

Salesforce Pardot

Salesforce

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 be able to extract the data, two jobs need to be set up in CData Sync, one for Salesforce Pardot and one for Salesforce.

Salesforce Pardot

Advanced job settings

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

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.

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.

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.

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.

Salesforce

Advanced job settings

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

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.

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 app template uses two different jobs to fetch all the data, both jobs need extra configuration:

Triggering the second job from the first job

Step Action
1 Go to the Events tab in the Job Settings panel of the first job
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 Replace EXTRACTION_JOB_2 with the name of the second job you created.
4 Click Save. 
<!-- Start Executing different Job -->
<api:set attr="job.JobName"        value="EXTRACTION_JOB_2"/> 
<api:set attr="job.ExecutionType"  value="Run"/> 
<api:set attr="job.WaitForResults" value="true"/> 
<api:call op="syncExecuteJob" in="job"/>

Calling the End of Upload API in the second job

Step Action
1 Go to the Events tab in the Job Settings panel of the second job.
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 Save. 
<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 the scenario where Multi Currency and Dated Conversion Rates are not used and are disabled. In case it 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

Input tables & fields

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

Configuring transformations

Seeds

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

Automated_users_raw

This file is used to set the users that are automated.

Field Type Description
User Text User that is considered automated.

Visitor_activity_names_raw

This file is used to set properties of the visitor activities.

Field Type Description
Type Integer Type of the visitor activity.
Name Text Name of the visitor activity.
Activity_order Integer How the visitor activities are ordered.

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.

Design specifications

Objects

The following diagram shows all objects that were used for the process. Only the Visitors, Prospects, and Leads are available in the dashboards.

Object are joined to additional master data in order to provide additional information.

Object Input Data
Prospect Prospect, Account, Lead, Opportunity, User, Currency_type, Dated_conversion_rate
Visitor Visitor,Visitor_activities
Lead Lead
Campaign Campaign
Campaign member Campaign member

Activities

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

Design details

Limitations

Known issues

Common problems

General
Object Specific