Revenue per visit (RPV)

This is for:

Business Analyst System Administrator

The revenue per visit (RPV) is the sum of the total gross revenue (earned in a specific time range), divided by the total number of visits (whether transactions where completed or not).

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.

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).

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

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

revenue per service Snowflake dashboard | Coveo

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.

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

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

revenue per service Snowflake dashboard | Coveo

RPV per component

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

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

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

    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.

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

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

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

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

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

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

revenue per service Snowflake dashboard | Coveo