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.
Since these queries are written in simple SQL using the default table names, they can be used in any supported Data Sharing provider. If you choose to rename tables or columns, or only expose a subset of tables or columns to your users, these queries may need to be adjusted accordingly.
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)
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
select
creator_id
, count(distinct prospect_id)
from sequence_states
where
activated_at>(current_date()-180)
and lower(creator_type)=’user’
group 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
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
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)
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
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)
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
;What kinds of rejections are we getting the most often when users reply to our sequenced emails?
Purpose: Effectiveness
Metrics: Count of users who viewed a recording by recording made in the last 180 days
select
intent_prediction_category
, prediction_label
, count(replier_message_id) as "total replies"
from email_sentiment
where
created_at>(current_date()-180)
and intent_prediction_category = 'Objection'
group by
intent_prediction_category
, prediction_label
;What kinds of objections are we getting the most often when users reply to our sequenced emails?
Purpose: Effectiveness
Metrics: Count of replies by objection type classified in the last 180 days
select
intent_prediction_category
, prediction_label
, count(replier_message_id) as "total replies"
from email_sentiment
where
created_at>(current_date()-180)
and intent_prediction_category = 'Objection'
group by
intent_prediction_category
, prediction_label
;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.
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
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
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
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
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
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
;[Kaia Meetings] Are our recorded prospect meetings getting viewed after the meeting?
Purpose: Adoption
Metrics: Count of users who viewed a prospect meeting by recording made in the last 180 days
with rv as (
select
*
from kaia_recordings_viewed
where
event_time>(current_date()-180)
and button_type = 'kaia recording play'
)
select
m.instance_id
, count(distinct rv.user_id) as "unique users"
from kaia_meetings as m
left join rv
on m.instance_id=rv.instance_id
and m.o_id=rv.o_id
where
m.start_time>(current_date()-180)
and m.has_prospect = TRUE
group by
m.instance_id
;[Reporting] How do I see SER data by team, grouped by the team the user was on the day of the activity?
Purpose: Performance
Metrics: All team-based SER data grouped by date using team membership history
WITH teams AS (
SELECT DISTINCT
fi.user_id,
fi.activity_date,
tms.team_id,
team.name AS team_name
FROM TEAM_MEMBERSHIPS_HISTORY tms
JOIN FUNNEL_INSIGHTS fi
ON fi.user_id = tms.user_id
AND to_date(fi.activity_date) >= to_date(tms.created_at)
JOIN TEAMS team
ON tms.team_id = team.id
WHERE ((tms.IS_DELETED_IN_APP = FALSE ) OR
(tms.IS_DELETED_IN_APP = TRUE AND to_date(fi.activity_date) < to_date(tms.dml_at)))
)
SELECT
t.team_id,
t.team_name,
fi.*
FROM FUNNEL_INSIGHTS fi
LEFT JOIN teams t
ON fi.user_id = t.user_id
AND fi.activity_date = t.activity_date
ORDER BY fi.activity_date DESC[Reporting] How do I see SER data by team, grouped by the team the user is currently on regardless of where they were at the time of the activity?
Purpose: Performance
Metrics: All team-based SER data grouped by date
with teams AS (
SELECT DISTINCT
fi.user_id,
tms.team_id,
team.name as team_name,
FROM TEAM_MEMBERSHIPS tms
JOIN FUNNEL_INSIGHTS fi
ON fi.user_id = tms.user_id
JOIN TEAMS team
ON tms.team_id = team.id
)
select
t.team_id,
t.team_name,
fi.*
from FUNNEL_INSIGHTS fi
LEFT JOIN teams t
ON fi.user_id = t.user_id