Upload listing info from new listing spreadsheet

[anders-schneider]

Gabe sent a spreadsheet with 2285 listings, and more granular fields than the data we have from ArcGIS. (I'm not sure what permissions we want on that spreadsheet, so won't link it here.) This ticket will track building a pipeline to populate the DB from that spreadsheet. I imagine this will look like a script that's parallel to backend/core/scripts/import-listings-from-detroit-arcgis.ts.

Done criteria: we can run a script to get listing data from the spreadsheet Gabe sent into the DB.

[anders-schneider]

A couple thoughts to narrow the scope:

• This script should simply add one listing per row in the spreadsheet. It should not delete anything from the DB or attempt to reconcile an existing listing in the DB with a listing in the spreadsheet.
  • The underlying assumption is that this is a script we'll run once, to initialize the data. Evidence: Gabe indicated that "This is a manually managed HRD spreadsheet that contains all of the regulated affordable housing properties that should appear on the site at launch".
• Let's have this script do what our existing scripts do: when a listing says it has 11 units, we create 11 units in the units table.
  • This is inefficient and will likely make the first (uncached) backend request for listings have even worse latency... but it makes everything else work (unitsSummarized is populated correctly, the frontend shows stuff correctly, etc.). And we'll tackle this issue in our discussion around having a UnitInfo (or UnitSummary?) table.

Open questions to answer:

• The spreadsheet includes a breakdown of number of units by unit type (studio, 1br, 2br, ...) and a breakdown of units by affordability at different AMI levels (15% AMI, 20% AMI, ...). It does not break down by both categories, so it's generally impossible to say e.g. how many of the 2bedrooms in a listing are affordable at 50% AMI. Without making changes to Bloom's data model, I think this means we have to choose which breakdown (by unit type or by AMI level) we care about. Oof.
• (Less important than the question above) Some rows don't list a total number of affordable units, but they do list a breakdown by unit type. How should we upload those? What about the ones that don't have a total number or any of the unit-type totals either?
  • My feeling:
    • If we have the units-broken-down-by-type, treat that as the source of truth (don't worry about whether the total is present or not) --> create units according to the units-broken-down-by-type
    • If we don't have units-broken-down-by-type but we do have total units, create that number of units and make their type be "unknown" (I think this will have to be a separate chunk of work - I don't think we have that "unknown" type or the ability to group by it)
    • If we don't have any indication of how many units there are, either create a listing with no units or create a listing with a single "placeholder" unit with type "unknown".

[anders-schneider]

Decision from meeting with Safiya: we'll keep the breakdown by unit type, and we'll condense the breakdown by affordability into a single field "affordability range" (with values like 30-60% AMI). That field will need to be added to the DB (probably on the Listing model).

[anders-schneider]

Recording notes from a conversation with Gabe about how to interpret the "Affordability Mix" field. Some values in there are "Up to 50% AMI". The way we will interpret that is "the most expensive affordable unit in this listing is affordable for someone making 50% of the AMI". This means we don't know what the least expensive affordable unit in the listing is. We could either guess that "Up to X% AMI" means "(X-30) to X% AMI", or we could leave the lower bound blank. I think I favor leaving the lower bound blank, and then in the UI we could represent it as "Affordability: ??? to 50% AMI" or something.

[anders-schneider]

Field mapping (HRD spreadsheet --> Bloom data model):

• HRDID --> listing.hrdid*
• Project Name --> listing.name
• Property Phone --> listing.property.phone_number*
• Owner Company --> listing.owner_company*
• Management Company --> listing.management_company*
• Manager Phone --> listing.leasing_agent_phone
• Manager Contact --> listing.leasing_agent_name
• Manager Email --> listing.leasing_agent_email
• Management Website --> listing.management_website*
• Project Address --> listing.property.building_address.street
• Zip Code --> listing.property.building_address.zip_code
• Latitude --> listing.property.building_address.latitude
• Longitude --> listing.property.building_address.longitude
• Neighborhood --> listing.property.neighborhood
• Region --> listing.property.region*
• Affordable Units --> used to create individual units only iff the next 6 columns are empty
• Number 0BR --> used to create listing.property.units (unit_type: "studio")
• Number 1BR --> used to create listing.property.units (unit_type: "oneBdrm")
• Number 2BR --> used to create listing.property.units (unit_type: "twoBdrm")
• Number 3BR --> used to create listing.property.units (unit_type: "threeBdrm")
• Number 4BR --> used to create listing.property.units (unit_type: "fourBdrm")
• Number 5BR --> used to create listing.property.units (unit_type: "fiveBdrm")
• Affordability Mix --> listing.min_ami_percentage* and listing.max_ami_percentage*

(*) These fields will need to be added. I'll do that in a first-step PR.