Email QuickBooks profit and loss to your leadership team
The profit and loss statement in the CEO's inbox every Monday, with the balance sheet behind it. QuickBooks goes into your data warehouse first, where each period is kept, and only then reaches email.
%20scale(1.25)%22%20fill%3D%22%23fff%22%20d%3D%22M12%200A12%2012%200%200%200%200%2012a12%2012%200%200%200%2012%2012%2012%2012%200%200%200%2012-12A12%2012%200%200%200%2012%200zm.642%204.1335c.9554%200%201.7296.776%201.7296%201.7332v9.0667h1.6c1.614%200%202.9275-1.3156%202.9275-2.933%200-1.6173-1.3136-2.9333-2.9276-2.9333h-.6654V7.3334h.6654c2.5722%200%204.6577%202.0897%204.6577%204.667%200%202.5774-2.0855%204.6666-4.6577%204.6666H12.642zM7.9837%207.333h3.3291v12.533c-.9555%200-1.73-.7759-1.73-1.7332V9.0662H7.9837c-1.6146%200-2.9277%201.316-2.9277%202.9334%200%201.6175%201.3131%202.9333%202.9277%202.9333h.6654v1.7332h-.6654c-2.5725%200-4.6577-2.0892-4.6577-4.6665%200-2.5771%202.0852-4.6666%204.6577-4.6666Z%22%2F%3E%0A%3C%2Fsvg%3E%0A)
QuickBooksConnectedA/P Aging Summar…A/R Aging Summar…AccountsBalance Sheet re…
EmailRealmReportReportDaily 09:00QuickBooks Endpoints
26 QuickBooks endpoints you can import. Each lands in its own table in your data warehouse.
A/P Aging Summary report
A/P Aging Summary as of report_date (window end): one row per vendor with aging buckets.
11 fieldsTime seriesA/R Aging Summary report
A/R Aging Summary as of report_date (window end): one row per customer with Current, 1-30, 31-60, 61-90, 91+ and Total columns.
11 fieldsTime seriesAccounts
Chart of accounts, active and inactive.
20 fieldsBalance Sheet report
Balance Sheet as of the run window end (Assets, Liabilities, Equity by account).
12 fieldsTime seriesBill payments
Payments against vendor bills; joins to Bill through Line[].LinkedTxn.
23 fieldsBills
Vendor bills (payables).
25 fieldsCash Flow report
Statement of Cash Flows for the run window.
12 fieldsTime seriesCompany info
One row per company: names, country, fiscal-year start.
15 fieldsCredit memos
Credits issued to customers (reduce receivables; negative revenue in reconciliations).
23 fieldsCustomers
Customers, including inactive ones and sub-customers, with open balance and a flattened billing address.
28 fieldsDeposits
Bank deposits, including the ones that group undeposited customer payments.
21 fieldsGeneral Ledger Detail report
General Ledger: every posting line per account for the run window, with opening balance and totals.
12 fieldsTime seriesInvoice changes (CDC, incl. deletes)
Change data capture for Invoice: every invoice created, updated or deleted since the run window start, full payload, deleted ones with status = Deleted.
9 fieldsTime seriesInvoices
Invoices with header amounts, customer and Line[] as JSON (SalesItemLineDetail.ItemRef, Qty, UnitPrice, Amount).
36 fieldsItems
Products and services with price, purchase cost and the three account links.
25 fieldsJournal entries
Manual and adjusting journal entries; the postings the transaction tables miss.
15 fieldsPayments
Customer payments.
21 fieldsPreferences
One row per company: home currency, multi-currency flag, fiscal-year and tracking settings.
17 fieldsProfit and Loss report
Profit and Loss per run window, one column per month (summarize_column_by=Month), rows nested by account group.
12 fieldsTime seriesPurchases
Expenses paid directly (cash, check, card) without a bill.
26 fieldsRefund receipts
Money refunded to customers.
26 fieldsSales by Customer report
Sales by Customer Summary for the run window.
12 fieldsTime seriesSales receipts
Sales paid at the time of sale (no receivable).
27 fieldsTransfers
Money moved between balance-sheet accounts.
16 fieldsVendor Expenses report
Expenses by Vendor for the run window.
12 fieldsTime seriesVendors
Vendors, including inactive ones, with open payable balance.
19 fields
Why QuickBooks does not email the statement on its own
QuickBooks can email a report, but not a period you can compare, not beside the other two statements and not with a short reading under it. Each snapshot is stored in the warehouse with its basis, and the message is built from there.
- A statement mailed once is a PDF nobody can query later
- Leadership reads result and position together, not one in isolation
- The data analyst controls the reading under the table
QuickBooksPeriod snapshotsEmail
Data warehouseJoined by idStep 1: Load QuickBooks into your data warehouse first
Create a Data Mart with QuickBooks as the source and choose where the data lands. The QuickBooks connector is a custom connector built in OWOX Data Marts, and the data goes into any popular data warehouse.
- Profit and Loss is one of 8 report snapshots among the 26 endpoints
- Authorize QuickBooks Online with OAuth and give the company (realm) id
- Open the QuickBooks connector for your data warehouse below
QuickBooksData MartGoogle BigQuery
AWS Athena
Snowflake
AWS Redshift
Databricksquickbooks.report_profit_and_…Step 2: Link QuickBooks profit and loss to the balance sheet
In your data warehouse the model joins Profit and Loss and Balance Sheet on realm_id, so the result can be read against the position at the end of the same period.
- Profit and Loss and Balance Sheet share the company and the period
- summarize_columns_by says whether columns are months or quarters
- Last month's column sits beside this month's
Step 3: Email QuickBooks reports to your recipients
Add an email destination with the recipients' addresses, separated by commas, semicolons or new lines, and attach a report of the QuickBooks Data Mart. The subject and the body are Markdown, and a table placeholder drops the rows into the body.
- The Profit and Loss report table carries start_period, end_period, summarize_columns_by, currency
- Allow-list the sending address insights@e.owox.com so the message reaches inboxes
- Run it once with Create & Run report to see the email before the schedule starts
EmailWeeklyMonthly profit and lossstart_periodend_periodsummarize_colum…currencyStep 4: Schedule the QuickBooks email and add a short analysis
Add a trigger in the report's Automate Report Runs section or on the Data Mart's Triggers tab, for example every weekday morning or Monday and Wednesday at a set hour in your time zone. Write the reading under the table.
- The reading is drawn from the report's fields and says only what the data shows
- Send always, only when the result is not empty, or only when it is empty
- Attach a pre-built Insight instead of plain text when you want it written for you
EmailMonthly profit and lossstart_periodend_periodsummarize_colum…currencyAnalysisreport_rows lists income and expense in the order QuickBooks gavesummarize_columns_by says how the columns divide the periodstart_period and end_period show which dates the table coversStep 5: Reuse QuickBooks monthly statements
The Data Mart behind your email report also works in OWOX, where your assistant for data can read it. Ask about any report, then get it in Google Sheets or Excel in one click.
- Ask how this month's result compares with the last
- A question worth repeating becomes the next scheduled email
Google SheetsOpen
ExcelOpen
- 0.41.0Dates from Google Sheets arrive as dates, not text
- 0.41.0Rotating a connector key is one change: Sources share reusable Credentials
- 0.40.0Connecting a plan from Google Sheets takes fewer guesses
- 0.39.0Reload a year of history in one backfill, not twelve
- 0.39.0Long Facebook Ads imports no longer fail on a corrupted response
Why OWOX for QuickBooks in email
A QuickBooks email shows one statement at a time. OWOX sends result and position together, with history and a reading, from your own data warehouse.
- Result and position, one email. Profit and Loss and Balance Sheet are joined by company and period.
- You own the data. QuickBooks lands in your own data warehouse, in tables you control.
- History included. Every snapshot is kept with its basis, so a period can be compared later.
- One place to fix. When QuickBooks's API changes, you publish one new connector version, so the schedule keeps running.
You are booked. The invitation is on its way to , with the calendar invite and the meeting link.