SKILL.md

skills/thanksio-postcard-formatter/SKILL.md

1. ---
2. name: thanksio-postcard-formatter
3. description: >-
4. Transform raw Meridian Display Co. order export XLSX files (from Plinth, the in-house order
5. system) into clean Thanks.io-ready postcard shipping CSVs. Use this skill when Matt mentions
6. processing a postcard sheet, formatting addresses for Thanks.io, running the postcard formatter,
7. dropping a new XLSX into the Thanksio folder, 'the postcard sheet,' 'Thanks.io export,' 'postcard
8. addresses,' or any variant, including casual phrasing like 'process the new postcard sheet,'
9. 'clean up the addresses for postcards,' or 'run the postcard formatter.' Also triggers when an
10. XLSX is placed in the 'Thanksio-Post-Card-Sheet-Formatter/01 - New/' folder.
11. ---
13. # Thanks.io Postcard Sheet Formatter
15. Transforms raw 21-column XLSX order exports from Plinth, Meridian Display Co.'s order system, into the 29-column CSV format required by Thanks.io's postcard shipping import. Handles guest-shipping cases (orders shipped to a hotel or convention center on behalf of the recipient), normalizes address and contact data, and validates the result before save.
17. ## When to run
19. Trigger when:
21. - A new `.xlsx` file appears in `Thanksio-Post-Card-Sheet-Formatter/01 - New/` (or wherever Matt has dropped it).
22. - Matt asks to "process the postcard sheet," "clean up postcard addresses," "format for Thanks.io," or any similar phrasing.
24. ## What it does
26. Reads the XLSX (rows 1-2 are report title and blank; row 3 is headers; row 4+ is data), applies address-selection logic to choose between shipping and billing fields, applies nine cleaning rules, runs four validation checks, and writes a UTF-8 CSV named `cleaned-postcard-addresses-YYYY-MM-DD.csv` into a sibling `02 - Formatted/` directory.
28. ## How to run
30. ```bash
31. cd ~/.claude/skills/thanksio-postcard-formatter/scripts
32. python3 format.py <path-to-input.xlsx>
33. ```
35. By default the output lands in `<input parent>/../02 - Formatted/`. To archive the input after a successful run:
37. ```bash
38. python3 format.py <path-to-input.xlsx> --archive
39. ```
41. This moves the input into `02 - Formatted/99 - Archive/` once the CSV is written.
43. To override the output directory:
45. ```bash
46. python3 format.py <path-to-input.xlsx> --output-dir /some/other/path
47. ```
49. ## Input columns the skill reads
51. The Plinth order export names its columns in Plinth's snake_case style. Of the 21 columns, the skill reads these:
53. | Block | Columns |
54. |---|---|
55. | Shipping | `ship_name`, `ship_company`, `ship_street`, `ship_street2`, `ship_city`, `ship_region`, `ship_postcode`, `ship_country` |
56. | Billing | `bill_name`, `bill_company`, `bill_street`, `bill_street2`, `bill_city`, `bill_region`, `bill_postcode`, `bill_country` |
57. | Shared | `email`, `phone` |
59. The remaining columns (order reference, dates, totals) are ignored.
61. ## Address-selection logic
63. Default: shipping address. The skill switches to the **billing** address fields if either of these is true:
65. 1. `ship_name` starts with `Guest:` (case-insensitive). This indicates the order is being shipped to someone on behalf of the recipient (e.g., a hotel reservation under a guest name).
66. 2. `ship_company` contains any of: `hotel`, `suites`, `marriott`, `hilton`, `hyatt`, `sheraton`, `resort`, `motel`, `lodge`, `convention center`, `expo center`, `fairground`, `conference center`, **AND** the billing street address (`bill_street`) differs from the shipping street address (`ship_street`).
68. If the billing and shipping street addresses are identical, the skill retains shipping. That case is treated as the company's own office, not a hotel-shipping scenario.
70. When the switch fires, Name, Company, Address, Address2, City, State, Postal Code, and Country are pulled from the `bill_*` columns instead of the `ship_*` columns. Email and Phone always come from the shared `email` and `phone` columns regardless of which address block is used.
72. ## Cleaning rules
74. Applied to every row:
76. 1. **HTML entity decoding** via `html.unescape` on all text fields (e.g., `'` → `'`, `&` → `&`).
77. 2. **Name:** strip `Guest:` prefix, strip whitespace, convert ALL CAPS to Title Case (mixed case left as-is).
78. 3. **Company:** decode entities, convert ALL CAPS to Title Case if longer than three characters (short acronyms like `ABC` or `IBM` preserved), replace literal `"nan"` strings with empty.
79. 4. **Address:** decode entities, collapse doubled commas to single, strip whitespace.
80. 5. **City:** decode entities, ALL CAPS to Title Case, strip trailing commas.
81. 6. **Postal Code:** strip `.0` float-conversion suffix, zero-pad 4-digit numeric codes to 5 (recovers lost-leading-zero Northeast US ZIPs).
82. 7. **Country:** normalize `United States` / `USA` / `us` (any case) to `US`; non-US countries left as-is.
83. 8. **Phone:** strip non-digit characters, prepend `1` for 10-digit US numbers, output digits only (e.g., `12065551234`).
84. 9. **General nulls:** replace `NaN`, `None`, and literal `"nan"` strings with empty across the entire output.
86. ## Validation
88. Logged as warnings; **no rows are dropped**:
90. - Empty `Name` after cleaning.
91. - Empty `Address` after cleaning.
92. - Suspicious `Company` field: contains dimensions (e.g., `10x8ft`) or words `ONLY` / `frame` / `banner`. These usually indicate the field actually holds an order note rather than a company name. Flag for manual review; do not auto-correct.
93. - US row missing `State` or `Postal Code`.
95. All rows ship to the CSV regardless of warning count. Review the warning log before importing to Thanks.io.
97. ## Output schema
99. 29 columns in this exact order:
101. ```
102. ID, Name, Company, Address, Address2, City, State, Postal Code, Country, Email, Phone, Custom 1, Custom 2, Custom 3, Custom 4, Order Item QR Scans, Order Item Status, Order Item Estimated Delivery Date, Order Item Failure, Message, Order Id, Order Type, Order Size, Order Method, Sub Account ID, Created At, Giftcard Reedem, Giftcard Brand, Giftcard Amount
103. ```
105. Defaults filled regardless of input:
107. | Column | Default |
108. |---|---|
109. | `Order Type` | `postcard` |
110. | `Order Size` | `4x6` |
111. | `Order Method` | `cardbuilder` |
112. | `Giftcard Reedem` | `N/A` |
113. | `Giftcard Brand` | `N/A` |
114. | `Giftcard Amount` | `N/A` |
116. All other unmapped columns are left blank.
118. The `Giftcard Reedem` column header is misspelled (should be `Redeem`). This matches Thanks.io's upstream import schema verbatim. **Do not "fix" it.** The import will fail if the column is renamed.
120. ## Dependencies
122. - Python 3.10+
123. - `pandas` (for `read_excel`)
124. - `openpyxl` (pandas' XLSX engine)
125. - Standard library: `html`, `re`, `csv`, `datetime`, `pathlib`, `argparse`, `logging`
127. Install once:
129. ```bash
130. pip install pandas openpyxl
131. ```
133. ## Known caveats
135. - The hotel-keyword list is a fixed allowlist of 13 entries. A hotel-equivalent venue not matching any of those words will not trigger the address switch. To extend, edit `HOTEL_KEYWORDS` in `scripts/format.py`.
136. - ALL-CAPS → Title Case is a heuristic. A name like `JD SMITH` becomes `Jd Smith`. Review names if source data quality is uneven.
137. - The address-difference comparison is case- and whitespace-normalized but otherwise literal. Two functionally identical addresses written differently (`123 Main St` vs. `123 Main Street`) will be treated as different.
138. - No persistence or run log. To audit a prior run, retain the archived XLSX and the produced CSV side by side.
139. - The skill processes one XLSX per invocation. If multiple XLSX files exist in `01 - New/`, run the skill once per file.
141. ## When something goes wrong
143. - **`KeyError` on a `ship_...` or `bill_...` column:** the source export's column headers have drifted from the documented 21-column structure. Compare against the input columns listed under "Input columns the skill reads" above and fix the Plinth export rather than the skill.
144. - **All rows produce "empty Name" or "empty Address" warnings:** likely a row-skip miscount. Check that the input XLSX still has the title-row + blank-row + headers pattern (i.e., headers on row 3). If the source format changed, update `skiprows=2` in `format.py`.
145. - **Pandas raises on `read_excel`:** confirm `openpyxl` is installed (`pip install openpyxl`).
146. - **Import to Thanks.io fails on column names:** the output column order or naming has drifted. Compare the output CSV header against the `OUTPUT_COLUMNS` constant in `scripts/format.py`. The misspelled `Giftcard Reedem` is intentional.