Example Queries

Note: these queries are written for BigQuery and may need to be adapted slightly depending on your export destination type.

Approximate session durations

SELECT TIMESTAMP_DIFF(session_end, session_start, SECOND)
FROM (
  SELECT
      MAX(updated_at) as session_end,
      MIN(updated_at) as session_start,
  FROM
    `<DB_NAME>.<SCHEMA_NAME>.logrocket_events`
  WHERE
    AND app_id = '<YOUR APP ID>'
  GROUP BY session_id
)

Number of identified users during a time range

WITH
  sessions_start AS (
  SELECT
    -- There can be many events with event_type = 'session' for the same session_id.
    -- Behavior such as a full page reload or opening the site in a new tab will
    -- trigger such an event; the start of a session is the one with the earliest date.
    MIN(updated_at) AS session_date,
    session_id
  FROM
    `<DB_NAME>.<SCHEMA_NAME>.logrocket_events`
  WHERE
    event_type = 'session'
    AND app_id = '<YOUR APP ID>'
  GROUP BY
    session_id 
  ),
  users AS (
  SELECT
    user_id,
    session_id
  FROM
    `<DB_NAME>.<SCHEMA_NAME>.logrocket_events`
  WHERE
    event_type = 'user'
    AND app_id = '<YOUR APP ID>'
    AND user_id <> ''
    AND user_id NOT LIKE '%<INTERNAL IDENTIFIER>%'
    AND user_identity_status = 'Identified'
  )
SELECT
  COUNT(DISTINCT user_id) AS user_count
FROM (
  SELECT
    users.user_id AS user_id,
    users.session_id AS session_id,
    sessions_start.session_date AS session_date
  FROM
    sessions_start
  INNER JOIN
    users
  ON
    users.session_id = sessions_start.session_id
    AND sessions_start.session_date BETWEEN '2023-05-07' and '2023-05-14'
)

Top 10 identified users by session count during a time range

WITH
  sessions_start AS (
  SELECT
    -- There can be many events with event_type = 'session' for the same session_id.
    -- Behavior such as a full page reload or opening the site in a new tab will
    -- trigger such an event; the start of a session is the one with the earliest date.
    MIN(updated_at) AS session_date,
    session_id
  FROM
    `<DB_NAME>.<SCHEMA_NAME>.logrocket_events`
  WHERE
    event_type = 'session'
    AND app_id = '<YOUR APP ID>'
  GROUP BY
    session_id 
  ),
  users AS (
  SELECT
    user_id,
    session_id
  FROM
    `<DB_NAME>.<SCHEMA_NAME>.logrocket_events`
  WHERE
    event_type = 'user'
    AND app_id = '<YOUR APP ID>'
    AND user_id <> ''
    AND user_id NOT LIKE '%<INTERNAL IDENTIFIER>%'
    AND user_identity_status = 'Identified'
  )
SELECT
  user_id,
  COUNT(DISTINCT session_id) AS user_session_count
FROM (
  SELECT
    users.user_id AS user_id,
    users.session_id AS session_id,
    sessions_start.session_date AS session_date
  FROM
    sessions_start
  INNER JOIN
    users
  ON
    users.session_id = sessions_start.session_id
    AND sessions_start.session_date BETWEEN '2023-05-07' and '2023-05-14'
)
GROUP BY
  user_id
ORDER BY
  user_session_count DESC
LIMIT
  10

Count of identified users by day

WITH
  sessions_start AS (
  SELECT
    -- There can be many events with event_type = 'session' for the same session_id.
    -- Behavior such as a full page reload or opening the site in a new tab will
    -- trigger such an event; the start of a session is the one with the earliest date.
    MIN(updated_at) AS session_date,
    session_id
  FROM
    `<DB_NAME>.<SCHEMA_NAME>.logrocket_events`
  WHERE
    event_type = 'session'
    AND app_id = '<YOUR APP ID>'
  GROUP BY
    session_id 
  ),
  users AS (
  SELECT
    user_id,
    session_id
  FROM
    `<DB_NAME>.<SCHEMA_NAME>.logrocket_events`
  WHERE
    event_type = 'user'
    AND app_id = '<YOUR APP ID>'
    AND user_id <> ''
    AND user_id NOT LIKE '%<INTERNAL IDENTIFIER>%'
    AND user_identity_status = 'Identified'
  )
SELECT
  session_day,
  COUNT(DISTINCT user_id) AS user_count
FROM (
  SELECT
    users.user_id AS user_id,
    users.session_id AS session_id,
    date(sessions_start.session_date) AS session_day
  FROM
    sessions_start
  INNER JOIN
    users
  ON
    users.session_id = sessions_start.session_id
    AND sessions_start.session_date BETWEEN '2023-05-07' and '2023-05-14'
)
GROUP BY
  session_day
ORDER BY session_day DESC

Top browsers by session count of identified users

WITH
  sessions_start AS (
  SELECT
    -- There can be many events with event_type = 'session' for the same session_id.
    -- Behavior such as a full page reload or opening the site in a new tab will
    -- trigger such an event; the start of a session is the one with the earliest date.
    MIN(updated_at) AS session_date,
    session_id,
    ANY_VALUE(session_browser) as browser
  FROM
    `<DB_NAME>.<SCHEMA_NAME>.logrocket_events`
  WHERE
    event_type = 'session'
    AND app_id = '<YOUR APP ID>'
  GROUP BY
    session_id 
  ),
  users AS (
  SELECT
    user_id,
    session_id
  FROM
    `<DB_NAME>.<SCHEMA_NAME>.logrocket_events`
  WHERE
    event_type = 'user'
    AND app_id = '<YOUR APP ID>'
    AND user_id <> ''
    AND user_id NOT LIKE '%<INTERNAL IDENTIFIER>%'
    AND user_identity_status = 'Identified'
  )
SELECT
  count(distinct session_id) as session_count,
  browser
FROM (
  SELECT
    distinct
    users.session_id as session_id,
    sessions_start.browser as browser
  FROM
    sessions_start
  INNER JOIN
    users
  ON
    users.session_id = sessions_start.session_id
    AND sessions_start.session_date BETWEEN '2023-05-07' and '2023-05-14'
)
GROUP BY
  browser
ORDER BY
  session_count DESC

Top devices by session count of identified users

WITH
  sessions_start AS (
  SELECT
    -- There can be many events with event_type = 'session' for the same session_id.
    -- Behavior such as a full page reload or opening the site in a new tab will
    -- trigger such an event; the start of a session is the one with the earliest date.
    MIN(updated_at) AS session_date,
    session_id,
    ANY_VALUE(session_device) as device
  FROM
    `<DB_NAME>.<SCHEMA_NAME>.logrocket_events`
  WHERE
    event_type = 'session'
    AND app_id = '<YOUR APP ID>'
  GROUP BY
    session_id 
  ),
  users AS (
  SELECT
    user_id,
    session_id
  FROM
    `<DB_NAME>.<SCHEMA_NAME>.logrocket_events`
  WHERE
    event_type = 'user'
    AND app_id = '<YOUR APP ID>'
    AND user_id <> ''
    AND user_id NOT LIKE '%<INTERNAL IDENTIFIER>%'
    AND user_identity_status = 'Identified'
  )
SELECT
  count(distinct session_id) as session_count,
  device
FROM (
  SELECT
    distinct
    users.session_id as session_id,
    sessions_start.device as device
  FROM
    sessions_start
  INNER JOIN
    users
  ON
    users.session_id = sessions_start.session_id
    AND sessions_start.session_date BETWEEN '2023-05-07' and '2023-05-14'
)
GROUP BY
  device
ORDER BY
  session_count DESC