How to List Load into Salesforce – Help Center

The following describes the high-level steps to implement list loading into Salesforce. The process involves cleaning data, checking the list for duplicates, checking the list entries for matches against existing Salesforce Leads and Contacts, and processing the list by adding/updating Salesforce.

Create a Template and Data Source

1. Create a Google sheets template with the data columns you’ll likely have in your lists. If your various list sources are similar, you may be able to have one template. However, if the columns in your lists vary widely between list sources, it may make sense to have more than one list loading process.

Typical columns to include are:

Name
Email
Address
City
State
Postal Code
Country
Phone
Company
Source
Campaign ID (if you want to add the list members to an existing Salesforce Campaign)

2. Create a Data Source in Openprise using your template. Select the "import by column names option". That way, if a list is processed and someone has moved the spreadsheet columns in a new order, the data will be imported correctly. It is important to follow the template column names exactly. It is helpful to put some sample data in the Google sheet to process as you build your jobs.

Create Jobs to Process the New Data Source

1. Clean the list entries as desired. Typical cleaning activities include:

Clean Company
Format Phone
Clean email address
Normalize Country, State, City and Zip
Infer geo data where possible. For example, given Country and Zip, you can often infer State and City

2. Validate the list entries for mandatory data, and tag the list entries as “List”.

Create a multi-text attribute (ie. OP Reject Reason) to hold error messages so as you check for mandatory data, you can identify what is missing by writing an error message to this attribute.
Use the Classification and Tagging task to label a new attribute (ie. OP Record Type) for each record as coming from the “List”. In the next steps, you’ll be checking Salesforce for matches, and keeping track of any matches by changing the value of this attribute.

3. Dedupe the list using whatever criteria you deem appropriate. For any list entry that is a non-surviving duplicate (ie. indicating that the record has a duplicate entry in the list), use the Classification and Tagging task and assign the OP Reject Reason a value of "duplicate record in list" to mark the record(s) as a duplicate record. Also, assign the OP Record Type a value of "Duplicate".

Check for Matches Against Salesforce Contacts

1. Using the output of the above job, and the Infer task, check the records against your Salesforce Contacts for matches using the match criteria you desire. Typically, you’ll be matching on email address, and possibly other attributes such as last name, country, and/or company. The infer rule should write the Salesforce Contact ID to a multi-text attribute (ie. OP All Lead Matches).

Add a filter at the task level to only process records where OP Reject Reason does not have value. After all, if you've already determined that a record in your sheet should be rejected, there is no sense processing it any further.
Under the Advanced configuration section of the Infer task, add a filter to exclude matching deleted SFDC Contacts.

2. For all records that have a value in OP All Lead Matches, tag the OP Record Type as “Contact”. This identifies these records as having matched to an existing Salesforce Contact.

3. Use the Permute task to separate the records with a value in OP All Lead Matches into individual records.

4. Using either the Append or Infer task, append any Contact data from the Salesforce Contact record to the record in the job. This data will later be used for deduplication if multiple matching Salesforce records are found. Typical attributes to append could be CreatedDate, Account ID, etc.

5. Using either the Append or Infer task, append any Account data needed. This data will be used for deduplication in the next task. Typical attributes to append could be total sales data, number of opportunities, etc.

6. Dedupe any record with a value in OP All Lead Matches. This step is to determine the best matching contact, and eliminate any duplicate matches if they were found.

7. Add a filter task and filter on “Duplicates excludes “Non-surviving”. You should end up with the same number of records as in the original list. These will be a combination of “List”, "Duplicate" and “Contacts”.

Check for Matches Against Salesforce Leads

Using the output of the above job, you’ll repeat the steps in Checking for Matches Against Salesforce Contacts with some minor differences.

1. Use the Infer task and a filter to only check for matches where OP Record Type does not equal “Contact” or “Duplicate” and OP Reject Reason has no value. Check the records against your Salesforce Leads for matches using the match criteria you desire. Typically, you’ll be matching on email address, and possibly other attributes such as last name, country, and/or company or matched account. The infer rule should write the Salesforce Lead ID to a multi-text attribute (ie. OP All Lead Matches). Under Advanced Configuration, add a filter to exclude matching on both deleted and converted leads.

2. For all records that have a value in OP All Lead Matches and OP Record Type does not equal “Contact” or “Duplicate”, and OP Reject Reason has no value, tag the OP Record Type as “Lead”. This identifies these records as having matched to an existing Salesforce Lead.

3. Use the Permute task to separate the records with a value in OP All Lead Matches into individual records.

4. Using either the Append or Infer task, append any Lead data from the Salesforce Lead record to the record in the job. This data will be used for deduplication later. Typical attributes to append could be CreatedDate, Account ID, etc.

5. Using either the Append or Infer task, append any Account data needed (if there is a matched account to the lead). This data will be used for deduplication in the next task. Typical attributes to append could be total sales data, number of opportunities, etc.

6. Dedupe any record with a value in OP All Lead Matches where OP Record Type = “Lead”.

Process “List” Entries

Use the Export task to add the new “List” member to Salesforce Leads. Remember to omit processing any record where OP Reject Reason has a value. The result of the export task will return the new lead ID in the op_export_reference attribute. Save this in a new attribute.

Process “Lead” Entries

This step is optional and used if you want to use List information to update the matched Lead in Salesforce.

Use the Export task to update the “Lead” member to Salesforce Leads.

Process “Contact” Entries

This step is optional and used if you want to use List information to update the matched Contact in Salesforce.

Use the Export task to update the “Contact” member to Salesforce Contact.

Add All List, Contact and Lead Members to the Campaign in Salesforce

This step is optional and used only if you also want to add list members to a Salesforce Campaign. This process assumes the 18-character Campaign ID is in a column in the original list.

Use the Manage Campaign Members task template to add all records where OP Reject Reason has no value to the campaign. If you want to add the members with a status other than the default status for that campaign, you will need an attribute that contains the desired status.

Write a Log File to Capture the Results

At the end of the list load process, it is helpful to output the results of the list you processed. Since Openprise overwrites the data in a job with each job execution, having the log file makes troubleshooting easier.

Create a Bot to Run the List Import and Jobs in Sequence

The bot flow will be similar to this:

Purge the list data source - this clears out any records that were processed during the previous run
Import the list
Run the Clean job
Run the Validate job
Run the Dedupe Leads job
Run the Dedupe Contacts job
Run the Load New Leads job
Run the Update Existing Leads job
Run the Update Existing Contacts job
Run the Add All to Campaign job
Run the Write log file job

Additional Notes:

1. Writing log files to a Google drive folder at critical points along the way will help when debugging. This is especially important if you set up the jobs in a Bot and schedule it to run automatically because the job outputs only reflect the most recent run.

2. Run and debug your jobs as you build them. Pay attention to the number of records entering and leaving each job. If you build out all your jobs and then run your tests, you may find debugging more difficult.

3. Use filters to make sure you’re processing only those records you really want to process.

4. Pay attention to the checkbox “Drop unselected data" to control the flow of records through your job and bot. There are times you want to eliminate/drop records, and times you will want to skip processing of records for that task only, but allow the rest of the records to flow to the next task.

5. Salesforce error messages are returned in op_export_xxx and op_campaign_member_xxx fields following the execution of the respective task. These attributes should be checked when possible to handle errors appropriately.

6. When you run your tests, make sure to re-import the Lead, Contact and Campaign Member objects before running subsequent tests. That way, you always are testing against current Salesforce data.

These instructions outline one method of list loading. As with any Openprise job, there are many ways to accomplish the desired outcome so feel free to alter the above instructions to suit your needs. You can also contact your Customer Success Manager to discuss your particular list loading needs.