Database Reference
In-Depth Information
This is a simple query that generates the
bounce rate
broken down by
browser. This query is quite uninteresting because you could easily access
the same data along with a nice chart with the Google Analytics web
interface. It is worth verifying that the results you get from a simple query
match what you see in the web interface. This can help verify that the correct
data is being transferred to your dataset. But, you are actually interested in
the new analysis that becomes feasible after you can access the raw hit data.
Here is a query that leverages the structure of the data and BigQuery's more
advanced query features to compute a more interesting result.
SELECT
yellow,
orange,
SUM(TotalTime)/SUM(Visits) avg_time
FROM (
SELECT
MAX(IF(hits.page.pagePath CONTAINS 'yellow', 1, 0))
WITHIN RECORD yellow,
MAX(IF(hits.page.pagePath CONTAINS 'orange', 1, 0))
WITHIN RECORD orange,
totals.timeOnSite TotalTime,
1 AS Visits
FROM [LondonCycleHelmet.ga_sessions_20130910])
GROUP BY 1, 2
The “Repeated Fields” section in Chapter 10, “Advanced Queries,”
introduced the scoped aggregation feature that allows you to control
aggregation operations. The feature is used here to compute new derived
properties for visits:
•
orange
indicates whether a user visited a web page with “orange” in
the path.
•
yellow
indicates whether a user visited a web page with “yellow” in the
path.
You can then use these derived properties as dimensions and compute the
average time spent per visit broken down by these dimensions.
After you understand the schema of the records, you can work with the
data in these tables just like any other table in BigQuery. As mentioned
