In addition to user properties, Amplitude also allows tracking of event properties which provide deeper data on user actions specific to the type of event that occurred.
For example, in a gambling game app when the user does a 'BET' event on a hand of cards, an event property called 'amount' can capture the amount of in-game currency they wagered. Or in a shopping app, when a user purchases an item, triggering a 'PURCHASE' event, an event property called 'item_name' can capture the name of the specific item that was purchased.
Amplitude stores these event properties in Redshift in their own individual columns for each event type. There is a limit of 50 event properties that can be pulled out into their own columns. All other event properties will be stored in a special JSON column called event_properties. To query for them, we use the same syntax type that we use for custom user properties (as described in User Properties) based on the Redshift json_extract_path_text() function. The values in red are what you will need to replace with your own.
NOTE: For most customers, the Help Center article on exporting Amplitude data to Redshift will be more useful.
Query Objective: Obtain the List of Items Bought and How Frequently That Item was Purchased
Taking the shopping app example from the previous paragraph, the following query finds the names of the items bought and the count of how many times that item was purchased over a period of time, ordered by the count:
SELECT e_item_name, count(*) AS count FROM app123.PURCHASE WHERE DATE(event_time) BETWEEN '2017-03-30' AND '2017-04-06' GROUP BY e_item_name ORDER BY count DESC;
Numerical Event Properties
If the event property you are interested in has numerical values instead of text, you can query for ranges of values.
Query Objective: Obtain the List of Items Bought and How Frequently that Item was Purchased
Taking the gambling game app example from the last section, we can query for the number of users who, when doing a 'BET' event, wagered between 100 and 500 credits:
SELECT count(DISTINCT amplitude_id) AS count FROM app123.BET WHERE DATE(event_time) BETWEEN '2017-03-30' AND '2017-04-06' AND NULLIF(e_credits,'')::int >= 100 AND NULLIF(e_credits, '')::int <= 500;