This cookbook contains examples of queries you can make on the Gong-Snowflake 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 Gong-Snowflake 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.
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.
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.
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 toif 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 Snowflake).
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?
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 Snowflake.
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.
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 Snowflake.
-- get all your calls and their attributes with calls as ( select c.WORKSPACE_ID, 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 -- 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 c.WORKSPACE_ID = 12345 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 = 12345 ), -- 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 ) -- aggregate to the required level (user / date / other) -- call owner ; can change to call owner manager select c.OWNER_ID, -- time bucket ; can change to any other bucket (day, week, quarter, year) date_trunc('month', c.call_date) month, count(distinct c.call_id) calls, 'tracker 1' tracker1_name, count(distinct case when c.tracker_name = 'tracker 1' then c.call_id_had_tracker end) calls_with_tracker1, 'tracker 2' tracker2_name, count(distinct case when c.tracker_name = 'tracker 2' 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
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 Running the Query
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?
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_ID, 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 c.WORKSPACE_ID = 12345 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 = 12345 ), -- 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,2