Square processes hundreds of daily payments across 6 cities. Each payment needs to reach QuickBooks with the correct customer name, reference number, and amount — including refunds and processing fees. Without automation, every single transaction would need to be entered by hand.
Square provides two separate reports: a Transfer Report (which matches what hits the bank but has no customer names) and a Transaction Report (which has customer names but misses refunds and fees). Neither report alone is complete. The system merges them automatically.
Connects to Square API and pulls all payments, refunds, and fees for the previous day. Looks up each customer's full name. Runs automatically each morning.
AutomatedMerges Transfer Report data (fees, refunds) with Transaction Report data (customer names) by Payment ID. Produces Elena's daily review spreadsheet and two OFX bank feed files.
AutomatedElena opens the daily Excel report from the reports page. Checks customer names, amounts, refunds, and fees are correct before QB upload.
Manual — 2 minutesDownloads 2 OFX files from the reports page. Opens QB Banking tab. Uploads one to "Square Deposits" and one to "Square Clearing." All transactions appear in For Review.
Manual — 30 secondsIn QB Banking → For Review, Natalia matches each bank feed entry to the corresponding invoice. QB links the payment to the invoice and reduces the customer's balance.
Manual — varies by volume| QB Area | What Appears | How It Gets There |
|---|---|---|
| Sales → Invoices | One invoice per sale showing the full package amount (e.g., $4,000). Created from CRM daily sales data. | Automated via QB API |
| Banking → Square Deposits | Booking deposit payments with customer names, daily fee deductions, refunds. | OFX file upload (Natalia) |
| Banking → Square Clearing | All other payments (recurring, studio sales, office) with customer names, daily fee deductions, refunds, chargebacks. | OFX file upload (Natalia) |
This is the Excel file Elena reviews each day. One report per city, all locations except deposits.
| Date | Total Collected | Customer Name | Reference ID |
|---|---|---|---|
| 3/26/2026 | $200.00 | Samantha Knee | C3758899 |
| 3/26/2026 | $550.00 | Osioriamhe Anetekhai | C3835916 |
| 3/26/2026 | $280.00 | Brian Griffin | C3760818 |
| 3/26/2026 | $500.00 | Gabriel Bailey | C3751034 |
| 3/26/2026 | $850.00 | Qecia Nkay | C3759315 |
| 3/26/2026 | $850.00 | Qeren Nkay | C3757605 |
| 3/26/2026 | ($58.33) | Refund | |
| 3/26/2026 | ($144.08) | Square Fees |
| Phase | What | When |
|---|---|---|
| 1 | Chicago sandbox validated by Elena | Now |
| 2 | Chicago goes live in production QB | Upon Elena's approval |
| 3 | Roll out to Texas (Houston + Dallas) | After Chicago stable |
| 4 | Boston, New York, Florida, Nashville | Sequential |
OFX bank feeds instead of web connector — Web connectors create hidden invoices behind the scenes, inflating sales figures. A customer with a $4,000 package would show $4,500 in sales if a $500 payment also generated an invoice. OFX files avoid this entirely.
Two bank feeds, not one — Deposits are separated from recurring/sales payments because they map to different income accounts in Elena's chart of accounts.
Daily fee lump sums — Processing fees are consolidated into one line per day rather than one fee per transaction, keeping the bank feed clean and matching how Square reports fees to the bank.
Customer full names, never location names — The bank feed shows "Ana Rodriguez" not "Recurring Collections Ana." This is how Elena needs it for customer-level reconciliation.