Why Square's API can inflate revenue

By James Tram · Last updated September 9, 2026

A raw Square payments total inflates because it includes failed and canceled payments, and because UTC timestamps move evening sales onto the next calendar day. On one merchant account the gap was $52,571. Count only completed payments, and bucket each sale in the merchant's timezone.

If you pull payment data from Square's API and sum it, the number you get will be higher than the money that reached the bank. Not by a rounding error. On one merchant account we work with, the gap was $52,571.

Two separate mistakes produce it, and they compound. Both are easy to make, because in each case the API is behaving correctly and the mistake is in the assumption you bring to the payload.

Why do failed and canceled Square payments inflate revenue?

Square's Payments API returns a status field on every payment object. The values that matter are COMPLETED, FAILED, and CANCELED.

A failed payment is a real record. The customer tapped, the terminal read the card, a row was written. It has an amount_money on it. If your query selects payments for a date range and sums amount_money, that failed swipe is now revenue.

So is the canceled one. So is the retry that failed twice before succeeding, which means a single $85 service can enter your totals three times: $85 failed, $85 canceled, $85 completed, $255 counted.

This does not show up in Square's own dashboard, which filters correctly. It shows up in anything built on the raw payload. In the account above, 246 rows carried a non-completed status.

The fix is to count only completed payments. The reason it gets missed is that nothing about the response makes it feel necessary. The rows look like sales.

WHERE status = 'COMPLETED'

Why does UTC date bucketing inflate Square totals?

Square returns timestamps in UTC. Your merchant does not live in UTC.

For a business on Pacific time, the offset is seven or eight hours depending on daylight saving. Every sale rung up after 4:00 PM local lands on the following calendar day once the timestamp is read as UTC. For an evening-heavy business, that can be a third of the day's transactions moving to tomorrow.

Daily totals are wrong in both directions. Weekly totals are wrong at the boundaries. Month-end is wrong in a way that matters, because the last evening of the month falls into the next one, and the comparison you show the merchant against last month is built on two differently broken windows.

The correction is to convert the timestamp into the merchant's own timezone before extracting a date, which means the timezone has to be stored per merchant and joined in.

(t.transaction_date AT TIME ZONE COALESCE(i.timezone, 'UTC'))::date AS truth_date

What does a corrected Square payments view look like?

Both fixes belong in the same place: a single view that every downstream query reads from, rather than a filter each report remembers to apply.

The timezone is stored per integration rather than per user, because one owner can run more than one location. A connection with no timezone recorded still produces rows instead of disappearing from every report, falling back to UTC. Tips, tax, refunds, and processing fees are summed into their own columns rather than folded into one revenue figure, because the gap between gross sales and the bank deposit is made of exactly those pieces, and you cannot explain the gap if you have already collapsed it. Only completed Square payments are included.

CREATE VIEW public.v_register_truth
  WITH (security_invoker = true)
AS
SELECT
  t.user_id,
  t.integration_id,
  (t.transaction_date AT TIME ZONE COALESCE(i.timezone, 'UTC'))::date AS truth_date,
  COUNT(*) AS transaction_count,
  COALESCE(SUM(t.gross_amount), 0::numeric) AS gross_sales,
  COALESCE(SUM(t.tip_amount), 0::numeric) AS tips_collected,
  COALESCE(SUM(t.tax_amount), 0::numeric) AS tax_collected,
  COALESCE(SUM(t.refund_amount), 0::numeric) AS refunds,
  COALESCE(SUM(t.processing_fee), 0::numeric) AS processing_fees,
  COALESCE(SUM(t.total_collected), 0::numeric) AS total_collected,
  COALESCE(SUM(t.gross_amount - t.refund_amount), 0::numeric) AS net_sales
FROM public.transactions t
LEFT JOIN public.integrations i ON i.id = t.integration_id
WHERE t.platform = 'square'
  AND t.status = 'COMPLETED'
GROUP BY
  t.user_id,
  t.integration_id,
  (t.transaction_date AT TIME ZONE COALESCE(i.timezone, 'UTC'))::date;

That is the real view, not a simplified one.

How do you find inflated Square revenue?

You will not find it by looking at the numbers. Inflated revenue looks like good revenue. It looks like a strong month.

You find it by reconciling against deposits. Take the gross sales your system reports for a period, subtract processing fees, subtract refunds, account for the payout timing offset, and compare against what actually landed in the bank. If that arithmetic does not close, the difference is telling you something specific about where the error lives.

A gap that scales with volume is usually a filtering problem. A gap that appears and disappears at period boundaries is usually a bucketing problem. A gap that does both is both, which is what $52,571 turned out to be.

Why does this happen if Square's API is correct?

Neither of these is a flaw in Square. The API is returning exactly what it should: every payment record, timestamped in a universal standard. The failure happens at the interpretation layer, where a payload gets treated as a ledger.

Any tool summing raw payment data inherits this. Spreadsheets built off CSV exports inherit it. Internal dashboards inherit it. We inherited it, in a view we wrote and shipped, until reconciliation against deposits refused to close and we went looking for why.

The number a business runs on should be the number that reached the bank. Every step between the terminal and that figure is a place for the two to drift apart.