Match employee to potential new job

[ITG1]

Hello,

Thanks all for all the great business cases and solves on these forums, they have been invaluable to me as I learn more about how to use Alteryx to create workflows that help our current tasks.

We have a business case that I've been trying to wrap my head around how to proceed, thought I might post here to get some ideas on a solution.

We have interns that rotate on a yearly basis to a new opportunity elsewhere within the company.  I've attached 2 spreadsheets one for the current employee data, one for the new job opportunity information.

The employee needs to be "best" matched with the new opportunity based on the following conditions:

1. Preference location matching: 1-6, 1 is top choice for job location, NA are not feasible destinations
2. If Extenuating circumstance, check if remote is needed then match to available remote roles
3. New role must differ from previous role
4. If previous role was tech, destination role must not be tech
5. Match according to rankings of skill areas (last columns on both sheets, 4 being priority)

I'm going on the assumption that the steps above are ranked by priority, within the actual data set I believe I have 50 employees and 50 jobs, certainly not all new roles will satisfy all criteria as submitted by the employee, so I suppose what I'm looking for is the best methodology/workflow to support matching the employee to the new job.

Maybe with some column for confidence level or rank of how closely it's matched to each other?  I'm not sure how to proceed and how if 2 or more employees both match well to a role, who gets priority, etc.

I'm thinking maybe something that just identifies which employees to new roles matches all criteria, ie) Employee IDs 1, 2, 9 all are a match for job role 3, etc.

I'm not exactly sure.  My guess is they were just doing this manually before by checking all the conditions above in Excel and doing the same thing I'm describing, likely seeing which roles were 100% match for which employees and going down from there and if there were multiple good matches, just manually selecting the candidate and moving on from there.  

In any event, I've attached some dummy sample data and wanted to get your thoughts on best approach.

Thanks!

[OTrieger]

@ITG1 

Many things are going on here, and in these type of cases I like to keep things as simple as possible.

I would create a flag criteria, 1 most desired and 5 or as per the number of different criteria as the least desired.

Then I will join the data to get all possible matches per employee and then use a Summarize tool to get the Min Flag. In this way you will get the to desired role per employee based on the available positions.
That would be the skeleton for it, so you can work around the rest of the conditions.
Good luck

[KGT]

Much like @OTrieger above, I agree that there is a lot going on here. I don't think the issue in front of you is necessarily, "how to do it in Alteryx", as much as what the process should be.

I would just start creating scores and work out what your variables are that people can adjust. Weighting on location for instance. Is USA-1, Mexico-2 etc really the same scale as Japan-1, Mexico-2, India-3... there might be correlation between roles and location or . Test on last years data if you can to see what "manual" corrections occurred. If half the base gets their second preference, then how much do you need to worry about the accuracy of the whole process.

Things I would be careful of:

• You don't need to fully automate the solution. If you created a process that scores against criteria, that would be an advantage. And then a human can validate the scores.
• Test on last years data if you can to see what "manual" corrections occurred.
• If >50% of the base gets their second preference, then how much do you need to worry about the accuracy of the whole process. Is the component of "Someone looking and choosing the best match" actually the bulk of what was done before?
• Expose the process. If something like this is used to "score" people, then build reporting and workpapers into the solution so that anyone can see/investigate why a score ended up as it is.

I think this type of operation would normally take a little while before a human is removed as it's not typically a process that can be entirely described. You are trying to get the automation to do follow rules that are not all written yet.

If it's just the rules you have listed, then score those and present them nicely. Wait for instruction on how they used those scores.

[ITG1]

Thanks both, I appreciate your inputs very much.  It was mentioned that perhaps the first step is to create a flag criteria.  Should I take this to mean first creating a rank column based on conditions I've laid out in the original post and then use the Alteryx rank tool to rank them?  Sorry but I've not used the rank tool before so I'm not sure if I am following correctly or not, the suggestions.

I had assumed since there are potentially multiple candidates for one role, I would need to look into iterative macros perhaps to loop through the datasets and start evaluating the conditions to find all possible matches?

I think part of my confusion lies with having to rank not solely on some columns in the first file but rank tied to how closely they match up with the 2nd file, if that makes any sense.  Apologies, but I'm still a bit lost on where/how to begin.

Thanks again, cheers!

[KGT]

Makes sense. There are a bunch of questions, however not all of them need to be addressed first up.

Example. This does not need to be solved now. If you get 43 assigned and someone has to choose 7, that's fine for the first attempt:

---

Maybe with some column for confidence level or rank of how closely it's matched to each other?  I'm not sure how to proceed and how if 2 or more employees both match well to a role, who gets priority, etc.

One could almost put money on this being true:

"I'm not exactly sure.  My guess is they were just doing this manually before by checking all the conditions above in Excel and doing the same thing I'm describing, likely seeing which roles were 100% match for which employees and going down from there and if there were multiple good matches, just manually selecting the candidate and moving on from there."

I would start by trying to get one dataset that you can then filter on conditions. You can brute force this with an append, but I think creating 3 datasets first and then joining them as needed might work.

Take a look at the attached. It has some methods that might get you started and to the point of where you can clearly see what still has to be decided. I think that it makes sense to split the "Extenuating Circumstances" and assign them first. The skill scores will need some logic behind how to prioritise as many methods would just prioritise people who are high all across.

[ITG1]

Thanks so much KGT, it's definitely different than some of the workflows I've been asked to build that were more straightforward to me, will take a look at your attached today and start to work through it.  Much appreciated.  Cheers.

[ITG1]

Ok, once again thanks so much for this KGT.  I'm following the breakouts now, looking at the last output a 5/5 out of the role_skill_score and employee_skill_score would be the "perfect match" at least as it pertains to that particular skill being highly valued by the employee and that role offering that skill opportunity.  I see what you mean about the human intervention at that point though, there's a lot of different considerations when it comes just to those sets of skills from an individual standpoint.

I assume summing them for each employee per role id would give me some sort of rudimentary ranking mechanism as you mentioned.  Assuming all skills are prioritized the same. 

[KGT]

Not a problem.

The core is removing roles that don't work first (and splitting off the Extenuating Circumstances), so that when you look at the location/role rankings and integrate skill rankings (somehow), you have a smaller dataset.

You're right on the skills. There is a ton of different ways that could be compared. In reference to the rudimentary ranking, I would probably create another field that is [Emp_Skillscore] - [Role Skill Score] and then bucket that as:

• 1bucket: >=2
• 0bucket: 0 to 1
• -1bucket: -1
• -2bucket <= -2

That list can then be summed/counted/aggregated or something else. It will be possible to show "Meets or exceeds on 3 skills, under on 2 skills and non-compliant on 3 skills" for instance. Three scores of -2 might not offset three scores of +2. As these are interns, I expect a score of -2 might be fine if they have enough skills above, and that you will rarely have someone that meets all skills.

[ITG1]

Hi KGT,

I was able to apply all the conditions and am close to the end at this point.  Thanks for all the help, I am "getting it" now.  One last question if I may, even after applying all the conditions, I'm left with something like 12,000 rows of data, this is because there are 8 possible skill types to match up so for each employee and potential role there are 8 rows each.

Something like the below is what is exported

Employee ID	Name	Job Role ID	Skill Name	Role Skill Score	Employee Skill Score
1	John Doe	1	Technology	3	4
1	John Doe	1	Finance	5	1
Rows for rest of skills	for this employee	for this role	then.....
1	John Doe	2	Technology	1	4
1	John Doe	2	Finance	3	3
Lots of rows	for this employee	for rest of roles	and rest of skills
2	Jane Doe	1	Technology	4	4
2	Jane Doe	1	Finance	5	3

etc

What is the best way, you think, to consolidate that data so that I can roll up each employee a bit and not view each row as its own skill, would the cross tab tool work?  I tried playing around with it but was not able to get it so that the skills become columns and the employee ID is rolled up.  Maybe I need transpose for this purpose?  Essentially I'm seeking the best way to reduce the row count and summarize it for the user(s).  After that I can apply more conditions on the ranking, etc.

Thanks!  Much appreciated!

[ITG1]

I think I have it sorted, not sure if it's the cleanest way to do it but I did one crosstab for employee skill score and one crosstab for role skill score then just joined them based on the common fields for employee id, etc.