Marketo Measure最終資料完整性需求 marketo-measure-ultimate-data-integrity-requirement

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

主題：
歸因

Marketo Measure會驗證傳入的AEP資料集，以確保資料充足且一致可歸因。若未符合資料完整性要求，資料集將遭到Marketo Measure系統拒絕。本文詳細說明資料完整性需求，提供資料檢查的查詢範例，並建議具有null值的必要欄位的解決方案。

實體物件 entity-object

XDM類別

xdm欄位群組

XDM路徑

XDM型別

資料Source欄位

必填？

附註

帳戶 (Marketo的Salesforce、公司和/或具名帳戶)

XDM商業帳戶

accountKey.sourceKey

字串

是

例如 — 123@999-abc-888.Marketo

accountKey.sourceID

字串

是

例如 — 123

accountKey.sourceInstanceID

字串

是

例如 — 999-abc-888

accountKey.sourceType

字串

是

例如 — Marketo

extSourceSystemAudit.createdDate

日期時間

CreatedDate

是

extSourceSystemAudit.lastUpdatedDate

日期時間

Modifieddate

是

XDM商業帳戶細節

帳戶名稱

字串

名稱

是

行銷活動 (Salesforce的行銷活動、Marketo的方案)

XDM商業活動

campaignKey.sourceKey

字串

是

例如 — 55555@999-abc-888.Marketo

campaignKey.sourceID

字串

是

例如 — 55555

campaignKey.sourceInstanceID

字串

是

例如 — 999-abc-888

campaignKey.sourceType

字串

是

例如 — Marketo

extSourceSystemAudit.createdDate

日期時間

CreatedDate

是

extSourceSystemAudit.lastUpdatedDate

日期時間

Modifieddate

是

campaignName

字串

名稱

是

campaignType

字串

CampaignType

否

用於頻道對應

XDM商業活動細節

channelname

字串

頻道名稱

否

用於頻道對應

campaignStartDate

日期時間

開始日期

否

針對行銷活動成本

campaignEndDate

日期時間

結束日期

否

針對行銷活動成本

actualCost.amount

數字

成本

否

針對行銷活動成本

actualCost.currencyCode

字串

^[A-Z]{3}$

CurrencyIsoCode

否

針對行銷活動成本

行銷活動成員 (Salesforce的行銷活動成員、Marketo的方案成員)

XDM商業活動會員

campaignMemberKey.sourceKey

字串

是

例如 — 987654321@999-abc-888.Marketo

campaignMemberKey.sourceID

字串

是

例如 — 987654321

campaignMemberKey.sourceInstanceID

字串

是

例如 — 999-abc-888

campaignMemberKey.sourceType

字串

是

例如 — Marketo

extSourceSystemAudit.createdDate

日期時間

CreatedDate

是

extSourceSystemAudit.lastUpdatedDate

日期時間

Modifieddate

是

personKey.sourceKey

字串

是

例如 — 333@999-abc-888.Marketo

personKey.sourceID

字串

潛在客戶ID或聯絡人ID

是

例如 — 333，根據資料來源表格，這有可能是銷售機會ID或聯絡人ID。

潛在客戶或聯絡人的外部索引鍵

personKey.sourceInstanceID

字串

是

例如 — 999-abc-888

personKey.sourceType

字串

是

例如 — Marketo

campaignKey.sourceKey

字串

是

例如 — 55555@999-abc-888.Marketo

campaignKey.sourceID

字串

行銷活動ID

是

例如 — 55555。

Campaign的外部索引鍵

campaignKey.sourceInstanceID

字串

是

例如 — 999-abc-888

campaignKey.sourceType

字串

是

例如 — Marketo

XDM商業活動會員細節

b2b.personType

字串

「銷售機會」或「聯絡人」

是

根據資料來源表格，這應該設定為「銷售機會」或「聯絡人」。建議在大部分使用案例中將它設為「聯絡」

memberstatus

字串

狀態

是

hasResponded

布林值

HasResponsed

是

firstRespondedDate

日期時間

FirstRespondedDate

否

人員 (Salesforce的聯絡人或銷售機會、Marketo的人員)

XDM 輪廓

XDM商業人士細節

b2b.personKey.sourceKey

字串

是

例如 — 333@999-abc-888.Marketo

b2b.personKey.sourceID

字串

是

例如 — 333，根據資料來源表格，這有可能是銷售機會ID或聯絡人ID

b2b.personKey.sourceInstanceID

字串

是

例如 — 999-abc-888

b2b.personKey.sourceType

字串

是

例如 — Marketo

workEmail.address

字串

電子郵件

是

b2b.personStatus

字串

狀態

是（僅限潛在客戶個人型別）

只有在b2b.personType為「銷售機會」時才需要

extSourceSystemAudit.createdDate

日期時間

CreatedDate

是

extSourceSystemAudit.lastUpdatedDate

日期時間

Modifieddate

是

b2b.isConverted

布林值

IsConverted

是（僅限潛在客戶個人型別）

只有在b2b.personType為「銷售機會」時才需要

b2b.personType

字串

「銷售機會」或「聯絡人」

是

根據資料來源表格，這應該設定為「銷售機會」或「聯絡人」。建議在大部分使用案例中將它設為「聯絡」

extendedWorkDetails.jobTitle

字串

否

XDM商業人士要素

personComponents.sourceAccountKey.sourceKey

字串

否

例如 — 123@999-abc-888.Marketo。

sourceAccountKey欄位集僅對真正的連絡人記錄為「必要」，定義為連結至帳戶的人員記錄。缺少它不會導致資料集遭到拒絕，但歸因結果將會關閉。

personComponents是陣列，但Marketo Measure僅取用第一個元素personComponents[0]

personComponents.sourceAccountKey.sourceID

字串

帳戶 ID

否

例如 — 123。

帳戶的外部索引鍵

personComponents.sourceAccountKey.sourceInstanceID

字串

否

例如 — 999-abc-888

personComponents.sourceAccountKey.sourceType

字串

否

例如 — Marketo

機會 (Salesforce的機會、Marketo的機會)

XDM商業機會

opportunityKey.sourceKey

字串

是

例如 — 77777@999-abc-888.Marketo

opportunityKey.sourceID

字串

是

例如 — 77777

opportunityKey.sourceInstanceID

字串

是

例如 — 999-abc-888

opportunityKey.sourceType

字串

是

例如 — Marketo

extSourceSystemAudit.createdDate

日期時間

CreatedDate

是

extSourceSystemAudit.lastUpdatedDate

日期時間

Modifieddate

是

accountKey.sourceKey

字串

是

例如 — 123@999-abc-888.Marketo

accountKey.sourceID

字串

帳戶 ID

是

例如 — 123。

帳戶的外部索引鍵

accountKey.sourceInstanceID

字串

是

例如 — 999-abc-888

accountKey.sourceType

字串

是

例如 — Marketo

機會名稱

字串

名稱

是

opportunestage

字串

階段

是

機會型別

字串

否

XDM商業機會詳細資料

isWon

布林值

IsWon

是

isClosed

布林值

IsClosed

是

expectedCloseDate

日期

關閉日期

是

opportunityAmount.amount

數字

數量

是

opportunityAmount.currencyCode

字串

^[A-Z]{3}$

CurrencyIsoCode

是

機會聯絡人角色（只有在計畫使用機會聯絡人角色作為購買群組進行歸因時才需要）

XDM商業機會個人關係

personKey.sourceKey

字串

是

例如 — 333@999-abc-888.Marketo

personKey.sourceID

字串

聯絡人ID

是

例如 — 333。

連絡人的外部索引鍵

personKey.sourceInstanceID

字串

是

例如 — 999-abc-888

personKey.sourceType

字串

是

例如 — Marketo

isPrimary

布林值

主要的

是

opportunityKey.sourceKey

字串

是

例如 — 77777@999-abc-888.Marketo

opportunityKey.sourceID

字串

機會 ID

是

例如 — 77777。

機會的外部索引鍵

opportunityKey.sourceInstanceID

字串

是

例如 — 999-abc-888

opportunityKey.sourceType

字串

是

例如 — Marketo

opportunityPersonKey.sourceKey

字串

是

例如 — 222222@999-abc-888.Marketo

opportunityPersonKey.sourceID

字串

是

例如 — 222222

opportunityPersonKey.sourceInstanceID

字串

是

例如 — 999-abc-888

opportunityPersonKey.sourceType

字串

是

例如 — Marketo

personRole

字串

角色

否

extSourceSystemAudit.createdDate

日期時間

CreatedDate

是

extSourceSystemAudit.lastUpdatedDate

日期時間

Modifieddate

是

轉換率(僅在使用多種貨幣時需要；只能將一個轉換率資料集啟用至Marketo Measure)

轉換

extSourceSystemAudit.externalKey.sourceKey

字串

是

例如 — 8888@0x012345.Salesforce

extSourceSystemAudit.externalKey.sourceId

字串

是

例如 — 8888

extSourceSystemAudit.externalKey.sourceInstanceId

字串

是

例如 — 0x012345

extSourceSystemAudit.externalKey.sourceType

字串

是

例如 — Salesforce

extSourceSystemAudit.createdDate

日期時間

CreatedDate

是

extSourceSystemAudit.lastUpdatedDate

日期時間

Modifieddate

是

isDeleted

布林值

是

幣別兌換率詳細資料

conversionRate

數字

ConversionRate

是

endDate

日期

NextStartDate

是

startDate

日期

開始日期

是

sourceISOCode

字串

ISOCode

是

例如EUR

targetisocode

字串

是

Marketo Measure中設定的預設貨幣代碼，例如USD

貨幣轉換資料需求 currency-conversion-data-requirements

預設貨幣：在Marketo Measure中，所有收入和成本在報告時都會轉換為預設貨幣。目標貨幣本身必須有相同日期涵蓋範圍的記錄（例如，USD至USD），其轉換率為1。

轉換率：每個（來源貨幣、目標貨幣）配對可以在不同的日期期間有多個轉換率。根據Salesforce DatedConversionRate物件，費率必須涵蓋從0001-01-01到9999-12-31的整個時間範圍。

日期範圍：

在（來源貨幣、目標貨幣）匯率集中沒有重疊的日期範圍（例如，2023-01-01到2023-02-01和2023-01-01到2024-01-01）。
日期範圍之間沒有間隙。開始日期不含，結束日期不含。

ExperienceEvent experienceevent

XDM類別

xdm欄位群組

XDM路徑

XDM型別

資料Source欄位

必填？

附註

活動

XDM ExperienceEvent

_id

字串

是

eventtype

字串

活動型別

是

timestamp

日期時間

活動日期

是

個人識別碼

personKey.sourceKey

字串

是

例如 — 333@999-abc-888.Marketo

personKey.sourceID

字串

潛在客戶ID或聯絡人ID

是

例如 — 333，根據資料來源表格，這有可能是銷售機會ID或聯絡人ID。

潛在客戶或聯絡人的外部索引鍵

personKey.sourceInstanceID

字串

是

例如 — 999-abc-888

personKey.sourceType

字串

是

例如 — Marketo

新增至行銷活動

leadOperation.addToCampaign.campaignKey.sourceKey

字串

是（僅適用於leadOperation.addToCampaign型別）

例如 — 55555@999-abc-888.Marketo

leadOperation.addToCampaign.campaignKey.sourceId

字串

行銷活動ID

是（僅適用於leadOperation.addToCampaign型別）

例如 — 55555。

Campaign的外部索引鍵

leadOperation.addToCampaign.campaignKey.sourceInstanceId

字串

是（僅適用於leadOperation.addToCampaign型別）

例如 — 999-abc-888

leadOperation.addToCampaign.campaignKey.sourceType

字串

是（僅適用於leadOperation.addToCampaign型別）

例如 — Marketo

促銷活動進度變更中的狀態

leadOperation.campaignProgression.campaignKey.sourceKey

字串

是（僅適用於leadOperation.campaignProgression型別）

例如 — 55555@999-abc-888.Marketo

leadOperation.campaignProgression.campaignKey.sourceId

字串

行銷活動ID

是（僅適用於leadOperation.campaignProgression型別）

例如 — 55555。

Campaign的外部索引鍵

leadOperation.campaignProgression.campaignKey.sourceInstanceId

字串

是（僅適用於leadOperation.campaignProgression型別）

例如 — 999-abc-888

leadOperation.campaignProgression.campaignKey.sourceType

字串

是（僅適用於leadOperation.campaignProgression型別）

例如 — Marketo

支援的體驗事件型別 experienceevent-type-supported

事件型別

XDM事件型別

說明

新銷售機會

leadOperation.newLead

用於記錄新行銷線索的建立和詳細資訊

轉換潛在客戶

leadOperation.convertLead

當行銷線索轉換為指派給銷售使用者的符合銷售條件的聯絡人時使用

有趣的時刻

leadOperation.interestingMoment

用於追蹤潛在客戶的高價值活動

填寫表單

web.formFilledOut

若有人填寫網頁上的表單時，可用於擷取詳細資訊

取消訂閱電子郵件

directMarketing.emailUnsubscribed

若有人取消訂閱電子郵件時，可用於擷取詳細資訊

開啟電子郵件

directMarketing.emailOpened

若有人開啟行銷電子郵件時，可用於擷取詳細資訊

按一下電子郵件

directMarketing.emailClicked

若有人點選行銷電子郵件中的連結時，可用於擷取詳細資訊

進度中的變更狀態

leadOperation.statusInCampaignProgressionChanged

若潛在客戶在促銷活動中的狀態改變時，可用於擷取詳細資訊

新增至參與計畫（新增至Nurture）

leadOperation.addToCampaign

用於將個人新增至特定行銷活動。

上表不支援的事件型別使用「有趣的時刻」事件型別。新增自訂欄位以指出子型別「有趣的時刻」。

資料檢查的查詢範例 query-examples-for-data-inspection

以下是檢查AEP資料湖中擷取資料集的查詢範例清單。若要對您的資料集使用這些資料集，請將下列查詢範例中的表格名稱取代為您實際的資料集表格名稱。

我們預期所有計數均為0。

對於personType欄位，我們預期只有「銷售機會」或「聯絡人」值，而且沒有null值。

對於所有「連絡人」個人記錄，我們預期會有「帳戶」外部索引鍵。

對於「銷售機會」人員記錄，「帳戶」外部索引鍵不存在且不是必要的。如果您選擇將「銷售機會」人員記錄擷取為「聯絡人」人員記錄（建議使用），則不需要此類人員記錄上的「帳戶」外部索引鍵。

XDM商業帳戶 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商業活動 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商業活動會員 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業務人員 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商業機會 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

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;

具有NULL值之必要欄位的建議解決方案 recommended-solution-for-required-fields-with-a-null-value

我們建議在欄位對應中使用計算欄位，以將欄位預設為非NULL值。以下是兩個範例：

如果某些機會記錄的opportunityName為Null，請在欄位對應中建立並使用下列計算欄位
- iif(name != null && trim(name) != "", name, "Unknown")
如果部分experienceevent記錄的leadOperation.campaignProgression.campaignID為Null，請在欄位對應中建立並使用以下計算欄位
- 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