Click rank
Click rank
The click rank is the average of the documentPosition
value for all click events that followed a search event.
This article contains SQL queries to create Snowflake dashboard tiles to report on the following metrics:
To learn how you can create custom dashboards in Snowflake and use the queries listed in this section, see Create Snowflake dashboards.
Click rank per component
The query in this section creates a Snowflake dashboard tile that reports on different Coveo service components.
Note
A Coveo service component refers to a specific instance exploited by a Coveo service. For example, you can have many components that leverage the Recommendation service:
In this case, the dashboard will report on these three different components. |
The following code sample serves as a base to get the click rank metric for different Coveo components. You can add the required information to this generic version, according to the Coveo service component that you want to report on. However, the required information has been added to the sample queries in the following sections, so we recommend that you try one of them:
select distinct
{COMPONENT},
div0(sum(case when click_event_id is not null then imp.position else 0 end), count(distinct click_event_id)) as click_rank
from COVEO_CORE_MODEL_V001.COMMON.INSIGHTS ins
join COVEO_CORE_MODEL_V001.COMMON.IMPRESSIONS imp on ins.visit_id = imp.visit_id and ins.insight_id = imp.insight_id
where date(start_time) = :daterange
and date(impression_timestamp) = :daterange
and insight_type = '{INSIGHT-TYPE}' -- ('listing', 'search', 'recommendation')
group by {ALIAS_OF_COMPONENT}
having click_rank > 0
order by click_rank
limit 10
Where you replace:
-
{INSIGHT-TYPE}
with the Coveo service on which you want to report (that is,search
,listing
, orrecommendation
). -
{COMPONENT}
depending on the service you selected in step 1:Service selected in step 1 Value to replace {COMPONENT}
withsearch
query_expression
listing
ec_listing
recommendation
origin as recommendation_component
-
Occurrences of
{ALIAS_OF_COMPONENT}
, depending on the Coveo service you selected in step 1:Service selected in step 1 Value to replace {ALIAS_OF_COMPONENT}
withsearch
query_expression
listing
ec_listing
recommendation
recommendation_component
Top 10 query expressions
The following query creates a Snowflake dashboard tile that displays the top 10 query expressions that generated the lowest click rank.
select distinct
query_expression,
div0(sum(case when click_event_id is not null then imp.position else 0 end), count(distinct click_event_id)) as click_rank
from COVEO_CORE_MODEL_V001.COMMON.INSIGHTS ins
join COVEO_CORE_MODEL_V001.COMMON.IMPRESSIONS imp on ins.visit_id = imp.visit_id and ins.insight_id = imp.insight_id
where date(start_time) = :daterange
and date(impression_timestamp) = :daterange
and insight_type = 'search'
group by query_expression
having click_rank > 0
order by click_rank
limit 10
When using the default query, your dashboard should look like the following:
The Chart type, Data, and Appearance sections should look like the following. See Using charts for more information.
Top 10 product listing pages
The following query creates a Snowflake dashboard tile that displays the paths of the top 10 product listing pages that generated the lowest click rank.
select distinct
ec_listing,
div0(sum(case when click_event_id is not null then imp.position else 0 end), count(distinct click_event_id)) as click_rank
from COVEO_CORE_MODEL_V001.COMMON.INSIGHTS ins
join COVEO_CORE_MODEL_V001.COMMON.IMPRESSIONS imp on ins.visit_id = imp.visit_id and ins.insight_id = imp.insight_id
where date(start_time) = :daterange
and date(impression_timestamp) = :daterange
and insight_type = 'listing'
group by ec_listing
having click_rank > 0
order by click_rank
limit 10
When using the default query, your dashboard should look like the following:
The Chart type, Data, and Appearance sections should look like the following. See Using charts for more information.
Top 10 recommendation components
The following query creates a Snowflake dashboard tile that displays the recommendation components that generated the lowest click rank.
select distinct
origin as recommendation_component,
div0(sum(case when click_event_id is not null then imp.position else 0 end), count(distinct click_event_id)) as click_rank
from COVEO_CORE_MODEL_V001.COMMON.INSIGHTS ins
join COVEO_CORE_MODEL_V001.COMMON.IMPRESSIONS imp on ins.visit_id = imp.visit_id and ins.insight_id = imp.insight_id
where date(start_time) = :daterange
and date(impression_timestamp) = :daterange
and insight_type = 'recommendation'
group by recommendation_component
having click_rank > 0
order by click_rank
limit 10
When using the default query, your dashboard should look like the following:
The Chart type, Data, and Appearance sections should look like the following. See Using charts for more information.