CODEWITHSUNDEEP
sqlgoogle-analyticsbigdatagoogle-bigquery
Meisam Ebrahimi
Meisam Ebrahimi

Reputation: 111

BigQuery flattening both GA session and hit level fields in the same query

In Standard SQL I want to be able to query the below all in the same query

So far, I've come up with something like this (includes a UNION of two GA properties, one for mobile, the other for desktop) - I will be adding a lot more columns than this and I can't imagine having them all as sub-selects is the best approach:

SELECT
# standard session fields
date,
fullVisitorId,
visitId,
visitNumber,
TIMESTAMP_SECONDS(visitStartTime) visitStartTime,
totals.visits,
device.deviceCategory,
totals.hits,
totals.newVisits,
totals.pageviews,
totals.timeOnSite,
trafficSource.adContent,
trafficSource.campaign,
trafficSource.keyword,
trafficSource.medium,
trafficSource.referralPath,
trafficSource.source,
channelGrouping,
device.browser,
device.browserSize,
device.browserVersion,
device.mobileDeviceInfo,
device.mobileDeviceModel,
device.operatingSystem,
device.mobileDeviceBranding,
geoNetwork.country,
geoNetwork.city,
# Session/User customDimension Example
(SELECT cd.value FROM UNNEST(customDimensions) cd WHERE cd.index=20 and REGEXP_CONTAINS(cd.value, "\\d")) userId,

# hits customMetrics Example
SUM((SELECT SUM(hcm.value) FROM UNNEST(hits) h,UNNEST(h.customMetrics) hcm WHERE hcm.index=28)) totalBooking,

# hits customDimension Example
SUM((SELECT COUNT(hcd.value) FROM UNNEST(hits) h,UNNEST(h.customDimensions) hcd WHERE hcd.index=20)) h_cd1,

# hits products customDimension Example
SUM((SELECT COUNT(hpc.value) FROM UNNEST(hits) h,UNNEST(h.product) hp,UNNEST(hp.customDimensions) hpc WHERE hpc.index=1)) h_pc1,
SUM((SELECT COUNT(hpc.value) FROM UNNEST(hits) h,UNNEST(h.product) hp,UNNEST(hp.customDimensions) hpc WHERE hpc.index=2)) h_pc2,
SUM((SELECT COUNT(hpc.value) FROM UNNEST(hits) h,UNNEST(h.product) hp,UNNEST(hp.customDimensions) hpc WHERE hpc.index=3)) h_pc3,
SUM((SELECT COUNT(hpc.value) FROM UNNEST(hits) h,UNNEST(h.product) hp,UNNEST(hp.customDimensions) hpc WHERE hpc.index=4)) h_pc4,
SUM((SELECT COUNT(hpc.value) FROM UNNEST(hits) h,UNNEST(h.product) hp,UNNEST(hp.customDimensions) hpc WHERE hpc.index=5)) h_pc5,
SUM((SELECT COUNT(hpc.value) FROM UNNEST(hits) h,UNNEST(h.product) hp,UNNEST(hp.customDimensions) hpc WHERE hpc.index=6)) h_pc6,
SUM((SELECT COUNT(hpc.value) FROM UNNEST(hits) h,UNNEST(h.product) hp,UNNEST(hp.customDimensions) hpc WHERE hpc.index=7)) h_pc7,
SUM((SELECT COUNT(hpc.value) FROM UNNEST(hits) h,UNNEST(h.product) hp,UNNEST(hp.customDimensions) hpc WHERE hpc.index=8)) h_pc8,
SUM((SELECT COUNT(hpc.value) FROM UNNEST(hits) h,UNNEST(h.product) hp,UNNEST(hp.customDimensions) hpc WHERE hpc.index=9)) h_pc9,

FROM (
  SELECT
  *
  FROM
  `abcdefgh.12345678.ga_sessions_*` desktopProperty
  WHERE
  _TABLE_SUFFIX BETWEEN '20180122' AND '20180122'
  UNION ALL
  SELECT
  *
  FROM
  `abcdefgh.12345678.ga_sessions_*` mobileProperty
  WHERE
  _TABLE_SUFFIX BETWEEN '20180122' AND '20180122'
  ) table
GROUP BY
  date,
  fullVisitorId,
  visitId,
  visitNumber,
  visitStartTime,
  totals.visits,
  device.deviceCategory,
  totals.hits,
  totals.newVisits,
  totals.pageviews,
  totals.timeOnSite,
  trafficSource.adContent,
  trafficSource.campaign,
  trafficSource.keyword,
  trafficSource.medium,
  trafficSource.referralPath,
  trafficSource.source,
  channelGrouping,
  device.browser,
  device.browserSize,
  device.browserVersion,
  device.mobileDeviceInfo,
  device.mobileDeviceModel,
  device.operatingSystem,
  device.mobileDeviceBranding,
  geoNetwork.country,
  geoNetwork.city,
  userId
ORDER BY
date

Upvotes: 0

Views: 1353

Answers (1)

Martin Weitzmann
Martin Weitzmann

Reputation: 4736

If you stay on session level you don't need a GROUP BY, because the source table already is on session scope. So you can get rid of that. Then you also have to remove the aggregation functions around your sub-selects. Simply bring everything to the desired scope -

Example for bringing different scopes to session level:

SELECT
  date AS sessionScope,
  (SELECT count(hitNumber) FROM t.hits) hitScope,
  (SELECT SUM(IF(cd.value='example',1,0)) FROM t.hits AS h, h.customDimensions cd WHERE cd.index=1) hitCdScope,
  (SELECT COUNT(p.productSku) FROM t.hits AS h, h.product AS p WHERE h.ecommerceaction.action_type='6') productScope
FROM
  `project.dataset.ga_sessions_20180127` AS t
WHERE
  totals.transactions > 0
LIMIT
  1000

Upvotes: 1

Related Questions