Creating complex ranking using certain values within a dimension

JustinB

Hello all!

I am attempting to create a sankey diagram that shows a customers activities leading up to a certain conversion. Each customer can have multiple conversions so I want to rank each activity leading up to a conversion, and have that rank reset after the conversion happens. Having some trouble theorizing how to do this in a dataflow or SQL.

In the sample data above, it is easy to create the ranking by partitioning by USER and ordering by date.

I am trying to create the journey and journey rank fields.

Journey: Each journey should include the activities leading up to and including the conversion

Journey Rank: the order of activities leading up to and including the conversion (should reset after the conversion happens). The journey rank should start over for every specific journey.

Hopefully there is an easy solution I am not considering! Thanks in advance.

GrantSmith

You might be able to do this with some window functions in a magic etl. You could do a lag function to calculate what the prior row value was. Then use a formula tile to check if it's a Conversion event and then return a value of 1, otherwise 0. Then you can use a Rank and Window tile to do a running total on that new field to get the Journey number. Finally do another Rank and Window and partition by the new Journey number to calculate the Journey Rank.