The following describes the high-level steps to implement list loading into Marketo. The process involves cleaning data, checking the list for duplicates, checking the list entries for matches against existing Marketo people, and processing the list by adding/updating records in Marketo and ensure those people are assigned to the appropriate Marketo program.
Create a Template and Data Source
1. Create a Google sheets template with the data columns you'll likely have in your lists. To ensure that the list is loaded into the correct Marketo program, a field must be populated with a designated value so that it is recognized by the Marketo program. In this example, the field in Marketo we’re using is “Keyword”, but it could be any appropriate field.
Typical columns to include are:
- Postal Code
- Program Value (which will be mapped to the Marketo field "Keyword")
An example template sheet is shown below.
Note that the name of the column in the spreadsheet and the name of the field in Marketo do not have to match, because you can use Openprise to map one to the other. Here the “Program Value” column in the spreadsheet is mapped to the “Keyword” field in Marketo.
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 geographic 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 Marketo 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 Marketo People Records
1. Using the output of the above job, and the Infer task, check the records against your Marketo people for matches using the match criteria you desire. Typically, you’ll be matching on email address. The infer rule should write the Marketo 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 Marketo people records.
2. For all records that have a value in OP All Lead Matches, tag the OP Record Type as “Lead”. This identifies these records as having matched to an existing Marketo person.
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 people data from the Marketo record to the record in the job. This data will later be used for deduplication if multiple matching Marketo records are found. (Although Marketo doesn't allow duplicate records with the same email address, if your Marketo is connected to a CRM that does allow duplicate email addresses, you will likely have duplicate records with the same email address.) Typical attributes to append could be leadStatus, sfdcType, createdAt, etc.
5. Dedupe any record with a value in OP All Lead Matches. This step is to determine the best matching record, 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 “Lead”.
Process “List” Entries
Use the Export task to add the new “List” member to Marketo. Remember to omit processing any record where OP Reject Reason has a value. The result of the export task will return the new Marketo 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 record in Marketo.
Use the Export task to update the “Lead” member to Marketo.
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.
Set up a Marketo Program
Your Marketo program will need a Smart Campaign that recognizes both when new leads are added with the correct value for “Keyword” and when existing leads have the “Keyword” field updated to the program’s value. To do this, we use both “Data Value Changes” and “Lead is Created” with the filter “Keyword is ‘monkey’.” In this example, Monkey is the field value that this program is using to drive the triggers, but it could be the name of your program or any designated value.
From here, your Smart Campaign Flow can contain any flow steps that process your leads in the way you want. In this example, we’ve simply added them to a static list, but it could be anything from adding them to a nurture program, to sending them a specific email, to changing their owner in Marketo or the CRM.
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 Load New Leads job
- Run the Update Existing Leads job
- Run the Write log file job
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. Marketo error messages are returned in op_export_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 your Marketo people data source before running subsequent tests. That way, you are always testing against current Marketo 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.
If you have any additional questions, please feel free to contact us at email@example.com.