Data model: wide_liability_transaction

Description

wide_liability_transaction provides an overview for the liabilities data available to Revel Data Connector users. Liability table has a grain of a single transaction: e.g. creation of a gift card, payment for an order with a house account, etc.

Revel Data Connector users using this data model can answer questions like:

  • What are the liabilities amounts and how they affect Net to Account For metric? I.e. transactions that result in cash flow.
  • What is the used/remaining amount of liabilities in the context of liability type, establishment, liability account, etc.? I.e. all the transaction types including adjustments.
  • How often do customers use their gift cards and in which establishments?
  • What are the trends for Donations, Gift Cards, House accounts, Store credits?
  • etc.

Data Objects

Column nameData typeModeDescriptionExample
liability_transaction_keyvarcharRequiredUnique identifier for a liability transaction.7709dcae45a7ed91b9d9215a201b7067
liability_typevarcharRequiredType of liability:
• Donation
• Gift Card
• House Account
• Store Credit.
Gift Card
liability_type_idvarchar'N/A'Type of an identifier for a unit of liability:
• Gift card number
• Store credit number
• Customer_id for house account
• 'N/A' for Donation.
Gift card number
liability_idvarcharRequiredUnique identifier for a unit of liability:
• Gift card number
• Store credit number
• Customer_id for house account
• 'N/A' for Donation.
123456
order_idint4NullableUnique identifier for an order. If transaction is not related to an order, then it's NULL.25
payment_idint4NullableUnique identifier for a payment. If transaction is not related to a payment, then it's NULL.12
created_attimestampRequiredTransaction creation date and time in UTC format.2022-10-12 07:13:31
updated_attimestampRequiredTransaction last update date and time in UTC format.2022-10-14 09:14:23
amountnumericRequiredAmount of the transaction. Positive for increase in liability, negative for decrease in liability.23.4
action_typevarcharRequiredA type of operation that created a record:
• Created
• Pay
• Tips
• Amount adjust
• etc.
Pay
notevarchar'N/A'A note explaining house account adjustment, 'N/A' for other liability types.N/A
establishment_idint4NullableEstablishment unique Key. If a Gift Card was imported from a .xslx file, then it's NULL.1
establishment_namevarcharNullableEstablishment Name.Company - 1
establishment_cityvarcharNullableEstablishment City.San Francisco
establishment_statevarcharNullableEstablishment State.CA
establishment_statevarcharNullableEstablishment Country.US
company_namevarcharNullableCompany name.Company
company_urlvarcharNullableCompany url address.company.revelup.com
establishment_latitudefloatNullableEstablishment Latitude.37.7979623
establishment_longitudefloatNullableEstablishment Longitude.-152.0074200
establishment_post_codefloatNullableEstablishment Post Code.94100
is_establishment_activeboolNullableIndicates if establishment is active.TRUE
timezonevarcharNullableThe configured timezone of the establishment.US/Eastern
establishment_divisionvarcharNullableEstablishment division. If establishment does not belong to a division, then value is N/A.Division - 1
establishment_sub_divisionvarcharNullableEstablishment sub-division. If establishment does not belong to a sub-division, then value is N/A.Sub-division - 1
created_by_employee_first_namevarchar'N/A'First name of employee who created a transaction. If it wasn’t created by employee, then value is N/A.John
created_by_employee_last_namevarchar'N/A'Last name of employee who created a transaction. If it wasn’t created by employee, then value is N/A.Doe
created_by_employee_is_activeboolNullableIdentification of whether employee is active. If transaction wasn’t created by employee, then value is NULL.TRUE
last_updated_by_employee_first_namevarchar'N/A'First name of last employee who updated a transaction. If transaction wasn’t updated by employee, then value is N/A.John
last_updated_by_employee_last_namevarchar'N/A'Last name of last employee who updated a transaction. If transaction wasn’t updated by employee, then value is N/A.Doe
last_updated_by_employee_is_activeboolNullableIdentification of whether employee is active. If transaction wasn’t updated by employee, then value is NULL.TRUE

Content details

  • Donations: Donation is a type of liability which represent an amount of money given to a charity. Donations can be enabled on the POS. See more here
  • Gift cards: A gift card is recorded as a liability when it’s sold to a customer and as a revenue when a customer redeems value from a gift card. See more here
  • House accounts: A house account is recorded as decrease in liability when a customer pays for an order with house account and increase in liability when customer covers house account balance with a payment. See more here
  • Store credits: A store credit is way to issue a refund on POS. See more here

Sample SQL queries

-- Calcluculate the balace of Gift Cards on a specific date. This is valuable for reconciliation and planning.
SELECT
       liability_id AS gift_card_number,
       sum(amount) AS balance
FROM
  (SELECT
          convert_timezone(timezone, created_at)::date,
          liability_type_id,
          liability_id,
          action_type,
          amount
   FROM wide_liability_transaction
   WHERE liability_type = 'Gift Card'
     AND convert_timezone(timezone, created_at)::date < '2023-01-01'
  ) t
GROUP BY 1
ORDER BY 1;