Build metric cards with SQL queries

On the Reports (platform-ca | platform-eu | platform-au) page of the Coveo Administration Console, you can add report cards to your dashboards and create comprehensive reports. These cards contain valuable insight into your organization data, enabling you to review event details.

Metric card

While there are several card types, it should be noted that they’re computed differently. Metric cards (e.g., 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 Usage Analytics (Coveo UA), you can build similar cards yourself with SQL queries through either the Snowflake reader account or through the Data Share feature. Building metric cards gives you the opportunity to compare the results obtained through the Administration Console or through third-party BI or ETL tools. You will 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.


The guidelines in this article can be applied to all cards except for the Visit Metric and the 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 article (coming soon!).

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. For example:

	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, we try to use the less tables and joins as possible for performance reasons, therefore 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:


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

  • Searches

    • 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

    • Click has 1 search

  • Custom events

    • Custom event has 0 to 1 search

  • Groups

    • Group has 1 search

  • Keywords

    • Keyword has 1 search

Common expressions

When a 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:

	COUNT(distinct "searches"."search_id"),
	COUNT(distinct "clicks"."click_id")
	LEFT OUTER JOIN clicks ON searches.search_id = clicks.search_id

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