This package should include metrics from the statistical model outlined in github link below

[kristin-bagnall]

https://github.com/fivetran/engineering/issues/78658

[kristin-bagnall]

Example Query:

with conversation_close_events as (

  select
    conversation_id,
    first_value(author_id) over (partition by conversation_id order by created_at desc) as last_close_by_author_id,
    first_value(created_at) over (partition by conversation_id order by created_at desc) as last_close_at,
    first_value(created_at) over (partition by conversation_id order by created_at asc) as first_close_at  
  from intercom.conversation_part_history
  where part_type = 'close'

), metric_timestamps as (

  select 
    conversation_history.id as conversation_id,
    conversation_history.created_at as conversation_created_at,
    min(case when conversation_part_history.part_type = 'comment' and conversation_part_history.author_type = 'admin' then conversation_part_history.created_at else null end) as first_response_at, 
    sum(case when conversation_part_history.part_type = 'open' then 1 else 0 end) as count_reopens,
  from intercom.conversation_history
  left join intercom.conversation_part_history
  on conversation_history.id = conversation_part_history.conversation_id
  group by 1,2

)

select
  metric_timestamps.conversation_id,
  metric_timestamps.conversation_created_at,
  count_reopens, 
  first_response_at,
  timestamp_diff(first_response_at, conversation_created_at, minute) as time_to_first_response,
  first_close_at,
  timestamp_diff(first_close_at, conversation_created_at, minute) as time_to_first_close,
  last_close_at,
  timestamp_diff(last_close_at, conversation_created_at, minute) as time_to_last_close,
  last_close_by_author_id
from metric_timestamps
left join conversation_close_events 
on conversation_close_events.conversation_id = metric_timestamps.conversation_id