Data model: wide_liability

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 name	Data type	Mode	Description	Example
liability_transaction_key	varchar	Required	Unique identifier for a liability transaction.	7709dcae45a7ed91b9d9215a201b7067
liability_type	varchar	Required	Type of liability: • Donation • Gift Card • House Account • Store Credit.	Gift Card
liability_type_id	varchar	'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_id	varchar	Required	Unique identifier for a unit of liability: • Gift card number • Store credit number • Customer_id for house account • 'N/A' for Donation.	123456
order_id	int4	Nullable	Unique identifier for an order. If transaction is not related to an order, then it's NULL.	25
payment_id	int4	Nullable	Unique identifier for a payment. If transaction is not related to a payment, then it's NULL.	12
created_at	timestamp	Required	Transaction creation date and time in UTC format.	2022-10-12 07:13:31
updated_at	timestamp	Required	Transaction last update date and time in UTC format.	2022-10-14 09:14:23
amount	numeric	Required	Amount of the transaction. Positive for increase in liability, negative for decrease in liability.	23.4
action_type	varchar	Required	A type of operation that created a record: • Created • Pay • Tips • Amount adjust • etc.	Pay
note	varchar	'N/A'	A note explaining house account adjustment, 'N/A' for other liability types.	N/A
establishment_id	int4	Nullable	Establishment unique Key. If a Gift Card was imported from a .xslx file, then it's NULL.	1
establishment_name	varchar	Nullable	Establishment Name.	Company - 1
establishment_city	varchar	Nullable	Establishment City.	San Francisco
establishment_state	varchar	Nullable	Establishment State.	CA
establishment_state	varchar	Nullable	Establishment Country.	US
company_name	varchar	Nullable	Company name.	Company
company_url	varchar	Nullable	Company url address.	company.revelup.com
establishment_latitude	float	Nullable	Establishment Latitude.	37.7979623
establishment_longitude	float	Nullable	Establishment Longitude.	-152.0074200
establishment_post_code	float	Nullable	Establishment Post Code.	94100
is_establishment_active	bool	Nullable	Indicates if establishment is active.	TRUE
timezone	varchar	Nullable	The configured timezone of the establishment.	US/Eastern
establishment_division	varchar	Nullable	Establishment division. If establishment does not belong to a division, then value is N/A.	Division - 1
establishment_sub_division	varchar	Nullable	Establishment sub-division. If establishment does not belong to a sub-division, then value is N/A.	Sub-division - 1
created_by_employee_first_name	varchar	'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_name	varchar	'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_active	bool	Nullable	Identification of whether employee is active. If transaction wasn’t created by employee, then value is NULL.	TRUE
last_updated_by_employee_first_name	varchar	'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_name	varchar	'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_active	bool	Nullable	Identification 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;