Have you ever built a technology process that allows people to apply to things? Followed by a process to review the applications and make a decision to approve some of them and decline others? Hot diggity, there are SO many “moving parts” to consider! And so much at stake. In this post, I’m going to share how I navigated some of the technology issues that came up in a recent project. I’m going to write about the strategy and (imperfect) decisions that I made. But, I’m not going to have room to discuss the exact formulas/code/techniques that I used to implement those decisions. So if you want to learn more about those, please leave me a comment for a future blog post!

I believe this whole topic is a prime example of “no right answer” and lots of, well, I don’t want to call them “wrong” answers but perhaps “pitfalls” is a better word. After all, making decisions and then evaluating them is a big part of how we learn!

One thing I will add here is that the process we were building changed significantly as it unfolded. AND DID YOU HEAR ME COMPLAIN? I’ll wait! Just kidding.

Me, actually: Of course it changed! I would be surprised if it didn’t! This is a small org running a competitive application for the first time. The whole point of using these new-fangled technologies should be to be able to course correct when we need to, while staying true to some fundamentals.

Keep reading to see the different project phases that we tackled, and which tools were the “right” ones for the job.

Recruitment

  • Tech: Pre-launch interest form, newsletter, social media; post-launch reports to see if we were on track to hit the goal for number of applicants

Design an application form

  • Tech: We did this in FormAssembly
  • Things to think about:
    • Do you need an “auto-responder” upon form submission?
    • Do you need conditional questions?
    • Do you need data validation?
    • Do you need graphics/branding?
    • Do you need a “save and continue” option?
    • Do you need the form to “close” at a specific date/time?
    • Are you requesting application responses in text, video, attachments, URLs/links, or all of the above?
    • Troubleshooting form issues

Store application responses in Salesforce

  • Tech: We used the FormAssembly post-submit Salesforce connector
  • Data Model: We stored most data fields on the Contact or the Campaign Member objects. Contact for “immutable” data (like contact info, demographic info) and Campaign Member for “mutable data” (data specific to this Campaign/application process)
  • Things to think about:
    • Human-audit for “false duplicates” – same contact, different email. We got a few of these and it caused data integrity issues.
    • Pros/cons of Campaign Member object to store data. Alternative would be an “Applications” object with an “Applications Submission” junction object to store responses.
    • Make sure to build a companion report that includes all of the application fields

Communicate with applicants

  • Tech: We did this with List Emails (this was easy because applicants were already organized into Campaigns)
  • Things to think about:
    • There are some limitations to list emails and email templates (our biggest issue was design-related)
    • Use reports to move Contacts to a new campaign for “advancing” and “declined” and then use list Emails to send different Email Templates to those cohorts
    • Overall, this went smoothly!

Allow applicants to revise responses

  • Tech: We used FormAssembly Prefill to devise custom links for each applicant to a form which had their previous answers and allowed them to revise responses. We used Email Templates and List Emails to send out emails with each respondent’s unique form URL
  • Data model:
    • We created new fields for “original response” and “revised response” for comparison sake. Alternatively, you could turn on Field History Tracking or create new records for this in the Application Submission object.
    • We kept application data on the Campaign Member and added a Campaign Member Status for “Revision Submitted”
    • Alternative 1 – create another Campaign (under the same Parent Campaign) to track submissions of this form
    • Alternative 2 – create another record in the “Application Submission” junction object
  • Things to think about:
    • If you are adding questions to the form, you will need to thoroughly test the form – schedule time for this!
    • Inform recipients to not share their URL or forward the email
    • Cannot send the generic form URL to stragglers if you do not know how to build the custom URL
    • Turn on Field History Tracking if you want to see prior answer OR use pre/post fields. We displayed the prior response as a read-only question in the Form and a new question for revisions.

Evaluate responses

  • Tech: Xappex and Google Sheets
  • Description: We needed the agility of spreadsheets to support external reviewers to see application responses and score them. Once the application data were in spreadsheet format, I used formulas like =Query(), =Filter(), =ImportRange(), and =VLOOKUP(), to divide rows into personal tabs for each reviewer, and then bring their review comments back to the main data tab. This enabled reviewers to not be influenced by each other’s opinions or overwhelmed by the scope of the full dataset, while the operations team could see all reviewer opinions in the same place
  • Things to think about:
    • What are the expectations for reviewers?
    • Are there any datapoints that should be hidden from reviewers?
    • What types of data do reviewers provide (rubric? written opinion?)? In other words, do you record their full analysis or only who is advancing to the next round?
    • Do data from reviewers need to be stored in Salesforce?

Suppress declined contacts from future emails

  • Tech: Salesforce
  • Description: Created a checkbox on the Contact for “Program [xyz] Declined 2023” and populated via upload
  • Rationale: Storing this info in a campaign was not enough to exclude emailing these people about other events/opportunities. Staff also needed easy access to “list views” of these individuals.
  • Things to keep in mind:
    • If you never, ever want to email a contact, you can use the “do not contact” or “do not email” standard fields in Salesforce
    • The danger here is “field proliferation” so gotta keep a close eye on this or wrap it in a governance process for adding and deprecating new fields.

Is this future proof?

No! This is an example of a project where “we built the plane as it flew;” but it was also informed by decades of life experience both in the subject area of the selective opportunity and form-building.

Next year, we might need to ask different questions or we might not do an intermediary step of an application revision… but what I’m proud of is… we were able to meet changing objectives without creating TOOOOO much technical debt. At the end of the day… we have

  • ONE new field on Contact
  • THREE new forms (recruitment, application, application revision)
  • A slew of campaigns
  • A bunch of new fields (Campaign Member object) – these store data from application questions
  • A few email templates
  • ONE complex spreadsheet
  • A structure to maintain institutional memory (ie who made it to each phase of the application process… as this will impact future relationship milestones with these individuals)

Leave a Reply