User Engagement and Activity Histogram Analysis - Issue 34

User engagement and frequency of usage analysis using SQL

User engagement is one of the best indicators that people love the product they use. You can improve user engagement if you can define and scale it. User engagement can mean any interaction with your product or it could also be a list of specific actions (or that one action). 

MAU and DAU, or DAU/MAU conversion as the ratio of daily active users over monthly active users, is a common metric measuring user engagement. It tells you “how deep” users engage with your product. It is tailored for heavy daily usage, however, and doesn’t provide insight into how often users engage. Additionally, your product may also be intended for weekly/monthly use instead of daily. Therefore, in addition to DAU/MAU conversion, I always look into the frequency of engagement or Activity Histogram. Below I’ll demonstrate a walkthrough of how to approach and conduct this analysis using SQL. 

User Activity Histogram shows the total number of days that users were active in a month (or 28 days). You can make it general and reflect top-level activity like views, opens, or logins. It can also represent a specific action to measure for your product. 

The purpose of this histogram is to illustrate: 

1. Does your product have an engaged user segment that comes back every day?

2. How often do users come back to use your product?

3. Does it have user segments? How many users are heavily engaged, and how many are just “exploring”?

This histogram is usually an outlet to many additional analyses on user activity, retention, or tenure - a timeline of behavior change. You can use it to locate your power users, and then keep this cohort for email and other marketing campaigns. 

Here is a sample of SQL that could be used for building a histogram: 

 SELECT active.num_days_active

, COUNT(DISTINCT active.user_id) AS num_users -- get how many users have a number of active days

FROM (

  SELECT a.user_id, COUNT(DISTINCT a.created_at) AS num_days_active

  FROM activity a

  GROUP BY a.user_id) AS active

LEFT JOIN activity a2 ON a2.user_id = active.user_id

WHERE a2.created_at >= CURRENT_DATE - 28

GROUP BY active.num_days_active

ORDER BY active.num_days_active ASC;

Output example: 

Now, let’s visualize the histogram using the power of BI charts and go through different variations of engagement types to understand what they mean.

Daily engagement frequency

This is one of the most common user behavior patterns skewed to the left. It usually says that your active users’ behavior is very similar to new users, and the majority of your users use the product only a few times per month. The gradual user pattern is a good sign that indicates an opportunity to improve Day+7 and Day 28 retention. If this histogram is applied for new users, it would mean that you might have to improve your onboarding flow. 

Weekly engagement frequency 

This chart shows that the majority of users use your app/product on a weekly basis. Day+1 retention is strong, and there is an opportunity to improve day+28 retention. If this histogram is applied for new users, it would show that your onboarding is strong and there is an opportunity to convert new users into active ones. 

Monthly engagement frequency

This is a good variation from all the charts above illustrating the group of users who use your product daily. Many users return frequently, so your power users category is large and gives you a green light for experimentation, ads, upsells, etc... 

Red flag 

This is not good. What you’re seeing here is that the majority of your users try the product only a few times and leave. Infrequent engagement is common and can be expected for some types of products that are meant to be used only a few times per month. However, if your features are expected to be used daily, revenue scaling can be more challenging, and you have to think more about your value proposition and product positioning. 

Power Users

See that 5% spike? That’s your power users. They use your product every day during the month. If you see such a pattern, you should focus on this segment. Learn it well and try to multiply. Look at their tenure, attributes, and behavior. All of these factors should give you some indications as to what currently works and how to utilize it even more. These users are more likely to engage with new features, click on upsells, and less likely to churn or unsubscribe. Use this knowledge wisely!

Another way to define and locate your power users is described in another deep-dive Power Users Analysis.

This is not the total conclusion of histogram analysis (there never is). Your next step will be to check the activity over time. Strong products are able to retain users better, and you should see the shift of activity to the right side of the chart with every month or year. If you don’t see such a shift to the right, you might have to check with your leadership about how to best address a stronger approach towards scaling and improving retention.

Historically, almost every product that has been successful has demonstrated a strong consistency in user retention. Retention is the output of engagement, activity, and resurrection (according to Reforge), and this analysis is a good starting point to learn your user behavior pattern. That’s how you influence or change the direction of a company strategy with insights like these.

Good luck, and see you next Wednesday!