08bf49eaec
ref https://linear.app/tryghost/issue/ANAL-27/setup-tinybird-project-and-cicd ref https://github.com/tinybirdco/web-analytics-starter-kit/blob/main/tinybird/pipes/analytics_sessions.pipe - These datasources and pipes work together to define the main endpoints we need for our stats dashboard - They are based on the web analytics starter kit from tinybird - We've updated them to handle site_uuid - There's more to do to pipe the member-related and post-related data through the system yet
72 lines
2.2 KiB
Plaintext
72 lines
2.2 KiB
Plaintext
DESCRIPTION >
|
|
Parsed `page_hit` events, implementing `browser` and `device` detection logic.
|
|
|
|
TOKEN "dashboard" 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,
|
|
case
|
|
when member_uuid REGEXP '^[0-9a-fA-F]{8}-[0-9a-fA-F]{4}-[0-9a-fA-F]{4}-[0-9a-fA-F]{4}-[0-9a-fA-F]{12}$'
|
|
then true
|
|
else false
|
|
END as logged_in,
|
|
member_uuid,
|
|
member_status,
|
|
post_uuid,
|
|
location,
|
|
referrer,
|
|
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
|