1. Dataset Name

career_site_server_log_aggregated

2. Description

An event-level dataset that captures candidate activity on the Career Site (PCS — Public Career Site), aggregated to a daily grain per event-namespace-visitor combination. Built from server logs filtered to candidate-facing events only, then enriched with position metadata. Two CTEs are applied: the first aggregates event counts per day/visitor/event combination and filters out bot traffic, internal hostnames, and non-candidate roles; the second enriches each row with position attributes and derived columns including funnel stage labels, source group classifications, visitor type, login status, and AI recommendation flags. This dataset powers career site visitor analytics, conversion funnels, source attribution, and applicant trend metrics.

3. Source Tables

• customer_views.www_server_log (primary event source — filtered in raw_events CTE to candidate-facing events only)
• customer_views.positions (aliased pos — LEFT JOIN on position_id to enrich events with position metadata)

4. Grain

One row per (event_date, group_id, vs_cookie, viewer_profile_id, user_email, position_id, namespace, microsite, event, utm_source, utm_campaign, interface, country, user_locale, query, q_location_arg, request_endpoint, request_path, referrer) combination, with event_count as the aggregated count of occurrences for that combination within the day. Each row represents a unique candidate-event-context combination on a given date.

5. Dashboards Supported

• Career Site (PCS) Dashboard

6. Metrics Supported

7. Known Exclusions / Filters Applied

• Bot traffic excluded: interface NOT IN ('bot', 'mobile_bot', 'tablet_bot', 'pc_bot').
• Internal, staging, and demo hostnames excluded: hostname NOT IN ('stage.eightfold.ai', 'qa.eightfold.ai') AND NOT LIKE '%%dev%%', '%%eightfolddemo%%', '%%github-ci%%'.
• Only successful HTTP responses included: response_code < 400.
• Only candidate-facing user roles included: user_role IN ('candidate', NULL, 'anonymous').
• Only qualifying candidate events are included via a complex event/namespace filter — covering career site browsing (smartapply, pcsx_api, career_hub_candidate), apply flow (apply_form_v2, pcsx_app), signup (career_signup), login, chatbot, notifications, and skill proficiency edits from the profile-review referrer.
• Unique Applicants uses user_email (not vs_cookie) as its grain and additionally requires namespace IN ('career_hub_candidate', 'apply_form_v2') and user_email NOT IN ('', 'loggedout@none.com') — scoped to logged-in applicants only.
• Unique Applications Submitted uses CONCAT(vs_cookie, '-', position_id) as its grain, counting distinct visitor-position submission pairs.
• career_site_funnel_activities and career_site_funnel_unique_users are derived columns assigning each qualifying event to a funnel stage. NULL stage rows are excluded from funnel metrics via implicit filter.
• source_group is derived via a utm_source LOCATE waterfall: LinkedIn, Indeed, Google, Glassdoor, Naukri, Monster, Ziprecruiter, EightFold, direct traffic to Logged-in PCS, (Unknown), Others.
• pcs_login_status is derived: 'PCS Logged-in' when user_email IS NOT NULL AND user_email != 'loggedout@none.com'; 'PCS Logged-out' otherwise.
• Date range filtering maps to www_server_log.t_create via the override_filters date_range configuration, not directly to event_date.

8. Schema