Untitled
unknown
sql
a month ago
42 kB
2
Indexable
Never
WITH
/* amd_fact AS () ,*/
/* The main body of the model is below this line. */
airbyte_fact_vwb AS (
SELECT DISTINCT
CAST(current_timestamp AS TIMESTAMP(6) WITH TIME ZONE) AS updated_timestamp
, CAST('dv3'
|| '_' || COALESCE(CAST(CAST(ROUND(TRY_CAST(advertiser_id AS DOUBLE )) AS BIGINT) AS VARCHAR), 'NA')
|| '_' || COALESCE(CAST(CAST(ROUND(TRY_CAST(campaign_id AS DOUBLE )) AS BIGINT) AS VARCHAR), 'NA')
|| '_' || COALESCE(CAST(CAST(ROUND(TRY_CAST(insertion_order_id AS DOUBLE )) AS BIGINT) AS VARCHAR), 'NA')
|| '_' || DATE_FORMAT(CAST(date AS DATE), '%Y%m%d') AS VARCHAR ) AS composite_fact_key
,
'vwb'
AS brand_key
, 'dv3' AS platform_key
, 'dv3' || '_' || COALESCE(CAST(CAST(ROUND(TRY_CAST(advertiser_id AS DOUBLE )) AS BIGINT) AS VARCHAR), 'NA') AS platform_account_key
, 'dv3' || '_' || COALESCE(CAST(CAST(ROUND(TRY_CAST(campaign_id AS DOUBLE )) AS BIGINT) AS VARCHAR), 'NA') AS platform_campaign_key
, 'dv3' || '_' || COALESCE(CAST(CAST(ROUND(TRY_CAST(insertion_order_id AS DOUBLE )) AS BIGINT) AS VARCHAR), 'NA') AS platform_universal_key
, CAST(date AS DATE) AS date
, CAST(CAST(ROUND(TRY_CAST(advertiser_id AS DOUBLE )) AS BIGINT) AS VARCHAR) AS ac_account_id
, CAST(CAST(ROUND(TRY_CAST(campaign_id AS DOUBLE )) AS BIGINT) AS VARCHAR) AS cm_campaign_id
, CAST(CAST(ROUND(TRY_CAST(insertion_order_id AS DOUBLE )) AS BIGINT) AS VARCHAR) AS un_universal_id
, SUM( TRY_CAST(media_cost_advertiser_currency AS DOUBLE PRECISION)) AS spend_local
, SUM( CAST( ROUND( TRY_CAST(impressions AS DOUBLE )) AS BIGINT )) AS impressions
, SUM( CAST( ROUND( TRY_CAST(clicks AS DOUBLE )) AS BIGINT )) AS clicks
, SUM( CAST( ROUND( TRY_CAST(engagements AS DOUBLE )) AS BIGINT )) AS engagements
, SUM( TRY_CAST(null AS DOUBLE )) AS reach
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS likes
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS shares
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS comments
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS visits
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS visits_unique
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS users_unique
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS quality_arrivals
, SUM( CAST( ROUND( TRY_CAST(starts_video AS DOUBLE )) AS BIGINT )) AS video_played_start
, SUM( CAST( ROUND( TRY_CAST(first_quartile_views_video AS DOUBLE )) AS BIGINT )) AS video_played_25
, SUM( CAST( ROUND( TRY_CAST(midpoint_views_video AS DOUBLE )) AS BIGINT )) AS video_played_50
, SUM( CAST( ROUND( TRY_CAST(third_quartile_views_video AS DOUBLE )) AS BIGINT )) AS video_played_75
, SUM( CAST( ROUND( TRY_CAST(complete_views_video AS DOUBLE )) AS BIGINT )) AS video_played_100
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS true_view
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS config_start
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS config_complete
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS config_complete_unique
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS config_trim_start
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS config_engine_start
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS config_exterior_start
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS config_interior_start
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS config_equipment_start
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS config_accessories_start
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_qualified
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_test_drive
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_finance_calculator
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_brochure_download
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_find_retailer
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_contact_us
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_info_request
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_dealer_contact_sent
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_newsletter
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_onfacebook
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_allfacebook
FROM vwg_iceberg.vwg_raw.vwb_dv3_raw_performance raw
WHERE TRUE
AND CAST(date AS DATE)
BETWEEN DATE_ADD('day', -1 * 31, current_timestamp) and current_timestamp
GROUP BY 1,2,3,4,5,6,7,8,9,10,11
)
,
airbyte_fact_aud AS (
SELECT DISTINCT
CAST(current_timestamp AS TIMESTAMP(6) WITH TIME ZONE) AS updated_timestamp
, CAST('dv3'
|| '_' || COALESCE(CAST(CAST(ROUND(TRY_CAST(advertiser_id AS DOUBLE )) AS BIGINT) AS VARCHAR), 'NA')
|| '_' || COALESCE(CAST(CAST(ROUND(TRY_CAST(campaign_id AS DOUBLE )) AS BIGINT) AS VARCHAR), 'NA')
|| '_' || COALESCE(CAST(CAST(ROUND(TRY_CAST(insertion_order_id AS DOUBLE )) AS BIGINT) AS VARCHAR), 'NA')
|| '_' || DATE_FORMAT(CAST(date AS DATE), '%Y%m%d') AS VARCHAR ) AS composite_fact_key
,
'aud'
AS brand_key
, 'dv3' AS platform_key
, 'dv3' || '_' || COALESCE(CAST(CAST(ROUND(TRY_CAST(advertiser_id AS DOUBLE )) AS BIGINT) AS VARCHAR), 'NA') AS platform_account_key
, 'dv3' || '_' || COALESCE(CAST(CAST(ROUND(TRY_CAST(campaign_id AS DOUBLE )) AS BIGINT) AS VARCHAR), 'NA') AS platform_campaign_key
, 'dv3' || '_' || COALESCE(CAST(CAST(ROUND(TRY_CAST(insertion_order_id AS DOUBLE )) AS BIGINT) AS VARCHAR), 'NA') AS platform_universal_key
, CAST(date AS DATE) AS date
, CAST(CAST(ROUND(TRY_CAST(advertiser_id AS DOUBLE )) AS BIGINT) AS VARCHAR) AS ac_account_id
, CAST(CAST(ROUND(TRY_CAST(campaign_id AS DOUBLE )) AS BIGINT) AS VARCHAR) AS cm_campaign_id
, CAST(CAST(ROUND(TRY_CAST(insertion_order_id AS DOUBLE )) AS BIGINT) AS VARCHAR) AS un_universal_id
, SUM( TRY_CAST(media_cost_advertiser_currency AS DOUBLE PRECISION)) AS spend_local
, SUM( CAST( ROUND( TRY_CAST(impressions AS DOUBLE )) AS BIGINT )) AS impressions
, SUM( CAST( ROUND( TRY_CAST(clicks AS DOUBLE )) AS BIGINT )) AS clicks
, SUM( CAST( ROUND( TRY_CAST(engagements AS DOUBLE )) AS BIGINT )) AS engagements
, SUM( TRY_CAST(null AS DOUBLE )) AS reach
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS likes
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS shares
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS comments
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS visits
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS visits_unique
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS users_unique
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS quality_arrivals
, SUM( CAST( ROUND( TRY_CAST(starts_video AS DOUBLE )) AS BIGINT )) AS video_played_start
, SUM( CAST( ROUND( TRY_CAST(first_quartile_views_video AS DOUBLE )) AS BIGINT )) AS video_played_25
, SUM( CAST( ROUND( TRY_CAST(midpoint_views_video AS DOUBLE )) AS BIGINT )) AS video_played_50
, SUM( CAST( ROUND( TRY_CAST(third_quartile_views_video AS DOUBLE )) AS BIGINT )) AS video_played_75
, SUM( CAST( ROUND( TRY_CAST(complete_views_video AS DOUBLE )) AS BIGINT )) AS video_played_100
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS true_view
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS config_start
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS config_complete
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS config_complete_unique
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS config_trim_start
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS config_engine_start
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS config_exterior_start
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS config_interior_start
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS config_equipment_start
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS config_accessories_start
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_qualified
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_test_drive
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_finance_calculator
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_brochure_download
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_find_retailer
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_contact_us
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_info_request
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_dealer_contact_sent
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_newsletter
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_onfacebook
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_allfacebook
FROM vwg_iceberg.vwg_raw.aud_dv3_raw_performance raw
WHERE TRUE
AND CAST(date AS DATE)
BETWEEN DATE_ADD('day', -1 * 31, current_timestamp) and current_timestamp
GROUP BY 1,2,3,4,5,6,7,8,9,10,11
)
,
airbyte_fact_por AS (
SELECT DISTINCT
CAST(current_timestamp AS TIMESTAMP(6) WITH TIME ZONE) AS updated_timestamp
, CAST('dv3'
|| '_' || COALESCE(CAST(CAST(ROUND(TRY_CAST(advertiser_id AS DOUBLE )) AS BIGINT) AS VARCHAR), 'NA')
|| '_' || COALESCE(CAST(CAST(ROUND(TRY_CAST(campaign_id AS DOUBLE )) AS BIGINT) AS VARCHAR), 'NA')
|| '_' || COALESCE(CAST(CAST(ROUND(TRY_CAST(insertion_order_id AS DOUBLE )) AS BIGINT) AS VARCHAR), 'NA')
|| '_' || DATE_FORMAT(CAST(date AS DATE), '%Y%m%d') AS VARCHAR ) AS composite_fact_key
,
'por'
AS brand_key
, 'dv3' AS platform_key
, 'dv3' || '_' || COALESCE(CAST(CAST(ROUND(TRY_CAST(advertiser_id AS DOUBLE )) AS BIGINT) AS VARCHAR), 'NA') AS platform_account_key
, 'dv3' || '_' || COALESCE(CAST(CAST(ROUND(TRY_CAST(campaign_id AS DOUBLE )) AS BIGINT) AS VARCHAR), 'NA') AS platform_campaign_key
, 'dv3' || '_' || COALESCE(CAST(CAST(ROUND(TRY_CAST(insertion_order_id AS DOUBLE )) AS BIGINT) AS VARCHAR), 'NA') AS platform_universal_key
, CAST(date AS DATE) AS date
, CAST(CAST(ROUND(TRY_CAST(advertiser_id AS DOUBLE )) AS BIGINT) AS VARCHAR) AS ac_account_id
, CAST(CAST(ROUND(TRY_CAST(campaign_id AS DOUBLE )) AS BIGINT) AS VARCHAR) AS cm_campaign_id
, CAST(CAST(ROUND(TRY_CAST(insertion_order_id AS DOUBLE )) AS BIGINT) AS VARCHAR) AS un_universal_id
, SUM( TRY_CAST(media_cost_advertiser_currency AS DOUBLE PRECISION)) AS spend_local
, SUM( CAST( ROUND( TRY_CAST(impressions AS DOUBLE )) AS BIGINT )) AS impressions
, SUM( CAST( ROUND( TRY_CAST(clicks AS DOUBLE )) AS BIGINT )) AS clicks
, SUM( CAST( ROUND( TRY_CAST(engagements AS DOUBLE )) AS BIGINT )) AS engagements
, SUM( TRY_CAST(null AS DOUBLE )) AS reach
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS likes
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS shares
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS comments
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS visits
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS visits_unique
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS users_unique
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS quality_arrivals
, SUM( CAST( ROUND( TRY_CAST(starts_video AS DOUBLE )) AS BIGINT )) AS video_played_start
, SUM( CAST( ROUND( TRY_CAST(first_quartile_views_video AS DOUBLE )) AS BIGINT )) AS video_played_25
, SUM( CAST( ROUND( TRY_CAST(midpoint_views_video AS DOUBLE )) AS BIGINT )) AS video_played_50
, SUM( CAST( ROUND( TRY_CAST(third_quartile_views_video AS DOUBLE )) AS BIGINT )) AS video_played_75
, SUM( CAST( ROUND( TRY_CAST(complete_views_video AS DOUBLE )) AS BIGINT )) AS video_played_100
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS true_view
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS config_start
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS config_complete
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS config_complete_unique
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS config_trim_start
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS config_engine_start
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS config_exterior_start
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS config_interior_start
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS config_equipment_start
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS config_accessories_start
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_qualified
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_test_drive
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_finance_calculator
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_brochure_download
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_find_retailer
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_contact_us
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_info_request
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_dealer_contact_sent
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_newsletter
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_onfacebook
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_allfacebook
FROM vwg_iceberg.vwg_raw.por_dv3_raw_performance raw
WHERE TRUE
AND CAST(date AS DATE)
BETWEEN DATE_ADD('day', -1 * 31, current_timestamp) and current_timestamp
GROUP BY 1,2,3,4,5,6,7,8,9,10,11
)
,
airbyte_fact_sea AS (
SELECT DISTINCT
CAST(current_timestamp AS TIMESTAMP(6) WITH TIME ZONE) AS updated_timestamp
, CAST('dv3'
|| '_' || COALESCE(CAST(CAST(ROUND(TRY_CAST(advertiser_id AS DOUBLE )) AS BIGINT) AS VARCHAR), 'NA')
|| '_' || COALESCE(CAST(CAST(ROUND(TRY_CAST(campaign_id AS DOUBLE )) AS BIGINT) AS VARCHAR), 'NA')
|| '_' || COALESCE(CAST(CAST(ROUND(TRY_CAST(insertion_order_id AS DOUBLE )) AS BIGINT) AS VARCHAR), 'NA')
|| '_' || DATE_FORMAT(CAST(date AS DATE), '%Y%m%d') AS VARCHAR ) AS composite_fact_key
,
'sea'
AS brand_key
, 'dv3' AS platform_key
, 'dv3' || '_' || COALESCE(CAST(CAST(ROUND(TRY_CAST(advertiser_id AS DOUBLE )) AS BIGINT) AS VARCHAR), 'NA') AS platform_account_key
, 'dv3' || '_' || COALESCE(CAST(CAST(ROUND(TRY_CAST(campaign_id AS DOUBLE )) AS BIGINT) AS VARCHAR), 'NA') AS platform_campaign_key
, 'dv3' || '_' || COALESCE(CAST(CAST(ROUND(TRY_CAST(insertion_order_id AS DOUBLE )) AS BIGINT) AS VARCHAR), 'NA') AS platform_universal_key
, CAST(date AS DATE) AS date
, CAST(CAST(ROUND(TRY_CAST(advertiser_id AS DOUBLE )) AS BIGINT) AS VARCHAR) AS ac_account_id
, CAST(CAST(ROUND(TRY_CAST(campaign_id AS DOUBLE )) AS BIGINT) AS VARCHAR) AS cm_campaign_id
, CAST(CAST(ROUND(TRY_CAST(insertion_order_id AS DOUBLE )) AS BIGINT) AS VARCHAR) AS un_universal_id
, SUM( TRY_CAST(media_cost_advertiser_currency AS DOUBLE PRECISION)) AS spend_local
, SUM( CAST( ROUND( TRY_CAST(impressions AS DOUBLE )) AS BIGINT )) AS impressions
, SUM( CAST( ROUND( TRY_CAST(clicks AS DOUBLE )) AS BIGINT )) AS clicks
, SUM( CAST( ROUND( TRY_CAST(engagements AS DOUBLE )) AS BIGINT )) AS engagements
, SUM( TRY_CAST(null AS DOUBLE )) AS reach
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS likes
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS shares
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS comments
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS visits
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS visits_unique
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS users_unique
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS quality_arrivals
, SUM( CAST( ROUND( TRY_CAST(starts_video AS DOUBLE )) AS BIGINT )) AS video_played_start
, SUM( CAST( ROUND( TRY_CAST(first_quartile_views_video AS DOUBLE )) AS BIGINT )) AS video_played_25
, SUM( CAST( ROUND( TRY_CAST(midpoint_views_video AS DOUBLE )) AS BIGINT )) AS video_played_50
, SUM( CAST( ROUND( TRY_CAST(third_quartile_views_video AS DOUBLE )) AS BIGINT )) AS video_played_75
, SUM( CAST( ROUND( TRY_CAST(complete_views_video AS DOUBLE )) AS BIGINT )) AS video_played_100
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS true_view
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS config_start
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS config_complete
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS config_complete_unique
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS config_trim_start
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS config_engine_start
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS config_exterior_start
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS config_interior_start
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS config_equipment_start
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS config_accessories_start
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_qualified
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_test_drive
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_finance_calculator
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_brochure_download
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_find_retailer
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_contact_us
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_info_request
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_dealer_contact_sent
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_newsletter
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_onfacebook
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_allfacebook
FROM vwg_iceberg.vwg_raw.sea_dv3_raw_performance raw
WHERE TRUE
AND CAST(date AS DATE)
BETWEEN DATE_ADD('day', -1 * 31, current_timestamp) and current_timestamp
GROUP BY 1,2,3,4,5,6,7,8,9,10,11
)
,
airbyte_fact_ben AS (
SELECT DISTINCT
CAST(current_timestamp AS TIMESTAMP(6) WITH TIME ZONE) AS updated_timestamp
, CAST('dv3'
|| '_' || COALESCE(CAST(CAST(ROUND(TRY_CAST(advertiser_id AS DOUBLE )) AS BIGINT) AS VARCHAR), 'NA')
|| '_' || COALESCE(CAST(CAST(ROUND(TRY_CAST(campaign_id AS DOUBLE )) AS BIGINT) AS VARCHAR), 'NA')
|| '_' || COALESCE(CAST(CAST(ROUND(TRY_CAST(insertion_order_id AS DOUBLE )) AS BIGINT) AS VARCHAR), 'NA')
|| '_' || DATE_FORMAT(CAST(date AS DATE), '%Y%m%d') AS VARCHAR ) AS composite_fact_key
,
'ben'
AS brand_key
, 'dv3' AS platform_key
, 'dv3' || '_' || COALESCE(CAST(CAST(ROUND(TRY_CAST(advertiser_id AS DOUBLE )) AS BIGINT) AS VARCHAR), 'NA') AS platform_account_key
, 'dv3' || '_' || COALESCE(CAST(CAST(ROUND(TRY_CAST(campaign_id AS DOUBLE )) AS BIGINT) AS VARCHAR), 'NA') AS platform_campaign_key
, 'dv3' || '_' || COALESCE(CAST(CAST(ROUND(TRY_CAST(insertion_order_id AS DOUBLE )) AS BIGINT) AS VARCHAR), 'NA') AS platform_universal_key
, CAST(date AS DATE) AS date
, CAST(CAST(ROUND(TRY_CAST(advertiser_id AS DOUBLE )) AS BIGINT) AS VARCHAR) AS ac_account_id
, CAST(CAST(ROUND(TRY_CAST(campaign_id AS DOUBLE )) AS BIGINT) AS VARCHAR) AS cm_campaign_id
, CAST(CAST(ROUND(TRY_CAST(insertion_order_id AS DOUBLE )) AS BIGINT) AS VARCHAR) AS un_universal_id
, SUM( TRY_CAST(media_cost_advertiser_currency AS DOUBLE PRECISION)) AS spend_local
, SUM( CAST( ROUND( TRY_CAST(impressions AS DOUBLE )) AS BIGINT )) AS impressions
, SUM( CAST( ROUND( TRY_CAST(clicks AS DOUBLE )) AS BIGINT )) AS clicks
, SUM( CAST( ROUND( TRY_CAST(engagements AS DOUBLE )) AS BIGINT )) AS engagements
, SUM( TRY_CAST(null AS DOUBLE )) AS reach
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS likes
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS shares
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS comments
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS visits
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS visits_unique
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS users_unique
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS quality_arrivals
, SUM( CAST( ROUND( TRY_CAST(starts_video AS DOUBLE )) AS BIGINT )) AS video_played_start
, SUM( CAST( ROUND( TRY_CAST(first_quartile_views_video AS DOUBLE )) AS BIGINT )) AS video_played_25
, SUM( CAST( ROUND( TRY_CAST(midpoint_views_video AS DOUBLE )) AS BIGINT )) AS video_played_50
, SUM( CAST( ROUND( TRY_CAST(third_quartile_views_video AS DOUBLE )) AS BIGINT )) AS video_played_75
, SUM( CAST( ROUND( TRY_CAST(complete_views_video AS DOUBLE )) AS BIGINT )) AS video_played_100
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS true_view
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS config_start
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS config_complete
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS config_complete_unique
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS config_trim_start
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS config_engine_start
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS config_exterior_start
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS config_interior_start
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS config_equipment_start
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS config_accessories_start
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_qualified
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_test_drive
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_finance_calculator
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_brochure_download
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_find_retailer
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_contact_us
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_info_request
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_dealer_contact_sent
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_newsletter
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_onfacebook
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_allfacebook
FROM vwg_iceberg.vwg_raw.ben_dv3_raw_performance raw
WHERE TRUE
AND CAST(date AS DATE)
BETWEEN DATE_ADD('day', -1 * 31, current_timestamp) and current_timestamp
GROUP BY 1,2,3,4,5,6,7,8,9,10,11
)
,
airbyte_fact_sko AS (
SELECT DISTINCT
CAST(current_timestamp AS TIMESTAMP(6) WITH TIME ZONE) AS updated_timestamp
, CAST('dv3'
|| '_' || COALESCE(CAST(CAST(ROUND(TRY_CAST(advertiser_id AS DOUBLE )) AS BIGINT) AS VARCHAR), 'NA')
|| '_' || COALESCE(CAST(CAST(ROUND(TRY_CAST(campaign_id AS DOUBLE )) AS BIGINT) AS VARCHAR), 'NA')
|| '_' || COALESCE(CAST(CAST(ROUND(TRY_CAST(insertion_order_id AS DOUBLE )) AS BIGINT) AS VARCHAR), 'NA')
|| '_' || DATE_FORMAT(CAST(date AS DATE), '%Y%m%d') AS VARCHAR ) AS composite_fact_key
,
'sko'
AS brand_key
, 'dv3' AS platform_key
, 'dv3' || '_' || COALESCE(CAST(CAST(ROUND(TRY_CAST(advertiser_id AS DOUBLE )) AS BIGINT) AS VARCHAR), 'NA') AS platform_account_key
, 'dv3' || '_' || COALESCE(CAST(CAST(ROUND(TRY_CAST(campaign_id AS DOUBLE )) AS BIGINT) AS VARCHAR), 'NA') AS platform_campaign_key
, 'dv3' || '_' || COALESCE(CAST(CAST(ROUND(TRY_CAST(insertion_order_id AS DOUBLE )) AS BIGINT) AS VARCHAR), 'NA') AS platform_universal_key
, CAST(date AS DATE) AS date
, CAST(CAST(ROUND(TRY_CAST(advertiser_id AS DOUBLE )) AS BIGINT) AS VARCHAR) AS ac_account_id
, CAST(CAST(ROUND(TRY_CAST(campaign_id AS DOUBLE )) AS BIGINT) AS VARCHAR) AS cm_campaign_id
, CAST(CAST(ROUND(TRY_CAST(insertion_order_id AS DOUBLE )) AS BIGINT) AS VARCHAR) AS un_universal_id
, SUM( TRY_CAST(media_cost_advertiser_currency AS DOUBLE PRECISION)) AS spend_local
, SUM( CAST( ROUND( TRY_CAST(impressions AS DOUBLE )) AS BIGINT )) AS impressions
, SUM( CAST( ROUND( TRY_CAST(clicks AS DOUBLE )) AS BIGINT )) AS clicks
, SUM( CAST( ROUND( TRY_CAST(engagements AS DOUBLE )) AS BIGINT )) AS engagements
, SUM( TRY_CAST(null AS DOUBLE )) AS reach
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS likes
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS shares
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS comments
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS visits
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS visits_unique
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS users_unique
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS quality_arrivals
, SUM( CAST( ROUND( TRY_CAST(starts_video AS DOUBLE )) AS BIGINT )) AS video_played_start
, SUM( CAST( ROUND( TRY_CAST(first_quartile_views_video AS DOUBLE )) AS BIGINT )) AS video_played_25
, SUM( CAST( ROUND( TRY_CAST(midpoint_views_video AS DOUBLE )) AS BIGINT )) AS video_played_50
, SUM( CAST( ROUND( TRY_CAST(third_quartile_views_video AS DOUBLE )) AS BIGINT )) AS video_played_75
, SUM( CAST( ROUND( TRY_CAST(complete_views_video AS DOUBLE )) AS BIGINT )) AS video_played_100
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS true_view
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS config_start
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS config_complete
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS config_complete_unique
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS config_trim_start
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS config_engine_start
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS config_exterior_start
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS config_interior_start
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS config_equipment_start
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS config_accessories_start
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_qualified
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_test_drive
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_finance_calculator
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_brochure_download
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_find_retailer
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_contact_us
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_info_request
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_dealer_contact_sent
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_newsletter
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_onfacebook
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_allfacebook
FROM vwg_iceberg.vwg_raw.sko_dv3_raw_performance raw
WHERE TRUE
AND CAST(date AS DATE)
BETWEEN DATE_ADD('day', -1 * 31, current_timestamp) and current_timestamp
GROUP BY 1,2,3,4,5,6,7,8,9,10,11
)
,
airbyte_fact_lcv AS (
SELECT DISTINCT
CAST(current_timestamp AS TIMESTAMP(6) WITH TIME ZONE) AS updated_timestamp
, CAST('dv3'
|| '_' || COALESCE(CAST(CAST(ROUND(TRY_CAST(advertiser_id AS DOUBLE )) AS BIGINT) AS VARCHAR), 'NA')
|| '_' || COALESCE(CAST(CAST(ROUND(TRY_CAST(campaign_id AS DOUBLE )) AS BIGINT) AS VARCHAR), 'NA')
|| '_' || COALESCE(CAST(CAST(ROUND(TRY_CAST(insertion_order_id AS DOUBLE )) AS BIGINT) AS VARCHAR), 'NA')
|| '_' || DATE_FORMAT(CAST(date AS DATE), '%Y%m%d') AS VARCHAR ) AS composite_fact_key
,
'lcv'
AS brand_key
, 'dv3' AS platform_key
, 'dv3' || '_' || COALESCE(CAST(CAST(ROUND(TRY_CAST(advertiser_id AS DOUBLE )) AS BIGINT) AS VARCHAR), 'NA') AS platform_account_key
, 'dv3' || '_' || COALESCE(CAST(CAST(ROUND(TRY_CAST(campaign_id AS DOUBLE )) AS BIGINT) AS VARCHAR), 'NA') AS platform_campaign_key
, 'dv3' || '_' || COALESCE(CAST(CAST(ROUND(TRY_CAST(insertion_order_id AS DOUBLE )) AS BIGINT) AS VARCHAR), 'NA') AS platform_universal_key
, CAST(date AS DATE) AS date
, CAST(CAST(ROUND(TRY_CAST(advertiser_id AS DOUBLE )) AS BIGINT) AS VARCHAR) AS ac_account_id
, CAST(CAST(ROUND(TRY_CAST(campaign_id AS DOUBLE )) AS BIGINT) AS VARCHAR) AS cm_campaign_id
, CAST(CAST(ROUND(TRY_CAST(insertion_order_id AS DOUBLE )) AS BIGINT) AS VARCHAR) AS un_universal_id
, SUM( TRY_CAST(media_cost_advertiser_currency AS DOUBLE PRECISION)) AS spend_local
, SUM( CAST( ROUND( TRY_CAST(impressions AS DOUBLE )) AS BIGINT )) AS impressions
, SUM( CAST( ROUND( TRY_CAST(clicks AS DOUBLE )) AS BIGINT )) AS clicks
, SUM( CAST( ROUND( TRY_CAST(engagements AS DOUBLE )) AS BIGINT )) AS engagements
, SUM( TRY_CAST(null AS DOUBLE )) AS reach
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS likes
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS shares
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS comments
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS visits
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS visits_unique
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS users_unique
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS quality_arrivals
, SUM( CAST( ROUND( TRY_CAST(starts_video AS DOUBLE )) AS BIGINT )) AS video_played_start
, SUM( CAST( ROUND( TRY_CAST(first_quartile_views_video AS DOUBLE )) AS BIGINT )) AS video_played_25
, SUM( CAST( ROUND( TRY_CAST(midpoint_views_video AS DOUBLE )) AS BIGINT )) AS video_played_50
, SUM( CAST( ROUND( TRY_CAST(third_quartile_views_video AS DOUBLE )) AS BIGINT )) AS video_played_75
, SUM( CAST( ROUND( TRY_CAST(complete_views_video AS DOUBLE )) AS BIGINT )) AS video_played_100
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS true_view
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS config_start
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS config_complete
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS config_complete_unique
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS config_trim_start
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS config_engine_start
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS config_exterior_start
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS config_interior_start
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS config_equipment_start
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS config_accessories_start
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_qualified
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_test_drive
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_finance_calculator
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_brochure_download
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_find_retailer
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_contact_us
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_info_request
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_dealer_contact_sent
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_newsletter
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_onfacebook
, SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_allfacebook
FROM vwg_iceberg.vwg_raw.lcv_dv3_raw_performance raw
WHERE TRUE
AND CAST(date AS DATE)
BETWEEN DATE_ADD('day', -1 * 31, current_timestamp) and current_timestamp
GROUP BY 1,2,3,4,5,6,7,8,9,10,11
)
,
final_fact AS (
select * from airbyte_fact_vwb
UNION ALL
select * from airbyte_fact_aud
UNION ALL
select * from airbyte_fact_por
UNION ALL
select * from airbyte_fact_sea
UNION ALL
select * from airbyte_fact_ben
UNION ALL
select * from airbyte_fact_sko
UNION ALL
select * from airbyte_fact_lcv
),
redshift_fact AS (
SELECT redshift_raw.*
FROM vwg_iceberg.vwg_rawredshift.all_all_admin_redshiftfactperformance AS redshift_raw
LEFT JOIN
(
SELECT composite_fact_key FROM final_fact GROUP BY 1
) airbyte_fact
ON redshift_raw.composite_fact_key = airbyte_fact.composite_fact_key
WHERE
redshift_raw.platform_key = 'dv3'
AND redshift_raw.date
BETWEEN DATE_ADD('day', -1 * 31, current_timestamp) and current_timestamp
AND airbyte_fact.composite_fact_key IS NULL
)
SELECT fact.*
FROM
(
SELECT * FROM final_fact WHERE composite_fact_key IS NOT NULL
UNION ALL
SELECT * FROM redshift_fact WHERE composite_fact_key IS NOT NULL
) fact
INNER JOIN
(
SELECT
platform_account_key
, MAX(brand_key) AS brand_key
FROM
vwg_iceberg.vwg_silver.all_dv3_dim_account
GROUP BY 1
) ac
ON fact.platform_account_key = ac.platform_account_key
AND fact.brand_key = ac.brand_key
enLeave a Comment