Hi
I want to create custom dimensions based on the naming convention fo our imported marketing data.
If I for example have the dimension “campaign” on our table, that has the values of our marketing campaigns with a naming of “c:campaignname | b:brand | m:market | sc:source” from which I want to create 4 different dimensions, campaignname, brand, market and source.
essentially I want for the campaignname dimensions to be “everything after c: to the next |”
How would i best go about doing this?
regards
Alex
Hey,
One way you could do this is by using regex on your campaign dimension in the sql parameter of each custom dimension. I’m not sure about other data warehouses but this example works with BigQuery:
dimension: campaign {
type: string
sql: "c:campaignname | b:brand | m:market | sc:source" ;;
}
dimension: campaign_name {
type: string
sql: REGEXP_REPLACE(REGEXP_EXTRACT(${campaign}, r'(^c:\w+)'), r'(c:)', '') ;;
}
dimension: brand {
type: string
sql: REGEXP_REPLACE(REGEXP_EXTRACT(${campaign}, r'(b:\w+)'), r'(b:)', '') ;;
}
dimension: market {
type: string
sql: REGEXP_REPLACE(REGEXP_EXTRACT(${campaign}, r'(m:\w+)'), r'(m:)', '') ;;
}
dimension: source {
type: string
sql: REGEXP_REPLACE(REGEXP_EXTRACT(${campaign}, r'(sc:\w+)'), r'(sc:)', '') ;;
}
The regex will need tweaking slightly if your campaign name and other values have more than one word. Hope this helps!
Thank for that
I tweaked the regex slightly to the below, so it would work in all of our cases
dimension: cd_adsetmarket{
label: “CD_Ad Market”
type: string
sql: REGEXP_REPLACE(REGEXP_EXTRACT(${ad_name}, r’m:([^|]*)’), r’(m:)’, ‘’) ;;