Bad things happen. Such as accidentally overwriting thousands of records in Marketo due to a faulty list upload. Or a rogue API integration. Or a Smart Campaign that wasn’t tested properly. How can you get your data back asap?
The good news is that Marketo stores old data values in the activity log for 90 days, in “Change Data Value” activities. The bad news is that it’s not so easy to access those old values and restore them. In this article I’ll explain the best approach to get your data back.
You could use Reverto to restore your Marketo data, but if you want to do it yourself, you can follow these steps.
Here's an example of data that was accidentally overwritten using a Smart Campaign. This is a screenshot of the activity log and you can see the old value is still available:
Standard Way to Restore Overwritten Data
The standard approach is to extract the correct old values from Marketo’s activity log and use those values to restore the person record. That does involve using the Marketo API, because that’s the only way to get “Change Data Values” out of Marketo in bulk.
Other Ways to Restore Overwritten Data
I’ve seen a couple of situations where exporting the activity log isn’t needed. For example, one Marketo client accidentally overwrote MQL date fields - but after further analysis - it turned out that they had another field storing the same information. Normally data duplication is not ideal, but in this case they could simply copy the data back using a Smart Campaign. Another Marketo client exported their records to a Data Warehouse nightly, so they could export the old values from the data warehouse and import it into Marketo. But those are exceptions.
Restoring Marketo Data via the API
So let’s look at the standard way of getting your data back, using the Marketo API. Step 1 is to get the old values extracted from Marketo. These are the things you’ll need to know:
- Start and end dates of when the data was overwritten (sometimes it’s just 1 day, sometimes it’s a longer period)
- API names of the fields that were overwritten
Once you have that information, you can use the Marketo API to extract the “Change Data Values”. Explaining the REST API is beyond the scope of this article, but you can use the “Marketo REST API Crash Course” by Workflow Pro or my intro article for the Marketo REST API.
The easiest way to extract this data is using the Bulk Activity Extract API, in which you can specify the time period and the field names, and it’ll give you a CSV file with all Data Value Changes. You can use a tool like Postman to extract this data. This is an example configuration, in case the “Department” field got overwritten on November 2, 2022:
{
"filter": {
"createdAt": {
"startAt": "2022-11-02T00:00:00-08:00",
"endAt": "2022-11-02T23:59:59-08:00"
},
"activityTypeIds": [
13
],
"primaryAttributeValues" : [
"department"
]
}
}
There is a little catch, which is that some of the fields are exported as JSON within a single CSV column, the ‘attributes’ column: old value, new value, source and reason. You’ll need these fields to find the correct old value. The best way to convert that to full CSV is to write a script, for example in Python. I usually use the “csv” module in Python with the “DictReader” method to read a CSV file and the “DictWriter” method to write the updated CSV back to a file. The “json” module is used to parse the JSON data.
{"Old Value":"Marketing","New Value":"Accounting","Reason":"Changed by Smart Campaign Program Import 1.Change Data Values action Change Data Value","Source":"Marketo Flow Action"}
Needs to be converted into:
| Old Value | New Value | Reason | Source |
| Marketing | Accounting | Changed by Smart Campaign Program Import 1.Change Data Values action Change Data Value |
Marketo Flow Action |
How to Determine What Value to Restore
If the situation is very straightforward, you’ll only have one Change Data Value for each person and each field. In that case, you can simply take the “old value” and import that into Marketo. However, that may not always be the case, especially if data has been overwritten over a longer period of time, or if it’s a while ago that it happened. Here are some examples of more complex situations:
- Some of the values have been fixed already and now your “old” value is outdated and shouldn’t be restored
- Multiple sources have updated the field in the affected time period; only one of those changes was an incorrect change; you have to pick that one by looking at the source, reason and/or a particular old or new value; in case of API changes, you can also look at the specific API user; the selection criteria will be unique to your situation
- The old value could be worse than the new one, for example if it’s bogus data (“UNKNOWN”, “asdf”, etc.); in those cases, you should not restore those values
Steps to Restore
The restore process may seem a little complicated, but it can be made a easier when following these steps:
- Find the data value change that was incorrect and note the “Old Value”
- Filter out any old values that shouldn’t be restored
- See if there are more recent data value changes after the incorrect data value change; alternatively, you could also look at the current field value on the person record: if that’s different from the incorrect new value, you could assume it has been fixed. So also remove these values from the restore file
- Any “Old Values” that remain can be restored in Marketo
Now you are ready to import the correct prior values back into Marketo.
Importing the Correct Data into Marketo
After going through this process, you’ll have a file with a Marketo Lead ID and the correct “Old Values” for the affected fields. Since you don’t have the email address, you have two options:
- Add all affected records to a Static List in Marketo and download it with the ‘id’ and ‘email address’ as columns, so you can use a VLOOKUP to find the email address. When you have the email address, you can just import a List
- Use the REST API to update the records in Marketo; it’s probably easiest to use the Bulk Lead Import API, since you can import a CSV file and specify the Marketo ID as the lookup field
Using Reverto for Marketo Data Restore
If this process is too daunting you can use Reverto to do the restore for you. Reverto has automated this entire process and it has been proven with many Marketo customers. You can contact us for more information at https://reverto.io/#contact
