Okay, I just finished migrating five years of campaign data from our old ESP to a new marketing automation platform. It was a beast of a project, and I learned a ton of practical things I wish someone had told me upfront.
For context, we're a B2B SaaS company doing about 2M unique website visits/year. We needed to move everything—email sends, opens, clicks, lead source data, UTM parameters—from the old system into the new one's data warehouse. The goal was to keep our historical performance data for modeling and attribution.
Here's what mattered most:
1. **Start with a data map, not a data dump.** I spent a week just documenting which fields in the old system matched the new one. Pay special attention to custom fields and any "unique key" like lead IDs or campaign IDs. Our old system used email as the primary key, but the new one uses its own ID, so we had to build a cross-reference table first.
2. **Clean before you migrate.** I found about 15% of our records had malformed data (like invalid dates or inconsistent country codes) that would have broken the import. We ran scripts to standardize date formats and country fields before moving anything.
3. **Do a phased import.** We moved "static" data first (lead profiles), then historical campaign metadata, and finally the event data (opens, clicks). This let us test each layer and catch issues without a huge rollback.
4. **Validate with queries.** After each import phase, I ran comparison queries to check record counts and spot-check critical fields. Don't just trust the import logs; actually query the new DB and compare samples to the old.
The biggest surprise? How much time we spent reconciling UTM parameters. The old system stored them in one combined string, while the new one uses separate columns. Had to write a parser to split them out. Also, budget extra time for the final sync of any "active" campaigns that were still running during the cutover.
Anyone else tackled a big migration recently? What was the one thing you underestimated?
Peace out.
Oh, the data map step is so critical, and I love that you called it out first. I made the mistake once of just assuming "email" was the universal key across two systems, and it completely broke our lead scoring continuity for months. That cross-reference table you built is a lifesaver.
Your point about cleaning before migrating is spot on. We had a similar issue with country codes, but ours were a mix of "US", "USA", and "United States". It caused our regional reporting to be a mess until we fixed it. I'd add one thing: also deduplicate aggressively *before* the migration, not after. It's much harder to merge records once they're in the new platform with its own new IDs.
Phased import is the only way to stay sane. Did you do it by date range, or by data type (like all emails first, then all clicks)?
Data is the new oil