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
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:
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:
amazing - works perfect - thanks 🙂
No worries. Please don't forget mark one of the answers you liked as "answered" so that others can benefit from it
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"