Build a view events report

This is for:

A recommendation interface typically logs a view event every time a page that you want it to recommend is opened. Although view events are logged, they aren’t included in Coveo’s out-of-the-box (OOTB) usage analytics reports since they can lead to large volumes of data within the reports and might also alter certain metrics (for example, a higher number of visits or visitors).

Nevertheless, view events provide valuable insight into the effectiveness of your recommendation interface, which can help you better understand and optimize the user journey. For this reason, you may want to assess view events for your page.

This can be done by using an SQL query based on the union of the event types which lets you retrieve data events as required. For example:

ALTER SESSION SET timezone = 'America/New_York';
with cte_events as (
    select date_trunc('day', convert_timezone('UTC', 'America/New_York', "datetime")) as date,
            "search_id" as search_id,
            "click_id" as click_id,
            "custom_event_id" as custom_event_id,
            null as view_event_id,
            "visit_id" as visit_id
    from "COVEO"."ua"."all_events_shared"
    where "account_id" = 'coveodev' and date between '2023-07-27 00:00:00.000'::TIMESTAMP_TZ and '2023-07-30 23:59:59.999'::TIMESTAMP_TZ
    union all
    select date_trunc('day', convert_timezone('UTC', 'America/New_York', "datetime")) as date,
            null as search_id,
            null as click_id,
            null as custom_event_id,
            "view_event_id" as view_event_id,
            "visit_id" as visit_id
    from "COVEO"."ua"."views_shared"
    where "account_id" = 'coveodev' and date between '2023-07-27 00:00:00.000'::TIMESTAMP_TZ and '2023-07-30 23:59:59.999'::TIMESTAMP_TZ
)
select date,
        count(distinct search_id) as search_event_count,
        count(distinct click_id) as click_event_count,
        count(distinct custom_event_id) as custom_event_count,
        count(distinct view_event_id) as view_event_count,
        count(distinct visit_id) as unique_visits
from cte_events
group by date
order by date desc

Results in: