Is there a way (in redshift) to join the page ping events back to the “parent” page view event? If for instance we wanted to do things like
- look up attributes of the original page view event or
- calculate a standard deviation of number of page pings per view (or scroll depth per view)
- determine what percent of pageviews scroll to a certain depth on the page
same question for link_click events, is there a way to join that back to the parent page view event for answering questions like
- is there a correlation between time on page (or scroll depth) and clicks on content recommendation links
- looking for an “effective click through rate” where we look at the number of page views that received a recommendation click as a proportion of page views that scrolled deep enough to see the links
I feel like there is probably an obvious answer to this and I’m just not seeing it. looks like the
root_id in the
com_snowplowanalytics_snowplow_link_click_1 table joins back to the events table to an event of type
unstruct but I don’t see any fields that allow me to join either the
unstruct click event or the
page ping events back to the original page view.
If there is not a way to do this using the out-of-the-box redshift table schema, is there a way for me to pass the page_view id in a custom context of the page ping and the click event?