Database Reference
In-Depth Information
•
transaction
•
customVariables
•
customDimensions
•
customMetrics
This structure may appear rather complex, especially when compared to
schemas in relational databases. However, it is quite natural considering
the data collected. As we discussed, Google Analytics organizes user actions
(hits) on your web property into sessions (visits). Each record in this table
corresponds to a single visit, and the visit contains the corresponding list of
hits. Each visit has a field describing data that is common across all the hits
in the visit, for example, the device used to access your property. Each hit
subrecord contains data specific to the hit, for example, the page that was
accessed.
As we mentioned earlier, the data collected can also be customized. Google
Analytics allows you to instrument your application and add data to each
record; these are recorded in the repeated
custom[
Variables
/
Dimensions
/
Metrics
]
fields. Dimensions that
are common across hits appear at the top level, and hit-specific custom
properties appear within each hit record.
The
totals
field that appears at the top level is actually a summary of the
hits collection. In principle it is possible to compute the information present
in the totals from the collection of hits, but it is convenient to have these visit
metrics precomputed.
Querying the Tables
Now that you have a basic feel for the shape of the data, try a simple query.
In the queries that follow, the table name has been abbreviated to meet
formatting requirements, but the queries in the supporting material for this
chapter use the fully qualified name.
SELECT
device.browser,
SUM(totals.bounces)/SUM(totals.visits) bounce_rate
FROM [LondonCycleHelmet.ga_sessions_20130910]
GROUP BY 1
