Data schema and export | Pear Metrics

It is a Postgres database and it is yours. Here is what is in it, so you can query it directly, and so that 'take your data with you' means something specific.

The marketing tables

Everything the marketing layer adds is prefixed ma_. Nothing in this list has a foreign key into the analytics tables, so the two halves can be queried, backed up and reasoned about separately.

Table	What it holds
`ma_website_settings`	Reporting timezone and currency, one row per site.
`ma_session_attribution`	One row per session, written at its first event: the UTM triple, the derived channel, and the name of the platform whose click ID was present. Never the click ID itself.
`ma_ad_account`	A connected Google Ads or Meta account, with the currency and timezone read from the platform. Any per-account credential override is encrypted at rest.
`ma_ad_campaign`	Platform campaigns. Names are refreshed on every sync.
`ma_ad_spend_daily`	Daily campaign spend, impressions and clicks. Unique on campaign and date, which is what makes the trailing seven-day re-pull an upsert rather than a duplicate.
`ma_campaign_map`	The join layer: one platform campaign to one UTM triple, marked as automatic or manual.
`ma_conversion_event`	Which event names count as conversions for a site.
`ma_sync_log`	Every sync run, the range it covered, and why it failed if it did.

The analytics tables

These hold the counting itself: sessions, page views and events, one row at a time and never aggregated away.

Table	What it holds
`website`	Your sites and their IDs.
`session`	One row per session: the rotating hash, and coarse device, browser, OS and country fields. No IP address, no persistent identifier.
`website_event`	Page views and custom events, with the URL path and query string. Click ID values are replaced with a literal placeholder before the row is written.
`event_data`	Properties attached to custom events, one row per key.

What you will not find

No IP column. No cookie ID. No persistent visitor ID. No email, name or account identifier, unless you put one in an event property yourself, which is why the custom events page asks you not to.

The join, in SQL

This is here to show what the product is doing, not because you will run it: the database is ours and shared, so there are no credentials to hand out. The join at the centre of it is not complicated, and it looks roughly like this:

select
  c.name                                    as campaign,
  sum(s.spend)                              as spend,
  count(distinct a.session_id)              as sessions,
  count(distinct e.session_id)              as conversions,
  sum(s.spend) / nullif(count(distinct e.session_id), 0) as cpa
from ma_ad_spend_daily s
join ma_ad_campaign  c on c.campaign_id = s.campaign_id
join ma_campaign_map m on m.campaign_id = c.campaign_id
left join ma_session_attribution a
  on a.utm_source   = m.utm_source
 and a.utm_medium   = m.utm_medium
 and a.utm_campaign = m.utm_campaign
left join website_event e
  on e.session_id = a.session_id
 and e.event_name = 'signup'
where s.date >= current_date - interval '30 days'
group by c.name
order by spend desc;

Two things that query glosses over and the product does not: day boundaries are cut in the site’s reporting timezone rather than UTC, and derived metrics are recomputed from summed components rather than averaged from child rows. Averaging a ratio of ratios is how a group total ends up disagreeing with the rows underneath it.

Export

CSV of any analytics table, from the dashboard, respecting whatever filters and date range are applied.
A zip of the analytics views for a date range: events, pages, referrers, browsers, operating systems, devices and countries, one CSV each.

What is not in either of those yet is the marketing layer: spend, mappings and CPA have no export button, so getting the campaign table out means copying it from the screen. That is a gap rather than a policy, and this page will say so differently when it is closed.

On cancellation you keep dashboard access for thirty days, which is your window to export what you want, and after that the account and its data are deleted. No exit fee, no retention period, no negotiation about getting your own numbers back.

Ask at hello@pearmetrics.com.