Common Queries

These are basic query examples to demonstrate how tables relate to each other. They don't include choices an analyst might make to include or exclude specific data (e.g. an analyst might filter to exclude auto-replies from a reply rate). Good data analysis requires many judgment calls on how to most accurately represent an outcome. We recommend working in collaboration with your analysts to gain the best insights for your business.

Are my Outreach users logging in?

Purpose: Adoption

Metrics: Email addresses and last login for all users that currently have access to Outreach (last login as of the time of data update, approx. midnight pacific time USA)

Copy
Copied
select
    email
    , current_sign_in_at
from users
where
    locked = FALSE
;

How many prospects are being added to sequences?

Purpose: Top of funnel

Metrics: Count of unique prospects that have been added to sequence per user in the last 180 days

Copy
Copied
select
    creator_id
    , count(distinct prospect_id)
from sequence_states
where
    activated_at>(current_date()-180)
    and lower(creator_type)=usergroup by
    creator_id
;

Have those prospects completed the sequence?

Purpose: State of funnel

Metrics: Current state of prospects added to sequence based on month (cohort) they were placed in sequence for those who were sequenced in the last 180 days

Copy
Copied
select
    date_part(month, cast(activated_at as timestamp)) as mo
    , date_part(year, cast(activated_at as timestamp)) as yr
    , sequence_id
    , state
    , count(distinct prospect_id)
from sequence_states
where
    to_date(cast(activated_at as timestamp))>=(to_date(current_timestamp())-180)
    and is_deleted_in_app = FALSE
group by
    mo
    , yr
    , sequence_id
    , state
order by
    yr
    , mo desc
;

Which sequenced emails are most successful?

Purpose: Conversion

Metrics: Total count, open rate, click rate, and reply rate for emails, by sequence step in the last 180 days

Copy
Copied
select
    sequence_id
    , sequence_step_id
    , count(id) as "total mailings"
    , sum(case when opened_at is not null then 1 else 0 end)/count(id) as "open rate"
    , sum(case when clicked_at is not null then 1 else 0 end)/count(id) as "click rate"
    , sum(case when replied_at is not null then 1 else 0 end)/count(id) as "reply rate"
    from mailings
where
    created_at>(current_date()-180)
    and sequence_id is not null
    and is_deleted_in_app = FALSE
group by
    sequence_id
    , sequence_step_id
order by
    sequence_id
    , sequence_step_id desc
;

Which templates are used in the most successful emails?

Purpose: Conversion

Metrics: Total count, open rate, click rate, and reply rate for emails, by template in the last 180 days (across all sequences)

Copy
Copied
select
    t.name
    ,m.template_id
    , count(m.id) as "total mailings"
    , sum(case when m.opened_at is not null then 1 else 0 end)/count(m.id) as "open rate"
    , sum(case when m.clicked_at is not null then 1 else 0 end)/count(m.id) as "click rate"
    , sum(case when m.replied_at is not null then 1 else 0 end)/count(m.id) as "reply rate"
from mailings as m
left join templates as t
    on m.template_id=t.id
where
    m.created_at>(current_date()-180)
    and m.is_deleted_in_app = FALSE
    and t.is_deleted_in_app = FALSE
    and t.name is not null
group by
    t.name
    , m.template_id
;

Which sequenced emails result in the most 'bad' outcomes?

Purpose: Attrition

Metrics: Total count, unsubscribe rate and bounce rate for emails, by sequence step in the last 180 days

Copy
Copied
select
    sequence_id
    , sequence_step_id
    , count(id) as "total mailings"
    , sum(case when bounced_at is not null then 1 else 0 end)/count(id) as "bounce rate"
    , sum(case when unsubscribed_at is not null then 1 else 0 end)/count(id) as "unsubscribe rate"
    , sum(case when replied_at is not null then 1 else 0 end)/count(id) as "reply rate"
from mailings
where
    created_at>(current_date()-180)
    and sequence_id is not null
    and is_deleted_in_app = FALSE
group by
    sequence_id
    , sequence_step_id
order by
    sequence_id
    , sequence_step_id desc
;

Which templates result in the most 'bad' outcomes?

Purpose: Attrition

Metrics: Total count, unsubscribe rate and bounce rate for emails, by template in the last 180 days (across all sequences)

Copy
Copied
select
    t.name
    ,m.template_id
    , count(m.id) as "total mailings"
    , sum(case when m.bounced_at is not null then 1 else 0 end)/count(m.id) as "bounce rate"
    , sum(case when m.unsubscribed_at is not null then 1 else 0 end)/count(m.id) as "unsubscribe rate"
from mailings as m
left join templates as t
    on m.template_id=t.id
where
    m.created_at>(current_date()-180)
    and m.is_deleted_in_app = FALSE
    and t.is_deleted_in_app = FALSE
    and t.name is not null
group by
    t.name
    , m.template_id
;

Are reps reaching the people they are trying to reach?

Purpose: Effectiveness

Metrics: Total outbound calls, answered rate, correct connect rate, and meeting set rate by recipient persona in the last 7 days

Note: This kind of question, like many questions that involve a manual classification or disposition, is based on how your dispositions have been structured and how your users are using them. If the classification does not exist or users are not correctly using it, it can not be measured like this.

Copy
Copied
select
    pers.name
    , count(c.id) as "total calls"
    , sum(case when cd.name ilike '%answered%' then 1 when cd.name ilike '%connect%' then 1 else 0 end)/count(c.id) as "answered rate"
    ,  sum(case when cd.name ilike '%correct connect%' then 1 else 0 end)/count(c.id) as "correct connect rate"
    , sum(case when cd.name ilike '%meeting set%' then 1 else 0 end)/count(c.id) as "meeting set rate"
from calls as c
join call_dispositions as cd
    on c.call_disposition_id=cd.id
    and c.o_id=cd.o_id
join orgs as o
    on c.o_id=o.o_id
join prospects as p
    on c.prospect_id=p.id
    and c.o_id=p.o_id
left join personas as pers
    on p.persona_id=pers.id
    and p.o_id=pers.o_id
where
    c.created_at>(current_date()-7)
    and c.outbound = TRUE
group by
    pers.name
;

Are reps completing their tasks?

Purpose: Adoption/performance

Metrics: % of current open tasks that are overdue per user

Copy
Copied
select
    owner_id
    , count(id) as "overdue tasks"
from tasks
where
    due_at<current_date()
    and state='incomplete'
group by
    owner_id
order by
    "overdue tasks"
    desc
;

Are reps usually completing their tasks on time?

Purpose: Adoption/performance

Metrics: % of completed tasks that were overdue at the time of completion by week in the last 30 days

Copy
Copied
select
    owner_id
    , sum(case when date(due_at)<date(state_changed_at) then 1 else 0 end) as "overdue tasks"
    , count(id) as "total tasks"
from tasks
where
    due_at>(current_date()-30)
    and state='complete'
group by
    owner_id
order by
    "overdue tasks"
    desc
;

[Kaia Meetings] How long are our meetings running?

Purpose: Efficiency

Metrics: Average call duration by user by week in the last 180 days

Copy
Copied
select
    host_id
    , date_part(week, cast(activated_at as timestamp)) as wk
    , date_part(year, cast(activated_at as timestamp)) as yr
    , avg(datediff(second, cast(start_time as timestamp), cast(end_time as timestamp))) as duration
from kaia_meetings
where
    start_time>(current_date()-180)
group by
	host_id
	, wk
	, yr
;

[Kaia Meetings] How many meetings are reps hosting?

Purpose: Adoption/performance

Metrics: Count of calls hosted per user by week in the last 180 days

Copy
Copied
select
	host_id
    , date_part(week, cast(activated_at as timestamp)) as wk
    , date_part(year, cast(activated_at as timestamp)) as yr
	, count(*) as calls
from kaia_meetings
where
    start_time>(current_date()-180)
group by
	host_id
	, wk
	, yr
;

[Kaia Meetings] Are reps speaking too much in meetings?

Purpose: Performance

Metrics: Average talk time rate by user by week in the last 180 days

Copy
Copied
select
    outreach_id
    , date_part(week, cast(activated_at as timestamp)) as wk
    , date_part(year, cast(activated_at as timestamp)) as yr
    , avg(talk_time_rate) as duration
from kaia_meeting_participants
where
    start_time>(current_date()-180)
group by
	outreach_id
	, wk
	, yr
;