payments

The payments schema has one row per payment received and per refund paid out, on the day the money moved. Use it for money in and out, by payment method, card brand, gateway and how each payment was taken.

revenue counts what was sold, on the day it is used. payments counts the money, on the day it came in or went back out, so a deposit paid in March for a stay in July is March money here and July revenue there.

Use cases

• Money in and out. Show gross_payments, refunded_payments and net_payments by month.
• Payment methods. Split by payment_method, card_brand and wallet.
• How guests paid. Compare online checkout, payment links, the terminal and guarantee cards with paid_via.
• Cash and transfers. Group payments staff recorded by external_payment_method.
• Order coverage. Set orders_with_payments against payments to see how many orders paid in parts.

Examples

Money in and out, month by month

FROM payments
  SHOW gross_payments, refunded_payments, net_payments
  DURING this_year
  TIMESERIES month
  WITH TOTALS

Payments by method, brand and wallet

FROM payments
  SHOW gross_payments, payments
  WHERE payment_kind = 'payment'
  DURING last_quarter
  GROUP BY payment_method, card_brand, wallet
  WITH TOTALS

How payments were taken

FROM payments
  SHOW net_payments, payments
  DURING last_month
  GROUP BY paid_via

Cash and other recorded payments

FROM payments
  SHOW net_payments
  WHERE payment_gateway = 'external'
  DURING last_month
  GROUP BY external_payment_method

Leave out payments imported from Mews

FROM payments
  SHOW net_payments
  WHERE external_payment_method NOT STARTS WITH 'MEWS Import'
  DURING last_year
  GROUP BY payment_gateway

What it counts

Every payment received and every refund paid out, on the day the money moved: payments through SERVOS (Stripe or Nets), by card, MobilePay, Vipps and so on, and payments staff recorded, such as cash. Money an OTA collected never reaches SERVOS, nor do refunds made in the Stripe dashboard. Hotels moved from Mews also have one recorded payment per imported order, for its total, on its booking day, OTA money included; WHERE external_payment_method NOT STARTS WITH 'MEWS Import' leaves them out.

Dates

Rows are dated by payment_date. Without DURING or SINCE, a query covers this_month. AS OF doesn't work on payments, because it keeps no history.

Metrics

Metrics you can use in FROM payments.

• gross_payments · Money

  Money received, before refunds.

• refunded_payments · Money

  Money paid back to customers, on the day the refund went through. Refunds made directly in the Stripe dashboard aren't included.

• net_payments · Money

  Money received less refunds paid out.

• payments · Number

  Payments received, each counted once. A refund isn't a payment.

• refunds · Number

  Refunds paid out. A refund paid back to two payments counts once for each payment.

• orders_with_payments · Number

  Orders with a payment in the result, each counted once per row. Refunds don't count an order.

  Doesn't add up over time, so WITH CUMULATIVE can't take it.

Dimensions

Dimensions you can group and filter by in FROM payments.

• channel · Text

  Where the booking came from: an OTA such as Booking.com, POS, B2B, or Direct.

• sales_channel · Text

  The kind of channel a booking was sold on: direct, ota, pos or b2b.

  Values: direct, ota, pos, b2b.

• payment_kind · Text

  Whether the money came in (payment) or went back out (refund).

  Values: payment, refund.

• payment_gateway · Text

  Who handled the money: stripe or nets for payments taken through SERVOS, external for payments staff recorded, payments imported from Mews and refunds handed back outside SERVOS.

  Values: stripe, nets, external.

• payment_method · Text

  How the customer paid: card (also in person, or through Apple Pay or Google Pay), mobilepay, vipps, klarna and so on. Empty for payments staff recorded; see external_payment_method.

• card_brand · Text

  The card scheme of a card payment: visa, mastercard, amex and so on.

• wallet · Text

  The digital wallet a card was paid through, such as apple_pay or google_pay. Empty when no wallet was used.

• paid_via · Text

  How the payment was taken: online at checkout, payment_link sent by email, terminal at the desk, guarantee_card when SERVOS charged a card an OTA passed on (virtual cards included), or recorded by staff or imported from Mews. A refund carries the payment it pays back; a refund handed back outside SERVOS is recorded.

  Values: online, payment_link, terminal, guarantee_card, recorded.

• external_payment_method · Text

  The name of the payment method staff chose when recording a payment, such as Cash or Bank transfer. Payments imported from Mews read 'MEWS Import - ' and the channel. Empty for payments through SERVOS.