Sales Data Standard is a nested structure that contains order information on the top level and has separate blocks for line items and refunds. You can extend it with custom fields on order and line item level, too. The data is integrated and stored as changes (also known as delta) in the Data Platform.
Schema in the Data Platform
Notes:
- The fields
row_total (price)
andquantity
can containfloat
values. - The
event id
on the plugin side contains the following fields:user
created at
order id
line item skus
refunds skus
[
{
"name": "event_id",
"type": "STRING",
"mode": "NULLABLE"
},
{
"name": "customer_id",
"type": "INTEGER",
"mode": "NULLABLE"
},
{
"name": "contact_id",
"type": "INTEGER",
"mode": "NULLABLE"
},
{
"name": "ems_user_id",
"type": "STRING",
"mode": "NULLABLE"
},
{
"name": "event_time",
"type": "TIMESTAMP",
"mode": "NULLABLE"
},
{
"name": "loaded_at",
"type": "TIMESTAMP",
"mode": "NULLABLE"
},
{
"name": "order_id",
"type": "STRING",
"mode": "NULLABLE"
},
{
"name": "sales_channel",
"type": "STRING",
"mode": "NULLABLE"
},
{
"name": "currency",
"type": "STRING",
"mode": "NULLABLE"
},
{
"name": "items",
"type": "RECORD",
"mode": "REPEATED",
"fields": [
{
"name": "item_id",
"type": "STRING",
"mode": "NULLABLE"
},
{
"name": "title",
"type": "STRING",
"mode": "NULLABLE"
},
{
"name": "quantity",
"type": "FLOAT",
"mode": "NULLABLE"
},
{
"name": "unit_price",
"type": "FLOAT",
"mode": "NULLABLE",
"description": "original price a.k.a listar"
},
{
"name": "discount_amount",
"type": "FLOAT",
"mode": "NULLABLE",
"description": "row total level"
},
{
"name": "row_total_gross",
"type": "FLOAT",
"mode": "NULLABLE"
},
{
"name": "row_total_net",
"type": "FLOAT",
"mode": "NULLABLE"
},
{
"name": "custom_fields",
"type": "RECORD",
"mode": "REPEATED",
"fields": [
{
"name": "k",
"type": "STRING",
"mode": "NULLABLE"
},
{
"name": "v",
"type": "STRING",
"mode": "NULLABLE"
}
]
}
]
},
{
"name": "shipping_cost",
"type": "FLOAT",
"mode": "NULLABLE"
},
{
"name": "custom_fields",
"type": "RECORD",
"mode": "REPEATED",
"fields": [
{
"name": "k",
"type": "STRING",
"mode": "NULLABLE"
},
{
"name": "v",
"type": "STRING",
"mode": "NULLABLE"
}
]
},
{
"name": "refunds",
"type": "RECORD",
"mode": "REPEATED",
"fields": [
{
"name": "refund_amount",
"type": "FLOAT",
"mode": "NULLABLE"
},
{
"name": "items",
"type": "RECORD",
"mode": "REPEATED",
"fields": [
{
"name": "item_id",
"type": "STRING",
"mode": "NULLABLE"
},
{
"name": "title",
"type": "STRING",
"mode": "NULLABLE"
},
{
"name": "quantity",
"type": "FLOAT",
"mode": "NULLABLE"
},
{
"name": "custom_fields",
"type": "RECORD",
"mode": "REPEATED",
"fields": [
{
"name": "k",
"type": "STRING",
"mode": "NULLABLE"
},
{
"name": "v",
"type": "STRING",
"mode": "NULLABLE"
}
]
}
]
},
{
"name": "custom_fields",
"type": "RECORD",
"mode": "REPEATED",
"fields": [
{
"name": "k",
"type": "STRING",
"mode": "NULLABLE"
},
{
"name": "v",
"type": "STRING",
"mode": "NULLABLE"
}
]
}
]
}
]
Payload from the Plug-in
Stream Onboarding example configuration:
{
"customerId": 211887606,
"subscriptionName": "projects/ems-private-cloud-staging/subscriptions/onboarding_211887606_ems_plugins_sales",
"subscriptionEventType": "sales_data",
"externalUserIdType": "email",
"mapping": {
"externalUserId": "email",
"eventType": null,
"eventId": "event_id",
"eventTime": "timestamp"
}
Client-facing schema
This is what clients using the Emarsys Plug-in see in Smart Insight (i.e. how the standard data is translated into Smart Insight filters):
Field name and path | Type | Description |
event_id | String | The unique ID for the event. It should remain the same across retries. |
user_id | String | The ID of the user. |
event_time | Timestamp | ISO8601, format: 2020-08-14T12:00:00[.000]Z |
order_id | String | The ID of the order. |
sales_channel | String | Available values:
|
currency | String | The currency of the purchase. It should be fix for a given customer and might be moved into the configuration. |
items | Array of records |
|
items.item_id | String | The ID of the purchased item. |
items.title | String | The product name of the item. |
items.quantity | Float | The amount of the purchased item. |
items.unit_price | Float | The original unit price without discounts. |
items.discount_amount | Float | On a row total level, not unit. |
items.row_total_gross | Float |
|
items.row_total_net | Float |
|
items.custom_fields | Array of records | Custom field for the item. |
items.custom_fields.k | String | The key of the custom field. |
items.custom_fields.v | String | The value of the custom field (it can be a plain or string or a JSON). |
shipping_cost | Float | The total shipping cost of the order. |
custom_fields | Array of records | Custom field for the order. |
custom_fields.k | String | The key of the custom field. |
custom_fields.v | String | The value of the custom field (it can be a plain string or a JSON). |
refunds | Record | Refunds for the order. |
refunds.refund_amount | Float | The total refund amount. |
refunds.items | Array of records. |
|
refunds.items.item_id | String | The ID of the purchased item. |
refunds.items.title | String | The product name of the item. |
refunds.items.quantity | Float | The amount of the purchased item. |
refunds.items.custom_fields | Array of records | Custom field for the item. |
refunds.items.custom_fields.k | String | The key of the custom field. |
refunds.items.custom_fields.v | String | The value of the custom field (it can be a plain string or a JSON). |
refunds.custom_fields | Array of records | Custom field for the refund. |
refunds.custom_fields.k | String | The key of the custom field. |
refunds.custom_fields.v | String | The value of the custom field (it can be a plain string or a JSON). |