This article will help you:
An important metric of product performance is the number of new users each day, week, or month. New users for a given day are the users whose first Amplitude-recorded event occurred on that day. This article provides several examples for using Redshift to query this data; just replace the values in red with your own.
Count new users on a given day in Redshift
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(amplitude_id) FROM events123 WHERE first_event = 'True' AND DATE(event_time) = '2017-04-06';
This query returns the number of users who logged their first event (specified by
first_event = 'True' ) on March 1, 2015, and were therefore new users on that day.
Count new users in different time zones
Dates and times are in UTC (formatted
yyyy-mm-dd hh:mm:ss ), so if you are interested in getting new user counts for different time zones, then forgo the
DATE() function and offset the full timestamps by the appropriate differential instead.
For example, to find 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(amplitude_id) FROM events123 WHERE first_event = 'True' AND WHERE event_time >= '2015-03-01 08:00:00' AND event_time < '2015-03-02 08:00:00';
Count the users who triggered a specific event
If you are instead interested in users who triggered a certain type of event as their first event, you can easily modify the original query to do so. For example, if you only want users who triggered the
signedUp event, query on the
signedUp event table:
SELECT COUNT(amplitude_id) FROM app123.signedUp WHERE first_event = 'True' AND DATE(event_time) = '2017-04-06';
You can also query for users who logged events other than the events you specify. For example, if your product tracks passive events like push notifications, an active user might be best defined as a user who triggers some active event. So if the event you want to exclude is called
receivedPush , modify the query to:
SELECT COUNT(DISTINCT amplitude_id) from events123 WHERE event_type != 'ReceivedPush' AND DATE(event_time)='2017-04-06';
Get a list of new users
Just as with active users, it's often useful to get a list of the actual new user IDs, in addition to the count.
SELECT DISTINCT user_id FROM events123 WHERE first_event = 'True' AND DATE(event_time) = '2017-04-06';