create or replace table `reby-cloud.reby_marketing_eu.user_360` as
select
created_at,
meta_user_id,
type,
concat(
'amount:',if(amount is null,'na',cast(amount as string)),'; ',
'minutes:',if(minutes is null,'na',cast(minutes as string)),'; ',
'service_area:',if(service_area is null, 'na',service_area),'; ',
'vehicle_type:',if(vehicle_type is null, 'na',vehicle_type),'; ',
'app_used:',if(app_promoting_co is null, 'na',app_promoting_co),'; ',
'vehicle_co:',if(owner_co is null,'na',owner_co),'; ',
'topup_through:',if(topup_trough_co is null, 'na',topup_trough_co)
) as details
from `reby-cloud.analytics_reby_v1_eu.py_ridestatus_combined` pyrsc
where type not in ('vehicle_reservation')
union all
SELECT
timestamp(created_at) as created_at,
user_id as meta_user_id,
'cx-conversation' as type,
concat (
'conversation_id:',conversation_id,'; ',
'conact_reason1:',if(cantact_reason_level1 is null, 'na',cantact_reason_level1),'; ',
'conact_reason2:',if(cantact_reason_level2 is null, 'na',cantact_reason_level2)
) as details
FROM `reby-cloud.kustomer_eu.conversation_messages_combination`
union all
select
created_at,
user_id as meta_user_id,
'user-created' as type,
concat ('company:',co.name) as details
from `reby-cloud.reby_marketing_eu.pg_company_user` cu
left join `reby-cloud.analytics_reby_v1_eu.pg_company` co on cu.company_id = co.id
Comments