As a first step, can you please provide the result of the below query.
SELECT eventparams.key,
eventparams.value.string_value, * FROM “scbpt_dl_persistent”.“ga_dib_history”
CROSS JOIN UNNEST(event_params) as t(eventparams)
where key='page_location' and string_value like '%sc_camp%'
limit 1000
SELECT eventparams.key,
eventparams.value.string_value,
split(eventparams.value.string_value,'sc_camp=',1) as sc_camp_value,
* FROM “scbpt_dl_persistent”.“ga_dib_history”
CROSS JOIN UNNEST(event_params) as t(eventparams)
where key='page_location' and string_value like '%sc_camp%'
limit 1000
Hi @Siriphon - You have a * in the sql. Can you run the below query.
SELECT eventparams.key,
eventparams.value.string_value,
split(eventparams.value.string_value,'sc_camp=',1) as sc_camp_value
FROM “scbpt_dl_persistent”.“ga_dib_history”
CROSS JOIN UNNEST(event_params) as t(eventparams)
where key='page_location' and string_value like '%sc_camp%'
limit 1000
Hi @Siriphon - Thanks. Can you please run the below query. Since string_value is a big one, it is not showing , Let’s see run the below query.
SELECT eventparams.key,
split(eventparams.value.string_value,'sc_camp=',1) as sc_camp_value
FROM “scbpt_dl_persistent”.“ga_dib_history”
CROSS JOIN UNNEST(event_params) as t(eventparams)
where key='page_location' and string_value like '%sc_camp%'
limit 1000
Hi @Siriphon - In case of it is not working, is it possible to create a new data set with the custom sql and click apply and see whether the result is coming or not.
Hi @Siriphon - Thanks. Can you do the below query.
SELECT eventparams.key,
split(eventparams.value.string_value,'sc_camp=',2) as sc_camp_value
FROM “scbpt_dl_persistent”.“ga_dib_history”
CROSS JOIN UNNEST(event_params) as t(eventparams)
where key='page_location' and string_value like '%sc_camp%'
limit 1000
If it is not working, there is a second option, can we create a calculated field. to do the same.
Step -1: In the data set page, click Add, Add a calculated field.
Step -2: Paste the below formula.
split({string_value},'sc_camp=',2)
and click Save. See the below screenshot for the same.