---
title: Click rank
slug: n45g2238
canonical_url: https://docs.coveo.com/en/n45g2238/
collection: coveo-for-commerce
source_format: adoc
---
# Click rank

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

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

The sum of the `position` values for all click events that followed a request to the [Commerce API](https://docs.coveo.com/en/103/), divided by the total number of click events that followed a request to the [Commerce API](https://docs.coveo.com/en/103/).

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

```txt
SUM (position values for all click events that followed a request to the Commerce API) / SUM (click events that followed a request to the Commerce API)
```

</details>

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

The sum of the [`documentPosition`](https://docs.coveo.com/en/2064#documentposition-unsigned-integer) values for all [click events](https://docs.coveo.com/en/2949#click-documentview) that followed a [search event](https://docs.coveo.com/en/2949#search-performsearch), divided by the total number of click events that followed a search event.

```txt
SUM (documentPosition values for all click events that followed a search event) / SUM (click events that followed a search event)
```

</details>

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

* [Click rank per component](#click-rank-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/).

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

* [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
    insight_id,
    count(distinct click_event_id) as click_count,
    sum(position) as click_position_sum
  from
    COVEO_CORE_MODEL_V001.COMMON.CLICKED_IMPRESSIONS
  where
    clicked_impression_timestamp between dateadd(day, -2, $start_timestamp :: date) and $end_timestamp
  group by
    insight_id
),
insights as (
  select
    insight_id,
    {COMPONENT}
  from
    COVEO_CORE_MODEL_V001.COMMON.INSIGHTS
  where
    start_time between dateadd(day, -2, $start_timestamp :: date) and $end_timestamp
    and insight_type = '{INSIGHT-TYPE}'
)
select
  ins.{COMPONENT},
  round(
    div0(
      sum(ci.click_position_sum),
      sum(ci.click_count)
    ),
    2
  ) as click_rank
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`.

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

. `{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 lowest click rank.

```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
    insight_id,
    count(distinct click_event_id) as click_count,
    sum(position) as click_position_sum
  from
    COVEO_CORE_MODEL_V001.COMMON.CLICKED_IMPRESSIONS
  where
    clicked_impression_timestamp between dateadd(day, -2, $start_timestamp :: date) and $end_timestamp
  group by
    insight_id
),
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 insight_type = 'search'
    and nullif(query_expression, '') is not null
)
select
  ins.query_expression,
  round(
    div0(
      sum(ci.click_position_sum),
      sum(ci.click_count)
    ),
    2
  ) as click_rank
from
  insights ins
  left join clicked_impressions ci on ins.insight_id = ci.insight_id
group by
  ins.query_expression
having
  click_rank > 0
order by
  click_rank asc
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 - Click rank - Snowflake dashboard | Coveo](https://docs.coveo.com/en/assets/images/coveo-for-commerce/images/snowflake-click-rank-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-click-rank-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 lowest click rank.

```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
    insight_id,
    count(distinct click_event_id) as click_count,
    sum(position) as click_position_sum
  from
    COVEO_CORE_MODEL_V001.COMMON.CLICKED_IMPRESSIONS
  where
    clicked_impression_timestamp between dateadd(day, -2, $start_timestamp :: date) and $end_timestamp
  group by
    insight_id
),
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(
      sum(ci.click_position_sum),
      sum(ci.click_count)
    ),
    2
  ) as click_rank
from
  insights ins
  left join clicked_impressions ci on ins.insight_id = ci.insight_id
group by
  ins.ec_listing
having
  click_rank > 0
order by
  click_rank asc
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 - Click rank - Snowflake dashboard | Coveo](https://docs.coveo.com/en/assets/images/coveo-for-commerce/images/snowflake-click-rank-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-click-rank-top-listings-format.png)

## Top 10 recommendation components

The following query creates a Snowflake dashboard tile that displays the recommendation components that generated the lowest click rank.

```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
    insight_id,
    count(distinct click_event_id) as click_count,
    sum(position) as click_position_sum
  from
    COVEO_CORE_MODEL_V001.COMMON.CLICKED_IMPRESSIONS
  where
    clicked_impression_timestamp between dateadd(day, -2, $start_timestamp :: date) and $end_timestamp
  group by
    insight_id
),
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(
      sum(ci.click_position_sum),
      sum(ci.click_count)
    ),
    2
  ) as click_rank
from
  insights ins
  left join clicked_impressions ci on ins.insight_id = ci.insight_id
group by
  ins.recommendation_component
having
  click_rank > 0
order by
  click_rank asc
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 - Click rank - Snowflake dashboard | Coveo](https://docs.coveo.com/en/assets/images/coveo-for-commerce/images/snowflake-click-rank-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-click-rank-top-recs-format.png)