In this tutorial, we will cover what active user analytics is, why it’s important, and a step-by-step example of how to build website performance analysis in SQL.
Active user analysis allows the entire business to monitor the usage of the company's product. This includes tracking signups from free PLG products and paying customer users, understanding if those users return, the number of weekly (or daily/monthly) repeat users, and the total number of users and system usage. This information comes directly from the product analytics tool.
Active user analysis is important across the entire business and the lifecycle of the user. Marketing teams track new user signups and trial signups to determine if their efforts are leading to prospects engaging with the product. Identifying new users who become repeat users is key for marketing to identify promising leads which can then be handed over to sales for greater engagement.
Sales teams use these repeat users to target their initial sales outreach and hopefully generate qualified leads. They can then track users as they start their trial (if appropriate) and when they finish the sale, check the new user signups to confirm product adoption.
Additionally, the account management team can follow enterprise account signups as they work to drive adoption and additional revenue. Checking for repeat enterprise use will be a major metric to track when measuring adoption. By tracking the enterprise use, an account team can determine if an account is decreasing its use and becoming a churn risk.
Finally, the product team can track all of these to understand how the product is being used and identify friction that may cause trouble with sales leads or adoption. In addition, understanding the trends in the number of users and usage can help forecast future capacity.
In order to analyze user activity, the following data is needed.
In this section, we will show how to build the active user analysis using SQL for weekly user metrics. We will assume the data comes from Heap, but you might be getting this data from Pendo, Amplitude, or other product measurement sources. We will need to clean this dataset, calculate the information weekly for multiple metrics and join the metric data back together.
The key metrics here are new free PLG users and users who come back to use the product multiple times. In particular, we are interested in users who come back at least a week after they first joined (trial engaged customers or product qualified leads). We can generate these from the daily active users data in Heap.
To calculate the new users each week (and determine if they come back a week later), we first aggregate each user’s weekly activity. In addition, as we are looking at truly new users, not new users on an enterprise account, we will first exclude those users with an organization_type of ‘ENTERPRISE’.
Similarly, if we only want to know how many users are repeat users each week, we can build off of the CTE weekly repeat by aggregating the number of users by repeat_week.
This same analysis can be performed for customers by changing the organization_type to select enterprise by changing the first WHERE clause to
In addition to the breakdowns by free and paying users, it is useful to track the total number of users and hours used each week. These can also be calculated from the Heap active daily users table.
All of these blocks can be put together and joined on the week column to create the full usage metrics by week.
Active user analysis is key for executives from marketing, sales, account management, and product management to understand how the business is functioning and how users are using the product. This information is necessary to ensure that the sales pipeline remains full, new customers continue to be converted and existing customers continue to renew.