1. Dataset Name

Source Analytics v2 dataset

2. Description

A candidate-and-application-level dataset that traces every application back to its source (application channel, referral status, sourcing recruiter) while also capturing candidates who have a profile in the talent network but have not yet applied. Each row represents one application, or, for profiles with no applications, one profile. This dataset powers source-channel reporting, recruiter and referrer attribution, referral program tracking, and talent network growth metrics on the Source Analytics v2 Dashboard.

3. Source Tables

• customer_views.profile (aliased pr) — base table; every profile appears at least once regardless of whether it has an application.
• customer_views.applications (aliased a) — LEFT JOINed on profile_id; provides application details, source, credited-to, hire outcome, and match score.
• customer_views.positions (aliased pos) — LEFT JOINed on position_id (from applications); provides business_unit, job_function, position_title, location_country, and the calibration flags used to derive is_calibrated.
• emp CTE (sourced from customer_views.employee, deduplicated by lowercased email) — joined twice: once to resolve application_credited_to to a display name, once to resolve profile_sourced_by to a display name.

4. Grain

One row per application. A profile with no applications still produces exactly one row (a profile-only row), with all application- and position-level fields NULL. A profile with multiple applications produces multiple rows, one per application.

5. Dashboards Supported

• Source Analytics v2 Dashboard

6. Metrics Supported

Key metrics shown; several additional widgets on this dashboard also draw from this dataset.

7. Known Exclusions / Filters Applied

• The emp CTE deduplicates customer_views.employee by lowercased email, taking MAX(first_name)/MAX(last_name) as a tie-breaker when duplicate employee records share the same email — this is a dedup mechanism, not a meaningful aggregation.
• application_credited_to is enriched to a 'First Last (email-prefix)' display format when a match is found in the employee table; it falls back to the raw credited-to value when no match exists.
• profile_sourced_by is enriched the same way from person_sourced_by.
• is_referral is derived from a simple case-insensitive check that application_source_category equals 'Referral'.
• is_calibrated requires at least 4 of the 5 position calibration flags (location, skills, titles, similar candidates, work experience) to be true.
• earliest_profile_creation_ts is computed via a window function (earliest profile_created_ts, partitioned by the coalesced canonical/profile ID) to resolve the true earliest creation date across merged or duplicate profile records.
• LEFT JOINs to applications, positions, and emp mean a profile with no matching application still appears as one row, with NULLs for all application- and position-level fields, rather than being dropped.

8. Schema