Revenue per visitor (RPV)

This is for:

Developer

The RPV is calculated by summing the total revenue (earned in a specific time range), and dividing it by the total number of visitors (that converted 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

This query creates a Snowflake dashboard tile that displays the RPV (including taxes, shipping costs and discounts) earned by each Coveo service (i.e., Search, Listing and Recommendation):

with transaction_base as (
select distinct
       tr.organization_id,
       date(ins.start_time) as date,
       v.client_xid,
       ins.insight_id,
       ins.insight_type,
       tr.transaction_id,
       c.item_id,
       c.price*c.quantity as item_revenue
from COVEO_CORE_MODEL_V001.COMMON.INSIGHTS ins
join COVEO_CORE_MODEL_V001.COMMON.VISITS v on v.visit_id = ins.visit_id
left join COVEO_CORE_MODEL_V001.COMMERCE.CART_ITEMS c on c.insight_id = ins.insight_id
left join COVEO_CORE_MODEL_V001.COMMERCE.TRANSACTIONS tr on tr.cart_id = c.cart_id
where date = :daterange
)

select distinct
       insight_type,
       sum(item_revenue) / count(distinct client_xid) as revenue_per_visitors
from transaction_base
group by insight_type
order by revenue_per_visitors desc

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

This query creates a Snowflake dashboard tile that displays the RPV (including taxes, shipping costs and discounts) earned by each Coveo service (i.e., Search, Listing and Recommendation) on a daily basis:

with transaction_base as (
select distinct
       tr.organization_id,
       date(ins.start_time) as date,
       v.client_xid,
       ins.insight_id,
       ins.insight_type,
       tr.transaction_id,
       c.item_id,
       c.price*c.quantity as item_revenue
from COVEO_CORE_MODEL_V001.COMMON.INSIGHTS ins
join COVEO_CORE_MODEL_V001.COMMON.VISITS v on v.visit_id = ins.visit_id
left join COVEO_CORE_MODEL_V001.COMMERCE.CART_ITEMS c on c.insight_id = ins.insight_id
left join COVEO_CORE_MODEL_V001.COMMERCE.TRANSACTIONS tr on tr.cart_id = c.cart_id
where date = :daterange
)

select distinct
       date,
       insight_type,
       sum(item_revenue) / count(distinct client_xid) as revenue_per_visitors
from transaction_base
group by date, insight_type

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

RVP per component

The query in this section creates a Snowflake dashboard tile that reports on the different Coveo services' 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 code sample below serves as a base to get the RVP metric for different Coveo components. Copy the query below and paste it in Snowflake. Don’t forget to add the required information, depending on which Coveo service’s component you want to report on.

with transaction_base as (
select distinct
       tr.organization_id,
       date(ins.start_time) as date,
       v.client_xid,
       {COMPONENT},
       c.insight_id,
       ins.insight_type,
       tr.transaction_id,
       c.item_id,
       c.price*c.quantity as item_revenue
from COVEO_CORE_MODEL_V001.COMMON.INSIGHTS ins
join COVEO_CORE_MODEL_V001.COMMON.VISITS v on v.visit_id = ins.visit_id
left join COVEO_CORE_MODEL_V001.COMMERCE.CART_ITEMS c on c.insight_id = ins.insight_id
left join COVEO_CORE_MODEL_V001.COMMERCE.TRANSACTIONS tr on tr.cart_id = c.cart_id
where date = :daterange
and ins.insight_type = '{INSIGHT-TYPE}' -- add on of the follwing between the quotes ('search', 'listing' or 'recommendation')
)

select distinct
       {ALIAS OF COMPONENT},
       sum(item_revenue)/count(distinct client_xid) as revenue_per_visitors
from transaction_base
group by {ALIAS OF COMPONENT}
order by revenue_per_visitors desc
limit 10

Where you replace:

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

  2. {COMPONENT} depending on the service you selected in step 1:

    Service selected in step 1 Value to replace {COMPONENT} with

    search

    query_expression

    listing

    ec_listing

    recommendation

    origin as recommendation_component

  3. Occurrences of {ALIAS_OF_COMPONENT}, depending on the Coveo service you selected in step 1:

    Service selected in step 1 Value to replace {ALIAS_OF_COMPONENT} with

    search

    query_expression

    listing

    ec_listing

    recommendation

    recommendation_component

Top 10 query expressions

This query creates a Snowflake dashboard tile that displays the top 10 query expressions that generated the highest RPV.

with transaction_base as (
select distinct
       tr.organization_id,
       date(ins.start_time) as date,
       v.client_xid,
       query_expression,
       c.insight_id,
       ins.insight_type,
       tr.transaction_id,
       c.item_id,
       c.price*c.quantity as item_revenue
from COVEO_CORE_MODEL_V001.COMMON.INSIGHTS ins
join COVEO_CORE_MODEL_V001.COMMON.VISITS v on v.visit_id = ins.visit_id
left join COVEO_CORE_MODEL_V001.COMMERCE.CART_ITEMS c on c.insight_id = ins.insight_id
left join COVEO_CORE_MODEL_V001.COMMERCE.TRANSACTIONS tr on tr.cart_id = c.cart_id
where date = :daterange
and ins.insight_type = 'search'
)

select distinct
       query_expression,
       sum(item_revenue)/count(distinct client_xid) as revenue_per_visitors
from transaction_base
group by query_expression
having revenue_per_visitors is not null
order by revenue_per_visitors desc
limit 10

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

This query creates a Snowflake dashboard tile that displays the path of the top 10 product listing pages that generated the highest RPV.

with transaction_base as (
select distinct
       tr.organization_id,
       date(ins.start_time) as date,
       v.client_xid,
       ec_listing,
       c.insight_id,
       ins.insight_type,
       tr.transaction_id,
       c.item_id,
       c.price*c.quantity as item_revenue
from COVEO_CORE_MODEL_V001.COMMON.INSIGHTS ins
join COVEO_CORE_MODEL_V001.COMMON.VISITS v on v.visit_id = ins.visit_id
left join COVEO_CORE_MODEL_V001.COMMERCE.CART_ITEMS c on c.insight_id = ins.insight_id
left join COVEO_CORE_MODEL_V001.COMMERCE.TRANSACTIONS tr on tr.cart_id = c.cart_id
where date = :daterange
and ins.insight_type = 'listing'
)

select distinct
       ec_listing,
       sum(item_revenue)/count(distinct client_xid) as revenue_per_visitors
from transaction_base
group by ec_listing
having revenue_per_visitors is not null
order by revenue_per_visitors desc
limit 10

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

This query creates a Snowflake dashboard tile that displays the recommendation components that generated the highest RPV.

with transaction_base as (
select distinct
       tr.organization_id,
       date(ins.start_time) as date,
       v.client_xid,
       origin as recommendation_component,
       c.insight_id,
       ins.insight_type,
       tr.transaction_id,
       c.item_id,
       c.price*c.quantity as item_revenue
from COVEO_CORE_MODEL_V001.COMMON.INSIGHTS ins
join COVEO_CORE_MODEL_V001.COMMON.VISITS v on v.visit_id = ins.visit_id
left join COVEO_CORE_MODEL_V001.COMMERCE.CART_ITEMS c on c.insight_id = ins.insight_id
left join COVEO_CORE_MODEL_V001.COMMERCE.TRANSACTIONS tr on tr.cart_id = c.cart_id
where date = :daterange
and ins.insight_type = 'recommendation'
)

select distinct
       recommendation_component,
       sum(item_revenue)/count(distinct client_xid) as revenue_per_visitors
from transaction_base
group by recommendation_component
having revenue_per_visitors is not null
order by revenue_per_visitors desc
limit 10

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