--- title: COMMERCE schema reference slug: p19a0120 canonical_url: https://docs.coveo.com/en/p19a0120/ collection: coveo-analytics source_format: adoc --- # COMMERCE schema reference This article lists the [views](https://docs.snowflake.com/en/user-guide/views-introduction) available for inspecting Coveo commerce-related data within Snowflake and provides users with information stored in each view, enabling them to generate meaningful reports tailored to their specific needs. ## Commerce schema The `COMMERCE` schema has four views, which contain metrics specific to Coveo for Commerce implementations. The available public views are the following: * [CARTS](#carts) * [CART_ITEMS](#cart_items) * [TRANSACTIONS](#transactions) * [CONFIGS](#configs) ### CARTS The **CARTS** view contains information about all different carts (baskets) on which products were added. This view provides information such as a cart's total value and currency codes. #### `BASE_CURRENCY` The global ISO currency code used (defaults to `USD`). This value is used to enable sorting across different currencies. #### `BASE_EXCHANGE_RATE` The exchange rate used between the cart's currency and the [`BASE_CURRENCY`](#base_currency) at the time the event occurred. #### `BASE_TOTAL` The [`TOTAL`](#total) value of the cart, expressed in the [`BASE_CURRENCY`](#base_currency). #### `CART_ID` The unique identifier of each cart. Used as a primary key. If a purchase occurred, then the `CART_ID` will appear in the [TRANSACTIONS](#transactions) view as well. We currently only track transacted carts. > **Note** > > This value is automatically generated by Coveo to uniquely identify each transaction in the Snowflake database, and is always of the `string` data type. #### `CURRENCY` The currency used for the price of the products in the cart at the time of the cart event (for example, `USD`). This will typically be your home currency, or it may vary if you support multiple currencies. #### `ORGANIZATION_ID` The {% glossarylink label=Coveo organization's id=185 force_render=true %} unique identifier for this record. #### `TOTAL` The total value of the cart (the sum of the products' prices multiplied by the quantity), in the cart currency. #### `USER_ID` The unique identifier of the logged-in user. This foreign key references the [USERS](https://docs.coveo.com/en/o1jc0451#users) view where all the users are stored. The value can be `NULL` if the user is anonymous. For more information on Snowflake's supported constraint types, such as primary and foreign keys, see [Overview of Constraints](https://docs.snowflake.com/en/sql-reference/constraints-overview). ### CART_ITEMS The **CART_ITEMS** view contains information about the items contained in each cart to which products were added and purchased. Each item has information about the Coveo service for which the action of adding the item to the cart was attributed (that is, `search`, `listing`, or `recommendation`). For more information about the attribution process, see [Attribution at Coveo](https://docs.coveo.com/en/m1ae0440/). #### `ADD_EVENT_DATE` The date of the purchase event. #### `ADD_EVENT_ID` The unique identifier of the purchase event. This foreign key references the [`EVENTS`](https://docs.coveo.com/en/o1jc0451#events) view, establishing a one-to-one relationship. The value can't be `NULL`. #### `ADD_EVENT_TIMESTAMP` The timestamp of the purchase event, in UTC. #### `BASE_CURRENCY` The global ISO currency code used (defaults to `USD`). #### `BASE_EXCHANGE_RATE` The exchange rate used between the cart item's currency and the [`BASE_CURRENCY`](#base_currency) at the time the event occurred. #### `BASE_PRICE` The price of the item converted to the global currency (that is, the [`BASE_CURRENCY`](#base_currency)). #### `BASE_SHIPPING` (deprecated) The shipping cost associated with the item, expressed in the [`BASE_CURRENCY`](#base_currency). This column is deprecated and value will always be `0`. #### `CART_ID` The unique identifier of the cart. This foreign key references the [CARTS](#carts) view, establishing a many-to-one relationship. > **Note** > > This value is automatically generated by Coveo to uniquely identify each transaction in the Snowflake database, and is always of the `string` data type. #### `CART_ITEM_ID` The unique identifier of the item in the cart. Used as a primary key. #### `CURRENCY` The currency used for the price of the products in the cart (for example, `USD`) at the time of the cart event. This will typically be your home currency, or it may vary if you support multiple currencies. #### `INSIGHT_ID` The unique identifier of the insight from which the item was added to the transacted cart. This foreign key references the [`INSIGHTS`](https://docs.coveo.com/en/o1jc0451#insights) view, establishing a many-to-one relationship. The value is `NULL` if the item couldn't be attributed to any Coveo service. For more information about the attribution process, see [Attribution at Coveo](https://docs.coveo.com/en/m1ae0440/). #### `ITEM_ID` The unique identifier of the item being placed in a transacted cart. This foreign key references the [`ITEMS`](https://docs.coveo.com/en/o1jc0451#items) view where all the items are stored. The value can't be `NULL`. > **Note** > > This value is automatically generated by Coveo to uniquely identify each transaction in the Snowflake database, and is always of the `string` data type. #### `ORGANIZATION_ID` The {% glossarylink label=Coveo organization's id=185 force_render=true %} unique identifier for this record. #### `PRICE` The price of the item. #### `QUANTITY` The quantity of each product added to the cart. #### `SHIPPING` (deprecated) The shipping cost associated with each product in the cart. This column is deprecated and value will always be `0`. ### TRANSACTIONS The **TRANSACTIONS** view contains all purchase event information, such as the total amount, taxes, shipping costs, and associated `CART_ID`. #### `BASE_CART_TOTAL` The [`CART_TOTAL`](#cart_total) value for this transaction, expressed in the [`BASE_CURRENCY`](#base_currency-2). #### `BASE_CURRENCY` The global ISO currency code used for the transaction (defaults to `USD`). #### `BASE_EXCHANGE_RATE` The exchange rate used between the transaction's currency and the [`BASE_CURRENCY`](#base_currency-2) at the time the transaction occurred. #### `BASE_SHIPPING` The [`SHIPPING`](#shipping) value for this transaction, expressed in the [`BASE_CURRENCY`](#base_currency-2). #### `BASE_TAX` The [`TAX`](#tax) value for this transaction, expressed in the [`BASE_CURRENCY`](#base_currency-2). #### `BASE_TOTAL` The [`TOTAL`](#total-2) value for this transaction, expressed in the [`BASE_CURRENCY`](#base_currency-2). #### `CART_ID` The unique identifier of the cart. This foreign key references the [CARTS](#carts) view, establishing a one-to-one relationship. For more information on Snowflake's supported constraint types, such as primary and foreign keys, see [Overview of Constraints](https://docs.snowflake.com/en/sql-reference/constraints-overview). #### `CART_TOTAL` For this transaction, the total of product revenue in the cart, expressed in the transaction's [`CURRENCY`](#currency-3). #### `CURRENCY` The currency used for the price of the products in the cart (for example, `USD`) at the time of the cart event. This will typically be your home currency, or it may vary if you support multiple currencies. #### `DISCOUNT_CODE` Placeholder. The value will always be `NULL`. #### `ORGANIZATION_ID` The {% glossarylink label=Coveo organization's id=185 force_render=true %} unique identifier for this record. #### `PAYMENT_TYPE` Placeholder. The value will always be `NULL`. #### `SHIPPING` Total shipping cost for this transaction, expressed in the transaction's [`CURRENCY`](#currency-3). #### `SHIPPING_METHOD` Placeholder. The value will always be `NULL`. #### `TAX` Total amount of tax for this transaction, expressed in the transaction's [`CURRENCY`](#currency-3). #### `TOTAL` Gross total revenue of the transaction (including [`TAX`](#tax) and [`SHIPPING`](#shipping)) minus any discounts, expressed in the transaction's [`CURRENCY`](#currency-3). #### `TRANSACTION_DATE` The date of the purchase event. #### `TRANSACTION_EVENT_ID` The unique identifier of the transaction event. This foreign key references the [`EVENTS`](https://docs.coveo.com/en/o1jc0451#events) view, establishing a one-to-one relationship. The value can't be `NULL`. #### `TRANSACTION_ID` The unique identifier of each purchase event. Used as a primary key. This can be used to calculate the total number of purchase events. > **Note** > > This value is automatically generated by Coveo to uniquely identify each transaction in the Snowflake database, and is always of the `string` data type. > **Warning** > > After a successful transaction, if a visitor revisits the transaction confirmation page after 30 days, a second purchase event with the same [transaction `id`](https://docs.coveo.com/en/l29e0540#id-string-2) will be sent. > > However, if the visitor revisits the transaction confirmation page within 30 days, the purchase event is deduplicated, meaning that the transaction won't be counted twice. #### `TRANSACTION_TIMESTAMP` The date and time at which the purchase event occurred, in UTC. #### `TRANSACTION_XID` The external identifier for each transaction. The value is equal to the received `transactionId`. #### `VISIT_ID` The unique identifier of the [visit](https://docs.coveo.com/en/271/) in which the transaction occurred. This foreign key references the [`VISITS`](https://docs.coveo.com/en/o1jc0451#visits) view, establishing a many-to-one relationship. The value can't be `NULL`. For more information on Snowflake's supported constraint types, such as primary and foreign keys, see [Overview of Constraints](https://docs.snowflake.com/en/sql-reference/constraints-overview). ### CONFIGS The **CONFIGS** view contains information about Coveo for Commerce [product listing pages (PLPs)](https://docs.coveo.com/en/m1sf3187/) and [recommendation configurations](https://docs.coveo.com/en/o8880463/). #### `CONFIG_ID` The unique identifier of the [product listing page (PLP)](https://docs.coveo.com/en/m1sf3187/) or recommendation configuration. Used as a primary key. #### `CONFIG_XID` The external identifier for each [product listing page (PLP)](https://docs.coveo.com/en/m1sf3187/) or recommendation configuration. The value is equal to the received [product listing page (PLP)](https://docs.coveo.com/en/m1sf3187/) or recommendation configuration `id`. #### `NAME` The name of the [product listing page (PLP)](https://docs.coveo.com/en/m1sf3187/) or recommendation configuration. #### `FIRST_SEEN` The UTC timestamp indicating when the [product listing page (PLP)](https://docs.coveo.com/en/m1sf3187/) or recommendation configuration first appeared in the [Coveo organization](https://docs.coveo.com/en/185/)'s data. #### `LAST_SEEN` The UTC timestamp indicating when the [product listing page (PLP)](https://docs.coveo.com/en/m1sf3187/) or recommendation configuration last appeared in the [Coveo organization](https://docs.coveo.com/en/185/)'s data.