Add Tracks to sessions for more accurate session times

[thtmnisamnstr]

Describe the feature

Currently, sessions only take into consideration page views. It would be better if they also took into consideration track calls as well. When only considering page views, session times are effectively 0 when only a single page is viewed, even if a visitor is on that page for an extended period. If track calls are also considered (along with some instrumentation to create "heartbeat" track calls), you get a much more accurate picture of session times.

Describe alternatives you've considered

There are no great alternatives to get accurate session times with Segment data.

Additional context

Instrumentation to add a heartbeat is helpful for this. An example of this type of instrumentation is here: https://github.com/earthly/website/blob/main/website/assets/js/heartbeat.js. This is currently a feature request with Segment.

Who will this benefit?

Everyone who uses this dbt package will benefit. Anybody who needs accurate session times will benefit.

Are you interested in contributing this feature?

I've already implemented this for our (Earthly's) web properties in this repo: https://github.com/earthly/dbt-segment. The changes are minimal, adding the event field to the models/base/segment_web_page_views.sql model and adding the first_event and last_event fields to the models/sessionization/segment_web_sessions__initial.sql model. I haven't cleaned up naming to convey that both page views and track calls are considered, because that isn't useful to us, but I would be willing to do that if you want to incorporate these changes. The downside of renaming things is that it would be a breaking change that requires significant remediation for all existing users. Without name changes, it is still a breaking change, but the only remediation required by existing users is to add the event field to their page_views models in their projects that use this package.

The heavy lifting for this is done in the page_views models in projects that use this package. Currently, you only have to select the required fields from pages tables. For example:

SELECT  ID, ANONYMOUS_ID, USER_ID, RECEIVED_AT, SENT_AT, TIMESTAMP, URL, PATH, TITLE,
    SEARCH, REFERRER, CONTEXT_CAMPAIGN_SOURCE, CONTEXT_CAMPAIGN_MEDIUM,
    CONTEXT_CAMPAIGN_NAME, CONTEXT_CAMPAIGN_TERM, CONTEXT_CAMPAIGN_CONTENT,
    CONTEXT_IP, CONTEXT_USER_AGENT
FROM SEGMENT_EVENTS.BLOG.PAGES

With these changes, you have to do a similar select that unions the pages and tracks tables. For example:

SELECT  ID, ANONYMOUS_ID, USER_ID, 'page' as EVENT, RECEIVED_AT, SENT_AT, TIMESTAMP, URL,
    PATH, TITLE, SEARCH, REFERRER, CONTEXT_CAMPAIGN_SOURCE, CONTEXT_CAMPAIGN_MEDIUM,
    CONTEXT_CAMPAIGN_NAME, CONTEXT_CAMPAIGN_TERM, CONTEXT_CAMPAIGN_CONTENT,
    CONTEXT_IP, CONTEXT_USER_AGENT
FROM SEGMENT_EVENTS.BLOG.PAGES
UNION
SELECT  ID, ANONYMOUS_ID, USER_ID, EVENT, RECEIVED_AT, SENT_AT, TIMESTAMP, CONTEXT_PAGE_URL as URL,
    CONTEXT_PAGE_PATH as PATH, CONTEXT_PAGE_TITLE as TITLE, CONTEXT_PAGE_SEARCH as SEARCH,
    CONTEXT_PAGE_REFERRER as REFERRER, CONTEXT_CAMPAIGN_SOURCE, CONTEXT_CAMPAIGN_MEDIUM,
    CONTEXT_CAMPAIGN_NAME, CONTEXT_CAMPAIGN_TERM, CONTEXT_CAMPAIGN_CONTENT, CONTEXT_IP,
    CONTEXT_USER_AGENT
FROM SEGMENT_EVENTS.BLOG.TRACKS

I considered other approaches, and this was the easiest to implement and also the one that would require the least remediation for existing users.

[hugohahn009]

Agree, considering both tracks and pages to monitor a session is what we're aiming for internally as well

[johnh]

+1 from me on this proposal. Instead of forking and modifying the package for our needs, I ended up creating down stream models that sort of stitch certain "key" events into the sessions. Because I don't have accurate session start and end times associating those key events with their original session is painful.

[0adamjones]

So sorry for the delay everyone. @thtmnisamnstr I just read this over, and I think it's a great idea! When we initially adopted this package from Segment, there was a recommendation to stitch together your pages and tracks upstream in a model (we call ours segment_marketing_site_formatted_events for example and we just union together the page views and track calls on our site like requesting a demo, downloading content, starting a trial. We use their example columns, and for Title we just say something like 'Track - Trial Started' so we can differentiate them. Not the most elegant, but it works for us.

I'm a little strapped at the moment but I can get this into my backlog and try to start on this over the next 2-3 weeks. You're welcome to beat me to it if you'd like 😄 Thanks for starting this convo!

[0adamjones]

Hey @thtmnisamnstr, sorry for the delay here. I'm still a bit strapped for bandwidth, but if you're interested in contributing to the repo you're welcome to open a PR with the changes that you done in your forked branch!

[thtmnisamnstr]

@0adamjones I no longer work for Earthly and don't have write access to the forked repo anymore. More than that though, I don't have the bandwidth to work on this right now.