COMMON schema reference

This is for:

Business Analyst System Administrator

This article provides a reference to the views found in the COMMON schema.

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 or click combination, used as primary key. Can be NULL.

INSIGHT_ID

Unique identifier of the insight responsible for impressing the item. Cannot be NULL. Keys into INSIGHTS.

ITEM_ID

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

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. Keys into VISITS.

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 data for the organization.

LAST_SEEN

UTC timestamp of when the device last appeared in the data for the organization.

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 the event was part of. Keys into VISITS.

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 data for the organization.

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 data for the organization.

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.

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 which were returned.

SERVICE_EVENT_ID

Reference to the source event which was logged for the insight. Keys into EVENTS.

START_TIME

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

VISIT_ID

Reference to the visit in which the insight was executed. Keys into VISITS.

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 and click combination. Used as the primary key.

IMPRESSION_TIMESTAMP

UTC timestamp of when the impression occurred.

INSIGHT_ID

Reference to the insight responsible for impressing the item. May be NULL if the impression wasn’t provided by Coveo. Keys into INSIGHTS.

ITEM_ID

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

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. Keys into VISITS.

ITEMS

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

Column name Description

ITEM_ID

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

ITEM_NAME

Item name as reported by incoming events.

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 page which was viewed. Keys into PAGES.

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. Keys into VISITS.

VIEW_EVENT_ID

Unique identifier of the page view event for the record. Keys into EVENTS.

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 data for the organization.

LAST_SEEN

UTC timestamp of when the site last appeared in the data for the organization.

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 data for the organization.

LAST_SEEN

UTC timestamp of when the user last appeared in the data for the organization.

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.

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.

VISIT_XID

UUID visit identifier for the visit. Equal to platform’s visitId.