Introduction

This article shows how to make a report that shows how many past employees have applied for open jobs. The process involves using pre-built reporting tools and executing custom SQL queries to extract the necessary data. By following the steps outlined in this article, you can efficiently extract and analyze the desired data to make informed decisions about your recruitment strategies.

Tools and resources required

• Candidate Report: A feature for setting up and making reports with fields like candidate names, ratings, and interviewer information.
• Talent Intelligence Dashboard: Provides insights into existing roles, their prevalence, and other metrics.
• Data Hub: A platform for executing custom SQL queries to fetch specific data sets.

Instructions

1. Utilize pre-built reporting tools

   • Access the Candidate Report
     • Navigate to the Candidate Report feature.
     • Configure the report to include relevant fields such as candidate names and application statuses.
   • Refer to the Talent Intelligence Dashboard
     • Use the dashboard to understand the current open positions and their requirements.

2. Execute a custom query for detailed analysis

   • Prepare the Environment
     • Ensure you have access to the Data Hub and the necessary permissions to execute SQL queries.
   • Identify relevant tables
     • Focus on the following tables:
     • profile: Contains candidate profiles.
     • applications: Includes application details for each candidate.
   • Build the query
     • Use the following template to create your custom query. This query joins the profile and applications tables to find out who's currently working and who's applying for open jobs.

    WITH work_experience AS (
      SELECT p.profile_id, p.first_name, p.last_name, p.primary_email, e.work::varchar AS work_company
      FROM accountname_profile p, p.experiences_json e
      WHERE p.experiences_json IS NOT NULL
    )
    SELECT t1.profile_id, application_id, application_ts, t1.status AS application_status, t2.first_name, t2.last_name, t2.primary_email, t2.work_company, t1.position_id, t3.status AS position_status, t3.title AS position_title
    FROM accountname_applications t1
    JOIN (SELECT * FROM work_experience WHERE work_company = 'Company Name') t2 ON t1.profile_id = t2.profile_id
    JOIN accountname_positions t3 ON t1.position_id = t3.position_id
    ORDER BY 1
   

• Run the Query
  • Run the query in the Insights > Data Hub to fetch the report.

Troubleshooting

• Common challenges

  • Filtering Results for Open Positions: Ensure you are using the correct position status in your query to filter for open roles.
  • Identifying Ex-Employees: Use the work_company field to filter for former employees who have previously worked at your company.

• Solutions

  • Filtering Results for Open Positions: Use the position_status field in your query to filter for open positions.
  • Identifying Ex-Employees: Ensure the work_company field in the work_experience table is correctly set to your company name.