Marketo Measure Ultimate Data Integrity Requirement

Last update: 2024-02-27

Marketo Measure validates the incoming AEP datasets to ensure the data is sufficient and coherent for the purpose of attribution. Failing to meet the data integrity requirement will cause the dataset to be rejected by the Marketo Measure system. This document details the data integrity requirement, provides query examples for data inspection, and recommends a solution for required fields with a null value.

Entity Object

XDM Class XDM Field Group XDM Path XDM Type Data Source Field Required? Notes
Account (Account for Salesforce, Company and/or Named Account for Marketo)
XDM Business Account accountKey.sourceKey string Yes E.g. - 123@999-abc-888.Marketo
accountKey.sourceID string ID Yes E.g. - 123
accountKey.sourceInstanceID string Yes E.g. - 999-abc-888
accountKey.sourceType string Yes E.g. - Marketo
extSourceSystemAudit.createdDate date-time CreatedDate Yes
extSourceSystemAudit.lastUpdatedDate date-time ModifiedDate Yes
XDM Business Account Details accountName string Name Yes
Campaign (Campaign for Salesforce, Program for Marketo)
XDM Business Campaign campaignKey.sourceKey string Yes E.g. - 55555@999-abc-888.Marketo
campaignKey.sourceID string ID Yes E.g. - 55555
campaignKey.sourceInstanceID string Yes E.g. - 999-abc-888
campaignKey.sourceType string Yes E.g. - Marketo
extSourceSystemAudit.createdDate date-time CreatedDate Yes
extSourceSystemAudit.lastUpdatedDate date-time ModifiedDate Yes
campaignName string Name Yes
campaignType string CampaignType No For channel mapping
XDM Business Campaign Details channelName string ChannelName No For channel mapping
campaignStartDate date-time StartDate No For campaign cost
campaignEndDate date-time EndDate No For campaign cost
actualCost.amount number Cost No For campaign cost
actualCost.currencyCode

string

^[A-Z]{3}$

CurrencyIsoCode No For campaign cost
Campaign Member (Campaign Member for Salesforce, Program Memberships for Marketo)
XDM Business Campaign Members campaignMemberKey.sourceKey string Yes E.g. - 987654321@999-abc-888.Marketo
campaignMemberKey.sourceID string ID Yes E.g. - 987654321
campaignMemberKey.sourceInstanceID string Yes E.g. - 999-abc-888
campaignMemberKey.sourceType string Yes E.g. - Marketo
extSourceSystemAudit.createdDate date-time CreatedDate Yes
extSourceSystemAudit.lastUpdatedDate date-time ModifiedDate Yes
personKey.sourceKey string Yes E.g. - 333@999-abc-888.Marketo
personKey.sourceID string Lead ID or Contact ID Yes

E.g. - 333, depending on the data source table, this is either Lead ID or Contact ID.

Foreign key to Lead or Contact

personKey.sourceInstanceID string Yes E.g. - 999-abc-888
personKey.sourceType string Yes E.g. - Marketo
campaignKey.sourceKey string Yes E.g. - 55555@999-abc-888.Marketo
campaignKey.sourceID string Campaign ID Yes

E.g. - 55555.

Foreign key to Campaign

campaignKey.sourceInstanceID string Yes E.g. - 999-abc-888
campaignKey.sourceType string Yes E.g. - Marketo
XDM Business Campaign Member Details b2b.personType string "Lead" or "Contact Yes Depending on the data source table, this should be set to either "Lead" or "Contact". We recommend setting it to "Contact" for most use cases
memberStatus string Status Yes
hasResponded boolean HasResponded Yes
firstRespondedDate date-time FirstRespondedDate No
Person (Contact or Lead for Salesforce, Persons for Marketo)
XDM Individual Profile XDM Business Person Details b2b.personKey.sourceKey string Yes e.g. - 333@999-abc-888.Marketo
b2b.personKey.sourceID string ID Yes e.g. - 333, depending on the data source table, this is either Lead ID or Contact ID
b2b.personKey.sourceInstanceID string Yes E.g. - 999-abc-888
b2b.personKey.sourceType string Yes E.g. - Marketo
workEmail.address

string

email

Email Yes
b2b.personStatus string Status Yes for Lead personType only Only required if b2b.personType is "Lead"
extSourceSystemAudit.createdDate date-time CreatedDate Yes
extSourceSystemAudit.lastUpdatedDate date-time ModifiedDate Yes
b2b.isConverted boolean IsConverted Yes for Lead personType only Only required if b2b.personType is "Lead"
b2b.personType string "Lead" or "Contact Yes Depending on the data source table, this should be set to either "Lead" or "Contact". We recommend setting it to "Contact" for most use cases
extendedWorkDetails.jobTitle string No
XDM Business Person Components personComponents.sourceAccountKey.sourceKey string No

E.g. - 123@999-abc-888.Marketo.

The set of sourceAccountKey fields is only "required" for true Contact records, defined as person records linked to Account. Missing it won't cause the dataset to be rejected but the attribution results will be off.

personComponents is an array but Marketo Measure only takes the first element personComponents[0]

personComponents.sourceAccountKey.sourceID string Account ID No

E.g. - 123.

Foreign key to Account

personComponents.sourceAccountKey.sourceInstanceID string No e.g. - 999-abc-888
personComponents.sourceAccountKey.sourceType string No E.g. - Marketo
Opportunity (Opportunity for Salesforce, Opportunities for Marketo)
XDM Business Opportunity opportunityKey.sourceKey string Yes e.g. - 77777@999-abc-888.Marketo
opportunityKey.sourceID string ID Yes E.g. - 77777
opportunityKey.sourceInstanceID string Yes E.g. - 999-abc-888
opportunityKey.sourceType string Yes E.g. - Marketo
extSourceSystemAudit.createdDate date-time CreatedDate Yes
extSourceSystemAudit.lastUpdatedDate date-time ModifiedDate Yes
accountKey.sourceKey string Yes e.g. - 123@999-abc-888.Marketo
accountKey.sourceID string Account ID Yes

E.g. - 123.

Foreign key to Account

accountKey.sourceInstanceID string Yes E.g. - 999-abc-888
accountKey.sourceType string Yes E.g. - Marketo
opportunityName string Name Yes
opportunityStage string Stage Yes
opportunityType string No
XDM Business Opportunity Details isWon boolean IsWon Yes
isClosed boolean IsClosed Yes
expectedCloseDate date CloseDate Yes
opportunityAmount.amount number Amount Yes
opportunityAmount.currencyCode

string

^[A-Z]{3}$

CurrencyIsoCode Yes
Opportunity Contact Role (Needed only if plan to use Opportunity Contact Role as the buying group for attribution)
XDM Business Opportunity Person Relation personKey.sourceKey string Yes e.g. - 333@999-abc-888.Marketo
personKey.sourceID string Contact ID Yes

e.g. - 333.

Foreign key to Contact

personKey.sourceInstanceID string Yes E.g. - 999-abc-888
personKey.sourceType string Yes E.g. - Marketo
isPrimary boolean IsPrimary Yes
opportunityKey.sourceKey string Yes e.g. - 77777@999-abc-888.Marketo
opportunityKey.sourceID string Opportunity ID Yes

e.g. - 77777.

Foreign key to Opportunity

opportunityKey.sourceInstanceID string Yes E.g. - 999-abc-888
opportunityKey.sourceType string Yes E.g. - Marketo
opportunityPersonKey.sourceKey string Yes e.g. - 222222@999-abc-888.Marketo
opportunityPersonKey.sourceID string ID Yes e.g. - 222222
opportunityPersonKey.sourceInstanceID string Yes E.g. - 999-abc-888
opportunityPersonKey.sourceType string Yes E.g. - Marketo
personRole string Role No
extSourceSystemAudit.createdDate date-time CreatedDate Yes
extSourceSystemAudit.lastUpdatedDate date-time ModifiedDate Yes
Conversion Rate (Needed only if use multiple currencies; Only one Conversion Rate dataset can be activated to Marketo Measure)
Conversion extSourceSystemAudit.externalKey.sourceKey string Yes e.g. - 8888@0x012345.Salesforce
extSourceSystemAudit.externalKey.sourceId string ID Yes e.g. - 8888
extSourceSystemAudit.externalKey.sourceInstanceId string Yes E.g. - 0x012345
extSourceSystemAudit.externalKey.sourceType string Yes E.g. - Salesforce
extSourceSystemAudit.createdDate date-time CreatedDate Yes
extSourceSystemAudit.lastUpdatedDate date-time ModifiedDate Yes
isDeleted boolean Yes
Currency Conversion Rate Details conversionRate number ConversionRate Yes
endDate date NextStartDate Yes
startDate date StartDate Yes
sourceISOCode string ISOCode Yes E.g. EUR
targetISOCode string Yes The default currency code set in Marketo Measure, e.g. USD

ExperienceEvent

XDM Class XDM Field Group XDM Path XDM Type Data Source Field Required? Notes
Activity
XDM ExperienceEvent _id string ID Yes Yes
eventType string ActivityType Yes Yes
timestamp date-time Activity Date Yes Yes
Person Identifier personKey.sourceKey string Yes E.g. - 333@999-abc-888.Marketo
personKey.sourceID string Lead ID or Contact ID Yes

E.g. - 333, depending on the data source table, this is either Lead ID or Contact ID.

Foreign key to Lead or Contact

personKey.sourceInstanceID string Yes E.g. - 999-abc-888
personKey.sourceType string Yes E.g. - Marketo
Add To Campaign leadOperation.addToCampaign.campaignKey.sourceKey string Yes for leadOperation.addToCampaign type only E.g. - 55555@999-abc-888.Marketo
leadOperation.addToCampaign.campaignKey.sourceId string Campaign ID Yes for leadOperation.addToCampaign type only

E.g. - 55555.

Foreign key to Campaign

leadOperation.addToCampaign.campaignKey.sourceInstanceId string Yes for leadOperation.addToCampaign type only E.g. - 999-abc-888
leadOperation.addToCampaign.campaignKey.sourceType string Yes for leadOperation.addToCampaign type only E.g. - Marketo
Status in Campaign Progression Changed leadOperation.campaignProgression.campaignKey.sourceKey string Yes for leadOperation.campaignProgression type only E.g. - 55555@999-abc-888.Marketo
leadOperation.campaignProgression.campaignKey.sourceId string Campaign ID Yes for leadOperation.campaignProgression type only

E.g. - 55555.

Foreign key to Campaign

leadOperation.campaignProgression.campaignKey.sourceInstanceId string Yes for leadOperation.campaignProgression type only E.g. - 999-abc-888
leadOperation.campaignProgression.campaignKey.sourceType string Yes for leadOperation.campaignProgression type only E.g. - Marketo

ExperienceEvent Type Supported

Event Type XDM Event Type Description
New Lead leadOperation.newLead Use to record the creation and details of a new marketing lead
Convert Lead leadOperation.convertLead Use when a marketing lead is converted into a sales-qualified contact that is assigned to a sales user
Interesting Moment leadOperation.interestingMoment Use for tracking high value activities by potential customers
Fill Out Form web.formFilledOut Use to capture details when a person fills out a form on a web page
Unsubscribe Email directMarketing.emailUnsubscribed Use to capture details when a person unsubscribes from an email
Open Email directMarketing.emailOpened Use to capture details when a person opens a marketing email
Click Email directMarketing.emailClicked Use to capture details when a person clicks a link in a marketing email
Change Status in Progression leadOperation.statusInCampaignProgressionChanged Use to capture details when a lead's status in a campaign changes
Add to Engagement Program (Add to Nurture) leadOperation.addToCampaign Use to add a person to the specific campaign.

Use the “Interesting Moment” event type for event types not supported in the table above. Add a custom field to indicate the sub-type “Interesting Moment”.

Query Examples for Data Inspection

The following is a list of query examples for inspecting ingested datasets in AEP data lake. To use them against your datasets, replace the table name in the query examples below with your actual dataset table name.

We expect all counts to be 0.

For personType field, we expect there are only “Lead” or “Contact” values, and there is no null value.

For all “Contact” person records, we expect there is an Account foreign key.

For “Lead” person records, an Account foreign key does not exist and is not required. If you choose to ingest “Lead” person records as “Contact” person records (which is recommended), an Account foreign key on such person records is not required.

XDM Business Account

select 'account source id', count(*) from salesforce_account where accountKey.sourceId is null
union all
select 'account source type', count(*) from salesforce_account where accountKey.sourceType is null
union all
select 'account source instance id', count(*) from salesforce_account where accountKey.sourceInstanceId is null
union all
select 'account source key', count(*) from salesforce_account where accountKey.sourceKey is null
union all
select 'account name', count(*) from salesforce_account where accountName is null
union all
select 'created date', count(*) from salesforce_account where extSourceSystemAudit.createdDate is null
union all
select 'last updated date', count(*) from salesforce_account where extSourceSystemAudit.lastUpdatedDate is null;

XDM Business Campaign

select 'campaign source id', count(*) from salesforce_campaign where campaignKey.sourceId is null
union all
select 'campaign source type', count(*) from salesforce_campaign where campaignKey.sourceType is null
union all
select 'campaign source instance id', count(*) from salesforce_campaign where campaignKey.sourceInstanceId is null
union all
select 'campaign source key', count(*) from salesforce_campaign where campaignKey.sourceKey is null
union all
select 'campaign name', count(*) from salesforce_campaign where campaignName is null
union all
select 'created date', count(*) from salesforce_campaign where extSourceSystemAudit.createdDate is null
union all
select 'last updated date', count(*) from salesforce_campaign where extSourceSystemAudit.lastUpdatedDate is null;

XDM Business Campaign Member

select 'campaign member source id', count(*) from salesforce_campaign_member where campaignMemberKey.sourceId is null
union all
select 'campaign member source type', count(*) from salesforce_campaign_member where campaignMemberKey.sourceType is null
union all
select 'campaign member source instance id', count(*) from salesforce_campaign_member where campaignMemberKey.sourceInstanceId is null
union all
select 'campaign member source key', count(*) from salesforce_campaign_member where campaignMemberKey.sourceKey is null
union all
select 'campaign source id', count(*) from salesforce_campaign_member where campaignKey.sourceId is null
union all
select 'campaign source type', count(*) from salesforce_campaign_member where campaignKey.sourceType is null
union all
select 'campaign source instance id', count(*) from salesforce_campaign_member where campaignKey.sourceInstanceId is null
union all
select 'campaign source key', count(*) from salesforce_campaign_member where campaignKey.sourceKey is null
union all
select 'person source id', count(*) from salesforce_campaign_member where personKey.sourceId is null
union all
select 'person source type', count(*) from salesforce_campaign_member where personKey.sourceType is null
union all
select 'person source instance id', count(*) from salesforce_campaign_member where personKey.sourceInstanceId is null
union all
select 'person source key', count(*) from salesforce_campaign_member where personKey.sourceKey is null
union all
select distinct 'person type', b2b.personType from salesforce_campaign_member
union all
select 'member status', count(*) from salesforce_campaign_member where memberStatus is null
union all
select 'has responded', count(*) from salesforce_campaign_member where hasResponded is null
union all
select 'created date', count(*) from salesforce_campaign_member where extSourceSystemAudit.createdDate is null
union all
select 'last updated date', count(*) from salesforce_campaign_member where extSourceSystemAudit.lastUpdatedDate is null;

XDM Business Person

select 'person source id', count(*) from marketo_person where b2b.personKey.sourceId is null
union all
select 'person source type', count(*) from marketo_person where b2b.personKey.sourceType is null
union all
select 'person source instance id', count(*) from marketo_person where b2b.personKey.sourceInstanceId is null
union all
select 'person source key', count(*) from marketo_person where b2b.personKey.sourceKey is null
union all
select 'email', count(*) from marketo_person where workEmail.address is null
union all
select 'Lead - person status', count(*) from marketo_person where b2b.personType = 'Lead' and b2b.personStatus is null
union all
select 'Lead - is converted', count(*) from marketo_person where b2b.personType = 'Lead' and b2b.isConverted is null
union all
select distinct 'person type', b2b.personType from marketo_person
union all
select 'created date', count(*) from marketo_person where extSourceSystemAudit.createdDate is null
union all
select 'last updated date', count(*) from marketo_person where extSourceSystemAudit.lastUpdatedDate is null;
select 'person source id', count(*) from salesforce_contact where b2b.personKey.sourceId is null
union all
select 'person source type', count(*) from salesforce_contact where b2b.personKey.sourceType is null
union all
select 'person source instance id', count(*) from salesforce_contact where b2b.personKey.sourceInstanceId is null
union all
select 'person source key', count(*) from salesforce_contact where b2b.personKey.sourceKey is null
union all
select 'email', count(*) from salesforce_contact where workEmail.address is null
union all
select 'Lead - person status', count(*) from salesforce_contact where b2b.personType = 'Lead' and b2b.personStatus is null
union all
select 'Lead - is converted', count(*) from salesforce_contact where b2b.personType = 'Lead' and b2b.isConverted is null
union all
select distinct 'person type', b2b.personType from salesforce_contact
union all
select 'account source id', count(*) from salesforce_contact where b2b.personType = 'Contact' and personComponents[0].sourceAccountKey.sourceId is null
union all
select 'account source type', count(*) from salesforce_contact where b2b.personType = 'Contact' and personComponents[0].sourceAccountKey.sourceType is null
union all
select 'account source instance id', count(*) from salesforce_contact where b2b.personType = 'Contact' and personComponents[0].sourceAccountKey.sourceInstanceId is null
union all
select 'account source key', count(*) from salesforce_contact where b2b.personType = 'Contact' and personComponents[0].sourceAccountKey.sourceKey is null
union all
select 'created date', count(*) from salesforce_contact where extSourceSystemAudit.createdDate is null
union all
select 'last updated date', count(*) from salesforce_contact where extSourceSystemAudit.lastUpdatedDate is null;

XDM Business Opportunity

select 'opportunity source id', count(*) from salesforce_opportunity where opportunityKey.sourceId is null
union all
select 'opportunity source type', count(*) from salesforce_opportunity where opportunityKey.sourceType is null
union all
select 'opportunity source instance id', count(*) from salesforce_opportunity where opportunityKey.sourceInstanceId is null
union all
select 'opportunity source key', count(*) from salesforce_opportunity where opportunityKey.sourceKey is null
union all
select 'account source id', count(*) from salesforce_opportunity where accountKey.sourceId is null
union all
select 'account source type', count(*) from salesforce_opportunity where accountKey.sourceType is null
union all
select 'account source instance id', count(*) from salesforce_opportunity where accountKey.sourceInstanceId is null
union all
select 'account source key', count(*) from salesforce_opportunity where accountKey.sourceKey is null
union all
select 'opportunity name', count(*) from salesforce_opportunity where opportunityName is null
union all
select 'opportunity stage', count(*) from salesforce_opportunity where opportunityStage is null
union all
select 'is won', count(*) from salesforce_opportunity where isWon is null
union all
select 'is closed', count(*) from salesforce_opportunity where isClosed is null
union all
select 'expected close date', count(*) from salesforce_opportunity where expectedCloseDate is null
union all
select 'opportunity amount', count(*) from salesforce_opportunity where opportunityAmount.amount is null
union all
select 'currency code', count(*) from salesforce_opportunity where opportunityAmount.currencyCode is null
union all
select 'created date', count(*) from salesforce_opportunity where extSourceSystemAudit.createdDate is null
union all
select 'last updated date', count(*) from salesforce_opportunity where extSourceSystemAudit.lastUpdatedDate is null;

XDM ExperienceEvent

select 'id', count(*) from marketo_activity where _id is null
union all
select 'event type', count(*) from marketo_activity where eventType is null
union all
select 'timestamp', count(*) from marketo_activity where timestamp is null
union all
select 'person source id', count(*) from marketo_activity where personKey.sourceId is null
union all
select 'person source type', count(*) from marketo_activity where personKey.sourceType is null
union all
select 'person source instance id', count(*) from marketo_activity where personKey.sourceInstanceId is null
union all
select 'person source key', count(*) from marketo_activity where personKey.sourceKey is null
union all
select 'addToCampaign campaign id', count(*) from marketo_activity where eventType = 'leadOperation.addToCampaign' and leadOperation.addToCampaign.campaignKey.sourceId is null
union all
select 'addToCampaign campaign type', count(*) from marketo_activity where eventType = 'leadOperation.addToCampaign' and leadOperation.addToCampaign.campaignKey.sourceType is null
union all
select 'addToCampaign campaign instance id', count(*) from marketo_activity where eventType = 'leadOperation.addToCampaign' and leadOperation.addToCampaign.campaignKey.sourceInstanceId is null
union all
select 'addToCampaign campaign key', count(*) from marketo_activity where eventType = 'leadOperation.addToCampaign' and leadOperation.addToCampaign.campaignKey.sourceKey is null
union all
select 'statusInCampaignProgressionChanged campaign id', count(*) from marketo_activity where eventType = 'leadOperation.campaignProgression.campaignKey.sourceKey' and leadOperation.campaignProgression.campaignKey.sourceId is null
union all
select 'statusInCampaignProgressionChanged campaign type', count(*) from marketo_activity where eventType = 'leadOperation.campaignProgression.campaignKey.sourceKey' and leadOperation.campaignProgression.campaignKey.sourceType is null
union all
select 'statusInCampaignProgressionChanged campaign instance id', count(*) from marketo_activity where eventType = 'leadOperation.campaignProgression.campaignKey.sourceKey' and leadOperation.campaignProgression.campaignKey.sourceInstanceId is null
union all
select 'statusInCampaignProgressionChanged campaign key', count(*) from marketo_activity where eventType = 'leadOperation.campaignProgression.campaignKey.sourceKey' and leadOperation.campaignProgression.campaignKey.sourceKey is null;
select 'id', count(*) from salesforce_task where _id is null
union all
select 'event type', count(*) from salesforce_task where eventType is null
union all
select 'timestamp', count(*) from salesforce_task where timestamp is null
union all
select 'person source id', count(*) from salesforce_task where personKey.sourceId is null
union all
select 'person source type', count(*) from salesforce_task where personKey.sourceType is null
union all
select 'person source instance id', count(*) from salesforce_task where personKey.sourceInstanceId is null
union all
select 'person source key', count(*) from salesforce_task where personKey.sourceKey is null;

Conversion

select 'conversion rate', count(*) from currency_conversion_rate where conversionRate is null
union all
select 'end date', count(*) from currency_conversion_rate where endDate is null
union all
select 'start date', count(*) from currency_conversion_rate where startDate is null
union all
select 'source ISO Code', count(*) from currency_conversion_rate where sourceISOCode is null
union all
select 'target ISO Code', count(*) from currency_conversion_rate where targetISOCode is null
union all
select 'source id', count(*) from currency_conversion_rate where extSourceSystemAudit.externalKey.sourceId is null
union all
select 'source type', count(*) from currency_conversion_rate where extSourceSystemAudit.externalKey.sourceType is null
union all
select 'source instance id', count(*) from currency_conversion_rate where extSourceSystemAudit.externalKey.sourceInstanceId is null
union all
select 'source key', count(*) from currency_conversion_rate where extSourceSystemAudit.externalKey.sourceKey is null
union all
select 'created date', count(*) from currency_conversion_rate where extSourceSystemAudit.createdDate is null
union all
select 'last updated date', count(*) from currency_conversion_rate where extSourceSystemAudit.lastUpdatedDate is null;

We recommend using a calculated field in field mapping to default the field to a non-NULL value. The following are two examples:

  • If opportunityName of some opportunity records are null, create and use the following calculated field in field mapping

    • iif(name != null && trim(name) != "", name, "Unknown")
  • If leadOperation.campaignProgression.campaignID of some experienceevent records are null, create and use the following calculated field in field mapping

    • iif(leadOperation.campaignProgression.campaignID != null && leadOperation.campaignProgression.campaignID != "" , to_object("sourceType", "Marketo", "sourceInstanceID", "123-abc-321", "sourceID", leadOperation.campaignProgression.campaignID, "sourceKey", concat(leadOperation.campaignProgression.campaignID,"@123-abc-321.Marketo")), iif(eventType == "leadOperation.statusInCampaignProgressionChanged", to_object("sourceType", "Marketo", "sourceInstanceID", "123-abc-321", "sourceID", "Unknown", "sourceKey", "Unknown@123-abc-321.Marketo"), null))

On this page