Using Heap data to power customer health score

  • 13 April 2023
  • 1 reply
Using Heap data to power customer health score
Userlevel 3
Badge +2
  • Community Manager
  • 8 replies

I recently posted a blog with one of our partners, Catalyst, on how I’ve created and tested Heap’s health score over the past several years. Wanted to share with the Heap Community to start a conversation around best practices for getting your product usage data from Heap into a normalized and actionable format.


We use our Health score at Heap to help CSMs, AMs, Support Engineers, and Scaled Adoption team prioritize outreach, trigger automated campaigns, and forecast renewal and expansion.


Here’s the full-length post that I did with Catalyst last week.


Highlights & key takeaways:

  1. Start with your key usage metrics
    1. Get those key metrics right from your Heap data! (see below)
    2. Determine the timeframe for aggregation (start with a month)
    3. Sync them into Catalyst (or another CS management tool, like Salesforce)
      1. Use Snowflake to aggregate + Sync
      2. Or use Heap’s SFDC connector (example: how to set that up)
  2. Avoid the temptation to over-engineer the score
    1. Observe how the score works for 1-2 quarters, then measure efficacy of the score, consider changes
    2. Start with 3-5 key metrics that can be easily captured (product usage behavior is perfect here)
    3. Rely as little on manual input as possible (behavioral data is great here)
  3. Build the score into your operating cadence to make it actionable
    1. We do a bi-weekly review of accounts at risk with our leadership team to swarm around action plans

The image at the top of this post is an example of what our health score looks like today.

All of the metrics in the “Adoption” section come from Heap.


Below I describe, including screenshots with guidance (and examples to leverage), how to quickly build your own score using the Heap + Snowflake + Catalyst recipe.

  1. Define, choose and sync the event from Heap:

In this example, I use an event that fires when someone clicks on the “Run Query” button in our app.

Note the table name in Snowflake for this event is going to be “app_analyze_click_run_query_all”. I would put that (later) in my SQL as “BEHAVIOR1”


  1. Login to your Snowflake account and create the view that you’ll sync to the other tool (like Catalyst). When you login, you will see Heap created Views (tables) in Snowflake for the events you synced, like this:
    Each event from Heap will be its own view, as in Behavior1,2,3,4 above in my terrible red annotation :). 


  2. Stay with me on this - it seems much harder than it actually is. 😉 You can use the example SQL I created here to create your own metrics rollup. This metrics rollup is what gets synced into Catalyst (or other tool).
    1. It will be organized in this case by USER/CONTACT and then we’ll do the rollup inside Catalyst using their rollup functionality.
    2. You could also roll them up by AccountID here, but for this example, I wanted to get to the lowest level of granularity.

Go into worksheets in Snowflake, and create a worksheet with your modified version of this SQL:

//Customize the metrics called METRIC1[,2,3,4] which will be based on the BEHAVIOR1[,2,3,4] events from Heap. 

//BEHAVIOR1[,2,3,4] are the table names you specify when you sync and event from the Heap UI into Snowflake.

//NAME_OF_YOUR_VIEW is the name of the view you create in Snowflake that is synced to Catalyst (or another CS tool).

create or replace view MY_USAGE_STATS(
company_name, //change to a property you care about or remove
company_id, //change to a property you care about or remove
number_sessions_30d, //uses Heap default table - no changes needed
number_sessions_90d, //uses Heap default table - no changes needed
most_recent_session, //uses Heap default table - no changes needed
METRIC1_30d, // specific metric 1 from your product
METRIC2_30d, // specific metric 2 from your product
METRIC3_30d, // specific metric 3 from your product
METRIC4_30d // specific metric 4 from your product
)as (

METRIC1_30d as (
select user_id, count (event_id) as METRIC1_30d
from HEAP_MAIN_PRODUCTION.HEAP.BEHAVIOR1 // table name comes from the event you sycned from Heap to Snowflake
where time >= DATEADD(Day ,-30, current_date)
group by 1

METRIC2_30d as (
select user_id, count (event_id) as METRIC2_30d
where time > DATEADD(Day ,-30, current_date)
group by 1

METRIC3_30d as (
select user_id, count (event_id) as METRIC3_30d
where time > DATEADD(Day ,-30, current_date)
group by 1

METRIC4_30d as (
select user_id, count (event_id) as METRIC4_30d
where time > DATEADD(Day ,-30, current_date)
group by 1

//no changes needed to users, sessions as those rely on the out of the box tables from Heap Connect
users as (
select, u.user_id, u.company_name, as company_id
group by 1,2,3,4

number_sessions_30d as (
select user_id, count(session_id) as number_sessions_30d
where (time > DATEADD(Day ,-30, current_date))
group by 1

number_sessions_90d as (
select user_id, count(session_id) as number_sessions_90d
where (time > DATEADD(Day ,-90, current_date))
group by 1

most_recent_session as (
select user_id, max(time) as most_recent_session
group by 1

final as (
users.company_name, // comes from a custom property in your account - remove if you don't have this
users.company_id, // comes from a custom property in your account - remove if you don't have this
from users
left join METRIC1_30d
on users.user_id = METRIC1_30d.user_id
left join METRIC2_30d
on users.user_id = METRIC2_30d.user_id
left join METRIC3_30d
on users.user_id = METRIC3_30d.user_id
left join METRIC4_30d
on users.user_id = METRIC4_30d.user_id
left join number_sessions_30d
on users.user_id = number_sessions_30d.user_id
left join number_sessions_90d
on users.user_id = number_sessions_90d.user_id
left join most_recent_session
on users.user_id = most_recent_session.user_id

where email is not null and email not like '%@garbage%' and email not like '' and most_recent_session is not null // can be deleted - used to filter out junk or internal users
group by 1,2,3,4,5,6,7,8,9,10,11


select * from final


Once you run this, it’ll create a new view called “MY_USAGE_STATS” (or whatever you replace that with in your SQL). 


Next, you’ll need to make that view accessible to other readers in your Snowflake account. Run this in your Snowflake worksheet:

grant all privileges on view MY_USAGE_STATS to role public;


By the way, the output of your new view called “MY_USAGE_STATS” will look like this. You’ll have a single row for each user ID with columns for the metrics we aggregated above:

  1. Sync the view into Catalyst. You’ll authenticate your Catalyst <> Snowflake account, then you will be able to choose the view that you just created. Pay attention to the Matching Strategy to ensure that you match a property on your Catalyst Contact with the ID you used in your Snowflake view.
    Snowflake configuration screen in Catalyst
  2. Do a quick rollup of your Contact level behaviors onto the account level like this in Catalyst:
Calculated Field configuration screen in Catalyst


  1. Woohoo! Now your metrics will sync from Heap → Snowflake → Catalyst → Aggregated at the Account level, and you can use them within your health score like this:
Health Score setup screen in Catalyst where you select the metrics and weighting of each score grouping.


How are you thinking of using behavioral data to drive your health score? Are you using behavioral data to automate interactions or automated campaigns? What challenges and successes have you seen?

1 reply

Userlevel 1
Badge +2

You’ve laid out the steps very nicely, Lane. Thanks for sharing the work you did so others don’t have to reinvent the wheel!