SQL expressions reference for UA metrics

This is for:

This article is a reference of SQL expressions relating to Coveo Usage Analytics (Coveo UA) metrics 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

Search Count

COUNT(distinct "searches_shared"."search_id")
COUNT(distinct "all_events_shared"."search_id")

Click Count

COUNT(distinct "clicks_shared"."click_id")
COUNT(distinct "all_events_shared"."click_id")

Custom Event Count

COUNT(distinct "custom_events_shared"."custom_event_id")
COUNT(distinct "all_events_shared"."custom_event_id")

Unique Visit Count

COUNT(distinct "searches_shared"."visit_id")
COUNT(distinct "clicks_shared"."visit_id")
COUNT(distinct "custom_events_shared"."visit_id")
COUNT(distinct "all_events_shared"."visit_id")

Click Rank

ZEROIFNULL(avg("clicks_shared"."click_rank"))
ZEROIFNULL(avg("all_events_shared"."click_rank"))

Click Through

DIV0NULL(
    COUNT(distinct "clicks_shared"."search_id"),
    COUNT(distinct "searches_shared"."search_id")
)

Visit Click Through

DIV0NULL(
    COUNT(distinct "clicks_shared"."visit_id"),
    COUNT(distinct
        COALESCE(
            "searches_shared"."visit_id",
            "clicks_shared"."visit_id",
            "custom_events_shared"."visit_id"
        )
    )
)

Manual Search Event Count

COUNT(
	distinct case
		when "searches_shared"."cause2" in (
			'didyoumeanAutomatic',
			'didyoumeanClick',
			'searchboxSubmit',
			'searchboxAsYouType',
			'omniboxSearch',
			'omniboxAnalytics',
			'omniboxFromLink',
			'omniboxField',
			'searchFromLink'
		) then "search_id"
		else null
	end
)

Refined Search Event Count

COUNT(
	distinct case
		when "searches_shared"."cause2" in (
			'contextRemove',
			'interfaceChange',
			'breadcrumbFacet',
			'breadcrumbResetAll',
			'documentField',
			'documentTag',
			'facetClearAll',
			'facetRangeSlider',
			'facetRangeGraph',
			'facetSelect',
			'facetDeselect',
			'facetExclude',
			'facetUnexclude',
			'facetSelectAll',
			'omniboxFacet'
		) then "search_id"
		else null
	end
)

Unique User ID

COUNT(distinct "searches_shared"."user_id")
COUNT(distinct "clicks_shared"."user_id")
COUNT(distinct "custom_events_shared"."user_id")
COUNT(distinct "all_events_shared"."user_id")

Unique User Name

COUNT(distinct "searches_shared"."user_name")
COUNT(distinct "clicks_shared"."user_name")
COUNT(distinct "custom_events_shared"."user_name")
COUNT(distinct "all_events_shared"."user_name")

Unique User IP

COUNT(distinct "searches_shared"."user_ip")
COUNT(distinct "clicks_shared"."user_ip")
COUNT(distinct "custom_events_shared"."user_ip")
COUNT(distinct "all_events_shared"."user_ip")

Unique Visitor ID

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

Attach to Case Count

COUNT(
	DISTINCT IF(
		"custom_events_shared"."event_value" = 'caseAttach',
		"custom_events_shared"."custom_event_id",
		NULL
	)
)

Search Event With Clicks

COUNT(distinct "clicks_shared"."search_id")

Average Response Time

NVL(
	cast(
		floor(avg("searches_shared"."response_time_ms")) as integer
	),
	0
)

Average Search Query Word Count

NVL(
	cast(
		floor(avg(regexp_count("searches_shared"."query_expression", '[\\w-]+'))) as integer
	),
	0
)

Maximum Response Time

MAX("searches_shared"."response_time_ms")

Last Search Date

MAX("searches_shared"."datetime")

Last Click Date

MAX("clicks_shared"."datetime")

Last Custom Event Date

MAX("custom_events_shared"."datetime")

Visits With Click

COUNT(distinct "clicks_shared"."visit_id")

Visit With Manual Search Events

COUNT(
	distinct case
		when "searches_shared"."cause2" in (
			'didyoumeanAutomatic',
			'didyoumeanClick',
			'searchboxSubmit',
			'searchboxAsYouType',
			'omniboxSearch',
			'omniboxAnalytics',
			'omniboxFromLink',
			'omniboxField',
			'searchFromLink'
		) then "visit_id"
		else null
	end
)

Dimension Count

COUNT(distinct <DIMENSION>)