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 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;
Updated 9 months ago