Alerting

Calculate the drop off rate during customer Journey

Path Finder

Hi, I am working on a query in Splunk to calculate the drop off rate and percentage of the customer journey drop outs. When customer comes to purchase an order it goes via different pages and I want to calculate where actually most of the customers are dropping off.

Journey Pages - /checkout/my-offer -> /checkout/your-details -> /checkout/direct-debit-> /checkout/creditcheck -> /checkout/review-basket -> /checkout/confirm-order -> /checkout/order complete

 

index=test_prod sourcetype=access_combined_wcookie req_content=/checkout/your-details OR req_content=/checkout/direct-debit OR req_content=/checkout/creditcheck OR req_content=/checkout/review-basket OR req_content=/checkout/confirm-order OR req_content=/checkout/order complete
| timechart span=1h count

 

The problem is I only want to calculate the drop outs for a specific journey which starts with this page and all other pages like  /checkout/your-details can come in other journeys as well. the only link between the pages for the journey is a field called uniqueId

Is it possible to calculate the percentage of drop outs during this journey?

Labels (1)
0 Karma

Communicator

Have you tried getting the latest req_content by journey, then count these. Then sum these counts and calculate the percentage that each count represents?

0 Karma

Path Finder

@ITWhisperer Hey I have edited the question. Could you kindly refer it to once again. It will give a better view of what I want. Actually I only want to calculate it for specific journey and the only common field between those pages is uniqueId.

0 Karma

Communicator

Something like this

index=test_prod sourcetype=access_combined_wcookie req_content=/checkout/your-details OR req_content=/checkout/direct-debit OR req_content=/checkout/creditcheck OR req_content=/checkout/review-basket OR req_content=/checkout/confirm-order OR req_content=/checkout/order complete
| stats latest(req_content) as endofjourney, latest(_time) as _time by uniqueId
| bin span=1h _time
| stats count by endofjourney, _time
| eventstats sum(count) as total by _time
| eval percent=round((count * 100) / total, 2)
| fields - total, count
| xyseries _time, endofjourney, percent

If you don't want to include journeys that start with your-details but don't go to any of the other req_content, just remove that from the search

0 Karma

Path Finder

@ITWhisperer Thanks for your response but this is not working and not what i want actually. I am looking for a table like  this -

Basically it tells me that on a particular day how much is the drop off rates at given hour on a particular page. As an example on 17th Sept at 08:00, 70% of the customers dropped at the starting page /my-offer, some of them moved to direct-debit but still 50% drop there and then so on.

Hope it make sense.

_timemy-offer-dropoff_percdirect-debit-dropoff_perccredit-check-dropoff_percreview-basket-dropoff_percconfirm-order-dropoff_percorder-complete-dropoff_perc
2020-09-17 08:0070504025205
2020-09-17 09:00      
2020-09-17 10:00      

 

@ITWhisperer @richgalloway @to4kawa 

0 Karma

Communicator

Hi @shashank_24 

If I understand correctly, you are saying the 70% of all customers ended their journey at the my-offer page. Of the remaining 30%, 50% (i.e. 15% of the original customers) ended their journey at the direct-debit page. Of these 15%, 40% (i.e. 6% of the original customers) ended their journey at the credit-check page. And so on?

0 Karma

Path Finder

@ITWhisperer yes that's correct. So for example if let's say in an hour from 8-9, 100 customer landed on page /my-offer, 50 of them moved ahead and accepted the offer and filled the direct-debit details /direct-debit, 30 of them moved to /confirm-order page etc.

0 Karma

Path Finder

@ITWhisperer Something like this. This is just the count and eventually I would to calculate the percentage which could be easy once i get the count like below

_timemy-offer-dropoffdirect-debit-dropoffcredit-check-dropoffreview-basket-dropoffconfirm-order-dropoff
2020-09-17 08:0010050404030
0 Karma

Communicator

@shashank_24 

So a count by req_content by time would give you the counts

index=test_prod sourcetype=access_combined_wcookie req_content=/checkout/your-details OR req_content=/checkout/direct-debit OR req_content=/checkout/credit-check OR req_content=/checkout/review-basket OR req_content=/checkout/confirm-order OR req_content=/checkout/order-complete
| bin span=1h _time
| stats count by req_content _time

However, let's say a customer ends their journey between 8 and 9, but started it between 7 and 8, do you want the counts for the earlier steps to be included in the 7-8 slot (which is what the above will do), or do you want the counts in the 8-9 slot which the below attempts to do

index=test_prod sourcetype=access_combined_wcookie req_content=/checkout/your-details OR req_content=/checkout/direct-debit OR req_content=/checkout/credit-check OR req_content=/checkout/review-basket OR req_content=/checkout/confirm-order OR req_content=/checkout/order-complete
| stats values(req_content) as req_content, latest(_time) as _time by uniqueId
| mvexpand req_content
| bin span=1h _time
| stats count by req_content, _time

This doesn't take into account whether they went back and forth during their journey nor where they were when they ended their journey, it counts the pages visited not how many times in the journey it was visited. If you want the number of times the page was requested by the customer, try changing values(req_content) to list(req_content)

0 Karma

Communicator

For the example you just gave, what would you want the table to look like?

0 Karma