Search word

PHOTO EMBED

Tue Nov 22 2022 19:37:03 GMT+0000 (Coordinated Universal Time)

Saved by @Janell #sql

---------------------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
content_copyCOPY