0 Replies Latest reply: Dec 6, 2016 10:52 AM by Uldis Boldans RSS

    How to assign values based on a UTM code to an URL

    Uldis Boldans

      Hi,

       

      I am using QlikSense to analyse digital marketing channels. I am trying to identify different URLs (Table 1) by their sources based on predefined UTM codes (Table 2). For example, some URL should be classified as "SomeSource1" if it contains "source=A1".

       

      Table 1

      URL
      http://web.com/some_page/?utm_source=A1&utm_medium=banner&utm_campaign=campaign_name
      http://web.com/some_page/?utm_source=B2&utm_medium=banner&utm_campaign=campaign_name
      http://web.com/some_page/?utm_source=C3&utm_medium=banner&utm_campaign=campaign_name
      http://web.com/some_page/?utm_source=A123B456&utm_medium=banner&utm_campaign=campaign_name
      http://web.com/some_pag/?utm_source=D4&utm_medium=banner&utm_campaign=campaign_name

       

      Table 2

      SourceMatching criteria
      SomeSource1source=A1
      SomeSource1source=B2
      SomeSource3source=C3
      SomeSource5source=F6
      Other...

       

      What would be the most optimal way to assign values from "Table 2" in Column "Source" to values stored in column "URL"?


      Typically I would use a) mapping load or b) wildmatch. However, I cannot find a decent solution for them to work in this case:

      a) mapping load - "Matching criteria" does not match with the whole content in Table 1

      b) wildmatch - the number of sources is more than 100, but most importantly, new sources and matching criteria can be added or updated. Hence, it would be excellent if the source and matching criteria would not be hard-coded values in the loading scr

       

      Thanks for your help,

      Uldis