---
title: Average order value (AOV)
slug: n45g0326
canonical_url: https://docs.coveo.com/en/n45g0326/
collection: coveo-for-commerce
source_format: adoc
---
# Average order value (AOV)

The average order value (AOV) is the sum of the transaction [revenue](https://docs.coveo.com/en/m16b0577#total-revenue-chart), divided by the total number of [transactions](https://docs.coveo.com/en/m16b0577#total-transactions-chart).

```txt
SUM (total transaction revenue) / SUM (transactions)
```

This article contains SQL queries to create Snowflake dashboard tiles to report on the following metrics:

* [AOV per service](#aov-per-service)

* [AOV per service on a daily basis](#aov-per-service-on-a-daily-basis)

* [AOV per component](#aov-per-component)

* [Top 10 query expressions](#top-10-query-expressions)

* [Top 10 product listing pages](#top-10-product-listing-pages)

* [Top 10 recommendation components](#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](https://docs.coveo.com/en/n45e0223/).

## Contribution to AOV

The metrics described in this article calculate the AOV for transactions that are [attributed](https://docs.coveo.com/en/m1ae0440/) to a Coveo [product discovery solution](https://docs.coveo.com/en/o9cf0524/) (that is, Search, Product listings, and Recommendations).

When a transaction contains one or more items attributed to a Coveo service, the entire transaction total is used in the calculation for that service.

> **Important**
>
> If a single transaction contains items attributed to multiple Coveo services (for example, one item found via Search and another via a Recommendation slot), the full value of that transaction will contribute to the AOV calculation for each of those services.
> This provides a clear view of the total order value influenced by each Coveo solution.

### Contribution to AOV example

Four customers make transactions on the Barca Sports store.
Three of these transactions include items which are [attributed](https://docs.coveo.com/en/m7l98577/) to Coveo [solutions](https://docs.coveo.com/en/o9cf0524/), and one doesn't.

**Order 1:**

* One item for $40 [attributed](https://docs.coveo.com/en/m7l98577/) to Coveo Search

* One item for $60 [attributed](https://docs.coveo.com/en/m7l98577/) to Coveo Recommendations

* One item for $20 not [attributed](https://docs.coveo.com/en/m7l98577/) to Coveo

* Tax/shipping: $10

* Order total: $130

**Order 2:**

* One item for $100 [attributed](https://docs.coveo.com/en/m7l98577/) to Coveo Search

* One item for $200 [attributed](https://docs.coveo.com/en/m7l98577/) to Coveo Recommendations

* Discount: $30

* Tax: $10

* Order total: $280

**Order 3:**

* One item for $90 [attributed](https://docs.coveo.com/en/m7l98577/) to Coveo Search

* Tax: $10

* Order total: $100

**Order 4:**

* One item for $90 not [attributed](https://docs.coveo.com/en/m7l98577/) to Coveo

* Order total: $90

**AOV calculations:**

[%header,cols="~,~,~"]
|===
|Metric
|Calculation
|Final value

|Total AOV (all orders)
|($130 + $280 + $100 + $90)/4
|$150

|Coveo contribution to AOV (all attributed orders)
|($130 + $280 + $100)/3
|$170

|Coveo Search contribution to AOV (Orders 1, 2, and 3)
|($130 + $280 + $100)/3
|$170

|Coveo Recommendations contribution to AOV (Orders 1 and 2)
|($130 + $280)/2
|$205
|===

## AOV per service

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

```sql
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 transaction_revenue as (
  select
    distinct ins.insight_type,
    tr.transaction_id,
    tr.base_total as transaction_total -- transactional total in base currency (currently USD)
  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
    inner join COVEO_CORE_MODEL_V001.COMMON.INSIGHTS ins on ins.insight_id = c.insight_id
  where
    tr.transaction_timestamp between $start_timestamp and $end_timestamp
    and ins.start_time :: date >= dateadd('day', -32, $start_timestamp :: date) -- attribution look-back from the start date
    )
select
  insight_type,
  sum(transaction_total) / count(transaction_id) as aov
from
  transaction_revenue
group by
  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](https://en.wikipedia.org/wiki/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:

![AOV per service Snowflake dashboard | Coveo](https://docs.coveo.com/en/assets/images/coveo-for-commerce/images/snowflake-aov-per-service.png)

The **Chart type**, **Data**, and **Appearance** sections should look like the following.
See [Using charts](https://docs.snowflake.com/en/user-guide/ui-snowsight-visualizations#using-charts) for more information.

![revenue per service Snowflake dashboard | Coveo](https://docs.coveo.com/en/assets/images/coveo-for-commerce/images/snowflake-aov-per-service-format.png)

## AOV per service on a daily basis

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

```sql
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 transaction_revenue as (
  select
    distinct date(tr.transaction_timestamp) as date, -- Assumes a transaction_timestamp column exists
    ins.insight_type,
    tr.transaction_id,
    tr.base_total as transaction_total
  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
    inner join COVEO_CORE_MODEL_V001.COMMON.INSIGHTS ins on ins.insight_id = c.insight_id
  where
    tr.transaction_timestamp between $start_timestamp and $end_timestamp
    and ins.start_time :: date >= dateadd('day', -32, $start_timestamp :: date) -- attribution look-back
    )
select
  date,
  insight_type,
  sum(transaction_total) / count(transaction_id) as aov
from
  transaction_revenue
group by
  date,
  insight_type
order by
  date,
  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](https://en.wikipedia.org/wiki/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 AOV per service Snowflake dashboard | Coveo](https://docs.coveo.com/en/assets/images/coveo-for-commerce/images/snowflake-aov-per-service-daily.png)

The **Chart type**, **Data**, and **Appearance** sections should look like the following.
See [Using charts](https://docs.snowflake.com/en/user-guide/ui-snowsight-visualizations#using-charts) for more information.

![revenue per service Snowflake dashboard | Coveo](https://docs.coveo.com/en/assets/images/coveo-for-commerce/images/snowflake-aov-per-service-daily-format.png)

## 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](#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:

* [Top 10 query expressions](#top-10-query-expressions)

* [Top 10 product listing pages](#top-10-product-listing-pages)

* [Top 10 recommendation components](#top-10-recommendation-components)

```sql
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 transaction_revenue as (
  select
    distinct {COMPONENT},
    tr.transaction_id,
    tr.base_total as transaction_total
  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
    inner join COVEO_CORE_MODEL_V001.COMMON.INSIGHTS ins on ins.insight_id = c.insight_id
  where
    tr.transaction_timestamp between $start_timestamp and $end_timestamp
    and ins.start_time :: date >= dateadd('day', -32, $start_timestamp :: date) -- attribution look-back
    and ins.insight_type = '{INSIGHT-TYPE}'
)
select
  {COMPONENT},
  sum(transaction_total) / count(transaction_id) as aov
from
  transaction_revenue
group by
  {COMPONENT};
```

Where you replace:

. `<START-DATE>` and `<END-DATE>` with the start and end dates of the period you want to analyze, in the [ISO 8601](https://en.wikipedia.org/wiki/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`.

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

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

[cols="2", options="header"]
|===
|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 recommendation components that generated the highest [contribution to AOV](#contribution-to-aov).

```sql
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 transaction_revenue as (
  select
    distinct lower(ins.query_expression) as query_expression,
    tr.transaction_id,
    tr.base_total as transaction_total
  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
    inner join COVEO_CORE_MODEL_V001.COMMON.INSIGHTS ins on ins.insight_id = c.insight_id
  where
    tr.transaction_timestamp between $start_timestamp and $end_timestamp
    and ins.start_time :: date >= dateadd('day', -32, $start_timestamp :: date) -- attribution look-back
    and ins.insight_type = 'search'
    and nullif(query_expression, '') is not null
)
select
  query_expression,
  sum(transaction_total) / count(transaction_id) as aov
from
  transaction_revenue
group by
  query_expression
order by
  aov 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](https://en.wikipedia.org/wiki/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 - AOV - Snowflake dashboard | Coveo](https://docs.coveo.com/en/assets/images/coveo-for-commerce/images/snowflake-aov-top-queries.png)

The **Chart type**, **Data**, and **Appearance** sections should look like the following.
See [Using charts](https://docs.snowflake.com/en/user-guide/ui-snowsight-visualizations#using-charts) for more information.

![revenue per service Snowflake dashboard | Coveo](https://docs.coveo.com/en/assets/images/coveo-for-commerce/images/snowflake-aov-top-queries-format.png)

## Top 10 product listing pages

The following query creates a Snowflake dashboard tile that displays the recommendation components that generated the highest [contribution to AOV](#contribution-to-aov).

```sql
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 transaction_revenue as (
  select
    distinct con.name as ec_listing,
    tr.transaction_id,
    tr.base_total as transaction_total
  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
    inner join COVEO_CORE_MODEL_V001.COMMON.INSIGHTS ins on ins.insight_id = c.insight_id
    inner join COVEO_CORE_MODEL_V001.COMMERCE.CONFIGS con on ins.config_id = con.config_id
  where
    tr.transaction_timestamp between $start_timestamp and $end_timestamp
    and ins.start_time :: date >= dateadd('day', -32, $start_timestamp :: date) -- attribution look-back
    and ins.insight_type = 'listing'
)
select
  ec_listing,
  sum(transaction_total) / count(transaction_id) as aov
from
  transaction_revenue
group by
  ec_listing
order by
  aov 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](https://en.wikipedia.org/wiki/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 listings - AOV - Snowflake dashboard | Coveo](https://docs.coveo.com/en/assets/images/coveo-for-commerce/images/snowflake-aov-top-listings.png)

The **Chart type**, **Data**, and **Appearance** sections should look like the following.
See [Using charts](https://docs.snowflake.com/en/user-guide/ui-snowsight-visualizations#using-charts) for more information.

![revenue per service Snowflake dashboard | Coveo](https://docs.coveo.com/en/assets/images/coveo-for-commerce/images/snowflake-aov-top-listings-format.png)

## Top 10 recommendation components

The following query creates a Snowflake dashboard tile that displays the recommendation components that generated the highest [contribution to AOV](#contribution-to-aov).

```sql
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 transaction_revenue as (
  select
    distinct ins.origin as recommendation_component,
    tr.transaction_id,
    tr.base_total as transaction_total
  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
    inner join COVEO_CORE_MODEL_V001.COMMON.INSIGHTS ins on ins.insight_id = c.insight_id
  where
    tr.transaction_timestamp between $start_timestamp and $end_timestamp
    and ins.start_time :: date >= dateadd('day', -32, $start_timestamp :: date) -- attribution look-back
    and ins.insight_type = 'recommendation'
)
select
  recommendation_component,
  sum(transaction_total) / count(transaction_id) as aov
from
  transaction_revenue
group by
  recommendation_component
order by
  aov 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](https://en.wikipedia.org/wiki/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 - AOV - Snowflake dashboard | Coveo](https://docs.coveo.com/en/assets/images/coveo-for-commerce/images/snowflake-aov-top-recs.png)

The **Chart type**, **Data**, and **Appearance** sections should look like the following.
See [Using charts](https://docs.snowflake.com/en/user-guide/ui-snowsight-visualizations#using-charts) for more information.

![revenue per service Snowflake dashboard | Coveo](https://docs.coveo.com/en/assets/images/coveo-for-commerce/images/snowflake-aov-top-recs-format.png)