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 timestamp, user_id, and 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?

Run this template against our sample database that mirrors real startup data. See the connection credentials, then connect in PopSQL.

Pro Tips

• 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!