採用・人材獲得データテンプレート

/*
iCIMS Reporting is configured through a user interface, not a direct script. 
This represents the configuration of the columns and the logic required to map statuses.
The user must configure these settings in the 'Filters' and 'Output' tabs of the iCIMS search creation tool.
*/

// SEARCH TYPE
Recruiting Workflow Search

// FILTERS
// Apply a date range to the history updated date to control the export size.
{
  "field": "Recruiting Workflow Profile -> Workflow -> Updated Date (History)",
  "operator": "is between",
  "values": ["[Start Date]", "[End Date]"]
}

// OUTPUT COLUMNS AND TRANSFORMATION LOGIC
// The following defines the columns to add in the 'Output' tab and the required post-export mapping.

SELECT
  -- Required Attributes
  w.SystemId                                    AS JobApplicationId,      -- Path: Recruiting Workflow Profile -> System ID
  w.UpdatedDate_History                         AS ActivityStartTime,     -- Path: Recruiting Workflow Profile -> Workflow -> Updated Date (History)

  -- ActivityName must be generated by mapping the raw status history in a post-processing step.
  CASE w.Status_History
    WHEN '[Your 'Application Received' Status]'       THEN 'Application Received'
    WHEN '[Your 'Application Screened' Status]'      THEN 'Application Screened'
    WHEN '[Your 'Phone Screened' Status]'         THEN 'Candidate Phone Screened'
    WHEN '[Your 'Submitted to HM' Status]'          THEN 'Candidate Submitted To Hiring Mgr'
    WHEN '[Your '1st Interview Scheduled' Status]'    THEN 'First Interview Scheduled'
    WHEN '[Your '1st Interview Conducted' Status]'   THEN 'First Interview Conducted'
    WHEN '[Your 'Feedback Submitted' Status]'       THEN 'Feedback Submitted'
    WHEN '[Your '2nd Interview Scheduled' Status]'    THEN 'Second Interview Scheduled'
    WHEN '[Your 'Offer Extended' Status]'           THEN 'Offer Extended'
    WHEN '[Your 'Offer Accepted' Status]'           THEN 'Offer Accepted'
    WHEN '[Your 'Background Check' Status]'         THEN 'Background Check Initiated'
    WHEN '[Your 'Hired' Status]'                    THEN 'Candidate Hired'
    WHEN '[Your 'Offer Rejected' Status]'           THEN 'Offer Rejected'
    WHEN '[Your 'Rejected by Company' Status]'      THEN 'Application Rejected By Company'
    WHEN '[Your 'Candidate Withdrew' Status]'       THEN 'Candidate Withdrew Application'
    ELSE w.Status_History  -- Keep original status if no map exists to identify gaps
  END                                             AS ActivityName,          -- Path: Recruiting Workflow Profile -> Workflow -> Status (History)

  -- Recommended Attributes
  job.Recruiter.FullName                        AS Recruiter,             -- Path: Job Profile -> Detail Tab -> Recruiter -> Full Name: First Last
  job.HiringManager.FullName                    AS HiringManager,         -- Path: Job Profile -> Detail Tab -> Hiring Manager -> Full Name: First Last
  job.SystemId                                  AS JobRequisitionId,      -- Path: Job Profile -> System ID
  job.Department                                AS Department,            -- Path: Job Profile -> Detail Tab -> Department
  w.Source                                        AS ApplicationSource,     -- Path: Recruiting Workflow Profile -> Detail Tab -> Source
  w.Status                                      AS ApplicationStatus      -- Path: Recruiting Workflow Profile -> Workflow -> Status

FROM 
  RecruitingWorkflowSearch w
  JOIN JobProfile job ON w.JobId = job.SystemId

import requests
import pandas as pd
import json
import base64
from datetime import datetime

class=class="hl-string">"hl-comment"># --- CONFIGURATION --- 
class=class="hl-string">"hl-comment"># Replace with your iCIMS credentials and customer ID
ICIMS_CUSTOMER_ID = class="hl-string">'[Your Customer ID]'
ICIMS_USERNAME = class="hl-string">'[Your API Username]'
ICIMS_PASSWORD = class="hl-string">'[Your API Password]'

class=class="hl-string">"hl-comment"># Define the date range for the extraction (YYYY-MM-DD)
START_DATE = class="hl-string">'2023-01-01'
END_DATE = class="hl-string">'2023-12-31'

class=class="hl-string">"hl-comment"># CRITICAL: Configure this mapping based on your iCIMS workflow statuses.
class=class="hl-string">"hl-comment"># The keys should be the class="hl-string">'id' or class="hl-string">'value' from the status object in the API response.
class=class="hl-string">"hl-comment"># The values are the standardized activity names for the event log.
STATUS_TO_ACTIVITY_MAPPING = {
    class=class="hl-string">"hl-comment"># Example Format: class="hl-string">'[iCIMS Status ID or Value]': class="hl-string">'[Activity Name]'
    class="hl-string">'D10100': class="hl-string">'Application Received',
    class="hl-string">'C21520': class="hl-string">'Application Screened',
    class="hl-string">'C21540': class="hl-string">'Candidate Phone Screened',
    class="hl-string">'C21580': class="hl-string">'Candidate Submitted To Hiring Mgr',
    class="hl-string">'C21600': class="hl-string">'First Interview Scheduled',
    class="hl-string">'C21620': class="hl-string">'First Interview Conducted',
    class="hl-string">'C51630': class="hl-string">'Feedback Submitted',
    class="hl-string">'C21640': class="hl-string">'Second Interview Scheduled',
    class="hl-string">'C22500': class="hl-string">'Offer Extended',
    class="hl-string">'C22520': class="hl-string">'Offer Accepted',
    class="hl-string">'C22560': class="hl-string">'Background Check Initiated',
    class="hl-string">'C23000': class="hl-string">'Candidate Hired',
    class="hl-string">'C22540': class="hl-string">'Offer Rejected',
    class="hl-string">'C10220': class="hl-string">'Application Rejected By Company',
    class="hl-string">'C10120': class="hl-string">'Candidate Withdrew Application',
}

class=class="hl-string">"hl-comment"># --- SCRIPT LOGIC (No changes needed below this line) ---

BASE_URL = fclass="hl-string">'https://api.icims.com/customers/{ICIMS_CUSTOMER_ID}'

def get_auth_token(username, password):
    creds = fclass="hl-string">'{username}:{password}'
    return base64.b64encode(creds.encode(class="hl-string">'utf-8')).decode(class="hl-string">'utf-8')

def get_paginated_results(url, headers, payload):
    results = []
    next_url = url

    while next_url:
        try:
            response = requests.post(next_url, headers=headers, data=json.dumps(payload))
            response.raise_for_status()
            data = response.json()
            results.extend(data.get(class="hl-string">'searchResults', []))

            class=class="hl-string">"hl-comment"># Check for next page link
            next_url = None class=class="hl-string">"hl-comment"># Default to stop
            links = data.get(class="hl-string">'links', [])
            for link in links:
                if link.get(class="hl-string">'rel') == class="hl-string">'next':
                    next_url = link.get(class="hl-string">'url')
                    break class=class="hl-string">"hl-comment"># Found the next link, continue loop

            print(fclass="hl-string">"Retrieved {len(results)} applications so far...")

        except requests.exceptions.RequestException as e:
            print(fclass="hl-string">"Error fetching data: {e}")
            print(fclass="hl-string">"Response content: {response.text}")
            break

    return results

def main():
    print(class="hl-string">"Starting iCIMS data extraction...")
    auth_token = get_auth_token(ICIMS_USERNAME, ICIMS_PASSWORD)
    headers = {
        class="hl-string">'Authorization': fclass="hl-string">'Basic {auth_token}',
        class="hl-string">'Content-Type': class="hl-string">'application/json'
    }

    search_url = fclass="hl-string">'{BASE_URL}/search/jobprofiles'
    search_payload = {
        class="hl-string">"filters": [
            {
                class="hl-string">"name": class="hl-string">"jobprofile.lastupdated",
                class="hl-string">"value": [fclass="hl-string">"{START_DATE} 00:00:00", fclass="hl-string">"{END_DATE} 23:59:59"],
                class="hl-string">"operator": class="hl-string">"="
            }
        ],
        class="hl-string">"columns": [class="hl-string">"person.id", class="hl-string">"job.id", class="hl-string">"status", class="hl-string">"id"]
    }

    print(fclass="hl-string">"Fetching applications updated between {START_DATE} and {END_DATE}...")
    applications = get_paginated_results(search_url, headers, search_payload)
    print(fclass="hl-string">"Total applications found: {len(applications)}")

    event_log = []
    processed_count = 0

    for app in applications:
        try:
            job_application_id = app.get(class="hl-string">'id')
            job_profile = app.get(class="hl-string">'jobprofile', {})

            class=class="hl-string">"hl-comment"># The full history is often included in the initial search result's status object
            status_history = job_profile.get(class="hl-string">'status', {}).get(class="hl-string">'history', [])

            if not job_application_id or not status_history:
                continue

            job_details = job_profile.get(class="hl-string">'job', {})
            person_details = job_profile.get(class="hl-string">'person', {})

            recruiter_info = job_details.get(class="hl-string">'recruiters', [{}])[0]
            hiring_manager_info = job_details.get(class="hl-string">'hiringmanagers', [{}])[0]

            for history_item in status_history:
                status_id = history_item.get(class="hl-string">'id')
                if status_id in STATUS_TO_ACTIVITY_MAPPING:
                    activity_name = STATUS_TO_ACTIVITY_MAPPING[status_id]
                    timestamp_str = history_item.get(class="hl-string">'date')
                    
                    class=class="hl-string">"hl-comment"># Format timestamp to ISO 8601 with Z
                    dt_obj = datetime.strptime(timestamp_str, class="hl-string">'%B %d, %Y %I:%M:%S %p')
                    activity_start_time = dt_obj.strftime(class="hl-string">'%Y-%m-%dT%H:%M:%SZ')

                    event_data = {
                        class="hl-string">'JobApplicationId': job_application_id,
                        class="hl-string">'ActivityName': activity_name,
                        class="hl-string">'ActivityStartTime': activity_start_time,
                        class="hl-string">'Recruiter': recruiter_info.get(class="hl-string">'fullname'),
                        class="hl-string">'HiringManager': hiring_manager_info.get(class="hl-string">'fullname'),
                        class="hl-string">'JobRequisitionId': job_details.get(class="hl-string">'id'),
                        class="hl-string">'Department': job_details.get(class="hl-string">'jobfolder'),
                        class="hl-string">'ApplicationSource': person_details.get(class="hl-string">'sourcename'),
                        class="hl-string">'ApplicationStatus': job_profile.get(class="hl-string">'status', {}).get(class="hl-string">'value')
                    }
                    event_log.append(event_data)

            processed_count += 1
            if processed_count % 100 == 0:
                print(fclass="hl-string">"Processed {processed_count}/{len(applications)} applications...")

        except Exception as e:
            print(fclass="hl-string">"Error processing application {app.get('id')}: {e}")

    if not event_log:
        print(class="hl-string">"No events were generated. Please check your STATUS_TO_ACTIVITY_MAPPING.")
        return

    df = pd.DataFrame(event_log)
    output_filename = class="hl-string">'icims_recruitment_event_log.csv'
    df.to_csv(output_filename, index=False, encoding=class="hl-string">'utf-8')

    print(fclass="hl-string">"\nExtraction complete. Event log saved to {output_filename}")

if __name__ == class="hl-string">'__main__':
    main()