Sessions To Order count - or Sessions until other interaction

  • 19 February 2015
  • 1 reply
  • 73 views

Userlevel 2

In a Visitors table, I had created a dimension for the Visitors’ Time to Order, as well as Time to complete several interactions. The Visitors table is derived from Sessions by grouping them by user_id.

Time To Order was of course useful, but I found it would be great to separate those users who purchase on 2nd visit, perhaps one month after their first session, and those users who after their first session make n visits during the first month, but only eventually purchase at the same time as the other group.

My code for this is along these lines:


derived_table:
SELECT
s.visitor_id
, MIN(CASE WHEN s.order_count > 0 THEN s.run_sequence ELSE 100000 END) AS run_sequence_of_session_with_first_order

FROM sessions AS s
GROUP BY 1

fields:
- dimension: run_sequence_of_session_with_first_order
label: '._. CONVERSION - Sessions To Order: session number ... has 1st order'
type: int
sql: |
CASE WHEN ${TABLE}.run_sequence_of_session_with_first_order = 100000 THEN 0
ELSE ${TABLE}.run_sequence_of_session_with_first_order
END


You may note my use of ._. in the label. Side note, I found including this or any numbering such as 1.a. currently always removes the name of the table from the dimension or measure’s labels, in the Explores.


1 reply

Userlevel 6
Badge

@Vaite, this is a really cool pattern and super valuable.



The label thing is an artifact of some backward compatibility we were trying to maintain.

Reply