DataVault 的 Schema 和 Tables

Tenjin 的 DataVault schema 是其功能的核心。来自不同地方的所有信息都可以通过id很好地结合在一起。通过以这种方式组织信息,DataVault 可以轻松地构建准确的模型和分析。

规范化的 Schema

以上是 DataVault 的 Schema。

Table 说明


  • 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
  • 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

Table 说明详细信息

(S): Sort Key
(D): Dist 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 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
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 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
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 24d016ca-d60e-4f01-b705-97299b3cf341
date (S) date when the spend happens 2018-07-01
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
Column Description Example value
id unique ID for the spend data 24d016ca-d60e-4f01-b705-97299b3cf341
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
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 c89abc24-e0fb-47c5-8bd3-10b5aa3a9f52
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
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
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. 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
Param 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

回溯期

以下这些 tables 的历史数据取决于回溯期。如果您想了解 DataVault 的回溯窗口,请联系 Tenjin 的客户经理。

  • events
  • ad_engagements
  • reporting_metrics
  • reporting_cohort_metrics

results matching ""

    No results matching ""