How to calculate visit start time?

NEW documentation: This article covers setup for OWOX BI. For the latest guides on setting up FREE connectors and Data Marts, visit the new OWOX Data Marts documentation.

This SQL query shows the start time of the session.

Parameters to set in the request

• project-name — the name of the Google BigQuery project containing user session data uploaded via OWOX BI;
• dataset-name — the name of the Google BigQuery dataset containing the session data;
• _TABLE_SUFFIX — the date range you want to view data for.

SQL query

#standardSQL
SELECT
  sessionId,
  TIMESTAMP_MILLIS(timestamp) visitStartTime
FROM (
  SELECT
    sessionId,
    hits.timestamp,
    ROW_NUMBER() OVER(PARTITION BY sessionId ORDER BY hits.timestamp ASC) rowf
  FROM
    `project-name.dataset-name.owoxbi_sessions_*`,
    UNNEST (hits) hits
    WHERE _TABLE_SUFFIX BETWEEN '20191213' AND '20191214'
)
WHERE
  rowf = 1