The Lead-to-Cash process is an end to end process which starts with a potential customer's intent to buy a product. After a sales rep finalizes a quote and orders it within Salesforce CPQ, Salesforce Billing picks up the order record for invoicing and payment.
Lead Qualification
Once a lead is generated, the lead will go through a qualification process. The objective of this process is to determine whether the lead is most likely to make an actual purchase. Once the lead is qualified this will turn into an actual opportunity for a sale. In Salesforce, this is tracked via the Lead object.
Opportunity Management
As a lead qualifies into an opportunity, the sales team will work on the opportunity together with the potential customer, in order to identify the need and suggest an appropriate product or service which will meet this need. In case the opportunity is won, this will generate one or more orders in order to fulfill the won opportunity. In Salesforce, this is tracked via the Opportunity object, and the Quote object.
Quote Management
Once an opportunity moves forward with a customer, products and services will be offered through quotes. In Salesforce CPQ the Quote object contains information about the products and services a customer wants to buy. You can create as many quotes as you want on a given opportunity. However, many quotes your opportunity contains, only one can be designated as primary.
Order Management
Within Order management, the order lifecycle is maintained, including order capture, and fulfillment. In Salesforce, the Order object is used to maintain this process. One quote may have multiple orders if the configuration is set in CPQ package.
Contract Management
Contracts are used when orders included at least one subscription product under a contract-based renewal model. Salesforce CPQ creates a contract record for them.
Invoice Management
The billing process begins when you invoice your order. When this action occurs, Salesforce Billing creates an Invoice record that contains invoice lines for each of your products from the Order. The invoice balance could be managed through credit and debit notes.
Payment Management
The collection of payments is possible once the invoices are posted. Salesforce Billing lets you collect and allocate payments to invoice lines.
The following tags are available by default.
| Tag | Description |
|---|---|
| Approval process started multiple times | Multiple approvals may exist for one object, however if the same approval process is triggered multiple times, there might be inefficiencies to review. Applies for all objects (Lead, Opportunity, Quote, Order, Contract). |
| Multiple Owner Changes | Applies for all objects (Lead, Opportunity, Quote, Order, Contract), reflects that the owner of that object has been changed multiple times and that may be justified (vacations, escalations) or may require attention. |
| Primary quote changed multiple times | The primary quote in an opportunity is the one that can turn into an order. There might be inefficiencies to review if these changes happen constantly. |
| Credit Notes allocated to Invoices | Processes that involve Credit Notes could potentially mean that errors were committed during the invoicing or order generation. It can also be part of the normal operation of the company as it may be related to rebates, write-offs or any other type of discount. |
| Debit Notes allocated to Invoices | Processes that involve Debit Notes usually mean that a mistake was made during the invoicing or order generation. This type of activities should be reviewed and check to whether they should be happening. |
| Credit and Debit Notes allocated to each other | When a Credit Note is allocated against a Debit Note (or the other way around) it means that they are cancelling each other. This procedure should only happen when mistakes were made and further analysis may be required to avoid them. |
| Unpaid Order Items | This tag lists all cases where Invoices were not fully paid and that they are overdue. |
| Top 20% unpaid Order Items by Amount | This tag is similar to 'Unpaid Order Item' but it is only selecting the top 20% by Amount of those cases |
| Top 20% unpaid Order Items by Days Past Due | This tag is similar to 'Unpaid Order Item' but it is only selecting the top 20% by days past due of those cases |
| Cases with unallocations | Applies to all those cases where any type of unallocation has happened. Unallocations are a good marker for activities that do not add any value and are probably happening as a result of a mistake made. |
| Multiple users for same activity | An order item has the same activtiy executed multiple times by different users. |
The following due dates are available by default.
| Due date | Description |
|---|---|
| Invoice Line Payment | This value compares the due date of an Invoice Line and the date when the payment was received. |
This app template uses Salesforce Sales Cloud as source system with the CPQ and Billing packages installed in the Salesforce Org. Salesforce CPQ managed package simplifies configuration and ensures pricing and quoting accuracy. Salesforce Billing is an add-on package that inherits key records and information from Salesforce CPQ and lets you invoice an order and manage its balances through payments, credit and debit notes.
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 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.
To allow users to create multiple orders from a quote, "Allow Multiple Orders" in CPQ Order Package Settings must be selected.
To allow users to allocate payments to invoice lines, Advanced AR Application should be enabled in Billing (Invoice package settings).
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.
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-to-Cash app template works on the process starting on the marketing campaign generation, up to the generation of customer orders. If the order includes subscription products the process adds contract to track the subscriptions.
This includes processing of the following Salesforce transactional objects:
Additionally, the following additional objects are used to add additional information:
In order to allow Currency conversion and to calculate amounts using historical exchange rates, Effective dated currency should be enabled in the Salesforce org, and historical exchange rates should be loaded into the system. The following object will become available after the feature is enabled:
*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.
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 create the Job in CData make sure to follow the steps below.
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 should be used for scenarios where Multi Currency, Dated Conversion Rates, Person Accounts and Record types are not used and are disabled. In case any of these are used, please update the queries as described in the Alternative Scenarios section.
REPLICATE [Account] SELECT [Id], [AccountSource], [BillingState], [BillingCountry], [CreatedById], FORMAT([CreatedDate], 'yyyy-MM-dd hh:mm:ss') as [CreatedDate], [Industry], [Name], [OwnerId], [Ownership], [ParentId], [Rating], [Type] FROM [Account];
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 [Campaign] SELECT [Id], [CreatedById], FORMAT([CreatedDate], 'yyyy-MM-dd hh:mm:ss') as [CreatedDate], [Name], [OwnerId], [ParentId], [Status], [Type] FROM [Campaign] WHERE ([CreatedDate] >= '{env:start_extraction_date}') AND ([CreatedDate] <= '{env:end_extraction_date}');
REPLICATE [Contract] SELECT [Id], [ContractNumber], [CreatedById], FORMAT([CreatedDate], 'yyyy-MM-dd hh:mm:ss') as [CreatedDate], [OwnerId] FROM [Contract] WHERE ([CreatedDate] >= '{env:start_extraction_date}') AND ([CreatedDate] <= '{env:end_extraction_date}');
REPLICATE [ContractHistory] WITH ReplicateStartDate = '{env:start_extraction_date}', replicateenddate = '{env:end_extraction_date}' SELECT [Id], [ContractId], [CreatedById], FORMAT([CreatedDate], 'yyyy-MM-dd hh:mm:ss') as [CreatedDate], [Field], [NewValue], [OldValue], [DataType] FROM [ContractHistory] WHERE [Field] IN ('Status', 'Owner', 'ContractTerm') AND [DataType] != 'EntityId';
REPLICATE [blng__CreditNote__History] WITH ReplicateStartDate = '{env:start_extraction_date}', replicateenddate = '{env:end_extraction_date}' SELECT [Id], [ParentId], [CreatedById], FORMAT([CreatedDate], 'yyyy-MM-dd hh:mm:ss') as [CreatedDate], [Field], [DataType], [OldValue], [NewValue] FROM [blng__CreditNote__History] WHERE [Field] IN ('blng__Status__c');
REPLICATE [blng__CreditNote__c] WITH ReplicateStartDate = '{env:start_extraction_date}', replicateenddate = '{env:end_extraction_date}' SELECT [Id], [CreatedById], FORMAT([CreatedDate], 'yyyy-MM-dd hh:mm:ss') as [CreatedDate], [Name] FROM [blng__CreditNote__c];
REPLICATE [blng__CreditNoteAllocation__c] WITH ReplicateStartDate = '{env:start_extraction_date}', replicateenddate = '{env:end_extraction_date}' SELECT [Id], [blng__Amount__c], [CreatedById], FORMAT([CreatedDate], 'yyyy-MM-dd hh:mm:ss') as [CreatedDate], [blng__CreditNoteLine__c], [blng__InvoiceLine__c], [blng__Type__c] FROM [blng__CreditNoteAllocation__c];
REPLICATE [blng__CreditNoteLine__History] WITH ReplicateStartDate = '{env:start_extraction_date}', replicateenddate = '{env:end_extraction_date}' SELECT [Id], [ParentId], [CreatedById], FORMAT([CreatedDate], 'yyyy-MM-dd hh:mm:ss') as [CreatedDate], [Field], [DataType], [OldValue], [NewValue] FROM [blng__CreditNoteLine__History] WHERE [Field] IN ('blng__Status__c');
REPLICATE [blng__CreditNoteLine__c] WITH ReplicateStartDate = '{env:start_extraction_date}', replicateenddate = '{env:end_extraction_date}' SELECT [Id], [CreatedById], FORMAT([CreatedDate], 'yyyy-MM-dd hh:mm:ss') as [CreatedDate], [blng__CreditNote__c], [Name] FROM [blng__CreditNoteLine__c];
REPLICATE [blng__DebitNote__History] WITH ReplicateStartDate = '{env:start_extraction_date}', replicateenddate = '{env:end_extraction_date}' SELECT [Id], [ParentId], [CreatedById], FORMAT([CreatedDate], 'yyyy-MM-dd hh:mm:ss') as [CreatedDate], [Field], [DataType], [OldValue], [NewValue] FROM [blng__DebitNote__History] WHERE [Field] IN ('blng__Status__c');
REPLICATE [blng__DebitNote__c] WITH ReplicateStartDate = '{env:start_extraction_date}', replicateenddate = '{env:end_extraction_date}' SELECT [Id], [CreatedById], FORMAT([CreatedDate], 'yyyy-MM-dd hh:mm:ss') as [CreatedDate], [Name] FROM [blng__DebitNote__c];
REPLICATE [blng__DebitNoteAllocation__c] WITH ReplicateStartDate = '{env:start_extraction_date}', replicateenddate = '{env:end_extraction_date}' SELECT [Id], [blng__Amount__c], [CreatedById], FORMAT([CreatedDate], 'yyyy-MM-dd hh:mm:ss') as [CreatedDate], [blng__DebitNoteLine__c], [blng__InvoiceLine__c], [blng__Type__c] FROM [blng__DebitNoteAllocation__c];
REPLICATE [blng__DebitNoteAllocationCreditNoteLine__c] WITH ReplicateStartDate = '{env:start_extraction_date}', replicateenddate = '{env:end_extraction_date}' SELECT [Id], [blng__Amount__c], [CreatedById], FORMAT([CreatedDate], 'yyyy-MM-dd hh:mm:ss') as [CreatedDate], [blng__CreditNoteLine__c], [blng__DebitNoteLine__c], [blng__Type__c] FROM [blng__DebitNoteAllocationCreditNoteLine__c];
REPLICATE [blng__DebitNoteLine__History] WITH ReplicateStartDate = '{env:start_extraction_date}', replicateenddate = '{env:end_extraction_date}' SELECT [Id], [ParentId], [CreatedById], FORMAT([CreatedDate], 'yyyy-MM-dd hh:mm:ss') as [CreatedDate], [Field], [DataType], [OldValue], [NewValue] FROM [blng__DebitNoteLine__History] WHERE [Field] IN ('blng__Status__c');
REPLICATE [blng__DebitNoteLine__c] WITH ReplicateStartDate = '{env:start_extraction_date}', replicateenddate = '{env:end_extraction_date}' SELECT [Id], [CreatedById], FORMAT([CreatedDate], 'yyyy-MM-dd hh:mm:ss') as [CreatedDate], [blng__DebitNote__c], [Name] FROM [blng__DebitNoteLine__c];
REPLICATE [blng__Invoice__History] WITH ReplicateStartDate = '{env:start_extraction_date}', replicateenddate = '{env:end_extraction_date}' SELECT [Id], [ParentId], [CreatedById], FORMAT([CreatedDate], 'yyyy-MM-dd hh:mm:ss') as [CreatedDate], [Field], [DataType], [OldValue], [NewValue] FROM [blng__Invoice__History] WHERE [Field] IN ('blng__InvoiceStatus__c');
REPLICATE [blng__Invoice__c] WITH ReplicateStartDate = '{env:start_extraction_date}', replicateenddate = '{env:end_extraction_date}' SELECT [Id], [CreatedById], FORMAT([CreatedDate], 'yyyy-MM-dd hh:mm:ss') as [CreatedDate], [Name] FROM [blng__Invoice__c];
REPLICATE [blng__InvoiceLine__History] WITH ReplicateStartDate = '{env:start_extraction_date}', replicateenddate = '{env:end_extraction_date}' SELECT [Id], [ParentId], [CreatedById], FORMAT([CreatedDate], 'yyyy-MM-dd hh:mm:ss') as [CreatedDate], [DataType], [Field], [OldValue], [NewValue] FROM [blng__InvoiceLine__History];
REPLICATE [blng__InvoiceLine__c] WITH ReplicateStartDate = '{env:start_extraction_date}', replicateenddate = '{env:end_extraction_date}' SELECT [Id], [blng__Balance__c], [CreatedById], FORMAT([CreatedDate], 'yyyy-MM-dd hh:mm:ss') as [CreatedDate], [blng__DaysPastDue__c], FORMAT([blng__DueDate__c], 'yyyy-MM-dd') as [blng__DueDate__c], [blng__Invoice__c], [Name], [blng__OrderProduct__c] FROM [blng__InvoiceLine__c];
REPLICATE [LeadHistory] WITH ReplicateStartDate = '{env:start_extraction_date}', replicateenddate = '{env:end_extraction_date}' SELECT [Id], [CreatedById], FORMAT([CreatedDate], 'yyyy-MM-dd hh:mm:ss') as [CreatedDate], [Field], [LeadId], [NewValue], [OldValue], [DataType] FROM [LeadHistory] WHERE [Field] IN ('Status', 'Rating', 'Owner') AND [DataType] != 'EntityId';
REPLICATE [Lead] SELECT [Id], [ConvertedOpportunityId], [CreatedById], FORMAT([CreatedDate], 'yyyy-MM-dd hh:mm:ss') as [CreatedDate], [Name], [OwnerId] FROM [Lead] WHERE ([CreatedDate] >= '{env:start_extraction_date}') AND ([CreatedDate] <= '{env:end_extraction_date}');
REPLICATE [Opportunity] SELECT [Id], [CreatedById], FORMAT([CreatedDate], 'yyyy-MM-dd hh:mm:ss') as [CreatedDate], [Name] FROM [Opportunity] WHERE ([CreatedDate] >= '{env:start_extraction_date}') AND ([CreatedDate] <= '{env:end_extraction_date}');
REPLICATE [OpportunityFieldHistory] WITH ReplicateStartDate = '{env:start_extraction_date}', replicateenddate = '{env:end_extraction_date}' SELECT [Id], [CreatedById], FORMAT([CreatedDate], 'yyyy-MM-dd hh:mm:ss') as [CreatedDate], [Field], [NewValue], [OldValue], [OpportunityId], [DataType] FROM [OpportunityFieldHistory] WHERE [Field] IN ('Amount', 'StageName', 'Owner', 'CloseDate') AND [DataType] != 'EntityId';
REPLICATE [Order] SELECT [Id], [AccountId], [CreatedById], FORMAT([CreatedDate], 'yyyy-MM-dd hh:mm:ss') as [CreatedDate], [Name], [OpportunityId], [OrderNumber], [Type], [OwnerId], [SBQQ__PaymentTerm__C], [SBQQ__Quote__c], [ShippingCountry], [ShippingState] FROM [Order] WHERE ([CreatedDate] >= '{env:start_extraction_date}') AND ([CreatedDate] <= '{env:end_extraction_date}');
REPLICATE [OrderHistory] WITH ReplicateStartDate = '{env:start_extraction_date}', ReplicateEndDate = '{env:end_extraction_date}' SELECT [Id], [CreatedById], FORMAT([CreatedDate], 'yyyy-MM-dd hh:mm:ss') as [CreatedDate], [Field], [NewValue], [OldValue], [OrderId], [DataType] FROM [OrderHistory] WHERE [Field] IN ('Status', 'Owner', 'TotalAmount') AND [DataType] != 'EntityId';
REPLICATE [OrderItem] SELECT [Id], [SBQQ__BillingFrequency__c], [SBQQ__ChargeType__c], [SBQQ__Contract__c], [CreatedById], FORMAT([CreatedDate], 'yyyy-MM-dd hh:mm:ss') as [CreatedDate], [OrderId], [OrderItemNumber], [SBQQ__Status__c], [Product2Id], [Quantity], [SBQQ__SubscriptionTerm__c], [SBQQ__DefaultSubscriptionTerm__c], [TotalPrice] FROM [OrderItem] WHERE ([CreatedDate] >= '{env:start_extraction_date}') AND ([CreatedDate] <= '{env:end_extraction_date}');
REPLICATE [blng__Payment__History] WITH ReplicateStartDate = '{env:start_extraction_date}', replicateenddate = '{env:end_extraction_date}' SELECT [Id], [ParentId], [CreatedById], FORMAT([CreatedDate], 'yyyy-MM-dd hh:mm:ss') as [CreatedDate], [Field], [DataType], [OldValue], [NewValue] FROM [blng__Payment__History] WHERE [Field] IN ('blng__Status__c');
REPLICATE [blng__Payment__c] WITH ReplicateStartDate = '{env:start_extraction_date}', replicateenddate = '{env:end_extraction_date}' SELECT [Id], [CreatedById], FORMAT([CreatedDate], 'yyyy-MM-dd hh:mm:ss') as [CreatedDate], [blng__PaymentDate__c] FROM [blng__Payment__c];
REPLICATE [blng__PaymentAllocationDebitNoteLine__c] WITH ReplicateStartDate = '{env:start_extraction_date}', replicateenddate = '{env:end_extraction_date}' SELECT [Id], [blng__Amount__c], [CreatedById], FORMAT([CreatedDate], 'yyyy-MM-dd hh:mm:ss') as [CreatedDate], [blng__DebitNoteLine__c], [blng__Payment__c], [blng__Type__c] FROM [blng__PaymentAllocationDebitNoteLine__c];
REPLICATE [blng__PaymentAllocationInvoiceLine__c] WITH ReplicateStartDate = '{env:start_extraction_date}', replicateenddate = '{env:end_extraction_date}' SELECT [Id], [blng__Amount__c], [CreatedById], FORMAT([CreatedDate], 'yyyy-MM-dd hh:mm:ss') as [CreatedDate], [blng__InvoiceLine__c], [blng__Payment__c], [blng__Type__c], [blng__Unallocated__c] FROM [blng__PaymentAllocationInvoiceLine__c];
REPLICATE [ProcessDefinition] SELECT [Id], [Name], [TableEnumOrId], [Type] FROM [ProcessDefinition] WHERE [Type] = 'Approval';
REPLICATE [ProcessInstance] SELECT [Id], [CreatedById], FORMAT([CreatedDate], 'yyyy-MM-dd hh:mm:ss') as [CreatedDate], [ProcessDefinitionId], [Status], [TargetObjectId] FROM [ProcessInstance] WHERE ([CreatedDate] >= '{env:start_extraction_date}') AND ([CreatedDate] <= '{env:end_extraction_date}');
REPLICATE [ProcessInstanceStep] SELECT [Id], [Comments], [CreatedById], FORMAT([CreatedDate], 'yyyy-MM-dd hh:mm:ss') as [CreatedDate], [ProcessInstanceId], [StepStatus] FROM [ProcessInstanceStep] WHERE ([CreatedDate] >= '{env:start_extraction_date}') AND ([CreatedDate] <= '{env:end_extraction_date}');
REPLICATE [Product2] SELECT [Id], [Name], [ProductCode], [Family], [QuantityUnitOfMeasure] FROM [Product2];
REPLICATE [SBQQ__Quote__History] WITH ReplicateStartDate = '{env:start_extraction_date}', replicateenddate = '{env:end_extraction_date}' SELECT [Id], [ParentId], [CreatedById], FORMAT([CreatedDate], 'yyyy-MM-dd hh:mm:ss') as [CreatedDate], [Field], [NewValue], [OldValue], [DataType] FROM [SBQQ__Quote__History] WHERE ([DataType] != 'EntityId') AND (([Field] IN ('SBQQ__Primary__c', 'SBQQ__Status__c', 'SBQQ__SubscriptionTerm__c')) OR (([Field] IN ('SBQQ__SalesRep__c', 'SBQQ__ExpirationDate__c')) AND ([OldValue] IS NOT NULL)));
REPLICATE [SBQQ__Quote__c] SELECT [Id], [SBQQ__Opportunity2__c], [CreatedById], FORMAT([CreatedDate], 'yyyy-MM-dd hh:mm:ss') as [CreatedDate], [Name] FROM [SBQQ__Quote__c] WHERE ([CreatedDate] >= '{env:start_extraction_date}') AND ([CreatedDate] <= '{env:end_extraction_date}');
REPLICATE [RecordType] SELECT [Id], [Name] FROM [RecordType];
REPLICATE [User] SELECT [Id], [Department], [Name], [UserType] FROM [User];
Note regarding CData Salesforce Provider Version 21.0.8097.0
When a table has an _Incremental Check Column_ field predefined in CData Sync, the SQL query cannot use the same date field in both statements (WITH and WHERE) at the same time.
If version 21.0.8097.0 (or an older version) of CData is used, the date filtering should be done in the WHERE clause and should be removed from the WITH statement.
Alternative Scenarios
The following scenarios can be used in combination with each other:
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 Payment Allocation Debit Note Line, Payment Allocation Invoice Line and Order Item query must be changed to the following to add the CurrencyIsoCode field in the query.
REPLICATE [blng__PaymentAllocationDebitNoteLine__c] WITH ReplicateStartDate = '{env:start_extraction_date}', replicateenddate = '{env:end_extraction_date}' SELECT [Id], [blng__Amount__c], [CreatedById], FORMAT([CreatedDate], 'yyyy-MM-dd hh:mm:ss') as [CreatedDate], [CurrencyIsoCode], [blng__DebitNoteLine__c], [blng__Payment__c], [blng__Type__c] FROM [blng__PaymentAllocationDebitNoteLine__c];
REPLICATE [blng__PaymentAllocationInvoiceLine__c] WITH ReplicateStartDate = '{env:start_extraction_date}', replicateenddate = '{env:end_extraction_date}' SELECT [Id], [blng__Amount__c], [CreatedById], FORMAT([CreatedDate], 'yyyy-MM-dd hh:mm:ss') as [CreatedDate], [CurrencyIsoCode], [blng__InvoiceLine__c], [blng__Payment__c], [blng__Type__c], [blng__Unallocated__c] FROM [blng__PaymentAllocationInvoiceLine__c];
REPLICATE [OrderItem] SELECT [Id], [SBQQ__BillingFrequency__c], [SBQQ__ChargeType__c], [SBQQ__Contract__c], [CreatedById], FORMAT([CreatedDate], 'yyyy-MM-dd hh:mm:ss') as [CreatedDate], [CurrencyIsoCode], [OrderId], [OrderItemNumber], [SBQQ__Status__c], [Product2Id], [Quantity], [SBQQ__SubscriptionTerm__c], [SBQQ__DefaultSubscriptionTerm__c], [TotalPrice] FROM [OrderItem] WHERE ([CreatedDate] >= '{env:start_extraction_date}') AND ([CreatedDate] <= '{env:end_extraction_date}');
Person Accounts enabled in Salesforce Org
In Case Person Accounts (field IsPersonAccount) are used - replace the Accounts query with the following query. This query will add the field IsPersonAccount in the Accounts extraction.
REPLICATE [Account] SELECT [Id], [AccountSource], [BillingState], [BillingCountry], [CreatedById], FORMAT([CreatedDate], 'yyyy-MM-dd hh:mm:ss') as [CreatedDate], [Industry], [IsPersonAccount], [Name], [OwnerId], [Ownership], [ParentId], [Rating], [Type] FROM [Account];
Record Types are enabled in Salesforce Org
In case Record Types are used the Order query must be amended to add the RecordTypeId field in the query.
The following replicate shows Order with RecordTypeId value added.
REPLICATE [Order] SELECT [Id], [AccountId], [CreatedById], FORMAT([CreatedDate], 'yyyy-MM-dd hh:mm:ss') as [CreatedDate], [Name], [OpportunityId], [OrderNumber], [Type], [OwnerId], [SBQQ__PaymentTerm__C], [SBQQ__Quote__c], [RecordTypeId], [ShippingCountry], [ShippingState] FROM [Order] 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 | |
| AccountSource | The source of the account record. For example, Advertisement, Data.com, or Trade Show. The source is selected from a pick list of available values, which are set by an administrator. | text | |
| BillingCountry | Details for the billing address of this account. | text | |
| BillingState | Details for the billing address of this account. | text | |
| CreatedById | User ID who created the account. | text | |
| CreatedDate | Date in which the account was created. | datetime | |
| Industry | An industry associated with this account. | text | |
| IsPersonAccount | Indicates whether this account has a record type of Person Account (true) or not (false). This field is not available by default in Salesforce, but is setup by Salesforce when requested for B2C environments. This attribute can be enabled / disabled using the use_is_person_account dbt variable. |
boolean | |
| 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 | |
| OwnerId | The ID of the user who currently owns this account. | text | |
| Ownership | Ownership type for the account, for example Private, Public, or Subsidiary. | text | |
| ParentId | ID of the parent object, if any. | 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 | |
| CreatedById | User ID who created the campaign. | text | |
| CreatedDate | Date in which the campaign was created. | datetime | time-based filter |
| Name | Name of the campaign. | text | |
| OwnerId | ID of the user who owns this campaign. | text | |
| ParentId | ID of the parent Campaign record, if any. | 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 picklist. | text |
Represents historical information about changes that have been made to the standard fields of the associated Credit Notes, or to any custom fields with history tracking enabled.
| Field | Description | Data Type | Filtering |
|---|---|---|---|
| Id | Credit Note History Identifier (Primary Key). | text | |
| ParentId | Related Credit Note 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 | |
| Field | Field which was modified on the record. | text | in ('blng__Status__c') |
| NewValue | Value after change. | text | |
| OldValue | Value before change. | text |
Contains all Credit notes generated in Billing.
| Field | Description | Data Type | Filtering |
|---|---|---|---|
| Id | Credit Note Identifier (Primary Key). | text | |
| CreatedById | User ID who created the record. | text | |
| CreatedDate | Date in which the record was created. | datetime | time-based filter |
| Name | User friendly ID for the Credit Note | text |
Contains all Credit Notes allocations for Invoices generated in Billing.
| Field | Description | Data Type | Filtering |
|---|---|---|---|
| Id | Credit Note Allocation Identifier (Primary Key). | text | |
| blng__Amount__c | Amount cleared | double | |
| CreatedById | User ID who created the record. | text | |
| CreatedDate | Date in which the record was created. | datetime | time-based filter |
| blng__CreditNoteLine__c | Credit Note Line ID that clears the Invoice Line | text | |
| blng__InvoiceLine__c | Invoice Line ID that gets cleared by the credit note | text | |
| blng__Type__c | Type of allocation | text |
Represents the transactional table that contains all Credit note lines
| Field | Description | Data Type | Filtering |
|---|---|---|---|
| Id | Credit Note Line Identifier (Primary Key). | text | |
| CreatedById | User ID who created the record. | text | |
| CreatedDate | Date in which the record was created. | datetime | time-based filter |
| blng__CreditNote__c | Related Credit Note ID | text | |
| Name | Detail of the Credit Note Line | text |
Represents historical information about changes that have been made to the standard fields of the associated Credit Note Line, or to any custom fields with history tracking enabled.
| Field | Description | Data Type | Filtering |
|---|---|---|---|
| Id | Credit Note Line History Identifier (Primary Key). | text | |
| ParentId | Related Credit Note Line 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 | |
| Field | Field which was modified on the record. | text | in ('blng__Status__c') |
| NewValue | Value after change. | text | |
| OldValue | Value before change. | text |
Represents the subscription products that customers have purchased. The Contract store information on the subscriptions that sales reps have quoted or ordered.
| Field | Description | Data Type | Filtering |
|---|---|---|---|
| Id | Contract Identifier (Primary Key). | text | |
| CreatedById | User ID who created the contract record. | text | |
| CreatedDate | Date in which the contract record was created. | datetime | time-based filter |
| ContractNumber | Auto Number assigned to the Contract once is created. | text | |
| OwnerId | ID of the user who owns this contract. | text |
History for tracked fields of Contract.
| Field | Description | Data Type | Filtering |
|---|---|---|---|
| Id | Contract History Identifier (Primary Key). | text | |
| ContractId | Related Contract 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 contract record. | text | in (Status, Owner, ContractTerm) |
| NewValue | Value after change. | text | |
| OldValue | Value before change. | 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 the transactional information related to Debit Notes at header level.
| Field | Description | Data Type | Filtering |
|---|---|---|---|
| Id | Debit Note Identifier (Primary Key). | text | |
| Name | Detail of the Debit Note | text | |
| CreatedById | User ID who created the record. | text | |
| CreatedDate | Date in which the record was created. | datetime | time-based filter |
Represents historical information about changes that have been made to the standard fields of the associated Debit Note History, or to any custom fields with history tracking enabled.
| Field | Description | Data Type | Filtering |
|---|---|---|---|
| Id | Debit Note History Identifier (Primary Key). | text | |
| ParentId | Related Debit Note 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 | |
| Field | Field which was modified on the record. | text | in ('blng__Status__c') |
| NewValue | Value after change. | text | |
| OldValue | Value before change. | text |
Contains all Debit Notes allocations for Invoices generated in Billing.
| Field | Description | Data Type | Filtering |
|---|---|---|---|
| Id | Debit Note Allocation Identifier (Primary Key). | text | |
| blng__Amount__c | Amount cleared | double | |
| CreatedById | User ID who created the record. | text | |
| CreatedDate | Date in which the record was created. | datetime | time-based filter |
| blng__InvoiceLine__c | Invoice Line ID that gets cleared by the Debit note | text | |
| blng__DebitNoteLine__c | Debit Note Line ID that clears the Invoice Line | text | |
| blng__Type__c | Type of allocation | text |
This table contains both types of allocations: when a debit note clears a credit note, and the other way around.
| Field | Description | Data Type | Filtering |
|---|---|---|---|
| Id | Debit Note or Credit Note Allocation Identifier (Primary Key). | text | |
| blng__Amount__c | Amount cleared | double | |
| CreatedById | User ID who created the record. | text | |
| CreatedDate | Date in which the record was created. | datetime | time-based filter |
| blng__CreditNoteLine__c | Credit Note Line ID that is involved in the allocation | text | |
| blng__DebitNoteLine__c | Debit Note Line ID that is involved in the allocation | text | |
| blng__Type__c | Type of allocation | text |
Represents the transactional table that contains all Debit note lines
| Field | Description | Data Type | Filtering |
|---|---|---|---|
| Id | Debit Note Line Identifier (Primary Key). | text | |
| CreatedById | User ID who created the record. | text | |
| CreatedDate | Date in which the record was created. | datetime | time-based filter |
| blng__DebitNote__c | Related Debit Note ID | text | |
| Name | Detail of the Debit Note Line | text |
Represents historical information about changes that have been made to the standard fields of the associated Debit Note Line, or to any custom fields with history tracking enabled.
| Field | Description | Data Type | Filtering |
|---|---|---|---|
| Id | Debit Note Line History Identifier (Primary Key). | text | |
| ParentId | Related Debit Note Line 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 | |
| Field | Field which was modified on the record. | text | in ('blng__Status__c') |
| NewValue | Value after change. | text | |
| OldValue | Value before change. | text |
Represents the transactional records related to Invoices at header level.
| Field | Description | Data Type | Filtering |
|---|---|---|---|
| Id | Invoice Identifier (Primary Key). | text | |
| CreatedById | User ID who created the record. | text | |
| CreatedDate | Date in which the record was created. | datetime | time-based filter |
| Name | Detail of the Invoice | text |
Represents historical information about changes that have been made to the standard fields of the associated Invoice, or to any custom fields with history tracking enabled.
| Field | Description | Data Type | Filtering |
|---|---|---|---|
| Id | Invoice History Identifier (Primary Key). | text | |
| ParentId | Related Invoice 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 | |
| Field | Field which was modified on the record. | text | in ('blng__InvoiceStatus__c') |
| NewValue | Value after change. | text | |
| OldValue | Value before change. | text |
Represents the transactional table for Invoice Line records.
| Field | Description | Data Type | Filtering |
|---|---|---|---|
| Id | Invoice Line Identifier (Primary Key). | text | |
| blng__Invoice__c | Invoice Identifier for the related Invoice header | text | |
| blng__OrderProduct__c | Order Item Identifier that this Invoice Line is related to. | text | |
| blng__Balance__c | Open amount that is yet to be cleared | double | |
| CreatedById | User ID who created the record. | text | |
| CreatedDate | Date in which the record was created. | datetime | time-based filter |
| blng__DaysPastDue__c | Days past the due date | double | |
| blng__DueDate__c | Due date for the invoice line | date | |
| Name | Detail of the Invoice Line | text |
Represents historical information about changes that have been made to the standard fields of the associated Invoice Line, or to any custom fields with history tracking enabled.
| Field | Description | Data Type | Filtering |
|---|---|---|---|
| Id | Invoice Line History Identifier (Primary Key). | text | |
| ParentId | Related Invoice Line 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 | |
| Field | Field which was modified on the record. | text | in ('blng__InvoiceLineStatus__c') |
| NewValue | Value after change. | text | |
| OldValue | Value before change. | text |
Represents a prospect or lead.
| Field | Description | Data Type | Filtering |
|---|---|---|---|
| Id | Lead Identifier (Primary Key). | 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 | |
| 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 | |
| OldValue | Value before change. | 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 | |
| CreatedById | User ID who created the opportunity record. | text | |
| CreatedDate | Date in which the opportunity record was created. | datetime | time-based filter |
| Name | A name for this opportunity. | text |
Represents the history of changes to the values in the fields of an opportunity.
| Field | Description | Data Type | Filtering |
|---|---|---|---|
| Id | Opportunity History Identifier (Primary Key). | text | |
| OpportunityId | Related Opportunity 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 opportunity record. | text | in (StageName, Amount, Owner, CloseDate) |
| NewValue | Value after change. | text | |
| OldValue | Value before change. | text |
Represents an order associated with a quote, an order record track the products from customers purchases.
| Field | Description | Data Type | Filtering |
|---|---|---|---|
| Id | Order Identifier (Primary Key). | text | |
| CreatedById | User ID who created the order. | text | |
| CreatedDate | Date in which the quote was order. | datetime | time-based filter |
| Name | Name for the order. | text | |
| OpportunityId | Related Opportunity Record Id. | text | |
| SBQQ__Quote__c | ID for the quote associated with the order. | text |
Represents an order product which is a product or service that is provided to a customer according to an associated order for each of the quote lines.
| Field | Description | Data Type | Filtering |
|---|---|---|---|
| Id | ID for the order associated with the order product. | text | |
| SBQQ__BillingFrequency__c | Frequency of invoice generation for a subscription product | text | |
| SBQQ__ChargeType__c | Classification between one-time or recurring billing | text | |
| SBQQ__Contract__c | The contract of the subscription that is created from this order product. | text | |
| CreatedById | User ID who created the Order Item | text | |
| CreatedDate | Date in which the Order Item 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 | |
| OrderId | Related order Record ID. | text | |
| OrderItemNumber | User friendly ID | text | |
| Product2Id | Product ID | text | |
| Quantity | Quantity sold | double | |
| SBQQ__Contract__c | Related Contract ID | text | |
| SBQQ__DefaultSubscriptionTerm__c | Subscription term used by default | double | |
| SBQQ__Status__c | Order item status | text | |
| SBQQ__SubscriptionTerm__c | Subscription term defined for the Order | double | |
| TotalPrice | Total amount of the Order Item | double |
Represents the transactional table for Payment records.
| Field | Description | Data Type | Filtering |
|---|---|---|---|
| Id | Payment Identifier (Primary Key). | text | |
| CreatedById | User ID who created the record. | text | |
| CreatedDate | Date in which the record was created. | datetime | time-based filter |
| blng__PaymentDate__c | Date for which the payment was executed (not the date when it was recorded in the system) | date |
Represents historical information about changes that have been made to the standard fields of the associated Payment, or to any custom fields with history tracking enabled.
| Field | Description | Data Type | Filtering |
|---|---|---|---|
| Id | Payment History Identifier (Primary Key). | text | |
| ParentId | Related Payment 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 | |
| Field | Field which was modified on the record. | text | in ('blng__Status__c') |
| NewValue | Value after change. | text | |
| OldValue | Value before change. | text |
This table contains allocations done for Payments impacting Debit Notes.
| Field | Description | Data Type | Filtering |
|---|---|---|---|
| Id | Payment allocation Identifier (Primary Key). | text | |
| blng__Amount__c | Amount cleared | double | |
| CreatedById | User ID who created the record. | text | |
| CreatedDate | Date in which the 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 | |
| blng__DebitNoteLine__c | Debit Note Line ID that is involved in the allocation | text | |
| blng__Payment__c | Payment ID that is involved in the allocation | text | |
| blng__Type__c | Type of allocation | text |
This table contains allocations done for Payments impacting Invoices.
| Field | Description | Data Type | Filtering |
|---|---|---|---|
| Id | Payment allocation Identifier (Primary Key). | text | |
| blng__Amount__c | Amount cleared | double | |
| CreatedById | User ID who created the record. | text | |
| CreatedDate | Date in which the 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 | |
| blng__InvoiceLine__c | Invoice Line ID that is involved in the allocation | text | |
| blng__Payment__c | Payment ID that is involved in the allocation | text | |
| blng__Type__c | Type of allocation | text |
Represents historical information about changes that have been made to the standard fields of the associated order, or to any custom fields with history tracking enabled.
| Field | Description | Data Type | Filtering |
|---|---|---|---|
| Id | Order History Identifier (Primary Key). | text | |
| OrderId | Related order 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 order record. | text | in (Status, Owner, TotalAmount) |
| NewValue | Value after change. | text | |
| OldValue | Value before change. | text |
Represents the Quote object that stores all the details of the products and prices quoted for an opportunity. Quotes are created from and synced with opportunities, and emailed as PDFs to customers.
| Field | Description | Data Type | Filtering |
|---|---|---|---|
| Id | Quote Identifier (Primary Key). | text | |
| CreatedById | User ID who created the quote. | text | |
| CreatedDate | Date in which the quote was created. | datetime | time-based filter |
| Name | System-generated number for referencing the quote. | text | |
| SBQQ__Opportunity2__c | ID for the opportunity associated with the quote. | text |
Represents historical information about changes that have been made to the standard fields of the associated quote, or to any custom fields with history tracking enabled.
| Field | Description | Data Type | Filtering |
|---|---|---|---|
| Id | Quote History Identifier (Primary Key). | text | |
| ParentId | Related quote 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 quote record. | text | in (SBQQ__Primary__c, SBQQ__Status__c, SBQQ__SubscriptionTerm__c) or in (SBQQ__SalesRep__c, SBQQ__ExpirationDate__c) and OldValue not null |
| NewValue | Value after change. | text | |
| OldValue | Value before change. | text | When "Field" takes the following values, OldValue cannot be null:'SBQQ__SalesRep__c', 'SBQQ__ExpirationDate__c' |
Represents the definition of a single approval process.
| Field | Description | Data Type | Filtering |
|---|---|---|---|
| Id | Process Definition Identifier (Primary Key). | text | |
| Name | The unique process name, used internally. | text | |
| TableEnumOrId | Specifies the object associated with the approval process, such as Account or Contact. | text | |
| Type | This field shows the type of process. In this case the only type that is of interest for the logic are the Approval types. | text | = Approval |
Represents an instance of a single, end-to-end approval process.
| Field | Description | Data Type | Filtering |
|---|---|---|---|
| Id | Process Instance Identifier (Primary Key). | text | |
| CreatedById | User ID who created the process instance. | text | |
| CreatedDate | Date in which the process instance was created. | datetime | time-based filter |
| ProcessDefinitionId | The ID of this approval process instance. This is a relationship field. | text | |
| Status | The status of this approval process instance, for example Started, Pending, or Approved. | text | |
| TargetObjectId | ID of the object affected by this approval process instance. This is a polymorphic relationship field. | text |
Represents one work item in an approval process (ProcessInstance).
| Field | Description | Data Type | Filtering |
|---|---|---|---|
| Id | Process Instance Step Identifier (Primary Key). | text | |
| Comments | Limit: 4,000 bytes. | text | |
| CreatedById | User ID who created the process instance step. | text | |
| CreatedDate | Date in which the process instance step was created. | datetime | time-based filter |
| ProcessInstanceId | ID of the ProcessInstance that this approval step belongs to. This is a relationship field. | text | |
| StepStatus | The current status of this approval step. Examples are Approved, Fault, Held, NoResponse, Pending, Reassigned, Rejected, Removed, Started. | text |
Master data table that represents an order product that the organization sells.
| Field | Description | Data Type | Filtering |
|---|---|---|---|
| Id | Product ID (Primary Key). | text | |
| Name | Product Name | text | |
| ProductCode | User friendly code | text | |
| Family | Family to which the product belongs to | text | |
| QuantityUnitOfMeasure | Unit of measure for the product | text |
Represents a record type, which might define a different sales process for opportunities.
| Field | Description | Data Type | Filtering |
|---|---|---|---|
| Id | Record Type Identifier (Primary Key). | text | |
| Name | Label of the record type in the user interface. | text |
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 |
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 |
|---|---|---|
| Automated_users | Text | User that is considered automated. |
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_is_person_account | boolean | Defines whether the IsPersonAccount attribute will be used. This should be disabled in Salesforce Orgs where there are no Person Accounts. Setting this to true will enable the attribute to being queried in the transformations, and will enable associated tests. Default is false. |
| use_currency_conversion | boolean | Defines whether the multi currency conversion will be used. This should be disabled in Salesforce Orgs where multi currency is not used. Setting this to true will enable currency conversion. Default is false. |
| use_record_types | boolean | Defines whether the record type conversions will be used. This should be disabled in Salesforce Orgs where record types are not used. Setting this to true will enable record types to be queried and added to the transformations. Default is false. |
Salesforce already treats each object as an individual object, however the objects are joined to additional master data in order to provide additional information.
| Object | Input Data |
|---|---|
| Contracts | Contract, User |
| Credit note allocations | CreditNoteAllocation, User |
| Credit note lines | CreditNoteLine, User |
| Credit notes | CreditNote, User |
| Debit note allocation credit note lines | DebitNoteAllocationCreditNoteLine, User |
| Debit note allocations | DebitNoteAllocation, User |
| Debit note lines | DebitNoteLine, User |
| Debit note | DebitNote, User |
| Invoice lines | InvoiceLine, User |
| Invoices | Invoice, User |
| Leads | Lead, User |
| Opportunities | Opportunity, User |
| Order items | OrderItem, Order, Accounts, User, Product2, RecordsType, CurrencyType, DatedConversionRate |
| Orders | Order, User |
| Payment allocation debit note lines | PaymentAllocationDebitNoteLine, User |
| Payment allocation invoice lines | PaymentAllocationInvoiceLine, User |
| Payments | Payment, User |
| Quotes | Quote, User |
| Object | Activity | Description |
|---|---|---|
| Contract | Creation | Contract is generated in the system. Identified based on the CreateDate field on the Contract. |
| Contract | Status Change* | Contract moves from different status. Standard status in Salesforce are: Draft, In Approval Process, Activated. Identified based on the Old and New values for field = Status on the ContractHistory object. |
| Contract | Additional Changes* | Depending on fields tracked on the object, the event will be record as a change activity. In the standard connector, Owner, and Contract term are tracked, but may be expanded depending on customer configuration. Identified based on the values on the ContractHistory object. |
| Contract | Approvals* | Any approval process which was submitted. This may be generated by any custom configuration the customer has. |
| Credit note allocations | Allocate Credit note lines to Invoice lines | Allocation of the document that reduces the open amount |
| Credit note allocations | Unallocate Credit note lines from Invoice lines | Reversal of the allocation |
| Credit notes | Approvals* | Any approval process which was submitted. This may be generated by any custom configuration the customer has. |
| Credit notes | Status Change* | Credit note moves from different status. Standard status in Salesforce are: Draft, Posted. Identified based on the Old and New values for field = Status on the CreditNoteHistory object. |
| Credit notes | Creation | Credit notes is generated in the system. Identified based on the CreateDate field on the CreditNote object. |
| Credit note lines | Status Change* | Credit note lines moves from different status. Standard status in Salesforce are: Draft, Posted. Identified based on the Old and New values for field = Status on the CreditNoteLinesHistory object. |
| Credit note lines | Creation | Credit note lines is generated in the system. Identified based on the CreateDate field on the CreditNoteLines object. |
| Debit note allocation credit note lines | Allocate Debit/Credit note to Credit/Debit note | Allocation of the document that reduces the open amount |
| Debit note allocation credit note lines | Unallocate Debit/Credit note from Credit/Debit note | Reversal of the allocation |
| Debit note allocation | Allocate Debit notes to Invoices | Allocation of the document that reduces the open amount |
| Debit note allocation | Unallocate Debit notes from Invoices | Reversal of the allocation |
| Debit notes | Approvals* | Any approval process which was submitted. This may be generated by any custom configuration the customer has. |
| Debit notes | Status Change* | Debit note moves from different status. Standard status in Salesforce are: Draft, Posted. Identified based on the Old and New values for field = Status on the DebitNote_History object. |
| Debit notes | Creation | Debit note is generated in the system. Identified based on the CreateDate field on the DebitNote object. |
| Debit note lines | Status Change* | Debit note lines moves from different status. Standard status in Salesforce are: Draft, Posted. Identified based on the Old and New values for field = Status on the DebitNoteLinesHistory object. |
| Debit note lines | Creation | Debit note lines is generated in the system. Identified based on the CreateDate field on the DebitNoteLines object. |
| Invoices | Status Change* | Invoice moves from different status. Standard status in Salesforce are: Cancelled, Draft, Post In progress, Posted, Rebilled. Identified based on the Old and New values for field = Status on the Invoice_History object. |
| Invoices | Creation | Invoice is generated in the system. Identified based on the CreateDate field on the Invoice object. |
| Invoice lines | Status Change* | Invoice lines move from different status. Standard status in Salesforce are: Cancelled, Draft, Posted, Rebilled. Identified based on the Old and New values for field = Status on the InvoiceLine_History object. |
| Invoice lines | Creation | Invoice line is generated in the system. Identified based on the CreateDate field on the InvoiceLine object. |
| Lead | Creation | 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 LeadId. |
| Lead | Status Change* | Lead 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. |
| Lead | Approvals* | Any approval process which was submitted. This may be generated by any custom configuration the customer has. |
| Lead | Additional Field Changes* | Depending on fields tracked on the object, the event will be record as a change activity. Owner and Rating are tracked, but may be expanded depending on customer configuration. Identified based on records on the LeadHistory object. |
| Opportunity | Creation | Opportunity is generated in the system. Identified based on the CreateDate field on the Opportunity object. |
| Opportunity | Stage Change* | Opportunity moves from different status. Standard status in Salesforce are: Prospecting, Qualification, Needs Analysis, Value Proposition, ID Decision Makers, Perception Analysis, Proposal / Price Quote, Negotiation / Review, Closed Won, Closed Lost. Identified based on the Old and New values for field = StageName on the OpportunityFieldHistory object. |
| Opportunity | Additional Changes* | Depending on fields tracked on the object, the event will be record as a change activity. Owner, Close Date and Amount are tracked, but may be expanded depending on customer configuration. Identified based on values on the OpportunityFieldHistory object. |
| Opportunity | Approvals* | Any approval process which was submitted. This may be generated by any custom configuration the customer has. |
| Order | Creation | Order is generated in the system. Identified based on the CreateDate field on the Order. |
| Order | Status Change* | Order moves from different status. Standard status in Salesforce are: Draft, Activated. Identified based on the Old and New values for field = Status on the OrderHistory object. |
| Order | Additional Changes* | Depending on fields tracked on the object, the event will be record as a change activity. Owner, and TotalAmount are tracked, but may be expanded depending on customer configuration. Identified based on the values on the OrderHistory object. |
| Order | Approvals* | Any approval process which was submitted. This may be generated by any custom configuration the customer has. |
| Payments | Allocate Payment to Invoice/Debit notes | Allocation of the document that reduces the open amount |
| Payments | Unallocate Payment from Invoice/Debit notes | Reversal of the allocation |
| Payments | Status Change* | Payments move from different status. Standard status in Salesforce are: Draft, Posted. Identified based on the Old and New values for field = Status on the Payment_History object. |
| Payments | Creation | Payments is generated in the system. Identified based on the CreateDate field on the Payment object. |
| Quote | Creation | Quote is generated in the system. Identified based on the CreateDate field on the Quote. |
| Quote | Approvals* | Any approval process which was submitted. This may be generated by any custom configuration the customer has. |
| Quote | Status Change* | Quote moves from different status. Standard status in Salesforce are: Draft, In Review, Approved, Denied, Presented, Accepted, Rejected. Identified based on the Old and New values for field = Status on the QuoteHistory object. |
| Quote | Additional Changes* | Depending on fields tracked on the object, the event will be record as a change activity. Primary quote, subscription terms, Sales Rep and Expiration date are tracked, but may be expanded depending on customer configuration. Identified based on the values on the QuoteHistory object. |
*The actual activity names will depend on the values defined on the customer Salesforce organization.
The following image shows all objects that were used for the process.
Note: DebitNoteAllocationCreditNoteLine will be converted into two objects.
All contracts that get amended will generate automatically a new Opportunity_id that will be linked to the former. Therefore, all activities happening to the contract will be included in both case_id's: the original opportunity and the amended one. That can be seen when comparing the total amount of records in Events_all table vs Event_log_base table, thus it will be correct to have more records in the Event_log_base table.
Automated Process which is used for automated processing for Salesforce changes, however any additional users such as interfaces or RPA will use normal licenses and are not differentiated by user type.CurrencyISOCode, IsPersonAccount, LastStageChangeDate, RecordTypeId check the specific issues in the following section.Order, Quote, CurrencyType, DatedConversionRate check the specific issues in the following section.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 either OrderItem, PaymentAllocationDebitNoteLine, PaymentAllocationInvoiceLine objects 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.IsPersonAccount field not available in Account object when extracting from Salesforce When extracting data from Salesforce, IsPersonAccount may not be available as the organization may not have B2C customers. In case this happens, the field should be removed from the data model. This can be achieved by adjusting the variable use_is_person_account in the dbt_project.yml to false.RecordTypeId is not available when extracting data from the Order object Record Types are not used 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_record_types in the dbt_project.yml to false.