As of 1/17/17, we now refer to "apps" as "projects."
The number of active users that a project has over a given period of time is one of the most basic and important metrics in measuring a project's level of user engagement. The values in red are what you will need to replace with your own.
Table of Contents
Query Objective: Count the Active Users on a Given Day
This metric counts the number of distinct users who performed at least one tracked event during the specified time period. A basic example of an active user count query is:
SELECT COUNT(DISTINCT amplitude_id) FROM events123 WHERE DATE(event_time)='2017-04-06';
Explanation
This query returns the number of users who logged at least one event on March 1, 2015. The red text of the query above should be adjusted to your specific case.
amplitude_id vs. device_id vs. user_id
Notice amplitude_id is used in the query above; this is the most accurate field to identify unique users as it combines information from device_id and user_id. Still, results based on either user_id or amplitude_id will usually be similar, so you can use either one in most cases.
Further, in certain situations (see below) device_id and user_id are more useful because they contain information usable outside of Amplitude -- e.g. user_id can be used for contacting users by email (as user_id's are often user's email addresses) and device_id can be used for push notifications. For more discussion of ID types and to understand how we count unique users, see our documentation.
Modifications
Time Zones
Dates and times are in UTC (formatted yyyy-mm-dd hh:mm:ss), so if you are interested in getting active user counts for different time zones, then forgo the DATE() function and offset the full timestamps by the appropriate differential. For example, to obtain the number of daily active users in the 24-hour period corresponding to March 1st Pacific Time, modify the event_time part of the query above to:
SELECT COUNT(DISTINCT amplitude_id) from events123
WHERE event_time >= '2015-03-01 08:00:00'
AND event_time < '2015-03-02 08:00:00';
Users Who Did Specific Events
The basic query above counts users who did any event as active users. If you are instead interested in users who did (or did not do) certain event types, then you can easily modify the query to do so. For example, if you only want users who did the 'sentMessage' event, then just modify the WHERE part of the query to:
Similarly, you can query for users who logged events other than certain events. For example, if your project tracks passive events such as push notifications, then an active user might be best defined as a user who does some active action. So, if the event you want to exclude is called 'receivedPush', then modify the query to:
SELECT COUNT(DISTINCT amplitude_id) from events123 WHERE event_type = 'signedUp' AND DATE(event_time)='2017-04-06';
Obtaining a List of Users
If you want to see who the set of active users are, rather than simply how many, then you can obtain the list of User IDs (which, depending on your project, may be a list of user email addresses, login names, etc). The query is the same except for the beginning:
SELECT DISTINCT user_id FROM events123 WHERE DATE(event_time) = '2017-04-06';
Note that we use user_id instead of amplitude_id because user_id is the identifier that your project recognizes (e.g. user email addresses, login names, etc) while amplitude_id is Amplitude's internal ID for users, which is not meaningful outside of Amplitude use.
Saving Output to a File
This modification above returns a table with one User ID per row, so if your project has thousands (or more) users per day, this can be a very long table. Note: It is often more useful to save the results of the query in a file instead of just viewing it the Redshift terminal. To do this, simply type the following command in the Redshift prompt:
\o your_file_name.csv
All query results for the remainder of your Redshift session will be written to the file “your_file_name.csv” on your local machine. To stop writing queries to the file, quit your session with:
\q
A variety of SQL UI tools exist where you can save tables generated from queries to Excel directly. A couple of these programs are SQL Workbench/J and Navicat.