SQL expressions reference for UA dimensions

This is for:

In this article

This article is a reference of SQL expressions relating to Coveo Usage Analytics (Coveo UA) dimensions that can be added to queries which can be run against UA data to build reports. The query syntax is Snowflake-specific and can be used in the Snowflake reader account or Snowflake data share.

Note

SQL expressions must be entered within a SELECT statement. For example:

SELECT
	"origin_country" AS "Search Country",
	COUNT(distinct "search_id") AS "Search Event Count"
FROM
	"COVEO"."ua"."searches_shared"
WHERE
	CONVERT_TIMEZONE('UTC', 'America/New_York', "datetime") BETWEEN '2022-12-13 00:00:00.000' :: TIMESTAMP_TZ
	AND '2023-01-12 23:59:59.999' :: TIMESTAMP_TZ
GROUP BY
	"Search Country"
ORDER BY
	"Search Event Count" DESC

Client ID

COALESCE(
	"searches_shared"."client_id",
	"searches_shared"."visitor_id"
)
COALESCE(
	"clicks_shared"."client_id",
	"clicks_shared"."visitor_id"
)
COALESCE(
	"custom_events_shared"."client_id",
	"custom_events_shared"."visitor_id"
)
COALESCE(
	"all_events_shared"."client_id",
	"all_events_shared"."visitor_id"
)

Visitor ID

COALESCE(
	"searches_shared"."client_id",
	"searches_shared"."visitor_id"
)
COALESCE(
	"clicks_shared"."client_id",
	"clicks_shared"."visitor_id"
)
COALESCE(
	"custom_events_shared"."client_id",
	"custom_events_shared"."visitor_id"
)
COALESCE(
	"all_events_shared"."client_id",
	"all_events_shared"."visitor_id"
)

Has Results

"searches_shared"."with_results"
"all_events_shared"."with_results"

User Name

"searches_shared"."user_name"
"clicks_shared"."user_name"
"custom_events_shared"."user_name"
"all_events_shared"."user_name"

User ID

"searches_shared"."unique_user_id"
"clicks_shared"."unique_user_id"
"custom_events_shared"."unique_user_id"
"all_events_shared"."unique_user_id"

Is Anonymous

"searches_shared"."is_anonymous"
"clicks_shared"."is_anonymous"
"custom_events_shared"."is_anonymous"
"all_events_shared"."is_anonymous"

Visit

"searches_shared"."visit_id"
"clicks_shared"."visit_id"
"custom_events_shared"."visit_id"
"all_events_shared"."visit_id"

Month

DATE_TRUNC(
	'month',
	CONVERT_TIMEZONE('UTC', <TIMEZONE>, "searches_shared"."datetime")
) DATE_TRUNC(
	'month',
	CONVERT_TIMEZONE('UTC', <TIMEZONE>, "clicks_shared"."datetime")
) DATE_TRUNC(
	'month',
	CONVERT_TIMEZONE(
		'UTC',
		<TIMEZONE>,
		"custom_events_shared"."datetime"
	)
) DATE_TRUNC(
	'month',
	CONVERT_TIMEZONE(
		'UTC',
		<TIMEZONE>,
		"all_events_shared"."datetime"
	)
) DATE_TRUNC(
	'month',
	CONVERT_TIMEZONE(
		'UTC',
		<TIMEZONE>,
		"all_events_shared"."datetime"
	)
)

Week

DATE_TRUNC(
	'week',
	CONVERT_TIMEZONE('UTC', <TIMEZONE>, "searches_shared"."datetime")
)
DATE_TRUNC(
	'week',
	CONVERT_TIMEZONE('UTC', <TIMEZONE>, "clicks_shared"."datetime")
)
DATE_TRUNC(
	'week',
	CONVERT_TIMEZONE(
		'UTC',
		<TIMEZONE>,
		"custom_events_shared"."datetime"
	)
)
DATE_TRUNC(
	'week',
	CONVERT_TIMEZONE(
		'UTC',
		<TIMEZONE>,
		"all_events_shared"."datetime"
	)
)
DATE_TRUNC(
	'week',
	CONVERT_TIMEZONE(
		'UTC',
		<TIMEZONE>,
		"all_events_shared"."datetime"
	)
)

Day

DATE_TRUNC(
	'day',
	CONVERT_TIMEZONE('UTC', <TIMEZONE>, "searches_shared"."datetime")
)
DATE_TRUNC(
	'day',
	CONVERT_TIMEZONE('UTC', <TIMEZONE>, "clicks_shared"."datetime")
)
DATE_TRUNC(
	'day',
	CONVERT_TIMEZONE(
		'UTC',
		<TIMEZONE>,
		"custom_events_shared"."datetime"
	)
)
DATE_TRUNC(
	'day',
	CONVERT_TIMEZONE(
		'UTC',
		<TIMEZONE>,
		"all_events_shared"."datetime"
	)
)
DATE_TRUNC(
	'day',
	CONVERT_TIMEZONE(
		'UTC',
		<TIMEZONE>,
		"all_events_shared"."datetime"
	)
)

Hour

DATE_TRUNC(
	'hour',
	CONVERT_TIMEZONE('UTC', <TIMEZONE>, "searches_shared"."datetime")
)
DATE_TRUNC(
	'hour',
	CONVERT_TIMEZONE('UTC', <TIMEZONE>, "clicks_shared"."datetime")
)
DATE_TRUNC(
	'hour',
	CONVERT_TIMEZONE(
		'UTC',
		<TIMEZONE>,
		"custom_events_shared"."datetime"
	)
)
DATE_TRUNC(
	'hour',
	CONVERT_TIMEZONE(
		'UTC',
		<TIMEZONE>,
		"all_events_shared"."datetime"
	)
)
DATE_TRUNC(
	'hour',
	CONVERT_TIMEZONE(
		'UTC',
		<TIMEZONE>,
		"all_events_shared"."datetime"
	)
)

Minute

DATE_TRUNC(
	'minute',
	CONVERT_TIMEZONE('UTC', <TIMEZONE>, "searches_shared"."datetime")
)
DATE_TRUNC(
	'minute',
	CONVERT_TIMEZONE('UTC', <TIMEZONE>, "clicks_shared"."datetime")
)
DATE_TRUNC(
	'minute',
	CONVERT_TIMEZONE(
		'UTC',
		<TIMEZONE>,
		"custom_events_shared"."datetime"
	)
)
DATE_TRUNC(
	'minute',
	CONVERT_TIMEZONE(
		'UTC',
		<TIMEZONE>,
		"all_events_shared"."datetime"
	)
)
DATE_TRUNC(
	'minute',
	CONVERT_TIMEZONE(
		'UTC',
		<TIMEZONE>,
		"all_events_shared"."datetime"
	)
)

Search ID

"searches_shared"."search_id"

Device

"searches_shared"."origin_device"
"clicks_shared"."origin_device"
"custom_events_shared"."origin_device"
"all_events_shared"."origin_device"

Device Category

"searches_shared"."device_category"
"clicks_shared"."device_category"
"custom_events_shared"."device_category"
"all_events_shared"."device_category"

Language

"searches_shared"."origin_language"
"clicks_shared"."origin_language"
"custom_events_shared"."origin_language"
"all_events_shared"."origin_language"

Country

"searches_shared"."origin_country"
"clicks_shared"."origin_country"
"custom_events_shared"."origin_country"
"all_events_shared"."origin_country"

City

"searches_shared"."origin_city"
"clicks_shared"."origin_city"
"custom_events_shared"."origin_city"
"all_events_shared"."origin_city"

Region

"searches_shared"."origin_region"
"clicks_shared"."origin_region"
"custom_events_shared"."origin_region"
"all_events_shared"."origin_region"

Is Mobile

"searches_shared"."origin_is_mobile"
"clicks_shared"."origin_is_mobile"
"custom_events_shared"."origin_is_mobile"
"all_events_shared"."origin_is_mobile"

Browser

"searches_shared"."web_browser"
"clicks_shared"."web_browser"
"custom_events_shared"."web_browser"
"all_events_shared"."web_browser"

Query Pipeline

"searches_shared"."query_pipeline"
"clicks_shared"."query_pipeline"
"all_events_shared"."query_pipeline"

Event Cause

"searches_shared"."cause2"
"clicks_shared"."cause2"
"all_events_shared"."cause2"

Group

"group_name_shared"."groups"

Keyword

"keywords_shared"."keyword"