Flexible Sales Data:: Flexible Sales Data Standard – Emarsys

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) and quantity can contain float 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"
                    }
                ]
            }
        ]
    }
]

Click to copy

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"
}

Click to copy

Client-facing schema

In the following tables we have collected the fields that clients using the Emarsys Plug-in see in Smart Insight (i.e. how the standard data is translated into Smart Insight filters):

Standard fields

The following fields are mandatory. If they are missing from your sales data file, the event will be dropped.

Field name and path	Type	Description
`event_id`	String	The unique ID for the event. It should remain the same across retries.
`event_time`	Timestamp	ISO8601, format: 2020-08-14T12:00:00[.000]Z Note: The Emarsys plug-in for Shopify uses `processed_at` as the default date field and syncs it via Flexible Sales Data (all date fields are synchronized by the plug-in as well and can be used for further segmentation by Smart Insight). For more information, see 5. Uploading Shopify orders to Emarsys.
`order_id`	String	The ID of the order.
`items`	Array of records
`items.item_id`	String	The ID of the purchased item.
`items.quantity`	Float	The amount of the purchased item.
`items.row_total_gross`	Float

Required identifier fields

Your sales data file has to contain at least one of the following identifier fields.

Field name and path	Type	Description
`user_id`	String	The ID of the user. Notes: Unidentified order events are stored in the sales data (in the `sales_orders_raw` tables) and the `user_id` field is set to null. This field might be empty in the retail use case. Please see `store_id`.
`store_id`	String	The identifier of the store if you plan to implement the retail use case.

Optional fields

The following fields are optional and can be used to fine-tune segmentation, personalisation, analytics services.

Field name and path	Type	Description
`sales_channel`	String	Available values: `online` `offline` Note: Further values are available depending on your commerce platform. For example, the following values are available for the `source_name` field for Shopify: `web`, `pos`, `shopify_draft_order`, `iphone`, `android`.
`currency`	String	The currency of the purchase. It should be fix for a given customer and might be moved into the configuration.
`items.title`	String	The product name of the 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_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.
`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.

Custom fields

The following custom fields might be required for your individual setup. If you are using custom fields, please align your segmentation, personalisation and analytics setup with these fields.

Field name and path	Type	Description
`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.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).

Loyalty fields

If you are using Emarsys Loyalty, you will also need to have these fields in your sales data onboarded through Flexible Sales Data to take advantage of all the benefits offered by this feature out-of-the-box.

Cancellation and refunds requirements for Loyalty

When submitting returned items or orders, always use the order, customer and item IDs of the original order and provide a negative value in both the price and the quantity fields, as well as in f_original_price. Please make sure to populate the fields in your sales data file this way, otherwise refunds will not be properly represented in your reports.

Field name and path	Type	Description
`s_market`	String	The market where this sale was made. If the client has multiple countries or regions, it can be the country or region. If the client has different web shops, it can be the name of the shop.
`s_original_currency`	String	The original currency the sale was made in. Please always use a three-letter abbreviation as described here.
`f_original_price`	Float	The total price of the items (the unit price multiplied by quantity) in the original currency. Please always use the period (`.`) as the decimal mark (i.e.`10.5`, NOT `10,5`). Do NOT use any thousands separator (i.e. `2000`, NOT `2,000`). Do NOT include the currency. For refunds, always submit a negative value.
`s_coupon`	String	Please insert here all coupons and voucher codes your user used at checkout. If the user used more than one, you can a dd multiple codes by separating them with a comma (`,`). If the coupon was not used for a specific item, you can send the coupon code for all items purchased, we will only consider the unique codes per each order. Loyalty uses this field for the referral program to know that the user referred made a purchase with the coupon code received from a loyalty member. For more information, see Referral program.

Need assistance with your Emarsys product?
We'll get you the help you need!

Preparing for Integration

Schema in the Data Platform

Payload from the Plug-in