---
title: Create custom Snowflake commerce dashboards
slug: n45e0223
canonical_url: https://docs.coveo.com/en/n45e0223/
collection: coveo-for-commerce
source_format: adoc
---
# Create custom Snowflake commerce dashboards
The [Coveo Platform](https://docs.coveo.com/en/186/) uses the [Snowflake Database](https://www.snowflake.com/en/) to store all [Coveo Analytics data](https://docs.coveo.com/en/259/).

Depending on whether you already have a Snowflake account or not, Coveo offers the [**Data Share**](https://docs.coveo.com/en/m1rf0146/) and the [Snowflake reader account](https://docs.coveo.com/en/l9e90297/) features to access your data in the Snowflake ecosystem.
The following table indicates which Coveo features you should use to access your data in Snowflake:

[cols="2", options="header"]
|===
|When you already have a Snowflake account
|When you don't have a Snowflake account

|You can use the [**Data Share**](https://docs.coveo.com/en/m1rf0146/) feature of the [Coveo Administration Console](https://docs.coveo.com/en/183/) to integrate your Coveo data to your Snowflake instance.
|You can use the [Snowflake reader account](https://docs.coveo.com/en/l9e90297/) to access your data in Snowflake.
|===

The articles in this section provide guidelines and SQL queries to help you build relevant dashboards allowing you to report on the key commerce metrics directly on Snowflake.

More specifically, these dashboards will help you get insights on the value brought by the different Coveo commerce services (that is, Search, Listings, and Recommendations) and related components.

> **Note**
>
> To learn more about the different commerce-related views that are available in Snowflake, see [COMMERCE schema reference](https://docs.coveo.com/en/p19a0120/).

## Create dashboards in Snowflake

To use the guidelines and queries contained in the articles from this section, first create empty dashboards in Snowflake:

. In Snowflake, [create a new empty dashboard](https://docs.snowflake.com/user-guide/ui-snowsight-dashboards#creating-an-empty-dashboard).

. Once the dashboard is created, you must set the environment so that your queries can return results.
To do so, select a role (that has the privileges required to create dashboards in Snowflake) and a warehouse in the [**Context selector**](https://docs.snowflake.com/user-guide/ui-snowsight-dashboards#snowsight-dashboards-context-selector).

. Once the proper role and warehouse are selected, click **Add tile** (+) to create a tile where you'll be able to add and edit the queries provided in the articles from this section.
See [Using dashboards](https://docs.snowflake.com/user-guide/ui-snowsight-dashboards#using-dashboards) for reference information on how to manage dashboards in Snowflake.


This will create a tile containing three sections:

* On the left side you can see all the databases, schemas, tables, and views you have access to.

* The central worksheet lets you write and edit the queries provided in the articles from this section.

* The _play_ button lets you run the query entered in the central worksheet.


![capture of a Snowflake dashboard tile](https://docs.coveo.com/en/assets/images/coveo-for-commerce/images/snowsight-tile.png)

. When you entered a query that targets a specific database, schema, table, or view, and that you hit _play_ to run the query, you can use the **Chart** feature to format the data returned by the query.

To do so, click **Chart** and then find the chart that's more appropriate to format your data.

> **Note**
>
> The Snowflake **Chart** feature displays the most relevant visualization by default.
> However, you can choose a different one by clicking **Chart type**.
> Any modification will appear on your dashboard when you close the tile.

## Add filters

You can optionally add filters to your dashboards to further scope the query results.


To add a filter

. In the query you entered in the central worksheet, you must add a [`where`](https://docs.snowflake.com/en/sql-reference/constructs/where) clause followed by the field you want to use as a filter.


For example, in the following query, `where date = :daterange` adds a date range filter to the [average order value per service dashboard](https://docs.coveo.com/en/n45g0326#aov-per-service).


```sql
with revenue_normalization as (
select distinct
       tr.organization_id,
       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,
       try_to_number(round(coalesce(tr.base_total, tr.total),3),38,3) as trans_total,
       sum(item_revenue) over (partition by tr.organization_id, tr.transaction_id) as total_revenue,
       round(div0(trans_total, total_revenue) * item_revenue, 3) as normalized_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 <1>
)

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

. When clicking _play_ to run the query, an error message will state that you must choose a value for your filter.
You can do so by selecting the filter in the upper-left corner of your worksheet.

> **Notes**
>
> * The queries provided in the articles from this section already contain a _date range_ filter.
> 
> * To run the queries contained in the articles from this section in a regular worksheet (that is, not in a Snowflake dashboard), you can't use this syntax.
> In that case, you must use regular SQL syntax.

## Metrics

Click the metric you want to explore to access the related queries:

* [Average order value](https://docs.coveo.com/en/n45g0326/)

* [Revenue per visit](https://docs.coveo.com/en/n45g1046/)

* [Conversion rate](https://docs.coveo.com/en/n45g1325/)

* [Click rank](https://docs.coveo.com/en/n45g2238/)

* [Clickthrough rate](https://docs.coveo.com/en/n45g0473/)