Last active
December 22, 2015 22:59
-
-
Save micheleorsi/6543829 to your computer and use it in GitHub Desktop.
"Google Analytics and AdSense Data Analysis in BigQuery" @ Google I/O 2013, https://developers.google.com/events/io/sessions/332940014
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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'); |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment