For a better reading experience, the following is the reference to different objects on Acho:
πŸ”€: Variable | πŸ”˜: Button | πŸ“‹: Table

acho_studio_user_stickiness_dashboard
Acho Studio - User Stickiness Dashboard

What is User Stickiness?

User stickiness is a concept for evaluating the depth of user engagement. It represents how often users visit your website or use your product. It answers questions such as: β€œDo they come to your website every day/week/month? Are they willing to spend time on your product? Do they heavily rely on your product for a particular scenario?” In essence, measuring user stickiness is a useful way of predicting churn rate and even net promoter score since stickier users can become your loyal customers and promoters at times.

How to Measure User Stickiness?

DAU/MAU Ratio is a useful metric to measure user stickiness. It is calculated from two metrics: Daily Active Users (DAU) and Monthly Active Users (MAU). DAU is the unique number of active users in a day, whereas MAU is the unique number of active users in a month. By dividing DAU by MAU, we can obtain an overview of how often your users engage with your product.

acho_studio_dau_mau_ratio_formula
Acho Studio - DAU/MAU Ratio Formula

For example, if DAU is 100 and MAU is 400, then DAU/MAU Ratio is 100/400 = 25%. It means that a user spends 25% of a month to use your product on average.

You may wonder why we don't just use DAU and MAU to measure stickiness? DAU and MAU are absolute values, not relative values. In other words, the growth of DAU and MAU doesn't mean the growth of stickiness.

Take the following graph as an example. As you can see, DAU and MAU seem to grow over time but DAU/MAU Ratio remain stable. Although the number of active users increase, they still spend the same amount of time on your product.

acho_studio_dau_mau_vs_dau_mau_ratio
Acho Studio - DAU and MAU vs. DAU/MAU Ratio

Basically, DAU/MAU is around 10% to 20% on average. Few companies can achieve 50%. However, lower DAU/MAU Ratio doesn't definitely mean that your user engagement is bad since different types of products may have different usage frequencies. People usually use social media platforms more often than video conferencing software. It makes sense that Facebook should have a higher DAU/MAU than Zoom.

acho_studio_user_stickiness_metrics_different_time_frames
Acho Studio - User Stickiness Metrics (Different Time Frames)

As we can see in the above graph, we calculate three ratios using different time frames of metrics and the results are totally different (WAU is Weekly Active Users.) Thus, choosing an appropriate KPI to evaluate user stickiness is important for your business in the long-term.

How to Create a Real-Time Pipeline for DAU/MAU Ratio?

Here we use a SaaS company’s user behavior data as an example. Before doing any calculation, we first need to define who our active users are. In this case, if a user logs in to the website and applies at least an action, we will consider this user as an active user.

Here is what our data looks like. This dataset records every user's id and their login time.

acho_studio_user_behavior_data
Acho Studio - User Behavior Data

1. Transform timestamp variables

As you can see, πŸ”€ login_time is in an UNIX timestamp format. The first thing is to convert it into a human-readable format. Click the πŸ”˜ Formula button and type in the following functions. (Check out this article to know more information about timestamp transformation)

acho_studio_date_transforamtion
Acho Studio - Date Transformation

To calculate MAU and WAU, you also need a πŸ”€ month and πŸ”€ week variable. Click πŸ”˜ SQL Editor and put the following code. The πŸ”€ week variable will return week numbers. You can check more information about the FORMAT_DATE function in BigQuery documentation.

SELECT *,
  FORMAT_DATE("%V", date) AS week,
  FORMAT_DATE("%Y-%m", date) AS month
FROM user_actions;

Here you are.

acho_studio_date_transformation_results
Acho Studio - Date Transformation Results

2. Calculate DAU

Now, click πŸ”˜ Pivot Tables. Drag πŸ”€ month, πŸ”€ week and πŸ”€ date in Vertical Dimensions. Then, put πŸ”€ user_id in Values and specify the aggregation as Count Distinct. This action tells the system to compute the unique number of users by date. The πŸ”€ month and πŸ”€ week will be used for joining tables later.

acho_studio_pivot_table_for_dau_calculation
Acho Studio - Pivot Table for DAU Calculation

Then, rename the variable as πŸ”€ DAU. Here is the result.

acho_studio_results_for_dau_calculation
Acho Studio - Results for DAU Calculation

3. Create a new table for WAU

Follow the previous steps to create a new table called πŸ“‹ WAU.

acho_studio_table_for_wau_calculation
Acho Studio - Table for WAU Calculation

Click πŸ”˜ Pivot Tables. Place πŸ”€ week Β in Vertical Dimensions and Β πŸ”€ user_id in Values. By specifying the aggregation as Count Distinct, it will return the unique number of users by week.

acho_studio_pivot_table_for_wau_calculation
Acho Studio - Pivot Table for WAU Calculation

Finally, rename πŸ”€ count_distinct_of_user_id as πŸ”€ WAU.

acho_studio_results_for_wau_calculation
Acho Studio - Results for WAU Calculation

4. Create a new table for MAU

Similar to the WAU table, you can create a new table for MAU, but this time you just need to create a πŸ”€ month variable

acho_studio_table_for_mau_calculation
Acho Studio - Table for MAU Calculation

Then, apply the πŸ”˜ Pivot Tables function and drag πŸ”€ month in Vertical Dimensions and πŸ”€ user_id in Values. Choose Count Distinct to calculate the unique number of users by month.

acho_studio_pivot_table_for_mau_calculation
Acho Studio - Pivot Table for MAU Calculation

Finally, rename πŸ”€ count_distinct_of_user_id as πŸ”€ MAU.

acho_studio_results_for_mau_calculation
Acho Studio - Results for MAU Calculation

5. Join Tables

Now, let's combine these tables. Go back to the πŸ“‹ DAU table and click πŸ”˜ Join. Then, merge the πŸ“‹ DAU and πŸ“‹ WAU table based on the πŸ”€ week column.

acho_studio_combine_table_dau_and_table_wau
Acho Studio - Combine Table DAU and Table WAU

Next, join another table. Click the πŸ”˜ Join button again, and combine the πŸ“‹ DAU and πŸ“‹ MAU table based on the πŸ”€ month column.

acho_studio_combine_table_dau_and_table_mau
Acho Studio - Combine Table DAU and Table MAU

* Important note: the πŸ”˜ Join action cannot be applied in succession. Use a filler action(πŸ”˜ Sort, πŸ”˜ Move Column, etc) in between 2 πŸ”˜ Join actions.

6. Calculate Ratios

Now, click πŸ”˜ Formula to calculate user stickiness metrics. Here, we demonstrate how to calculate three kinds of ratios. In reality, you don't have to calculate these three metrics. You just need to choose one of them as KPI to evaluate user stickiness.

acho_studio_ratio_calculation
Acho Studio - Ratio Calculation

Here are the final results.

acho_studio_final_results
Acho Studio - Final Results

7. Export to Visual Panel and visualize the results

The last step is to produce a chart with your table. Acho offers a built-in tool called Visual Panel for this. You can also connect your Acho table to your favorite BI tools such as Tableau and Metabase.

acho_studio_user_stickiness_dashboard_2
Acho Studio - User Stickiness Dashboard

Finally, if you want to create a real-time dashboard to update numbers automatically, you can check out this article to set up Data Sync.