Need your help to advise me on this one :).
I want to create a transactional dataset. This is my initial dataset
user_id date_key channel order_id
123 1 sep Affiliates -
123 2 sep Price Comp -
123 3 sep Direct 12342213
435 2 sep Organic -
435 7 sep Direct 45485485
And I want modify above table into a transactional dataset like this. It will show a user journey from its first visit into purchase (let say user_id 123 visit from Affiliates then Price Comp and Direct (ended up purchasing).
user_id channel_journey order_id
123 Affiliates - Price Comp - Direct 12342213
435 Organic - Direct 45485485
Do you guys know and advise how can I get into the second table?
Thanks in advance :)
Consider using STRING_AGG:
SELECT user_id, STRING_AGG(channel, ' - ' ORDER BY date_key), MAX(order_id)
FROM `dataset.table`
GROUP BY user_id