How to write a search to calculate a ratio using my sample data?

[stephenmoorhous]

Hi

I'm trying to calculate the conversion rate of people going from a product page to a payment page.

ie given the following log data

item_code | user_session_id | page_type
aaa | 123456 | item page
aaa | 123456 | payment page
ccc |  654321 | item page
aaa | 654321 | item page
bbb | 111111 | item page
bbb | 111111 | payment page

I would like to be able to get the output:

item code | item page count | payment page count | conversion(%)
aaa | 2 | 1 | 50%
bbb | 1 | 1 | 100%
ccc | 1| 0 | null 

To get the columns separately, you can do:

.... page_type "item page" | stats count by item_code

or

.... page_type="payment page" | stats count by item_code

Just joining them and outputting them side by side doesn’t work because some people do not go to both pages, so they become out of sync.

Thanks

[javiergn · Accepted solution]

What about this?

| chart values(user_session_id) as user_session_id OVER item_code BY page_type
| eval item_page_count = mvcount('item page')
| eval payment_page_count = mvcount('payment page')
| fillnull value=0 item_page_count, payment_page_count
| eval 'conversion(%)' = round(payment_page_count/item_page_count*100, 2) . "%"

Output sample based on the values you provided:

[stephenmoorhous]

amazing - works perfect - thanks 🙂

[javiergn]

No worries. Please don't forget mark one of the answers you liked as "answered" so that others can benefit from it

[richgalloway]

Try this.

... | stats sum(eval(if(page_type="item page",1,0))) as "itemPageCount", sum(eval(if(page_type="payment page",1,0))) as "paymentPageCount", list(eval(paymentPageCount/itemPageCount)) as conversion by "item code"