Creating complex ranking using certain values within a dimension

JustinB
JustinB Member
edited January 2022 in Magic ETL

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.

Screen Shot 2022-01-31 at 2.43.29 PM.png

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.

Tagged:

Answers

  • GrantSmith
    GrantSmith Coach

    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.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Categories