Try out all OWOX BI features with a trial period Start for free

November 4, 2020 — OWOX BI Pipeline. Both iOS and Android tables (AppsFlyer→GBQ pipeline) are now combined into a single View in Google BigQuery

OWOX BI starts automatically combining data from both iOS and Android tables into one logical View named mobile_devices_events in Google BigQuery. Accessing consolidated data allows you to optimize data processing and save your time. Yet, you can still query each table separately. 

The View will also be used as a data source for marketing report creation in Smart Data.

These changes affect all current and new AppsFlyer→GBQ pipelines.

Please note, that the mobile_devices_events View is required to process your data correctly. The View must not be deleted. Otherwise, it cannot be automatically restored.   

If you delete the View for some reasons, you need to create it manually:

  • Copy SQL query from below. For FROM `$(projectId).$(datasetId).ios_events` and FROM `$(projectId).$(datasetId).android_events` ,  enter the names of your BigQuery project and dataset that are used in the AppsFlyer→GBQ pipeline. Then run the query.
  • Click Save view.
  • In the Save view dialog, select the same BigQuery project and dataset that are used in your AppsFlyer→GBQ pipeline. For Table name, enter "mobile_devices_events".
  • Click Save.

SQL query to create mobile_devices_events View:

SELECT
advertising_id,
af_ad,
af_ad_id,
af_ad_type,
af_adset,
af_adset_id,
af_attribution_lookback,
af_c_id,
af_channel,
af_cost_currency,
af_cost_model,
af_cost_value,
af_keywords,
af_prt,
af_reengagement_window,
af_siteid,
af_sub_siteid,
af_sub1,
af_sub2,
af_sub3,
af_sub4,
af_sub5,
amazon_aid,
android_id,
api_version,
app_id,
app_name,
app_version,
appsflyer_id,
attributed_touch_time,
attributed_touch_time_selected_timezone,
attributed_touch_type,
bundle_id,
campaign,
carrier,
city,
contributor_1_af_prt,
contributor_1_campaign,
contributor_1_match_type,
contributor_1_media_source,
contributor_1_touch_time,
contributor_1_touch_type,
contributor_2_af_prt,
contributor_2_campaign,
contributor_2_match_type,
contributor_2_media_source,
contributor_2_touch_time,
contributor_2_touch_type,
contributor_3_af_prt,
contributor_3_campaign,
contributor_3_match_type,
contributor_3_media_source,
contributor_3_touch_time,
contributor_3_touch_type,
cost_in_selected_currency,
country_code,
custom_data,
customer_user_id,
deeplink_url,
device_category,
device_type,
dma,
download_time,
download_time_selected_timezone,
event_name,
event_revenue,
event_revenue_currency,
event_revenue_usd,
event_source,
event_time,
event_time_selected_timezone,
event_value,
gp_broadcast_referrer,
gp_click_time,
gp_install_begin,
gp_referrer,
http_referrer,
idfa,
idfv,
imei,
install_app_store,
install_time,
install_time_selected_timezone,
ip,
is_LAT,
is_primary_attribution,
is_receipt_validated,
is_retargeting,
keyword_id,
keyword_match_type,
language,
match_type,
media_source,
network_account_id,
oaid,
operator,
original_url,
os_version,
platform,
postal_code,
region,
retargeting_conversion_type,
revenue_in_selected_currency,
sdk_version,
selected_currency,
selected_timezone,
state,
store_reinstall,
user_agent,
wifi,
DATE(_PARTITIONTIME) as partition_time
FROM `$(projectId).$(datasetId).android_events`
UNION ALL
SELECT
advertising_id,
af_ad,
af_ad_id,
af_ad_type,
af_adset,
af_adset_id,
af_attribution_lookback,
af_c_id,
af_channel,
af_cost_currency,
af_cost_model,
af_cost_value,
af_keywords,
af_prt,
af_reengagement_window,
af_siteid,
af_sub_siteid,
af_sub1,
af_sub2,
af_sub3,
af_sub4,
af_sub5,
amazon_aid,
NULL as android_id,
api_version,
app_id,
app_name,
app_version,
appsflyer_id,
attributed_touch_time,
attributed_touch_time_selected_timezone,
attributed_touch_type,
bundle_id,
campaign,
carrier,
city,
contributor_1_af_prt,
contributor_1_campaign,
contributor_1_match_type,
contributor_1_media_source,
contributor_1_touch_time,
contributor_1_touch_type,
contributor_2_af_prt,
contributor_2_campaign,
contributor_2_match_type,
contributor_2_media_source,
contributor_2_touch_time,
contributor_2_touch_type,
contributor_3_af_prt,
contributor_3_campaign,
contributor_3_match_type,
contributor_3_media_source,
contributor_3_touch_time,
contributor_3_touch_type,
cost_in_selected_currency,
country_code,
custom_data,
customer_user_id,
deeplink_url,
device_category,
device_type,
dma,
download_time,
download_time_selected_timezone,
event_name,
event_revenue,
event_revenue_currency,
event_revenue_usd,
event_source,
event_time,
event_time_selected_timezone,
event_value,
gp_broadcast_referrer,
gp_click_time,
gp_install_begin,
gp_referrer,
http_referrer,
idfa,
idfv,
imei,
install_app_store,
install_time,
install_time_selected_timezone,
ip,
is_LAT,
is_primary_attribution,
is_receipt_validated,
is_retargeting,
keyword_id,
keyword_match_type,
language,
match_type,
media_source,
network_account_id,
oaid,
operator,
original_url,
os_version,
platform,
postal_code,
region,
retargeting_conversion_type,
revenue_in_selected_currency,
sdk_version,
selected_currency,
selected_timezone,
state,
store_reinstall,
user_agent,
wifi,
DATE(_PARTITIONTIME) as partition_time
FROM `$(projectId).$(datasetId).ios_events`
Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.