1.1 E-commerce Analytics ;Business Metrics : MAU
In the world of digital products and services, it’s common to track the number of monthly active users (MAUs) as a key metric of success. In this blog, we’ll look at how you can use SQL queries to calculate MAUs for your product or service.
MAU is an important KPI (Key performance Indicator) which is used to assess how a particular product or feature is performing. What MAU does is it measure how many unique users perform some kind of defined activity on a product (like app or website) . As the name suggest, it measures the number of ACTIVE users in one month (or 30 day cycle)
Now, what does ACTIVE mean? ACTIVE can be defined in several ways. It depends on how the company defines the term active for their product.
First, let’s define what we mean by “monthly active users.” Essentially, a monthly active user is someone who engages with your product or service at least once during a given month. This could mean making a purchase, visiting a website, logging into an app, or any other action that indicates engagement with your product.
To calculate MAUs using SQL, you’ll need a table that tracks user activity over time. This table should include a column that records the date of each user action, as well as a unique identifier for each user (such as a user ID or email address).
With this table in place, you can use the following SQL query to calculate the number of monthly active users:
SELECT COUNT(DISTINCT user_id)
FROM activity_log
WHERE action_date BETWEEN ‘2022-01-01’ AND ‘2022-01-31’;
This query counts the number of unique user IDs in the activity_log table for the month of January 2022 (assuming the action_date column is in a date format). The DISTINCT keyword ensures that each user is only counted once, even if they took multiple actions during the month.
You can adjust the date range in the WHERE clause to calculate MAUs for any other month or time period. For example, to calculate MAUs for the entire year of 2022, This then essentially becomes Yearly Active users But for the purpose of business, such a case is generally referred to as Yearly MAU. you could use the following query:
SELECT COUNT(DISTINCT user_id)
FROM activity_log
WHERE action_date BETWEEN ‘2022-01-01’ AND ‘2022-12-31’;
You can also use additional WHERE clauses to filter the data further. For example, suppose you only want to count users who made a purchase during the month. You could modify the query like this:
SELECT COUNT(DISTINCT user_id)
FROM activity_log
WHERE action_date BETWEEN ‘2022-01-01’ AND ‘2022-01-31’
AND action = ‘purchase’;
This query only counts users who made at least one purchase during January 2022.
Overall, SQL is a powerful tool for calculating monthly active users and other key metrics for your product or service. By tracking user activity and using the techniques outlined above, you can gain valuable insights into the performance and growth of your business