Build visit cards

This is for:

In this article

Union tables to create visit cards
Filter types

On the Reports (platform-ca | platform-eu | platform-au) page of the Coveo Administration Console, you can add report cards to your dashboards and create comprehensive reports. These report card contain valuable insight into your organization data, enabling you to review event details.

There are several cards available in the Coveo Administration Console, and depending on the type, they’re computed differently. Metric cards (for example, Dimensions time series, Metric time series, etc.) rely on joining events (see How cards compute user visits), while visit cards (that is, Visit metric and Visit metric time series cards) use the all_events_shared table and rely on unioning events. For example:

SELECT
   *
FROM
   searches_shared
UNION ALL
SELECT
   *
FROM
   clicks_shared
UNION ALL
SELECT
   *
FROM
   custom_events_shared

To go a step further when exploring data collected by Coveo Usage Analytics (Coveo UA), you can access the all_events_shared table in Snowflake and build similar cards with SQL queries through either the Snowflake reader account or through the Data Share feature. Building visit cards lets you compare the results obtained through the visit cards of the Coveo Administration Console or through third-party BI or ETL tools. You will also be able to create cards that are fully customized to your needs.

This article provides details on how visit cards are created in the Administration Console, along with SQL snippet examples that you can use as guidelines to create your own cards.

Union tables to create visit cards

Since visit cards are the union of search, click, and custom events, they use the all_events_shared table where the common expressions (datetime, origin level, etc.) are consolidated. For example:

"all_events_shared"."datetime"

The union is made without regards to relationships between event types, meaning that it ignores the sequence of events during the visits. Therefore, adding filter conditions is essential to building an accurate visit card.

Note

Visit cards use the following filter patterns to qualify a visit:

They return a visit if at least one event matches the defined inclusion filters.
They discard a visit if at least one event matches the defined exclusion filters.

For example:

"visit_id" [NOT] in (
	SELECT
		visit_id
	FROM
		<EVENT TABLE>
	WHERE
		<DATE RANGE FILTER>
		and <CONDITION FILTER>
)

Filter types

When assembling the SQL query to create a visit card, it’s useful to keep the filter structure in the Administration Console in mind, and then add the filters accordingly:

1 The date range which is comprised of inclusion and exclusion filters. These filters include visits from within the date range and visits for the 24 hours following the range, as well as exclude visits that started 24 hours before the range. The chosen date range applies over all other filter types added in the visit card.

2 The global filter which is added to the Show visits containing field in step 3 using the AND operator. The global filter includes only the event type specified from within the selected date range.

3 The Show visits containing option, which is an inclusion filter for event type conditions.

4 The Exclude visits containing option, which is an exclusion filter for event type conditions.

5 The Hide events option, which is an exclusion filter for specific events.

Note

These filters are added as multiple conditions to the SQL query and are therefore combined using the AND operator.

Date range

The date range on a visit card filters data with the following criteria:

It excludes visits that begin in the previous 24 hours.
It includes visits that occurred within a defined date range and events up to 24 hours after the specified date range. This ensures that complete visits are returned, instead of truncated or partial ones. For more information, see visit exclusion/inclusion based on date time.

Therefore, when defining the date range on a visit card, the following filters are required:

A filter to discard visits that started in the 24 hours before the beginning of the date range. For example:

"visit_id" NOT in (
	SELECT
		visit_id
	FROM
		"all_events_shared"
	WHERE
		convert_timezone('UTC', 'America/New_York', "all_events_shared"."datetime") BETWEEN '2022-10-31 00:00:00.000'::timestamp_tz AND '2022-10-31 23:59:59.999'::TIMESTAMP_TZ
)

A filter to include visits from within the date range. For example:

"visit_id" in (
	SELECT
		visit_id
	FROM
		"all_events_shared"
	WHERE
		convert_timezone('UTC', 'America/New_York', "all_events_shared"."datetime") BETWEEN '2022-11-01 00:00:00.000'::TIMESTAMP_TZ AND '2022-11-30 23:59:59.999'::TIMESTAMP_TZ
)

A filter to include visits and related events from within the date range, plus during the 24 hours that follow. For example:

convert_timezone('UTC', 'America/New_York', "all_events_shared"."datetime") BETWEEN '2022-11-01 00:00:00.000'::TIMESTAMP_TZ AND '2022-12-01 23:59:59.999'::TIMESTAMP_TZ

Example

You run a query that had a datetime filter 03/13/2023 to 03/17/2023. Within that range, you focus on five visits that started at different times.

where:

Visit 1 started before March 13th, and is therefore discarded.
Visit 2 and visit 4 started after March 13th and ended before March 17th, and is therefore included.
Visit 3 started after March 13th, and is therefore discarded.
Visit 5 started after March 13th but ended beyond March 18th, and is therefore partially included.

Global filter

The global filter includes the visits from within the time range as well as from the 24 hours that follow. For example:

"visit_id" in
(
   SELECT
      "visit_id"
   FROM
      "all_events_shared"
   WHERE
      convert_timezone('UTC', 'America/New_York', "datetime") BETWEEN '2022-11-01 00:00:00.000'::TIMESTAMP_TZ AND '2022-12-01 23:59:59.999'::TIMESTAMP_TZ
      AND "unique_user_id" = 'john@coveo.com'
)

Visit inclusion filters

In the Coveo Administration Console, when adding a visit card, you can include visits containing certain event types as needed in the Show visits containing section.

When building a card with an SQL query, you can add one of the following conditions:

search event

"all_events_shared"."event_type”" = 'Search'

click event

"all_events_shared"."event_type" = 'Click'

custom event

"all_events_shared"."event_type" NOT in ('Search', 'Click')

Example

You add the search event condition to the SQL query:

"visit_id" in (
  SELECT
    "visit_id"
  FROM
    "all_events_shared"
  WHERE
    convert_timezone(
      'UTC', 'America/New_York', "datetime"
    ) BETWEEN '2022-11-01 00:00:00.000' :: TIMESTAMP_TZ
    AND '2022-12-01 23:59:59.999' :: TIMESTAMP_TZ
    AND "all_events_shared"."event_type”" = 'Search'
    AND "unique_user_id" = 'john@coveo.com'
)

Visit exclusion filters

In the Coveo Administration Console, when adding a visit card, you can exclude visits that contain certain event types as needed in the Exclude visits containing section.

When building a card with an SQL query, you can add one of the following conditions:

search event

"all_events_shared"."event_type”" NOT 'Search'

click event

"all_events_shared"."event_type" NOT 'Click'

custom event

"all_events_shared"."event_type" in ('Search', 'Click')

Example

You add the search event condition to the SQL query:

"visit_id" NOT in (
  SELECT
    "visit_id"
  FROM
    "all_events_shared"
  WHERE
    convert_timezone(
      'UTC', 'America/New_York', "datetime"
    ) BETWEEN '2022-11-01 00:00:00.000' :: TIMESTAMP_TZ
    AND '2022-12-01 23:59:59.999' :: TIMESTAMP_TZ
    AND "all_events_shared"."event_type”" = 'Search'
    AND "unique_user_id" = 'john@coveo.com'
)

Event-specific exclusion filters

In the Coveo Administration Console, when adding a visit card, you can exclude specific events as needed in the Hide events section.

Note

When adding more than one exclusion filter to hide events, the OR operator is used.

When building a card with an SQL query, you can add an exclusion filter to your query:

NOT("event_type" = '<EVENT_TYPE>')