DEV Community: PopSQL

The Easy Way to Find Customers Likely to Churn

Bill Babeaux — Thu, 03 Sep 2020 20:47:13 +0000

Now more than ever, you have to hold onto every customer you can.

Customers don’t simply love your product one minute and fall out of love the next. Normally there’s a declining trend in usage of your product over time.

The good news: a single proactive outreach can reverse a downward trend.

The challenge: efficiently finding customers with declining usage.

Why SQL?

With just graphing tools, it’s near impossible to spot trends in customer usage. It’s neon spaghetti:

And examining customers one by one? That would take ages.

Using statistical functions

PostgreSQL’s statistical functions help you rapidly sift through the noise. We explore these functions further in our template on Linear Regression in SQL.

Below we’ll use just the regr_slope() function:

| Function         | Argument Type    | Return Type      | Description                                                                   |
|------------------|------------------|------------------|-------------------------------------------------------------------------------|
| regr_slope(Y, X) | double precision | double precision | slope of the least-squares-fit linear equation determined by the (X, Y) pairs |

This function first creates a trendline that fits your data. It then tells you the slope of that line (your algebra teacher was right! You will use this stuff in your real job 🤓 ).

What regr_slope() is doing:

SQL you can copy / paste

Here’s the query (we’ll break it down below):

-- DATA PREP
with action_data AS (
  select
    extract('week' from time) as week, -- quick hack to turn week into a numeric so we can use regr_slope() function
    team_id, -- we're looking at team usage
    (count(name) / count(distinct user_id))::numeric as actions_per_user -- we want to see actions per user to normalize
  from events
  where time between '{{start_date}}' and '{{end_date}}' -- your date range, make sure your start_date is a Monday and end_date is a Sunday
  group by 1,2
)

-- FINDING SLOPE OF ALL CUSTOMER USAGE TRENDLINES
select
  team_id,
  count(week) as weeks_considered,
  round(regr_slope(actions_per_user,week)::numeric,2) as slope
from action_data
group by 1
having count(week) >= 5 -- let's say we want at least 5 weeks of data
  and regr_slope

Data prep

The first part of the query in the CTE action_data is just data prep.

The extract() function turns our timestamp into a numeric, as regr_slope() doesn’t accept timestamps as parameters. This hack works great except at the change of calendar years (workaround at the bottom of the post).

For the extract() function, a week goes from Monday to Sunday. Avoid partial weeks by starting your date range with a Monday and ending on a Sunday 👍

The output of the CTE action_data looks like this:

| week | team_id | actions_per_user |
|------|---------|------------------|
| 12   | 93336   | 28               |
| 13   | 93336   | 6                |
| 14   | 93336   | 10               |
| ...  | ...     | ...              |
| 12   | 92982   | 26               |
| 13   | 92982   | 1                |
| 14   | 92982   | 2                |
| ...  | ...     | ...              |

Finding slope of all customer usage trendlines

In the second part of the query, the regr_slope() creates a trendline between actions_per_user and week for each team, then returns the slope of that trendline. Again, visualized:

If the slope is negative, then the usage trend is negative. The more negative the output, the steeper the decline in their usage. We added the weeks_considered column to ensure we had enough data points to see a trend. You can see that in the output:

| team_id | weeks_considered | slope |
|---------|------------------|-------|
| 97003   | 5                | -5.70 |
| 77503   | 9                | -4.93 |
| 95535   | 5                | -4.23 |
| 92982   | 5                | -3.11 |
| …       | ...              | …     |

☎️ Contact the customers with the worst slope! Their usage of your product is plummeting. You could be letting great customers slip away!

Seeing the SQL

Once more, to help you visualize what PostgreSQL is doing, we've graphed the trends for each of these teams in the output above:

If you'd like to see an individual customer's behavior, here’s the query we used:

select
    extract('week' from DATE_TRUNC('week',time)) as week,
    (COUNT(name) / COUNT(distinct user_id))::integer as actions_per__user
from events
where team_id = 97003 -- or 77503, 95535, 92982
group by 1;

Try it yourself?

Run this template against our sample database that mirrors real startup data. You can also run this query on your own data, so long as you have a table that tracks events and includes a timestamp and a user/team ID.

Bonus Content

The inverse is also true: customers with strong positive slopes are growing 📈
Here's the aforementioned workaround if your data spans a changing calendar year involves casting from integer to text and back to integer. But it works!

with action_data AS (
 select
  (extract('year' from time)::text || extract('week' from time)::text)::integer as yearweek,
... -- rest of query continues as above

Photo by Sarah Brown on Unsplash

How to Measure Cohort Retention using BigQuery

Abdisalan — Fri, 28 Aug 2020 22:02:27 +0000

With some simple visitor data, you can tell how you're retaining your customers on a month by month basis. That's super valuable!

You can follow along at home. On the Google Cloud Platform, we'll use one of BigQuery's public datasets (details below). It's real visitor data from the Google Store in 2017.

If you want to use your own data, the queries in this guide can be converted to work with your favorite SQL dialect. All your data needs is a timestamp of when your users visit your site and a visitor ID to track them.

What This Analysis Will Produce
Cohort Retention Definition
Cohort Retention Framework
BigQuery Data
SQL
Part One: Find Our Cohorts
Part Two: Measure Activity After Cohort Month
Final: Divide the Number of Remaining Users by the Cohort Size
Conclusion
Future Use and Modifications

What This Analysis Will Produce

Here's a graph showing what percent of users came back to the Google Store after visiting for the first time. It's grouped by what month they first visited and shows what percent remain after subsequent months.

And here are the cohort sizes, for the more detail oriented folks.

Cohort Retention Definition

What's a cohort? Essentially a group of people.

How are we grouping our customers? I've decided to group them by the month that they visited the site.

How do we know if we retained a user? We can know if they visit the site again in a subsequent month. For example if they visited in June then again in July, they were retained. But the months are independent — They could visit in June, and not show up again until August. We wouldn't count July, but would count August.

Cohort Retention Framework

Group all our users into cohorts based on the month they first came to the store and count the total.
Count how many users came back each month, starting from their cohort month.
Calculate the percentage of cohort remaining after each month

BigQuery Data

Feel free to skip this section if you don't want to use the example data from BigQuery.

BigQuery has a large number of public datasets and Google Store Analytics from 2017 is one of them.

You'll need to make a Google Cloud Platform account, create a project, and add the public dataset to your project. Next, since we can't edit the public dataset, you'll need to copy the data from the public dataset to your project.

SQL

In BigQuery, I named my project "Cohort Analysis" and named my duplicate table "Google Store Analytics" so you'll see the prefix cohort-analysis-284422.Google_Store_Analytics often in this article. To follow along, replace this prefix with the name of your project and table in BigQuery.

For each part, I'll also be creating views to make the queries reusable and easier to work with!

Part One: Find Our Cohorts

First we find out the first time each customer visited the site this year and create a view so that we can reference this query later.

create view `cohort-analysis-284422.Google_Store_Analytics.cohort` as
select
    extract(date from timestamp_seconds(min(visitStartTime))) as cohort_month,
    fullVisitorId
from `cohort-analysis-284422.Google_Store_Analytics.ga_sessions_2017*`
group by fullVisitorId

Example Results:
cohort_month    fullVisitorId   
2017-05-12    0206596068278667912
2017-05-12    3310123534725675314
2017-04-25    3153380067864919818
2017-06-23    1216639212736323098

We also find out the size of each cohort by counting the number of unique ids that show up for the first time in a month.

create view `cohort-analysis-284422.Google_Store_Analytics.cohort_size` as
select
    extract(month from cohort_month) as cohort_month,
    count(1) as num_users
from `cohort-analysis-284422.Google_Store_Analytics.cohort`
group by cohort_month

Example Results:
cohort_month    num_users   
1             52613
2             49133
3             55346
4             52873

Part Two: Measure Activity After Cohort Month

Next, we find what months there's been activity after their cohort month. For example If their cohort was January and they came to the site on February, then it notes activity on month one — one month after their cohort month.

create view `cohort-analysis-284422.Google_Store_Analytics.user_activities` as
select date_diff(
    extract(date from timestamp_seconds(GA.visitStartTime)),
    C.cohort_month,
    MONTH
) as month_number,
GA.fullVisitorId
from `cohort-analysis-284422.Google_Store_Analytics.ga_sessions_2017*` GA
left join `cohort-analysis-284422.Google_Store_Analytics.cohort` C
on GA.fullVisitorId = C.fullVisitorId
group by 2, 1

Example Results:
month_number    fullVisitorId
0             7868243379843705173
0             5418231530547311489
0             4704421551407457903

Now we count how many users were retained in each month after their cohort month and name it retention_table.

create view `cohort-analysis-284422.Google_Store_Analytics.retention_table` as
select
    extract(MONTH from C.cohort_month) as cohort_month,
    A.month_number,
    count(1) as num_users
from `cohort-analysis-284422.Google_Store_Analytics.user_activities` A
left join `cohort-analysis-284422.Google_Store_Analytics.cohort` C ON A.fullVisitorId = C.fullVisitorId
group by 1, 2

Example Results:
cohort_month    month_number    num_users
1             0             52613
1             1             2198
1             2             908

Final: Divide the Number of Remaining Users by the Cohort Size

Finally we get the cohort percent retention after each month.

create view `cohort-analysis-284422.Google_Store_Analytics.final` as
select
  R.cohort_month,
  S.num_users as total_users,
  R.month_number,
  CAST(R.num_users as float64) * 100 / S.num_users as percentage
from `cohort-analysis-284422.Google_Store_Analytics.retention_table` R
left join `cohort-analysis-284422.Google_Store_Analytics.cohort_size` S ON R.cohort_month = S.cohort_month
where R.cohort_month IS NOT NULL
order by 1, 3

You should end up with a final result that looks like this.

With this data I also made this bar graph to show the drop off in users each month.

Conclusion

Again, figuring out how you're retaining your users is super valuable! You can see in this chart that the January cohort is consistently more retained than other cohorts. This could be for various reasons and knowing why could help your business capitalize on this fact.

Future Use and Modifications

Anyone who has used Google Analytics on their site knows that this cohort analysis is immediately available to them for free. But this manual method is much more flexible!

Imagine that you also want to find retention numbers for customers who have paid for a product? What about customers who have used your latest feature or were referred by a friend? There are tons of insights you can get from visitor data which makes this data super powerful for guiding your business.

Thank you for reading and I'll see you in the next article!

✌🏾