Determining Demographics from Applicant Surveys

This is the process used for determining the missing demographic majorities for with applicants with application surveys in either the IDAO or IDFPR S3 buckets.

Look at Sam's Missing Demographics Worksheet view (or whichever view you are using) in the Companies Table. If a company "Has Owners = 1", this means that there are owners listed for that company, whereas if a company "Has Owners = 0", this means that there are not owners listed for that company. The workflows for each option vary slightly and are detailed below:

  1. Choose a company in the Missing Demographics view
    1. If a company "Has Owners = 1":
      1. Copy Company Name: Copy the "Company Name" in question.
      2. Search Applicants Table in Ownership Base: Go to the Applicants table of the Ownership base. Then search using airtable's filter function with the copied "Company Name" to view the company.
      3. Record: Record the largest shareholders, their missing demographics, and the application number.
    2. If a company "Has Owners = 0":
      1. Look at application number: Find company in question's "Application Number" in the "Applications" column of the Companies Table.
  2. Find Application: Use the company's application number to decide which S3 bucket to search following the instructions below:
    1. 4 digit applications are IDOA and sorted by application number. Search using Application Number.
    2. 7 digit ones are IDFPR and sorted by company name. Search using Name of Company.
  3. Record Demographics: Sift through the various exhibits in the IDOA or IDFPR application using the algorithms below. Then record the respective gender, and race/eth of the largest owners in descending order. (note: exhibits, especially in the IDOA bucket, may be mislabeled so look through all files if necessary)
    1. If application is in the IDFPR:
      • Ownership %: Exhibit C has an optional ownership breakdown table
      • Ownership % and Race/Gender: Exhibit B has required ownership and required demographic information on a separate page for each applicant. Look at fingerprints to find the demographic information if it isn't on the ownership form.
    2. If application is in the IDOA
      • Ownership %: Exhibit varies for optional ownership breakdown table
      • Ownership % and Race/Gender: Exhibit L has one page per applicant with required ownership and optional demographic information.
      • Race/Gender: Exhibit O's fingerprint form has required demographic information and one page per applicant.
  4. Search Contacts Table in the Licensees Base: Go to the Contacts table of the Licensees base. Search using airtable's filter function with the owner's name for whom you have found missing demographics information.
  5. Fill in the Missing Demographics: Search either returns a matching contact or not. Follow the instructions below for each scenario:
    1. If no existing contacts match the query:
      1. Create a new contact (name, dob, gender, and race/eth)
      2. Change filter (using copied company name) to view the company in the Applicants table of the Ownership base
      3. Delete the "temp tempN/A N/A" contact and replace it with the contact matched/created above
      4. Enter "Applicant Ownership %"
    2. Else, enter gender and race/eth for that contact.
  6. Repeat: Repeat Steps 3-5 with next largest owner and then stop once there is a majority race in the ownership, or if no demographic information can be found.
    1. You can check whether you have reached a majority in the company ownership by looking at the original missing demographics view to see if the company is still there. Or you can check by looking at the "Majority Race/Ethnicity" column in the Applications table of the Ownership base.
  7. If no majority achieved, check the "Researched" column. These contacts can potentially have their demographics inferred later.