Website visitor pathway analysis

KNIME Analytics Platform
1

Hello everybody,
is there a smart way to aggregate the values of 2 columns according to their sequence?
For example:

Id Previous_page      Page
1  /                  /Page_c
1  /Page_a            /Page_e
1  /Page_c            /Page_a
2  /                  /Page_b
2  /Page_b            /Page_c
3  /                  /Page_b
3  /Page_a            /Page_d
3  /Page_b            /Page_a 
3  /Page_d	          /Page_c

so the new column should contain:

ID Sequence
1 /;Page_c;Page_a;Page_e
2 /;Page_b;Page_c;
3 /;Page_b;Page_a;Page_d;page_c

Best,
Alfredo

2

Hello @alfroc,

is something like this working for you?

Let me know,
RB

1 Like
3

Thank you, @lelloba!
Your solution comes very close to what I was asking for but unfortunately it is not the right one:
Id page
1 /Page_a, /Page_c, /Page_e
2 /Page_b, /Page_c
3 /Page_a, /Page_b, /Page_d, /Page_c

The right solution (the sequence) should be as described above:
ID Sequence
1 /;Page_c; /Page_a; /Page_e
2 /;Page_b; /Page_c;
3 /;Page_b; /Page_a; /Page_d; /Page_c

Thanks anyway!
Alfredo

1 Like
4

Hi @alfroc,

I am out office and cannot look at my solution (no PC). I had probably used a group by with a sorted list. There should be another option so you can do what you want (set, unique concatenate, list (not sorted).

Tell me if it work, hope it helps.

RB

7

Hi @alfroc , it looks like you deleted your post where you said you figured it out. Does this mean that you still need help?

I wrote something a while ago that shows the hierarchy and path of a parent-child relation table that may be is similar to your issue:

You can see the results in the thread here:

2 Likes
8

Hi @bruno29a ,
I deleted my previous post because the solution I proposed - see attached workflow - is not yet perfect (for example, when ID = “005” the result is “, a, b, c, a, d, b” instead of “, a, b, c, a, d, b, e, a”).
I tried your solution using the same data but unfortunately it goes in a loop…
Ciao
Alfredo

String_Sequence.knwf (73.1 KB)

9

Hi @alfroc , forgive me for asking. I find this is interesting. But I’m simply curious as to what real case scenario is this for? Would you be kind enough to share? Thank you!

10

Hi @badger101,
I’m trying to analyze the path that every visitor of my website does, as you can see from the table obtained with DataStudio on Google Analytics data.
Ciao
Alfredo

DataStudio_Report
DataStudio_Report1387×911 149 KB

11

Thank you @alfroc . That makes sense. I would love to help you out. Would be kind enough to export at least the portion you screenshorted above to a file? (I’m assuming you’ll be using Excel for that matter, which is fine by me). All I need is to have something to work with as an input (that resembles the pattern and format of the real data).

Note: I have also edited the name of this forum thread to ‘Website visitor pathway analysis’ so that others seeking for solutions in the future for the same kind of case will be able to find this easily.

12

Hi @badger101
Thanks for your help! I finally managed to find a solution that works for me as it fully supports reverse paths as well.
I renamed the workflow according to the name of this thread.
Ciao
Alfredo

Pathway_Analysis.knwf (70.4 KB)

1 Like
13

Hi @alfroc , I’m glad you have found your own workaround, and thank you for sharing the workflow.

Forgive me, but I’ll have to say that the workflow you came up with has a nested loop which might not be so efficient when applied to large datasets. Plus, it looks fairly complicated to begin with, given a simple dataset that you have. It might work great for you, but it can be difficult for others to adopt it.

I was thinking of proposing solutions without loops, and in addition to that, I was thinking to add a special column summarizing the dataset for each unique pathway from the results.

But hey, as long as you’re happy with what you’ve got. Cheers!

14

Hi @badger101,
such a solution would be great!
Actually it seems like I’m not the only one interested …
All the best
Alfredo

15

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.