Solved: Re: Counting Abandoned Flows

Anonymous · ‎03-02-2020

Hello All,

Thanks for looking in to my post. I am struggling to find a better solution for below problem.

Data:

mud_id	visitor_id	session_id	Time stamp	Page Name
sk4844625	523012	202020	10/22/19 10:29 AM	Hello1
sk4844625	523012	202020	10/22/19 10:30 AM	Projects Page
sk4844625	523012	202020	10/22/19 10:32 AM	Click on A Project - Project1
sk4844625	523012	202020	10/22/19 10:34 AM	Click on "Connect an API (bottom right on Project)
sk4844625	523012	202020	10/22/19 10:36 AM	API catalog page
sk4844625	523012	202020	10/22/19 10:38 AM	Select an API - API details page
sk4844625	523012	202020	10/22/19 10:40 AM	Connect to Project button
sk4844625	523012	202020	10/22/19 10:42 AM	Select API environment
sk4844625	523012	202020	10/22/19 10:44 AM	Hit Connect
sk4844625	523012	202020	10/22/19 10:46 AM	Projects Page
sk4844625	523012	202020	10/22/19 10:48 AM	Click on A Project - Project1
sk4844625	523012	202020	10/22/19 10:50 AM	Click on "Connect an API (bottom right on Project)
sk4844625	523012	202020	10/22/19 10:52 AM	API catalog page
sk4844625	523012	202020	10/22/19 10:54 AM	Select an API - API details page
sk4844625	523012	202020	10/22/19 10:56 AM	Connect to Project button
sk4844625	523012	202020	10/22/19 10:58 AM	Select API environment
sk4844625	523012	202020	10/22/19 10:58 AM	Hello2
sk4844625	523012	202020	10/22/19 11:00 AM	Projects Page
sk4844625	523012	202020	10/22/19 11:02 AM	Click on A Project - Project1
sk4844625	523012	202020	10/22/19 11:04 AM	Click on "Connect an API (bottom right on Project)
sk4844625	523012	202020	10/22/19 11:06 AM	API catalog page
sk4844625	523012	202021	10/22/19 11:08 AM	Select an API - API details page
sk4844625	523012	202021	10/22/19 11:10 AM	Connect to Project button
sk4844625	523012	202020	10/22/19 11:12 AM	Projects Page
sk4844625	523012	202020	10/22/19 11:14 AM	Click on A Project - Project1
sk4844625	523012	202020	10/22/19 11:16 AM	Click on "Connect an API (bottom right on Project)
sk4844625	523012	202020	10/22/19 11:18 AM	API catalog page
sk4844625	523012	202020	10/22/19 11:20 AM	Select an API - API details page
sk4844625	523012	202020	10/22/19 11:22 AM	Connect to Project button
sk4844625	523012	202020	10/22/19 11:24 AM	Select API environment
GK321456	432145	123421	10/22/19 10:30 AM	Hep123
GK321456	432145	123421	10/22/19 10:30 AM	Hep12312fdrg
GK321456	432145	123421	10/22/19 10:31 AM	Projects Page
GK321456	432145	123421	10/22/19 10:33 AM	Click on A Project - Project1
GK321456	432145	123421	10/22/19 10:35 AM	Click on "Connect an API (bottom right on Project)
GK321456	432145	123421	10/22/19 10:37 AM	API catalog page
GK321456	432145	123421	10/22/19 10:39 AM	Select an API - API details page
GK321456	432145	123421	10/22/19 10:41 AM	Connect to Project button
GK321456	432145	123421	10/22/19 10:43 AM	Select API environment
GK321456	432145	123421	10/22/19 10:45 AM	Hit Connect
GK321456	432145	123421	10/22/19 10:47 AM	Projects Page
GK321456	432145	123421	10/22/19 10:49 AM	Click on A Project - Project1
GK321456	432145	123421	10/22/19 10:51 AM	Click on "Connect an API (bottom right on Project)
GK321456	432145	123421	10/22/19 10:53 AM	API catalog page
GK321456	432145	123421	10/22/19 10:55 AM	Select an API - API details page
GK321456	432145	123421	10/22/19 10:57 AM	Connect to Project button
GK321456	432145	123421	10/22/19 10:59 AM	Select API environment
BR124323	675432	987654	10/22/19 10:31 AM	Projects Page
BR124323	675432	987654	10/22/19 10:33 AM	Click on A Project - Project1
BR124323	675432	987654	10/22/19 10:36 AM	Click on "Connect an API (bottom right on Project)
BR124323	675432	987654	10/22/19 10:38 AM	API catalog page
BR124323	675432	987654	10/22/19 10:40 AM	Select an API - API details page
BR124323	675432	987654	10/22/19 10:43 AM	Connect to Project button
BR124323	675432	987654	10/22/19 10:45 AM	Select API environment
BR124323	675432	987654	10/22/19 10:47 AM	Hit Connect
BR124323	675432	987654	10/22/19 10:50 AM	Projects Page
BR124323	675432	987654	10/22/19 10:52 AM	Click on A Project - Project1
BR124323	675432	987654	10/22/19 10:54 AM	Click on "Connect an API (bottom right on Project)
BR124323	675432	987654	10/22/19 10:57 AM	API catalog page
BR124323	675432	987654	10/22/19 10:59 AM	Select an API - API details page
BR124323	675432	987654	10/22/19 11:01 AM	Connect to Project button
BR124323	675432	987654	10/22/19 11:04 AM	Select API environment
BR124323	675432	987654	10/22/19 11:06 AM	Projects Page
BR124323	675432	987654	10/22/19 11:08 AM	Click on A Project - Project1
BR124323	675432	987654	10/22/19 11:11 AM	Click on "Connect an API (bottom right on Project)
BR124323	675432	987654	10/22/19 11:13 AM	API catalog page
BR124323	675432	987654	10/22/19 11:15 AM	Select an API - API details page
BR124323	675432	987654	10/22/19 11:18 AM	Projects Page
BR124323	675432	987654	10/22/19 11:20 AM	Click on A Project - Project1
BR124323	675432	987654	10/22/19 11:22 AM	Click on "Connect an API (bottom right on Project)
BR124323	675432	987654	10/22/19 11:25 AM	API catalog page
BR124323	675432	987654	10/22/19 11:27 AM	Select an API - API details page
BR124323	675432	987654	10/22/19 11:29 AM	Connect to Project button
BR124323	675432	987654	10/22/19 11:32 AM	Select API environment

Please Note:

- From above data, there are 3 users who used a website parallelly between 10:30 to 11:30

Completed flow(Flows Starts at Row "Projects Page" and if use Hit button("Hit Connect") which means compleetd flow) :

Projects Page

Click on A Project - Project1

Click on "Connect an API (bottom right on Project)

API catalog page

Select an API - API details page

Connect to Project button

Select API environment

Hit Connect

Abandoned Flow(didnt hit final button(so no "Hit Connect"):

Projects Page

Click on A Project - Project1

Click on "Connect an API (bottom right on Project)

API catalog page

Select an API - API details page

Connect to Project button

Select API environment

No flow at all(There is event for "Select API Enviroment", which means no flow at all):

Projects Page

Click on A Project - Project1

Click on "Connect an API (bottom right on Project)

API catalog page

Select an API - API details page

Connect to Project button

I need to count Number of abandoned flows.

Kindly request your help

v-alq-msft · ‎03-02-2020

Hi, @Anonymous

Based on my research, you may try the following measures.

totalflowforuser = 
COUNTROWS(
    FILTER(
        ALL('Table'),
        'Table'[Page Name] = "Projects Page"&&
        'Table'[mud_id] = MAX('Table'[mud_id])
    )
)

completedflows = 
COUNTROWS(
    FILTER(
        ALL('Table'),
        'Table'[Page Name] = "Hit Connect"&&
        'Table'[mud_id] = MAX('Table'[mud_id])
    )
)

abandonedflows = 
COUNTROWS(
    FILTER(
        ALL('Table'),
        'Table'[Page Name] = "Select API environment"&&
        'Table'[mud_id] = MAX('Table'[mud_id])
    )
)-[completedflows]

notflow = 
[totalflowforuser]-[completedflows]-[abandonedflows]

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

v-alq-msft · ‎03-02-2020

Hi, @Anonymous

Based on my research, you may try the following measures.

totalflowforuser = 
COUNTROWS(
    FILTER(
        ALL('Table'),
        'Table'[Page Name] = "Projects Page"&&
        'Table'[mud_id] = MAX('Table'[mud_id])
    )
)

completedflows = 
COUNTROWS(
    FILTER(
        ALL('Table'),
        'Table'[Page Name] = "Hit Connect"&&
        'Table'[mud_id] = MAX('Table'[mud_id])
    )
)

abandonedflows = 
COUNTROWS(
    FILTER(
        ALL('Table'),
        'Table'[Page Name] = "Select API environment"&&
        'Table'[mud_id] = MAX('Table'[mud_id])
    )
)-[completedflows]

notflow = 
[totalflowforuser]-[completedflows]-[abandonedflows]

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous · ‎03-04-2020

Hi Allan,

Thank you. This dax solution is great. I even applied it my original data.

Only doubt i have at this point is,

Is there a way to force flow of events and then say its an abandoned flow(At present we are considering only "Select API environment" , but is there a way if the order as below, then its a abandoned flow..

Projects Page

Click on A Project - Project1

Click on "Connect an API (bottom right on Project)

API catalog page

Select an API - API details page

Connect to Project button

Select API environment

Thanks,

Siva

stretcharm · ‎03-02-2020

I used the group all rows to next the Pages, then used Table functions to filed it the Page has been visited Table.RowCount(Table.FindText([Events], "Projects Page"))

Note this just looks for the page rather than first and last pages. If you need that then the code would be a bit trickier

Data

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("vZjfb9owEMf/FStPrdSqubMvP3jr+jCmahLaHlseUprRrGlcgbu/fw4kaxsNOGMHIaETxJ9zcl++d/juLlo/q0ypBCm6iAhlDGgDjNuXDSC+QryCXEA8wVxcf7efTcu61hDNL1wWy3i7eLbSv8uFWYtZsSxdGbhl3NTV4lnoRlyLDicu+8h5X2rAvI9udNO0zMImmH0TZw/aGP0iVtXyybRXdJnOXTMl20wtc1GYotZL8XrEQ8i2mJ9l/WGTl5v3x9IUVb0+Bqu6+vQ3b/S/Z/vwZu+/ceXhp222uyubP9VKNy9lY1xhXZGmlRHdBl0Jib/8VBZefhSfSn6EQeRHahT5URJWfpQFlF8P2xgfOi2GSextfJYR3Pgs80TGZzMdZ3wwxIQxviEWghqf5aF/vWFYmwD1huRU9YYsgNPABOMRnMZiMWy9UR10mq+3EkFRYq9Sm6hFoFQDLb4PSdPy1X7vsxbw1+Nq6UiAXcrlIyRXuHwk+emWnyjdI1s+JXdQLZuqgCNaPk6G06yi/89mfEDqLTuVB5cdwYlkRzKE7IjGkB2lQWVH+UHZffkBaCHSXpWkZCMb5FmakGKbFR/BNis+0rPJ8hPt67FsinJpsXyq5KiGj6Nwqum9ZmhWbADtnOb5CPYwz0d6zvL8RPt6JJ/i0iOZVPv/gNUj+bjDcx2ftfP0gY9gHz6wkeDZ4viJ9rU4PsWlxfGpmXdpcHiG418aHHrEWKVBClEaTMcoTX/eHOgHLQ+cSM7/Ag==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [mud_id = _t, visitor_id = _t, session_id = _t, #"Time stamp" = _t, #"Page Name" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"mud_id", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"visitor_id", "session_id"}, {{"Events", each _, type table [mud_id=text, visitor_id=number, session_id=number, Time stamp=text, Page Name=text]}, {"Start Time stamp", each List.Min([Time stamp]), type text}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Abandoned Flow", each if Table.RowCount(Table.FindText([Events], "Hit Connect"))=0 then "Y" else "N"),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "No flow at all", each if Table.RowCount(Table.FindText([Events], "Select API environment"))=0 then "Y" else "N"),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Completed flow", each if Table.RowCount(Table.FindText([Events], "Projects Page"))<>0 and Table.RowCount(Table.FindText([Events], "Hit Connect"))<>0 then "Y" else "N")
in
    #"Added Custom2"

Data Summary

let
    Source = Data,
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {"visitor_id", "session_id", "Events", "Start Time stamp"}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Columns", each ([Value] = "Y")),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Attribute"}, {{"Count", each Table.RowCount(_), type number}})
in
    #"Grouped Rows"

Anonymous · ‎03-04-2020

Thank you. this works great. But my original data is complex and editing at query level is became tough. Dax solution is preferred.

Thanks again for helping me.

Counting Abandoned Flows

Helpful resources

Europe’s largest Microsoft Fabric Community Conference

New forum boards available in Real-Time Intelligence.

How to Get Your Question Answered Quickly

Jumpstart your career with the Fabric Career Hub

Counting Abandoned Flows

Helpful resources

Europe’s largest Microsoft Fabric Community Conference

New forum boards available in Real-Time Intelligence.

How to Get Your Question Answered Quickly