Build metric cards with SQL queries

This is for:

Business Analyst System Administrator

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 (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 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 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 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.

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 for more information.

For example:

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

    • 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

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

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
SELECT
	COALESCE(
		"searches"."originLevel1",
		"clicks"."datoriginLevel1etime",
		"custom_events"."originLevel1"
	)