"Google Analytics and AdSense Data Analysis in BigQuery" @ Google I/O 2013, https://developers.google.com/events/io/sessions/332940014

01_Top Channels Query AdSense.sql

SELECT
  custom_channel_id,
  SUM(earnings) AS earnings
FROM
  [google.com:adsense-reports:Reports.DailyCustomChannelReport]
GROUP BY
  custom_channel_id
ORDER BY
  earnings DESC
LIMIT
  5;

02_Top Growing Channels Query AdSense.sql

SELECT
  jan_report.custom_channel_name,
  feb_report.clicks_sum - jan_report.clicks_sum AS clicks_delta
FROM
  (SELECT custom_channel_name, SUM(clicks) as clicks_sum
  FROM [..DailyCustomChannelReport]
  WHERE date >= '20130101' AND date < '20130201'
  GROUP BY custom_channel_name) AS jan_report
INNER JOIN
  (.. WHERE date >= '20130201' AND date < '20130301' ..) AS feb_report
ON jan_report.custom_channel_name = feb_report.custom_channel_name
ORDER BY clicks_delta DESC 
LIMIT 5;

03_Debugging Errors Step1.sql

SELECT
  visitorId AS visitorId1,
  visitId AS visitId1,
  hits.customVariables.customVarValue as errorType,
  hits.hitNumber
FROM
  [gumtree-uk-ga:google_analytics.sessions_20130421] AS t1
WHERE
  hits.customVariables.index = 1
  AND REGEXP_MATCH(hits.customVariables.CustomVarValue, '400|500');

04_Debugging Errors Step2.sql

SELECT
  errorType.visitorId1,
  errorType.visitId1,
  errorType.errorType,
  MAX(previousHit.hits.hitNumber) AS hitNumber
FROM
  (SELECT
  ...
  ) as errorType
INNER JOIN EACH
  (FLATTEN ([gumtree-uk-ga:google_analytics.sessions_20130421]), hits.hitNumber)) AS previousHit
  ON errorType.visitorId1 = previousHit.visitorId
  AND errorType.visitId1 = previousHit.visitId
WHERE 
  errorType.hits.hitNumber < previousHit.hits.hitNumber
GROUP EACH BY 1,2,3;

05_Debugging Errors Step3.sql

SELECT
  pageName.hits.hour AS hour,
  hitFinder.errorType AS errorType,
  pageName.hits.customVariables.customVarValue AS pageName,
  COUNT(*) as errorCount
FROM
  (FLATTEN ([gumtree-uk-ga:google_analytics.sessions_20130421]), hits.hitNumber)) AS pageName
INNER JOIN EACH
  (SELECT
  ...
  ) AS hitFinder
  ON pageName.visitorId = hitFinder.visitorId1
  AND pageName.visitId = hitFinder.visitId1
  AND pageName.hits.hitNumber = hitFinder.hitNumber
WHERE
  pageName.hits.customVariables.index=1
GROUP EACH BY 1,2,3
ORDER BY 4 DESC;

06_Segmenting by TV region Step2.sql

SELECT
  hits.customVariables.customVarValues AS location_level_2,
  visitId,
  visitorId,
  COUNT(hits.hitNumber) AS pageviews,
  SUM(totals.bounces) AS bounces
FROM
  [gumtree-uk-ga:google_analytics.sessions_20130421]
WHERE
  hits.customVariables.index = 4
GROUP EACH BY 1,2,3;

07_Segmenting by TV region Step3.sql

SELECT
  location_level_2,
  location_level_1 AS tv_region
FROM
  [gumtree-uk-ga:gumtree_uk_metadata.location]
WHERE
  location_level_1 IN ('North West',
  'South',
  'Midlands',
  'South West')
GROUP BY 1,2;

08_Segmenting by TV region Step4.sql

SELECT
  location.tv_region,
  COUNT(DISTINCT CONCAT(STRING(ga.visitorId),'-',STRING(ga.visitId))) AS VISITS,
  COUNT(DISTINCT ga.visitorId) AS visitors,
  SUM(ga.pageviews) AS pageviews,
  SUM(ga.bounces) AS bounces
FROM
  (SELECT
    ...
  ) AS ga
INNER JOIN 
  (SELECT
    ...
  ) AS location
  ON ga.location_level_2 = location.location_level_2
GROUP BY 1;

09_A|B Testing - AdSense & Analytics Step1.sql

SELECT
  custom_channel_name AS adSenseGroup,
  SUM(ad_requests) AS ad_requests,
  SUM(matched_ad_requests) AS matched_ad_requests,
  SUM(clicks) AS clicks,
  SUM(earnings) AS earnings
FROM
  [874385582184:Reports.DailyCustomChannelReport]
WHERE
  REGEXP_MATCH(ad_client_id,'gumtree-com')
GROUP BY 1;

10_A|B Testing - AdSense & Analytics Step2.sql

SELECT
  hits.customVariables.customVarValue as adSenseGroup,
  COUNT(DISTINCT CONCAT(STRING(visitorId),'-',STRING(visitId))) AS visits,
  COUNT(DISTINCT visitorId) AS visitors,
  COUNT(hits.hitNumber) AS pageviews
FROM
  [gumtree-uk-ga:google_analytics.sessions_20130421]
WHERE
  hits.customVariables.index=42
GROUP BY 1;

10_A|B Testing - AdSense & Analytics Step3.sql

SELECT 
  ga.adSenseGroup,
  ga.visits,
  adSense.matched_ad_requests,
  FLOAT(adSense.clicks/adSense.matched_ad_requests) AS CTR,
  FLOAT(adSense.matched_ad_requests/ga.visitors) AS matched_ad_requests_per_visitor,
  FLOAT(ga.pageviews/ga.visits) AS pageviews_per_visit
FROM
  (SELECT
  ...
  ) AS ga
INNER JOIN 
  (SELECT
  ...
  ) AS adSense
  ON ga.adSenseGroup = adSense.adSenseGroup;