Visit card example

This is for:

Business Analyst System Administrator

This article provides an example of a complete SQL query relating to a visit report card (that is, Visit Metric and Visit Metric Time series cards). Visit cards are added to dashboards in the Reports (platform-ca | platform-eu | platform-au) page of the Coveo Administration Console.

visit-card-dashboard-only | Coveo

Within this dashboard is the Visit Metric card:

visit-card-dashboard-only | Coveo

The SQL query corresponding to this card is the following:

ALTER SESSION SET timezone = 'America/New_York';

SELECT count(distinct "all_events_shared"."visit_id")
FROM "all_events_shared"
WHERE
-- Date range to filter the visit and events
    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
AND
-- Date range to discard the previous 24 hours
    "visit_id" NOT in (SELECT "visit_id" FROM "all_events_shared" WHERE convert_timezone('UTC', 'America/New_York', "datetime") BETWEEN '2022-10-31 00:00:00.000'::TIMESTAMP_TZ AND '2022-10-31 23:59:59.999'::TIMESTAMP_TZ)
AND
-- Date range to include visits in the specified time range
    "visit_id" in (SELECT "visit_id" FROM "all_events_shared" WHERE convert_timezone('UTC', 'America/New_York', "datetime") BETWEEN '2022-11-01 00:00:00.000'::TIMESTAMP_TZ AND '2022-11-30 23:59:59.999'::TIMESTAMP_TZ)
AND
-- Global filter
    "visit_id" in (SELECT "visit_id" FROM "all_events_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 AND "unique_user_id" NOT in ('john@coveo.com'))
AND
-- Inclusion filter
    "visit_id" in (SELECT "visit_id" FROM "all_events_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 AND "origin_level_1" in ('case_deflection' ))
AND
-- Exclusion filter
    "visit_id" NOT in (SELECT "visit_id" FROM "all_events_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 AND "event_type" = 'Click' AND "origin_level_1" in ('case_deflection', 'knowledge_base') )
AND
-- Exclusion filter
    "visit_id" NOT in (SELECT "visit_id" FROM "all_events_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 AND "event_value" = 'submitButton')
AND
-- Hide events
NOT("event_type" = 'interface');