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:

SELECT * FROM swrve_properties LIMIT 100;

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_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_fcm_token – user’s Firebase 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. (Deprecated in iOS 16+)
  • swrve_sim_operator_iso_country_code – the 2-letter ISO country code of the mobile operator, if applicable. (Deprecated in iOS 16+)
  • swrve_sim_operator_name – the mobile operator of the registered SIM card, if applicable. (Deprecated in iOS 16+)
  • 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.

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

SELECT CAST(MAX(spend) AS FLOAT) / 100 FROM swrve_properties;

Count of Google Android users by screen dimension

SELECT swrve_device_width,
       swrve_device_height,
       COUNT(1) AS usercount
FROM swrve_properties
WHERE swrve_app_store = 'google'
GROUP BY swrve_device_width, swrve_device_height
ORDER BY COUNT(1) DESC;

Distribution of non-payer French speakers by level

SELECT level,
       COUNT(1) AS usercount
FROM custom_properties
INNER JOIN swrve_properties
 ON swrve_properties.swrve_data_id=custom_properties.swrve_data_id
WHERE swrve_language LIKE 'fr%'
 AND spend = 0
GROUP BY level
ORDER BY level ASC

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?

SELECT swrve_user_id,
       events_fired.*
FROM events_fired
INNER JOIN swrve_properties
  ON swrve_properties.swrve_data_id = events_fired.swrve_data_id
LIMIT 100;

Working with the items_purchased table

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

SELECT swrve_user_id,
       items_purchased.*
FROM items_purchased
INNER JOIN swrve_properties
  ON swrve_properties.swrve_data_id = items_purchased.swrve_data_id
WHERE items_purchased.<ITEM_X> > 0
LIMIT 100;

Working with the segment_membership table

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

SELECT abtest_exposure.<TEST-X>,
       COUNT(1) AS usercount
FROM abtest_exposure
INNER JOIN segment_membership
 ON segment_membership.swrve_data_id = abtest_exposure.swrve_data_id
WHERE abtest_exposure.<TEST-X> IS NOT NULL
 AND segment_membership.payers=TRUE
GROUP BY abtest_exposure.<TEST-X>
ORDER BY abtest_exposure.<TEST-X> ASC

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.