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:
| ID of the contact (primary key) who made the transaction |
| idTransaction primary key | Transaction ID | NVarChar(100) | ID 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 | ID of the sendlog for Google Analytics |
| idBatch | Batch ID | Int | ID of the Batch for Google Analytics |
| Affiliation | Affiliation | NVarChar(500) | Affiliation data for Google Analytics (obsolete for 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 | Creation date of the transaction in the store |
| financialStatus | Transaction financial status | NVarChar(100) | Status of the order payment(e.g., "Paid", "Pending", "Refunded") |
| fulfillmentStatus | Transaction fulfillment status | NVarchChar(100) | Processing status of the order (e.g., "Processing", "Shipped", "Completed") |
| idVisit | Visit ID | BigInt | Used by Journey for tracking |
ECommerce_Item
| Code | Label | DataType | Description |
|---|---|---|---|
| idTransaction primary key | Transaction ID | NVarChar(100) | ID of the transaction linked to this item, associated with the Transaction table |
| ProductName | Product name | NVarChar(100) | Name of the product |
| Status | Status | NVarChar(50) | Status of the ticket (e.g., "Confirmed", "Cancelled") |
| 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) | ID of a variant in the third-party. NULL if there are no variants |
| idProductExternal | Id product external | NVarChar(100) | ID 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 | Item unit price |
| Price_total | Total price | Decimal | Total cost for the item (Price_unit x Quantity) |
ECommerce_Product
| Code | Label | DataType | Description |
|---|---|---|---|
| idProduct | Product ID | NVarChar(100) | ID of the product in the third-party solution |
| ProductName | Product name | NVarChar(100) | 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) | Product's Dialog Insight status in the store (e.g., "Active", "Inactive", "Sold Out") |
| 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) | 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) | Origin of the data imported for the product (e.g., external system, import) |
ECommerce_ProductCategory
| Code | Label | DataType | Description |
|---|---|---|---|
| idCategory primary key | Category ID | NVarChar(100) | ID 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) | Origin of the data imported for the cart (e.g., external system, import) |
| 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 | ID 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) | Data to retrieve a cart (generally a URL generated by some e-commerce integrations from Dialog Insight) |
ECommerce_CartItem
| Code | Label | DataType | Description |
|---|---|---|---|
| idCart primary key | Cart ID | NVarChar(500) | ID of the cart in the third-party solution |
| ProductName | Product name | NVarChar(100) | The name of the product |
| idProduct | Product ID | NVarChar(100) | ID of the product in the third-party solution |
| idVariantExternal | External variant ID | NVarChar(100) | ID of the variant in the third-party solution. NULL if there are no variants |
| idProductExternal | External product ID | NVarChar(100) | ID 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 |