DataVault Schema and Tables Updated on 22 Jan 2025 16 Minutes to read Did you find this summary helpful?
Thank you for your feedback
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.
Show more 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 ad_networks apps 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 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. Note: this table is not enabled by default; please ask your Tenjin account manager if you want to use it. 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 bucket_campaign_info view that joins campaign buckets and campaigns table sk_ad_networks A mapping of SK Ad Network IDs to Tenjin-specific IDs for ad networks. skan_ad_network_metrics Aggregated reports for conversion values and dimensions received via SK Ad Network postbacks. skan_apple_metrics Aggregated reports for conversion values and dimensions received via Apple. Columns are the same as for sk_ad_network_metrics. However, campaign_id is always null due to limitations on data received from Apple. installs App install events, as provided by Tenjin SDK. ad_mediation_impressions_preview Your Impression-level Ad Revenue from the Mediation Provider. Note: this table is not enabled by default; please ask your Tenjin account manager if you want to use it. Show more (S): Sort Key
Column Description Example value created_at (S) 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(we convert bundle_id to lowercase) com.tenjin.wordfinder platform app's platform ios or android 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 - no receipt was received with the purchase 1: Unverified - a receipt was provided but not able to be verified 2: Fraudulent - a fraudulent receipt was provided 3: Verified - the receipt was successfully verified 4: Sandbox - the receipt was verified in the test sandbox 5: Duplicate - the receipt was already received with an earlier purchase 6: Stale - the purchase event happened 3 days ago and not in real time 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 loaded_at timestamp when the record is updated in DataVault 2018-07-01 00:00:00 tracking_status integer for the iOS device's ATT status . 0 => not determined, 1 => restricted, 2 => denied, 3 => authorized 3 analytics_installation_id Tenjin generated Application install-specific identifier. Lower case, without hyphen. f024e65f3dd94f16983726bcef192d68 customer_user_id User ID from the app, as set by setCustomerUserId SDK method jdabGcerT32_d
Column 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 in Tenjin's database 2018-07-01 00:00:00 loaded_at timestamp when the record is updated in DataVault 2018-07-01 00:00:00
Column 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 in Tenjin's database 2018-07-01 00:00:00 loaded_at timestamp when the record is updated in DataVault 2018-07-01 00:00:00
Column Description Example value id (S) unique ID for the ad_network 3 name ad network name Facebook updated_at timestamp when the record is updated in Tenjin's database 2018-07-01 00:00:00
Column Description Example value id unique ID for the app ee270433-b7da-4e0f-9b03-8dd25d89da49 name app name Word Search store_id app's store ID 887212194 bundle_id app's bundle ID com.tenjin.wordfinder platform app's platform ios or android updated_at timestamp when the record is updated in Tenjin's database 2018-07-01 00:00:00 loaded_at timestamp when the record is updated in DataVault 2018-07-01 00:00:00
Column Description Example value id unique ID for the spend data 174491158 date (S) date when the spend happens 2018-07-01 country country where the spend happens US campaign_id (D) 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 4 original_currency original currency USD updated_at timestamp when the record is updated in Tenjin's database 2018-07-01 00:00:00 loaded_at timestamp when the record is updated in DataVault 2018-07-01 00:00:00 missing_country if country info was missing for the spend that the ad network sent false
Column 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 in Tenjin's database 2018-07-01 00:00:00 loaded_at timestamp when the record is updated in DataVault 2018-07-01 00:00:00
Column Description Example value id unique ID for the ad revenue 107852371 date (S) date when the ad revenue happens 2018-07-01 publisher_app_id (D) 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 interstitial_revenue ad revenue amount in USD cents for interstitial ads 20000 interstitial_conversions number of conversions reported for interstitial ads 8 interstitial_clicks number of clicks reported for interstitial ads 10 interstitial_impressions number of impressions reported for interstitial ads 126 banner_revenue ad revenue amount in USD cents for banner ads 20000 banner_conversions number of conversions reported for banner ads 8 banner_clicks number of clicks reported for banner ads 10 banner_impressions number of impressions reported for banner ads 126 native_revenue ad revenue amount in USD cents for native ads 20000 native_conversions number of conversions reported for native ads 8 native_clicks number of clicks reported for native ads 10 native_impressions number of impressions reported for native ads 126 offerwall_revenue ad revenue amount in USD cents for offerwall ads 20000 offerwall_conversions number of conversions reported for offerwall ads 8 offerwall_clicks number of clicks reported for offerwall ads 10 offerwall_impressions number of impressions reported for offerwall ads 126 video_revenue ad revenue amount in USD cents for video ads 20000 video_conversions number of conversions reported for video ads 8 video_clicks number of clicks reported for video ads 10 video_impressions number of impressions reported for video ads 126 other_revenue ad revenue amount in USD cents for other ads 20000 other_conversions number of conversions reported for other ads 8 other_clicks number of clicks reported for other ads 10 other_impressions number of impressions reported for other ads 126 updated_at timestamp when the record is updated in Tenjin's database 2018-07-01 00:00:00 loaded_at timestamp when the record is updated in DataVault 2018-07-01 00:00:00
NOTE
Contact your Account Manager to access this table
Column Description Example value created_at (S) 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 attributable if the click/impression is used for attribution or not. true or false true 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 user_agent User Agent Dalvik/2.1.0 (Linux; U; Android 8.0.0; SM-G570M Build/R16NW) remote_click_id click id ad-networks passes through the click url. DSGfaa loaded_at timestamp when the record is updated in DataVault 2018-07-01 00:00:00 tracking_status integer for the iOS device's ATT status . 0 => not determined, 1 => restricted, 2 => denied, 3 => authorized 3 analytics_installation_id Tenjin generated Application install-specific identifier. Lower case, without hyphen. f024e65f3dd94f16983726bcef192d68
Column 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
Column Description Example value id (S) 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 loaded_at timestamp when the record is updated in DataVault 2018-07-01 00:00:00
Column Description Example value date (S) 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 loaded_at timestamp when the record is updated in DataVault 2018-07-01 00:00:00
Column Description Example value event_date (S) 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 loaded_at timestamp when the record is updated in DataVault 2018-07-01 00:00:00
For reporting_cohort_metrics use the install_date and days_since_install to get metrics as in the dashboard.
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
Column Description Example value id unique ID for the campaign bucket c89abc24-e0fb-47c5-8bd3-10b5aa3a9f52 ad_network_id Tenjin ID of the ad network 3 app_id Tenjin ID of the app ee270433-b7da-4e0f-9b03-8dd25d89da49 name campaign bucket name or raw campaign name US - TEST is_bucket flat that shows if the record is campaign bucket or not TRUE or FALSE
Contact your Account Manager to access this table.
Column Description Example value id(S) Lowercase string SK Ad Network ID for the ad network. "cstr6suwn9.skadnetwork" ad_network_id Integer Tenjin-specific ID for the ad network. Can be joined via ad_networks.id. 5 updated_at Timestamp representing when the record was last updated. 2021-01-01 00:00:00 loaded_at Timestamp representing when the record was last loaded into DataVault. 2021-01-01 00:00:00
Contact your Account Manager to access this table.
Column Description Example value date(S) Date the SK Ad Network postback was received. 2021-01-01 sk_ad_network_id Lowercase string SK Ad Network ID for the ad network. "cstr6suwn9.skadnetwork" ad_network_id Integer Tenjin-specific ID for the ad network. Can be joined via ad_networks.id. 5 sk_app_id Integer SK App ID of the App being advertised. Equivalent to apps.store_id for iOS Apps. 1478027094 app_id UUID Tenjin-specific ID for the App. Can be joined via apps.id. "9668185b-da2e-4638-b15f-e03431377ac5" sk_campaign_id SK Campaign ID, an Integer from 0-99. 10 campaign_id UUID Tenjin-specific ID for the Campaign. Can be joined via campaigns.id. "61817a78-0a8f-4e83-98c0-8106f7a6ea92" sk_source_app_id String representing the App that showed the advertisement that led to a conversion. "284882215" fidelity_type 1 for StoreKit Ads, 0 for view-through ads. 1 conversion_value Conversion Value, an Integer from 0-63. 33 conversion_value_count Integer count of occurrences of the conversion_value. 250 redownload_count Integer count of occurrences of the conversion_value that were redownloads. 11 assist_count Integer count of occurrences of the conversion_value that were assists. 2 redownload_assist_count Integer count of occurrences of the conversion_value that were both redownloads and assists. 1 loaded_at Timestamp representing when the record was last loaded into DataVault. 2021-01-01 00:00:00
Contact your Account Manager to access this table.
Column Description Example value date(S) Date the SK Ad Network postback was received. 2021-01-01 sk_ad_network_id Lowercase string SK Ad Network ID for the ad network. "cstr6suwn9.skadnetwork" ad_network_id Integer Tenjin-specific ID for the ad network. Can be joined via ad_networks.id. 5 sk_app_id Integer SK App ID of the App being advertised. Equivalent to apps.store_id for iOS Apps. 1478027094 app_id UUID Tenjin-specific ID for the App. Can be joined via apps.id. "9668185b-da2e-4638-b15f-e03431377ac5" sk_campaign_id SK Campaign ID, an Integer from 0-99. 10 campaign_id UUID Tenjin-specific ID for the Campaign. Always null, as Apple does not provide any way to get this value. nullsk_source_app_id String representing the App that showed the advertisement that led to a conversion. "284882215" fidelity_type 1 for StoreKit Ads, 0 for view-through ads. 1 conversion_value Conversion Value, an Integer from 0-63. 33 conversion_value_count Integer count of occurrences of the conversion_value. 250 redownload_count Integer count of occurrences of the conversion_value that were redownloads. 11 assist_count Integer count of occurrences of the conversion_value that were assists. 2 redownload_assist_count Integer count of occurrences of the conversion_value that were both redownloads and assists. 1 loaded_at Timestamp representing when the record was last loaded into DataVault. 2021-01-01 00:00:00
Column Description Example value created_at timestamp when the event was created 2018-11-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(we convert bundle_id to lowercase) com.tenjin.wordfinder platform app's platform ios or android 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 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-10-01 00:00:00 country country code US 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 uuid unique event ID a3e3e535-a32c-4887-9c91-3160c91b3876 device device type iPAD 2,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 loaded_at timestamp when the record is updated in DataVault 2018-10-01 00:00:00 tracking_status integer for the iOS device's ATT status. 0 => not determined, 1 => restricted, 2 => denied, 3 => authorized 3 analytics_installation_id Tenjin generated Application install-specific identifier. Lower case, without hyphen f024e65f3dd94f16983726bcef192d68
Contact your Account Manager to access this table
Column Description Example value acquired_at timestamp when the user was acquired 2018-11-01 00:00:00 ad_mediation_ad_format Format of the Ad unit Banner ad_mediation_network Network that displayed the ad Applovin ad_mediation_placement Network defined placement my_banner_placement ad_mediation_provider Ad mediation provider Applovin MAX ad_mediation_revenue_microcents Ad revenue in microscents as provided by the mediation provider 22000 advertising_id IDFA for iOS or Google advertising ID for Android. Lower case, without hyphen. 29eeb1610fe74997b6d53f02e9711f8c analytics_installation_id Application install-specific identifier for Android devices. Used for analytics when a user does not consent to sharing their Google advertising id. (Empty for iOS.) Lower case, without hyphen. f024e65f3dd94f16983726bcef192d68 app_id Tenjin's internal app id. It can be joined with id in apps table ee270433-b7da-4e0f-9b03-8dd25d89da49 app_version app version 1.0.3 bundle_id app's bundle ID(we convert bundle_id to lowercase) com.tenjin.wordfinder country country code US created_at timestamp when the event was created 2018-11-01 00:00:00 creative_name creative name test_creative customer_user_id User ID from the app, as set by setCustomerUserId SDK method jdabGcerT32_d developer_device_id IDFV for iOS. Empty for Android. Lower case, without hyphen b6bc48fe0ce949e4b229ce6c55663fca device device type iPAD 2.5 engaged_at Timestamp for the impression event 2018-01-01 00:00:00 ip_address user's IP address 71.165.85.114 limit_ad_tracking user's limit ad tracking (0,1) loaded_at timestamp when the record is updated in DataVault 2018-07-01 00:00:00 os_version device's os_version 8.0 platform app's platform 8.0 sdk_version User's Tenjin SDK version 12.2.22 site_id campaign's publisher ID if the campaign is paid campaign instagram 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 source_uuid UUID for the impression event if the campaign was paid campaign. It can be joined with uuid in ad_engagements table 0df8ccda-372b-4e9e-8fdc-dfa0eb0c333e tracking_status integer for the iOS device's ATT status. 0 => not determined, 1 => restricted, 2 => denied, 3 => authorized 3 uuid unique ID for the event. It can be joined with source_uuid events table 0df8ccda-372b-4e9e-8fdc-dfa0eb0c333e
Show more These tables have limited historical data depending on the lookback window as mentioned in your Tenjin contract. 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
Was this article helpful?
Thank you for your feedback! Our team will get back to you
 (1).png){kind=logo}
