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
33 lines
1.1 KiB
Plaintext
33 lines
1.1 KiB
Plaintext
DESCRIPTION >
|
|
Top visiting Countries ordered by most visits.
|
|
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 location, uniqMerge(visits) as visits, countMerge(hits) as 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 location
|
|
order by visits desc
|
|
limit {{ Int32(skip, 0) }},{{ Int32(limit, 50) }}
|