New user analysis in Redshift

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';