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.
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
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.
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.
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
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
customer_user_id
User ID from the app, as set by setCustomerUserId SDK method
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
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.
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. 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
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
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
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
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}