---
title: Clickthrough rate (CTR)
slug: n45g0473
canonical_url: https://docs.coveo.com/en/n45g0473/
collection: coveo-for-commerce
source_format: adoc
---
# Clickthrough rate (CTR)

Expand the following sections to learn how the average clickthrough rate metric is calculated depending on the Coveo API your implementation targets:

**Commerce API**
<details><summary>Details</summary>

The sum of the requests to the [Commerce API](https://docs.coveo.com/en/103/) that were followed by at least one click event (with a matching unique identifier value), divided by the total number of search requests to the Commerce API.

Requests must be either a [search](https://docs.coveo.com/en/103#tag/Search), a [recommendations](https://docs.coveo.com/en/103#tag/Recommendations), or a [listings](https://docs.coveo.com/en/103#tag/Listings) request.

```text
SUM (requests to the Commerce API followed by a click event) / SUM (requests to the Commerce API)
```

</details>

**Search API**
<details><summary>Details</summary>

The sum of the [search events](https://docs.coveo.com/en/2949#search-performsearch) that were followed by at least one [click event](https://docs.coveo.com/en/2949#click-documentview) (with a matching unique identifier value), divided by the total number of search events.

```text
SUM (search events followed by a click event) / SUM (search events)
```

</details>

This article contains SQL queries to create Snowflake dashboard tiles to report on the following metrics:

* [CTR per component](#ctr-per-component)

* [Top 10 query expressions](#top-10-query-expressions)

* [Top 10 product listing pages](#top-10-product-listing-pages)

* [Top 10 recommendation components](#top-10-recommendation-components)

To learn how you can create custom dashboards in Snowflake and use the queries listed in this section, see [Create Snowflake dashboards](https://docs.coveo.com/en/n45e0223/).

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

* [Top 10 query expressions](#top-10-query-expressions)

* [Top 10 product listing pages](#top-10-product-listing-pages)

* [Top 10 recommendation components](#top-10-recommendation-components)

```sql
set start_timestamp = '<START-DATE>'; -- The start date and time. Replace with the desired timestamp.
set end_timestamp = '<END-DATE>';   -- The end date and time. Replace with the desired timestamp.

with clicked_impressions as (
  select
    distinct insight_id
  from
    COVEO_CORE_MODEL_V001.COMMON.CLICKED_IMPRESSIONS
  where
    clicked_impression_timestamp between dateadd(day, -2, $start_timestamp :: date) and $end_timestamp
),
insights as (
  select
    insight_id,
    {COMPONENT}
  from
    COVEO_CORE_MODEL_V001.COMMON.INSIGHTS ins
  where
    start_time between dateadd(day, -2, $start_timestamp :: date) and $end_timestamp
)
select
  ins.{COMPONENT},
  round(
    div0(
      count(distinct ci.insight_id),
      count(distinct ins.insight_id)
    ) * 100,
    2
  ) as click_through_rate
from
  insights ins
  left join clicked_impressions ci on ins.insight_id = ci.insight_id
group by
  ins.{COMPONENT}
order by
  ins.{COMPONENT};
```

Where you replace:

. `<START-DATE>` and `<END-DATE>` with the start and end dates of the period you want to analyze, in the [ISO 8601](https://en.wikipedia.org/wiki/ISO_8601) format.
For example, to analyze data from January 1st, 2025 to January 31st, 2025, you would replace `<START-DATE>` with `2025-01-01` and `<END-DATE>` with `2025-01-31`.

. `{COMPONENT}` depending on the service you want to report on:

[cols="2", options="header"]
|===
|Coveo services
|Value to replace `{COMPONENT}` with

|`search`
|`query_expression`

|`listing`
|`ec_listing`

|`recommendation`
|`origin as recommendation_component`
|===

## Top 10 query expressions

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

```sql
set start_timestamp = '<START-DATE>'; -- The start date and time. Replace with the desired timestamp.
set end_timestamp = '<END-DATE>';   -- The end date and time. Replace with the desired timestamp.

with clicked_impressions as (
  select
    distinct insight_id
  from
    COVEO_CORE_MODEL_V001.COMMON.CLICKED_IMPRESSIONS
  where
    clicked_impression_timestamp between dateadd(day, -2, $start_timestamp :: date) and $end_timestamp
),
insights as (
  select
    insight_id,
    lower(query_expression) as query_expression
  from
    COVEO_CORE_MODEL_V001.COMMON.INSIGHTS
  where
    start_time between dateadd(day, -2, $start_timestamp :: date) and $end_timestamp
    and nullif(query_expression, '') is not null
    and insight_type = 'search'
)
select
  ins.query_expression,
  round(
    div0(
      count(distinct ci.insight_id),
      count(distinct ins.insight_id)
    ) * 100,
    2
  ) as click_through_rate
from
  insights ins
  left join clicked_impressions ci on ins.insight_id = ci.insight_id
group by
  ins.query_expression
order by
  click_through_rate desc
limit
  10;
```

* Replace `<START-DATE>` and `<END-DATE>` with the start and end dates of the period you want to analyze, in the [ISO 8601](https://en.wikipedia.org/wiki/ISO_8601) format.
For example, to analyze data from January 1st, 2025 to January 31st, 2025, you would replace `<START-DATE>` with `2025-01-01` and `<END-DATE>` with `2025-01-31`.

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

![Top queries - CTR - Snowflake dashboard | Coveo](https://docs.coveo.com/en/assets/images/coveo-for-commerce/images/snowflake-ctr-top-queries.png)

The **Chart type**, **Data**, and **Appearance** sections should look like the following.
See [Using charts](https://docs.snowflake.com/en/user-guide/ui-snowsight-visualizations#using-charts) for more information.

![revenue per service Snowflake dashboard | Coveo](https://docs.coveo.com/en/assets/images/coveo-for-commerce/images/snowflake-ctr-top-queries-format.png)

## Top 10 product listing pages

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

```sql
set start_timestamp = '<START-DATE>'; -- The start date and time. Replace with the desired timestamp.
set end_timestamp = '<END-DATE>';   -- The end date and time. Replace with the desired timestamp.

with clicked_impressions as (
  select
    distinct insight_id
  from
    COVEO_CORE_MODEL_V001.COMMON.CLICKED_IMPRESSIONS
  where
    clicked_impression_timestamp between dateadd(day, -2, $start_timestamp :: date) and $end_timestamp
),
insights as (
  select
    ins.insight_id,
    con.name as ec_listing
  from
    COVEO_CORE_MODEL_V001.COMMON.INSIGHTS ins
    inner join COVEO_CORE_MODEL_V001.COMMERCE.CONFIGS con on con.config_id = ins.config_id
  where
    ins.start_time between dateadd(day, -2, $start_timestamp :: date) and $end_timestamp
    and ins.insight_type = 'listing'
)
select
  ins.ec_listing,
  round(
    div0(
      count(distinct ci.insight_id),
      count(distinct ins.insight_id)
    ) * 100,
    2
  ) as click_through_rate
from
  insights ins
  left join clicked_impressions ci on ins.insight_id = ci.insight_id
group by
  ins.ec_listing
order by
  click_through_rate desc
limit
  10;
```

* Replace `<START-DATE>` and `<END-DATE>` with the start and end dates of the period you want to analyze, in the [ISO 8601](https://en.wikipedia.org/wiki/ISO_8601) format.
For example, to analyze data from January 1st, 2025 to January 31st, 2025, you would replace `<START-DATE>` with `2025-01-01` and `<END-DATE>` with `2025-01-31`.

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

![Top listing pages - CTR - Snowflake dashboard | Coveo](https://docs.coveo.com/en/assets/images/coveo-for-commerce/images/snowflake-ctr-top-listings.png)

The **Chart type**, **Data**, and **Appearance** sections should look like the following.
See [Using charts](https://docs.snowflake.com/en/user-guide/ui-snowsight-visualizations#using-charts) for more information.

![revenue per service Snowflake dashboard | Coveo](https://docs.coveo.com/en/assets/images/coveo-for-commerce/images/snowflake-ctr-top-listings-format.png)

## Top 10 recommendation components

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

```sql
set start_timestamp = '<START-DATE>'; -- The start date and time. Replace with the desired timestamp.
set end_timestamp = '<END-DATE>';   -- The end date and time. Replace with the desired timestamp.

with clicked_impressions as (
  select
    distinct insight_id
  from
    COVEO_CORE_MODEL_V001.COMMON.CLICKED_IMPRESSIONS
  where
    clicked_impression_timestamp between dateadd(day, -2, $start_timestamp :: date) and $end_timestamp
),
insights as (
  select
    insight_id,
    origin as recommendation_component
  from
    COVEO_CORE_MODEL_V001.COMMON.INSIGHTS
  where
    start_time between dateadd(day, -2, $start_timestamp :: date) and $end_timestamp
    and insight_type = 'recommendation'
)
select
  ins.recommendation_component,
  round(
    div0(
      count(distinct ci.insight_id),
      count(distinct ins.insight_id)
    ) * 100,
    2
  ) as click_through_rate
from
  insights ins
  left join clicked_impressions ci on ins.insight_id = ci.insight_id
group by
  ins.recommendation_component
order by
  click_through_rate desc
limit
  10;
```

* Replace `<START-DATE>` and `<END-DATE>` with the start and end dates of the period you want to analyze, in the [ISO 8601](https://en.wikipedia.org/wiki/ISO_8601) format.
For example, to analyze data from January 1st, 2025 to January 31st, 2025, you would replace `<START-DATE>` with `2025-01-01` and `<END-DATE>` with `2025-01-31`.

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

![Top recommendation components - CTR - Snowflake dashboard | Coveo](https://docs.coveo.com/en/assets/images/coveo-for-commerce/images/snowflake-ctr-top-recs.png)

The **Chart type**, **Data**, and **Appearance** sections should look like the following.
See [Using charts](https://docs.snowflake.com/en/user-guide/ui-snowsight-visualizations#using-charts) for more information.

![revenue per service Snowflake dashboard | Coveo](https://docs.coveo.com/en/assets/images/coveo-for-commerce/images/snowflake-ctr-top-recs-format.png)