Missing dataset - marketing_ga4_v1_prod

[Jmingpt]

I can't find the dataset of marketing_ga4_v1_prod in my BigQuery after running the terraform:

One of the routine: `-- Copyright 2023 Google LLC

-- Licensed under the Apache License, Version 2.0 (the "License"); -- you may not use this file except in compliance with the License. -- You may obtain a copy of the License at

-- http://www.apache.org/licenses/LICENSE-2.0

-- Unless required by applicable law or agreed to in writing, software -- distributed under the License is distributed on an "AS IS" BASIS, -- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. -- See the License for the specific language governing permissions and -- limitations under the License.

-- Setting procedure to lookback from the day before input_date until the day before end_date SET input_date = DATE_SUB(input_date, INTERVAL 1 DAY); SET end_date = DATE_SUB(end_date, INTERVAL 1 DAY);

CREATE OR REPLACE TEMP TABLE user_dimensions_event_session_scoped as ( SELECT DISTINCT user_pseudo_id, LAST_VALUE(user_ltv_revenue) OVER(PARTITION BY user_pseudo_id, input_date ORDER BY event_timestamp DESC) AS user_ltv_revenue, LAST_VALUE(T.traffic_source_medium) OVER(PARTITION BY user_pseudo_id, input_date ORDER BY event_timestamp DESC) AS last_traffic_source_medium, LAST_VALUE(T.traffic_source_name) OVER(PARTITION BY user_pseudo_id, input_date ORDER BY event_timestamp DESC) AS last_traffic_source_name, LAST_VALUE(T.traffic_source) OVER(PARTITION BY user_pseudo_id, input_date ORDER BY event_timestamp DESC) AS last_traffic_source_source, LAST_VALUE(CASE WHEN (TIMESTAMP_DIFF(event_timestamp, user_first_touch_timestamp, DAY) < 7) OR (user_first_touch_timestamp IS NULL) THEN 'new' ELSE 'existing' END) OVER(PARTITION BY user_pseudo_id, input_date ORDER BY event_timestamp DESC) AS new_or_established_user, LAST_VALUE(L.subcontinent) OVER(PARTITION BY user_pseudo_id, input_date ORDER BY event_timestamp DESC) as geo_sub_continent, LAST_VALUE(L.country) OVER(PARTITION BY user_pseudo_id, input_date ORDER BY event_timestamp DESC) as geo_country, LAST_VALUE(L.region) OVER(PARTITION BY user_pseudo_id, input_date ORDER BY event_timestamp DESC) as geo_region, LAST_VALUE(L.city) OVER(PARTITION BY user_pseudo_id, input_date ORDER BY event_timestamp DESC) as geo_city, LAST_VALUE(L.metro) OVER(PARTITION BY user_pseudo_id, input_date ORDER BY event_timestamp DESC) as geo_metro, FIRST_VALUE(T.traffic_source_medium) OVER(PARTITION BY user_pseudo_id, input_date ORDER BY event_timestamp ASC) AS first_traffic_source_medium, FIRST_VALUE(T.traffic_source_name) OVER(PARTITION BY user_pseudo_id, input_date ORDER BY event_timestamp ASC) AS first_traffic_source_name, FIRST_VALUE(T.traffic_source) OVER(PARTITION BY user_pseudo_id, input_date ORDER BY event_timestamp ASC) AS first_traffic_source_source, MAX(CASE WHEN user_id IS NOT NULL THEN TRUE ELSE FALSE END) OVER(PARTITION BY user_pseudo_id, input_date ORDER BY event_timestamp ASC) AS has_signed_in_with_user_id, LAST_VALUE(device_category) OVER(PARTITION BY user_pseudo_id, input_date ORDER BY event_timestamp DESC) as device_category, LAST_VALUE(device_mobile_brand_name) OVER(PARTITION BY user_pseudo_id, input_date ORDER BY event_timestamp DESC) as device_mobile_brand_name, LAST_VALUE(device_mobile_model_name) OVER(PARTITION BY user_pseudo_id, input_date ORDER BY event_timestamp DESC) as device_mobile_model_name, LAST_VALUE(device_os) OVER(PARTITION BY user_pseudo_id, input_date ORDER BY event_timestamp DESC) as device_os, LAST_VALUE(language) OVER(PARTITION BY user_pseudo_id, input_date ORDER BY event_timestamp DESC) AS device_language, LAST_VALUE(device_web_browser) OVER(PARTITION BY user_pseudo_id, input_date ORDER BY event_timestamp DESC) as device_web_browser, LAST_VALUE(device_advertising_id) OVER(PARTITION BY user_pseudo_id, input_date ORDER BY event_timestamp DESC) as device_advertising_id FROM pt-ma-project.marketing_ga4_v1_prod.event as E INNER JOIN pt-ma-project.marketing_ga4_v1_prod.traffic_source as T ON E.traffic_source_id = T.traffic_source_id INNER JOIN pt-ma-project.marketing_ga4_v1_prod.device as D ON E.device_type_id = D.device_type_id INNER JOIN pt-ma-project.marketing_ga4_v1_prod.location as L ON E.location_id = L.location_id WHERE event_date BETWEEN end_date AND input_date AND ga_session_id IS NOT NULL AND D.device_os IS NOT NULL );

-- All users in the platform CREATE OR REPLACE TEMP TABLE events_users as ( SELECT DISTINCT Users.user_pseudo_id, Users.user_id, FROM pt-ma-project.marketing_ga4_v1_prod.event Users INNER JOIN pt-ma-project.marketing_ga4_v1_prod.device as D ON Users.device_type_id = D.device_type_id WHERE ga_session_id IS NOT NULL AND device_os IS NOT NULL AND event_date BETWEEN end_date AND input_date );

CREATE OR REPLACE TEMP TABLE DataForTargetTable AS SELECT DISTINCT CURRENT_TIMESTAMP() AS processed_timestamp, input_date as feature_date, EU.user_pseudo_id, MAX(EU.user_id) as user_id, MAX(UDESS.user_ltv_revenue) as user_ltv_revenue, MAX(UDESS.device_category) as device_category, MAX(UDESS.device_mobile_brand_name) as device_mobile_brand_name, MAX(UDESS.device_mobile_model_name) as device_mobile_model_name, MAX(UDESS.device_os) as device_os, MAX(UDESS.device_language) as device_language, MAX(UDESS.device_web_browser) as device_web_browser, APPROX_TOP_COUNT(UDESS.geo_sub_continent, 1)[OFFSET(0)].value as geo_sub_continent, APPROX_TOP_COUNT(UDESS.geo_country, 1)[OFFSET(0)].value as geo_country, APPROX_TOP_COUNT(UDESS.geo_region, 1)[OFFSET(0)].value as geo_region, APPROX_TOP_COUNT(UDESS.geo_city, 1)[OFFSET(0)].value as geo_city, APPROX_TOP_COUNT(UDESS.geo_metro, 1)[OFFSET(0)].value as geo_metro, MAX(UDESS.last_traffic_source_medium) as last_traffic_source_medium, MAX(UDESS.last_traffic_source_name) as last_traffic_source_name, MAX(UDESS.last_traffic_source_source) as last_traffic_source_source, MAX(UDESS.first_traffic_source_medium) as first_traffic_source_medium, MAX(UDESS.first_traffic_source_name) as first_traffic_source_name, MAX(UDESS.first_traffic_source_source) as first_traffic_source_source, MAX(UDESS.has_signed_in_with_user_id) as has_signed_in_with_user_id FROM events_users EU INNER JOIN user_dimensions_event_session_scoped UDESS ON EU.user_pseudo_id = UDESS.user_pseudo_id --AND EU.user_id = UDESS.user_id GROUP BY input_date, EU.user_pseudo_id ;

MERGE pt-ma-project.feature_store.user_dimensions I USING DataForTargetTable T ON I.feature_date = T.feature_date AND I.user_pseudo_id = T.user_pseudo_id WHEN MATCHED THEN UPDATE SET I.processed_timestamp = T.processed_timestamp, I.user_id = T.user_id, I.user_ltv_revenue = T.user_ltv_revenue, I.device_category = T.device_category, I.device_mobile_brand_name = T.device_mobile_brand_name, I.device_mobile_model_name = T.device_mobile_model_name, I.device_os = T.device_os, I.device_language = T.device_language, I.device_web_browser = T.device_web_browser, I.geo_sub_continent = T.geo_sub_continent, I.geo_country = T.geo_country, I.geo_region = T.geo_region, I.geo_city = T.geo_city, I.geo_metro = T.geo_metro, I.last_traffic_source_medium = T.last_traffic_source_medium, I.last_traffic_source_name = T.last_traffic_source_name, I.last_traffic_source_source = T.last_traffic_source_source, I.first_traffic_source_medium = T.first_traffic_source_medium, I.first_traffic_source_name = T.first_traffic_source_name, I.first_traffic_source_source = T.first_traffic_source_source, I.has_signed_in_with_user_id = T.has_signed_in_with_user_id WHEN NOT MATCHED THEN INSERT (processed_timestamp, feature_date, user_pseudo_id, user_id, user_ltv_revenue, device_category, device_mobile_brand_name, device_mobile_model_name, device_os, device_language, device_web_browser, geo_sub_continent, geo_country, geo_region, geo_city, geo_metro, last_traffic_source_medium, last_traffic_source_name, last_traffic_source_source, first_traffic_source_medium, first_traffic_source_name, first_traffic_source_source, has_signed_in_with_user_id) VALUES (T.processed_timestamp, T.feature_date, T.user_pseudo_id, T.user_id, T.user_ltv_revenue, T.device_category, T.device_mobile_brand_name, T.device_mobile_model_name, T.device_os, T.device_language, T.device_web_browser, T.geo_sub_continent, T.geo_country, T.geo_region, T.geo_city, T.geo_metro, T.last_traffic_source_medium, T.last_traffic_source_name, T.last_traffic_source_source, T.first_traffic_source_medium, T.first_traffic_source_name, T.first_traffic_source_source, T.has_signed_in_with_user_id) ;

SET rows_added = (SELECT COUNT(DISTINCT user_pseudo_id) FROM pt-ma-project.feature_store.user_dimensions);`

[chmstimoteo]

Hi @Jmingpt , please check the post-installation instructions.

Double check that your Data Store is successfully executed and data has been populated.

[chmstimoteo]

@Jmingpt any news on this issue?

[chmstimoteo]

Follow this troubleshooting guide to understand why Dataform Execution has failed: https://github.com/GoogleCloudPlatform/marketing-analytics-jumpstart/blob/main/docs/data_store.md#monitoring--troubleshooting

[chmstimoteo]

@Jmingpt I'm closing this issue, since it is stalled for over 2 months.