Run revenue analysis queries in Redshift

If your product tracks revenue-generating events through Amplitude (such as in-app purchases), you can query for users and actions based on those events in Redshift. In the example queries below, just replace the values in red with your own. 

NOTE: This article assumes you have instrumented revenue verification for your project. Verified revenue events are stored in Amplitude's Redshift database with the verified_revenue event type, and the actual monetary amount for that purchase is stored in the revenue column.

Get 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, along with 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';

Get a list of top-paying users

Next, let's generate a list of users who are highly engaged and are the highest spenders on in-app purchases. The following query returns the user IDs of paying users, along with the total amount they've spent over a specified time period, in descending order: 

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;