TryGhost/Ghost

DESCRIPTION >
    Parsed `page_hit` events, implementing `browser` and `device` detection logic.

TOKEN "dashboard" READ
TOKEN "stats page" READ

NODE parsed_hits
DESCRIPTION >
    Parse raw page_hit events

SQL >
    SELECT
        timestamp,
        action,
        version,
        coalesce(session_id, '0') as session_id,
        JSONExtractString(payload, 'locale') as locale,
        JSONExtractString(payload, 'location') as location,
        JSONExtractString(payload, 'referrer') as referrer,
        JSONExtractString(payload, 'pathname') as pathname,
        JSONExtractString(payload, 'href') as href,
        JSONExtractString(payload, 'site_uuid') as site_uuid,
        JSONExtractString(payload, 'member_uuid') as member_uuid,
        JSONExtractString(payload, 'member_status') as member_status,
        JSONExtractString(payload, 'post_uuid') as post_uuid,
        lower(JSONExtractString(payload, 'user-agent')) as user_agent
    FROM analytics_events
    where action = 'page_hit'

NODE endpoint
SQL >
    SELECT
        site_uuid,
        timestamp,
        action,
        version,
        session_id,
        member_uuid,
        member_status,
        post_uuid,
        location,
        domainWithoutWWW(referrer) as source,
        pathname,
        href,
        case
            when match(user_agent, 'wget|ahrefsbot|curl|urllib|bitdiscovery|\+https://|googlebot')
            then 'bot'
            when match(user_agent, 'android')
            then 'mobile-android'
            when match(user_agent, 'ipad|iphone|ipod')
            then 'mobile-ios'
            else 'desktop'
        END as device,
        case
            when match(user_agent, 'firefox')
            then 'firefox'
            when match(user_agent, 'chrome|crios')
            then 'chrome'
            when match(user_agent, 'opera')
            then 'opera'
            when match(user_agent, 'msie|trident')
            then 'ie'
            when match(user_agent, 'iphone|ipad|safari')
            then 'safari'
            else 'Unknown'
        END as browser
    FROM parsed_hits