Data schema
Available schemas and tables
At the moment, all tables are in theexportsdatabase
ProcessingOrders
Contains information about orders, balance changes, promoactions
| Table | Description | Key |
|---|---|---|
| BonusPointChanges | Balance changes events | id |
| BonusPointsMechanics | Bonus promoactions | id |
| NegativeCustomerBalanceChangeDetails | Dataset of the links between write-offs and accruals of points | id |
| Balances | Balances dictionary | id |
| Orders | Orders dataset: contains the total amount, date, and touchpoints of an order | id |
| Purchases | Order items dataset | orderId + lineNumber / lineId |
| PurchaseStatuses | Order items statuses dataset | internalId |
| PointsOfContact | Dataset of contact points: contains a list of touchpoints in which interactions with clients occur | id |
Mailings
Contains information about mailings, actions with the mailings
| Table | Description | Key |
|---|---|---|
| Mailings | Mailing campaign dictionary | id |
| SubscriptionTopics | Subscription topics dictionary | internalId |
| CustomerMessagesStatuses | Customer message statuses e.g. clicks, openings etc. | messageStatusId |
CDP
Contains general information about customer merges and campaigns folders
| Table | Description | Key |
|---|---|---|
| MergedCustomers | Customer merge events | unmergedCustomerId + mergedCustomerId |
| Folders | Folders dictionary | internalId |
| Segmentations | Segmentations dictionary | id |
| Segments | Segments dictionary | id |
| CustomerSegmentHistory | History of customer segments | id |
AbTests
Contains information about AB-tests and its participants
| Table | Description | Key |
|---|---|---|
| AbTests | AB-test dictionary | internalId |
| AbTestVariants | AB-tests variants dictionary | abTestId |
| ScenariosAbTestParticipants | Customer participation in AB tests | unmergedCustomerId + abTestId / variantId |
Why do you need a key?
To get the latest state of an entity or the state of an entity at a certain point in time, you will need a key - it will be used to group rows in the table and select suitable values.
Let's look at an example: you have already downloaded data for order id198123, but today information about an order update arrived. How can you get only one current record? For this, you can use, for example, this script:
SELECT *
FROM
Orders --orders dataset
ORDER BY
Orders._rowversion_ts DESC --sorting the table by date of change
LIMIT 1 BY Orders.id --we take one row for each unique idTable schema
Updated 7 months ago