Person IDs

The person_ids table extracts the ft_id cookie value from form_responses.url and stores it alongside the person_id. Because a person can submit multiple forms — each with a different ft_id — this is a one-to-many table. The ft_id is the bridge between a Slate person and their GA4 session data.

Create person_ids

CREATE OR REPLACE TABLE person_ids (
  person_id UUID,
  id_type VARCHAR,
  id_value VARCHAR,
);

Extract ft_id from form_response URLs

DELETE FROM person_ids
WHERE id_type = 'ft_id';

INSERT INTO person_ids
SELECT DISTINCT
  fr.record_id AS person_id,
  'ft_id' AS id_type,
  regexp_extract(fr.url, '[?&]ft_id=([^&]+)', 1) AS id_value
FROM form_responses fr
JOIN persons p
  ON fr.record_id = p.id
WHERE fr.record_id IS NOT NULL
  AND regexp_extract(fr.url, '[?&]ft_id=([^&]+)', 1) IS NOT NULL
  AND regexp_extract(fr.url, '[?&]ft_id=([^&]+)', 1) <> '';

Note

DISTINCT deduplicates cases where the same person submitted the same form more than once with the same ft_id. Each unique (person_id, ft_id) pair is stored once.

Schema

Column	Type
person_id	uuid
id_type	varchar
id_value	varchar