Phenom on Workday: Reporting setup guide for Archer

How to build a candidate-level funnel report in Phenom Report Builder so you and Archer can track how Archer applicants progress through your hiring process.

Summary

If you run Phenom on top of Workday, the most reliable way to report on Archer applicants is a custom SQL query in Phenom's Report Builder. The query joins your Lead to Hire data to your Jobs data and returns one row per candidate per job, with a 1/0 flag for each hiring step the candidate reached. Phenom's standard dashboards can't be drilled into, so they don't give candidate-level pass-through rates. An example query is included at the end of this guide. Your hiring step names were set up during your own implementation, so the main task is mapping your steps into that query.

Before you start

• Report Builder access. Phenom's Report Builder (query builder) is available to Phenom clients. If you can't see it, contact your Phenom customer success manager.

• An owner who knows your Phenom data. The report is most useful when it is built and checked by someone who understands what your Phenom data means and where it comes from, usually a TA operations or HR systems lead. Archer can help with the structure; your owner confirms the numbers are right.

• Your hiring steps and disposition reasons. Have the list of hiring statuses and steps configured in your Workday-Phenom setup to hand. These differ from company to company.

• The Workday integration. Check that the hiring status integration between Workday and Phenom is two-way, so a recruiter updating a candidate in the Phenom CRM is reflected in both systems. This guide is written for Phenom on Workday. If you use Phenom with a different ATS, the hiring status data may behave differently.

How Phenom data is structured

• Candidates and jobs are separate data sets. A candidate can apply to many jobs, so the report works at the level of one candidate on one job.

• Lead to Hire is the main data set for hiring statuses. There are two versions: Lead to Hire, and Lead to Hire for closed jobs. Include the closed-jobs version if the reporting period covers roles that have since closed.

• Hiring status has two parts: a status and a step. For a rejected candidate the status is "Rejected" and the step holds the disposition reason. Report on the step rather than the status, so rejection reasons are kept.

• Disposition reasons often depend on the stage. Many setups restrict which reasons can be used at each stage (for example, "Rescind offer" isn't available at phone screen), so the list of values can be long.

• Job dates differ between Workday and Phenom. Workday's job creation date is not the same as Phenom's, so filter jobs on a posted date early enough to include every relevant requisition, then narrow by application date.

Step-by-step setup

1. Open Report Builder. In Phenom, go to Report Builder and choose Create to open the query builder. Phenom's data sets are listed there.

2. Identify the data sets. Locate the Lead to Hire data (and its closed-jobs version) and the Jobs data. In the example query these appear as APPLICATIONS_DETAILS, APPLICATIONS and JOBS. Table names can vary, so check them against what you see in Report Builder.

3. Map your hiring steps. From your list of steps, pick the ones that represent the funnel stages to track, such as phone screen, submitted to hiring manager, interview, offer extended and offer accepted. Use the exact step names as they appear in Phenom, including capitalisation.

4. Adapt the example query. Copy the example query into the query builder and change the step names in each CASE line, the two date filters and the apply source filter (see "Adapting the query").

5. Run it and spot check. Use the ATS candidate ID to look up a few candidates in Phenom and confirm that the flags and current step match their history.

6. Filter to Archer applicants. The query returns applicants from all sources. Add a source filter to the query, or filter the output to the Archer apply source.

7. Agree delivery with Archer. Decide whether you will export the report manually each week or schedule it (see "Sharing the report with Archer").

Report columns

Adapting the query

• How it works. APPLICATIONS_DETAILS holds the history of status changes, one row per step. Each CASE line checks whether any row for that candidate and job matches a step, and MAX turns that into a single 1 or 0. APPLICATIONS holds the application's current step, which becomes FURTHEST_STEP_REACHED.

• Step names. Replace the quoted step names with your own, and add or remove CASE lines to match your process. A name that doesn't match exactly returns 0 for every candidate, so a column of all zeros usually means a typo or a renamed step.

• Job date filter (j.POSTED_DATE). Set it early enough to include every requisition Archer is sourcing for, allowing for the Workday and Phenom date difference.

• Application date filter (a.APPLIED_DATE). Set it to the start of your Archer trial or subscription.

• Apply source. To limit the output to Archer applicants, add a line to the WHERE clause such as AND a.APPLY_SOURCE = '<value>', using the exact source value mapped for Archer in your setup. Run the query once without the filter to see the values.

• "Furthest step" is the current step. For a rejected candidate it shows the disposition reason rather than the last stage they passed. Use the stage flags for pass-through rates and this column for rejection reasons.

Sharing the report with Archer

• Manual export. Run the report and send it to your Archer contact as a spreadsheet, weekly or at an agreed cadence.

• Scheduling. Report Builder can schedule a saved report to run and send automatically. Scheduled reports can only be sent to people with Phenom access, so someone on your team will need to forward it to Archer.

Common issues

• Candidates not dispositioned. If candidates are left in a stage instead of being rejected with a reason, the funnel looks healthier than it is and rejection reasons are missing. Check that recruiters are dispositioning Archer applicants.

• Apply source accuracy. Compare the number of Archer applicants in the report with the numbers Archer shares with you. A large gap usually points to a source mapping issue.

• A working query stops returning data. Phenom occasionally changes its data sets. If a query that worked suddenly fails, raise it with Phenom support.

Example SQL query

This is an example from a Phenom on Workday setup. Replace the step names, dates and source filter with your own before running it.

SELECT
    a.ATS_ID AS ATS_CANDIDATE_ID,
    a.APPLY_SOURCE,
    a.JOB_ID,
    a.JOB_TITLE,
    TO_CHAR(j.POSTED_DATE, 'MM/DD/YYYY') AS POSTED_DATE,
    app.HIRING_STATUS_STEP AS FURTHEST_STEP_REACHED,
    MAX(CASE WHEN a.HIRING_STATUS_STEP = 'Recruiter Phone Screen' THEN 1 ELSE 0 END) AS RECRUITER_PHONE_SCREEN,
    MAX(CASE WHEN a.HIRING_STATUS_STEP = 'Submit to Hiring Manager' THEN 1 ELSE 0 END) AS SUBMIT_TO_HM,
    MAX(CASE WHEN a.HIRING_STATUS_STEP = 'Submit to Client' THEN 1 ELSE 0 END) AS SUBMIT_TO_CLIENT,
    MAX(CASE WHEN a.HIRING_STATUS_STEP = 'Interview with Hiring Manager' THEN 1 ELSE 0 END) AS INTERVIEW_HM,
    MAX(CASE WHEN a.HIRING_STATUS_STEP = 'Interview with Client' THEN 1 ELSE 0 END) AS INTERVIEW_CLIENT,
    MAX(CASE WHEN a.HIRING_STATUS_STEP = 'Offer Extended' THEN 1 ELSE 0 END) AS OFFER_EXTENDED,
    MAX(CASE WHEN a.HIRING_STATUS_STEP = 'Offer Accepted' THEN 1 ELSE 0 END) AS OFFER_ACCEPTED
FROM APPLICATIONS_DETAILS a
INNER JOIN JOBS j
    ON a.JOB_ID = j.JOB_ID
LEFT JOIN APPLICATIONS app
    ON a.JOB_ID = app.JOB_ID
    AND a.ATS_ID = app.ATS_ID
WHERE j.POSTED_DATE >= '2026-01-01'
  AND a.APPLIED_DATE >= '2026-07-15'
GROUP BY
    a.ATS_ID,
    a.APPLY_SOURCE,
    a.JOB_ID,
    a.JOB_TITLE,
    TO_CHAR(j.POSTED_DATE, 'MM/DD/YYYY'),
    app.HIRING_STATUS_STEP;