Commerce-related views in Snowflake

This is for:

Business Analyst System Administrator
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 in regards to all common Coveo services. The available views are the following:

The following image shows how the COMMON schema is built, the views it contains, and how the different metrics interact with each other:

schema of the Common schema| Coveo

CLICKED_IMPRESSIONS

The CLICKED_IMPRESSIONS view contains all impressions that were clicked. It contains the same information as the IMPRESSIONS view.

CLICK_IMPRESSION_ID

The unique identifier of the impression that was clicked.

DEVICES

The DEVICES view provides information about identified devices, such as the browser, the OS, and the device category.

BROWSER

The browser used by this device.

BROWSER_WITH_VERSION

The full version of the browser used by this device.

DEVICE_CATEGORY

The category of the device used.

Example: smartphone

DEVICE_ID

The unique identifier of all identified devices.

FIRST_SEEN

The date and time this device first appeared in the data for this Coveo organization, in UTC.

LAST_SEEN

The date and time this device last appeared in the data for this organization, in UTC.

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.

OS

The operating system used by this device.

OS_VERSION

The full version of the operating system used by this device.

EVENTS

The EVENTS view contains all the events that happened during each visit/session. It gives a good overview of the user’s journey, by showing the sequence of events in a visit.

It provides information such as the event_type, the action_cause, as well as metadata displayed in a JSON object.

ACTION_CAUSE

The identifier of the user action that triggered a query and caused the interface to log an event (for example, documentOpen).

EVENT_ID

The unique identifier of each recorded event. We recommend using the value of the event_id to join the EVENTS view with other views.

Note

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

EVENT_TIMESTAMP

The date and time at which the event occurred, in UTC.

EVENT_TYPE

The type of the event (for example, search, click, purchase).

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.

ORIGIN_CONTEXT

The context of the user who performed the event (for example, the page or component they were on when performing the event).

PAGEVIEW_EVENT_ID

The unique identifier of the page view event that preceded this event, if any. This value is null if no page views were recorded before this event.

RAW_EVENT_XID

The unique identifier of each recorded event. The difference with the EVENT_ID is that this value is the one that was used by the Coveo Search API.

VISIT_ID

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

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

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

GEO_LOCATIONS

The GEO_LOCATIONS view provides information about the specific geographic location, such as the country, region, and city.

Note

The value aggregates down to the city level.

CITY

The name of the city recorded for this location, in English.

COUNTRY

The name of the country recorded for this specific location, in English.

FIRST_SEEN

The date and time this location first appeared in the data for this Coveo organization, in UTC.

GEO_LOCATION_ID

The unique identifier of the location of each visit. This value can be used to join the GEO_LOCATIONS view with other views such as the VISITS view.

LAST_SEEN

The date and time this location last appeared in the data for this organization, in UTC.

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.

REGION

The name of the region recorded for this specific location, in English.

IMPRESSIONS

The IMPRESSIONS view contains all results returned following a given search event.

Note

We assume that all products shown after the search event were viewed by the end user, whether they originated from a recommendation component, a listing page, or a search results list.

CLICK_CAUSE

If the returned item was clicked, this represents the actionCause of the associated click event.

The value of the CLICK_CAUSE is null if no clicks happened on the returned item, or if there was no cause reported for the associated click event.

CLICK_EVENT_ID

The unique identifier of the click event generated by the user when clicking this specific item. If the value is not null, this means that the item or product was clicked. When the item wasn’t clicked, the value is null.

IMPRESSION_ID

The unique identifier of each returned impression.

Note

Every single item returned following a given search event has a unique IMPRESSION_ID.

IMPRESSION_TIMESTAMP

The date and time at which the impression happened, in UTC.

INSIGHT_ID

The unique identifier of the search event that returned a list of items, as generated by Coveo to uniquely identify the event in the Snowflake database.

It consists of a foreign key used to join the IMPRESSIONS view with the INSIGHTS view.

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

ITEM_ID

The unique identifier of each item returned.

Note

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

It consists of a foreign key used to join the IMPRESSIONS view with the ITEMS view.

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

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.

POSITION

The position of each item or product returned following a given search event. The item position is the result of the combination of Coveo’s index ranking and machine learning algorithm optimizations.

VISIT_ID

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

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

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

INSIGHTS

The INSIGHTS view contains all instances where Coveo returned a list of items.

Each INSIGHT is attributed to one of the main Coveo services (that is, Search, Listing, or Recommendation).

Note that if the INSIGHT can’t be attributed to one of the main services, the value defaults to other.

For more information about the attribution process, see What is attribution?.

ACTION_CAUSE

The actionCause of the search event that returned a list of items.

ANONYMOUS

Whether user-based personalization was applied for this insight.

The value is true when personalization wasn’t applied, and false if personalization was applied.

EC_LISTING

The name of the listing page from which the insight was triggered.

INSIGHT_ID

The unique identifier of the search event that returned a list of items, as generated by Coveo to uniquely identify the event in the Snowflake database.

It serves as a primary key that you can join with other views, such as the PAGEVIEWS view. For more information on Snowflake’s supported constraint types, such as primary and foreign keys, see Overview of Constraints.

INSIGHT_TYPE

The Coveo touch point to which the insight was attributed (that is, Search, Listing, or Recommendation).

If Coveo couldn’t determine the touch point, the value defaults to other.

INSIGHT_XID

The unique identifier of the search event that returned a list of items, as read by the Coveo Search API (for example, the value of the event’s searchEventUid).

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.

ORIGIN

The origin’s identifier for this insight.

This value typically matches the value of the search hub from which the insight was generated.

PAGEVIEW_ID

The unique identifier of the page view event that preceded this execution.

It consists of a foreign key used to join the INSIGHTS view with the PAGEVIEWS view.

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

QUERY_EXPRESSION

The basic query expression (q) logged for this event.

RESPONSE_TIME

The date and time it took for the execution to return results.

RESULT_COUNT

The number of results returned for this insight.

SERVICE_EVENT_ID

The unique identifier of the source event that was logged for this insight.

It consists of a foreign key used to join the INSIGHTS view with the EVENTS view.

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

START_TIME

The date and time at which the insight was generated.

Note

It’s also the same as the date and time that the Coveo Search API was called.

VISIT_ID

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

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

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

ITEMS

The ITEMS view provides information about different items returned from a search event.

ITEM_ID

The unique identifier of each item returned.

Note

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

ITEM_NAME

The name of the item, as reported by incoming events.

ITEM_URL

The URL of the item, as reported by incoming events.

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.

PERMANENT_XID

The unique identifier of each item returned.

The difference with the ITEM_ID is that this value is the one returned by the catalog (the value of the item’s permanentid).

PAGES

The PAGES view provides information on unique pages contained in view events.

Note

URL parameters and fragments aren’t taken into account.

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.

PAGE_ID

The unique identifier of each page that was interacted with (clicked or viewed) on your website or application.

Note

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

PAGE_TITLE

The title of the page on which the view event was recorded.

PAGE_TYPE

The type of the page on which the view event was recorded.

Note

While this column is visible in the PAGE view, its value is unpopulated for the moment.

PAGE_URL

The page’s URL, excluding parameters or fragments.

PAGE_XID

The unique identifier of this page, as read by the Coveo Search API.

PAGEVIEWS

The PAGEVIEWS view lists the page view events that occurred on individual web pages.

It shows the specific page_url that was visited as well as a link to information on the specific PAGE instance.

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.

PAGE_ID

The unique identifier of the page that was viewed.

It consists of a foreign key used to join the PAGEVIEWS view with the PAGES view.

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

PAGEVIEW_ID

A sequential identifier to ensure each row in the table is unique. It serves as a primary key and can be joined with other views, such as the INSIGHTS view.

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

VIEW_EVENT_ID

The unique identifier of the view event sent by Coveo to Coveo Usage Analytics (Coveo UA).

It also serves as a foreign key with the EVENTS view, meaning you can join the two views using the values of the VIEW_EVENT_ID or EVENT_ID from the EVENTS view.

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

REFERRER

The page on which the user was before accessing the one that logged this specific view event.

For example, if a user visited the T-shirts page, then navigated to the Shoes page (which triggered this specific view event), the referrer will be the URL of the T-shirts page.

URL

The URL of the page in which the page view event was recorded, including parameters or fragments (if any).

VIEW_TIMESTAMP

The date and time when the page view event occurred, in UTC.

VISIT_ID

The unique identifier of the visit in which the page view event occurred.

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

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

SITES

The SITES view contains information about the different websites Coveo serves.

FIRST_SEEN

The date and time this site first appeared in the data for this Coveo organization, in UTC.

LAST_SEEN

The date and time this site last appeared in the data for this Coveo organization, in UTC.

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.

SITE_ID

The unique identifier of each site served by Coveo.

SITE_NAME

The name of the website for this site.

USERS

The USERS view contains the list of all users (excluding anonymous ones), along with information about the first time and last time they’ve generated events.

FIRST_SEEN

The date and time at which the user generated an event for the first time.

LAST_SEEN

The date and time at which the user generated their last event.

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.

USER_ID

The unique identifier for each user.

You can use this value to join the USERS view with the VISITS view.

VISITS/SESSIONS

The VISITS and SESSIONS views contain all the visits (sessions) contained in received events.

CLIENT_XID

The unique identifier of the browser client used for the visit.

DEVICE_ID

The unique identifier of the user’s device. It consists of a foreign key used to join the VISITS view with the DEVICES view.

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

GEO_LOCATION_ID

The unique identifier of the geolocation of the user during their visit. It consists of a foreign key used to join the VISITS view with the GEO_LOCATIONS view.

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

IS_BOT

Whether the visit was performed by a potential bot.

IS_INTERNAL

Whether the event performed by the user was internal based on the user’s IP address.

IP_HASH

The hashed IP address of the user who performed the visit.

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.

SITE_ID

The unique identifier of each website this client may be running. Each visit can be linked to only one site. It consists of a foreign key used to join the VISITS view with the SITES view.

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

USER_ID

The unique identifier of each user. It consists of a foreign key used to join the VISITS view with the USERS view.

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

If the user is anonymous, the value is null and it can’t be found in the user’s dimensions.

VISIT_END

The date and time the visit ended, in UTC.

VISIT_ID

The unique identifier for each visit.

Note

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

VISIT_START

The date and time the visit started, in UTC.

VISIT_XID

The unique identifier representing this visit. This Coveo UA generated value can be inspected in the Visit Browser (platform-ca | platform-eu | platform-au).

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.

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 integer 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.

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 What is attribution?.

BASE_PRICE

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

CART_ID

The unique identifier of each cart.

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 integer data type.

CART_ITEM_ID

The unique identifier of the item in the cart.

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 What is attribution?.

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 integer data type.

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_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_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.

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 integer data type.
Warning

If a user revisits the purchase confirmation page, a second transaction event with the same transaction id may be sent. In reporting, transaction events with identical identifiers are treated as duplicates, preventing multiple counts of the same purchase.

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.