Salesforce Marketing Attribution data model
Kimball warehouse for Salesforce marketing (Campaign, CampaignMember, Account Engagement/Pardot engagement, Campaign Influence). A campaign-member transaction fact, a prospect-engagement transaction fact, and a Campaign Influence allocation fact spanning multiple attribution models for multi-touch ROI and pipeline attribution.
Salesforce campaigns and Account Engagement (Pardot) migrated to a cloud warehouse: campaign membership, prospect engagement, and multi-touch Campaign Influence that allocates opportunity revenue across touches under first/last/even/W-shaped/U-shaped/time-decay/data-driven models, over conformed Account, Contact, and Campaign dimensions for marketing ROI, pipeline sourced/influenced, and channel-mix analytics. It is a Kimball dimensional model: fact tables at a declared grain surrounded by conformed dimensions, ready for BI tools and a semantic layer.
- 10
- tables
- 84
- columns
- 12
- relationships
- 4
- PII columns
Opening the template creates a project with the ERD, platform-native DDL, a dbt project and documentation already generated (recommended platform: Snowflake). Edit it in plain English or on the canvas, then ship it to any supported warehouse.
Subject areas
Design decisions
Campaign Influence modelled as an allocation fact keyed by model
Multi-touch attribution credits one opportunity across many campaign/contact touches; storing one row per (opportunity, campaign, contact, model) with an influence_pct lets every attribution model be compared side-by-side without rebuilding the fact.
Engagement events separated from campaign membership
Pardot emits high-volume open/click/form/page-view events at a finer grain than CampaignMember status; isolating them keeps the membership funnel clean while still supporting content and scoring analysis.
Lead and Contact unified into one person dimension
A prospect is a Lead before conversion and a Contact after; conforming both into dim_contact with a person_type flag lets pre- and post-conversion engagement be attributed to the same individual.
Consent flags carried on the person dimension
CAN-SPAM/GDPR require honouring email opt-out and tracking consent; surfacing has_opted_out_of_email and do_not_track on dim_contact keeps suppression auditable directly in the warehouse.
Tables in the Salesforce Marketing Attribution model
Every table with its grain, columns, types and keys. PK marks a primary key, BK a business key and PII a column classified as personal data, which gets a masking strategy by default.
Fact tables (3)
fct_campaign_member
Grain: one row per CampaignMember (a person's membership in a campaign)
Transaction-grain campaign membership for reach, response rate, and member-status funnels.
| Column | Type | Keys |
|---|---|---|
| campaign_member_key | bigint | PK |
| campaign_key | bigint | |
| contact_key | bigint | |
| date_key | bigint | |
| campaign_member_id | varchar | |
| member_type | varchar | |
| member_status | varchar | |
| responded_date_key | bigint | |
| has_responded | boolean | |
| member_count | smallint |
fct_engagement_activity
Grain: one row per Pardot prospect engagement event
Transaction-grain prospect-engagement events for content performance and scoring trends.
| Column | Type | Keys |
|---|---|---|
| engagement_activity_key | bigint | PK |
| contact_key | bigint | |
| campaign_key | bigint | |
| channel_key | bigint | |
| date_key | bigint | |
| activity_id | varchar | |
| activity_type | varchar | |
| asset_name | varchar | |
| engagement_score_delta | decimal | |
| grade_change | varchar | |
| activity_count | smallint |
fct_campaign_influence
Grain: one row per (opportunity, campaign, contact, attribution model) credit allocation
Campaign Influence allocation fact — distributes opportunity revenue across touches per model.
| Column | Type | Keys |
|---|---|---|
| campaign_influence_key | bigint | PK |
| campaign_key | bigint | |
| account_key | bigint | |
| contact_key | bigint | |
| attribution_model_key | bigint | |
| date_key | bigint | |
| opportunity_id | varchar | |
| touch_position | varchar | |
| influence_pct | decimal | |
| attributed_amount | decimal | |
| attributed_pipeline | decimal | |
| is_primary_campaign_source | boolean |
Dimension tables (5)
dim_date
Grain: one row per calendar day
| Column | Type | Keys |
|---|---|---|
| date_key | bigint | PK |
| full_date | date | BK |
| month_number | smallint | |
| quarter | smallint | |
| year | smallint | |
| week_of_year | smallint | |
| is_business_day | boolean |
dim_account
Grain: one row per Salesforce Account per SCD2 version
SCD type 2
| Column | Type | Keys |
|---|---|---|
| account_key | bigint | PK |
| salesforce_id | varchar | BK |
| account_name | varchar | |
| account_segment | varchar | |
| industry | varchar | |
| billing_country | varchar | |
| is_target_account | boolean |
dim_contact
Grain: one row per Contact/Lead (person) per SCD2 version
SCD type 2
| Column | Type | Keys |
|---|---|---|
| contact_key | bigint | PK |
| salesforce_id | varchar | BK |
| person_type | varchar | |
| first_name | varchar | PII |
| last_name | varchar | PII |
| varchar | PII | |
| job_title | varchar | |
| persona | varchar | |
| lead_source | varchar | |
| country | varchar | |
| has_opted_out_of_email | boolean | |
| do_not_track | boolean |
dim_campaign
Grain: one row per Salesforce Campaign per SCD2 version
SCD type 2
| Column | Type | Keys |
|---|---|---|
| campaign_key | bigint | PK |
| salesforce_id | varchar | BK |
| campaign_name | varchar | |
| campaign_type | varchar | |
| parent_campaign_id | varchar | |
| status | varchar | |
| start_date | date | |
| end_date | date | |
| budgeted_cost | decimal | |
| actual_cost | decimal | |
| is_active | boolean |
dim_user
Grain: one row per Salesforce User (campaign/record owner)
| Column | Type | Keys |
|---|---|---|
| user_key | bigint | PK |
| salesforce_id | varchar | BK |
| user_name | varchar | PII |
| marketing_team | varchar | |
| region | varchar | |
| is_active | boolean |
Lookup tables (2)
dim_channel
Grain: one row per marketing channel
| Column | Type | Keys |
|---|---|---|
| channel_key | bigint | PK |
| channel_name | varchar | BK |
| channel_group | varchar | |
| is_paid | boolean |
dim_attribution_model
Grain: one row per Campaign Influence attribution model
| Column | Type | Keys |
|---|---|---|
| attribution_model_key | bigint | PK |
| model_name | varchar | BK |
| model_description | varchar | |
| is_data_driven | boolean |
Relationships
Each relationship is a foreign key on the first table referencing the second.
- fct_campaign_member.campaign_key → dim_campaign (many-to-one)
- fct_campaign_member.contact_key → dim_contact (many-to-one)
- fct_campaign_member.date_key → dim_date (many-to-one)
- fct_engagement_activity.contact_key → dim_contact (many-to-one)
- fct_engagement_activity.campaign_key → dim_campaign (many-to-one)
- fct_engagement_activity.channel_key → dim_channel (many-to-one)
- fct_engagement_activity.date_key → dim_date (many-to-one)
- fct_campaign_influence.campaign_key → dim_campaign (many-to-one)
- fct_campaign_influence.account_key → dim_account (many-to-one)
- fct_campaign_influence.contact_key → dim_contact (many-to-one)
- fct_campaign_influence.attribution_model_key → dim_attribution_model (many-to-one)
- fct_campaign_influence.date_key → dim_date (many-to-one)
Related templates
All templates →From requirements to AI-ready context, in one platform.
Describe your business in a conversation, start from a template or connect your own database. Your first model is ready in minutes.