This article presents all the fields used in standard e-commerce tables with the Retail 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.
| To generate this structure, you can use an e-commerce integration (Shopify, PrestaShop or Adobe Commerce) or create a project with an e-commerce structure and select the Retail option. |
ECommerce_Transaction
This table, linked to the ECommerce_Item table, contains the e-commerce transactions made by contacts. Each transaction is linked to a contact. The Transaction table is the central point for analyzing revenue and conversions.
| 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
This table contains the items associated with a transaction. Each record is linked to the Transaction and Product tables, allowing for analysis of purchase details, quantities, and prices.
| 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
This table contains information about products offered for sale. It is linked to the Cart Item and Transaction Item tables and allows carts and transactions to be enriched with product context (price, currency, category, status, inventory, URLs, source).
| 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
This table references the categories used to classify products. It is linked to the Product table as well as the cart item and transaction item tables to facilitate the segmentation, filtering and analysis of e-commerce data by product type.
| 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
This table contains information about shopping carts created by contacts and is linked to the ECommerce_CartItem table. The cart data allows for analysis of purchasing behavior before the transaction (cart status, creation and update dates, total amount, source, and associated visit).
| 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
This table contains details of the products added to a cart and is linked to the ECommerce_Cart table as well as the Product table (ECommerce_Product). The fields in the CartItem table allow you to analyze the composition of the carts, quantities, prices, and associated categories before the transaction is finalized.
| 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 |