Database Reference
In-Depth Information
earlier, one of the most useful features of having your Google Analytics
data accessible in BigQuery is that you can join it with other data you
have uploaded. The sample dataset contains a refunds table with a
TransactionId
field that can be joined against the
hits.transaction.transactionId
field in the traffic data table. For
completeness here is a query that performs a join to compute the average
refund broken down by traffic source. All the techniques used in the query
should be familiar from Chapter 10. Note the use of
FLATTEN
to allow
joining with a repeated field.
SELECT
traffic_data.source,
SUM(refunds.RefundAmount)/COUNT(traffic_data.tid)
refund
FROM FLATTEN(
(SELECT
trafficSource.source source,
hits.transaction.transactionId tid
FROM
[LondonCycleHelmet.ga_sessions_20130910]
), tid) traffic_data
INNER JOIN
[LondonCycleHelmet.refunds_201309] refunds
ON traffic_data.tid=refunds.TransactionId
GROUP BY 1
The discussion of querying this data wraps up with a recipe for dealing with
custom dimensions, metrics, and variables. These dynamic properties can
be a bit awkward to work with because they do not fit comfortably in a query
language intended to work with explicitly specified fields. You can use the
WITHIN
operator to
pivot
these fields in an inner query so that the outer
query can treat your custom property as a regular field. Here is a sample
query:
SELECT * FROM (
SELECT
visitId, hits.hitNumber,
MAX(IF(hits.customDimensions.index = 1,
hits.customDimensions.value, NULL))
