---
title: Build metric cards with SQL queries
slug: n29g0464
canonical_url: https://docs.coveo.com/en/n29g0464/
collection: coveo-analytics
source_format: adoc
---
# Build metric cards with SQL queries
On the [**Reports**](https://platform.cloud.coveo.com/admin/#/orgid/usage/reports/) ([platform-ca](https://platform-ca.cloud.coveo.com/admin/#/orgid/usage/reports/) | [platform-eu](https://platform-eu.cloud.coveo.com/admin/#/orgid/usage/reports/) | [platform-au](https://platform-au.cloud.coveo.com/admin/#/orgid/usage/reports/)) page of the [Coveo Administration Console](https://docs.coveo.com/en/183/), you can [add report cards](https://docs.coveo.com/en/1972/) to your [dashboards](https://docs.coveo.com/en/256/) and create comprehensive [reports](https://docs.coveo.com/en/266/).
These cards contain valuable insight into your [organization](https://docs.coveo.com/en/185/) data, enabling you to review [event](https://docs.coveo.com/en/260/) details.

![Metric card | Coveo Platform](https://docs.coveo.com/en/assets/images/coveo-analytics/metric-card-example.png)

While there are several card types, it should be noted that they're computed differently.
[Metric cards](https://docs.coveo.com/en/2040/) (such as **Dimensions time series**, **Metric time series**, etc.) rely on joining events, whereas the **Visit metric** and **Visit metric time series** cards use the `all_events` table and rely on unioning events.

In cases where you want go a step further when exploring data collected by [Coveo Analytics](https://docs.coveo.com/en/182/), you can build similar cards yourself with SQL queries through either the [Snowflake reader account](https://docs.coveo.com/en/l9e90297#whats-a-reader-account) or through the [**Data Share** feature](https://docs.coveo.com/en/m1rf0146/).
Building metric cards allows you to compare results obtained through the Administration Console.
You can also compare these results with data from third-party business intelligence (BI) and extract, transform, load (ETL) tools.
BI tools assist in analyzing and visualizing data effectively.
ETL tools help data extraction, transformation, and loading processes.
You'll also be able to create cards that are fully customized to your needs.

This article provides details on how metric cards are created, along with SQL snippet examples that you can use as guidelines to create your own cards.

> **Note**
>
> The guidelines in this article can be applied to all cards except for the [Visit Metric](https://docs.coveo.com/en/1972#visit-metric) and the [Visit Metric Time Series](https://docs.coveo.com/en/1972#visit-metric-time-series) cards.
> Since they're visit cards, the filters are similar to the ones used in the **Visit Browser**, and the guidelines will be covered in the [**Build visit cards with SQL queries**](https://docs.coveo.com/en/m1rf0146/) article.

## Joining tables to create metric cards

The `JOIN` clause is used when building a metric card.
The `searches`, `clicks`, and `custom_events` tables are joined, with a left outer join on the search and click events and a full outer join on the search and custom events.
See [snowflake documentation on joins](https://docs.snowflake.com/en/sql-reference/constructs/join#:~:text=like%20data%20source.-,JOIN,-Use%20the%20JOIN) for more information.

For example:

```sql
FROM
	searches
	LEFT OUTER JOIN clicks ON searches.search_id = clicks.search_id full
	OUTER JOIN custom_events ON searches.search_id = custom_events.last_search_id
```

When generating a query, it's encouraged to use the least amount of tables and joins for performance reasons.
For this reason, the tables joined are based on the metrics and dimensions.
When dimensions and metrics exist in more than one table, the tables are picked in this order:

`searches` -> `clicks` -> `custom_events`

The relationships between the resulting events are as follows (with the `searches` table being the anchor to join the other tables):

* [Searches](https://docs.coveo.com/en/2949#search-performsearch)

** Search has 0 to n clicks

** Search has 0 to n custom events

** Search has 0 to n groups

** Search has 0 to n keywords

* [Clicks](https://docs.coveo.com/en/2949#click-documentview)

** Click has 1 search

* [Custom events](https://docs.coveo.com/en/2949#custom-customevent)

** Custom event has 0 to 1 search

* Groups

** Group has 1 search

* Keywords

** Keyword has 1 search

The following ER diagram illustrates how the **ua** schema is built, the views it contains, and how they interact with each other:  
  

![Coveo | ER diagram metric card](https://docs.coveo.com/en/assets/images/coveo-analytics/build-metric-cards-er-diagram.png)

> **Notes**
>
> * Only primary and foreign keys have been added as the focus is to only show the relationship among the views in the **ua** schema.
> 
> * `views_shared` has no relationship with other views in this context.
> However, it's still included in the ER diagram as a part of the whole **ua** schema.
> 
> * You can ignore the `account_id` primary key if you're using a reader account.
> However, if you're using data share to combine data from several Coveo organizations into a single Snowflake account, then `account_id` should be part of the primary key.

## Common expressions

When an expression is present over more than one required table in the query, we pick the value of the first expression with a non-null value based on the order of tables (`searches`, `clicks`, `custom_events`).
The `COALESCE()` function is therefore helpful for combining expressions from multiple tables, as it automatically selects the first non-null value based on the specific order.

For example:

```sql
SELECT
	COALESCE("searches"."user_name","clicks"."user_name"),
	COUNT(distinct "searches"."search_id"),
	COUNT(distinct "clicks"."click_id")
FROM
	searches
	LEFT OUTER JOIN clicks ON searches.search_id = clicks.search_id
```

When the query involves two or more event types (for example, click and search) with common fields (for example, origin level 1, date time), the value of the expressions is used in the following order of tables:

`searches` -> `clicks` -> `custom_events`

**Example**

```sql
SELECT
	COALESCE(
		"searches"."originLevel1",
		"clicks"."datoriginLevel1etime",
		"custom_events"."originLevel1"
	)
```