Untitled

Here is the schema for GA4_PROPERTY_MAPPING (its basically a view)


create or replace view GA4_PROPERTY_MAPPING(
	WEB_INFO_HOSTNAME,
	ABSTRACTED_HOSTNAME,
	ABSTRACTED_PROPERTYNAME,
	PROPERTY_NAME,
	PROPERTY_ID,
	PROPERTY_CODE,
	CLUSTER_NAME,
	MARKET_NAME,
	REGION_NAME
) as select 
	t1.web_info_hostname, t1.abstracted_hostname, 
    t2.abstracted_propertyname, t2.saddr1 as propertyname, t2.property_id, t2.property_code, t2.clustername CLUSTER_NAME, t2.marketname MARKET_NAME, t2.regionname REGION_NAME
from
	(
     select 
        DISTINCT WEB_INFO_HOSTNAME,
        replace(WEB_INFO_HOSTNAME, 'www.', '') as s1, 
        replace(s1, '.com', '') as s2,
        replace(s2, 'www-', '') as s3,
        replace(s3, '-com', '') as s4,
        replace(s4, '.translate.goog', '') as s5,
        replace(s5, '.securecafe', '') as abstracted_hostname
     from 
         SRC_GA4_PRD.RAW.SRC_RAW_EVENTS_PARSED
    	-- GID_ADF.GOOGLE_ANALYTICS_4.STG_RAW_EVENTS_PARSED
    ) t1
left join (
			SELECT 
            	saddr1, 
                lower(replace(trim(saddr1), ' ', '')) as abstracted_propertyname, 
                HMY as property_id,
                SCODE as property_code,
                --rtdm_P.propertyid,
                --rtdm_P.propertycode,
                --rtdm_P.propertyname,
                c.localized_naming clustername,
                rtdm_P.marketattribute marketname,
                rtdm_P.regionattribute regionname
            FROM SRC_YARDI_PRD.RAW.PROPERTY
            JOIN residential_tdm.model.property rtdm_P ON PROPERTY.hmy=rtdm_P.propertyid
            JOIN src_smartsheet_prd.clean.windsor_localized_from_funnel c on c.property_id = rtdm_P.propertycode
            where abstracted_propertyname is not null
           ) t2
on t1.abstracted_hostname = t2.abstracted_propertyname
;









Here is the schema for SRC_RAW_EVENTS_PARSED

              create or replace view SRC_RAW_EVENTS_PARSED(
	EVENT_DATE,
	EVENT_TIMESTAMP,
	EVENT_NAME,
	EVENT_PARAM_KEY,
	EVENT_PARAM_VALUE_STR,
	EVENT_PARAM_VALUE_INT,
	EVENT_PARAM_VALUE_FLOAT,
	EVENT_PARAM_VALUE_DOUBLE,
	EVENT_PREVIOUS_TIMESTAMP,
	EVENT_VALUE_IN_USD,
	EVENT_BUNDLE_SEQUENCE_ID,
	EVENT_SERVER_TIMESTAMP_OFFSET,
	USER_ID,
	USER_PSEUDO_ID,
	ANALYTICS_STORAGE,
	ADS_STORAGE,
	USES_TRANSIENT_TOKEN,
	USER_PROPERTY_KEY,
	USER_PROPERTY_VALUE_STR,
	USER_PROPERTY_VALUE_INT,
	USER_PROPERTY_VALUE_FLOAT,
	USER_PROPERTY_VALUE_DOUBLE,
	USER_FIRST_TOUCH_TIMESTAMP,
	REVENUE,
	CURRENCY,
	CATEGORY,
	MOBILE_BRAND_NAME,
	MOBILE_MODEL_NAME,
	MOBILE_MARKETING_NAME,
	MOBILE_OS_HARDWARE_MODEL,
	OPERATING_SYSTEM,
	OPERATING_SYSTEM_VERSION,
	VENDOR_ID,
	ADVERTISING_ID,
	LANGUAGE,
	IS_LIMITED_AD_TRACKING,
	TIME_ZONE_OFFSET_SECONDS,
	BROWSER,
	BROWSER_VERSION,
	WEB_INFO_BROWSER,
	WEB_INFO_BROWSER_VERSION,
	WEB_INFO_HOSTNAME,
	CONTINENT,
	COUNTRY,
	REGION,
	CITY,
	SUB_CONTINENT,
	METRO,
	APP_INFO_ID,
	APP_INFO_VERSION,
	APP_INFO_INSTALL_STORE,
	APP_INFO_FIREBASE_APP_ID,
	APP_INFO_INSTALL_SOURCE,
	TRAFFIC_SOURCE_NAME,
	TRAFFIC_SOURCE_MEDIUM,
	TRAFFIC_SOURCE_SOURCE,
	STREAM_ID,
	PLATFORM,
	EVENT_DIMENSION_HOSTNAME,
	TOTAL_ITEM_QUANTITY,
	PURCHASE_REVENUE_IN_USD,
	PURCHASE_REVENUE,
	REFUND_VALUE_IN_USD,
	REFUND_VALUE,
	SHIPPING_VALUE_IN_USD,
	SHIPPING_VALUE,
	TAX_VALUE_IN_USD,
	TAX_VALUE,
	UNIQUE_ITEMS,
	TRANSACTION_ID,
	ITEMS
) as select * from GID_ADF.GOOGLE_ANALYTICS_4.STG_RAW_EVENTS_PARSED;