Chicago Business Licenses → Analytics Schema¶

What

Turn the City of Chicago's raw Business Licenses export into a readable analytics CSV: cryptic status/type codes decoded to plain English, ISO dates, and an explicit marker for the (very common) missing application date.

Why interesting¶

It's a real, actively-maintained municipal dataset whose two most important columns are opaque codes: LICENSE STATUS is AAI / AAC / REV / REA and APPLICATION TYPE is ISSUE / RENEW / C_LOC / C_CAPA / C_EXPA / C_SBA. A naive import leaves them as-is — you cannot tell a cancelled licence from a revoked one without keeping the data dictionary open in another tab. On top of that, 77% of rows have a blank APPLICATION CREATED DATE (only fresh applications carry one), which reads as a data-loss bug unless the gap is made explicit. The fix is exactly what a lookup table + a sentinel are for.

Edge cases sourced from. All code meanings are quoted verbatim from the dataset's own description on the City of Chicago data portal:

LICENSE STATUS — AAI = licence issued, AAC = cancelled during its term, REV = revoked, REA = revocation appealed.
APPLICATION TYPE — ISSUE = initial application, RENEW = renewal, C_LOC = change of location, C_CAPA = change of capacity, C_EXPA = liquor-area expansion, C_SBA = change of business activity.

Data source. City of Chicago — Business Licenses (r5kz-chrr) (this slice: 10 real rows pulled via the Socrata API and hand-picked so that one short table carries all four status codes, three application types, both a present and a blank application date, and a legal name with an embedded comma). Public domain (City of Chicago).

At full scale¶

The committed sample.csv is a 10-row teaching slice; the real register is the complete licence history from 2002 to today. Pull it and run the same template against the whole thing:

bash fetch-full.sh          # downloads ./full/chicago_licenses.csv (~1.2M rows)
bxp-cli --config full.json  # processes the whole register

Measured on the reference machine (ReleaseFast, 8 cores):

metric	value
input / output	1,197,482 rows (1:1) / 194 MB → 121 MB
wall time	~3.1 s
peak RSS	~22 MB (flat)
`issued`	1,117,686
`cancelled_during_term`	78,329
`revoked`	1,453
`revocation_appealed`	13
`applied = <not-on-file>`	918,458 (77%)

Two things the full run surfaces that the slice can't:

1,453 revoked + 78,329 cancelled licences decoded out of the cryptic REV/AAC codes — a compliance query can finally filter on a readable label.
One row carries an undocumented INQ status (the dataset description lists only AAI/AAC/REV/REA). REMAP leaves an unmapped code visible and unchanged rather than blanking it, so the gap surfaces instead of silently vanishing — a forward-safe lookup, not a silent drop.

The tricks¶

(see inline comments in sample.json)

Quoted commas — csv_text_quote_in: "double"; legal/DBA names embed commas.
DBA fallback — COALESCE([doing_business_as_name], [legal_name]) so the business is never blank.
Status code → label — REMAP([license_status], 'license_status_label') over a named map built from the documented AAI/AAC/REV/REA meanings. (Not clickable: the named form resolves through the template's maps registry, which a standalone expression has no access to.)
Application type → label — a CASE multi-branch (a second controlled vocabulary kept inline to show CASE; it could equally be a second named map). CASE matches the code against value/label pairs with the raw code as the fallback — one call in place of a six-deep nested IF.
ISO date trim + missing-date sentinel — IF([application_created_date] = '', '<not-on-file>', DATE_CONVERT([application_created_date], 'YYYY-MM-DD[T]hh:mm:ss', 'YYYY-MM-DD')) keeps the date part; IF([application_created_date] = '', '<not-on-file>', …) turns the 84%-blank column into an explicit marker.

Final result¶

The two columns that decide every compliance query stop being codes:

raw                                     →  converted
AAI    RENEW   (blank date)             →  issued                 renewal              <not-on-file>
AAI    C_LOC   2026-05-28T00:00:00.000  →  issued                 change_of_location   2026-05-28
AAC    RENEW   (blank date)             →  cancelled_during_term  renewal              <not-on-file>
REV    ISSUE   2026-04-15T00:00:00.000  →  revoked                initial_application  2026-04-15
REA    RENEW   (blank date)             →  revocation_appealed    renewal              <not-on-file>

In the raw file REV and AAC are indistinguishable to anyone without the data dictionary open, and a compliance query filtering on the literal string "cancelled" returns zero rows. The blank application date becomes a sentinel you can see, instead of a hole you mistake for data loss.

Sample data¶

Run it with bxp-cli --config ./sample.json --template chicago_licenses_to_analytics:

sample.json (config)sample.csvsample.csvx (result)

{
  // City of Chicago — Business Licenses. The status and application-type
  // columns are cryptic codes that mean nothing without the data dictionary;
  // the application date is blank on most rows. Every mapping below is taken
  // verbatim from the dataset's official description (see 00-readme.md).
  maps: {
    // LICENSE STATUS codes — from the City of Chicago dataset description.
    license_status_label: {
      "AAI": "issued",
      "AAC": "cancelled_during_term",
      "REV": "revoked",
      "REA": "revocation_appealed"
    }
  },
  conversion_templates: {
    chicago_licenses_to_analytics: {
      data_dir:           ".",
      file_pattern_in:    ".csv",
      file_pattern_out:   ".csvx",
      // TRICK 0 — Socrata CSV is double-quoted; legal/DBA names embed commas.
      csv_text_quote_in:  "double",
      csv_text_quote_out: "double",

      input_schema: {
        // TRICK 1 — many rows have no DBA; fall back to the legal name so the
        // business is never blank (COALESCE returns the first non-empty).
        $business:   "COALESCE([doing_business_as_name], [legal_name])",
        $license:    "[license_description]",
        $zip:        "[zip_code]",

        // TRICK 2 — cryptic LICENSE STATUS code → human-readable via a
        // documented named map. "AAC" alone tells an analyst nothing;
        // "cancelled_during_term" is self-explanatory.
        $status:     "REMAP([license_status], 'license_status_label')",

        // TRICK 3 — APPLICATION TYPE code → label, here via a CASE multi-branch
        // (a second controlled vocabulary kept inline to show CASE; it could
        // equally be a second named map). CASE compares its first arg against each value/label pair
        // and returns the first match, with the raw code as the trailing
        // default — far more readable than a six-deep nested IF.
        $app_type:   "CASE([application_type], 'ISSUE', 'initial_application', 'RENEW', 'renewal', 'C_LOC', 'change_of_location', 'C_CAPA', 'change_of_capacity', 'C_EXPA', 'liquor_area_expansion', 'C_SBA', 'change_of_business_activity', [application_type])",

        // TRICK 4 — dates are ISO with a midnight time + millis; keep the date
        // part only. The application date is blank on ~84% of rows — make the
        // gap explicit instead of an empty cell that looks like a data loss.
        $start:      "DATE_CONVERT([license_start_date], 'YYYY-MM-DD[T]hh:mm:ss', 'YYYY-MM-DD')",
        $expires:    "DATE_CONVERT([expiration_date], 'YYYY-MM-DD[T]hh:mm:ss', 'YYYY-MM-DD')",
        $applied:    "IF(ISEMPTY([application_created_date]), '<not-on-file>', DATE_CONVERT([application_created_date], 'YYYY-MM-DD[T]hh:mm:ss', 'YYYY-MM-DD'))"
      },

      row_rules: [ { when: "1 = 1", rows: [ {} ] } ],

      output_schema: {
        business:  "$business",
        license:   "$license",
        zip:       "$zip",
        status:    "$status",
        app_type:  "$app_type",
        start:     "$start",
        expires:   "$expires",
        applied:   "$applied"
      }
    }
  }
}

"legal_name","doing_business_as_name","license_description","application_type","license_status","zip_code","city","state","application_created_date","license_start_date","expiration_date"
"DIYA 31ST HALSTED, LLC","Dunkin'","Retail Food Establishment","RENEW","AAI","60608","CHICAGO","IL",,"2026-07-16T00:00:00.000","2028-07-15T00:00:00.000"
"AVADA, INC.","SUPER SAVINGS FOOD","Retail Food Establishment","RENEW","AAI","60641","CHICAGO","IL",,"2026-07-16T00:00:00.000","2028-07-15T00:00:00.000"
"REDPEG MARKETING, INC.","REDPEG MARKETING, INC.","Pop-Up Retail User","ISSUE","AAI","22314","ALEXANDRIA","VA","2026-05-28T00:00:00.000","2026-05-29T00:00:00.000","2026-06-07T00:00:00.000"
"FLAVIN ELECTRIC, LLC","FLAVIN ELECTRIC, LLC","Limited Business License","ISSUE","AAI","60618","CHICAGO","IL","2026-05-28T00:00:00.000","2026-05-29T00:00:00.000","2027-05-15T00:00:00.000"
"HELLMUTH, OBATA & KASSABAUM, INC.","HELLMUTH, OBATA & KASSABAUM, INC.","Limited Business License","C_LOC","AAI","60601","CHICAGO","IL","2026-05-28T00:00:00.000","2026-05-29T00:00:00.000","2028-04-15T00:00:00.000"
"RAVEN ZBIKOWSKI","LA BONNE FEE","Limited Business License","ISSUE","AAC","60614","CHICAGO","IL","2026-04-15T00:00:00.000","2026-04-16T00:00:00.000","2028-05-15T00:00:00.000"
"VICTORIA'S SECRET STORES, LLC","VICTORIA'S SECRET STORES, LLC #282","Limited Business License","RENEW","AAC","60652","CHICAGO","IL",,"2025-02-16T00:00:00.000","2027-02-15T00:00:00.000"
"PURE BEAUTY CORP","PURE BEAUTY","Limited Business License","ISSUE","REV","60616","CHICAGO","IL","2026-04-15T00:00:00.000","2026-04-15T00:00:00.000","2027-05-15T00:00:00.000"
"BABA'S MINI MARKET, INC.","BABA'S MINI MARKET, INC.","Retail Food Establishment","RENEW","REV","60621","CHICAGO","IL",,"2025-11-16T00:00:00.000","2027-11-15T00:00:00.000"
"CLUB 215, LLC","Lite","Public Place of Amusement","RENEW","REA","60654","CHICAGO","IL",,"2023-05-16T00:00:00.000","2025-05-15T00:00:00.000"

business,license,zip,status,app_type,start,expires,applied
Dunkin',Retail Food Establishment,60608,issued,renewal,2026-07-16,2028-07-15,<not-on-file>
SUPER SAVINGS FOOD,Retail Food Establishment,60641,issued,renewal,2026-07-16,2028-07-15,<not-on-file>
"REDPEG MARKETING, INC.",Pop-Up Retail User,22314,issued,initial_application,2026-05-29,2026-06-07,2026-05-28
"FLAVIN ELECTRIC, LLC",Limited Business License,60618,issued,initial_application,2026-05-29,2027-05-15,2026-05-28
"HELLMUTH, OBATA & KASSABAUM, INC.",Limited Business License,60601,issued,change_of_location,2026-05-29,2028-04-15,2026-05-28
LA BONNE FEE,Limited Business License,60614,cancelled_during_term,initial_application,2026-04-16,2028-05-15,2026-04-15
"VICTORIA'S SECRET STORES, LLC #282",Limited Business License,60652,cancelled_during_term,renewal,2025-02-16,2027-02-15,<not-on-file>
PURE BEAUTY,Limited Business License,60616,revoked,initial_application,2026-04-15,2027-05-15,2026-04-15
"BABA'S MINI MARKET, INC.",Retail Food Establishment,60621,revoked,renewal,2025-11-16,2027-11-15,<not-on-file>
Lite,Public Place of Amusement,60654,revocation_appealed,renewal,2023-05-16,2025-05-15,<not-on-file>

Full-scale & binary files (run it on the complete dataset): fetch-full.sh · full.json.