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
37 lines
1.0 KiB
Plaintext
37 lines
1.0 KiB
Plaintext
|
|
DESCRIPTION >
|
|
Visits trend over time for the last 30 minutes, filling the blanks.
|
|
Works great for the realtime chart.
|
|
|
|
TOKEN "dashboard" READ
|
|
|
|
NODE timeseries
|
|
DESCRIPTION >
|
|
Generate a timeseries for the last 30 minutes, so we call fill empty data points
|
|
|
|
SQL >
|
|
with (now() - interval 30 minute) as start
|
|
select addMinutes(toStartOfMinute(start), number) as t
|
|
from (select arrayJoin(range(1, 31)) as number)
|
|
|
|
NODE hits
|
|
DESCRIPTION >
|
|
Get last 30 minutes metrics gropued by minute
|
|
|
|
SQL >
|
|
%
|
|
select toStartOfMinute(timestamp) as t, uniq(session_id) as visits
|
|
from analytics_hits
|
|
where
|
|
site_uuid = {{String(site_uuid, 'mock_site_uuid', description="Tenant ID", required=True)}} and
|
|
timestamp >= (now() - interval 30 minute)
|
|
group by toStartOfMinute(timestamp)
|
|
order by toStartOfMinute(timestamp)
|
|
|
|
NODE endpoint
|
|
DESCRIPTION >
|
|
Join and generate timeseries with metrics for the last 30 minutes
|
|
|
|
SQL >
|
|
select a.t, b.visits from timeseries a left join hits b on a.t = b.t order by a.t
|