This article presents all the fields used in standard e-commerce tables with the Retail structure (see how to generate this structure). This reference aims to provide a consistent view of the data model to simplify data integration, analysis, and use in Dialog Insight. For each table, you will find a list of the fields, including their code, label, type, and description.
ECommerce_Transaction
| Code | Label | DataType | Description |
|---|---|---|---|
| customer_id foreign key | Source Person ID | Depending on the primary key of the project:
| Identifier of the contact in the third-party solution. |
| idTransaction primary key | Transaction ID | NVarChar(100) | Identifier of the transaction in the third-party solution. |
| OrderName | The unique identifier for the order | NVarChar(500) | Name of the transaction. |
| idSendLog | Send log ID | Int | Identifier of the sendlog (Google Analytics). |
| idBatch | Batch ID | Int | Identifier of the Batch (Google Analytics). |
| Affiliation | Affiliation | NVarChar(500) | Obsolete with GA4. |
| Revenue | Revenue | Decimal | Gross revenue from the sold items. |
| Shipping | Shipping | Decimal | Shipping cost before taxes at the time the order was placed. |
| Tax | Tax | Decimal | Total of the taxes at the time the order was placed. |
| dtTransactionCreatedAt | Transaction creation date | DateTime | Creation date of the transaction in the store. |
| dtTransactionUpdatedAt | Date of the last update of the transaction | DateTime | Last modification of the transaction in the store. |
| dtTransaction | Transaction date | DateTime | |
| financialStatus | Transaction financial status | NVarChar(100) | Status of the order payment. |
| fulfillmentStatus | Transaction fulfillment status | NVarchChar(100) | Fulfillment status of the order. |
| idVisit | Visit ID | BigInt | Used by Journey for tracking. |
ECommerce_Item
| Code | Label | DataType | Description |
|---|---|---|---|
| idTransaction primary key | Transaction ID | NVarChar(100) | Identifier of the transaction. |
| ProductName | Product name | NVarChar(100) | The name of the product. |
| Status | Status | NVarChar(50) | |
| idProduct | Product ID | NVarChar(100) | Dialog Insight ID for the product. If the product has variants, the value represents the unique identifier of the third-party service variant. Otherwise, the value represents the unique identifier of the third-party service product. |
| idVariantExternal | Id variant external | NVarChar(100) | Identifier of a variant in the third-party. NULL if there are no variants. |
| idProductExternal | Id product external | NVarChar(100) | Identifier of the product in the third-party solution. |
| Category | Category name | NVarChar(100) | Name of the category. |
| idCategory | Category ID | NVarChar(100) | Identifier of the category. |
| Quantity | Quantity | Int | Quantity for a specific ordered item (product). |
| Price_unit | Unit price | Decimal | The item unit price. |
| Price_total | Total price | Decimal | The total cost of an item: the sum of the quantity (Price_unit x Quantity). |
ECommerce_Product
| Code | Label | DataType | Description |
|---|---|---|---|
| idProduct | Product ID | NVarChar(100) | Identifier of the product in the third-party solution. |
| ProductName | Product name | NVarChar(100) | The name of the product. |
| idCategory | Category ID | NVarChar(100) | Category Identifier. |
| Data | Data | JSON | Data from different structures can be displayed, but not used for any other functions, except for custom purposes. |
| Description | Description | NVarChar(500) | Product description. |
| CurrentPrice | Current price | Decimal | Current price in the store. |
| RegularPrice | Regular price | Decimal | Price before discounts. |
| Currency | Currency | NVarChar(5) | The selected currency in the E-commerce configuration. |
| SKU | SKU | NVarChar(100) | A SKU (Stock Keeping Unit) is a combination of unique characters used by retailers to identify and track products. |
| LastUpdate | Last modification date | DateTime | Last product update in the store. |
| Status | Status | NVarChar(50) | The product's Dialog Insight status in the store. Suggests active or inactive stings (may be something else, but not supported by the product list). |
| Source | Source | NVarChar(50) | Name of the source from which the product comes (e.g., the name of a Shopify store). |
| UrlProduct | Product url | NVarChar(500) | The link to see the product in the default store language. |
| UrlImageDefaut | Default image url | NVarChar(500) | Default image of the product. |
| InventoryQuantity | Inventory Quantity | Int | Inventory quantity. |
| sourceApplication | Product source application | NVarChar(100) | Source of the product. |
ECommerce_ProductCategory
| Code | Label | DataType | Description |
|---|---|---|---|
| idCategory primary key | Category ID | NVarChar(100) | Identifier of the category. |
| CategoryName | Category name | NVarChar(100) | Name of the category. |
ECommerce_Cart
| Code | Label | DataType | Description |
|---|---|---|---|
| customer_id foreign key | Source Person ID | NVarChar(100) | Identifier of the contact linked to the cart in the third-party solution. |
| idCart primary key | Cart ID | NVarChar(500) | Identifier of the cart. |
| sourceApplication | Cart source application | NVarChar(100) | |
| Status | Status | NVarChar(50) | If the cart is abandoned or is part of an order. |
| idSendLog | Send log ID | BigInt | Identifier of the sendlog (Used by Journey). |
| idBatch | Batch ID | Int | Identifier of the batch (used by Journey). |
| dtCreated | Creation date | DateTime | Cart creation date. |
| dtModified | Modification date | DateTime | Cart modification date. |
| TotalPrice | Total price | Decimal | Total price of the cart. |
| idVisit | Visit ID | BigInt | Used by Journey for tracking |
| RetrievalData | Data to retrieve carts | NVarChar(MAX) |
ECommerce_CartItem
| Code | Label | DataType | Description |
|---|---|---|---|
| idCart primary key | Cart ID | NVarChar(500) | Identifier of the cart in the third-party solution. |
| ProductName | Product name | NVarChar(100) | The name of the product. |
| idProduct | Product ID | NVarChar(100) | Identifier of the product in the third-party solution. |
| idVariantExternal | External variant ID | NVarChar(100) | Identifier of the variant in the third-party solution. NULL if there are no variants. |
| idProductExternal | External product ID | NVarChar(100) | Identifier of the product in the third-party solution. |
| Category | Category name | NVarChar(100) | Name of the category. |
| idCategory | Category ID | NVarChar(100) | Identifier of the category. |
| Quantity | Quantité | Int | Quantity for a specific ordered item (product). |
| PriceUnit | Unit price | Decimal | Unit price of the item. |
| dtCreated | Creation date | DateTime | Date and time of creation. |
| dtModified | Modification date | DateTime | Date and time of the modification. |