This script helps you creating sessions from Google Analytics raw data on hit level in Google Big Query. The export will also include some metrics related to site speed based on Google's Latency Tracking KPIs.

Big Query Google Analytics sessions export including site speed metrics.sql

SELECT
  first_sessions.sid                                                               AS sessionId,
  visitorId,
  first_transactions.transactionId                                                 AS transactionId,
  timestamp,
  deviceCategory,
  landingPage,
  pageviews,
  timeOnSite,
  channel,
  speed_measures.domInteractiveTime                                                AS sessionDomInteractive,
  speed_measures.domContentLoadedTime                                              AS sessionDomContentLoaded,
  speed_measures.pageLoadTime                                                      AS sessionPageLoaded,
  first_page_measures.first_domInteractiveTime                                     AS firstDomInteractive,
  first_page_measures.first_domContentLoadedTime                                   AS firstDomContentLoaded,
  first_page_measures.first_pageLoadTime                                           AS firstPageLoaded
FROM   (SELECT
        ABS(HASH(fullVisitorId))                                                   AS visitorId,
        FORMAT_UTC_USEC(visitstarttime * 1000000)                                  AS timestamp,
        CONCAT(fullVisitorId, STRING(visitId))                                     AS sid,
        trafficsource.medium                                                       AS channel,
        device.deviceCategory                                                      AS deviceCategory,
        FIRST(SPLIT(hits.page.pagePath, '?'))                                      AS landingPage,
        totals.pageviews                                                           AS pageviews,
        totals.timeOnSite                                                          AS timeOnSite,
        hits.hitNumber                                                             AS hitNumber
FROM    (SELECT * FROM TABLE_DATE_RANGE([dataset_name],TIMESTAMP('DATE'),TIMESTAMP('DATE')))                            AS campaign_code
LEFT JOIN 
       (SELECT
        fullVisitorId                                                               AS sl_fullVisitorId,
        visitId                                                                     AS sl_visitId,
        min(hits.hitNumber)                                                         AS min_hit_number
        FROM   (SELECT * FROM TABLE_DATE_RANGE([dataset_name],TIMESTAMP('DATE'),TIMESTAMP('DATE')))
        GROUP BY sl_fullVisitorId, sl_visitId) AS session_length
        ON campaign_code.fullVisitorId = session_length.sl_fullVisitorId AND campaign_code.visitId = session_length.sl_visitId
        WHERE (hits.hitNumber = 1 OR hits.hitNumber = min_hit_number)) AS first_sessions
LEFT JOIN 
       (SELECT
        CONCAT(fullVisitorId, STRING(visitId))                                      AS sid,
        MIN(hits.transaction.transactionId)                                         AS transactionId
        FROM (SELECT * FROM TABLE_DATE_RANGE([dataset_name],TIMESTAMP('DATE'),TIMESTAMP('DATE')))
        WHERE hits.eCommerceAction.action_type = "6" OR hits.type = "TRANSACTION" GROUP BY sid) AS first_transactions
        ON first_sessions.sid = first_transactions.sid
LEFT JOIN 
       (SELECT
        CONCAT(fullVisitorId, STRING(visitId))                                      AS sid,
        AVG (hits.latencyTracking.domContentLoadedTime)                             AS domContentLoadedTime,
        AVG (hits.latencyTracking.pageLoadTime)                                     AS pageLoadTime,
        AVG (hits.latencyTracking.domInteractiveTime)                               AS domInteractiveTime
        FROM (SELECT * FROM TABLE_DATE_RANGE([dataset_name],TIMESTAMP('DATE'),TIMESTAMP('DATE')))
        WHERE hits.latencyTracking.pageLoadSample = 1 GROUP BY sid) AS speed_measures
        ON first_sessions.sid = speed_measures.sid
 LEFT JOIN 
       (SELECT
        sid,
        first_domContentLoadedTime,
        first_domInteractiveTime,
        first_pageLoadTime
        FROM (SELECT
              CONCAT(fullVisitorId, STRING(visitId))                                AS sid,
              hits.latencyTracking.domContentLoadedTime                             AS first_domContentLoadedTime,
              hits.latencyTracking.pageLoadTime                                     AS first_pageLoadTime,
              hits.latencyTracking.domInteractiveTime                               AS first_domInteractiveTime,
              hits.hitNumber                                                        AS hit_number,
              MIN(hit_number) OVER (PARTITION BY sid)                               AS min_hit
              FROM (SELECT * FROM TABLE_DATE_RANGE([dataset_name],TIMESTAMP('DATE'),TIMESTAMP('DATE')))
              WHERE hits.latencyTracking.pageLoadSample = 1 GROUP BY sid,first_domContentLoadedTime,first_pageLoadTime,first_domInteractiveTime,hit_number)
        WHERE hit_number = min_hit) AS first_page_measures
        ON first_sessions.sid = first_page_measures.sid
WHERE speed_measures.domContentLoadedTime > 0