# Project 2 data dictionary

Money is in cents. Dates are `YYYY-MM-DD` in the company's local calendar. A blank value is unknown or not applicable, never zero. Data build `c14c2737ec6d`, the same version the data explorer and the starter notebook name.


## `app_login_days` (32,135 rows)

| column | type | description |
|---|---|---|
| `driver_id` | INTEGER | Part of the primary key (`driver_id`, `login_date`). Foreign key to `drivers.driver_id`. The driver who logged in. |
| `login_date` | TEXT | Part of the primary key (`driver_id`, `login_date`). One row per day a driver authenticated. No time of day is kept. |
| `logins` | INTEGER | Number of logins that day. |
| `platform` | TEXT | ios, android, or web. |

## `charging_sessions` (39,556 rows)

| column | type | description |
|---|---|---|
| `session_id` | INTEGER | Primary key. Unique id for the session. |
| `driver_id` | INTEGER | Foreign key to `drivers.driver_id`. The driver who charged. |
| `station_id` | TEXT | The charging station. |
| `station_state` | TEXT | State where the station is. Not the driver's address. |
| `session_date` | TEXT | Date of the charging session. |
| `kwh` | REAL | Energy delivered, in kilowatt-hours. |
| `txn_id` | INTEGER | The wallet_ledger row that billed this session. Blank if none exists. |

## `company_notices` (12,673 rows)

| column | type | description |
|---|---|---|
| `notice_id` | INTEGER | Primary key. Unique id for the notice. |
| `driver_id` | INTEGER | Foreign key to `drivers.driver_id`. The driver it was sent to. |
| `sent_on` | TEXT | Date it was sent. |
| `channel` | TEXT | email, postal, or push. |
| `notice_type` | TEXT | balance_statement, promo_email, or service_alert. |
| `bounced` | INTEGER | 1 if the message could not be delivered. |

## `drivers` (8,000 rows)

| column | type | description |
|---|---|---|
| `driver_id` | INTEGER | Primary key. Wallet holder. Joins everything else. |
| `opened_on` | TEXT | Date the wallet was opened. |
| `closed_on` | TEXT | Date the driver closed the wallet in the app. Blank if it is open. Closing pays nothing out, because the wallet cannot be withdrawn. |
| `address_line1` | TEXT | Street address as captured at signup. Blank when no address was captured. |
| `city` | TEXT | City as captured at signup. |
| `state_raw` | TEXT | State or province exactly as captured at signup. Never cleaned. |
| `postal_raw` | TEXT | Postal code exactly as captured. Keep it as text: foreign codes are not numbers. |
| `country_raw` | TEXT | Country as captured. Blank when no address was captured. |
| `mail_returned_on` | TEXT | Most recent date postal mail came back undelivered. Blank if it never has. |

## `due_diligence_letters` (422 rows)

| column | type | description |
|---|---|---|
| `letter_id` | INTEGER | Primary key. Unique id for the letter. |
| `driver_id` | INTEGER | Foreign key to `drivers.driver_id`. The owner the letter was mailed to. |
| `cycle_year` | INTEGER | Reporting cycle the letter belonged to. Prior cycles only. |
| `mailed_on` | TEXT | Date the letter was mailed. |
| `method` | TEXT | first_class or certified. |
| `returned_on` | TEXT | Date the postal service returned it undelivered. Blank if it was not. |
| `owner_responded_on` | TEXT | Date the owner responded. Blank if they did not. |

## `gl_wallet_control` (270 rows)

| column | type | description |
|---|---|---|
| `period_end` | TEXT | Part of the primary key (`period_end`, `gl_account`). Month-end date of the balance. |
| `gl_account` | TEXT | Part of the primary key (`period_end`, `gl_account`). 2110 wallet cash liability. 2115 deferred promotional credits. 2120 customer deposits. |
| `gl_balance_cents` | INTEGER | General ledger balance at that period end, in cents. |

## `reservation_events` (3,048 rows)

| column | type | description |
|---|---|---|
| `event_id` | INTEGER | Primary key. Unique id for the event. |
| `reservation_id` | INTEGER | Foreign key to `reservations.reservation_id`. The reservation the event belongs to. |
| `activity_on` | TEXT | Date the event happened. |
| `posted_on` | TEXT | Date it posted to the general ledger. |
| `event_type` | TEXT | deposit, application (the deposit applied to a vehicle order), or refund. |
| `amount_cents` | INTEGER | Signed, in cents. Positive increases the deposit held. |
| `status_after` | TEXT | The reservation's status after the event: open, converted, or cancelled_refunded. |
| `source_channel` | TEXT | Same channel vocabulary as the ledger. |

## `reservations` (2,104 rows)

| column | type | description |
|---|---|---|
| `reservation_id` | INTEGER | Primary key. Unique id for the reservation. |
| `driver_id` | INTEGER | Foreign key to `drivers.driver_id`. The driver who placed it. |
| `placed_on` | TEXT | Date the reservation was placed. |
| `model` | TEXT | The vehicle model reserved. |

## `run_params` (1 row)

| column | type | description |
|---|---|---|
| `cutoff_date` | TEXT | The cutoff date for this reporting cycle. |
| `observed_through_date` | TEXT | The last date the data covers. |
| `report_due_date` | TEXT | Date the report is due to the states. |
| `diligence_window_open` | TEXT | First date due diligence letters can be mailed this cycle. |
| `diligence_window_close` | TEXT | Last date due diligence letters can be mailed this cycle. |

## `wallet_ledger` (57,916 rows)

| column | type | description |
|---|---|---|
| `txn_id` | INTEGER | Primary key. Unique id for the row, assigned in the order rows were received. |
| `driver_id` | INTEGER | Foreign key to `drivers.driver_id`. The wallet holder the row belongs to. |
| `session_id` | INTEGER | Foreign key to `charging_sessions.session_id`. The charging session the row relates to, when there is one. |
| `activity_on` | TEXT | Date the transaction happened. |
| `posted_on` | TEXT | Date it posted to the general ledger. Usually the same day. |
| `txn_type` | TEXT | load, charge, refund, fee, adjustment, reversal, promo_grant, promo_redeem, promo_expire. A refund is a credit back into the wallet for a disputed charging bill; the wallet cannot be withdrawn. |
| `amount_cents` | INTEGER | Signed, in cents. Positive increases the wallet. |
| `source_channel` | TEXT | The system the entry was submitted through: mobile_app, web_portal, kiosk, ivr, csr_console, batch_job, partner_api. |
| `entered_by` | TEXT | Employee identifier when a person keyed the entry. |
| `reverses_txn_id` | INTEGER | Foreign key to `wallet_ledger.txn_id`. On a reversal, the txn_id it backs out. On a refund, the charge it corrects. |

## `run_params` and `_meta`

`run_params` holds the cutoff and reporting dates for this cycle. `_meta` holds the build id and file checksums. `_data_dictionary` is this document in table form, so the tutor can read it.