Average order value (AOV)

The average order value (AOV) is calculated by dividing the sum of the total revenue by the total number of transactions.

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.

Contribution to AOV

The metrics described in this article don’t use the total AOV as previously defined. Instead, they use the contribution to AOV earned by each Coveo service (that is, Searches, Product listings, and Recommendations).

This metric represents the revenue of the items attributed to a Coveo service, excluding taxes, shipping costs, and discounts, divided by the number of transactions attributed to that service.

Important

The calculations for this metric only consider the revenue from items and transactions that are attributed to a Coveo service.

Contribution to AOV example

Four customers make transactions on the Barca Sports store. Three of these transactions include items which are attributed to Coveo services, and one doesn’t.

Order 1:

  • One item for $40 attributed to Coveo Search

  • One item for $60 attributed to Coveo Recommendations

  • One item for $20 not attributed to Coveo

  • Tax/shipping: $10

  • Order total: $130

Order 2:

  • One item for $100 attributed to Coveo Search

  • One item for $200 attributed to Coveo Recommendations

  • Discount: $30

  • Tax: $10

  • Order total: $280

Order 3:

  • One item for $90 attributed to Coveo Search

  • Tax: $10

  • Order total: $100

Order 4:

  • One item for $90 not attributed to Coveo

  • Order total: $90

AOV calculations:

Metric Calculation Final value

Total AOV

($130 + $280 + $100 + $90)/4

$150

Coveo contribution to AOV

($100 + $300 + $90)/3

$163.33

Coveo Search contribution to AOV

($40 + $100 + $90)/3

$76.67

Coveo Recommendations contribution to AOV

($60 + $200)/2

$130

AOV per service

The following query creates a Snowflake dashboard tile that displays the contribution to AOV earned by each Coveo service (that is, Searches, Product listings, and Recommendations).

Important

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.

with revenue_normalization as (
select distinct
       date(tr.transaction_timestamp) as date,
       c.insight_id,
       ins.insight_type,
       tr.transaction_id,
       c.item_id,
       c.price*c.quantity as item_revenue
from COVEO_CORE_MODEL_V001.COMMERCE.TRANSACTIONS tr
join COVEO_CORE_MODEL_V001.COMMERCE.CART_ITEMS c on c.cart_id = tr.cart_id
join COVEO_CORE_MODEL_V001.COMMON.INSIGHTS ins on ins.insight_id = c.insight_id
where date = :daterange
)

select distinct
       rn.insight_type,
       sum(rn.item_revenue) / count(distinct rn.transaction_id) as aov
from revenue_normalization rn
group by rn.insight_type

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

AOV 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

AOV per service on a daily basis

The following query creates a Snowflake dashboard tile that displays the contribution to AOV earned by each Coveo service (that is, Searches, Product listings, and Recommendations) on a daily basis.

Important

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.

with revenue_normalization as (
select distinct
       date(tr.transaction_timestamp) as date,
       c.insight_id,
       ins.insight_type,
       tr.transaction_id,
       c.item_id,
       c.price*c.quantity as item_revenue
from COVEO_CORE_MODEL_V001.COMMERCE.TRANSACTIONS tr
join COVEO_CORE_MODEL_V001.COMMERCE.CART_ITEMS c on c.cart_id = tr.cart_id
join COVEO_CORE_MODEL_V001.COMMON.INSIGHTS ins on ins.insight_id = c.insight_id
where date = :daterange
)

select distinct
       rn.date,
       rn.insight_type,
       sum(rn.item_revenue) / count(distinct rn.transaction_id) as aov
from revenue_normalization rn
group by rn.date, rn.insight_type

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

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

AOV 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 contribution to AOV 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:

Important

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.

with revenue_normalization as (
select distinct
       date(tr.transaction_timestamp) as date,
       c.insight_id,
       ins.insight_type,
       {COMPONENT},
       tr.transaction_id,
       c.item_id,
       c.price*c.quantity as item_revenue
from COVEO_CORE_MODEL_V001.COMMERCE.TRANSACTIONS tr
join COVEO_CORE_MODEL_V001.COMMERCE.CART_ITEMS c on c.cart_id = tr.cart_id
join COVEO_CORE_MODEL_V001.COMMON.INSIGHTS ins on ins.insight_id = c.insight_id
where date = :daterange
and ins.insight_type = '{INSIGHT-TYPE}' -- ('listing', 'search' or 'recommendation')
)

select distinct
       rn.{ALIAS_OF_COMPONENT},
       sum(rn.item_revenue) / count(distinct rn.transaction_id) as aov
from revenue_normalization rn
group by rn.{ALIAS_OF_COMPONENT}
order by aov desc
limit 10

Where you replace:

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

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

Important

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.

with revenue_normalization as (
select distinct
       date(tr.transaction_timestamp) as date,
       c.insight_id,
       ins.insight_type,
       query_expression,
       tr.transaction_id,
       c.item_id,
       c.price*c.quantity as item_revenue
from COVEO_CORE_MODEL_V001.COMMERCE.TRANSACTIONS tr
join COVEO_CORE_MODEL_V001.COMMERCE.CART_ITEMS c on c.cart_id = tr.cart_id
join COVEO_CORE_MODEL_V001.COMMON.INSIGHTS ins on ins.insight_id = c.insight_id
where date = :daterange
and ins.insight_type = 'search'
)

select distinct
       rn.query_expression,
       sum(rn.item_revenue) / count(distinct rn.transaction_id) as aov
from revenue_normalization rn
group by rn.query_expression
order by aov desc
limit 10

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

top queries - AOV - 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 contribution to AOV.

Important

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.

with revenue_normalization as (
select distinct
       date(tr.transaction_timestamp) as date,
       c.insight_id,
       ins.insight_type,
       ec_listing,
       tr.transaction_id,
       c.item_id,
       c.price*c.quantity as item_revenue
from COVEO_CORE_MODEL_V001.COMMERCE.TRANSACTIONS tr
join COVEO_CORE_MODEL_V001.COMMERCE.CART_ITEMS c on c.cart_id = tr.cart_id
join COVEO_CORE_MODEL_V001.COMMON.INSIGHTS ins on ins.insight_id = c.insight_id
where date = :daterange
and ins.insight_type = 'listing'
)

select distinct
       rn.ec_listing,
       sum(rn.item_revenue) / count(distinct rn.transaction_id) as aov
from revenue_normalization rn
group by rn.ec_listing
order by aov desc
limit 10

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

top listings - AOV - 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 contribution to AOV.

Important

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.

with revenue_normalization as (
select distinct
       date(tr.transaction_timestamp) as date,
       c.insight_id,
       ins.insight_type,
       origin as recommendation_component,
       tr.transaction_id,
       c.item_id,
       c.price*c.quantity as item_revenue
from COVEO_CORE_MODEL_V001.COMMERCE.TRANSACTIONS tr
join COVEO_CORE_MODEL_V001.COMMERCE.CART_ITEMS c on c.cart_id = tr.cart_id
join COVEO_CORE_MODEL_V001.COMMON.INSIGHTS ins on ins.insight_id = c.insight_id
where date = :daterange
and ins.insight_type = 'recommendation'
)

select distinct
       rn.recommendation_component,
       sum(rn.item_revenue) / count(distinct rn.transaction_id) as aov
from revenue_normalization rn
group by rn.recommendation_component
order by aov desc
limit 10

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

top recommendation components - AOV - 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