---
title: Conversion rate (CR)
slug: n45g1325
canonical_url: https://docs.coveo.com/en/n45g1325/
collection: coveo-for-commerce
source_format: adoc
---
# Conversion rate (CR)

The sum of the completed [sessions](https://docs.coveo.com/en/oa8g3582/) with at least one [transaction](https://docs.coveo.com/en/m16b0577#total-transactions-chart), divided by the total number of completed sessions.

```txt
SUM (completed sessions with at least one transaction) / SUM (completed sessions)
```

> **Notes**
>
> * The **Conversion rate with Coveo** metric is the sum of the completed [sessions](https://docs.coveo.com/en/oa8g3582/) that involved a Coveo [solution](https://docs.coveo.com/en/o9cf0524/) (**Search**, **Product listings**, or **Recommendations**) with at least one transaction, divided by the total number of completed sessions that involved any Coveo [solution](https://docs.coveo.com/en/o9cf0524/).
>  
> ```txt
SUM (completed sessions involving a Coveo solution with at least one transaction) / SUM (completed sessions involving a Coveo solution)
```
> 
> * The conversion rate for a specific Coveo [solution](https://docs.coveo.com/en/o9cf0524/), such as **Search**, is the sum of the completed [sessions](https://docs.coveo.com/en/oa8g3582/) involving that [solution](https://docs.coveo.com/en/o9cf0524/) with at least one transaction, divided by the total number of completed sessions in which that Coveo [solution](https://docs.coveo.com/en/o9cf0524/) was used.
>  
> ```txt
SUM (completed sessions involving the solution with at least one transaction) / SUM (completed sessions involving that solution)
```
> 
> * The **Conversion rate without Coveo** metric is the sum of the completed [sessions](https://docs.coveo.com/en/oa8g3582/) that didn't involve any Coveo [solutions](https://docs.coveo.com/en/o9cf0524/) with at least one transaction, divided by the total number of completed sessions that didn't involve any Coveo [solutions](https://docs.coveo.com/en/o9cf0524/).
>  
> If Coveo [solutions](https://docs.coveo.com/en/o9cf0524/) are used across the whole commerce experience, there will be few [sessions](https://docs.coveo.com/en/oa8g3582/) that don't involve a Coveo [solution](https://docs.coveo.com/en/o9cf0524/), so the denominator may be very small.
> As a result, the **Conversion rate without Coveo** may appear disproportionately high.
>  
> ```txt
SUM (completed sessions that didn't involve any Coveo solutions with at least one transaction) / SUM (completed sessions that didn't involve any Coveo solutions)
```

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

* [CR per service](#cr-per-service)

* [CR per service on a daily basis](#cr-per-service-on-a-daily-basis)

* [CR per component](#cr-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/).

## CR per service

The following query creates a Snowflake dashboard tile that displays the CR earned by each Coveo service (that is, **Searches**, **Product listings**, and **Recommendations**).

```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 visits_insights as (
  select
    v.visit_id,
    ins.insight_type
  from
    COVEO_CORE_MODEL_V001.COMMON.VISITS v
    inner join COVEO_CORE_MODEL_V001.COMMON.INSIGHTS ins on v.visit_id = ins.visit_id
  where
    v.visit_end between $start_timestamp and $end_timestamp
    and ins.start_time :: date >= dateadd(day, -2, $start_timestamp :: date)
),
transaction_visits as (
  select
    distinct visit_id
  from
    COVEO_CORE_MODEL_V001.COMMERCE.TRANSACTIONS
  where
    transaction_timestamp between dateadd(day, -1, $start_timestamp :: date) and $end_timestamp
)
select
  vi.insight_type,
  round(
    div0(
      count(distinct tv.visit_id),
      count(distinct vi.visit_id)
    ) * 100,
    2
  ) as conversion_rate
from
  visits_insights vi
  left join transaction_visits tv on vi.visit_id = tv.visit_id
group by
  vi.insight_type
order by
  vi.insight_type;
```

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

![CR per service Snowflake dashboard | Coveo](https://docs.coveo.com/en/assets/images/coveo-for-commerce/images/snowflake-conversion-rate-per-service.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-conversion-rate-format.png)

## CR per service on a daily basis

The following query creates a Snowflake dashboard tile that displays the CR earned by each Coveo service (that is, **Searches**, **Product listings**, and **Recommendations**) on a daily basis.

```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 visits_insights as (
  select
    v.visit_id,
    date(v.visit_end) as date,
    ins.insight_type
  from
    COVEO_CORE_MODEL_V001.COMMON.VISITS v
    inner join COVEO_CORE_MODEL_V001.COMMON.INSIGHTS ins on v.visit_id = ins.visit_id
  where
    v.visit_end between $start_timestamp and $end_timestamp
    and ins.start_time :: date >= dateadd(day, -2, $start_timestamp :: date)
),
transaction_visits as (
  select
    distinct visit_id
  from
    COVEO_CORE_MODEL_V001.COMMERCE.TRANSACTIONS
  where
    transaction_timestamp between dateadd(day, -1, $start_timestamp :: date) and $end_timestamp
)
select
  vi.date,
  vi.insight_type,
  round(
    div0(
      count(distinct tv.visit_id),
      count(distinct vi.visit_id)
    ) * 100,
    2
  ) as conversion_rate
from
  visits_insights vi
  left join transaction_visits tv on vi.visit_id = tv.visit_id
group by
  vi.date,
  vi.insight_type
order by
  vi.date,
  vi.insight_type;
```

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

![CR per service Snowflake dashboard | Coveo](https://docs.coveo.com/en/assets/images/coveo-for-commerce/images/snowflake-conversion-rate-per-service-daily.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-conversion-rate-daily-format.png)

## CR 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 CR 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 visits_insights as (
  select
    v.visit_id,
    {COMPONENT}
  from
    COVEO_CORE_MODEL_V001.COMMON.VISITS v
    inner join COVEO_CORE_MODEL_V001.COMMON.INSIGHTS ins on v.visit_id = ins.visit_id
  where
    v.visit_end between $start_timestamp and $end_timestamp
    and ins.start_time :: date >= dateadd(day, -2, $start_timestamp :: date)
),
transaction_visits as (
  select
    distinct visit_id
  from
    COVEO_CORE_MODEL_V001.COMMERCE.TRANSACTIONS tr
  where
    transaction_timestamp between dateadd(day, -1, $start_timestamp :: date) and $end_timestamp
)
select
  vi.{COMPONENT},
  round(
    div0(
      count(distinct tv.visit_id),
      count(distinct vi.visit_id)
    ) * 100,
    2
  ) as conversion_rate
from
  visits_insights vi
  left join transaction_visits tv on vi.visit_id = tv.visit_id
group by
  vi.{COMPONENT}
order by
  vi.{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 CR.

```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 visits_insights as (
  select
    distinct v.visit_id,
    lower(ins.query_expression) as query_expression
  from
    COVEO_CORE_MODEL_V001.COMMON.VISITS v
    inner join COVEO_CORE_MODEL_V001.COMMON.INSIGHTS ins on v.visit_id = ins.visit_id
  where
    v.visit_end between $start_timestamp and $end_timestamp
    and ins.start_time :: date >= dateadd(day, -2, $start_timestamp :: date) -- 48 hours
    and ins.insight_type = 'search'
    and nullif(query_expression, '') is not null
),
transaction_visits as (
  select
    distinct visit_id
  from
    COVEO_CORE_MODEL_V001.COMMERCE.TRANSACTIONS
  where
    transaction_timestamp between dateadd(day, -1, $start_timestamp :: date) and $end_timestamp
)
select
  vi.query_expression,
  round(
    div0(
      count(distinct tv.visit_id),
      count(distinct vi.visit_id)
    ) * 100,
    2
  ) as conversion_rate
from
  visits_insights vi
  left join transaction_visits tv on vi.visit_id = tv.visit_id
group by
  vi.query_expression
order by
  conversion_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 - CR - Snowflake dashboard | Coveo](https://docs.coveo.com/en/assets/images/coveo-for-commerce/images/snowflake-conversion-rate-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-conversion-rate-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 CR.

```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 visits_insights as (
  select
    distinct v.visit_id,
    con.name as ec_listing
  from
    COVEO_CORE_MODEL_V001.COMMON.VISITS v
    inner join COVEO_CORE_MODEL_V001.COMMON.INSIGHTS ins on v.visit_id = ins.visit_id
    inner join COVEO_CORE_MODEL_V001.COMMERCE.CONFIGS con on con.config_id = ins.config_id
  where
    v.visit_end between $start_timestamp and $end_timestamp
    and ins.start_time :: date >= dateadd(day, -2, $start_timestamp :: date)
    and ins.insight_type = 'listing'
),
transaction_visits as (
  select
    distinct visit_id
  from
    COVEO_CORE_MODEL_V001.COMMERCE.TRANSACTIONS
  where
    transaction_timestamp between dateadd(day, -1, $start_timestamp :: date) and $end_timestamp
)
select
  vi.ec_listing,
  round(
    div0(
      count(distinct tv.visit_id),
      count(distinct vi.visit_id)
    ) * 100,
    2
  ) as conversion_rate
from
  visits_insights vi
  left join transaction_visits tv on vi.visit_id = tv.visit_id
group by
  vi.ec_listing
order by
  conversion_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 - CR - Snowflake dashboard | Coveo](https://docs.coveo.com/en/assets/images/coveo-for-commerce/images/snowflake-conversion-rate-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-conversion-rate-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 CR.

```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 visits_insights as (
  select
    distinct v.visit_id,
    ins.origin as recommendation_component
  from
    COVEO_CORE_MODEL_V001.COMMON.VISITS v
    inner join COVEO_CORE_MODEL_V001.COMMON.INSIGHTS ins on v.visit_id = ins.visit_id
  where
    v.visit_end between $start_timestamp and $end_timestamp
    and ins.start_time :: date >= dateadd(day, -2, $start_timestamp :: date)
    and ins.insight_type = 'recommendation'
),
transaction_visits as (
  select
    distinct visit_id
  from
    COVEO_CORE_MODEL_V001.COMMERCE.TRANSACTIONS
  where
    transaction_timestamp between dateadd(day, -1, $start_timestamp :: date) and $end_timestamp
)
select
  vi.recommendation_component,
  round(
    div0(
      count(distinct tv.visit_id),
      count(distinct vi.visit_id)
    ) * 100,
    2
  ) as conversion_rate
from
  visits_insights vi
  left join transaction_visits tv on vi.visit_id = tv.visit_id
group by
  vi.recommendation_component
order by
  conversion_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 - CR - Snowflake dashboard | Coveo](https://docs.coveo.com/en/assets/images/coveo-for-commerce/images/snowflake-conversion-rate-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-conversion-rate-top-recs-format.png)