Building a Marketing Analytics Layer for Salesforce: From Campaign Data to Decision-Ready Metrics
A marketing analytics layer for Salesforce is a governed data model that sits between raw source data and the dashboards people use. It extracts campaign, campaign member, lead, opportunity, and attribution records from Salesforce, joins them with spend and engagement data from ad, email, and web platforms, conforms shared dimensions such as channel and campaign, and publishes each metric with exactly one definition. Most of the hard work is not in the pipelines. It is in grain, identity, history, and definitions.
Why stitching campaign reports together doesn't work
Exporting a Salesforce campaign report and a few ad platform reports into one dashboard feels like progress. It usually produces numbers nobody can defend, for predictable reasons:
Definitions drift. One team counts a lead as qualified when sales accepts it. Another counts it when it reaches a score threshold. Both are called "MQL" on the dashboard.
Naming conventions differ. The same program is "Q3_Webinar_Security" in the ad platform, "Security Webinar Q3" in Salesforce, and has a UTM campaign value nobody remembers choosing.
Conversions mean different things. Ad platforms report conversions by their own rules and attribution windows. A platform conversion is not a Salesforce lead, and the counts will rarely match.
Attribution is mixed silently. One tile uses first touch, another uses Primary Campaign Source, and a third uses the ad platform's model.
None of these is a tooling problem. They are modeling and governance problems, and they surface no matter which BI tool sits on top.
A reference flow in six layers
At a high level, the flow is: Salesforce and external marketing data → ingestion → staging → conformed model → metrics layer → BI.
Sources. From Salesforce CRM: Campaign, CampaignMember, Lead, Contact, Opportunity, and OpportunityContactRole. If Customizable Campaign Influence is enabled, the CampaignInfluence and CampaignInfluenceModel objects are added to the org. Outside Salesforce: ad platforms, web analytics, marketing automation, and finance for actual spend.
Ingestion. Incremental loads keyed on record modification timestamps, with explicit handling for deleted records. Schedule frequency to match how decisions are made; most marketing decisions don't need sub-hourly data.
Staging. Land data close to its source shape, typed and deduplicated, with load metadata. This is where you keep the raw history you'll need later.
Conformed model. Facts and dimensions with shared keys for campaign, channel, date, and person.
Metrics layer. Governed metric definitions in a semantic layer, dbt metrics, or your BI tool's model.
BI and downstream use. Dashboards, ad hoc analysis, and feeds into forecasting or predictive models.
Choosing the grain
Grain decisions determine which questions the model can answer. A workable starting set:
Campaign membership fact: one row per campaign per person. A campaign member references either a lead or a contact, and member statuses can be flagged as responded, which gives you response counts and response dates.
Lead lifecycle fact: one row per lead with milestone dates (created, qualified, converted) and conversion outputs.
Opportunity fact: one row per opportunity, with stage, amount, and close date. Snapshot it periodically if you need pipeline as it stood on a past date.
Attribution fact: one row per opportunity, campaign, and attribution model, with a credit percentage.
Spend fact: one row per day, platform, and campaign key.
The identity problem after lead conversion
When a lead converts, Salesforce creates an account and a contact, and optionally an opportunity. Campaign history attached to the lead and later activity attached to the contact now live on different records. If the model doesn't link the converted lead to its contact and opportunity, pre-conversion marketing touches disappear from pipeline analysis. Build a person key that resolves leads and contacts into one identity, and test it against a sample of known conversions.
Conformed dimensions: campaign and channel
Treat channel as a governed mapping, not a free-text field. Maintain a mapping table from every source value (Salesforce campaign type, UTM source and medium, ad platform account) to a standard channel and sub-channel, owned by marketing operations and versioned like code. Unmapped values should fail a test, not fall into "Other."
For campaigns, preserve the Salesforce campaign hierarchy so parent programs can roll up children, and store a cross-reference from ad platform campaign IDs to Salesforce campaign IDs. Where no reliable key exists, a naming convention enforced at creation time is cheaper than fuzzy matching later.
Attribution data as a first-class fact
Salesforce's Customizable Campaign Influence has specific behavior worth modeling around:
The default Primary Campaign Source model assigns 100% of influence to the campaign in that opportunity field.
Models create influence records for campaign members who also hold a contact role on an open opportunity. Once an opportunity is closed, new influence records are no longer created.
Auto-association can be limited by time frame and other criteria.
First-touch, last-touch, and even-distribution models are available as additional models for Account Engagement users. Custom models are also possible, and the number allowed varies by edition.
Practical implications: always carry the model identifier, never sum credit across models, and expect values to change when settings, close dates, or campaign membership change. If you need a stable historical view, snapshot attribution rather than reading only the current state. If your organization computes its own multi-touch model, build it from the touchpoint and membership facts so its logic is visible and testable.
Governed metric definitions
Write a spec for every metric before it reaches a dashboard. For example, "campaign lead-to-opportunity rate":
Numerator: leads from the campaign that converted with an opportunity created.
Denominator: leads that became members of the campaign.
Cohort basis: the date the person joined the campaign, not the conversion date.
Window: conversions within 180 days of membership.
Attribution: none; this is a membership-based rate.
Owner: marketing operations.
The cohort and window choices alone can change the number substantially, which is why they belong in the definition, not in someone's head.
Data quality checks that pay for themselves
Every campaign maps to a channel.
Every spend row maps to a campaign key.
Converted leads resolve to a contact.
Attribution credit per opportunity and model sums to 100%, or to the expected value for that model.
Lead counts in the model reconcile with Salesforce within an agreed tolerance.
Security and access
Salesforce sharing rules don't follow the data into a warehouse. Decide which roles may see opportunity amounts or person-level data, and enforce that with row-level security in the warehouse or BI layer.
Build from scratch or start from pre-built components
Building this layer from scratch gives full control and takes real engineering time, most of it spent on extraction, modeling, and metric definitions that look similar across organizations. Starting from pre-built components moves that time to validation and extension. Salesforce's own option, Marketing Intelligence in Marketing Cloud Next, harmonizes marketing data in Data 360 with prebuilt connectors, a marketing data model, and first- and last-touch attribution. Marketing Cloud Intelligence, formerly Datorama, is a separate product. Teams that want the model on their own warehouse can look at an enterprise analytics accelerator, such as KPI Partners' offering, which lists Marketing Analytics for Salesforce scoped to leads and campaigns with pre-built ingestion and a medallion-style data model. In every case, test it against your own definitions before trusting the output.
FAQ
Can I skip the staging layer? You can, but you lose the ability to rebuild history when definitions change, and they will.
Should attribution logic live in BI or the warehouse? In the warehouse or metrics layer, so every tool reads the same answer.