Sisense has always taken a customer-centric approach to our business, relying on a range of product usage and customer experience metrics to ensure our users are happy and our accounts are healthy. We recently posted a <a href="https://www.sisense.com/blog/helping-humans-with-healthcare-analytics/">blog article</a> outlining the reasons we think customer health is so important.



We&#8217;d also like to provide an easy approach to creating your first customer health score in SQL to help you get started. Customer health is metric that is really a combination of metrics, so it requires creativity and company expertise to do well. What we are creating is a simple model with a series of independent variable inputs, and a customer health score as a &#8220;target&#8221; or &#8220;dependent&#8221; variable output.&nbsp;



Building a Customer Health Score has three steps.



<ol><li>Choose input metrics — the numbers you think are critical to customer health</li><li>Normalize them so your customer success team can have a 0–100 score for easy consumption</li><li>Visualize the output</li></ol>



<h2 class="wp-block-heading">Your Input Variables</h2>



The first thing to do is identify your potential model inputs. These can come from a wide range of sources, but generally fall into a few types:



<ul><li>Behavioral Indicators — Product usage data, support interactions or product training completion are all important examples of these</li><li>Customer Feedback — Net Promoter Score or Customer Effort Score for example</li><li>Subjective Evaluations — These come from interactions with the success team, who can gauge a customer&#8217;s health based on their expertise and interactions</li></ul>



It&#8217;s often best to use some simple analysis to find the best indicators — for example, by correlating the possible metrics to retention — but for your first CHS you can have the analytics and customer success teams put their heads together and come up with some initial metrics to get started.&nbsp;



For this post, we&#8217;re going to take three important metrics and combine them into a unified customer health score.&nbsp;



<ul><li>Net Promoter Score (If you&#8217;d like to brush up on NPS <a href="https://www.sisense.com/blog/net-promoter-score/">see our blog post here</a>)</li><li>Company Time in Product</li><li>Percent of users who were active at least once a month (%MAU)</li></ul>



<h2 class="wp-block-heading">Data Normalization</h2>



For a CHS you&#8217;ll need to normalize each of your variables to a 0–100 range. Once all your variables are normalized, you can take a simple average and use that as your score. Since we are using three scores, this would be as simple as:



<pre class="wp-block-code"><code>(normalized(NPS) + normalized(Company Time in Product) + normalized(%MAU)) / 3 = CHS</code></pre>



But of course, we need to think carefully about how we normalize our data!



Let&#8217;s take a look at Net Promoter Score. NPS can be examined at in a few ways. A 0–10 integer or as a three-tiered categorization of Detractors, Passives and Promoters are the most common. This means there are a number of ways we could normalize our NPS score.&nbsp;



The simplest would be to take the integer survey response and multiply it by 10. Every score will range from 0–100, and we&#8217;d be done! This is a good first approximation, but in some ways ignores the most critical insight in NPS — that users fall into categories that behave differently.



Alternatively, we could assign point values to our NPS segments. For instance, we could say that Detractors are 0, Passives are 50, and Promoters are 100. This is arbitrary but captures the ideology of NPS by utilizing a step function.&nbsp;



To demonstrate something a bit more complex, we&#8217;ll use both. From a previous analysis, we know any score below 4 is very bad, so we&#8217;ll weight those to 0 and then have a smoother function for higher scores. We can define a simple step function with a case statement for NPS to create a score that more closely matches our expectations, and when we do more analysis, we can refine it.&nbsp;



<pre class="wp-block-code"><code>with nps_chs_data as (
 select
 company
 , avg(case 
 when nps_integer &lt;= 4 then 0
 when nps_integer > 4 and nps_integer &lt;= 6 then 30
 else nps_integer * 10
 end) as nps_chs_score
 from nps_data
 where survey_date > now() - 30
 group by 1)</code></pre>



Our next metric, Company Time in Product, is a a bit different. It has no absolute scale across companies. The amount of time a company should spend in our product is dependent on the size of a company and the number of users. We need to normalize it to the company&#8217;s user base and then give a score.&nbsp;



First, we need to figure out how much time we expect a company to use our product based on the number of users a company has. This is as simple as aggregating our total time in product and dividing it by our total user base.&nbsp;



We might see that an average user across all our companies spends 70 hours per month in product. We&#8217;re pretty happy with any company that is using our product as much or more than an average user, so we&#8217;ll say that&#8217;s a a score of 80.



We can normalize our average product usage to a score of 80, and use a least function to ensure a maximum score of 100. With these values, a customer will get a perfect score of 100 if they spend 87.5 hours or more in product per month.



<pre class="wp-block-code"><code>, product_usage_chs_data as (
 select 
 company
 , count(distinct user_id) / time_in_product as time_in_product_per_user

 , least((80/70.0) * time_in_product) / count(distinct user_id), 100) as time_in_product_chs_score)
 from time_in_product_logs
 where timestamp > now() - 30
 group by 1)</code></pre>



Our final metric, %MAU, is a company specific measure. This means we won&#8217;t need an average value for our company compared to our total user base like we did with the previous metric. In this case, we&#8217;ll simply say the percentage of users active in any month is the health score. We can measure this by joining two common tables, a table of our users, and a table of time in product by user.



<pre class="wp-block-code"><code>, mau_chs_data as (
 select 
 company
 , count(distinct a.user_id) / (count(distinct all_users.user_id) * 1.0) * 100 as pct_users_active_chs_score
 from all_users
 left join time_in_product_logs
 using (userid)
 where timestamp > now() - 30
 group by 1)
</code></pre>







And boom! We have 3 normalized metrics, all nicely scaled from 1 to 100.



<h2 class="wp-block-heading">Stitching it all Together</h2>



Now we can join the 3 tables together and calculate our final customer health score!



<pre class="wp-block-code"><code>select
 company
 -- If you don't have data for each company, you'll need to make sure to programatically set your denominator here.
 , round((nps_chs_score + time_in_product_chs_score + pct_users_active_chs_score) / 3, 0) as customer_health_score
 , round(nps_chs_score, 0) as nps_score
 , round(time_in_product_chs_score, 0) as time_in_product_score
 , round(pct_users_active_chs_score, 0) as mau_score 
from nps_chs_data
join product_usage_chs_data
 on nps_chs_data.company = product_usage_chs_data.company
join mau_chs_data
 on nps_chs_data.company = mau_chs_data.company</code></pre>



Now we output a table and add a bit of color, and our customer success team can immediately begin triaging any at-risk customers. One you begin to use (and optimize) a CHS you&#8217;ll begin seeing immediate benefits.



<figure class="wp-block-image fancybox"><img decoding="async" src="https://cdn.sisense.com/wp-content/uploads/Customer-health-score.png" alt="Customer health score" class="wp-image-75018"/></figure>



Over time, as we analyze our data we can fine tune our metrics, add additional metrics and really understand when our customers are at risk from a single table!



If you’re interested in learning more about how modern customer success teams use data, download our <a href="https://www.sisense.com/whitepapers/better-customer-health-through-data-analysis/">Better Customer Health Through Data Analysis</a> guide.

Building a Customer Health Score Dashboard in SQL

LinkedIn

Twitter

GitHub

curve-image-unique-image-unique

curve

3-dark-2-image-unique-image-unique

3 DARK 2

Get the latest in analytics right in your inbox.

Article