The Frequency Of User Activity: SQL and Analysis - Issue 127
How to get the frequency of usage and create a user activity histogram for user engagement analysis in SQL.
Today’s topic is a special one. The frequency of user engagement is my favorite analysis of all. The approach and analysis described below changed my understanding of user behavior and product usage expectation a lot. It was one of the reasons why I pivoted my career heavily toward product analytics. The analysis itself is simple, and yet so crucial and impactful to the business and product.
High user engagement is a strong indicator that people love the product. Top engagement KPIs usually include DAU, MAU, and Retention. They surface a high level of user activity but don’t answer the question of how often users engage with your product.
DAU/MAU and WAU/MAU Ratios take it one step further. This is the ratio of daily (or weekly) active users over monthly active users that tells you what % of users use your product every day or every week of the month. (A quick benchmark note, DAU/MAU ratio at 25%-28% is average for B2C, and 15% is average for B2B and SaaS).
That being said, your product may be intended for episodic use instead of daily (Airbnb, Lyft, Tinder, Dropbox, LinkedIn) so your DAU/MAU ratio could be small regardless of high volume usage, which isn’t that helpful to illustrate your true user engagement. You are most likely to have a few small groups of power users driving daily usage while other product personas prevail in bi-weekly or monthly usage.
So how can you measure how many users use daily, weekly, and monthly? Does your product have an engaged user segment (or segments) that comes back every day? How big is it? What are your user segments? How many users are heavily engaged, and how many are just “exploring”? How can you locate groups of power users?
To answer these questions, you’ll need to create a user activity distribution or histogram. Below, I’ll demonstrate a walkthrough of how to approach and conduct this analysis using SQL.
Keep reading with a 7-day free trial
Subscribe to Data Analysis Journal to keep reading this post and get 7 days of free access to the full post archives.