COMMON schema reference

This is for:

Business Analyst System Administrator

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

CLICKED_IMPRESSIONS

The CLICKED_IMPRESSIONS view contains all impressions that were clicked. It contains the same information as the IMPRESSIONS view. It’s effectively a filtered view of impressions that have been clicked multiple times and appear multiple times in the table.

Column name Description

CLICK_CAUSE

The identifier of the action that triggered the click event (for example, documentOpen). May 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.

IMPRESSION_ID

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

IMPRESSION_TIMESTAMP

UTC timestamp of when the impression occurred.

INSIGHT_ID

Unique identifier of the insight responsible for impressing the item. May be NULL if no impression was 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 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_WITH_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, the ACTION_CAUSE, as well as metadata displayed in a JSON object.

Column name Description

ACTION_CAUSE

Action cause reported on the event, or NULL if none.

CUSTOM_DATA

JSON object with custom data for the event.

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.

ORIGIN_CONTEXT

Origin level 1 reported on the event, or NULL if none.

ORGANIZATION_ID

Organization identifier for the record.

PAGEVIEW_EVENT_ID

Unique identifier of the pageview event that preceded the event or NULL if none.

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.

COUNTRY

Country name of the location.

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.

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.

EC_LISTING

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

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.

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.

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

The identifier of the action that triggered the click event. May be NULL if there was no click or if no cause was reported.

CLICK_EVENT_ID

Unique identifier of the click event. May be NULL if the item wasn’t clicked. Keys into EVENTS.

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_DATA

Optional additional information for the item.

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.

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.

VISITS

The VISITS view provides all the visits within received events.

Column name Description

ORGANIZATION_ID

Organization identifier for the record.

VISIT_ID

Unique identifier of the visit. Used as primary key.

CLIENT_XID

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

VISIT_XID

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

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.

USER_ID

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

SITE_ID

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

IP_HASH

One-way hashed IP address.

IS_INTERNAL

Whether the IP address was flagged as customer internal.

IS_BOT

Whether the visit was flagged as a potential bot visit.

VISIT_START

UTC start time of the visit.

VISIT_END

UTC end time of the visit.