Last modified September 14, 2018 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 app version on the device, taken from the app itself.
    • milliseconds_played – accumulated milliseconds of app usage.
    • spend – accumulated USD cents spent.
    • swrve_android_device_xdpi – the exact physical pixels per inch of the screen in the X dimension.
    • swrve_android_device_ydpi – the exact physical pixels per inch of the screen in the Y dimension.
    • swrve_app_store – the app store for the device. For example, Apple, Google, or Amazon.
    • swrve_conversation_version – if relevant, the version of Conversations campaigns. Swrve uses this to correctly target campaigns.
    • swrve_device_dpi – the dots per inch (DPI) in pixels of the device screen.
    • swrve_device_height – the height of the device screen in pixels.
    • swrve_device_name – the name of the device as reported by the OS.
    • swrve_device_region – the 2-letter ISO country code of the selected region of the device.
    • swrve_device_width – the width of the device screen in pixels.
    • swrve_gcm_token – user’s Google Cloud Messaging push notification token.
    • swrve_install_date – the date the user was first seen by Swrve in string format (YYYYMMDD).
    • swrve_ios_min_version – the deployment target for the app build, which represents the minimum version the user’s device must be running to install your app.
    • swrve_ios_token – user’s Apple Push Notification Service (APNS) token.
    • swrve_language – the language of the device.
    • swrve_os – the operating system of the device.
    • swrve_os_version – the operating system version.
    • swrve_permission_ios_push_bg_refresh – current status of iOS background refresh permission.
    • swrve_permission_ios_push_notifications – current status of iOS push notification permission.
    • swrve_permission_notifications_enabled – current status of Android push notification permission.
    • swrve_permission_notifications_importance – the importance level of notifications (Android only).
    • swrve_sdk_flavour – the flavour of the Swrve Android SDK library (for example, core, Google, Firebase, Amazon).
    • swrve_sdk_version – the Swrve SDK version.
    • swrve_sim_operator_code – the unique number assigned to every mobile operator in all countries of the world, if applicable.
    • swrve_sim_operator_iso_country_code – the 2-letter ISO country code of the mobile operator, if applicable.
    • swrve_sim_operator_name – the mobile operator of the registered SIM card, if applicable.
    • swrve_support_rich_attachment – boolean value for whether the app build supports rich push notification attachments.
    • swrve_support_rich_buttons – boolean value for whether the app build supports rich push notification buttons.
    • swrve_support_rich_gif – boolean value for whether the app build supports rich push notification gifs
    • swrve_timezone_name – user timezone name.
    • swrve_utc_offset_seconds – the device’s time offset from UTC, in seconds.
  • 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.