Forum Discussion

MichaelHutchens's avatar
MichaelHutchens
Icon for Helper V rankHelper V
5 years ago
Solved

Tracking successful search experiences

Hi folks, I'm hoping someone can help 🙂

I need to measure successful search experiences in my ITSM tool (based on sessions that contained at least one search and at least one article view), as a percentage of total sessions. Notes:

  • A 'search' is measured as when the 'Description' column of my table starts with the string 'Keyword search'.
  • An 'Article view' is measured as when the 'Description' column of my table starts with the string 'Article viewed'.
  • A 'Search session' is measured as the unique string in the 'Session Id' column of my table

There will be cases where there are no searches are performed in a session, this means that users have opened articles via a direct link rather than by a search. These should be ignored from the measure.

Here's an example table of data:

Table name = data

DateTimeSession IdUserDescription
1/22/2021 16:10182489A61B3D68102C262F49274BCB96Michael HutchensArticle viewed | KB0016624 | Samsung devices
1/22/2021 16:16182489A61B3D68102C262F49274BCB96Michael HutchensArticle viewed | KB0016624 | Samsung devices
12/21/2020 8:581A08CE04DB9964109FAA61730596198EMichael HutchensArticle viewed | KB0011483 | Service Desk
12/21/2020 9:361A08CE04DB9964109FAA61730596198EMichael HutchensArticle viewed | KB0016758 | Self Service Kiosks
12/21/2020 14:161A08CE04DB9964109FAA61730596198EMichael HutchensArticle viewed | KB0015571 | SharePoint files
12/21/2020 14:221A08CE04DB9964109FAA61730596198EMichael HutchensArticle viewed | KB0015571 | SharePoint files
12/21/2020 9:4828ED089FDB656810F46DF561F39619E6Michael HutchensArticle viewed | KB0016758 | Self Service
12/21/2020 10:0028ED089FDB656810F46DF561F39619E6Michael HutchensKeyword search for "cim"
12/21/2020 10:0028ED089FDB656810F46DF561F39619E6Michael HutchensArticle viewed | KB0013632 | ICT
12/21/2020 10:0328ED089FDB656810F46DF561F39619E6Michael HutchensKeyword search for "cim"
12/21/2020 10:0328ED089FDB656810F46DF561F39619E6Michael HutchensArticle viewed | KB0013632 | ICT
12/21/2020 11:2728ED089FDB656810F46DF561F39619E6Michael HutchensArticle viewed | KB0011442 | ABC
12/17/2020 7:48360671BEDB4DE4105E026A1505961967James JamiesonKeyword search for "3M"
12/17/2020 9:55360671BEDB4DE4105E026A1505961967James JamiesonArticle viewed | KB0016642 | Adobe
12/17/2020 12:37360671BEDB4DE4105E026A1505961967James JamiesonKeyword search for "GIS"
12/17/2020 9:55A0FFF203DBC560105E026A15059619C9James JamiesonArticle viewed | KB0013330 | Handbook
12/17/2020 9:55A0FFF203DBC560105E026A15059619C9James JamiesonArticle viewed | KB0016642 | Adobe
12/17/2020 7:48A0FFF203DBC560105E026A1505961ABCJames JamiesonKeyword search for "3M"

 

And here's an example of the results I'd like to see in a new table called 'Results':

Session IdUserSession contains a searchSession contains a view
182489A61B3D68102C262F49274BCB96Michael HutchensNoYes
1A08CE04DB9964109FAA61730596198EMichael HutchensNoYes
28ED089FDB656810F46DF561F39619E6Michael HutchensYesYes
360671BEDB4DE4105E026A1505961967James JamiesonYesYes
A0FFF203DBC560105E026A15059619C9James JamiesonNoYes
A0FFF203DBC560105E026A1505961ABCJames JamiesonYesNo


Any help would really be appreciated 🙂

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi MichaelHutchens 

     

    How would you like to do it, M or DAX? Here is one way in M, paste in Advanced Editor can see the steps

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("xdRda9swFAbgvyJ8Xeg5+jiWfGf5Y+1CYdDdtb1wbWUxSWyw05bBfvykpd0gacoS3PbGCGN49L468s1NhOecn3PgyJAShOgsQs2lNimhFTlpBJ5x4qU0PJY2s4b8J1dtvajcil08bOqF60b/Kh02bb1y7LF1T65hv9jMAiARl359Xa3Hh+4Ha9xjW7sxujvbhekDYc9ikIHpROkAp6CzAmRujSGJYMrU7yIWoAyh0cUxMEotAuyGILLcjctd1SSCJlYpVvqPupr/pWdtPy73IqN8LntCXakYg76oBvetb7sNm7er/bY9zfnn0CaR4aC5LnLQpswtqTBhpaS8VISlCHJx3ITtV74XGBKAk9mZ+/nUDw0bXTXUCzbvB3Yb1e36NprWeT2eIMH9+jL7/pomPijV6c7RqTDh8cQaShm01GYvGsZbLd7OoyCgGG2RW5kX/iaoAjilqLY3gcJ+vlZrNzL/bN3Yd4cKFFf/+nsxTKLUicahf+o2TtPfu10MeSLiKRN9ubw+GCmFsiw5iNxmimAXycz/RxJCgF9fVF1z3/fLd/feqvB5Jt7EwigdNxN3vwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DateTime = _t, #"Session Id" = _t, User = _t, Description = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"DateTime", type text}, {"Session Id", type text}, {"User", type text}, {"Description", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Session contains a search", each if Text.Contains([Description],"Keyword search") then 1 else 0),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Session contains a view", each if Text.Contains([Description],"Article viewed") then 1 else 0),
    #"Grouped Rows" = Table.Group(#"Added Custom1", {"Session Id", "User"}, {{"Session contains a search", each if List.Sum([Session contains a search]) >0 then "Yes" else "No"}, 
{"Session contains a view#(tab)", each if List.Sum([Session contains a view])>0 then "Yes" else "No"}})
in
    #"Grouped Rows"

     

     

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi MichaelHutchens 

     

    How would you like to do it, M or DAX? Here is one way in M, paste in Advanced Editor can see the steps

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("xdRda9swFAbgvyJ8Xeg5+jiWfGf5Y+1CYdDdtb1wbWUxSWyw05bBfvykpd0gacoS3PbGCGN49L468s1NhOecn3PgyJAShOgsQs2lNimhFTlpBJ5x4qU0PJY2s4b8J1dtvajcil08bOqF60b/Kh02bb1y7LF1T65hv9jMAiARl359Xa3Hh+4Ha9xjW7sxujvbhekDYc9ikIHpROkAp6CzAmRujSGJYMrU7yIWoAyh0cUxMEotAuyGILLcjctd1SSCJlYpVvqPupr/pWdtPy73IqN8LntCXakYg76oBvetb7sNm7er/bY9zfnn0CaR4aC5LnLQpswtqTBhpaS8VISlCHJx3ITtV74XGBKAk9mZ+/nUDw0bXTXUCzbvB3Yb1e36NprWeT2eIMH9+jL7/pomPijV6c7RqTDh8cQaShm01GYvGsZbLd7OoyCgGG2RW5kX/iaoAjilqLY3gcJ+vlZrNzL/bN3Yd4cKFFf/+nsxTKLUicahf+o2TtPfu10MeSLiKRN9ubw+GCmFsiw5iNxmimAXycz/RxJCgF9fVF1z3/fLd/feqvB5Jt7EwigdNxN3vwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DateTime = _t, #"Session Id" = _t, User = _t, Description = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"DateTime", type text}, {"Session Id", type text}, {"User", type text}, {"Description", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Session contains a search", each if Text.Contains([Description],"Keyword search") then 1 else 0),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Session contains a view", each if Text.Contains([Description],"Article viewed") then 1 else 0),
    #"Grouped Rows" = Table.Group(#"Added Custom1", {"Session Id", "User"}, {{"Session contains a search", each if List.Sum([Session contains a search]) >0 then "Yes" else "No"}, 
{"Session contains a view#(tab)", each if List.Sum([Session contains a view])>0 then "Yes" else "No"}})
in
    #"Grouped Rows"

     

     

    • MichaelHutchens's avatar
      MichaelHutchens
      Icon for Helper V rankHelper V
      5 years ago

      That's perfect, thanks so much Anonymous, I really appreciate it 🙂