Redshift: New Users

As of 1/17/17, we now refer to "apps" as "projects."

IMPORTANT NOTE: The new user queries below are only applicable if you have not performed any backfills into your project.

Another fundamental and important metric of product performance is the number of new users (per day, week, month, etc.). New users for a given day are the users whose first Amplitude-recorded event occurred on that day. The values in red are what you will need to replace with your own.

Table of Contents

• Query Objective: Count the New Users on a Given Day
• Explanation
• Modifications
• Time Zones
• Number of Users Who Did a Specific Event
• Obtaining a List of New Users

Query Objective: Count the New 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(amplitude_id)
FROM events123
WHERE first_event = 'True'
AND DATE(event_time) = '2017-04-06';

Explanation

The query above returns the number of users who logged their first event (specified by first_event = 'True') and hence were new users on March 1, 2015. The red text of the query above should be adjusted to your specific case.

Modifications

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.

For example, for 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';

Number of Users Who Did a Specific Event

The basic query above counts users who did any event as their first event. If you are instead interested in users who did a certain event type, then you can easily modify the query to do so. For example, if you only want users who did the 'signedUp' event, then just query on the 'signedUp' event table:

SELECT COUNT(amplitude_id)
FROM app123.signedUp
WHERE first_event = 'True'
AND DATE(event_time) = '2017-04-06';

Similarly, you can query for users who logged events other than certain events. For example, if your product 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 != 'ReceivedPush'
AND DATE(event_time)='2017-04-06';

Obtaining a List of New Users

Just as with active users, it is often useful to obtain 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';