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.
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 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.
| 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 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.
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.
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.
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.
Define the following settings in the Replicate Options section in the Advanced tab in the Job Settings panel.
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.
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_raw] SELECT [Id], [BillingCountry], [BillingState], [CreatedAt],
[Industry], [Name], [Rating], [Type] FROM [ProspectAccounts] WHERE ([CreatedAt] >= '{env:start_extraction_date}') AND ([CreatedAt] <= '{env:end_extraction_date}');
REPLICATE [LifecycleStages_raw] SELECT [Id], [Name] FROM [LifecycleStages];
REPLICATE [LifecycleHistories_raw] WITH ReplicateStartDate = '{env:start_extraction_date}', replicateenddate = '{env:end_extraction_date}' SELECT [Id], [CreatedAt], [NextStageId], [PreviousStageId], [ProspectId] FROM [LifecycleHistories] WHERE [NextStageId] IN ('25114', '25117', '25120', '25123');
REPLICATE [Visitors_raw] SELECT [Id], [CreatedAt], [ProspectId] FROM [Visitors] WHERE ([CreatedAt] >= '{env:start_extraction_date}') AND ([CreatedAt] <= '{env:end_extraction_date}');
REPLICATE [VisitorActivities_raw] WITH ReplicateStartDate = '{env:start_extraction_date}', replicateenddate = '{env:end_extraction_date}' SELECT [Id], [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_raw] SELECT [Id], [CreatedAt], [FirstName], [LastName], [CampaignId], [Country], [City], [CrmLeadFid], [CrmContactFid], [CrmOwnerFid], [CrmAccountFid], [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.
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.
Define the following settings in the Replicate Options section in the Advanced tab in the Job Settings panel.
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.
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_raw] SELECT [Id], [CreatedById], [CreatedDate], [Name], [OwnerId], [Status], [Type] FROM [Campaign];
REPLICATE [CampaignMember_raw] SELECT [Id], [CampaignId], [CreatedById], [CreatedDate], [LeadId], [Status] FROM [CampaignMember] WHERE ([CreatedDate] >= '{env:start_extraction_date}') AND ([CreatedDate] <= '{env:end_extraction_date}');
REPLICATE [Lead_raw] SELECT [Id], [ConvertedContactId], [pi__conversion_object_type__c], [pi__url__c], [ConvertedOpportunityId], [CreatedById], [CreatedDate], [Name], [OwnerId] FROM [Lead] WHERE ([CreatedDate] >= '{env:start_extraction_date}') AND ([CreatedDate] <= '{env:end_extraction_date}');
REPLICATE [Opportunity_raw] SELECT [Id], [Amount], [CreatedDate], [CloseDate], [StageName] FROM [Opportunity] WHERE ([CreatedDate] >= '{env:start_extraction_date}') AND ([CreatedDate] <= '{env:end_extraction_date}');
REPLICATE [User_raw] SELECT [Id], [Department], [Name], [UserType] FROM [User];
REPLICATE [LeadHistory_raw] WITH ReplicateStartDate = '{env:start_extraction_date}', replicateenddate = '{env:end_extraction_date}' SELECT [Id], [LeadId], [CreatedById], [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_raw] SELECT [Id], [IsCorporate], [IsoCode] FROM [CurrencyType] WHERE [IsCorporate] = 1;
REPLICATE [DatedConversionRate_raw] SELECT [Id], [IsoCode], [NextStartDate], [StartDate], [ConversionRate] FROM [DatedConversionRate];
Additionally, the Opportunity query must be changed to the following to add the CurrencyIsoCode field in the query.
REPLICATE [Opportunity_raw] SELECT [Id], [Amount], [CreatedDate], [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 |
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.
| 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.
Automated_users_raw file contains a list of users which are considered automated.Visitor_activity_names_raw file contains Type that is the type of visitor activity, Name that is how the visitor activities types are going to be translated and Activity_order for those activities.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. |
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.
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.
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.
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.