COMMON schema reference

This is for:

Business Analyst System Administrator

This article provides a reference to the views found in the COMMON schema of the CORE data model.

CLICKED_IMPRESSIONS

This view consists of all clicked impressions (search results), as well as references to their impression data and insights.

Notes

  • Impressions clicked more than once will appear multiple times in this view.

  • Direct add-to-cart events, where a product is added to the cart directly from a search results list, a product listing page (PLP), or a recommendations slot without visiting the product detail page (PDP), are logged as clicked impressions.

  • Clicked impressions reference an impression from the insight_id through the impression_id. However, in cases of data error, position error, pinning, or customer-injected items in the display, the data provided in the clicked impression isn’t sufficient to locate the item in the result list. In such cases, the impression_id for the clicked impression will be null. To avoid losing these clicked impressions in joins with the IMPRESSIONS view, a left join is recommended ensuring that clicked impressions without a matching impression_id are still included in the results.

  • This view only includes clicks that followed an insight (generated by a search, listing page, or recommendation component). Therefore if the insight referenced in the click isn’t located through its searchId or insightId, the click is discarded from the model.
Column name Description

CLICK_CAUSE

The identifier of the action that triggered the click event (for example, documentOpen). Can be NULL if no cause is reported.

CLICK_EVENT_ID

Reference to the click event if the item was clicked or otherwise interacted with. Keys into EVENTS.

CLICKED_IMPRESSION_DATE

Date of when the impression was clicked.

CLICKED_IMPRESSION_ID

Unique identifier of the impression and click combination, used as primary key.

CLICKED_IMPRESSION_TIMESTAMP

UTC timestamp of when the impression was clicked.

IMPRESSION_ID

Unique identifier of the impression. This foreign key references the IMPRESSIONS view, establishing a many-to-one relationship, and can be NULL.

INSIGHT_ID

Unique identifier of the insight responsible for impressing the item. This foreign key references the INSIGHTS view, establishing a many-to-one relationship, and can’t be NULL.

ITEM_ID

Unique identifier of the clicked item. This foreign key references the ITEMS view where all the items are stored, and can’t be NULL.

ORGANIZATION_ID

Organization identifier for the record.

POSITION

Position of the clicked impression in a results list. One-based, so position of the first item will be 1.

VISIT_ID

Unique identifier of the visit. This foreign key references the VISITS view, establishing a many-to-one relationship, and can’t be NULL.

DEVICES

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

Column name Description

BROWSER

Browser used by the device.

BROWSER_VERSION

Browser with full version used by the device.

DEVICE_CATEGORY

Categorization of the device, such as game console, personal computer, smartphone, or other. May be NULL.

DEVICE_ID

Unique identifier of the device. Used as the primary key.

FIRST_SEEN

UTC timestamp of when the device first appeared in the organization data.

LAST_SEEN

UTC timestamp of when the device last appeared in the organization data. Can be updated.

OS

Operating system used by the device.

OS_VERSION

Operating system with full version.

ORGANIZATION_ID

Organization identifier for the record.

EVENTS

The EVENTS view contains all the events that occurred during each visit. It gives an overview of the user’s journey by showing the sequence of events in a visit, and also provides information such as the EVENT_TYPE and the ACTION_CAUSE.

Column name Description

EVENT_DATE

Date of when the event occurred.

EVENT_ID

Unique identifier of the event, used as primary key.

EVENT_TIMESTAMP

Timestamp of the event in UTC.

EVENT_TYPE

Reported event type, either from COMMON events or custom events.

ORGANIZATION_ID

Organization identifier for the record.

RAW_EVENT_XID

Reference to the RAW event ID. Keys into RAW.EVENTS.

VISIT_ID

Unique identifier of the visit. This foreign key references the VISITS view, establishing a many-to-one relationship, and can’t be NULL.

FACETS

The FACETS view provides information about the facets that were interacted with during a visit.

Column name Description

FACET_ID

Unique identifier of the facet. Used as the primary key.

FACET_TYPE

Type of the facet. May have values such as dateRange, numericalRange, hierarchical, specific.

FIELD

Field name of the facet.

FIRST_SEEN

The UTC timestamp indicating when the facet first appeared in the organization data.

ID

Identifier of the facet.

LAST_SEEN

The UTC timestamp indicating when the facet last appeared in the organization data.

ORGANIZATION_ID

Organization identifier for the record.

FACET_VALUES

The FACET_VALUES view provides information about the different facet and value combinations that were interacted with during a visit.

Column name Description

FACET_VALUE_ID

Unique identifier of the facet and value combination. Used as the primary key.

FIRST_SEEN

The UTC timestamp of when the facet and value combination first appeared in the organization data.

LAST_SEEN

The UTC timestamp of when the facet and value combination last appeared in the organization data.

ORGANIZATION_ID

Organization identifier for the record.

VALUE

Value of the facet.

GEO_LOCATIONS

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

Notes

  • The value aggregates down to the city level.

  • The geographical names are in English.
Column name Description

CITY

City name of the location.

CONTINENT

Continent name of the location.

COUNTRY

Country name of the location.

COUNTRY_IS_IN_EUROPEAN_UNION

Whether the country is part of the European Union.

FIRST_SEEN

UTC timestamp of when the location first appeared in the organization data.

GEO_LOCATION_ID

Unique identifier of the geographical location. Used as the primary key.

LAST_SEEN

UTC timestamp of when the location last appeared in the organization data. Can be updated.

ORGANIZATION_ID

Organization identifier for the record.

ORIGIN_TIMEZONE

Time zone of the location.

REGION

Region name of the location.

INSIGHTS

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

Column name Description

ACTION_CAUSE

The UI-reported reason why the insight was executed. See actionCause.

ANONYMOUS

Set to TRUE only if the insight was run without considering user-based personalization.

CONFIG_ID

Unique identifier of the listing or recommendation configuration. Used as a primary key.

CORRECTED_QUERY_EXPRESSION

(Optional) Corrected query expression if the query correction logic has been applied.

EC_LISTING

(Commerce only) Name of the listing page which triggered the event.

INSIGHT_DATE

Date of when the insight’s execution was started.

INSIGHT_ID

Unique identifier of the insight. Used as the primary key.

INSIGHT_TYPE

The type of algorithm that was executed. Type can be either search, listing, recommendation, or other. New types may be added when required.

INSIGHT_XID

External identifier for the insight. Typically, the platform’s searchId for Coveo-driven insights.

LOCALE

The locale of the current user. Must comply with IETF’s BCP 47 definition.

ORIGIN

Origin identifier for the insight. Typically the platform’s searchHub for Coveo-powered insights.

ORGANIZATION_ID

Organization identifier for the record.

PAGEVIEW_ID

Reference to the pageview which spawned the execution. Keys into PAGEVIEWS.

QUERY_EXPRESSION

Basic query expression for the event.

QUERY_PIPELINE

The name of the query pipeline that processed the query.

RESPONSE_TIME

Duration it took for the execution to return results.

RESULT_COUNT

The number of results returned.

Note

This value is returned before any additional facets are selected. Therefore, the result count can greater than 0 while there are no impressions recorded for the insight.

SERVICE_EVENT_ID

Unique identifier of the insight event. This foreign key references the EVENTS view, establishing a one-to-one relationship, and can’t be NULL.

START_TIME

UTC timestamp of when the insight’s execution was started.

VISIT_ID

Unique identifier of the visit in which the insight was executed. This foreign key references the VISITS view, establishing a many-to-one relationship, and can’t be NULL.

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.
Column name Description

CLICK_CAUSE (deprecated)

The identifier of the action that triggered the click event.

Note

This column is deprecated. See the CLICK_CAUSE column of the CLICKED_IMPRESSIONS view.

CLICK_EVENT_ID (deprecated)

Unique identifier of the click event.

Note

This column is deprecated. See the CLICK_EVENT_ID column of the CLICKED_IMPRESSIONS view.

IMPRESSION_DATE

Date of when the impression occurred.

IMPRESSION_ID

Unique identifier of the impression. Used as the primary key.

IMPRESSION_TIMESTAMP

UTC timestamp of when the impression occurred.

INSIGHT_ID

Unique identifier of the insight responsible for impressing the item. This foreign key references the INSIGHTS view, establishing a many-to-one relationship, and can’t be NULL.

ITEM_ID

Unique identifier of the item being impressed. This foreign key references the ITEMS view where all the items are stored, and can’t be NULL.

ORGANIZATION_ID

Organization identifier for the record.

POSITION

Position of the impression in a results list. One-based, therefore the position of the first item will be 1.

VISIT_ID

Unique identifier of the visit in which the impression occurred. This foreign key references the VISITS view, establishing a many-to-one relationship, and can’t be NULL.

INSIGHT_FACET_STATES

The INSIGHT_FACET_STATES view contains all insights where facets were used.

Column name Description

FACET_ID

Unique identifier of the facet. Keys into FACETS.

FACET_POSITION

Position of the facet.

FACET_VALUE_ID

Unique identifier of the facet and value combination. Keys into FACET_VALUES.

FACET_VALUE_POSITION

Position of the value in the facet.

INSIGHT_DATE

Date of when the insight’s execution was started.

INSIGHT_ID

Unique identifier of the insight. Keys into INSIGHTS.

ORGANIZATION_ID

Organization identifier for the record.

START_TIME

The UTC timestamp indicating when the insight’s execution was started.

STATE

State of the facet. The values can be either idle or selected.

ITEMS

The ITEMS view provides information about different items returned from search and click events.

Note

While the ITEMS view logs details from both search and click events, the item data received may only include limited information, such as the ITEM_ID. Values for ITEM_NAME and ITEM_URL are only included when the corresponding item has been clicked. This ensures data security as sensitive information about items that were only searched for but not clicked aren’t exposed.
Column name Description

ITEM_DATA

Any detailed information on the item. The amount and type of content may vary per use case. For commerce customers, this will include product categories and brand, which are sourced from the product catalog.

ITEM_ID

Unique identifier of the item. Used as the primary key.

ITEM_NAME

Item name as reported by incoming events or enriched by the item catalog.

ITEM_URL

Item URL as reported by incoming events.

ORGANIZATION_ID

Organization identifier for the record.

PERMANENT_XID

Index identifier of the item.

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.

Column name Description

ORGANIZATION_ID

Organization identifier for the record.

PAGE_ID

Unique identifier of the viewed page. This foreign key references the PAGES view, establishing a many-to-one relationship, and can’t be NULL.

PAGEVIEW_ID

Unique identifier of the page view event, used as primary key.

REFERRER

Page that was visited just before the page view event was logged.

VISIT_ID

Unique identifier of the visit in which the page view occurred. This foreign key references the VISITS view, establishing a many-to-one relationship, and can’t be NULL.

VIEW_EVENT_ID

Unique identifier of the page view event. This foreign key references the EVENTS view, establishing a one-to-one relationship, and can’t be NULL .

VIEW_TIMESTAMP

UTC timestamp of when the page view event occurred.

URL

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

PAGES

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

Note

URL parameters and fragments aren’t taken into account.
Column name Description

ORGANIZATION_ID

Organization identifier for the record.

PAGE_ID

Unique identifier of the page. Used as primary key.

PAGE_TITLE

Title of the page.

PAGE_TYPE

Type of the page.

PAGE_URL

URL of the page, excluding any parameters or fragments.

PAGE_XID

Index identifier of the page, if it was part of the index.

SITES

The SITES view provides information about the different websites that Coveo serves.

Column name Description

FIRST_SEEN

UTC timestamp of when the site first appeared in the organization data.

LAST_SEEN

UTC timestamp of when the site last appeared in the organization data. Can be updated.

ORGANIZATION_ID

Organization identifier for the record.

SITE_ID

Unique identifier of the site. Used as the primary key.

SITE_NAME

Passed website or tracking ID.

TRACKING_ID

Passed website or tracking ID.

USERS

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

Column name Description

FIRST_SEEN

UTC timestamp of when the user first appeared in the organization data.

LAST_SEEN

UTC timestamp of when the user last appeared in the organization data. Can be updated.

ORGANIZATION_ID

Organization identifier for the record.

USER_ID

Unique identifier of the user. Used as primary key.

USER_XID

Unique identifier of the user, derived from passed userId or the user’s authentication in logged events.

VIEWED_ITEMS

The VIEWED_ITEMS view provides information about the items that were viewed during a visit.

Column name Description

EVENT_DATE

The date the event occurred.

EVENT_ID

Unique identifier of the event, used as primary key. Can be used to link to EVENTS.

EVENT_TIMESTAMP

Timestamp of the event in UTC.

ITEM_ID

Unique identifier of the item being viewed. Keys into ITEMS.

ITEM_TYPE

The type of item viewed, either product or document.

ORGANIZATION_ID

Organization identifier for this record.

RAW_EVENT_XID

Reference to the RAW event ID. Keys into RAW.EVENTS.

REFERRER

Page which was visited just before the page view event was logged.

VISIT_ID

Unique identifier of the visit in which the page view occurred. Keys into VISITS view

VISITS

The VISITS view provides all the visits within received events.

Column name Description

CLIENT_XID

UUID client identifier for the visit. Equal to received clientId.

DEVICE_ID

Unique identifier of the device used. Keys into DEVICES.

GEO_LOCATION_ID

Unique identifier of the visit’s origin location. Keys into GEO_LOCATIONS.

IP_HASH

One-way hashed IP address.

IS_BOT

Whether the visit was flagged as a potential bot visit.

IS_INTERNAL

Whether the IP address was flagged as customer internal.

ORGANIZATION_ID

Organization identifier for the record.

SITE_ID

Unique identifier for the site or web property. Keys into SITES.

USER_ID

Unique identifier of the user and equal to the platform’s userId. Keys into USERS. Will be NULL for anonymous visits.

VISIT_END

UTC end time of the visit.

VISIT_ID

Unique identifier of the visit. Used as primary key.

VISIT_START

UTC start time of the visit.