SQL expressions reference for metrics

This is for:

Business Analyst System Administrator

This article is a reference of SQL expressions relating to Coveo Analytics metrics that can be added to queries which can be run against Coveo Analytics 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")

DocumentView Count

COUNT(distinct "click_id") AS "Click Event Count"
FROM "COVEO"."ua"."clicks_shared"
Note

When using this expression, add a WHERE clause to filter the data and avoid a long query execution time.

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
)

Relevance Index

Notes

  • The Relevance Index is intended as a relative metric used to compare queries and identify potential relevance issues, rather than an absolute measure of search performance. The expression shown here reflects the current implementation and may evolve over time as the metric continues to be refined.

  • The base_metrics section of the SQL expression uses placeholder values to illustrate the formula. To reproduce the metric on real data, replace these values with expressions that compute the required base metrics (search_count, search_with_click_count, and avg_click_rank) from your dataset.

 
WITH base_metrics AS (
    SELECT
        1 AS search_count,
        1 AS search_with_click_count,
        1 AS avg_click_rank,
        DIV0(1,1) AS click_through_rate
),

scoring_components AS (
    SELECT
        *,
        (0.15 / 0.5 * SQRT(search_count)) AS search_volume_score,
        (-1 * EXP(-9.210340372 * click_through_rate) + 1) AS rank_weight,

        case
            when avg_click_rank < 12
            then -0.2377002128 * EXP((avg_click_rank - 1) * 0.15) + 1.237700213
            else EXP((avg_click_rank - 12) * -0.1) - 1
        end AS rank_score,

        case
            when click_through_rate < 0.25
            then 16 * POWER(click_through_rate, 2) - 1
            else -3.745104746 * EXP(-5.2 * click_through_rate) + 1.020660112
        end AS ct_score
    FROM base_metrics
),

calculated_performance AS (
    SELECT
        *,
        (1 / (1 + EXP(-1.65451 * search_volume_score))) AS search_probability,
        (ct_score + rank_weight * rank_score) / (1 + rank_weight) AS performance
    FROM scoring_components
)

SELECT
    search_count,
    search_with_click_count,
    avg_click_rank,
    click_through_rate,
    (search_probability * 2 - 1) AS confidence,
    performance,
    (performance * (search_probability * 2 - 1) * 0.5 + 0.5) AS relevance_index
FROM calculated_performance;

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>)