SQL expressions reference for UA metrics
SQL expressions reference for UA metrics
In this article
- Search Count
- Click Count
- Custom Event Count
- Unique Visit Count
- Click Rank
- Click Through
- Visit Click Through
- Manual Search Event Count
- Refined Search Event Count
- Unique User ID
- Unique User Name
- Unique User IP
- Unique Visitor ID
- Attach to Case Count
- Search Event With Clicks
- Average Response Time
- Average Search Query Word Count
- Maximum Response Time
- Last Search Date
- Last Click Date
- Last Custom Event Date
- Visits With Click
- Visit With Manual Search Events
- Dimension Count
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
|
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>)