How do the page type, device, browser, etc. affect the page load speed or server response 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.

The query shows average pageload time and server responce time by dimensions: device category, page type or browser.

Dynamic parameters

• dimension — name of a dimension (device, browser or pageType) for the metrics Average pageload time and Server response time,
• dataSetName — name of Google BigQuery dataset with Google Analytics sessions data,
• tableName — name of a table with the data,
• startDate — start date of the analyzed period,
• endDate — end date of the analyzed period.

Visualization example

SQL Query

SELECT
  {dimension default="device" type="select" values="device,browser,pageType"},
  date,
  AVG(avgPageLoadTime) as avgPageLoadTime,
  AVG(avgServerResponseTime) as avgServerResponseTime
FROM (
  SELECT
    device.deviceCategory AS device,
    device.browser AS browser,
    hits.pageType AS pageType,
    date,
    hits.timingInfo.pageLoad/1000 AS avgPageLoadTime,
    hits.timingInfo.serverResponse/1000 AS avgServerResponseTime
  FROM
    TABLE_DATE_RANGE({dataSetName default="OWOXBI_Streaming" type="input"}.{tableName default="session_streaming_" type="input"}, TIMESTAMP('{startDate default="2016-08-10" type="datetime"}'), TIMESTAMP('{endDate default="2016-08-13" type="datetime"}'))
  WHERE
    hits.timingInfo.pageLoad IS NOT NULL
    AND hits.timingInfo.serverResponse IS NOT NULL
  GROUP BY
    device,
    browser,
    pageType,
    date,
    avgPageLoadTime,
    avgServerResponseTime
  ORDER BY
    date )
GROUP BY
  {dimension},
  date
ORDER BY
  date