Cookbook

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:

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:

To run this query you need to know the following:

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