The views

Everything eodia insights reads is in the analytics schema of the database. It contains only views, read-only for the eodia_analytics role. Three rules:

• every view has site_id: the insights row rule hooks onto it, so that a dashboard shows only one site;
• every view and every column has its comment, in French: insights reads them and shows them in its Structure screen and in the context of its AI assistant;
• they contain no IP address, no secret, and no person in the sense of an application account.

Days and hours (day, hour) are in the site’s time zone. The types are readable by Trino’s PostgreSQL connector: no arrays, jsonb read as json.

In eodia insights

Insights goes through Trino: a view is referred to there as catalog.analytics.page_views, the catalog being the name of the source. The examples on this page omit the catalog.

Audience

page_views

One row per page displayed, including address changes without a reload. The campaign and the channel are those of the visit.

Column	Contents
at, day, hour	the instant; the day and hour in the site’s time zone
page_view_id, session_id	the page view, the visit
visitor_id, visitor_kind	the visitor: daily hash (daily), measurement cookie (cookie), or none (none)
new_visitor	with a cookie: true on the day it was created, false afterwards; empty without a cookie
user_id, person	the signed-in visitor, matched across the whole visit; the person to count
entry	true for the entry page of the visit
hostname, path, title	the page, without the query string
referrer_host	the external referring site
source, medium, campaign, term, content, channel	the campaign and channel of the visit
click_id_type	the type of click identifier (gclid, fbclid…), never its value
country, device, browser, os, language, viewport_width	the country (ISO 3166), the device, the browser, the operating system, the language, the window width
engaged_seconds, scroll_depth	the engagement time, the scroll depth (%)
via	script (browser) or server (server-side collection)

sessions

One row per visit. A visit ends after 30 minutes of inactivity, or when a page arrives with a different campaign or from a different referring site.

Column	Contents
started_at, ended_at, day, hour	the start, the last activity
duration_seconds, engaged_seconds	the duration, the cumulative engagement time
visitor_id, visitor_kind, new_visitor, user_id, person	the visitor and the person
page_views, events	the number of page views and events
conversions, conversion_value	the goals reached and their value
engaged, bounce	engaged visit as in GA4 (10 s of engagement, 2 page views or a conversion); bounce otherwise
entry_path, exit_path	the entry and exit pages
referrer_host, source, medium, campaign, term, content, channel, click_id_type	the origin of the visit
country, device, browser, os, language	the visitor

events and event_properties

events: one row per event: track, automatic measurements, e-commerce, server-side sends.

Column	Contents
at, day, event_id	the instant, the day, the event
name, label, declared	the name; the label, and true if the event is declared
page_view_id, session_id, path	the page and the visit
visitor_id, visitor_kind, user_id, person	the visitor and the person
props	the properties, as JSON (items included)
via	script or server

event_properties flattens the properties: one row per event and per property: event, property, type, and one column per type: value_text, value_number, value_boolean, value_time.

evt_<name>

Generated: one view per declared event, analytics.evt_newsletter_signup, analytics.evt_purchase… They carry the common columns (site_id, at, day, event_id, session_id, page_view_id, visitor_id, user_id, person, path, via) and add one typed column per declared property, with its label and description as a comment. They are regenerated whenever the metadata changes.

SELECT placement, count(*) AS signups
FROM analytics.evt_newsletter_signup
WHERE day >= current_date - INTERVAL '30' DAY
GROUP BY placement

Goals and e-commerce

View	One row per	Main columns
conversions	goal reached and visit	goal_id, goal, conversion_id, session_id, person, path, value
purchases	order or refund (deduplicated on transaction_id)	kind, transaction_id, value (negative for a refund), currency, tax, shipping, coupon, items, session_id, person, received, confirmed
commerce_items	item and step	step, transaction_id, item_id, item_name, category, brand, variant, price, quantity, revenue, currency, position

See e-commerce.

Journey and attribution

View	One row per	Main columns
touchpoints	visit of a person, in order	person, session_id, started_at, rank, channel, source, medium, campaign
attribution	conversion, visit and model	model, conversion_kind, conversion, converted_at, session_id, touchpoint, touchpoints, channel, credit, value, days_before

See attribution.

Clicks and zones

View	One row per	Main columns
clicks	click	page_view_id, path, selector, zone, x, y (0 to 1 within the element), viewport_width, device
zone_views	zone and page view	zone, label, visible_seconds, seen (visible for at least one second)

See zones.

Reference data and metadata

View	Contents
sites	the sites: name, key, domains, timezone, tracking_mode, identified_visitors, attribution_days, created_at, archived, to display names
annotations	the annotations, one row per site
cls_<name>	generated: the imported classifications, key and one column per attribute
event_definitions	the declared events: name, label, description, system, properties
zones	the declared zones: zone, label, description
goals	the goals: name, kind (page or event), path_pattern, event_name, value_mode, value
channels	the channels in the order of their rules: position, channel, description

A few queries

-- Visits and bounce rate by channel, this month
SELECT channel, count(*) AS visits, avg(CASE WHEN bounce THEN 1.0 ELSE 0 END) AS bounce_rate
FROM analytics.sessions
WHERE day >= date_trunc('month', current_date)
GROUP BY channel
ORDER BY visits DESC

-- Unique people per week (cookie or matched identity)
SELECT date_trunc('week', day) AS week, count(DISTINCT person) AS people
FROM analytics.sessions
WHERE visitor_kind = 'cookie'
GROUP BY 1
ORDER BY 1

Unique visitors without cookies

In cookieless mode, visitor_id changes every day: a unique visitor is counted per day. Adding up unique visitors over several days counts the same browser several times.