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
25 lines
664 B
Plaintext
25 lines
664 B
Plaintext
NODE analytics_pages_1
|
|
DESCRIPTION >
|
|
Aggregate by pathname and calculate session and hits
|
|
|
|
SQL >
|
|
SELECT
|
|
site_uuid,
|
|
member_uuid,
|
|
member_status,
|
|
post_uuid,
|
|
toDate(timestamp) AS date,
|
|
device,
|
|
browser,
|
|
location,
|
|
pathname,
|
|
uniqState(session_id) AS visits,
|
|
countState() AS hits,
|
|
countStateIf(logged_in = true) AS logged_in_hits,
|
|
countStateIf(logged_in = false) AS logged_out_hits
|
|
FROM analytics_hits
|
|
GROUP BY date, device, browser, location, pathname, member_uuid, member_status, post_uuid, site_uuid
|
|
|
|
TYPE MATERIALIZED
|
|
DATASOURCE analytics_pages_mv
|