Last modified July 14, 2015 by John Kearney

Redshift Sample Event Queries

This article describes some sample queries you can run on the raw event data imported from Swrve to Redshift.

Custom Properties with JSON_EXTRACT_PATH_TEXT

Use the json_extract_path_text command to extract custom attributes and payload data.

For example, to query information about your users’ timezones:

Example Result

Redshift table

 


Custom Views on Fields Containing JSON

Create a custom view to simplify accessing tables with JSON.

Custom view


Join Events and User State Tables

If you have imported user state data, use a JOIN command to quickly filter events based user state. The events table is joined to swrve_properties on swrve_properties.swrve_user_id = events.event_user. The other user property tables, like abtest_exposure, segment_membership, and so forth are joined on swrve_data_id.

User State


Need a hosted solution?
Most Swrve customers can self-host this pipeline; all you have to do is follow the steps in these tutorials. However, if you prefer a turn-key hosted solution, we do offer one as a professional service. There is an additional fee associated with this service. For more information, contact your Customer Success Manager at support@swrve.com.

Need help with Queries?
Swrve support can help you with basic setup and configuration of the pipeline described above. If you need help with your queries, contact our Data Services team at support@swrve.com. They will help you get the most out of your data with dedicated support, pre-built and custom reports, and dedicated data science hours.