Average order value (AOV)

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 transaction_revenue as (
  select
    distinct ins.insight_type,
    tr.transaction_id,
    tr.base_total as transaction_total -- transactional total in base currency (currently USD)
  from
    COVEO_CORE_MODEL_V001.COMMERCE.TRANSACTIONS tr
    inner join COVEO_CORE_MODEL_V001.COMMERCE.CART_ITEMS c on c.cart_id = tr.cart_id
    inner join COVEO_CORE_MODEL_V001.COMMON.INSIGHTS ins on ins.insight_id = c.insight_id
  where
    tr.transaction_timestamp between $start_timestamp and $end_timestamp
    and ins.start_time :: date >= dateadd('day', -32, $start_timestamp :: date) -- attribution look-back from the start date
    )
select
  insight_type,
  sum(transaction_total) / count(transaction_id) as aov
from
  transaction_revenue
group by
  insight_type;

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 transaction_revenue as (
  select
    distinct date(tr.transaction_timestamp) as date, -- Assumes a transaction_timestamp column exists
    ins.insight_type,
    tr.transaction_id,
    tr.base_total as transaction_total
  from
    COVEO_CORE_MODEL_V001.COMMERCE.TRANSACTIONS tr
    inner join COVEO_CORE_MODEL_V001.COMMERCE.CART_ITEMS c on c.cart_id = tr.cart_id
    inner join COVEO_CORE_MODEL_V001.COMMON.INSIGHTS ins on ins.insight_id = c.insight_id
  where
    tr.transaction_timestamp between $start_timestamp and $end_timestamp
    and ins.start_time :: date >= dateadd('day', -32, $start_timestamp :: date) -- attribution look-back
    )
select
  date,
  insight_type,
  sum(transaction_total) / count(transaction_id) as aov
from
  transaction_revenue
group by
  date,
  insight_type
order by
  date,
  insight_type;

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 transaction_revenue as (
  select
    distinct {COMPONENT},
    tr.transaction_id,
    tr.base_total as transaction_total
  from
    COVEO_CORE_MODEL_V001.COMMERCE.TRANSACTIONS tr
    inner join COVEO_CORE_MODEL_V001.COMMERCE.CART_ITEMS c on c.cart_id = tr.cart_id
    inner join COVEO_CORE_MODEL_V001.COMMON.INSIGHTS ins on ins.insight_id = c.insight_id
  where
    tr.transaction_timestamp between $start_timestamp and $end_timestamp
    and ins.start_time :: date >= dateadd('day', -32, $start_timestamp :: date) -- attribution look-back
    and ins.insight_type = '{INSIGHT-TYPE}'
)
select
  {COMPONENT},
  sum(transaction_total) / count(transaction_id) as aov
from
  transaction_revenue
group by
  {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 transaction_revenue as (
  select
    distinct lower(ins.query_expression) as query_expression,
    tr.transaction_id,
    tr.base_total as transaction_total
  from
    COVEO_CORE_MODEL_V001.COMMERCE.TRANSACTIONS tr
    inner join COVEO_CORE_MODEL_V001.COMMERCE.CART_ITEMS c on c.cart_id = tr.cart_id
    inner join COVEO_CORE_MODEL_V001.COMMON.INSIGHTS ins on ins.insight_id = c.insight_id
  where
    tr.transaction_timestamp between $start_timestamp and $end_timestamp
    and ins.start_time :: date >= dateadd('day', -32, $start_timestamp :: date) -- attribution look-back
    and ins.insight_type = 'search'
    and nullif(query_expression, '') is not null
)
select
  query_expression,
  sum(transaction_total) / count(transaction_id) as aov
from
  transaction_revenue
group by
  query_expression
order by
  aov 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 transaction_revenue as (
  select
    distinct con.name as ec_listing,
    tr.transaction_id,
    tr.base_total as transaction_total
  from
    COVEO_CORE_MODEL_V001.COMMERCE.TRANSACTIONS tr
    inner join COVEO_CORE_MODEL_V001.COMMERCE.CART_ITEMS c on c.cart_id = tr.cart_id
    inner join COVEO_CORE_MODEL_V001.COMMON.INSIGHTS ins on ins.insight_id = c.insight_id
    inner join COVEO_CORE_MODEL_V001.COMMERCE.CONFIGS con on ins.config_id = con.config_id
  where
    tr.transaction_timestamp between $start_timestamp and $end_timestamp
    and ins.start_time :: date >= dateadd('day', -32, $start_timestamp :: date) -- attribution look-back
    and ins.insight_type = 'listing'
)
select
  ec_listing,
  sum(transaction_total) / count(transaction_id) as aov
from
  transaction_revenue
group by
  ec_listing
order by
  aov 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 transaction_revenue as (
  select
    distinct ins.origin as recommendation_component,
    tr.transaction_id,
    tr.base_total as transaction_total
  from
    COVEO_CORE_MODEL_V001.COMMERCE.TRANSACTIONS tr
    inner join COVEO_CORE_MODEL_V001.COMMERCE.CART_ITEMS c on c.cart_id = tr.cart_id
    inner join COVEO_CORE_MODEL_V001.COMMON.INSIGHTS ins on ins.insight_id = c.insight_id
  where
    tr.transaction_timestamp between $start_timestamp and $end_timestamp
    and ins.start_time :: date >= dateadd('day', -32, $start_timestamp :: date) -- attribution look-back
    and ins.insight_type = 'recommendation'
)
select
  recommendation_component,
  sum(transaction_total) / count(transaction_id) as aov
from
  transaction_revenue
group by
  recommendation_component
order by
  aov desc
limit
  10;