Now more than ever, you have to hold onto every customer you can. Customers don’t love your product one minute, then fall out of love with it the next. Normally there’s a declining trend in usage of your product over time. a single proactive outreach can reverse a downward trend. 🙌 The good news: efficiently finding customers with declining usage. 🤦♀️ The challenge: 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 help you rapidly sift through the noise. I explore these functions further in our statistical functions template on Linear Regression in SQL (full disclosure: I am an avid SQL writer at PopSQL , a modern SQL editor built for teams). Below we’ll use just the function: regr_slope() | 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. The slope of that light blue line is the output of the function: regr_slope() SQL you can copy / paste Here’s the query (I’ll break it down below): action_data ( ( ) , team_id, ( ( ) / ( user_id)):: actions_per_user , ) team_id, ( ) weeks_considered, ( (actions_per_user, ):: , ) slope action_data ( ) >= ; -- DATA PREP with AS select extract 'week' from time as week -- quick hack to turn week into a numeric so we can use regr_slope() function -- we're looking at team usage count name count distinct numeric as -- 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 count week as round regr_slope week numeric 2 as from 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 is just data prep. action_data The function turns our timestamp into a numeric, as doesn’t accept timestamps as parameters. This hack works great (workaround at the bottom of the post). extract() regr_slope() except at the change of calendar years For the function, a week goes from Monday to Sunday. Avoid partial weeks by starting your date range with a Monday and ending on a Sunday 👍 extract() The output of the CTE looks like this: action_data | week | team per_user | |------|---------|------------------| | 12 | 93336 | 28 | | 13 | 93336 | 6 | | 14 | 93336 | 10 | | ... | ... | ... | | 12 | 92982 | 26 | | 13 | 92982 | 1 | | 14 | 92982 | 2 | | ... | ... | ... | _id | actions_ Finding slope of all customer usage trendlines In the second part of the query, the creates a trendline between and for each team, then returns the slope of that trendline. Again, visualized: regr_slope() actions_per_user week 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 column to ensure we had enough data points to see a trend. weeks_considered You can see that in the output: | team considered | slope | |---------|------------------|-------| | 97003 | 5 | -5.70 | | 77503 | 9 | -4.93 | | 95535 | 5 | -4.23 | | 92982 | 5 | -3.11 | | … | ... | … | _id | weeks_ ☎️ 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, I've graphed the trends from the output above for each of these teams: If you'd like to see an individual customer's behavior, here’s the query I used: ( DATE_TRUNC( , )) , ( ( ) / ( user_id)):: actions_per__user team_id = ; select extract 'week' from 'week' time as week COUNT name COUNT distinct integer as from events where 97003 -- or 77503, 95535, 92982 group by 1 Try it yourself? Run this template against a 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. sample database 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. It involves casting from integer to text and back to integer. But it works! action_data ( ( ( ):: || ( ):: ):: , ... with 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 (with some emoji game by the author). Previously published at https://popsql.com/sql-templates/support/finding-customers-at-risk-of-churning
