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.
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.
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.
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.
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.
The user must be marked as "Marketing User" in the user settings in order to extract Campaign information.
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.
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.
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.
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.
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.
In the Advanced tab in the Job Settings panel, define the following settings:
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.
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.
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.
In the Advanced tab in the Job Settings panel, define the following settings:
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.
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"/>
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 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}');
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.
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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) |
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 |
This file is used to set the users that are automated.
| Field | Type | Description |
|---|---|---|
| User | Text | User that is considered automated. |
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. |
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. |
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 |
| 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.
CurrencyISOCode check the specific issues in the following section.delta_time_variation variable to accommodate for this issue. 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.