How to quickly backfill historical data in Marketo Measure
Backfilling historical data is something that comes up quite often when working with clients on Marketo Measure. Here’s the scenario… There’s a specific type of campaign that hasn’t been tracked in the past, but we want to start tracking it. But, we want to be able to backfill the historical touchpoint dates, instead of it all just being forward-facing.
The good news is that, in many situations, this isn’t as hard as it may seem. We’re not saying it’s easy… but it’s probably not as difficult as you’d think.
So, let’s get started.
Create your Historical Data Field
First thing we need to do is to create a custom field that we can use to import the historical data into inside Salesforce (SFDC). We’re going to want to create this field on the Campaign Member object. And, the field should be a date/time field. For the purposes of this article, we’re going to call this field, “Bizible Historical Date,” but you can call it whatever you’d like to call it.
Separate the Historical Data from the Future Data
The next thing you want to do is to separate the historical group from the group that is going to be the forward-facing group. You want to do this in Marketo Measure, and in the SFDC Campaign. Here’s how to do it.
In the SFDC Campaign
When you run the workflow–preferably in your marketing automation platform–to bring in the historical members of the Campaign, you want to run that on a day that is unlikely to have regular organic members flowing into it… like a weekend day. So, pick a day on a weekend that you’re going to run your workflow and then jot that date down. Then, schedule the workflow to run on that day. This isolates the historical group as becoming a member of the campaign on that particular day.
In Marketo Measure
Now, on the Marketo Measure side, you’re going to create two separate Campaign rules for this Campaign Type.
One rule will apply to the forward-facing group. That’s your normal rule. Build this rule as you would any other rule, but you want to include an extra filter in the rule that this rule should apply to all Campaign Members that didn’t become a member on the date you ran your historical workflow (above). Then, the Touchpoint Date for that rule will likely be something like either Member Created Date or Member First Responded Date. When you name this rule, name it something that indicates that it’s the forward-facing, or standard, rule.
The other rule will apply only to that historical group. This rule should be constructed exactly like the above rule, with two exceptions. First, where you excluded the historical group that became a member of the campaign on the date you ran your workflow, you want to change this to only include those folks that became a member of the campaign on the date you ran your historical workflow. Second, you’re going to use a different date field for the date of the Touchpoint. That date field will be, “CampaignMember.Bizible_Historic_Date__c”. This field is the custom field that you created up above, and it’s a field that can be manually manipulated, whereas the other dates available to you can’t… because they’re system fields.
Now, you have two separate rules in Marketo Measure… one for the forward-facing, or standard group, and the other for the historical group.
Export the Historical Data
At this stage, you’re now creating touchpoints for the forward-facing group and the historical group. But, the historical group doesn’t have their historical dates set yet. So, the first step here is to export the historical data.
You should be able to export this data from your marketing automation platform. The date you want to export is likely going to be one of a couple possible dates.
- Creation Date – This is used when the campaign member should become a member of the Campaign when the record is created
- This should be super quick and easy to find
- Success/Responded Date – This is used when the campaign member should have become “successful” (in a Marketo program) or “responded” (in a SFDC Campaign)
- This is harder to get at than the Creation Date and may require some manual effort
Once you have those dates, you want to get them into a CSV file. Your CSV file needs to only have two columns. Those two columns are “Email Address” and the date field you selected above.
We’re going to refer back to this CSV in just a minute, and for easy reference, we’re going to refer to this list as the “Historical List”.
Marry the Historical Data
Now you need to marry up the historical data from your marketing automation platform, and get it ready for import into SFDC.
Export a CSV of the Campaign Members in the SFDC Campaign you need to update. But, you only want to do this for the historical group… so only the group that became members of this Campaign on the date you ran the historical workflow. In this CSV, you only need three columns; Email Address, Campaign Member ID and “Bizible Historical Date” (this is the custom field you created above, and the field that you mapped to the Touchpoint Date in Marketo Measure). The “Bizible Historical Date” field should be empty. We’re going to call this list the “Import List”.
Once you have this CSV ready to go, run a Vlookup. You want to search the “Historical List” for the email addresses on the “Import List”. When you find a match, you want the Vlookup to pull the date from the second column on the “Historical List” and drop that date value into the “Bizible Historical Date” column on the “Import List”.
Once your Vlookup is done, copy and paste the values that are now in the “Touchpoint Date” field on the “Import List” into a new column. You need these to be actual values, not the Vlookup results.
What you should have now is an “Import List” with three columns of data–Email, Campaign Member ID and Bizible Historical Date–with dates listed in the “Bizible Historical Date” field for all records on the CSV. You’re ready to rock.
Import the Historical Data
This is our second to last step… which is getting the data back into SFDC. You’re going to want to run a DataLoader to import the Campaign Member data back into SFDC. You want to match based on the Campaign Member ID and you only care about inserting the “Bizible Historical Date”. The Email Address field, at this point, is not important.
Run your DataLoader and get that data in there!
The Last Mile
The last piece here is to QA your data. You want to give Marketo Measure some time to reprocess the data, so give it about 24-48 hours. Once you’ve given it enough time, run a Bizible Persons with Bizible Touchpoints report in SFDC. Filter for the SFDC Campaign in question so that we’re only looking at the Touchpoints tied to this SFDC Campaign.
Now, you’ll want to validate that the “Touchpoint Date” field on the Bizible Touchpoint object has been updated to the dates that you imported. Just do a quick spot check to make sure it’s all accurate. It should be. But, you may have had some import errors during the DataLoader. So, you may need to run it a couple times if you encounter any import errors.
Clean up any stragglers… and now you have backfilled the historical data into Marketo Measure.
We hope this helps make this process a bit easier for you and gives you some ideas for how to clean up some historical data issues that you may have. As always, if you have any questions, don’t hesitate to reach out to us.
