Last modified November 22, 2014 by Gordon Glauser

User DB Schema and Sample Queries

Tutorial objective: Get comfortable working with the data exposed by the Swrve User DBs.

This tutorial assumes you’ve already loaded some User DB data into a MySQL or Redshift database. The following are examples of useful queries you can run on the user database to get comfortable working with the User DB data.


Understanding the User DB Data Schema

The user tables contain the state of your users. Each row represents the state of a single user that is an accumulation of all of that user’s actions. User state is refreshed every 24 hours.

To see what you user state looks like, run the following query:

If you are using the Swrve Insights hosted solution, in the above SQL query, the table name is app_01234_swrve_properties instead of swrve_properties.

The results include common Swrve attributes like user, date joined, last_active, spend and many others. These attributes are the same ones that are available to you in the Audience Builder. To query custom properties and other parameters, you can query these other user tables:

  • swrve_properties
    • swrve_data_id – identifier used internally by Swrve for joining between tables.
    • swrve_user_id – identifier you sent to identify your user. There is a one-to-one correspondence between swrve_data_id and swrve_user_id.
    • date_joined – the date the user was first seen by Swrve.
    • last_active – the date the user was last seen by Swrve.
    • swrve_app_version – the latest version of the app used.
    • milliseconds_played – accumulated milliseconds of app usage.
    • spend – accumulated USD cents spent.
    • swrve_device_name – name of the device.
    • swrve_ios_token – user’s APNS push token.
    • swrve_gcm_token – user’s Google Cloud Messaging token.
    • swrve_sim_operator_code – operator code if the user has SIM connection.
    • swrve_sim_operator_ios_country_code – country code if the user has SIM connection.
    • swrve_timezone_name – user timezone name.
  • custom_properties
    • swrve_data_id – identifier used internally by Swrve.
    • One column for each custom property you’ve sent to Swrve.
  • events_fired
    • swrve_data_id – identifier used internally by Swrve for joining between tables.
    • One column for each custom event sent to Swrve. Each column displays the number of times that event has been sent by that user.
  • swrve_events_fired
    • swrve_data_id – identifier used internally by Swrve for joining between tables.
    • One column for each default event sent to Swrve. Each column displays the number of times that event has been sent by that user.
  • items_purchased
    • swrve_data_id – identifier used internally by Swrve for joining between tables.
    • One column for each item sent to Swrve. Each column displays the number of times that item has been sent by that user.
    • In order for an item to appear in this table, the item must also be added as a resource.
  • segment_membership
    • swrve_data_id – identifier used internally by Swrve for joining between tables.
    • One column for each segment you’ve created in the Swrve dashboard. A value of 1 indicates the user is in the segment, 0 indicates the user is not.
  • abtest_exposure
    • swrve_data_id – identifier used internally by Swrve for joining between tables.
    • One column for each resource A/B test you’ve launched in the Swrve dashboard. The number indicates the variant the user has been exposed to, a value of 0 represents the control.

If you are using the Swrve Insights hosted solution, the table names are app_01234_<table_name> instead of table names above.

Working With the swrve_properties Table

The first table to understand is the swrve_properties table. This table contains basic Swrve facts about the user. An example query of this table would be to find the highest amount spent by a user in your app. Other examples are listed below.

Finding the maximum user spend

Count of Google Android users by screen dimension

Distribution of non-payer French speakers by level

Working With the events_fired Table

When looking at tables other than the swrve_properties table, it is useful to join the table back to the swrve_properties table so that you can access the swrve_user_id (your identifier for the user), along with the data in that table.

How many times has each user fired each custom event?

Working With the items_purchased Table

What other items do users purchase in addition to item X?

Working With the segment_membership Table

How many users exposed to each variant of test X are in the payers segment?


Next Steps

  • Learn how to download data sets and import them into your database by following the User DB tutorials. For more information, see Automating User DB Imports.

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.