Schema
  • 01 Sep 2023
  • 16 Minutes to read
  • Dark
    Light

Schema

  • Dark
    Light

Article Summary

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.


List of tables

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
    • list of campaign buckets
  • ad_networks
    • list of ad networks
  • apps
    • list of 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 Revenue 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.

List of table fields

Show more

(S): Sort Key

(D): Dist Key

ColumnDescriptionExample value
created_at (S)timestamp when the event was created2018-07-01 00:00:00
advertising_idIDFA for iOS or Google advertising ID for Android. Lower case, without hyphen.29eeb1610fe74997b6d53f02e9711f8c
bundle_idapp's bundle ID(we convert bundle_id to lowercase)com.tenjin.wordfinder
platformapp's platformios or android
os_versiondevice's os_version8.0
app_versionapp version1.0.3
limit_ad_trackinguser's limit ad tracking (0,1)1
app_idTenjin's internal app id. It can be joined with id in apps tableee270433-b7da-4e0f-9b03-8dd25d89da49
event_typeeither event or purchaseevent
eventwhen event_type = event, then this value is either open, or a custom event name.open
source_campaign_idTenjin's internal campaign id for the campaign user is attributed to. It can be joined with id in campaigns table18a0c1d6-02a2-4188-bbc0-0fe38dbcc37e
acquired_attimestamp when the user was acquired2018-07-01 00:00:00
priceraw purchase price for the purchase event1.99
quantitypurchase quantity for the purchase event2
product_idproduct id for the purchase eventcom.tenjin.wordfinder.package50
currencycurrency code for the purchase eventUSD
countrycountry codeUS
total_revenuegross revenue for the purchase event in USD cents350
revenuenet revenue for the purchase event in USD cents245
site_idcampaign's publisher ID if the campaign is paid campaignInstagram
developer_device_idIDFV 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
valueCustom event value1
uuidunique event IDa3e3e535-a32c-4887-9c91-3160c91b3876
devicedevice typeiPad2,5
creative_namecreative nametest_creative
ip_addressuser's IP address71.165.85.114
source_uuidUUID for the click or impression event if the campaign was paid campaign. It can be joined with uuid in ad_engagements table0df8ccda-372b-4e9e-8fdc-dfa0eb0c333e
loaded_attimestamp when the record is updated in DataVault2018-07-01 00:00:00
tracking_statusinteger for the iOS device's ATT status. 0 => not determined, 1 => restricted, 2 => denied, 3 => authorized3
analytics_installation_idApplication 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_idUser ID from the app, as set by setCustomerUserId SDK methodjdabGcerT32_d
ColumnDescriptionExample value
idunique ID for the campaign3b70e477-c151-4167-9174-cca6ffa2c7bc
namecampaign nametest @bYkiNAbrAOCpzETWviMILo
ad_network_idid for the ad network, campaign is assigned to. It can be joined with id in ad_networks table3
app_idTenjin's internal app id. It can be joined with id in apps tableee270433-b7da-4e0f-9b03-8dd25d89da49
remote_campaign_idcampaign_id ad-network uses. We get this through ad-network reporting API.6026825090762
campaign_bucket_idcampaign bucket id to bucket campaigns. This can be joined with id in campaign_buckets tablefbbc74bd-8a73-472a-86f7-5e2a4642a201
updated_attimestamp when the record is updated in Tenjin's database2018-07-01 00:00:00
loaded_attimestamp when the record is updated in DataVault2018-07-01 00:00:00
ColumnDescriptionExample value
idunique ID for the campaign bucket3b70e477-c151-4167-9174-cca6ffa2c7bc
namecampaign bucket nametest @bYkiNAbrAOCpzETWviMILo
ad_network_idid for the ad network campaign is attributed to. It can be joined with id in ad_networks table5
app_idTenjin's internal app id. It can be joined with id in apps tableee270433-b7da-4e0f-9b03-8dd25d89da49
updated_attimestamp when the record is updated in Tenjin's database2018-07-01 00:00:00
loaded_attimestamp when the record is updated in DataVault2018-07-01 00:00:00
ColumnDescriptionExample value
id (S)unique ID for the ad_network3
namead network nameFacebook
updated_attimestamp when the record is updated in Tenjin's database2018-07-01 00:00:00
ColumnDescriptionExample value
idunique ID for the appee270433-b7da-4e0f-9b03-8dd25d89da49
nameapp nameWord Search
store_idapp's store ID887212194
bundle_idapp's bundle IDcom.tenjin.wordfinder
updated_attimestamp when the record is updated in Tenjin's database2018-07-01 00:00:00
loaded_attimestamp when the record is updated in DataVault2018-07-01 00:00:00
ColumnDescriptionExample value
idunique ID for the spend data174491158
date (S)date when the spend happens2018-07-01
countrycountry where the spend happensUS
campaign_id (D)ID for the campaign. This can be joined with id in the campaigns tableb1d67331-204b-4ee3-9cac-7c0f0f33b5bf
spendad spend amount in USD cents.400
installsnumber of installs reported8
clicksnumber of clicks reported10
impressionsnumber of impressions reported126
original_spendad spend amount in original currency4
original_currencyoriginal currencyUSD
updated_attimestamp when the record is updated in Tenjin's database2018-07-01 00:00:00
loaded_attimestamp when the record is updated in DataVault2018-07-01 00:00:00
ColumnDescriptionExample value
idunique ID for the publisher appb71ff0ab-f1b4-4ca6-98fc-0064a5b9d1d7
ad_network_idid for the ad network publisher campaign is attributed to. It can be joined with id in ad_networks table1
app_idTenjin's internal app id. It can be joined with id in apps tableee270433-b7da-4e0f-9b03-8dd25d89da49
namename of the publisher campaignWord Search! Free-ios-Tapjoy
updated_attimestamp when the record is updated in Tenjin's database2018-07-01 00:00:00
loaded_attimestamp when the record is updated in DataVault2018-07-01 00:00:00
ColumnDescriptionExample value
idunique ID for the ad revenue107852371
date (S)date when the ad revenue happens2018-07-01
publisher_app_id (D)ID for the publisher campaign. This can be joined with id in the publisher_apps tableb71ff0ab-f1b4-4ca6-98fc-0064a5b9d1d7
countrycountry for the metricsUS
revenuead revenue amount in USD cents20000
conversionsnumber of conversions reported8
clicksnumber of clicks reported10
impressionsnumber of impressions reported126
interstitial_revenuead revenue amount in USD cents for interstitial ads20000
interstitial_conversionsnumber of conversions reported for interstitial ads8
interstitial_clicksnumber of clicks reported for interstitial ads10
interstitial_impressionsnumber of impressions reported for interstitial ads126
banner_revenuead revenue amount in USD cents for banner ads20000
banner_conversionsnumber of conversions reported for banner ads8
banner_clicksnumber of clicks reported for banner ads10
banner_impressionsnumber of impressions reported for banner ads126
native_revenuead revenue amount in USD cents for native ads20000
native_conversionsnumber of conversions reported for native ads8
native_clicksnumber of clicks reported for native ads10
native_impressionsnumber of impressions reported for native ads126
offerwall_revenuead revenue amount in USD cents for offerwall ads20000
offerwall_conversionsnumber of conversions reported for offerwall ads8
offerwall_clicksnumber of clicks reported for offerwall ads10
offerwall_impressionsnumber of impressions reported for offerwall ads126
video_revenuead revenue amount in USD cents for video ads20000
video_conversionsnumber of conversions reported for video ads8
video_clicksnumber of clicks reported for video ads10
video_impressionsnumber of impressions reported for video ads126
other_revenuead revenue amount in USD cents for other ads20000
other_conversionsnumber of conversions reported for other ads8
other_clicksnumber of clicks reported for other ads10
other_impressionsnumber of impressions reported for other ads126
updated_attimestamp when the record is updated in Tenjin's database2018-07-01 00:00:00
loaded_attimestamp when the record is updated in DataVault2018-07-01 00:00:00
NOTE

Contact your Account Manager to access this table

ColumnDescriptionExample value
created_at (S)timestamp when the event was created2018-07-01 00:00:00
advertising_idIDFA for iOS or Google advertising ID for Android. Lower case, without hyphen.29eeb1610fe74997b6d53f02e9711f8c
attributableif the click/impression is used for attribution or not. true or falsetrue
event_typeeither clicks or impressionsclicks
campaign_idID for the campaign. This can be joined with id in the campaigns tableb1d67331-204b-4ee3-9cac-7c0f0f33b5bf
bundle_idapp's bundle IDcom.tenjin.wordfinder
platformapp's platformios or anroid
ip_addressuser's IP address71.165.85.114
limit_ad_trackinguser's limit ad tracking(0,1)
uuidunique ID for the event. It can be joined with source_uuid events table0df8ccda-372b-4e9e-8fdc-dfa0eb0c333e
app_idTenjin's internal app id. It can be joined with id in apps tableee270433-b7da-4e0f-9b03-8dd25d89da49
countrycountry codeUS
site_idcampaign's publisher ID if the campaign is paid campaignInstagram
user_agentUser AgentDalvik/2.1.0 (Linux; U; Android 8.0.0; SM-G570M Build/R16NW)
remote_click_idclick id ad-networks passes through the click url.DSGfaa
loaded_attimestamp when the record is updated in DataVault2018-07-01 00:00:00
tracking_statusinteger for the iOS device's ATT status. 0 => not determined, 1 => restricted, 2 => denied, 3 => authorized3
analytics_installation_idApplication 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
ColumnDescriptionExample value
campaign_idID for the campaign. This can be joined with id in the campaigns tableb1d67331-204b-4ee3-9cac-7c0f0f33b5bf
targeting_tag_idID for the targeting tag. This can be joined with id in the targeting_tags table1
ColumnDescriptionExample value
id (S)ID for the targeting tag.1
displayTargeting tag name shown on Tenjin dashboardGender: Male
standardTenjin's default targeting tag or not. True or False.true
categorycategory for the targeting tagGender
segmentsegment for the targeting tagMale
loaded_attimestamp when the record is updated in DataVault2018-07-01 00:00:00
ColumnDescriptionExample value
date (S)date the metric applies to2018-11-01
ad_network_idTenjin ID of the ad network3
platformapp's platformios or android
app_idTenjin ID of the appee270433-b7da-4e0f-9b03-8dd25d89da49
campaign_idTenjin ID of the campaignb1d67331-204b-4ee3-9cac-7c0f0f33b5bf
countrytwo-letter country codeUS
site_idSite IDInstagram
daily_active_usersunique devices seen105
sessionscount of app open events23650
iap_revenuesum of in-app purchase revenue in USD cents245
reported_spendallocated spend from ad network reporting APIs in cents5323
reported_impressionsallocated impressions from ad network reporting APIs13000
reported_clicksallocated clicks from ad network reporting APIs3000
reported_installsallocated installs from ad network reporting APIs4000
publisher_ad_revenueallocated sum of publisher ad revenue from ad network reporting API in USD cents2000
tracked_impressionscount of impressions tracked by Tenjin attribution13000
tracked_clickscount of clicks tracked by Tenjin attribution4000
tracked_installscount of installs tracked by Tenjin attribution3000
loaded_attimestamp when the record is updated in DataVault2018-07-01 00:00:00
ColumnDescriptionExample value
event_date (S)date current cohort the metrics apply to2018-11-01
install_datedate of the install2018-10-01
days_since_installdays since install3
platformapp's platformios or anroid
app_idTenjin ID of the appee270433-b7da-4e0f-9b03-8dd25d89da49
campaign_idTenjin ID of the campaignb1d67331-204b-4ee3-9cac-7c0f0f33b5bf
countrytwo-letter country codeUS
site_idSite IDInstagram
daily_active_usersunique devices seen105
sessionscount of app open events23650
iap_revenuesum of in-app purchase revenue in USD cents245
publisher_ad_revenueallocated sum of publisher ad revenue from ad network reporting API in USD cents2000
loaded_attimestamp when the record is updated in DataVault2018-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
ColumnDescriptionExample value
idunique ID for the campaign bucketc89abc24-e0fb-47c5-8bd3-10b5aa3a9f52
ad_network_idTenjin ID of the ad network3
app_idTenjin ID of the appee270433-b7da-4e0f-9b03-8dd25d89da49
namecampaign bucket name or raw campaign nameUS - TEST
is_bucketflat that shows if the record is campaign bucket or notTRUE or FALSE

Contact your Account Manager to access this table.

ColumnDescriptionExample value
id(S)Lowercase string SK Ad Network ID for the ad network."cstr6suwn9.skadnetwork"
ad_network_idInteger Tenjin-specific ID for the ad network. Can be joined via ad_networks.id.5
updated_atTimestamp representing when the record was last updated.2021-01-01 00:00:00
loaded_atTimestamp representing when the record was last loaded into DataVault.2021-01-01 00:00:00

Contact your Account Manager to access this table.

ColumnDescriptionExample value
date(S)Date the SK Ad Network postback was received.2021-01-01
sk_ad_network_idLowercase string SK Ad Network ID for the ad network."cstr6suwn9.skadnetwork"
ad_network_idInteger Tenjin-specific ID for the ad network. Can be joined via ad_networks.id.5
sk_app_idInteger SK App ID of the App being advertised. Equivalent to apps.store_id for iOS Apps.1478027094
app_idUUID Tenjin-specific ID for the App. Can be joined via apps.id."9668185b-da2e-4638-b15f-e03431377ac5"
sk_campaign_idSK Campaign ID, an Integer from 0-99.10
campaign_idUUID Tenjin-specific ID for the Campaign. Can be joined via campaigns.id."61817a78-0a8f-4e83-98c0-8106f7a6ea92"
sk_source_app_idString representing the App that showed the advertisement that led to a conversion."284882215"
fidelity_type1 for StoreKit Ads, 0 for view-through ads.1
conversion_valueConversion Value, an Integer from 0-63.33
conversion_value_countInteger count of occurrences of the conversion_value.250
redownload_countInteger count of occurrences of the conversion_value that were redownloads.11
assist_countInteger count of occurrences of the conversion_value that were assists.2
redownload_assist_countInteger count of occurrences of the conversion_value that were both redownloads and assists.1
loaded_atTimestamp representing when the record was last loaded into DataVault.2021-01-01 00:00:00

Contact your Account Manager to access this table.

ColumnDescriptionExample value
date(S)Date the SK Ad Network postback was received.2021-01-01
sk_ad_network_idLowercase string SK Ad Network ID for the ad network."cstr6suwn9.skadnetwork"
ad_network_idInteger Tenjin-specific ID for the ad network. Can be joined via ad_networks.id.5
sk_app_idInteger SK App ID of the App being advertised. Equivalent to apps.store_id for iOS Apps.1478027094
app_idUUID Tenjin-specific ID for the App. Can be joined via apps.id."9668185b-da2e-4638-b15f-e03431377ac5"
sk_campaign_idSK Campaign ID, an Integer from 0-99.10
campaign_idUUID Tenjin-specific ID for the Campaign. Always null, as Apple does not provide any way to get this value.null
sk_source_app_idString representing the App that showed the advertisement that led to a conversion."284882215"
fidelity_type1 for StoreKit Ads, 0 for view-through ads.1
conversion_valueConversion Value, an Integer from 0-63.33
conversion_value_countInteger count of occurrences of the conversion_value.250
redownload_countInteger count of occurrences of the conversion_value that were redownloads.11
assist_countInteger count of occurrences of the conversion_value that were assists.2
redownload_assist_countInteger count of occurrences of the conversion_value that were both redownloads and assists.1
loaded_atTimestamp representing when the record was last loaded into DataVault.2021-01-01 00:00:00
ColumnDescriptionExample value
created_attimestamp when the event was created2018-11-01 00:00:00
advertising_idIDFA for iOS or Google advertising ID for Android. Lower case, without hyphen29eeb1610fe74997b6d53f02e9711f8c
bundle_idapp's bundle ID(we convert bundle_id to lowercase)com.tenjin.wordfinder
platformapp's platformios or android
os_versiondevice's os_version8.0
app_versionapp version1.0.3
limit_ad_trackinguser's limit ad tracking (0,1)1
app_idTenjin's internal app id. It can be joined with id in apps tableee270433-b7da-4e0f-9b03-8dd25d89da49
source_campaign_idTenjin's internal campaign id for the campaign user is attributed to. It can be joined with id in campaigns table18a0c1d6-02a2-4188-bbc0-0fe38dbcc37e
acquired_attimestamp when the user was acquired2018-10-01 00:00:00
countrycountry codeUS
site_idcampaign's publisher ID if the campaign is paid campaignInstagram
developer_device_idIDFV for iOS. Empty for Android. Lower case, without hyphenb6bc48fe0ce949e4b229ce6c55663fca
uuidunique event IDa3e3e535-a32c-4887-9c91-3160c91b3876
devicedevice typeiPAD 2,5
creative_namecreative nametest_creative
ip_addressuser's IP address71.165.85.114
source_uuidUUID for the click or impression event if the campaign was paid campaign. It can be joined with uuid in ad_engagements table0df8ccda-372b-4e9e-8fdc-dfa0eb0c333e
loaded_attimestamp when the record is updated in DataVault2018-10-01 00:00:00
tracking_statusinteger for the iOS device's ATT status. 0 => not determined, 1 => restricted, 2 => denied, 3 => authorized3
analytics_installation_idApplication 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 hyphenf024e65f3dd94f16983726bcef192d68

Contact your Account Manager to access this table

ColumnDescriptionExample value
acquired_attimestamp when the user was acquired2018-11-01 00:00:00
ad_mediation_ad_formatFormat of the Ad unitBanner
ad_mediation_networkNetwork that displayed the adApplovin
ad_mediation_placementNetwork defined placementmy_banner_placement
ad_mediation_providerAd mediation providerApplovin MAX
ad_mediation_revenue_microcentsAd revenue in microscents as provided by the mediation provider22000
advertising_idIDFA for iOS or Google advertising ID for Android. Lower case, without hyphen.29eeb1610fe74997b6d53f02e9711f8c
analytics_installation_idApplication 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_idTenjin's internal app id. It can be joined with id in apps tableee270433-b7da-4e0f-9b03-8dd25d89da49
app_versionapp version1.0.3
bundle_idapp's bundle ID(we convert bundle_id to lowercase)com.tenjin.wordfinder
countrycountry codeUS
created_attimestamp when the event was created2018-11-01 00:00:00
creative_namecreative nametest_creative
customer_user_idUser ID from the app, as set by setCustomerUserId SDK methodjdabGcerT32_d
developer_device_idIDFV for iOS. Empty for Android. Lower case, without hyphenb6bc48fe0ce949e4b229ce6c55663fca
devicedevice typeiPAD 2.5
engaged_atTimestamp for the impression event2018-01-01 00:00:00
ip_addressuser's IP address71.165.85.114
limit_ad_trackinguser's limit ad tracking(0,1)
loaded_attimestamp when the record is updated in DataVault2018-07-01 00:00:00
os_versiondevice's os_version8.0
platformapp's platform8.0
sdk_versionUser's Tenjin SDK version12.2.22
site_idcampaign's publisher ID if the campaign is paid campaigninstagram
source_campaign_idTenjin's internal campaign id for the campaign user is attributed to. It can be joined with id in campaigns table18a0c1d6-02a2-4188-bbc0-0fe38dbcc37e
source_uuidUUID for the impression event if the campaign was paid campaign. It can be joined with uuid in ad_engagements table0df8ccda-372b-4e9e-8fdc-dfa0eb0c333e
tracking_statusinteger for the iOS device's ATT status. 0 => not determined, 1 => restricted, 2 => denied, 3 => authorized3
uuidunique ID for the event. It can be joined with source_uuid events table0df8ccda-372b-4e9e-8fdc-dfa0eb0c333e

Lookback window

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?

What's Next