Calculating Daily Active Users (and digging deeper)
Daily Active Users is the critical metric for many startups.
Yet "different companies have almost unlimited definitions for what 'active' means. Some...don't even define what that activity is. Be clear on how you define 'active.'" -- a16z, in an overview of startup metrics.
As promised, we'll share a simple SQL query for Daily Active Users (DAU). We push you, though, to go beyond the basic definition.
SQL you can copy / paste
Our sample database is for a B2B SaaS company that makes "whiteboarding software for remote teams". Editing a canvas is the baseline indicator of activity, so to calculate DAU:
select date_trunc('day', time), count(distinct user_id) from events where name = 'Edit Canvas' AND time > now() - interval '4 weeks' group by 1 order by 1;
If you have an
events table with a
event_name you can use the query above with little modification.
Beyond the basic definition
Do your active users have any likelihood of becoming customers? Learn how to create Lead Scores in SQL. Lead scoring focuses your sales team on your best “Product Qualified leads”. Martin Casado (also of a16z) champions this tactic in your go to market strategy.
“Active” is a very binary classification. Most users aren’t in love with your product one day and out of love the next. Along the way they signal their engagement with your product. We have templates to walk you through how customers have used your product and which are at risk of churning.
Reflect on what is actually valuable to customer to define "active". Our friends at Braze have a useful framework.
Try it yourself?
- Use query variables to quickly go from day to week to month (i.e. daily to weekly to monthly active users)
- Need to exclude partial / incomplete weeks from your output? A quick tutorial.
- Create a folder of “Base Queries” to codify your definition of “Active” (and other core metrics)
- PopSQL helps track changes to versions of these base queries over time.
- PopSQL <> GitHub integration coming soon!
Spread the wordTweet