Creating custom dimension based on part of a string

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

0 2 4,775
2 REPLIES 2

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 @George, it worked perfectly!

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:)’, ‘’) ;;

Top Labels in this Space
Top Solution Authors