Commerce-related views in Snowflake

In this article

This article lists the views 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.

To access all the data listed in this article, you must have access to your Coveo data in Snowflake. See Manage raw data for instructions.

In Snowflake, Coveo data is structured within different views, which are categorized in two different schemas: the Common schema and the Commerce schema.

To access the schemas in Snowflake

Schemas are stored in the COVEO_CORE_MODEL_V001 database object.

  1. Under Find database objects, click COVEO_CORE_MODEL_V001.

  2. You should now see the COMMERCE and COMMON schemas, which correspond to the Commerce schema and Common schema respectively.

  3. Click these schemas to expand the related public views.

public views in the Snowflake database | Coveo

Common schema

The views of the COMMON schema contain metrics that cover all of the common Coveo services.

For complete reference documentation on the COMMON schema, see the COMMON schema reference.

Commerce schema

The COMMERCE schema has three views, which contain metrics specific to Coveo for Commerce implementations.

The available public views are the following:

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 at the time the event occurred.

BASE_TOTAL

The TOTAL value of the cart, expressed in the 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 view as well. We currently only track transacted carts.

Note

This value is automatically generated by Coveo to uniquely identify each cart 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 Coveo organization’s unique identifier for this record.

Note

The value isn’t necessarily unique as you could have production and non-production Coveo organizations.

For more information on non-production organizations, see About non-production organizations.

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 user who used this cart.

It consists of a foreign key used to join the CARTS view with the USERS view.

For more information on Snowflake’s supported constraint types, such as primary and foreign keys, see Overview of Constraints.

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.

ADD_EVENT_DATE

The date of the purchase event.

ADD_EVENT_ID

The unique identifier of the purchase event. Keys into the EVENTS table.

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 at the time the event occurred.

BASE_PRICE

The price of the item converted to the global currency (that is, the BASE_CURRENCY).

BASE_SHIPPING

The shipping cost associated with the item, expressed in the BASE_CURRENCY.

CART_ID

The unique identifier of each cart. Keys into the CARTS table.

If a purchase occurred, then the CART_ID will appear in the TRANSACTIONS view as well. We currently only track transacted carts.

Note

This value is automatically generated by Coveo to uniquely identify each cart 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 item when it was purchased (for example, USD).

INSIGHT_ID

The INSIGHT_ID from which an added to cart item is originally attributed to (that is, search, listing, or recommendation).

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.

This value can be used to join the CART_ITEMS view with the INSIGHTS view to retrieve the INSIGHT_TYPE.

ITEM_GROUP_XID

The unique identifier of a group that contains a bundle of products (see Product grouping).

If your Coveo organization leverages product group IDs, Coveo looks at this metric to compute attribution.

ITEM_ID

The unique identifier of each item for keying into the ITEMS table.

Each item maps to an entry in the Coveo index.

Note

This value is automatically generated by Coveo to uniquely identify each item in the Snowflake database, and is always of the string data type.

ORGANIZATION_ID

The Coveo organization’s unique identifier for this record.

Note

The value isn’t necessarily unique as you could have production and non-production Coveo organizations.

For more information on non-production organizations, see About non-production organizations.

PRICE

The price of the item.

PRODUCT_SKU

The SKU of the items present in the cart.

QUANTITY

The quantity of each product added to the cart.

SHIPPING

The shipping cost associated with each product in the cart.

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 value for this transaction, expressed in the BASE_CURRENCY.

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 at the time the transaction occurred.

BASE_SHIPPING

The SHIPPING value for this transaction, expressed in the BASE_CURRENCY.

BASE_TAX

The TAX value for this transaction, expressed in the BASE_CURRENCY.

BASE_TOTAL

The TOTAL value for this transaction, expressed in the BASE_CURRENCY.

CART_ID

The unique identifier of the cart that was part of the transaction.

It consists of a foreign key used to join the TRANSACTIONS view with the CARTS view.

For more information on Snowflake’s supported constraint types, such as primary and foreign keys, see Overview of Constraints.

CART_TOTAL

For this transaction, the total of product revenue in the cart, expressed in the transaction’s CURRENCY.

CURRENCY

The ISO currency code used for this transaction (for example, USD).

DISCOUNT_CODE

The discount code applied to this transaction.

Note

This value doesn’t specify the amount of the discount.

ORGANIZATION_ID

The Coveo organization’s unique identifier for this record.

Note

The value isn’t necessarily unique as you could have production and non-production Coveo organizations.

For more information on non-production organizations, see About non-production organizations.

PAYMENT_TYPE

The type of payment that was used for this transaction.

SHIPPING

Total shipping cost for this transaction, expressed in the transaction’s CURRENCY.

SHIPPING_METHOD

The shipping method applied to this transaction.

TAX

Total amount of tax for this transaction, expressed in the transaction’s CURRENCY.

TOTAL

Gross total revenue of the transaction (including TAX and SHIPPING) minus any discounts, expressed in the transaction’s CURRENCY.

TRANSACTION_DATE

The date of the purchase event.

TRANSACTION_EVENT_ID

The unique identifier of the event that logged this transaction (transaction event).

You can use this value to join the TRANSACTION view with the EVENTS view.

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 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 unique identifier of each purchase event.

The difference between TRANSACTION_XID and the TRANSACTION_ID is that the TRANSACTION_XID is the one used in the payload of the request to Coveo UA.

VISIT_ID

The unique identifier of the visit in which the transaction occurred.

It consists of a foreign key used to join the TRANSACTIONS view with the VISITS view.

For more information on Snowflake’s supported constraint types, such as primary and foreign keys, see Overview of Constraints.