This article will help you:
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;