From Disconnected Marketing Data to Connected Customer Intelligence: A Salesforce Marketing Cloud Case Study
Most organizations don’t set out to create disconnected marketing data.
It usually happens as their technology environment grows.
One platform manages customer profiles and personalization. Another handles email campaigns and engagement. Additional data sources provide campaign information, customer identifiers, and historical activity.
Each system serves a purpose.
The challenge begins when marketing teams need to understand how the information fits together.
That was the situation facing a large financial services organization using Salesforce Marketing Cloud Personalization and Marketing Cloud Engagement.
The organization had access to valuable customer information and email engagement data, but connecting those records consistently was becoming a challenge. Customer identifiers varied between systems, campaign attribution required additional information, and historical engagement data needed to be incorporated into reporting.
The company wasn’t looking to replace its existing marketing platforms. It needed a more reliable way to connect them.
Revenue Ops was brought in to design and implement an integration and reporting foundation that would make customer engagement data more consistent, traceable, and useful.
What initially appeared to be a data integration project quickly became an exercise in understanding how customer identities, marketing activities, and campaign information should work together.
The Challenge Wasn’t a Lack of Marketing Data
The organization already had substantial information available across its Salesforce marketing environment.
Marketing Cloud Personalization contained customer and participant context, including profile-level activity measures. Marketing Cloud Engagement captured email interactions such as sends, opens, clicks, bounces, complaints, and unsubscribes.
The problem wasn’t that the data didn’t exist.
It was that the information wasn’t consistently connected.
Some email engagement records used Salesforce Contact IDs as Subscriber Keys, while others used email addresses. That made it difficult to reliably associate every interaction with the appropriate customer record.
Campaign attribution presented another complication.
The organization needed a specific campaign attribute for its reporting, but that information wasn’t directly available through the standard Marketing Cloud Engagement activity data views.
Even when the engagement records existed, the team needed additional processing to connect them with the right campaign context.
Historical reporting also required attention. The organization needed 180 days of engagement activity, but the data had to be processed carefully to prevent duplicate records or gaps between reporting periods.
There was another important distinction.
Marketing Cloud Personalization provided profile-level information, including total actions and the customer’s last recorded activity. Those measures were useful, but they didn’t represent every individual behavioral event.
Without clear definitions, downstream reporting could interpret the data incorrectly.
These weren’t isolated reporting issues. They were connected problems involving data architecture, identity management, and the processes required to maintain reliable information.
We Started by Understanding How the Data Connected
Before building the final reporting model, we needed to understand how information moved between the two Salesforce platforms.
Which customer identifiers were available?
How did Marketing Cloud Engagement identify subscribers?
Where could we find the campaign information required for attribution?
Which engagement activities needed to be captured?
And how could we process historical information without introducing duplicate records?
Answering those questions shaped the implementation.
The solution needed to do more than move records from one platform to another. It needed to preserve their meaning and establish consistent relationships between customer profiles, engagement activity, and campaign information.
We developed a Python-based integration that retrieved user-level information from Marketing Cloud Personalization through its APIs and loaded the required data into Marketing Cloud Engagement.
That involved API authentication, field mapping, batch processing, and diagnostic testing.
One detail was particularly important.
The Personalization data included a last activity timestamp. In the integration, this value represented the last activity associated with the customer profile, converted to UTC.
It did not represent the timestamp of every individual customer action.
That distinction might seem minor, but it matters when teams begin using the information for analysis.
A profile’s most recent activity and the individual events that make up a customer’s engagement history answer different questions.
Treating them as interchangeable would create misleading reporting.
By establishing the meaning of these fields during implementation, we helped ensure the integrated data could be interpreted consistently.

Creating One Model for Email Engagement
The next challenge involved bringing email engagement activity into a common reporting structure.
Marketing Cloud Engagement provides different data views for different types of email interactions.
A sent email represents one type of event. An open represents another. Clicks, bounces, complaints, and unsubscribes each have their own characteristics.
For this organization, the reporting foundation needed to account for all six activity types.
We built an Automation Studio SQL process that standardized Sent, Open, Click, Bounce, Complaint, and Unsubscribe records into a common activity model.
Each record included the relevant subscriber and job information, activity type, timestamp, customer identifiers, email metadata, campaign attribution, and fields specific to the activity.
This gave the organization a consistent structure for downstream reporting.
Instead of treating each engagement type as a separate reporting problem, the new model established a common way to process and interpret activity across the email program.
However, standardizing the activity fields was only part of the work.
We still needed to make sure those activities connected to the correct customer.
Solving the Customer Identity Problem
One of the most important decisions in the implementation was establishing a consistent customer identifier.
Some engagement records used Salesforce Contact IDs. Others used email addresses.
Both formats could identify a subscriber, but they couldn’t always be treated as equivalent when joining records across systems.
That inconsistency created challenges for attribution and duplicate prevention.
We revised the identity model so Salesforce Contact ID became the canonical identifier.
The participant Data Extension was updated to use Contact ID as its primary key, and the SQL processing logic was modified to resolve different Subscriber Key formats to the appropriate Contact ID.
When an engagement record contained an email address rather than a Contact ID, the process used the participant information to resolve the corresponding Salesforce record.
We also retained the original Subscriber Key.
That was intentional.
While Contact ID provided the consistent identifier needed for reporting, preserving the source identifier made it possible to investigate discrepancies and troubleshoot records when necessary.
The result was a more dependable identity framework for associating email engagement with customer information.
It also addressed a problem that frequently appears in connected marketing environments: moving data between systems is relatively straightforward compared with ensuring every system agrees on who the customer is.
Connecting Email Activity to Campaign Attribution
Customer identity wasn’t the only information that needed to be resolved.
The organization also required campaign metadata that wasn’t directly available through the standard engagement activity views.
Without that information, email interactions lacked part of the context needed for campaign reporting.
We identified an enterprise shared Data Extension containing the required campaign attribute and incorporated it into the activity processing model.
The integration used Job ID to connect engagement records with the relevant campaign information.
This required careful handling.
A poorly structured join can multiply records when more than one matching source row exists. That can inflate engagement totals and introduce duplicates into downstream reporting.
We addressed this by controlling the campaign enrichment at the job level before adding the information to the activity dataset.
The resulting model preserved the engagement records while providing the additional campaign context the organization needed.
This was an important step toward making the data more useful for marketing analysis.
Knowing that someone clicked an email is valuable.
Being able to connect that interaction to the appropriate customer and campaign provides a much stronger foundation for understanding marketing engagement.
What We Found During Testing
Not every data issue becomes visible during initial design.
Some only emerge when a solution begins processing actual records.
During testing, complaint activity exposed primary-key collisions in the target Data Extension.
The problem required more than adjusting a single SQL statement.
We examined the target data structure, participant joins, Subscriber Key formats, and the different paths used to resolve customer identities.
The investigation revealed areas where the processing logic needed stronger controls to prevent duplicate activity records.
We refined the SQL to filter unique events, improve email-to-Contact resolution, and apply final-result deduplication for complaint activity.
We also simplified the target Data Extension so ActivityID served as the primary key.
This aligned the database structure with the activity-level identity model rather than depending on a composite key containing source-specific information.
The changes resolved the duplicate-key issues encountered during implementation and made the processing logic more consistent.
This was an important part of the engagement.
The objective wasn’t simply to get the queries to run successfully once. We needed an approach that could continue processing real marketing data while giving the team enough visibility to investigate problems when they occurred.
Recovering Six Months of Marketing Engagement History
Once the activity model and identity logic were established, the organization still needed historical coverage.
A daily feed could support current reporting, but it wouldn’t provide the previous six months of engagement activity required for the project.
We completed a 180-day historical backfill in controlled 30-day increments.
Each batch used defined, non-overlapping date boundaries.
That prevented the same boundary records from being processed twice while ensuring the historical periods connected without gaps.
The backfill moved progressively toward the present until the required historical window was complete.
Afterward, we transitioned the automation to its ongoing daily operating model.
Under that model, the target Data Extension is refreshed with one day of engagement activity during each run.
We also retained two timestamps for different purposes.
EventDate identifies when the customer engagement occurred.
LoadDate identifies when the automation loaded the record.
Keeping both fields gives the team a clearer way to investigate processing activity.
For example, if an engagement record appears in a dataset later than expected, the team can distinguish the original customer interaction from the time the automation processed it.
That separation is useful for troubleshooting and operational auditing.
The completed historical backfill and daily processing model provided the organization with the required historical coverage and a repeatable approach to maintaining current activity data.
Making the Integration Easier to Support
A working integration is only one part of a successful implementation.
The organization also needs a way to understand what happens when data doesn’t behave as expected.
Throughout the project, we developed diagnostic capabilities that allowed the team to inspect source payloads and investigate processing issues without unnecessarily affecting production Data Extensions.
We also retained information that would help explain how records entered the activity model.
Original Subscriber Keys, consistent Contact IDs, engagement timestamps, and load timestamps all served a purpose beyond the immediate reporting requirement.
They made the resulting data easier to trace.
That matters because marketing data environments change.
New records enter the system. Customer information evolves. Campaign configurations change. Unexpected source values appear.
A solution that works only when every record follows the expected pattern becomes difficult to maintain.
By addressing the underlying identity and processing logic, we established a more supportable approach to ongoing marketing data operations.
The Result: A More Reliable Foundation for Customer Intelligence
By the end of the engagement, the financial services organization had a working integration between Marketing Cloud Personalization and Marketing Cloud Engagement.
It also had a standardized email activity model covering six engagement types, a consistent Contact ID-based identity framework, and enriched campaign information for reporting.
The implementation resolved the query validation, duplicate-key, and join-related issues encountered during development.
The required 180-day historical backfill was completed, and the solution transitioned to repeatable daily processing.
These improvements addressed the specific data challenges that had complicated the organization’s marketing reporting environment.
More importantly, the solution created a clearer relationship between customer profiles, email interactions, and campaign context.
The organization now had a more consistent foundation for downstream engagement reporting and analysis.
The project did not establish a measured increase in campaign revenue, conversion rates, or marketing ROI. Its documented outcomes were improvements to integration, data consistency, historical coverage, attribution enrichment, and operational processing.
Those foundational improvements are important because reliable marketing analysis depends on understanding what the data represents and how the records connect.

Why Connecting Marketing Platforms Requires More Than an Integration
It’s easy to think of a Marketing Cloud integration as a straightforward exchange of information between two systems.
Retrieve the data.
Map the fields.
Load the records.
Schedule the automation.
But the difficult questions often sit underneath those steps.
What happens when two systems use different identifiers for the same customer?
How should engagement records connect to campaign information?
What prevents duplicate records from changing reported activity totals?
How do you distinguish a customer’s last recorded activity from individual behavioral events?
And how can the organization tell whether an issue originated in the source system, the integration, or the reporting process?
These questions are just as important as the technical connection itself.
For this organization, Revenue Ops combined API development, Marketing Cloud architecture, SQL processing, identity resolution, and data quality controls within one implementation.
Rather than treating each issue independently, we designed the solution around a consistent data model and a repeatable operating process.
That approach helped turn two valuable but disconnected sources of marketing information into a more coordinated reporting foundation.
Why We Focus on the Data Before the Dashboard
Marketing teams often begin with the reporting questions they want answered.
Which campaigns are performing?
How are customers engaging?
What activity should we attribute to a particular campaign?
Those are reasonable questions.
But the answers depend on what happens before the dashboard.
If customer identifiers don’t match, engagement attribution becomes unreliable.
If campaign metadata isn’t available, reporting loses important context.
If historical data contains gaps or duplicates, trends become difficult to interpret.
And if the organization confuses profile-level measures with individual events, even a well-designed dashboard can produce misleading conclusions.
That’s why we believe the better starting point is understanding the data.
Where does it originate?
What does each field actually represent?
How do records connect?
Which system owns the information?
What happens when the expected relationships don’t exist?
For this financial services organization, answering those questions led to a solution that connected Marketing Cloud Personalization and Marketing Cloud Engagement while addressing the identity, attribution, and processing challenges underneath.
The result wasn’t simply a new integration.
It was a more reliable foundation for understanding customer engagement.
And when organizations want to make better decisions from their marketing data, that foundation matters more than the dashboard they build on top of it.
Connect Your Marketing Cloud Data With Revenue Ops
When customer profiles, engagement activity, and campaign information don’t connect consistently, marketing teams can struggle to trust the reporting they depend on.
Revenue Ops helps organizations integrate Salesforce marketing platforms, improve data quality, establish reliable attribution processes, and build more dependable marketing operations.











