Conversion rate (CR)
Conversion rate (CR)
The conversion rate is calculated by adding up the number of transactions divided by the total number of sessions, multiplied by 100.
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.
CR per service
This query creates a Snowflake dashboard tile that displays the CR earned by each Coveo service (i.e., Search, Listing and Recommendation):
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:

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

CR per service on a daily basis
This query creates a Snowflake dashboard tile that displays the CR earned by each Coveo service (i.e., Search, Listing and Recommendation) on a daily basis:
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 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:
In this case, the dashboard will report on these three different components. |
The code sample below serves as a base to get the CR 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.
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 (i.e.,search
,listing
, orrecommendation
). -
{COMPONENT}
depending on the service you selected in step 1:Service selected in step 1 Value to replace {COMPONENT}
withsearch
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}
withsearch
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 CR.
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:

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

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

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

Top 10 recommendation components
This query creates a Snowflake dashboard tile that displays the recommendation components that generated the highest CR.
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:

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