Redshift: Revenue Analysis

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

If your product tracks revenue-generating events through Amplitude, such as in-app purchases, then you can query for users and actions based on these revenue-generating events in Redshift. The values in red are what you will need to 

Table of Contents

• Query Objective: Obtain the Number of Paying Users and Total Revenue
• Query Objective: Obtain a List of Top Paying Users

Note: Amplitude offers revenue tracking by verifying purchases with Apple iTunes, Google Play, and Amazon. This section assumes that your project has instrumented revenue verification. Verified revenue events are stored in Amplitude's Redshift database with event_type 'verified_revenue' and the actual monetary amount for that purchase is stored in the revenue column.

Query Objective: Obtain the Number of Paying Users and Total Revenue

A very useful summary query is to find the number of distinct users who spent money on purchases over a period of time and the total amount of money they spent:

SELECT count(DISTINCT amplitude_id), sum(revenue) 
FROM app123.verified_revenue
WHERE revenue > 0 
AND  DATE(event_time) BETWEEN '2017-03-30' AND '2017-04-06';

Query Objective: Obtain a List of Top Paying Users

Next, we can obtain a list of our product's so-called “whales” (e.g. users who are highly engaged and are the highest spenders on in-app purchases). The following query returns the user_ids of paying users and the total amount they have purchased over a specified time period, in descending order (highest paying users first): 

SELECT user_id, sum(revenue) as totalSpent 
FROM events123 
WHERE DATE(event_time) BETWEEN '2017-03-30' AND '2017-04-06'
AND revenue IS NOT NULL 
GROUP BY user_id 
ORDER BY totalSpent DESC;