Single Page App Navigation Timings

In the Fullstory exported data, a PageId refers to all activity that occurred for a single page load. This concept of a "page" also applies to each of the Page- fields in the export.

However, if your site is a Single Page App (SPA), then there will likely be multiple "navigate" events within a single page load. In this case, PageDuration is less helpful by itself since it doesn't change for intra-"Page" navigations.

Inter-page timing table schema

The following queries produce a table with the following schema.

Column	Type	Description
UserId	INTEGER	Id corresponding to a single user cookie.
SessionId	INTEGER	Id corresponding to a single session.
PageId	INTEGER	Id corresponding to a single page load.
PageUrl	STRING	A visited Url.
PreviousUrl	STRING	If the first navigate for a PageId, then this is the PageRefererUrl. Otherwise, the PageUrl from the previous navigate event.
EnteredPage	TIMESTAMP	When the user navigated to the corresponding PageUrl.
LeftPage	TIMESTAMP	When the user navigated away from the corresponding PageUrl
PageDurationMillis	INTEGER	The duration in milliseconds that the user spent on the corresponding PageUrl.

BigQuery SQL

# If using BigQuery tables created by hauser, then the table will be partitioned by day.
# This alias isn't necessary, but is separated out for clarity.
WITH part AS (
    SELECT * FROM project.dataset.table WHERE DATE(_PARTITIONTIME) = "2021-07-07"
),
page_timings AS (
    SELECT 
        UserId,SessionId,PageId,
        PageUrl,
        COALESCE(LAG(PageUrl, 1) OVER w1, PageRefererUrl) AS PreviousUrl,
        EventStart AS EnteredPage,
        COALESCE(LEAD(EventStart, 1) OVER w1, TIMESTAMP_ADD(PageStart, INTERVAL PageDuration millisecond)) AS LeftPage,
    FROM part
    WHERE EventType = "navigate"
    WINDOW w1 AS (
        PARTITION BY UserId, SessionId, PageId
        ORDER BY EventStart
    )
),
SELECT 
    *,
    TIMESTAMP_DIFF(LeftPage, EnteredPage, millisecond) AS PageDurationMillis
FROM page_timings
;

Redshift SQL

WITH page_timings AS (
    SELECT 
        UserId,SessionId,PageId,
        PageUrl,
        COALESCE(
          LAG(PageUrl, 1) OVER (PARTITION BY UserId, SessionId, PageId ORDER BY EventStart),
          PageRefererUrl
        ) AS PreviousUrl,
        EventStart AS EnteredPage,
        COALESCE(
          LEAD(EventStart, 1) OVER (PARTITION BY UserId, SessionId, PageId ORDER BY EventStart), 
       	  DATEADD(millisecond, PageDuration, PageStart)
        ) AS LeftPage
    FROM database.schema.table
    WHERE EventType = 'navigate'
)
SELECT 
    *,
    DATEDIFF(millisecond, EnteredPage, LeftPage) AS PageDurationMillis
FROM page_timings
;