Calculated metric card example

This is for:

This article provides an example of a complete SQL query relating to a calculated metric card. A calculated metric card can be found in a dashboard on the Reports (platform-ca | platform-eu | platform-au) page of the Coveo Administration Console. The card functions by using either two existing metric card values, or a number and a metric card value, in the calculation.

Therefore, prior to creating a calculated metric card, at least one other card is required. For example, in the following dashboard, a calculated metric card was added by taking the existing metrics cards, Search Event Count and Custom Event Count.

You can reproduce the same result by using the same calculation in a SQL query. To do so, wrap the query related to each card with the WITH clause, and use a SELECT statement to apply the metric card operator defined in the calculated metric card, which is based on both cards:

ALTER SESSION SET timezone = 'America/New_York';

WITH cte_searches as
( -- Left card
   SELECT
      count(distinct "search_id") as search_event_count
   FROM
      "COVEO"."ua"."searches_shared"
   WHERE
      convert_timezone('UTC', 'America/New_York', "datetime") between '2023-05-07' and '2023-06-06'
)
,
cte_clicks as
( -- Right card
   SELECT
      count(distinct "click_id") as click_event_count
   FROM
      "COVEO"."ua"."clicks_shared"
   WHERE
      convert_timezone('UTC', 'America/New_York', "datetime") BETWEEN '2022-11-01 00:00:00.000'::TIMESTAMP_TZ AND '2022-12-01 23:59:59.999'::TIMESTAMP_TZ
)
SELECT -- Apply the operator between both cards
   (SELECT click_event_count FROM cte_clicks) / (SELECT search_event_count FROM cte_searches)
        as click_to_search_ratio;