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, 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 highly accurate 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;