Challenge for the Parabola community! I’ve been trying to wrap my head around this and trying to find the right workflow, wondering if someone out there has any ideas.
(fake data for simplicity)
I have 100 users and 1000 jobs. Each user is either a cook, dishwasher, or waiter. I have jobs in all three categories. Is it possible to pick the top highest paying jobs from each category, then send each user the top 3 paying jobs (in their category) via an email?
Lets say both data sources are csv
The data I have for the users:
name, email, category
The data I have for the jobs:
category, pay, link to apply
The part I get stuck on is
filtering the 100 jobs to only have the top 3 highest paying per category
combing that data into new columns for the users table via some type of join (ideally the new users table would new columns like “Job1, Job2, Job3”)
The part I’m conformable with would be finishing that flow with a sendgrid destination and importing an html template + adding the merge tags from the newly created users table.
What do you think? Any idea how to solve this? Thank you!
Hey Jacob - Fun challenge! Is there a finite number of job categories? You mentioned “all three categories”, but I want to make sure it’s just three.
Also, for the new columns, Job1, Job2, Job3, what data do you want supplied in those columns? Just the link to the job? Or, would you want Job1Pay, Job1Link, Job2Pay, Job2Link, Job3Pay, Job3Link?
Great question, the answer is ‘kind of’. So technically that are unlimited categories, because we may hire a new role, like ‘doorman’. But typically we can expect to always be hiring for our most popular categories (those three mentioned).
Another great question, we would definitely have a few pieces of data for each job, so Job1Pay, Job1Link, Job2Pay, Job2Link, Job3Pay, Job3Link is the ideal format.
I’ve also included some data that should not make it’s way to the final output, including the scenario where we might have a user, and not a job available for them (and vice-versa).
You need your user data formatted so that its 1 row per user+category combination. So if your category column right now shows a list for each user - such as dishwasher, waiter then you need to use a column split to create a row for each category, which would result in each user being on multiple columns.
Sort the job data so that it has the highest paying jobs at the top.
Use a Dedupe step to dedupe the job data, based on the category column, keeping 3 duplicates - this gives you the top 3 jobs per category
Take the job data and join it onto this user data - this should create a row for each possibly job that a user can be sent.
Now you just need to format it in the way that you would like to send it. You probably only want 1 row per user, so you could use a Merge Values step to merge the job link and job pay columns, and merge them according to the User name and User email columns. Use a comma to delimit the merge in the settings.
Use a column split to split those merged columns into new columns, splitting on the comma (or whatever you merged with)
