Clickthrough rate (CTR)

set start_timestamp = '<START-DATE>'; -- The start date and time. Replace with the desired timestamp.
set end_timestamp = '<END-DATE>';   -- The end date and time. Replace with the desired timestamp.

with clicked_impressions as (
  select
    distinct insight_id
  from
    COVEO_CORE_MODEL_V001.COMMON.CLICKED_IMPRESSIONS
  where
    clicked_impression_timestamp between dateadd(day, -2, $start_timestamp :: date) and $end_timestamp
),
insights as (
  select
    insight_id,
    {COMPONENT}
  from
    COVEO_CORE_MODEL_V001.COMMON.INSIGHTS ins
  where
    start_time between dateadd(day, -2, $start_timestamp :: date) and $end_timestamp
)
select
  ins.{COMPONENT},
  round(
    div0(
      count(distinct ci.insight_id),
      count(distinct ins.insight_id)
    ) * 100,
    2
  ) as click_through_rate
from
  insights ins
  left join clicked_impressions ci on ins.insight_id = ci.insight_id
group by
  ins.{COMPONENT}
order by
  ins.{COMPONENT};

set start_timestamp = '<START-DATE>'; -- The start date and time. Replace with the desired timestamp.
set end_timestamp = '<END-DATE>';   -- The end date and time. Replace with the desired timestamp.

with clicked_impressions as (
  select
    distinct insight_id
  from
    COVEO_CORE_MODEL_V001.COMMON.CLICKED_IMPRESSIONS
  where
    clicked_impression_timestamp between dateadd(day, -2, $start_timestamp :: date) and $end_timestamp
),
insights as (
  select
    insight_id,
    lower(query_expression) as query_expression
  from
    COVEO_CORE_MODEL_V001.COMMON.INSIGHTS
  where
    start_time between dateadd(day, -2, $start_timestamp :: date) and $end_timestamp
    and nullif(query_expression, '') is not null
    and insight_type = 'search'
)
select
  ins.query_expression,
  round(
    div0(
      count(distinct ci.insight_id),
      count(distinct ins.insight_id)
    ) * 100,
    2
  ) as click_through_rate
from
  insights ins
  left join clicked_impressions ci on ins.insight_id = ci.insight_id
group by
  ins.query_expression
order by
  click_through_rate desc
limit
  10;

set start_timestamp = '<START-DATE>'; -- The start date and time. Replace with the desired timestamp.
set end_timestamp = '<END-DATE>';   -- The end date and time. Replace with the desired timestamp.

with clicked_impressions as (
  select
    distinct insight_id
  from
    COVEO_CORE_MODEL_V001.COMMON.CLICKED_IMPRESSIONS
  where
    clicked_impression_timestamp between dateadd(day, -2, $start_timestamp :: date) and $end_timestamp
),
insights as (
  select
    ins.insight_id,
    con.name as ec_listing
  from
    COVEO_CORE_MODEL_V001.COMMON.INSIGHTS ins
    inner join COVEO_CORE_MODEL_V001.COMMERCE.CONFIGS con on con.config_id = ins.config_id
  where
    ins.start_time between dateadd(day, -2, $start_timestamp :: date) and $end_timestamp
    and ins.insight_type = 'listing'
)
select
  ins.ec_listing,
  round(
    div0(
      count(distinct ci.insight_id),
      count(distinct ins.insight_id)
    ) * 100,
    2
  ) as click_through_rate
from
  insights ins
  left join clicked_impressions ci on ins.insight_id = ci.insight_id
group by
  ins.ec_listing
order by
  click_through_rate desc
limit
  10;

set start_timestamp = '<START-DATE>'; -- The start date and time. Replace with the desired timestamp.
set end_timestamp = '<END-DATE>';   -- The end date and time. Replace with the desired timestamp.

with clicked_impressions as (
  select
    distinct insight_id
  from
    COVEO_CORE_MODEL_V001.COMMON.CLICKED_IMPRESSIONS
  where
    clicked_impression_timestamp between dateadd(day, -2, $start_timestamp :: date) and $end_timestamp
),
insights as (
  select
    insight_id,
    origin as recommendation_component
  from
    COVEO_CORE_MODEL_V001.COMMON.INSIGHTS
  where
    start_time between dateadd(day, -2, $start_timestamp :: date) and $end_timestamp
    and insight_type = 'recommendation'
)
select
  ins.recommendation_component,
  round(
    div0(
      count(distinct ci.insight_id),
      count(distinct ins.insight_id)
    ) * 100,
    2
  ) as click_through_rate
from
  insights ins
  left join clicked_impressions ci on ins.insight_id = ci.insight_id
group by
  ins.recommendation_component
order by
  click_through_rate desc
limit
  10;