Conversion rate (CR)

This is for:

In this article

CR per service
CR per service on a daily basis
CR per component
Top 10 query expressions
Top 10 product listing pages
Top 10 recommendation components

The sum of the completed sessions with at least one transaction, divided by the total number of completed sessions.

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 that involved a Coveo solution (Search, Product listings, or Recommendations) with at least one transaction, divided by the total number of completed sessions that involved any Coveo solution.
```
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, such as Search, is the sum of the completed sessions involving that solution with at least one transaction, divided by the total number of completed sessions in which that Coveo solution was used.
```
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 that didn’t involve any Coveo solutions with at least one transaction, divided by the total number of completed sessions that didn’t involve any Coveo solutions.

If Coveo solutions are used across the whole commerce experience, there will be few sessions that don’t involve a Coveo solution, so the denominator may be very small. As a result, the Conversion rate without Coveo may appear disproportionately high.
```
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 on a daily basis
CR per component
Top 10 query expressions
Top 10 product listing pages
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.

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

Updates to the computation methods for this metric have been implemented. As a result, executing this SQL query may yield slightly different results from those shown in the Advanced Reports (platform-ca | platform-eu | platform-au) page of the Coveo Administration Console.

A new version of this query, which aligns with the outcomes of the reports of the Advanced Reports page, will soon be available.

select ins.organization_id,
       ins.insight_type,
       (count(distinct tr.transaction_id) / count(distinct ins.visit_id)) * 100 as cr
from COVEO_CORE_MODEL_V001.COMMON.INSIGHTS ins
left join COVEO_CORE_MODEL_V001.COMMERCE.CART_ITEMS ci on ci.insight_id = ins.insight_id
left join COVEO_CORE_MODEL_V001.COMMERCE.TRANSACTIONS tr on tr.cart_id = ci.cart_id
where date(ins.start_time) = :daterange
group by ins.organization_id, ins.insight_type

When using the default query, your dashboard should look like the following:

CR 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

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.

A new version of this query, which aligns with the outcomes of the reports of the Advanced Reports page, will soon be available.

select ins.organization_id,
       date(ins.start_time) as date,
       ins.insight_type,
       (count(distinct tr.transaction_id) / count(distinct ins.visit_id)) * 100 as cr
from COVEO_CORE_MODEL_V001.COMMON.INSIGHTS ins
left join COVEO_CORE_MODEL_V001.COMMERCE.CART_ITEMS ci on ci.insight_id = ins.insight_id
left join COVEO_CORE_MODEL_V001.COMMERCE.TRANSACTIONS tr on tr.cart_id = ci.cart_id
where date(ins.start_time) = :daterange
group by ins.organization_id, date, ins.insight_type

When using the default query, your dashboard should look like the following:

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

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 product listing pages
Top 10 recommendation components

A new version of this query, which aligns with the outcomes of the reports of the Advanced Reports page, will soon be available.

select distinct
       ins.organization_id,
       {COMPONENT},
       count(distinct tr.transaction_id)/count(distinct ins.visit_id) * 100 as cr
from COVEO_CORE_MODEL_V001.COMMON.INSIGHTS ins
left join COVEO_CORE_MODEL_V001.COMMERCE.CART_ITEMS ci on ci.insight_id = ins.insight_id
left join COVEO_CORE_MODEL_V001.COMMERCE.TRANSACTIONS tr on tr.cart_id = ci.cart_id
where date(ins.start_time) = :daterange
and insight_type = '{INSIGHT-TYPE}' -- Add one of the following between the quotes ('listing', 'search' or 'recommendation')
group by ins.organization_id, {ALIAS_OF_COMPONENT}
order by cr desc
limit 10

Where you replace:

{INSIGHT-TYPE} with the Coveo service on which you want to report (that is, search, listing, or recommendation).
{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
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

Service selected in step 1	Value to replace `{COMPONENT}` with
`search`	`query_expression`
`listing`	`ec_listing`
`recommendation`	`origin as recommendation_component`

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

The following query creates a Snowflake dashboard tile that displays the top 10 query expressions that generated the highest CR.

A new version of this query, which aligns with the outcomes of the reports of the Advanced Reports page, will soon be available.

select distinct
       ins.organization_id,
       query_expression,
       count(distinct tr.transaction_id)/count(distinct ins.visit_id) * 100 as cr
from COVEO_CORE_MODEL_V001.COMMON.INSIGHTS ins
left join COVEO_CORE_MODEL_V001.COMMERCE.CART_ITEMS ci on ci.insight_id = ins.insight_id
left join COVEO_CORE_MODEL_V001.COMMERCE.TRANSACTIONS tr on tr.cart_id = ci.cart_id
where date(ins.start_time) = :daterange
and insight_type = 'search'
group by ins.organization_id, query_expression
order by cr desc
limit 10

When using the default query, your dashboard should look like the following:

Top queries - CR - Snowflake dashboard | Coveo

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

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

A new version of this query, which aligns with the outcomes of the reports of the Advanced Reports page, will soon be available.

select distinct
       ins.organization_id,
       ec_listing,
       count(distinct tr.transaction_id)/count(distinct ins.visit_id) * 100 as cr
from COVEO_CORE_MODEL_V001.COMMON.INSIGHTS ins
left join COVEO_CORE_MODEL_V001.COMMERCE.CART_ITEMS ci on ci.insight_id = ins.insight_id
left join COVEO_CORE_MODEL_V001.COMMERCE.TRANSACTIONS tr on tr.cart_id = ci.cart_id
where date(ins.start_time) = :daterange
and insight_type = 'listing'
group by ins.organization_id, ec_listing
order by cr desc
limit 10

When using the default query, your dashboard should look like the following:

Top listing pages - CR - Snowflake dashboard | Coveo

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

Top 10 recommendation components

The following query creates a Snowflake dashboard tile that displays the recommendation components that generated the highest CR.

A new version of this query, which aligns with the outcomes of the reports of the Advanced Reports page, will soon be available.

select distinct
       ins.organization_id,
       origin as recommendation_component,
       count(distinct tr.transaction_id)/count(distinct ins.visit_id) * 100 as cr
from COVEO_CORE_MODEL_V001.COMMON.INSIGHTS ins
left join COVEO_CORE_MODEL_V001.COMMERCE.CART_ITEMS ci on ci.insight_id = ins.insight_id
left join COVEO_CORE_MODEL_V001.COMMERCE.TRANSACTIONS tr on tr.cart_id = ci.cart_id
where date(ins.start_time) = :daterange
and insight_type = 'recommendation'
group by ins.organization_id, recommendation_component
order by cr desc
limit 10

When using the default query, your dashboard should look like the following:

Top recommendation components - CR - Snowflake dashboard | Coveo

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