DocumentationMarketo Measure Guide

Marketo Measure Ultimate Data Integrity Requirement marketo-measure-ultimate-data-integrity-requirement

Last update: Tue Jul 16 2024 00:00:00 GMT+0000 (Coordinated Universal Time)

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

Entity Object 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
For example - 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
For example - 333@999-abc-888.Marketo
b2b.personKey.sourceID
string
ID
Yes
For example - 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

Currency Conversion Data Requirements currency-conversion-data-requirements

Default Currency: In Marketo Measure, all revenue and cost are converted to a default currency at reporting time. There must be one record with the same date coverage for the target currency itself (e.g., USD to USD) with a conversion rate of 1.

Conversion Rates: Each (source currency, target currency) pair can have multiple conversion rates for different date periods. The rates must cover the entire time span from 0001-01-01 to 9999-12-31, as per the Salesforce DatedConversionRate object.

Date Range:

  • No overlapping date ranges within a (source currency, target currency) rate set (e.g., 2023-01-01 to 2023-02-01 and 2023-01-01 to 2024-01-01).
  • No gaps between date ranges. The start date is inclusive, and the end date is exclusive.

ExperienceEvent 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 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 subtype “Interesting Moment”.

Query Examples for Data Inspection 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 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 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 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 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 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 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 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))
recommendation-more-help
9deee213-85c8-4c42-8ba8-089345b91d20