Project 2, Unclaimed Property at Silicone Motors

Silicone Motors holds its drivers' money in a charging wallet and in refundable reservation deposits. When an owner goes quiet long enough, state law says the company has to report the balance and send it to the state. You read the rules out of a counsel memo, including a schedule that comes as a scanned image, work out what the company owes and to which state, and recommend what the Controller should do before the report goes to the states on November 1. Due Sunday, November 1, 11:59 PM MT. Start with the TLDR at the top of the guide.

Required

The graded work depends on these.

• Project 2 guide
  The assignment. Start with the TLDR at the top: the Controller's two questions, what you turn in, and the steps in order. Then the data, the numbers you report and exactly how each is defined, the deck and one-pager, and how it is graded.
• Counsel memo
  The rules, from outside counsel: dormancy periods by state, what counts as owner contact, where a balance reports, and who gets a due diligence letter. Schedule A is a scanned image on pages 3 and 4, and reading it into a table is part of the work. Turning the memo into a rule table is the first step.
• Project database (.sqlite, about 10 MB)
  The records you analyze. Open it in DB Browser for SQLite, or in Python with sqlite3 or pandas.read_sql. The same tables come as CSV files in p2_csv.zip, for pandas or Excel. Excel users: import with Data > From Text/CSV and set postal codes to Text. The README in the zip has details.
• Data dictionary
  Every table and column in the Silicone Motors records: the wallet ledger, charging sessions, logins, reservations, notices, prior letters and the general ledger control balances, with a diagram of how the tables join.

Optional help

These make the work easier. You can skip any of them.

• Starter notebook (opens in Colab)
  A load cell for every table, a stub for each step in the guide, and a numbers section that lays out every item you report. Save a copy to your Drive before you start.
• Project 2 data explorer
  The full project database in a SQL editor that runs in your browser, with charts and CSV export. Click a table to see its columns, search for any table or column, and see how the tables join in the diagram. Its AI tutor can explain a table or an error and help you plan and debug a query, but it will not tell you the memo's rules or write the query behind a graded number.
• An example one-pager
  A decision one-pager on a different topic, with a note on what makes it work. Use it to see what a one-pager needs; your layout can differ.

Background

Only if escheatment is new to you.

• How an unclaimed property balance is built
  The model behind the project on a small practice example: rebuild a balance, find the last owner contact, test dormancy, route it and decide the letter.

Files

• Download everything (.zip, about 6 MB)
  Every file you can download from this page, in one zip. The interactive pages stay here. A README says what each file is and labels it Required, Optional help or Background.
• Everything for your AI (.txt)
  The guide, the counsel memo and the data dictionary in one plain-text file, with a short note for the AI that the memo's rules govern. Paste it into the AI you work with. Schedule A is not in it, because it is an image, so give the AI your own checked rule table when a question turns on a state's rules.
• Project 2 guide (.txt)
  The assignment as plain text, the same words as the guide page.
• Counsel memo (.txt)
  The memo as plain text. Schedule A is an image, so read it on the memo page or in the zip.
• Data dictionary (.txt)
  Every table and column as plain text.
• Database schema (SQL, as .txt)
  The CREATE TABLE statements, with every join listed at the top. The download-everything zip has the same file as schema.sql.
• Table diagram (.svg)
  Every table, its primary and foreign keys, and how the tables join. It opens in your browser.
• Starter notebook (.ipynb)
  The same notebook for Jupyter or VS Code. It loads the data from the course GitHub repository, or from a p2_csv folder next to it.
• Example one-pager (.pdf)
  The example one-pager as a printable page.