Metric card example

This is for:

Business Analyst System Administrator

This article provides an example of a complete SQL query relating to a metric card. Metric cards can be found in a dashboard on the Reports (platform-ca | platform-eu | platform-au) page of the Coveo Administration Console. The following image is that of a Summary dashboard:

summary-dashboard-example

The Summary dashboard contains report cards, such as the following pie chart card:

pie-chart-example

The SQL query corresponding to this card is the following:

WITH "cte_searches" AS (
    SELECT "search_id", "origin_country", "visit_id", "custom_datas":"c_context_website"::string AS "website"
    FROM "COVEO"."ua"."searches_shared"
    WHERE CONVERT_TIMEZONE('UTC', 'America/New_York', "datetime") BETWEEN '2022-12-13 00:00:00.000'::TIMESTAMP_TZ AND '2023-01-12 23:59:59.999'::TIMESTAMP_TZ
), "cte_clicks" AS (
    SELECT "search_id", "origin_country", "visit_id", "custom_datas":"c_context_website"::string AS "website"
    FROM "COVEO"."ua"."clicks_shared"
    WHERE convert_timezone('UTC', 'America/New_York', "datetime") BETWEEN '2022-12-13 00:00:00.000'::TIMESTAMP_TZ AND '2023-01-12 23:59:59.999'::TIMESTAMP_TZ
), "cte_customs" AS (
    SELECT "last_search_id" AS "search_id", "origin_country", "visit_id", "custom_datas":"c_context_website"::string AS "website"
    FROM "COVEO"."ua"."custom_events_shared"
    WHERE convert_timezone('UTC', 'America/New_York', "datetime") BETWEEN '2022-12-13 00:00:00.000'::TIMESTAMP_TZ AND '2023-01-12 23:59:59.999'::TIMESTAMP_TZ
)
SELECT
    COALESCE("cte_searches"."origin_country", "cte_clicks"."origin_country", "cte_customs"."origin_country") AS "Country"
    , COUNT(DISTINCT COALESCE("cte_searches"."visit_id", "cte_clicks"."visit_id", "cte_customs"."visit_id")) AS "Country - Visits"
FROM "cte_searches" LEFT OUTER JOIN "cte_clicks" ON "cte_searches"."search_id" = "cte_clicks"."search_id"
        FULL OUTER JOIN "cte_customs" ON "cte_searches"."search_id" = "cte_customs"."search_id"
WHERE coalesce("cte_searches"."website","cte_clicks"."website","cte_customs"."website") IN ('engineering','Engineering')
GROUP BY "Country"
ORDER BY "Country - Visits" DESC;

Results obtained after running the query your Snowflake worksheet are the following:

summary-dashboard-example