Customer Segmentation for SAAS, API products

Using SQL to segment customers into various clusters.

26 Mar

A brief history of change

Everyone knows about their customers. But do you “really” know which customers your product provides the most value to?

Some popular household names didn’t and later changed their approach.

  • McDonald’s: From kids → To low-income families

  • Netflix: DVD player owners → Anyone with a laptop, PC, or phone

  • Dunkin Donuts: Average American who commutes → Students and young adults

There were many more, and frankly, there always will be brands that change their target audience due after thorough analytical due diligence.

Why research and identify your segments?

  • To scale

  • To earn more revenue

  • To get a more significant ROI (Return on Investment)

  • Launch features more tailored to your users. And others that we’ll look at during our journey in this substack!Subscribe now

  • Demographic segmentation: This approach involves dividing your user base into groups based on shared characteristics like age, income, gender, and occupation.

  • Geographic segmentation: With this technique, you segment your users based on their physical location, such as city, state, region, country, or continent.

  • Psychographic segmentation: This approach involves segmenting your users based on shared personality traits, values, lifestyles, hobbies, and passions.

  • Behavioral segmentation: With this technique, you divide your users into groups based on their in-app behaviors, personal habits, purchase frequency, product preferences, and other tendencies.

And probably 69420 more that the internet broke down into sub-sections and their sub-sections. See the pyramid scheme? Yeah, let’s focus on only these four above for now.

Case study: API for tax rates

I’m utilizing a product usage dataset for an API that provides tax information in its API responses. Find the dataset here. This dataset looks like this:

Our first task is to allocate the respective cost to the pricing plan and aggregate the view by user fields and month.with data as (select *, CASE WHEN pricing_plan = 'Basic' then 10 WHEN pricing_plan = 'Enterprise' then 50 WHEN pricing_plan = 'Pro' then 20 end as plan_pricefrom tableA)select month, employment, pricing_plan, sum(plan_price) as monthly_revenue,sum(api_calls) as monthly_api_callsfrom datagroup by 1, 2,3

A graph representation of this shows us that users working in sales produce the most revenue in all three pricing plans, usually followed by pharma, acquisitions, and tech.

We can also drill this down and calculate the rolling median revenue per plan during the past 12 months. This provides a better metric for revenue by excluding outliers.

An additional way to identify value users would be to bin each user into age groups e.g. 20-25, 25-30, and granulate the above data into sub-segments. Perhaps 25-30-year-olds in sales generate significantly more revenue than 35-40-year-olds. In that case, these users would be one of our more valued customer segments, amongst others.

This should allow targeting the marketing funnels and a good chunk of product features to be tailored to this domain, resulting in more growth.