---
title: Revenue per visit (RPV)
slug: n45g1046
canonical_url: https://docs.coveo.com/en/n45g1046/
collection: coveo-for-commerce
source_format: adoc
---
# Revenue per visit (RPV)

The revenue per visit (RPV) is the sum of the total [gross revenue](https://docs.coveo.com/en/nc1a0009.md#gross-revenue) (earned in a specific time range), divided by the total number of [visits](https://docs.coveo.com/en/271.md) (whether transactions where completed or not).

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

* [RPV per service](#rpv-per-service)

* [RPV per service, on a daily basis](#rpv-per-service-on-a-daily-basis)

* [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.md).

## RPV per service

The following query creates a Snowflake dashboard tile that displays the RPV 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
    distinct 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_gross_revenue as (
  select
    tr.visit_id,
    sum(tr.base_total) as gross_revenue
  from
    COVEO_CORE_MODEL_V001.COMMERCE.TRANSACTIONS tr
    inner join COVEO_CORE_MODEL_V001.COMMERCE.CART_ITEMS c on c.cart_id = tr.cart_id
  where
    tr.transaction_timestamp between dateadd(day, -1, $start_timestamp :: date) and $end_timestamp
  group by
    tr.visit_id
)
select
  vi.insight_type,
  sum(tnr.gross_revenue) / count(distinct vi.visit_id) as revenue_per_visit
from
  visits_insights vi
  left join transaction_gross_revenue tnr on vi.visit_id = tnr.visit_id
group 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:

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

## RPV per service on a daily basis

The following query creates a Snowflake dashboard tile that displays the RPV 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
    distinct date(v.visit_end) as date,
    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_gross_revenue as (
  select
    tr.visit_id,
    sum(tr.base_total) as gross_revenue
  from
    COVEO_CORE_MODEL_V001.COMMERCE.TRANSACTIONS tr
    inner join COVEO_CORE_MODEL_V001.COMMERCE.CART_ITEMS c on c.cart_id = tr.cart_id
  where
    tr.transaction_timestamp between dateadd(day, -1, $start_timestamp :: date) and $end_timestamp
  group by
    tr.visit_id
)
select
  vi.date,
  vi.insight_type,
  sum(tnr.gross_revenue) / count(distinct vi.visit_id) as revenue_per_visit
from
  visits_insights vi
  left join transaction_gross_revenue tnr on vi.visit_id = tnr.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:

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

## RPV per component

```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 date(v.visit_end) as date,
    v.visit_id,
    ins.{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 = '{INSIGHT-TYPE}'
),
transaction_gross_revenue as (
  select
    tr.visit_id,
    sum(tr.base_total) as gross_revenue
  from
    COVEO_CORE_MODEL_V001.COMMERCE.TRANSACTIONS tr
    inner join COVEO_CORE_MODEL_V001.COMMERCE.CART_ITEMS c on c.cart_id = tr.cart_id
  where
    tr.transaction_timestamp between dateadd(day, -1, $start_timestamp :: date) and $end_timestamp
  group by
    tr.visit_id
)
select
  vi.date,
  vi.{COMPONENT},
  sum(tnr.gross_revenue) / count(distinct vi.visit_id) as revenue_per_visit
from
  visits_insights vi
  left join transaction_gross_revenue tnr on vi.visit_id = tnr.visit_id
group by
  vi.date,
  vi.{COMPONENT}
order by
  vi.date,
  vi.{COMPONENT};
```

Where you replace:

1. `<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`.

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

1. `{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 top 10 query expressions that generated the highest RPV.

```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)
    and ins.insight_type = 'search'
    and nullif(query_expression, '') is not null
),
transaction_gross_revenue as (
  select
    tr.visit_id,
    sum(tr.base_total) as gross_revenue
  from
    COVEO_CORE_MODEL_V001.COMMERCE.TRANSACTIONS tr
    inner join COVEO_CORE_MODEL_V001.COMMERCE.CART_ITEMS c on c.cart_id = tr.cart_id
  where
    tr.transaction_timestamp between dateadd(day, -1, $start_timestamp :: date) and $end_timestamp
  group by
    tr.visit_id
)
select
  vi.query_expression,
  sum(tnr.gross_revenue) / count(distinct vi.visit_id) as revenue_per_visit
from
  visits_insights vi
  left join transaction_gross_revenue tnr on vi.visit_id = tnr.visit_id
group by
  vi.query_expression
having
  revenue_per_visit is not null
order by
  revenue_per_visit 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 - RPV - Snowflake dashboard | Coveo](https://docs.coveo.com/en/assets/images/coveo-for-commerce/images/snowflake-revenue-per-visitor-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-rpv-top-queries-format.png)

## 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 RPV.

```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 ins.config_id = con.config_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 = 'listing'
),
transaction_gross_revenue as (
  select
    tr.visit_id,
    sum(tr.base_total) as gross_revenue
  from
    COVEO_CORE_MODEL_V001.COMMERCE.TRANSACTIONS tr
    inner join COVEO_CORE_MODEL_V001.COMMERCE.CART_ITEMS c on c.cart_id = tr.cart_id
  where
    tr.transaction_timestamp between dateadd(day, -1, $start_timestamp :: date) and $end_timestamp
  group by
    tr.visit_id
)
select
  vi.ec_listing,
  sum(tnr.gross_revenue) / count(distinct vi.visit_id) as revenue_per_visit
from
  visits_insights vi
  left join transaction_gross_revenue tnr on vi.visit_id = tnr.visit_id
group by
  vi.ec_listing
having
  revenue_per_visit is not null
order by
  revenue_per_visit 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 - RPV - Snowflake dashboard | Coveo](https://docs.coveo.com/en/assets/images/coveo-for-commerce/images/snowflake-revenue-per-visitor-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-rpv-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 RPV.

```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_gross_revenue as (
  select
    tr.visit_id,
    sum(tr.base_total) as gross_revenue
  from
    COVEO_CORE_MODEL_V001.COMMERCE.TRANSACTIONS tr
    inner join COVEO_CORE_MODEL_V001.COMMERCE.CART_ITEMS c on c.cart_id = tr.cart_id
  where
    tr.transaction_timestamp between dateadd(day, -1, $start_timestamp :: date) and $end_timestamp
  group by
    tr.visit_id
)
select
  vi.recommendation_component,
  sum(tnr.gross_revenue) / count(distinct vi.visit_id) as revenue_per_visit
from
  visits_insights vi
  left join transaction_gross_revenue tnr on vi.visit_id = tnr.visit_id
group by
  vi.recommendation_component
having
  revenue_per_visit is not null
order by
  revenue_per_visit 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 - RPV - Snowflake dashboard | Coveo](https://docs.coveo.com/en/assets/images/coveo-for-commerce/images/snowflake-revenue-per-visitor-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-rpv-top-recs-format.png)