Reconciliation

How export files connect, which fields to join, and how to reconcile balances and counts.

Reconciliation: How the Data Connects

Use this reference to combine export files into one data model.

Load files correctly

• Delta files: Upsert by the documented primary key. Keep the newest updatedAt.

• Custom Events: Upsert by customEventId. Keep the latest file version.

• Snapshots: Replace the previous state. Do not merge snapshot files.

Campaign Visibility, Members Segments, and Wallet Balance are snapshots. A missing row is no longer in the current state.

Start with an *_all_until_YYYY_MM_DD.csv export, if available. Then load daily deltas in date order. Load reference data before event-style files.

Shared identifiers

Concept	Canonical source	Referencing columns
Tenant	Tenants → tenantId	tenantId, storeId, storeCode
Member	Members → memberId	memberId, customerId, targetId, referrerMemberId
Wallet	Wallet Balance → walletId	walletId, defaultAccount.accountId
Tier	Tiers → levelId	levelId, currentTierId, manuallyAssignedLevelId
Campaign	Campaigns → campaignId	campaignId, visibleCampaigns[]
Transaction	Transactions → transactionId	transactionId
Reward	Rewards → rewardId	rewardId, reward.rewardId
Segment	Segments → segmentId	segmentIds[], segments[]

All identifiers are UUIDs. They are unique across the environment.

Direct joins

• Join Campaign Calculated Effect Results to Campaign Execution with campaignExecutionId.

• Join Unit Transfers to Transactions, Campaigns, and Custom Events with their IDs.

• Join Rewards Redemption to Rewards through reward.rewardId.

• Unnest segmentIds and visibleCampaigns before joining them.

• Join Achievement Usage to Achievements with achievementId.

Reconcile wallet balances

For a wallet balance dated D, use transfers created before D+1 00:00 UTC.

• Add adding and p2p_adding transfers.

• Subtract spending, p2p_spending, blocked, and expired transfers.

• Exclude cancelled transfers.

• Exclude pending adding transfers from activeUnits.

points is always positive. Apply the sign from type before aggregating.

The following identity normally holds:

activeUnits = earnedUnits + transferredUnits − spentUnits − blockedUnits − expiredUnits

Prefer Wallet Balance over members.defaultAccount. Member profile balances can be stale.

Known limitations

No exported ID directly links an issued reward to its spending transfer. No ID directly links an effect result to its resulting transfer or issued reward. Match these records using member, campaign, amount, wallet type, and close timestamps.