Cookbook

10 minute read
Contributors

Cookbook

10 minute read
Contributors

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-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.

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 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?

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 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.

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 Snowflake.

-- 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,2

Query 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,2

Query 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 1

Displaying 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?

What's Next

Integrations with Gong

In this article

Query 1: Getting the most out of Tracker data
Query 2: How tracker mentions change over time
Query 3: Coaching and Collaboration
Query 4: Team member activity reporting
Query 5: Deal activity reporting
Displaying the data