• Recipes
  • Track trending user time on site with Segment and Dataform

Track trending user time on site with Segment and Dataform

In this recipe, you'll learn how to track a user's time on site with Segment and Dataform.
Increase conversion with personalized lifecycle campaigns with Segment
Collect data once with Segment and send it any tool you use. No more tedious integrations. No more engineering resources wasted.
Start with Segment

Once your cloud data warehouse is connected to Segment there are several new tables created in your warehouse. These tables are great sources for analysts to get detailed data about their users and activity, however, without preprocessing they can be complex and time consuming to work with. Answering a simple question - how long do users typically spend on my website, and how has it trended over time - requires complex SQL queries.

Dataform have prepared a package that creates some core data models with your Segment data to enable quick and easy analysis. This package can be added to a Dataform project with just a few lines of code.

How this works:
  • Connect Segment to your cloud data warehouse and start sending web events.

  • Install the Dataform Segment package in your Dataform project to create two easy-to-use tables; segment_sessions: Contains a combined view of track and page events from Segment. Common fields are extracted out into the top level and type specific fields are kept within two structs: records.track and records.page.

  • segment_users: Aggregates all identifies calls to give a table with one row per user_id. Identify calls with only an anonymous_id are mapped to the matching user_id where possible.

  • Use a simplified SQL query in Dataform to query the segment_sessions table and find out how many users are visiting your website and how that has trended over time.

Steps to get started:
Tools you need:
  • Segment

  • Dataform

  • Cloud data warehouse (Bigquery, Snowflake, Redshift)