Clickthrough rate (CTR)

This is for:

Business Analyst System Administrator

The clickthrough rate is calculated by taking the total number of search events that were followed by at least one click event (with a matching unique identifier value) divided by the total number of search events.

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.

CTR 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:

  • A recommendation component on product detail pages (PDP) named PDP

  • A recommendation component on cart pages named Cart

  • A recommendation component on home pages named Home

In this case, the dashboard will report on these three different components.

The following code sample serves as a base to get the CTR 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:

with aggregated_info as (
select distinct
        ins.organization_id,
        {COMPONENT},
        count(distinct ins.insight_id) as total_searches,
        count(distinct case when click_event_id is not null then ins.insight_id else null end) as total_searches_clicks
from COVEO_CORE_MODEL_V001.COMMON.INSIGHTS ins
left join COVEO_CORE_MODEL_V001.COMMON.CLICKED_IMPRESSIONS imp on ins.visit_id = imp.visit_id and ins.insight_id = imp.insight_id
where date(start_time) = :daterange
and (date(imp.clicked_impression_timestamp) = :daterange or imp.clicked_impression_timestamp is null)
and ins.insight_type = '{INSIGHT-TYPE}' -- ('listing', 'search' or 'recommendation')
group by ins.organization_id, {ALIAS_OF_COMPONENT}
)
select organization_id,
       {ALIAS_OF_COMPONENT},
       (total_searches_clicks / total_searches) * 100 as ctr
from aggregated_info
where total_searches > 50
order by ctr desc
limit 10

Where you replace:

  1. {INSIGHT-TYPE} with the Coveo service on which you want to report (that is, search, listing, or recommendation).

  2. {COMPONENT} depending on the service you selected in step 1:

    Service selected in step 1 Value to replace {COMPONENT} with

    search

    query_expression

    listing

    ec_listing

    recommendation

    origin as recommendation_component

  3. 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} with

    search

    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 highest CTR.

with aggregated_info as (
select distinct
        ins.organization_id,
        query_expression,
        count(distinct ins.insight_id) as total_searches,
        count(distinct case when click_event_id is not null then ins.insight_id else null end) as total_searches_clicks
from COVEO_CORE_MODEL_V001.COMMON.INSIGHTS ins
left join COVEO_CORE_MODEL_V001.COMMON.CLICKED_IMPRESSIONS imp on ins.visit_id = imp.visit_id and ins.insight_id = imp.insight_id
where date(start_time) = :daterange
and (date(imp.clicked_impression_timestamp) = :daterange or imp.clicked_impression_timestamp is null)
and ins.insight_type = 'search'
group by ins.organization_id, query_expression
)
select organization_id,
       query_expression,
       (total_searches_clicks / total_searches) * 100 as ctr
from aggregated_info
where total_searches > 50
order by ctr desc
limit 10

When using the default query, your dashboard should look like the following:

Top queries - CTR - Snowflake dashboard | Coveo

The Chart type, Data, and Appearance sections should look like the following. See Using charts for more information.

revenue per service Snowflake dashboard | Coveo

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 highest CTR.

with aggregated_info as (
select distinct
        ins.organization_id,
        ec_listing,
        count(distinct ins.insight_id) as total_searches,
        count(distinct case when click_event_id is not null then ins.insight_id else null end) as total_searches_clicks
from COVEO_CORE_MODEL_V001.COMMON.INSIGHTS ins
left join COVEO_CORE_MODEL_V001.COMMON.CLICKED_IMPRESSIONS imp on ins.visit_id = imp.visit_id and ins.insight_id = imp.insight_id
where date(start_time) = :daterange
and (date(imp.clicked_impression_timestamp) = :daterange or imp.clicked_impression_timestamp is null)
and ins.insight_type = 'listing'
group by ins.organization_id, ec_listing
)
select organization_id,
       ec_listing,
       (total_searches_clicks / total_searches) * 100 as ctr
from aggregated_info
where total_searches > 50
order by ctr desc
limit 10

When using the default query, your dashboard should look like the following:

Top listing pages - CTR - Snowflake dashboard | Coveo

The Chart type, Data, and Appearance sections should look like the following. See Using charts for more information.

revenue per service Snowflake dashboard | Coveo

Top 10 recommendation components

The following query creates a Snowflake dashboard tile that displays the recommendation components that generated the highest CTR.

with aggregated_info as (
select distinct
        ins.organization_id,
        origin as recommendation_component,
        count(distinct ins.insight_id) as total_searches,
        count(distinct case when click_event_id is not null then ins.insight_id else null end) as total_searches_clicks
from COVEO_CORE_MODEL_V001.COMMON.INSIGHTS ins
left join COVEO_CORE_MODEL_V001.COMMON.CLICKED_IMPRESSIONS imp on ins.visit_id = imp.visit_id and ins.insight_id = imp.insight_id
where date(start_time) = :daterange
and (date(imp.clicked_impression_timestamp) = :daterange or imp.clicked_impression_timestamp is null)
and ins.insight_type = 'recommendation'
group by ins.organization_id, recommendation_component
)
select organization_id,
       recommendation_component,
       (total_searches_clicks / total_searches) * 100 as ctr
from aggregated_info
where total_searches > 50
order by ctr desc
limit 10

When using the default query, your dashboard should look like the following:

Top recommendation components - CTR - Snowflake dashboard | Coveo

The Chart type, Data, and Appearance sections should look like the following. See Using charts for more information.

revenue per service Snowflake dashboard | Coveo