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
39 lines
1.2 KiB
Plaintext
39 lines
1.2 KiB
Plaintext
DESCRIPTION >
|
|
Most visited pages for a given period.
|
|
Accepts `date_from` and `date_to` date filter. Defaults to last 7 days.
|
|
Also `skip` and `limit` parameters for pagination.
|
|
|
|
TOKEN "dashboard" READ
|
|
|
|
NODE endpoint
|
|
DESCRIPTION >
|
|
Group by pagepath and calculate hits and visits
|
|
|
|
SQL >
|
|
%
|
|
select
|
|
pathname,
|
|
uniqMerge(visits) as visits,
|
|
countMerge(hits) as hits,
|
|
countMerge(logged_in_hits) as logged_in_hits,
|
|
countMerge(logged_out_hits) as logged_out_hits
|
|
from analytics_pages_mv
|
|
where
|
|
site_uuid = {{String(site_uuid, 'mock_site_uuid', description="Tenant ID", required=True)}} and
|
|
{% if defined(date_from) %}
|
|
date
|
|
>=
|
|
{{ Date(date_from, description="Starting day for filtering a date range", required=False) }}
|
|
{% else %} date >= timestampAdd(today(), interval -7 day)
|
|
{% end %}
|
|
{% if defined(date_to) %}
|
|
and date
|
|
<=
|
|
{{ Date(date_to, description="Finishing day for filtering a date range", required=False) }}
|
|
{% else %} and date <= today()
|
|
{% end %}
|
|
|
|
group by pathname
|
|
order by visits desc
|
|
limit {{ Int32(skip, 0) }},{{ Int32(limit, 50) }}
|