Cookbook
- 12 minute read
- Contributors
- DarkLight
Cookbook
- 12 minute read
- Contributors
- DarkLight
Article summary
Did you find this summary helpful?
Thank you for your feedback
This cookbook contains examples of queries you can make on the Gong data model so that you answer your business questions based on what is really going on. The stakeholders in your company will be able to react immediately to changes that affect your company, and make important strategic decisions that will have the most impact on your business. In this section, we demonstrate how you can query the database to answer your business questions. We provide out-of-the-box queries, with minimal customization required, that will get you from data to insights in minutes rather than days.
Query 1: Getting the most out of Tracker data
Trackers are tools that identify when words, phrases or concepts are mentioned in calls, allowing you to know what your reps and customers are talking about. If you set up trackers effectively, you can monitor important questions such as how often competitors come up in your sales calls, or which competitors are mentioned the most.
Aggregating tracker and call data
This query gives data on how different trackers are mentioned in your calls, so that you can answer business questions on competitor analysis or initiative tracking. Following are the different scenarios you can answer with tracker data, or you can jump straight to the query.
Competitor analysis
Ever wondered how often competitors come up in your sales calls? Which competitors are trending among your customer base? Gong can help you answer these questions. To answer these questions you need to check that you have competitor trackers configured in your Gong instance (contact your Gong admin for more details), and have access to the Gong data model (via your datawarehouse).
The query gives you the data you need to answer questions such as:
How often are competitors mentioned in your sales calls? Are your sales calls increasingly competitive? How often do your customers bring up competitors?
How often does each competitor get mentioned in your sales calls? Is there a competitor you should pay special attention to? How is each competitor trending among your prospects? What percentage of your customer calls are each of your major competitors mentioned in?
Initiative Tracking
Your go-to-market teams are constantly shipping new initiatives, be it new products, pricing strategies or sales talk tracks. Help the sales executives, front line managers and sales enablement teams track the adoption and traction of their initiatives. To monitor initiative adoption, you need the initiative tracker names, which you can get from your Gong admin, or look them up in the Trackers table in your Gong database in your data warehouse.
The query gives you the data you need to answer questions such as:
Which reps are adopting a new initiative? Which reps are mentioning the new product in their calls? Which reps need coaching to become more comfortable with the new talk track? See the percentage of each rep’s calls the new initiatives are mentioned in, and create a personalized report for each manager to monitor their own team and initiatives.
How are your initiatives trending over time? Are your sales teams getting better at adopting the new talk track? Are they finding it useful to discuss the new product? See how the percentage of mentions of each strategic initiative in a team’s calls are trending over time.
Running the query
To run this query you need to know the following:
The workspace your calls are in
The names of the trackers and how they are set up. Competitors, for example, might have one tracker with keywords for each competitor, or a separate tracker for each competitor. You can get the tracker names from your Gong admin, or look them up in the Trackers table in your Gong database in data warehouse.
-- set the trackers you want to analyze
set tracker1 = 'Competitors / competitor1';
set tracker2 = 'Competitors / competitor2';
-- get all your calls and their attributes
with calls as
(
select c.WORKSPACE_IDS,
c.CONVERSATION_ID call_id,
c.CONVERSATION_KEY,
u.MANAGER_ID,
cl.OWNER_ID,
cl.EFFECTIVE_START_DATETIME::date call_date
-- additional attributes
from [db_name].[schema_name].CONVERSATIONS c -- change to your DB/schema name
join [db_name].[schema_name].CALLS cl
on c.CONVERSATION_KEY = cl.CONVERSATION_KEY
and cl.STATUS = 'COMPLETED' -- completed calls only
-- select the timeframe of the call
and cl.EFFECTIVE_START_DATETIME > current_date - interval '6 month'
and cl.EFFECTIVE_START_DATETIME < current_date
left join [db_name].[schema_name].USERS u
on cl.OWNER_ID = u.USER_ID
and u.valid_to_datetime is null
-- left join to more tables for additional attributes
-- for example: left join to [db_name].[schema_name].CALL_RECORDING
-- for call attributes, left join to [db_name].[schema_name].USERS for user attributes
where array_contains('[workspace_id]'::variant, c.WORKSPACE_IDS)
and c.CONVERSATION_TYPE = 'call' -- 'CONVERSATIONS' table have more conversation types, like 'email'
),
-- get trackers in calls (a call can have more than one tracker)
trackers as
(
select ct.CONVERSATION_KEY,
ct.TRACKER_ID,
t.NAME tracker_name,
ct.COUNT tracker_count -- count of tracker mentions in a call
from [db_name].[schema_name].CONVERSATION_TRACKERS ct -- trackers in calls
join [db_name].[schema_name].TRACKERS t -- tracker's metadata
on ct.TRACKER_ID = t.TRACKER_ID
and t.NAME in ('tracker 1', 'tracker 2'/*, ...*/) -- tracker names
where ct.WORKSPACE_ID = '[workspace_id]'
),
-- calls and trackers (not all calls have trackers)
-- some calls will have more than one record (if they had more than one tracker)
calls_and_trackers as
(
select c.*,
t.tracker_name,
t.tracker_count,
case when t.tracker_count > 0 then c.call_id end call_id_had_tracker
from calls c
left join trackers t
on c.CONVERSATION_KEY = t.CONVERSATION_KEY
)
-- edit this section to aggregate the report according the data you need (user / date / other)
select c.OWNER_ID, -- call owner ; can change to call owner manager
date_trunc('month', c.call_date) month,
-- time bucket ; can change to any other bucket (day, week, quarter, year)
count(distinct c.call_id) calls,
$tracker1 tracker1_name,
count(distinct case when c.tracker_name = $tracker1 then c.call_id_had_tracker end)
calls_with_tracker1,
$tracker2 tracker2_name,
count(distinct case when c.tracker_name = $tracker2 then c.call_id_had_tracker end)
calls_with_tracker2
-- % calls with tracker: we suggest to calculate that in your BI tool,
--so you could measure it along different dimensions (user / month / both)
from calls_and_trackers c
-- filter on eligible calls only (for example - calls of AEs only)
group by 1,2
-- keep data points (in this case: user and month) with 10+ calls
having calls >= 10
Query 2: How tracker mentions change over time
This query returns a list of trackers and how many times they were mentioned each month. Following are the different scenarios you can answer with tracker data, or you can jump straight to the query
Competitor trends
You may want to monitor which of your competitors is being mentioned more by your customers and whether there has been a shift over a period of time which you need to adjust to. This query answers questions such as which competitors have been gaining/losing ground recently? Which competitors have the most upward trend among your prospects recently? And which one is losing ground?
Running the Query
To run this query you need to know the workspace ID the calls are in.
-- get all your calls and their attributes
with calls as
(
select c.WORKSPACE_IDS,
c.CONVERSATION_ID call_id,
c.CONVERSATION_KEY,
u.MANAGER_ID,
cl.OWNER_ID,
cl.EFFECTIVE_START_DATETIME::date call_date,
date_trunc('month', call_date) call_month
-- additional attributes
from [db_name].[schema_name].CONVERSATIONS c -- change to your DB/schema name
join [db_name].[schema_name].CALLS cl
on c.CONVERSATION_KEY = cl.CONVERSATION_KEY
and cl.STATUS = 'COMPLETED' -- completed calls only
and cl.EFFECTIVE_START_DATETIME > current_date - interval '6 month' -- select the timeframe of the call
and cl.EFFECTIVE_START_DATETIME < current_date
left join [db_name].[schema_name].USERS u
on cl.OWNER_ID = u.USER_ID
-- left join to more tables for additional attributes
-- for example: left join to [db_name].[schema_name].CALL_RECORDING
-- for call attributes, left join to [db_name].[schema_name].USERS for user attributes
where array_contains('[workspace_id]'::variant, c.WORKSPACE_IDS)
and c.CONVERSATION_TYPE = 'call' -- 'CONVERSATIONS' table have more conversation types, like 'email'
),
-- get trackers in calls (a call can have more than one tracker)
trackers as
(
select ct.CONVERSATION_KEY,
ct.TRACKER_ID,
t.NAME tracker_name,
ct.COUNT tracker_count -- count of tracker mentions in a call
from [db_name].[schema_name].CONVERSATION_TRACKERS ct -- trackers in calls
join [db_name].[schema_name].TRACKERS t -- tracker's metadata
on ct.TRACKER_ID = t.TRACKER_ID
where ct.WORKSPACE_ID = '[workspace_id]'
),
-- calls and trackers (not all calls have trackers)
-- some calls will have more than one record (if they had more than one tracker)
calls_and_trackers as
(
select c.*,
t.tracker_name,
t.tracker_count,
case when t.tracker_count > 0 then c.call_id end call_id_had_tracker
from calls c
left join trackers t
on c.CONVERSATION_KEY = t.CONVERSATION_KEY
),
total_monthly_calls as
(
select c.call_month,
count(distinct c.call_id) calls
from calls_and_trackers c
group by 1
)
-- aggregate to the required level (user / date / other)
select c.tracker_name,
date_trunc('month', c.call_date) month,-- time bucket ; can change to any other bucket (day, week, quarter, year)
mc.calls,
count(distinct c.call_id) calls_with_tracker,
calls_with_tracker / mc.calls::float rate_calls_with_tracker,
abs(rate_calls_with_tracker - lag(rate_calls_with_tracker) over
(partition by c.tracker_name order by month)) >= 0.10 significant_change_since_last_period
-- rate of tracker in this timer period, in comparison to the previous ; if the gap is above +- X% - return true
from calls_and_trackers c
join total_monthly_calls mc
on c.call_month = mc.call_month
and mc.calls >= 10 -- time buckets with 10+ calls
where c.tracker_name is not null
group by 1,2,3
order by 1,2Query 3: Coaching and Collaboration
This query returns details of the type of coaching each manager gives their team, and whether a team member gives feedback or comments on a call to collaborate with their team member. Following are the different scenarios you can answer with coaching data, or you can jump straight to the query.
See which team members need coaching
See which managers are effectively coaching their team
See the percentage of calls managers coach on
See which team members are giving feedback to other team members
Running the query
To run this query you need to know the following:
The workspace your calls are in
-- get all your calls, and their coaching activities
with calls as
(
select c.WORKSPACE_IDS,
u.MANAGER_ID,
u2.FIRST_NAME || ' ' || u2.LAST_NAME manager_name,
cl.OWNER_ID,
c.CONVERSATION_ID call_id,
c.CONVERSATION_KEY,
date_trunc('month', cl.EFFECTIVE_START_DATETIME)::date month_of_call,
a.GONG_ACTIVITY_DATETIME,
a.GONG_ACTIVITY_TYPE,
case when a.GONG_ACTIVITY_TYPE is not null then c.CONVERSATION_ID end call_id_with_manager_coaching
-- change your db/schema name
from [db_name].[schema_name].CONVERSATIONS c
join [db_name].[schema_name].CALLS cl
on c.CONVERSATION_KEY = cl.CONVERSATION_KEY
and cl.STATUS = 'COMPLETED' -- completed calls only
--select the timeframe of the call
and cl.EFFECTIVE_START_DATETIME > current_date - interval '6 month'
and cl.EFFECTIVE_START_DATETIME < current_date
join [db_name].[schema_name].USERS u
on cl.OWNER_ID = u.USER_ID
and u.valid_to_datetime is null
join [db_name].[schema_name].USERS u2
on u.MANAGER_ID = u2.USER_ID
and u2.valid_to_datetime is null
left join [db_name].[schema_name].USER_CONVERSATION_GONG_ACTIVITIES a
on a.CONVERSATION_KEY = c.CONVERSATION_KEY
and a.USER_ID = u.MANAGER_ID
-- list of coaching activities
and a.GONG_ACTIVITY_TYPE in ('gave_comment', 'filled_scorecard', 'gave_feedback', 'listened_to_call')
and a.GONG_ACTIVITY_DATETIME > current_date - interval '6 month'
where array_contains('[workspace_id]'::variant, c.WORKSPACE_IDS)
and c.CONVERSATION_TYPE = 'call'
)
-- get a list of managers, the number of calls made by people who report directly to the manager,
-- the number and percentage of calls the manager gave coaching on
select c.manager_name,
c.month_of_call,
count(distinct c.call_id) total_calls_of_direct_reports,
count(distinct c.call_id_with_manager_coaching) calls_gave_coaching,
calls_gave_coaching / total_calls_of_direct_reports::float "% calls gave coaching"
from calls c
group by 1,2
order by 1,2Query 4: Team member activity reporting
This query returns data on team members activities such as, the calls they made, how long they lasted and how many calls they made in total. Using this data, you get a full picture of your teams' communication patterns and behavior. Following are some insights you can get from the activity data, or you can jump straight to the query.
How much time each team member spends talking to customers.
The number of calls they participate in.
Running the query
To run this query you need to know the following:
The workspace your calls are in
with calls as
(
select c.WORKSPACE_IDS,
c.CONVERSATION_TYPE,
c.CONVERSATION_ID,
cl.EFFECTIVE_START_DATETIME::date activity_date,
cl.OWNER_ID call_owner_id,
u.FIRST_NAME || ' ' || u.LAST_NAME call_owner_name,
u.MANAGER_ID call_owner_manager_id,
u2.FIRST_NAME || ' ' || u2.LAST_NAME call_owner_manager_name
-- change to your DB/schema name
from [db_name].[schema_name].CONVERSATIONS c
join [db_name].[schema_name].CALLS cl
on c.CONVERSATION_KEY = cl.CONVERSATION_KEY
-- select the timeframe of the call
and cl.EFFECTIVE_START_DATETIME > current_date - interval '1 month'
and cl.EFFECTIVE_START_DATETIME < current_date
left join [db_name].[schema_name].USERS u
on cl.OWNER_ID = u.USER_ID
and u.valid_to_datetime is null
left join [db_name].[schema_name].USERS u2
on u.MANAGER_ID = u2.USER_ID
and u2.valid_to_datetime is null
where c.CONVERSATION_TYPE = 'call'
and array_contains('[workspace_id]'::variant, c.WORKSPACE_IDS)c.WORKSPACE_ID = 5237998047883638784
),
activities as
(
select *
from calls c
)
select a.call_owner_name,
a.call_owner_manager_name,
count(distinct case when a.CONVERSATION_TYPE = 'call' then a.CONVERSATION_ID end) calls,
-- meetings ...
-- assumes 20 business days in the last month. Change according to your needs
calls / 20.0 calls_per_day from activities a
group by 1,2
Query 5: Deal activity reporting
This query returns data on deal activities such as, which team members are working on specific deals and the accounts each team member works with. Following are some insights you can get from the activity data, or you can jump straight to the query.
Which deals have a lot of activity?
Are your team members investing in the correct deals?
Which deals could be at risk due to low activity?
Running the query
select cc.OBJECT_ID crm_opportunity_id,
count(distinct cc.CONVERSATION_KEY) activities,
count(distinct case when c.CONVERSATION_TYPE = 'call' then cc.CONVERSATION_KEY end) calls
from [db_name].[schema_name].CONVERSATION_CONTEXTS cc
join [db_name].[schema_name].CONVERSATIONS c
on cc.CONVERSATION_KEY = c.CONVERSATION_KEY
where cc.OBJECT_TYPE = 'opportunity'
and c.CONVERSATION_DATETIME > current_date - interval '5 day'
group by 1Displaying the data
Once you have the data you need, you can put the data in your BI tool and create reports or graphs for a graphic visualisation for the questions you want to answer.
Was this article helpful?