none
How to parse Google Analytics __utmz cookies RRS feed

  • Question

  • Hi!

    I have table with Google Analytics __utmz cookies and I want to parse it.

    Below just 2 examples of data.

    257054494.1446747210.2.2.utmcsr=google|utmgclid=CKneu6vxcgCFVIXHwodroC9g|utmccn=DP_UK_Brand_Search|utmcmd=cpc|utmctr=[company]|utmcct=company_exact

    257054494.1446537454.1.1.utmgclid=CLOcu_bj88gCFSjlwgodJrEA7w|utmccn=(not set)|utmcmd=(not set)

    I faced issue that I can't rely on order of utm tags and sometimes some tags are missed.

    Please help me with PQ code that parse string and does not take into account number of tags and position of tag in string.

    -- Paul


    • Edited by Paul Levchuk Thursday, November 19, 2015 1:43 PM
    Thursday, November 19, 2015 1:42 PM

Answers

  • Hi all,

    I found simple solution. Here is it.

    I coded two functions which helps me to deal with issue.

    ----

    // Function 'prepare' converts every utmz row in my source table into subtable.

    (row)=>
    let
        utmz1 = row,
        next = Text.Split(utmz1, "|"),
        #"Converted to Table" = Table.FromList(next, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
    in
        #"Converted to Table"


    // Function 'parse' parses each utmz subtable into tags and pivots results to table.

    (table)=>
    let
        Custom1 = table,
        #"Split Column by Delimiter" = Table.SplitColumn(Custom1,"Column1",Splitter.SplitTextByDelimiter("="),{"Column1.1", "Column1.2"}),
        #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", type text}}),
        #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1","Column1.1",Splitter.SplitTextByEachDelimiter({"utm"}, null, true),{"Column1.1.1", "Column1.1.2"}),
        #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Column1.1.1", type text}, {"Column1.1.2", type text}}),
        #"Removed Columns" = Table.RemoveColumns(#"Changed Type2",{"Column1.1.1"}),
        #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Column1.1.2]), "Column1.1.2", "Column1.2")
    in
        #"Pivoted Column"

    ----

    Now main function is quite simple.

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"utmz", type text}}),
        #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each prepare([utmz])),
        #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each parse([Custom])),
        #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Custom"}),
        #"Expanded Custom.1" = Table.ExpandTableColumn(#"Removed Columns", "Custom.1", {"csr", "ccn", "cmd", "cct", "ctr", "gclid"}, {"csr", "ccn", "cmd", "cct", "ctr", "gclid"})
    in
        #"Expanded Custom.1"



    • Marked as answer by Paul Levchuk Friday, November 20, 2015 8:25 AM
    • Edited by Paul Levchuk Friday, November 20, 2015 10:06 AM
    Friday, November 20, 2015 8:24 AM

All replies

  • Does splitting by "|", and then splitting each of the resulting columns by "=" work?

    Ehren

    Thursday, November 19, 2015 7:48 PM
    Owner
  • Hi Ehren,

    Nice try but it does not work. Actually I tried the same technique to parse but results were wrong.

    As I wrote before tags concatenated into utmz randomly so I can't assume that for example 2nd tag is always 'utmgclid'.

    I think there could be two solutions: one based on full scan of string to find each tag and second is based of lists.

    -- Paul


    Friday, November 20, 2015 7:25 AM
  • Hi all,

    I found simple solution. Here is it.

    I coded two functions which helps me to deal with issue.

    ----

    // Function 'prepare' converts every utmz row in my source table into subtable.

    (row)=>
    let
        utmz1 = row,
        next = Text.Split(utmz1, "|"),
        #"Converted to Table" = Table.FromList(next, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
    in
        #"Converted to Table"


    // Function 'parse' parses each utmz subtable into tags and pivots results to table.

    (table)=>
    let
        Custom1 = table,
        #"Split Column by Delimiter" = Table.SplitColumn(Custom1,"Column1",Splitter.SplitTextByDelimiter("="),{"Column1.1", "Column1.2"}),
        #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", type text}}),
        #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1","Column1.1",Splitter.SplitTextByEachDelimiter({"utm"}, null, true),{"Column1.1.1", "Column1.1.2"}),
        #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Column1.1.1", type text}, {"Column1.1.2", type text}}),
        #"Removed Columns" = Table.RemoveColumns(#"Changed Type2",{"Column1.1.1"}),
        #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Column1.1.2]), "Column1.1.2", "Column1.2")
    in
        #"Pivoted Column"

    ----

    Now main function is quite simple.

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"utmz", type text}}),
        #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each prepare([utmz])),
        #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each parse([Custom])),
        #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Custom"}),
        #"Expanded Custom.1" = Table.ExpandTableColumn(#"Removed Columns", "Custom.1", {"csr", "ccn", "cmd", "cct", "ctr", "gclid"}, {"csr", "ccn", "cmd", "cct", "ctr", "gclid"})
    in
        #"Expanded Custom.1"



    • Marked as answer by Paul Levchuk Friday, November 20, 2015 8:25 AM
    • Edited by Paul Levchuk Friday, November 20, 2015 10:06 AM
    Friday, November 20, 2015 8:24 AM