Retrieve trend data

This is for:

Business Analyst System Administrator
In this article

This article provides an example of how to calculate trend data between different report periods as you would in the Coveo Administration Console. For instance, when adding a metric report card to a dashboard, you can view trend data between the current period and the previous one by selecting the Display trend data option when creating a card.

Display trend data | Coveo

When selected, the trend value appears next to the metric value in the card as a percentage with an up or down arrow, respectively indicating an increasing or a decreasing trend.

Metric card trend | Coveo

The period comparison is determined by the date ranges selected with the Report Period panel (the previous period is selected by default).

Report period | Coveo

By using an SQL query, you can compute the trend data between different date ranges depending on your requirements.

Note

The trend data shows the ratio between two summary values. For more information on how to compute a card summary, see Calculate a simple summary.

Calculate the trend data

To calculate the trend data, you must run a query that retrieves the data from two date ranges in order to compare the current period to the previous period. For example:

ALTER SESSION SET timezone = 'America/New_York';
-- We use variables as it's easier to change the date without having to replace them over all SQL queries.
SET START_DATE = '2023-05-19 00:00:00.000'::TIMESTAMP_TZ;
SET END_DATE = '2023-06-18 23:59:59.999'::TIMESTAMP_TZ;

WITH "cte_searches" AS (
    SELECT "datetime", "search_id", "visit_id", "custom_datas":"c_context_website"::string AS "website"
    FROM "COVEO"."ua"."searches_shared"
    WHERE convert_timezone('UTC', 'America/New_York', "datetime") BETWEEN $START_DATE AND $END_DATE
), "cte_searches_previous_period" AS (
    SELECT "datetime", "search_id", "visit_id", "custom_datas":"c_context_website"::string AS "website"
    FROM "COVEO"."ua"."searches_shared"
-- Computes the date range of the previous period to the current one, by subtracting the end date from the start date.
    WHERE convert_timezone('UTC', 'America/New_York', "datetime") BETWEEN DATEADD(day, DATEDIFF(day, $END_DATE, $START_DATE) - 1, $START_DATE) AND DATEADD(day, DATEDIFF(day, $END_DATE, $START_DATE) - 1, $END_DATE)
), "cte_clicks" AS (
    SELECT "datetime", "search_id", "click_id", "origin_country", "visit_id", "custom_datas":"c_context_website"::string AS "website", "click_rank"
    FROM "COVEO"."ua"."clicks_shared"
    WHERE convert_timezone('UTC', 'America/New_York', "datetime") BETWEEN $START_DATE AND $END_DATE
), "cte_clicks_previous_period" AS (
    SELECT "datetime", "search_id", "click_id", "origin_country", "visit_id", "custom_datas":"c_context_website"::string AS "website", "click_rank"
    FROM "COVEO"."ua"."clicks_shared"
    WHERE convert_timezone('UTC', 'America/New_York', "datetime") BETWEEN DATEADD(day, DATEDIFF(day, $END_DATE, $START_DATE) - 1, $START_DATE) AND DATEADD(day, DATEDIFF(day, $END_DATE, $START_DATE) - 1, $END_DATE)
), "cte_custom_events" AS (
    SELECT "datetime", "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 $START_DATE AND $END_DATE
), "cte_custom_events_previous_period" AS (
    SELECT "datetime", "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 DATEADD(day, DATEDIFF(day, $END_DATE, $START_DATE) - 1, $START_DATE) AND DATEADD(day, DATEDIFF(day, $END_DATE, $START_DATE) - 1, $END_DATE)
), "cte_card_summary" as (
    SELECT
        count(distinct "cte_searches"."search_id" ) as "Search Event Count"
        , count(distinct "cte_clicks"."click_id") as "Click Event Count"
        , count(distinct coalesce("cte_searches"."visit_id", "cte_clicks"."visit_id", "cte_custom_events"."visit_id")) as "Unique Visits"
    FROM "cte_searches" LEFT OUTER JOIN "cte_clicks" ON "cte_searches"."search_id" = "cte_clicks"."search_id"
            FULL OUTER JOIN "cte_custom_events" ON "cte_searches"."search_id" = "cte_custom_events"."search_id"
    WHERE coalesce("cte_searches"."website","cte_clicks"."website", "cte_custom_events"."website") in ('engineering','Engineering')
), "cte_card_summary_previous_period" as (
    SELECT
        count(distinct "cte_searches_previous_period"."search_id" ) as "Search Event Count"
        , count(distinct "cte_clicks_previous_period"."click_id") as "Click Event Count"
        , count(distinct coalesce("cte_searches_previous_period"."visit_id", "cte_clicks_previous_period"."visit_id", "cte_custom_events_previous_period"."visit_id")) as "Unique Visits"
    FROM "cte_searches_previous_period" LEFT OUTER JOIN "cte_clicks_previous_period" ON "cte_searches_previous_period"."search_id" = "cte_clicks_previous_period"."search_id"
            FULL OUTER JOIN "cte_custom_events_previous_period" ON "cte_searches_previous_period"."search_id" = "cte_custom_events_previous_period"."search_id"
    WHERE coalesce("cte_searches_previous_period"."website","cte_clicks_previous_period"."website", "cte_custom_events_previous_period"."website") in ('engineering','Engineering')
)
SELECT  (select "Search Event Count" from "cte_card_summary_previous_period" ) as "Search From"
        , (select "Search Event Count" from "cte_card_summary" ) as "Search To"
        , (("Search To" - "Search From") / "Search From" * 100)::decimal(5,2) as "Search % change over last period"
        , (select "Click Event Count" from "cte_card_summary_previous_period" ) as "Click From"
        , (select "Click Event Count" from "cte_card_summary" ) as "Click To"
        , (("Click To" - "Click From") / "Click From" * 100)::decimal(5,2) as "Click % change over last period"
        , (select "Unique Visits" from "cte_card_summary_previous_period" ) as "Unique Visits From"
        , (select "Unique Visits" from "cte_card_summary" ) as "Unique Visits To"
        , (("Unique Visits To" - "Unique Visits From") / "Unique Visits From" * 100)::decimal(5,2) as "Unique Visits % change over last period";

Which results in:

Trend data SQL results | Coveo