1. Dataset Name

survey_analytics

2. Description

A response-level dataset that captures every survey event submitted by candidates on the career site or hiring platform, including NPS classification, participant identification, and contextual details. Each row represents a single survey event (a rating, a comment, or a lifecycle action) for a given participant. This dataset powers the Survey Analytics dashboard, including the Candidate Experience Score (NPS), promoter/detractor breakdowns, average ratings, unique participant counts, and trends over time, by country, and by device.

3. Source Tables

• customer_views.survey_analytics (sole source table — no joins)

4. Grain

One row per survey event per participant. A single candidate who submits a rating and a comment will produce two rows (one per event type). NPS and participant metrics deduplicate on unique_id or vs_cookie as appropriate, so the multi-row-per-submission structure does not cause double-counting in aggregations.

5. Dashboards Supported

• Survey Analytics Dashboard

6. Metrics Supported

7. Known Exclusions / Filters Applied

• A hardcoded list of 5 position IDs is excluded from the dataset via a WHERE clause on position_id — rows where position_id IS NULL are retained (surveys not tied to a specific position).
• All NPS calculations (promoter %, detractor %, NPS score) filter to event = 'submit_survey' inside CASE expressions — other event types present in the table are excluded from these aggregations.
• Unique Survey Participants uses vs_cookie as its COUNT DISTINCT grain to count distinct users, not submissions. A candidate who completes two surveys is counted once.
• NPS and completion counts use unique_id as their COUNT DISTINCT grain to count distinct survey submissions. A single submission may produce multiple rows (rating + comment); deduplication on unique_id prevents double-counting.
• is_promoter = NULL rows (participants who viewed but did not rate) are excluded from the NPS denominator — only rows where is_promoter IS NOT NULL are counted as rated participants.
• The is_promoter classification uses configurable thresholds: by default, 1 (promoter) = rating equals max_rating, 0 (passive) = rating between promoter and detractor thresholds, -1 (detractor) = rating ≤ 3 on a 5-point scale. Customers can configure custom thresholds for 5-point or 10-point surveys.
• embedding_id, interface, and candidate_country are available as both drilldown dimensions and additional outer select filters, enabling dashboard-level cross-filtering by these attributes.
• Monthly trend widgets use DATE_TRUNC('month', t_create) as the time bucket and are ordered ascending by month.

8. Schema