DataVault Schema and Tables

Tenjin's DataVault schema lies at the heart of its special sauce. All information from various places is nicely joined together through ids. With information organized in this way, DataVault makes it easy to build models and analyses in a deterministic way.

Normalized Schema

Above is the schema for DataVault.

Table description


  • events
    • device level data that comes from Tenjin SDK or 3rd party attribution provider
  • campaigns
    • campaigns that users are attributed to, or campaigns from ad-networks API
  • campaign_buckets
    • list of campaign buckets
  • ad_networks
    • list of ad networks
  • apps
    • list of apps
  • daily_spend
    • includes pre-install metrics(such as imps, clicks, installs, and spend) by campaign and date. “spend” is spend amount converted to USD, and “original_spend” is spend amount in “original_currency”
  • daily_country_spend
    • Includes pre-install metrics(such as imps, clicks, installs, and spend) by date, campaign, and country. “spend” is spend amount converted to USD, and “original_spend” is spend amount in “original_currency”
    • Only for ad-networks that have spend by country. Some ad-networks don’t have spend by country breakdown. So daily_country_spend contains partial spend of daily_spend
  • daily_behavior
    • Pre-aggregated view from events table. It includes non-cohort metrics(such as dau, arpdau) by date, campaign, country, and site
  • cohort_behavior
    • Pre-aggregated view from events table. It includes cohort metrics(such as ltv, retained users) by date, campaign, country, and site
    • “xday” is day of user's lifetime (relative to acquisition timestamp, starting with 0). So “revenue” on xday = 1 means revenue generated on day 1 after the acquisition
  • publisher_apps
    • publisher campaigns that we get from ad-network API
  • daily_ad_revenue
    • includes ad revenue data by publisher campaigns, date, and country
  • ad_engagements
    • includes click or impression data for each device. It only has data for non-self attributing ad-networks. We don’t store click or impression data for Google or Facebook.
  • campaigns_targeting_tags
    • includes targeting tag information for each campaign.
  • targeting_tags
    • includes targeting tag information
  • reporting_metrics
    • includes all non-cohorted metrics that are synced with dashboard data
  • reporting_cohort_metrics
    • includes all cohorted metrics that are synced with dashboard data

Table description detail

Param Description Example value
created_at timestamp when the event was created 2018-07-01 00:00:00
advertising_id IDFA for iOS or Google advertising ID for Android. Lower case, without hyphen. 29eeb1610fe74997b6d53f02e9711f8c
bundle_id app's bundle ID com.tenjin.wordfinder
platform app's platform ios or anroid
os_version device's os_version 8.0
app_version app version 1.0.3
limit_ad_tracking user's limit ad tracking (0,1) 1
app_id Tenjin's internal app id. It can be joined with id in apps table ee270433-b7da-4e0f-9b03-8dd25d89da49
event_type either event or purchase event
event when event_type = event, then this value is either open, or a custom event name. open
source_campaign_id Tenjin's internal campaign id for the campaign user is attributed to. It can be joined with id in campaigns table 18a0c1d6-02a2-4188-bbc0-0fe38dbcc37e
acquired_at timestamp when the user was acquired 2018-07-01 00:00:00
price raw purchase price for the purchase event 1.99
quantity purchase quantity for the purchase event 2
product_id product id for the purchase event com.tenjin.wordfinder.package50
currency currency code for the purchase event USD
country country code US
total_revenue gross revenue for the purchase event in USD cents 350
revenue net revenue for the purchase event in USD cents 245
site_id campaign's publisher ID if the campaign is paid campaign Instagram
developer_device_id IDFV for iOS. Empty for Android. Lower case, without hyphen. b6bc48fe0ce949e4b229ce6c55663fca
purchase_state 0 => Unknowable - this happens when there is no receipt, 1 => Unverified - this happens when the receipt passed didn't get verified properly, 2 => Fraudulent - this happens when the receipt passed is fraudulent, 3 => Verified - this happens when the receipt passed is OK, 4 => Sandbox - this happens when you're testing a receipt validation 3
value Custom event value 1
uuid unique event ID a3e3e535-a32c-4887-9c91-3160c91b3876
device device type iPad2,5
creative_name creative name test_creative
ip_address user's IP address 71.165.85.114
source_uuid UUID for the click or impression event if the campaign was paid campaign. It can be joined with uuid in ad_engagements table 0df8ccda-372b-4e9e-8fdc-dfa0eb0c333e
Param Description Example value
id unique ID for the campaign 3b70e477-c151-4167-9174-cca6ffa2c7bc
name campaign name test @bYkiNAbrAOCpzETWviMILo
ad_network_id id for the ad network, campaign is assigned to. It can be joined with id in ad_networks table 3
app_id Tenjin's internal app id. It can be joined with id in apps table ee270433-b7da-4e0f-9b03-8dd25d89da49
remote_campaign_id campaign_id ad-network uses. We get this through ad-network reporting API. 6026825090762
campaign_bucket_id campaign bucket id to bucket campaigns. This can be joined with id in campaign_buckets table fbbc74bd-8a73-472a-86f7-5e2a4642a201
updated_at timestamp when the record is updated 2018-07-01 00:00:00
Param Description Example value
id unique ID for the campaign bucket 3b70e477-c151-4167-9174-cca6ffa2c7bc
name campaign bucket name test @bYkiNAbrAOCpzETWviMILo
ad_network_id id for the ad network campaign is attributed to. It can be joined with id in ad_networks table 5
app_id Tenjin's internal app id. It can be joined with id in apps table ee270433-b7da-4e0f-9b03-8dd25d89da49
updated_at timestamp when the record is updated 2018-07-01 00:00:00
Param Description Example value
id unique ID for the ad_network 3
name ad network name Facebook
updated_at timestamp when the record is updated 2018-07-01 00:00:00
Param Description Example value
id unique ID for the app ee270433-b7da-4e0f-9b03-8dd25d89da49
store_id app's store ID 887212194
bundle_id app's bundle ID com.tenjin.wordfinder
updated_at timestamp when the record is updated 2018-07-01 00:00:00
Param Description Example value
id unique ID for the spend data 24d016ca-d60e-4f01-b705-97299b3cf341
date date when the spend happens 2018-07-01
campaign_id ID for the campaign. This can be joined with id in the campaigns table b1d67331-204b-4ee3-9cac-7c0f0f33b5bf
spend ad spend amount in USD cents. 400
installs number of installs reported 8
clicks number of clicks reported 10
impressions number of impressions reported 126
original_spend ad spend amount in original currency USD
original_currency original currency 4
updated_at timestamp when the record is updated 2018-07-01 00:00:00
Param Description Example value
id unique ID for the spend data 24d016ca-d60e-4f01-b705-97299b3cf341
date date when the spend happens 2018-07-01
country country where the spend happens US
campaign_id ID for the campaign. This can be joined with id in the campaigns table b1d67331-204b-4ee3-9cac-7c0f0f33b5bf
spend ad spend amount in USD cents. 400
installs number of installs reported 8
clicks number of clicks reported 10
impressions number of impressions reported 126
original_spend ad spend amount in original currency USD
original_currency original currency 4
updated_at timestamp when the record is updated 2018-07-01 00:00:00
Param Description Example value
date date for the metrics 2018-07-01
campaign_id ID for the campaign. This can be joined with id in the campaigns table b1d67331-204b-4ee3-9cac-7c0f0f33b5bf
country country for the metrics US
site_id site_id for the metrics Instagram
users the number of unique users 11
weekly_users the number of unique users in the week 13
monthly_users the number of unique users in the month 122
sessions the number of sessions 4
revenue revenue amount in USD cents 245
transactions the number of purchase transactions 1
Param Description Example value
date date for the metrics 2018-07-01
xday day of user's lifetime (relative to acquisition timestamp, starting with 0) 3
campaign_id ID for the campaign. This can be joined with id in the campaigns table b1d67331-204b-4ee3-9cac-7c0f0f33b5bf
country country for the metrics US
site_id site_id for the metrics Instagram
users the number of unique users 11
weekly_users the number of unique users in the week 13
monthly_users the number of unique users in the month 122
sessions the number of sessions 4
revenue revenue amount in USD cents 245
transactions the number of purchase transactions 1
Param Description Example value
id unique ID for the publisher app b71ff0ab-f1b4-4ca6-98fc-0064a5b9d1d7
ad_network_id id for the ad network publisher campaign is attributed to. It can be joined with id in ad_networks table 1
app_id Tenjin's internal app id. It can be joined with id in apps table ee270433-b7da-4e0f-9b03-8dd25d89da49
name name of the publisher campaign Word Search! Free-ios-Tapjoy
updated_at timestamp when the record is updated 2018-07-01 00:00:00
Param Description Example value
id unique ID for the ad revenue c89abc24-e0fb-47c5-8bd3-10b5aa3a9f52
date date when the ad revenue happens 2018-07-01
publisher_app_id ID for the publisher campaign. This can be joined with id in the publisher_apps table b71ff0ab-f1b4-4ca6-98fc-0064a5b9d1d7
country country for the metrics US
revenue ad revenue amount in USD cents 20000
conversions number of conversions reported 8
clicks number of clicks reported 10
impressions number of impressions reported 126
updated_at timestamp when the record is updated 2018-07-01 00:00:00
Param Description Example value
created_at timestamp when the event was created 2018-07-01 00:00:00
advertising_id IDFA for iOS or Google advertising ID for Android. Lower case, without hyphen. 29eeb1610fe74997b6d53f02e9711f8c
event_type either clicks or impressions clicks
campaign_id ID for the campaign. This can be joined with id in the campaigns table b1d67331-204b-4ee3-9cac-7c0f0f33b5bf
bundle_id app's bundle ID com.tenjin.wordfinder
platform app's platform ios or anroid
ip_address user's IP address 71.165.85.114
limit_ad_tracking user's limit ad tracking (0,1)
uuid unique ID for the event. It can be joined with source_uuid events table 0df8ccda-372b-4e9e-8fdc-dfa0eb0c333e
app_id Tenjin's internal app id. It can be joined with id in apps table ee270433-b7da-4e0f-9b03-8dd25d89da49
country country code US
site_id campaign's publisher ID if the campaign is paid campaign Instagram
remote_click_id click id ad-networks passes through the click url. DSGfaa
Param Description Example value
campaign_id ID for the campaign. This can be joined with id in the campaigns table b1d67331-204b-4ee3-9cac-7c0f0f33b5bf
targeting_tag_id ID for the targeting tag. This can be joined with id in the targeting_tags table 1
Param Description Example value
id ID for the targeting tag. 1
display Targeting tag name shown on Tenjin dashboard Gender: Male
standard Tenjin's default targeting tag or not. True or False. true
category category for the targeting tag Gender
segment segment for the targeting tag Male
Param Description Example value
date date the metric applies to 2018-11-01
ad_network_id Tenjin ID of the ad network 3
platform app's platform ios or android
app_id Tenjin ID of the app ee270433-b7da-4e0f-9b03-8dd25d89da49
campaign_id Tenjin ID of the campaign b1d67331-204b-4ee3-9cac-7c0f0f33b5bf
country two-letter country code US
site_id Site ID Instagram
daily_active_users unique devices seen 105
sessions count of app open events 23650
iap_revenue sum of in-app purchase revenue in USD cents 245
reported_spend allocated spend from ad network reporting APIs in cents 5323
reported_impressions allocated impressions from ad network reporting APIs 13000
reported_clicks allocated clicks from ad network reporting APIs 3000
reported_installs allocated installs from ad network reporting APIs 4000
publisher_ad_revenue allocated sum of publisher ad revenue from ad network reporting API in USD cents 2000
tracked_impressions count of impressions tracked by Tenjin attribution 13000
tracked_clicks count of clicks tracked by Tenjin attribution 4000
tracked_installs count of installs tracked by Tenjin attribution 3000
Param Description Example value
event_date date current cohort the metrics apply to 2018-11-01
install_date date of the install 2018-10-01
days_since_install days since install 3
platform app's platform ios or anroid
app_id Tenjin ID of the app ee270433-b7da-4e0f-9b03-8dd25d89da49
campaign_id Tenjin ID of the campaign b1d67331-204b-4ee3-9cac-7c0f0f33b5bf
country two-letter country code US
site_id Site ID Instagram
daily_active_users unique devices seen 105
sessions count of app open events 23650
iap_revenue sum of in-app purchase revenue in USD cents 245
publisher_ad_revenue allocated sum of publisher ad revenue from ad network reporting API in USD cents 2000

For reporting_cohort_metrics use the install_date and days_since_install to get metrics as in the dashboard. ex:

SELECT install_date AS DATE,
       apps.name as app_name,
       ad_networks.name as ad_network,
       country,
       SUM(CASE WHEN days_since_install <= 1 THEN (publisher_ad_revenue + iap_revenue) / 100.0 ELSE 0 END) AS d1_total_LTV,
       SUM(CASE WHEN days_since_install <= 2 THEN (publisher_ad_revenue + iap_revenue) / 100.0 ELSE 0 END) AS d2_total_LTV,
       SUM(CASE WHEN days_since_install <= 3 THEN (publisher_ad_revenue + iap_revenue) / 100.0 ELSE 0 END) AS d3_total_LTV,
       SUM(CASE WHEN days_since_install = 1 THEN daily_active_users ELSE 0 END) AS d1_retained_users,
       SUM(CASE WHEN days_since_install = 2 THEN daily_active_users ELSE 0 END) AS d2_retained_users,
       SUM(CASE WHEN days_since_install = 3 THEN daily_active_users ELSE 0 END) AS d3_retained_users
FROM reporting_cohort_metrics
  LEFT JOIN apps ON apps.id = reporting_cohort_metrics.app_id
  LEFT JOIN bucket_campaign_info ON bucket_campaign_info.id = reporting_cohort_metrics.campaign_id
  LEFT JOIN ad_networks ON ad_networks.id = bucket_campaign_info.ad_network_id
WHERE install_date = '2018-10-11'
GROUP BY 1,
         2,
         3,
         4
LIMIT 10

Lookback window

These tables have limited historical data depending on the lookback window. If you want to know the lookback window for your DataVault, please contact your account manager at Tenjin.

  • events
  • ad_engagements
  • reporting_metrics
  • reporting_cohort_metrics

results matching ""

    No results matching ""