Person Tags

The person_tags table follows an Entity-Attribute-Value (EAV) pattern. Rather than storing each data point about a person as its own column, every attribute is stored as a row with three core components: the person it belongs to (person_id), the name of the attribute (tag_type), and its value (tag_value).

This makes the table flexible by design — new tag types can be added without schema changes, and heterogeneous data sources (applications, form responses, person touches, derived logic) can be unioned into a single consistent shape.

The tradeoff is that querying across multiple attributes requires pivoting. This cost is paid once at the persons_dashboard layer, keeping the underlying tag data clean and extensible.

Create person_tags

CREATE OR REPLACE TABLE person_tags (
  person_id UUID,
  tag_type VARCHAR,
  tag_value VARCHAR,
  tag_date TIMESTAMP DEFAULT NOW(),
  source VARCHAR
);

Person Touches → First UTM Source

DELETE FROM person_tags
WHERE tag_type = 'first_touch_utm_source';

INSERT INTO person_tags
WITH first_touch AS (
  SELECT
    person_id,
    utm_campaign,
    utm_source,
    utm_medium,
    url,
    touched_at,
    touch_type,
    row_number() OVER (
      PARTITION BY person_id
      ORDER BY touched_at ASC
    ) AS rn
  FROM person_touches
)
SELECT
  person_id,
  'first_touch_utm_source' AS tag_type,
  utm_source AS tag_value,
  touched_at AS tag_date,
  'person_touches' AS source
FROM first_touch
WHERE rn = 1
  AND person_id IS NOT NULL
  AND utm_source IS NOT NULL;

Person Touches → Last UTM Source

DELETE FROM person_tags
WHERE tag_type = 'last_touch_utm_source';

INSERT INTO person_tags
WITH last_touch AS (
  SELECT
    person_id,
    utm_campaign,
    utm_source,
    utm_medium,
    url,
    touched_at,
    touch_type,
    row_number() OVER (
      PARTITION BY person_id
      ORDER BY touched_at DESC
    ) AS rn
  FROM person_touches
)
SELECT
  person_id,
  'last_touch_utm_source' AS tag_type,
  utm_source AS tag_value,
  touched_at AS tag_date,
  'person_touches' AS source
FROM last_touch
WHERE rn = 1
  AND person_id IS NOT NULL
  AND utm_source IS NOT NULL;

Status → Applicant

Tag which persons have completed an application.

Tip

This is the most bulletproof way to identify applicants in Slate. Some instances use a separate field or flag to mark submission, and standard values like applications.submitted_at and persons.status have proven unreliable — particularly where Common App data imports are involved.

Note

Don’t be surprised if this is different than numbers reported by Admission teams. They often report applications or applications by enrollment term instead of persons or persons by fiscal year.

DELETE FROM person_tags
WHERE tag_type = 'applied';

INSERT INTO person_tags
SELECT DISTINCT
  a.person_id,
  'applied' AS tag_type,
  'Yes' AS tag_value,
  CAST(
    COALESCE(a.submitted_at, a.created_at) AS DATE
  ) AS tag_date,
  'applications' AS source
FROM applications a
JOIN persons p
  ON a.person_id = p.id
WHERE a.person_id IS NOT NULL
  AND COALESCE(a.status, '') <> 'Awaiting Submission'
  AND (
    a.submitted_at IS NOT NULL
    OR a.status IN (
      'Decided',
      'Awaiting Decision',
      'Awaiting Confirmation'
    )
  );

Via Fields

Miami uses a separate field or flag to indicate when an application has been submitted. In this case, we can use the field value to tag an applicant.

Caution

This tag requires Slate fields and lookup_prompts tables. Add these to your DuckDB load before running this insert.

Undg

DELETE FROM person_tags
WHERE tag_type = 'applied';

INSERT INTO person_tags
SELECT
  a.person_id,
  'applied' AS tag_type,
  'Yes' AS tag_value,
  CAST(a.submitted_at AS DATE) AS tag_date,
  'applications.submitted_at' AS source
FROM applications a
JOIN persons p
  ON a.person_id = p.id
WHERE a.person_id IS NOT NULL
  AND a.submitted_at IS NOT NULL
  AND p.slate_instance = 'undg';

Grad

DELETE FROM person_tags
WHERE tag_type = 'applied';

INSERT INTO person_tags
SELECT
  a.person_id,
  'applied' AS tag_type,
  'Yes' AS tag_value,
  CAST(
    COALESCE(
      try_strptime(COALESCE(lp.prompt_value, f.value), '%m/%d/%Y %H:%M'),
      try_strptime(COALESCE(lp.prompt_value, f.value), '%m/%d/%Y')
    ) AS DATE
  ) AS tag_date,
  'field.reportable_date' AS source
FROM fields f
LEFT JOIN lookup_prompts lp
  ON f.prompt = lp.id
LEFT JOIN applications a
  ON f.record_id = a.id
JOIN persons p
  ON a.person_id = p.id
WHERE f.field = 'reportable_date'
  AND a.person_id IS NOT NULL
  AND COALESCE(lp.prompt_value, f.value) IS NOT NULL
  AND p.slate_instance = 'grad';

Enrolled

Caution

This tag requires Slate fields and lookup_prompts tables. Add these to your DuckDB load before running this insert.

Undg

DELETE FROM person_tags
WHERE tag_type = 'enrolled';

INSERT INTO person_tags
SELECT
  a.person_id,
  'enrolled' AS tag_type,
  'Yes' AS tag_value,
  a.created_at AS tag_date,
  'field.enrolled_after_confirmation' AS source
FROM fields f
LEFT JOIN lookup_prompts lp
  ON f.prompt = lp.id
LEFT JOIN applications a
  ON f.record_id = a.id
JOIN persons p
  ON a.person_id = p.id
WHERE f.field = 'enrolled_after_confirmation'
  AND p.slate_instance = 'grad'
  AND a.person_id IS NOT NULL
  AND COALESCE(lp.prompt_value, f.value) IS NOT NULL;

Grad

DELETE FROM person_tags
WHERE tag_type = 'enrolled';

INSERT INTO person_tags
SELECT
  a.person_id,
  'enrolled' AS tag_type,
  'Yes' AS tag_value,
  a.created_at AS tag_date,
  'field.enrolled_after_confirmation' AS source
FROM fields f
LEFT JOIN lookup_prompts lp
  ON f.prompt = lp.id
LEFT JOIN applications a
  ON f.record_id = a.id
JOIN persons p
  ON a.person_id = p.id
WHERE f.field = 'enrolled_after_confirmation'
  AND p.slate_instance = 'grad'
  AND a.person_id IS NOT NULL
  AND COALESCE(lp.prompt_value, f.value) IS NOT NULL;

Campus

DELETE FROM person_tags
WHERE tag_type = 'campus';

INSERT INTO person_tags
WITH campus_candidates AS (

  -- Priority 1: most recent application campus
  SELECT
    a.person_id,
    COALESCE(lp.prompt_value, f.value) AS campus,
    a.created_at AS tag_date,
    'field.app_campus_location' AS source,
    1 AS priority
  FROM fields f
  JOIN applications a
    ON f.record_id = a.id
  JOIN persons p
    ON a.person_id = p.id
  LEFT JOIN lookup_prompts lp
    ON f.prompt = lp.id
  WHERE f.field = 'app_campus_location'
    AND p.slate_instance = 'undg'
    AND a.person_id IS NOT NULL
    AND COALESCE(lp.prompt_value, f.value) IS NOT NULL

  UNION ALL

  -- Priority 2: person preferred campus
  SELECT
    p.id AS person_id,
    COALESCE(lp.prompt_value, f.value) AS campus,
    p.created_at AS tag_date,
    'field.campus_preferred' AS source,
    2 AS priority
  FROM fields f
  JOIN persons p
    ON f.record_id = p.id
  LEFT JOIN lookup_prompts lp
    ON f.prompt = lp.id
  WHERE f.field = 'campus_preferred'
    AND p.slate_instance = 'undg'
    AND p.id IS NOT NULL
    AND COALESCE(lp.prompt_value, f.value) IS NOT NULL
),

ranked AS (
  SELECT
    *,
    row_number() OVER (
      PARTITION BY person_id
      ORDER BY priority ASC, tag_date DESC
    ) AS rn
  FROM campus_candidates
)

SELECT
  person_id,
  'campus' AS tag_type,
  campus AS tag_value,
  tag_date,
  source
FROM ranked
WHERE rn = 1;

Academic Interest (at time of inquiry)

Pluck the person field labeling the academic program in which a student/inquiry showed interest. Because this is an person field, this query joins on the persons table;

Undg

DELETE FROM person_tags
WHERE tag_type = 'intended_program';

INSERT INTO person_tags
SELECT
  p.id AS person_id,
  'intended_program' AS tag_type,
  COALESCE(lp.prompt_value, f.value) AS tag_value,
  NOW() AS tag_date,
  'field.primary_academic_interest' AS source
FROM fields f
LEFT JOIN lookup_prompts lp
  ON f.prompt = lp.id
LEFT JOIN persons p
  ON f.record_id = p.id
WHERE f.field = 'primary_academic_interest'
  AND p.slate_instance = 'undg'
  AND p.id IS NOT NULL
  AND COALESCE(lp.prompt_value, f.value) IS NOT NULL;

Grad

DELETE FROM person_tags
WHERE tag_type = 'intended_program';

INSERT INTO person_tags
SELECT
  p.id AS person_id,
  'intended_program' AS tag_type,
  COALESCE(lp.prompt_value, f.value) AS tag_value,
  NOW() AS tag_date,
  'field.most_recent_pgrm' AS source
FROM fields f
LEFT JOIN lookup_prompts lp
  ON f.prompt = lp.id
LEFT JOIN persons p
  ON f.record_id = p.id
WHERE f.field = 'most_recent_pgrm'
  AND p.slate_instance = 'grad'
  AND p.id IS NOT NULL
  AND COALESCE(lp.prompt_value, f.value) IS NOT NULL;

Academic Term (at time of inquiry)

Pluck the person field labeling the academic term in which a student showed interest. Because this is an person field, this query joins on the persons table;

In undergraduate Slate, the field is term. In graduate Slate, the field is intended_term.

Undg

DELETE FROM person_tags
WHERE tag_type = 'intended_term';

INSERT INTO person_tags
SELECT
  p.id AS person_id,
  'intended_term' AS tag_type,
  COALESCE(lp.prompt_value, f.value) AS tag_value,
  NOW() AS tag_date,
  'field.term' AS source
FROM fields f
LEFT JOIN lookup_prompts lp
  ON f.prompt = lp.id
LEFT JOIN persons p
  ON f.record_id = p.id
WHERE f.field = 'term'
  AND p.slate_instance = 'undg'
  AND p.id IS NOT NULL
  AND COALESCE(lp.prompt_value, f.value) IS NOT NULL;

Grad

DELETE FROM person_tags
WHERE tag_type = 'intended_term';

INSERT INTO person_tags
SELECT
  p.id AS person_id,
  'intended_term' AS tag_type,
  COALESCE(lp.prompt_value, f.value) AS tag_value,
  NOW() AS tag_date,
  'field.intended_term' AS source
FROM fields f
LEFT JOIN lookup_prompts lp
  ON f.prompt = lp.id
LEFT JOIN persons p
  ON f.record_id = p.id
WHERE f.field = 'intended_term'
  AND p.slate_instance = 'grad'
  AND p.id IS NOT NULL
  AND COALESCE(lp.prompt_value, f.value) IS NOT NULL;

Academic Division

Pluck the person field labeling the person’s division/college. Because this is an person field, this query joins on the persons table;

In undergraduate Slate, the person field is academic_division_interest (multiple values) and the application field is app_major_div. In graduate Slate, the person field is rfi_division and the application field is division.

There is a special case here, however, where the undergraduate application division is actually stored in the category column in lookup_prompts for the app_real_major field.

Note

The coalesced value from application data is pulled from the most recent application.

For undergraduate Slate:

DELETE FROM person_tags
WHERE tag_type = 'academic_division';

-- Graduate
INSERT INTO person_tags

WITH grad_app_division AS (
  SELECT
    a.person_id,
    COALESCE(lp.prompt_value, f.value) AS division,
    a.created_at AS tag_date,
    row_number() OVER (
      PARTITION BY a.person_id
      ORDER BY a.created_at DESC, a.id DESC
    ) AS rn
  FROM fields f
  LEFT JOIN lookup_prompts lp
    ON f.value = lp.id
  LEFT JOIN applications a
    ON f.record_id = a.id
  JOIN persons p
    ON a.person_id = p.id
  WHERE f.field = 'division'
    AND p.slate_instance = 'grad'
    AND a.person_id IS NOT NULL
    AND COALESCE(lp.prompt_value, f.value) IS NOT NULL
),

grad_person_division AS (
  SELECT
    p.id AS person_id,
    COALESCE(lp.prompt_value, f.value) AS division,
    p.created_at AS tag_date
  FROM fields f
  LEFT JOIN lookup_prompts lp
    ON f.value = lp.id
  LEFT JOIN persons p
    ON f.record_id = p.id
  WHERE f.field = 'rfi_division'
    AND p.slate_instance = 'grad'
    AND p.id IS NOT NULL
    AND COALESCE(lp.prompt_value, f.value) IS NOT NULL
)

SELECT
  COALESCE(ad.person_id, pd.person_id) AS person_id,
  'academic_division' AS tag_type,
  COALESCE(ad.division, pd.division) AS tag_value,
  COALESCE(ad.tag_date, pd.tag_date) AS tag_date,
  CASE
    WHEN ad.division IS NOT NULL THEN 'field.division'
    ELSE 'field.rfi_division'
  END AS source
FROM grad_person_division pd
FULL OUTER JOIN grad_app_division ad
  ON pd.person_id = ad.person_id
 AND ad.rn = 1
WHERE COALESCE(ad.division, pd.division) IS NOT NULL;

-- Undergraduate
INSERT INTO person_tags

WITH undg_app_division AS (
  SELECT
    a.person_id,
    lp.category AS division,
    a.created_at AS tag_date,
    row_number() OVER (
      PARTITION BY a.person_id
      ORDER BY a.created_at DESC, a.id DESC
    ) AS rn
  FROM fields f
  LEFT JOIN lookup_prompts lp
    ON f.prompt = lp.id
  LEFT JOIN applications a
    ON f.record_id = a.id
  JOIN persons p
    ON a.person_id = p.id
  WHERE f.field = 'app_real_major'
    AND p.slate_instance = 'undg'
    AND a.person_id IS NOT NULL
    AND lp.category IS NOT NULL
),

undg_person_division AS (
  SELECT
    p.id AS person_id,
    COALESCE(lp.prompt_value, f.value) AS division,
    p.created_at AS tag_date
  FROM fields f
  LEFT JOIN lookup_prompts lp
    ON f.prompt = lp.id
  LEFT JOIN persons p
    ON f.record_id = p.id
  WHERE f.field = 'academic_division_interest'
    AND p.slate_instance = 'undg'
    AND p.id IS NOT NULL
    AND COALESCE(lp.prompt_value, f.value) IS NOT NULL
)

SELECT
  COALESCE(ad.person_id, pd.person_id) AS person_id,
  'academic_division' AS tag_type,
  COALESCE(ad.division, pd.division) AS tag_value,
  COALESCE(ad.tag_date, pd.tag_date) AS tag_date,
  CASE
    WHEN ad.division IS NOT NULL THEN 'field.app_major_div'
    ELSE 'field.academic_division_interest'
  END AS source
FROM undg_person_division pd
FULL OUTER JOIN undg_app_division ad
  ON pd.person_id = ad.person_id
 AND ad.rn = 1
WHERE COALESCE(ad.division, pd.division) IS NOT NULL;

Application Program

Pluck the application field labeling the program to which a student applied. Because this is an application field, this query joins on the applications table;

In undergraduate Slate, the field is app_real_major. In graduate Slate, the field is program_code.

Note

This data point is pulled from the most recent application.

DELETE FROM person_tags
WHERE tag_type = 'application_program';

-- Graduate
INSERT INTO person_tags
WITH ranked AS (
  SELECT
    a.person_id,
    COALESCE(lp.prompt_value, f.value) AS program,
    a.created_at AS tag_date,
    row_number() OVER (
      PARTITION BY a.person_id
      ORDER BY a.created_at DESC, a.id DESC
    ) AS rn
  FROM fields f
  LEFT JOIN lookup_prompts lp
    ON f.prompt = lp.id
  LEFT JOIN applications a
    ON f.record_id = a.id
  JOIN persons p
    ON a.person_id = p.id
  WHERE f.field = 'program_code'
    AND p.slate_instance = 'grad'
    AND a.person_id IS NOT NULL
    AND COALESCE(lp.prompt_value, f.value) IS NOT NULL
)
SELECT
  person_id,
  'application_program' AS tag_type,
  program AS tag_value,
  tag_date,
  'field.program_code' AS source
FROM ranked
WHERE rn = 1;

-- Undergraduate
INSERT INTO person_tags
WITH ranked AS (
  SELECT
    a.person_id,
    COALESCE(lp.prompt_value, f.value) AS program,
    a.created_at AS tag_date,
    row_number() OVER (
      PARTITION BY a.person_id
      ORDER BY a.created_at DESC, a.id DESC
    ) AS rn
  FROM fields f
  LEFT JOIN lookup_prompts lp
    ON f.prompt = lp.id
  LEFT JOIN applications a
    ON f.record_id = a.id
  JOIN persons p
    ON a.person_id = p.id
  WHERE f.field = 'app_real_major'
    AND p.slate_instance = 'undg'
    AND a.person_id IS NOT NULL
    AND COALESCE(lp.prompt_value, f.value) IS NOT NULL
)
SELECT
  person_id,
  'application_program' AS tag_type,
  program AS tag_value,
  tag_date,
  'field.app_real_major' AS source
FROM ranked
WHERE rn = 1;

Application Term

Pluck the application field labeling the term to which a student applied. Because this is an application field, this query joins on the applications table;

In undergraduate Slate, the field is app_entry. In graduate Slate, the field is term_applied.

Note

This data point is pulled from the most recent application.

DELETE FROM person_tags
WHERE tag_type = 'application_term';

-- Graduate
INSERT INTO person_tags
WITH ranked AS (
  SELECT
    a.person_id,
    COALESCE(lp.prompt_value, f.value) AS application_term,
    a.created_at AS tag_date,
    row_number() OVER (
      PARTITION BY a.person_id
      ORDER BY a.created_at DESC, a.id DESC
    ) AS rn
  FROM fields f
  LEFT JOIN lookup_prompts lp
    ON f.prompt = lp.id
  LEFT JOIN applications a
    ON f.record_id = a.id
  JOIN persons p
    ON a.person_id = p.id
  WHERE f.field = 'term_applied'
    AND p.slate_instance = 'grad'
    AND a.person_id IS NOT NULL
    AND COALESCE(lp.prompt_value, f.value) IS NOT NULL
)

SELECT
  person_id,
  'application_term' AS tag_type,
  application_term AS tag_value,
  tag_date,
  'field.term_applied' AS source
FROM ranked
WHERE rn = 1;

-- Undergraduate
INSERT INTO person_tags
WITH ranked AS (
  SELECT
    a.person_id,
    COALESCE(lp.prompt_value, f.value) AS application_term,
    a.created_at AS tag_date,
    row_number() OVER (
      PARTITION BY a.person_id
      ORDER BY a.created_at DESC, a.id DESC
    ) AS rn
  FROM fields f
  LEFT JOIN lookup_prompts lp
    ON f.prompt = lp.id
  LEFT JOIN applications a
    ON f.record_id = a.id
  JOIN persons p
    ON a.person_id = p.id
  WHERE f.field = 'app_entry'
    AND p.slate_instance = 'undg'
    AND a.person_id IS NOT NULL
    AND COALESCE(lp.prompt_value, f.value) IS NOT NULL
)

SELECT
  person_id,
  'application_term' AS tag_type,
  application_term AS tag_value,
  tag_date,
  'field.app_entry' AS source
FROM ranked
WHERE rn = 1;

Application Status

Pluck the application field labeling the status of an application (cancelled or confirmed in Grad Slate). Because this is an application field, this query joins on the applications table;

In undergraduate Slate, the field is application_status. In graduate Slate, the field is app_status.

Note

This data point is pulled from the most recent application.

DELETE FROM person_tags
WHERE tag_type = 'application_status';

-- Graduate
INSERT INTO person_tags
WITH ranked AS (
  SELECT
    a.person_id,
    COALESCE(lp.prompt_value, f.value) AS application_status,
    a.created_at AS tag_date,
    row_number() OVER (
      PARTITION BY a.person_id
      ORDER BY a.created_at DESC, a.id DESC
    ) AS rn
  FROM fields f
  LEFT JOIN lookup_prompts lp
    ON f.prompt = lp.id
  LEFT JOIN applications a
    ON f.record_id = a.id
  JOIN persons p
    ON a.person_id = p.id
  WHERE f.field = 'app_status'
    AND p.slate_instance = 'grad'
    AND a.person_id IS NOT NULL
    AND COALESCE(lp.prompt_value, f.value) IS NOT NULL
)

SELECT
  person_id,
  'application_status' AS tag_type,
  application_status AS tag_value,
  tag_date,
  'field.app_status' AS source
FROM ranked
WHERE rn = 1;

-- Undergraduate
INSERT INTO person_tags
WITH ranked AS (
  SELECT
    a.person_id,
    COALESCE(lp.prompt_value, f.value) AS application_status,
    a.created_at AS tag_date,
    row_number() OVER (
      PARTITION BY a.person_id
      ORDER BY a.created_at DESC, a.id DESC
    ) AS rn
  FROM fields f
  LEFT JOIN lookup_prompts lp
    ON f.prompt = lp.id
  LEFT JOIN applications a
    ON f.record_id = a.id
  JOIN persons p
    ON a.person_id = p.id
  WHERE f.field = 'application_status'
    AND p.slate_instance = 'undg'
    AND a.person_id IS NOT NULL
    AND COALESCE(lp.prompt_value, f.value) IS NOT NULL
)

SELECT
  person_id,
  'application_status' AS tag_type,
  application_status AS tag_value,
  tag_date,
  'field.application_status' AS source
FROM ranked
WHERE rn = 1;

Application Type

Pluck the application field labeling an application’s type. Because this is an application field, this query joins on the applications table;

Note

This data point is pulled from the most recent application.

DELETE FROM person_tags
WHERE tag_type = 'application_type';

INSERT INTO person_tags
WITH ranked AS (
  SELECT
    a.person_id,
    COALESCE(lp.prompt_value, f.value) AS application_type,
    a.created_at AS tag_date,
    row_number() OVER (
      PARTITION BY a.person_id
      ORDER BY a.created_at DESC, a.id DESC
    ) AS rn
  FROM fields f
  LEFT JOIN lookup_prompts lp
    ON f.prompt = lp.id
  LEFT JOIN applications a
    ON f.record_id = a.id
  WHERE f.field = 'app_type'
    AND a.person_id IS NOT NULL
    AND COALESCE(lp.prompt_value, f.value) IS NOT NULL
)
SELECT
  person_id,
  'application_type' AS tag_type,
  application_type AS tag_value,
  tag_date,
  'field.app_type' AS source
FROM ranked
WHERE rn = 1;

Revenue Group

Tag a student into a group segment: ‘undergraduate_resident’, ‘undergraduate_nonresident’, ‘graduate’, ‘regionals’.

DELETE FROM person_tags
WHERE tag_type = 'revenue_group'
  AND tag_value = 'undergraduate_resident';

INSERT INTO person_tags
SELECT DISTINCT
  p.id AS person_id,
  'revenue_group' AS tag_type,
  'undergraduate_resident' AS tag_value,
  NOW() AS tag_date,
  'derived.undergraduate_resident_address' AS source
FROM persons p
JOIN addresses a
  ON a.record_id = p.id
  WHERE p.slate_instance = 'undg'
  AND lower(trim(a.region)) = 'oh';

Tag all undergraduate non-residents:

DELETE FROM person_tags
WHERE tag_type = 'revenue_group'
  AND tag_value = 'undergraduate_nonresident';

INSERT INTO person_tags
SELECT DISTINCT
  p.id AS person_id,
  'revenue_group' AS tag_type,
  'undergraduate_nonresident' AS tag_value,
  NOW() AS tag_date,
  'derived.undergraduate_nonresident_address' AS source
FROM persons p
JOIN addresses a
  ON a.record_id = p.id
WHERE p.slate_instance = 'undg'
  AND p.id IS NOT NULL
  AND (
    a.region IS NULL
    OR lower(trim(a.region)) NOT IN ('oh', 'ohio')
  );

Tag all graduate students:

DELETE FROM person_tags
WHERE tag_type = 'revenue_group'
  AND tag_value = 'graduate';

INSERT INTO person_tags
SELECT DISTINCT
  id AS person_id,
  'revenue_group' AS tag_type,
  'graduate' AS tag_value,
  NOW() AS tag_date,
  'derived.graduate_slate_instance' AS source
FROM persons
WHERE slate_instance = 'grad'
  AND id IS NOT NULL;

Tag all regionals students:

Note

This depends on the academic_division person tag and the campus person tag, so create those first.

DELETE FROM person_tags
WHERE tag_type = 'revenue_group'
  AND tag_value = 'regionals';

INSERT INTO person_tags
SELECT DISTINCT
  person_id,
  'revenue_group' AS tag_type,
  'regionals' AS tag_value,
  tag_date,
  'derived.regionals_academic_division_or_campus' AS source
FROM person_tags
WHERE person_id IS NOT NULL
  AND (
    (
      tag_type = 'academic_division'
      AND tag_value ILIKE '%(Regionals)%'
    )
    OR
    (
      tag_type = 'campus'
      AND lower(trim(tag_value)) IN ('hamilton', 'middletown')
    )
  );

GA ID

This is a custom pass-through ID tracked into Google Analytics that’s later captured on form responses. Join this to GA4 data later as a way of capturing more attribution data. If you want a more automated way of tracking this, decorate the Slate form embed code URLs or hijack the utm_term field.

DELETE FROM person_tags
WHERE tag_type = 'ga_id';

INSERT INTO person_tags
SELECT
  p.id AS person_id,
  'ga_id' AS tag_type,
  COALESCE(lp.prompt_value, f.value) AS tag_value,
  NOW() AS tag_date,
  'field.ga_id' AS source
FROM fields f
LEFT JOIN lookup_prompts lp
  ON f.prompt = lp.id
LEFT JOIN persons p
  ON f.record_id = p.id
WHERE f.field = 'ga_id'
  AND p.id IS NOT NULL
  AND COALESCE(lp.prompt_value, f.value) IS NOT NULL;

Paid Exposed

This gives you a durable feature in person_tags for whether a person is exposed to paid digital media.

DELETE FROM person_tags
WHERE tag_type = 'paid_exposed';

INSERT INTO person_tags
SELECT DISTINCT
  person_id,
  'paid_exposed' AS tag_type,
  'true' AS tag_value,
  MIN(touched_at) AS tag_date,
  'person_touches' AS source
FROM person_touches
WHERE utm_medium IN (
  'cpc',
  'ppc',
  'paid-social',
  'display',
  'mediately',
  'lightbox',
  'discovery'
)
GROUP BY person_id;

EDDY (Vendor)

Tags persons who had engagement tracked through EDDY, a digital media vendor that back-imports touch data directly into Slate. Because EDDY touches arrive as Slate source records or field values rather than web pings or form responses, they do not appear in person_touches and would otherwise be excluded from touch-based analyses.

Caution

This tag requires Slate source tables not present in the base data model: source_records, sources, source_formats, fields, and lookup_prompts. Add these to your DuckDB load before running this insert.

Three detection signals are unioned together — a person is tagged if any one matches:

• A form response with a utm_campaign matching EMS_*
• A Slate source record linked to a source format containing “Microsite”
• A Slate field value matching EMS_*

SELECT count(*) FROM person_tags WHERE tag_type = 'eddy';
DELETE FROM person_tags WHERE tag_type = 'eddy';

INSERT INTO person_tags
WITH eddy_candidates AS (

  -- Form responses with EMS_ campaign
  SELECT
    record_id AS person_id,
    submitted_at AS tag_date,
    'form_response.utm_campaign' AS source
  FROM form_responses
  WHERE record_id IS NOT NULL
    AND utm_campaign ILIKE 'EMS_%'

  UNION ALL

  -- Source format contains Microsite
  SELECT
    p.id AS person_id,
    s.created_at AS tag_date,
    'source_format.microsite' AS source
  FROM source_records sr
  JOIN persons p
    ON sr.record_id = p.id
  JOIN sources s
    ON sr.source_id = s.id
  JOIN source_formats sf
    ON s.format_id = sf.id
  WHERE sf.name ILIKE '%Microsite%'
    AND p.id IS NOT NULL

  UNION ALL

  -- Person field contains EMS_
  SELECT
    p.id AS person_id,
    p.created_at AS tag_date,
    COALESCE(lp.prompt_value, f.value) AS source
  FROM fields f
  LEFT JOIN lookup_prompts lp
    ON f.prompt = lp.id
  LEFT JOIN persons p
    ON f.record_id = p.id
  WHERE p.id IS NOT NULL
    AND COALESCE(lp.prompt_value, f.value) ILIKE 'EMS_%'
    AND COALESCE(lp.prompt_value, f.value) IS NOT NULL
),

ranked AS (
  SELECT
    person_id,
    tag_date,
    source,
    row_number() OVER (
      PARTITION BY person_id
      ORDER BY tag_date ASC
    ) AS rn
  FROM eddy_candidates
)

SELECT
  person_id,
  'eddy' AS tag_type,
  'Yes' AS tag_value,
  tag_date,
  source
FROM ranked
WHERE rn = 1;

Note

After the EDDY tag is populated, update the engaged-pipeline-health query to include EDDY-tagged persons in the engaged cohort alongside the 36+ touch threshold.

In-Platform Lead (Carnegie)

Tags persons who entered the funnel through Carnegie’s Meta in-platform lead ads — form completions that happen entirely within Facebook or Instagram, never touching the institution’s website. These leads are imported into Slate via Zapier and have a near-zero application rate (~0.06%).

Caution

Requires the same Slate source tables as the EDDY tag: source_records, sources, and source_formats.

DELETE FROM person_tags
WHERE tag_type = 'in_platform';

INSERT INTO person_tags
SELECT DISTINCT
  p.id AS person_id,
  'in_platform' AS tag_type,
  'Yes' AS tag_value,
  s.created_at AS tag_date,
  'source_format.zapier' AS source
FROM source_records sr
JOIN persons p
  ON sr.record_id = p.id
JOIN sources s
  ON sr.source_id = s.id
JOIN source_formats sf
  ON s.format_id = sf.id
WHERE sf.name LIKE '%Zapier%'
  AND p.id IS NOT NULL;

Fiscal Year

Fiscal year is derived from persons.created_at using a July 1 cutoff — the standard higher ed fiscal year. A person created on or after July 1, 2025 belongs to FY2026.

Note

If your Slate instance stores an entry term on the application, that’s a more meaningful source — it reflects when the student plans to enroll rather than when they entered the funnel. Field names vary, so this query uses created_at as a universal fallback.

DELETE FROM person_tags
WHERE tag_type = 'fiscal_year';

INSERT INTO person_tags
SELECT
  p.id AS person_id,
  'fiscal_year' AS tag_type,
  CAST(
    CASE
      WHEN EXTRACT(MONTH FROM p.created_at) >= 7 THEN EXTRACT(YEAR FROM p.created_at) + 1
      ELSE EXTRACT(YEAR FROM p.created_at)
    END AS VARCHAR
  ) AS tag_value,
  p.created_at AS tag_date,
  'persons' AS source
FROM persons p
WHERE p.id IS NOT NULL;