Metrickle
Start free

Warehouse export

Last updated

The warehouse export writes every day's events to files you can load into BigQuery, Snowflake or any warehouse that reads JSON. Metrickle keeps a hosted copy for 30 days, and can also deliver the files to your own S3-compatible bucket. It's on the Pro, Agency and Enterprise plans. For smaller, ad hoc reads, use the query API.

Set it up

Go to Workspace → Data export in the dashboard. Setting up, changing or removing the export needs the warehouse.manage permission, which owners and admins have and API tokens never carry. Anyone with data.export can see the settings and download hosted files.

Choose:

  • A start day. The first run exports from that day, up to two years back, a week at a time until it's caught up.
  • Where the files go. Hosted by Metrickle only, or also copied to your own bucket.

Setting up, changing or removing the export, and asking for a run, are recorded in the workspace's audit log. Each run, and each hosted file downloaded, is recorded as Data exported.

What's written

The export runs once a day, a little after 04:00 UTC. Files are gzipped NDJSON, one event per line:

events/app={app id}/dt={YYYY-MM-DD}/part-{received from}-{n}.ndjson.gz
erasures/dt={YYYY-MM-DD}/erasures-{run}.ndjson.gz
runs/{run}.json
  • Events are split by app and by the UTC day of the event's time. Each line has the same fields as the query API's events: app_id, id, ts, type, name, visitor_id, user_id, session_id, path, url, title, referrer, referrer_domain, the five utm_ fields, country, region, city, platform, device_type, os, browser, app_version, screen_w, screen_h, locale, value, a11y, properties and received_at. The folder is app=, not app_id=, so it never clashes with the app_id field.
  • Erasures list people whose data was erased (see Erasure).
  • Runs are a manifest for each run: the days, files and row counts.

Session replays, screenshots, feedback text, study participants' details and IP addresses are never exported.

Late events and repeats

Each run exports new complete days, and checks the last 3 exported days again for events that arrived late, such as from a phone that was offline. An event that arrives more than 3 days late only reaches the export if you export again from an earlier start day.

A retried run rewrites its own files, so a file you've already loaded can come back with more rows. Load with deduplication on app_id and id. The recipes below do.

Your own bucket

Any S3-compatible store works: Amazon S3, Cloudflare R2, Google Cloud Storage (with interoperability HMAC keys, endpoint https://storage.googleapis.com, region auto), Backblaze B2 or MinIO. Files are copied to the same paths under the folder you choose.

  • Give the key write access to that bucket only (s3:PutObject). s3:DeleteObject is optional and only used to remove the connection test file.
  • Test connection writes and deletes _metrickle/connection-check.txt.
  • The secret is encrypted and never shown or logged. Changing the endpoint or access key ID means entering the secret again.
  • If delivery fails, the run fails and the next run tries the same days again. The settings page shows the storage error code.

The hosted copy of an EU workspace is stored in the EU. Delivery to your own bucket goes wherever that bucket is. See data location.

Load into BigQuery

BigQuery reads from Cloud Storage or Amazon S3. With R2 or another store, copy the files into Cloud Storage first, or point the export straight at a Cloud Storage bucket with HMAC keys.

# The table, with properties and a11y as JSON.
bq mk --table metrickle.events_raw \
  app_id:STRING,id:STRING,ts:INT64,type:STRING,name:STRING,visitor_id:STRING,user_id:STRING,session_id:STRING,path:STRING,url:STRING,title:STRING,referrer:STRING,referrer_domain:STRING,utm_source:STRING,utm_medium:STRING,utm_campaign:STRING,utm_term:STRING,utm_content:STRING,country:STRING,region:STRING,city:STRING,platform:STRING,device_type:STRING,os:STRING,browser:STRING,app_version:STRING,screen_w:INT64,screen_h:INT64,locale:STRING,value:FLOAT64,a11y:JSON,properties:JSON,received_at:INT64

# A daily transfer that loads only files new since its last run (google_cloud_storage shown;
# use data_source=amazon_s3 with data_path, access_key_id and secret_access_key for S3).
bq mk --transfer_config \
  --data_source=google_cloud_storage \
  --target_dataset=metrickle \
  --display_name="Metrickle events" \
  --schedule="every day 06:00" \
  --params='{"data_path_template":"gs://BUCKET/PREFIX/events/*","destination_table_name_template":"events_raw","file_format":"JSON","write_disposition":"APPEND"}'

Then query through a view that keeps one row per event:

CREATE OR REPLACE VIEW metrickle.events AS
SELECT * EXCEPT (rn) FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY app_id, id ORDER BY received_at DESC) AS rn
  FROM metrickle.events_raw)
WHERE rn = 1;

To query the files in place instead, create an external table over gs://BUCKET/PREFIX/events/* with format = 'NEWLINE_DELIMITED_JSON', compression = 'GZIP' and hive partitioning on gs://BUCKET/PREFIX/events/{app:STRING}/{dt:DATE}, so queries can skip days by dt.

Load into Snowflake

-- AWS S3: URL = 's3://BUCKET/PREFIX/'. R2, GCS, MinIO: an S3-compatible stage.
CREATE STAGE metrickle_stage
  URL = 's3compat://BUCKET/PREFIX/'
  ENDPOINT = 'ACCOUNT_ID.r2.cloudflarestorage.com'
  CREDENTIALS = (AWS_KEY_ID = '<read-only key>' AWS_SECRET_KEY = '<secret>')
  FILE_FORMAT = (TYPE = JSON COMPRESSION = GZIP);

CREATE TABLE IF NOT EXISTS metrickle_raw (v VARIANT, file STRING, loaded_at TIMESTAMP_NTZ DEFAULT CURRENT_TIMESTAMP());

-- Daily, after 05:00 UTC (a task or your scheduler). COPY skips files it has already loaded.
COPY INTO metrickle_raw (v, file)
  FROM (SELECT $1, METADATA$FILENAME FROM @metrickle_stage/events/)
  PATTERN = '.*[.]ndjson[.]gz';

CREATE OR REPLACE VIEW metrickle_events AS
SELECT v:app_id::STRING AS app_id, v:id::STRING AS id, TO_TIMESTAMP_LTZ(v:ts::NUMBER, 3) AS ts, v:type::STRING AS type,
       v:name::STRING AS name, v:visitor_id::STRING AS visitor_id, v:user_id::STRING AS user_id, v:session_id::STRING AS session_id,
       v:path::STRING AS path, v:country::STRING AS country, v:device_type::STRING AS device_type, v:value::FLOAT AS value,
       v:a11y AS a11y, v:properties AS properties, v:received_at::NUMBER AS received_at
       -- …and the other columns as needed
FROM metrickle_raw
QUALIFY ROW_NUMBER() OVER (PARTITION BY v:app_id, v:id ORDER BY v:received_at DESC) = 1;

For an external table, take the date from the path: dt DATE AS TO_DATE(SPLIT_PART(SPLIT_PART(METADATA$FILENAME, 'dt=', 2), '/', 1)).

The settings page shows both recipes filled in with your bucket and folder.

Erasure

When you erase a person in Metrickle, their events are removed straight away, so no later export or API read includes them. Metrickle can't change files it has already delivered, so it passes the erasure on:

  1. The next run writes every id the person was known by to erasures/dt={day}/erasures-{run}.ndjson.gz, in the hosted copy and your bucket. Each line is {"app_id", "ids": [...], "erased_at"}. Metrickle keeps the ids only until they're delivered.
  2. You apply them in your warehouse, then empty your erasures table, since it holds the ids too.
-- BigQuery (load erasures/* into metrickle.erasures: app_id STRING, ids ARRAY<STRING>, erased_at INT64)
DELETE FROM metrickle.events_raw e
WHERE EXISTS (SELECT 1 FROM metrickle.erasures x, UNNEST(x.ids) AS sid
              WHERE x.app_id = e.app_id AND (e.visitor_id = sid OR e.user_id = sid));
TRUNCATE TABLE metrickle.erasures;

-- Snowflake
CREATE TABLE IF NOT EXISTS metrickle_erasures (v VARIANT);
COPY INTO metrickle_erasures FROM (SELECT $1 FROM @metrickle_stage/erasures/) PATTERN = '.*[.]ndjson[.]gz';
DELETE FROM metrickle_raw r USING (
  SELECT e.v:app_id::STRING AS app_id, f.value::STRING AS sid
  FROM metrickle_erasures e, LATERAL FLATTEN(input => e.v:ids) f) x
WHERE r.v:app_id::STRING = x.app_id AND (r.v:visitor_id::STRING = x.sid OR r.v:user_id::STRING = x.sid);
TRUNCATE TABLE metrickle_erasures;

Hosted copies aren't rewritten. They're deleted with the rest of their run within 30 days.

Under GDPR, your organisation is the controller for data it has exported, so applying erasures in your own warehouse is your job. The erasures file is how Metrickle passes each request on. Removing the export also removes erasure notices that haven't been delivered, since there's nowhere left to send them.