Person Touches

The person_touches table/view tracks person touches in form responses, pings, back-imported paid-media leads (EDDY), GA4 (where possible), etc.

Create person_touches

Caution

This will need to expand and include GA4 data and other sources.

CREATE OR REPLACE TABLE person_touches AS

SELECT
    pi.person_id,
    pi.created_at AS touched_at,
    'ping' AS touch_type,
    'engagement' AS touch_category,
    pi.url,
    split_part(regexp_replace(lower(trim(pi.utm_campaign)), '_+$', ''), ',', 1) AS utm_campaign,
    split_part(regexp_replace(lower(trim(pi.utm_source)), '_+$', ''), ',', 1) AS utm_source,
    split_part(regexp_replace(lower(trim(pi.utm_medium)), '_+$', ''), ',', 1) AS utm_medium,
    NULL AS utm_content,
    NULL AS utm_term,
    'pings' AS source
FROM pings pi
JOIN persons p ON pi.person_id = p.id
WHERE pi.person_id IS NOT NULL

UNION ALL

SELECT
    fr.record_id AS person_id,
    fr.submitted_at AS touched_at,
    'form_response' AS touch_type,
    'engagement' AS touch_category,
    fr.url,
    split_part(regexp_replace(lower(trim(fr.utm_campaign)), '_+$', ''), ',', 1) AS utm_campaign,
    split_part(regexp_replace(lower(trim(fr.utm_source)), '_+$', ''), ',', 1) AS utm_source,
    split_part(regexp_replace(lower(trim(fr.utm_medium)), '_+$', ''), ',', 1) AS utm_medium,
    NULL AS utm_content,
    NULL AS utm_term,
    'form_responses' AS source
FROM form_responses fr
JOIN persons p ON fr.record_id = p.id
WHERE fr.record_id IS NOT NULL

UNION ALL

-- EDDY paid media leads: UTM data is back-imported into person-level Slate
-- fields rather than pings or form responses. Each UTM component is a
-- separate row in `fields`, so we pivot per person, then scrub as usual.
SELECT
    pu.record_id AS person_id,
    p.created_at AS touched_at,
    'person_utm' AS touch_type,
    'exposure' AS touch_category,
    NULL AS url,
    split_part(regexp_replace(lower(trim(pu.utm_campaign)), '_+$', ''), ',', 1) AS utm_campaign,
    split_part(regexp_replace(lower(trim(pu.utm_source)), '_+$', ''), ',', 1) AS utm_source,
    split_part(regexp_replace(lower(trim(pu.utm_medium)), '_+$', ''), ',', 1) AS utm_medium,
    split_part(regexp_replace(lower(trim(pu.utm_content)), '_+$', ''), ',', 1) AS utm_content,
    split_part(regexp_replace(lower(trim(pu.utm_term)), '_+$', ''), ',', 1) AS utm_term,
    'person_fields' AS source
FROM (
    SELECT
        f.record_id,
        MAX(CASE WHEN f.field = 'person_utm_source'   THEN COALESCE(lp.prompt_value, f.value) END) AS utm_source,
        MAX(CASE WHEN f.field = 'person_utm_medium'   THEN COALESCE(lp.prompt_value, f.value) END) AS utm_medium,
        MAX(CASE WHEN f.field = 'person_utm_campaign' THEN COALESCE(lp.prompt_value, f.value) END) AS utm_campaign,
        MAX(CASE WHEN f.field = 'person_utm_content'  THEN COALESCE(lp.prompt_value, f.value) END) AS utm_content,
        MAX(CASE WHEN f.field = 'person_utm_term'     THEN COALESCE(lp.prompt_value, f.value) END) AS utm_term
    FROM fields f
    LEFT JOIN lookup_prompts lp ON f.prompt = lp.id
    WHERE f.field IN (
        'person_utm_source',
        'person_utm_medium',
        'person_utm_campaign',
        'person_utm_content',
        'person_utm_term'
    )
    GROUP BY f.record_id
) pu
JOIN persons p ON pu.record_id = p.id
WHERE pu.utm_source IS NOT NULL;

Note

The EDDY branch is categorized as exposure, not engagement. These are back-imported paid-media records — a single lead capture, not active site engagement — so counting them as engagement would inflate touch-volume, velocity, and engaged-pipeline thresholds with a record that represents one ad interaction. As exposure, they still provide paid-media source attribution and correctly trigger the paid_exposed tag, while the engagement-based analyses (which filter touch_category = 'engagement') remain honest. Change this to 'engagement' only if you want EDDY leads to count toward touch thresholds.

Caution

The person-level fields (person_utm_*) are distinct from the form-response-level utm_* fields, which are already captured through the form_responses branch. Only the person_utm_* fields are pivoted here. Verify these field names against your own Slate instance — field naming is instance-specific.

Schema

If you’re doing a pro-level import, here’s the final schema. Basic and advanced imports will have fewer columns.

Column	Type
person_id	uuid
touch_type	varchar
touch_category	varchar
url	varchar
utm_campaign	varchar
utm_source	varchar
utm_medium	varchar
utm_content	varchar
utm_term	varchar
source	varchar
touched_at	timestamp

Column values

Column	Values produced by this query
touch_type	ping, form_response, person_utm
touch_category	engagement (pings, form responses), exposure (EDDY person_utm)
source	pings, form_responses, person_fields

Note

Analyses that measure active engagement — first-touch source group, touch volume, touch velocity, engaged pipeline health — filter on touch_category = 'engagement' and therefore exclude EDDY exposure touches by design. Channel and paid-media analyses (source mix, paid exposed) can include both categories.