Data Schema

Overview


The DataMine UI allows for the easy creation of queries. By clicking on the table icon on the right hand side of the interface a list of available data tables will be shown. By clicking on triangle icon next to a table, the columns for each table will be revealed.

Table and Apps

There are four main types of column types which are:

  1. String - String

  2. Int - Int

  3. Boolean - Boolean

  4. Map - Map

Within DataMine, each table contains all the data for all the apps. To filter your data by app you can use the column app_id and the = function. For example:

SELECT * FROM campaign WHERE app_id = 200660 AND day = 20150730

Additionally you can use the IN function to select data from multiple apps at once.

All tables have standard attributes that the SDK collects automatically which includes:

  • User Unique SID
  • Opt Out
  • Location
  • App Version
  • App Bundle ID
  • Device OS
  • Device OS Version
  • Device Type (Phone, Tablet)
  • Device Hardware
  • Limited Ad Tracking (iOS only)
  • Device Manufacture
  • Device Carrier
  • Device Connection
  • Device Jailbroken
  • Screen Width
  • Screen Height
  • Screen Scale
  • Screen DPI
  • IDFV
  • IDFA
  • Android ID
  • Google Advertising ID
  • User’s Time Zone
  • Long and Lat (if Location Tracking is enabled)

Once you have finished your query you can easily download your data either as a CSV or Excel compatible version.

Note Excel compatible exports are limited to one million rows.

Tables


Depending on the message type, data gets stored to one of the following tables.

Standard TablesDescriptionAvailability

comm

Logs push related messages

Table created when push is implemented

campaign

Logs marketing campaign messages

Table created when campaigns are sent

device

Logs user device attributes and is automatically updated once every ~4 hours

Table created when a user first launches the app with the Upsight SDK

event

Logs event messages

Table created when application starts sending custom events

install

Logs user installs

Table created when a user first launches the app with the Upsight SDK

milestone

Logs user milestones

Table created when milestones are reached. Set up milestones under Application - Milestones

monetization

Logs IAP revenue

Table created when a purchase is made. Requires the Upsight monetization call to be implemented

session

Logs when a user began and ended a session

Table created when a user first launches the app with the Upsight SDK

If your application is defined as a Web application on the Dashboard, data will appear in separate tables prepended with web_, such as web_event.

Message Attributes


Each message comes with default attributes tracked by the Upsight SDK.

NameDescription

type

The name of the event. SDK generated events are prepended with “upsight” (e.g., upsight.session.start). Custom events are prepended with “pub” (e.g., pub.economy.spend.coin)

app_id

The 5-digit application ID assigned by Upsight. It is recommended to filter by app_id when querying.

sid

The identifier of the device which sent the message (assigned by Upsight upon installs, including reinstalls)

ts

The timestamp the message was sent in Unixtime, UTC

hour / day / month

The time the message was sent in YYYYMMddHH date format. These are the partitions which restrict how much data is read. It is recommended to apply a time filter in your innermost query. For example, WHERE day = 20170811

device_os / app_version / location_country_ip / etc.

Standard device data

user_attributes

Custom field containing user information when the message was sent

pub_data

Custom field containing information about the event

For a full list of attributes, see Table Column Reference.

Custom Events and Custom Attributes


An important feature of Datamine is pub_data and user_attributes. All tables have columns for pub_data and user_attributes. These columns have data saved in key value pairs. For example if you had a music app and you sent the song name in a key value pair with the key called song in a custom event called voted, you would use the following query to get the distinct song names that was voted for on a particular day.

SELECT DISTINCT pub_data[‘Song’] AS Song FROM event WHERE type = "pub.voted" AND day = 20150730

Note Values for pub_data and user_attributes are always saved as strings to they will need to be converted to ints or doubles before any mathematical operators can be applied to them.