---------------------Example 1 SELECT hits.page.searchKeyword as searchKeyword, COUNT(hits.page.searchKeyword) AS HIT_COUNT, COUNT(DISTINCT sessions.fullVisitorId) AS USER_COUNT, COUNT(DISTINCT CONCAT(sessions.fullVisitorId, CAST(sessions.visitStartTime AS STRING))) AS SESSION_COUNT FROM `digicce.142980449.ga_sessions_*` sessions, UNNEST(sessions.hits) as hits WHERE _TABLE_SUFFIX BETWEEN '20200701' AND '20211001' -- _TABLE_SUFFIX = '20210401' AND hits.page.hostname = "www.pearson.com" AND REGEXP_CONTAINS(hits.page.pagePath, r"\/pearsonplus") -- AND REGEXP_CONTAINS(hits.page.pagePath, r"^\/(en-us|(store\/(en-us|p)))") AND hits.page.searchKeyword is not null GROUP BY hits.page.searchKeyword ORDER BY HIT_COUNT DESC ---------------------Example 2 SELECT date, -- clientId, sessions.fullVisitorId AS fullVisitorId, CONCAT(sessions.fullVisitorId, CAST(sessions.visitStartTime AS STRING)) as visitIdentifier, hits.eventInfo.eventCategory as hitEventCategory, hits.eventInfo.eventAction as hitEventAction, hits.eventInfo.eventLabel as hitEventLabel, REGEXP_EXTRACT(hits.page.pagePath, r"^([^\?]+)") as pagePath, hits.page.searchKeyword as searchKeyword, hits.type as hitType, hits.hitNumber as hitNumber -- hits.product AS product, -- hits.transaction AS transaction, -- (SELECT MAX(cd.value) FROM UNNEST(hits.customDimensions) cd WHERE cd.index=13) as pageCategory -- (SELECT MAX(cd.value) FROM UNNEST(hits.customDimensions) cd WHERE cd.index=26) as countryLocale -- sessions.channelGrouping AS channelGrouping FROM `digicce.142980449.ga_sessions_*` sessions, UNNEST(sessions.hits) as hits WHERE sessions.totals.visits > 0 -- AND _TABLE_SUFFIX BETWEEN '20210101' AND '20210419' AND _TABLE_SUFFIX = '20210401' AND hits.page.hostname = "www.pearson.com" AND REGEXP_CONTAINS(hits.page.pagePath, r"^\/(en-us|(store\/(en-us|p)))") AND ( ( hits.type = "PAGE" ) OR ( hits.type = "EVENT" AND hits.eventInfo.eventCategory IN ('ecommerce') AND hits.eventInfo.eventAction IN ('purchase') ) ) ORDER BY fullVisitorId, visitIdentifier, hitNumber
Preview:
downloadDownload PNG
downloadDownload JPEG
downloadDownload SVG
Tip: You can change the style, width & colours of the snippet with the inspect tool before clicking Download!
Click to optimize width for Twitter