Data model: wide_service_fee
Description
wide_service_fee data model has information required to do analytics on an applied service fee level. It helps distinguish between fee levels and separate pass through fees that are treated as liabilities from regular fees that are included into sales.
Revel Data Connector users using this data model can answer questions like:
• What are my service fee trends over the selected time period?
• How is my service fee amount compared to previous time periods?
• What is the breakdown of service fee amount by service fee type?
• What is the breakdown of service fee amount by service fee name?
• What is the most used service fee?
• What amount of pass through fees we have to pass to delivery drivers, employees?
etc.
Data Objects
Column name | Data type | Mode | Description | Example |
---|---|---|---|---|
service_fee_id | int4 | Required | Unique identifier for an applied service fee. | 123 |
service_fee_level | varchar | Required | A service fee can be applied to: • Order • Item | Order |
order_id | int4 | Nullable | An identifier of an order the service fee was applied to. If the service_fee_level is Item, then it's Null. | 22 |
item_id | int4 | Nullable | An identifier of an item the service fee was applied to. If the service_fee_level is Order, then it's Null. | Null |
service_fee_name | varchar | Required | Service fee name. | Delivery Fee |
total_amount | numeric | Required | Service fee amount, monetary value. | 2.5 |
taxed | bool | Required | An indicator if service fee is taxed. | FALSE |
tax_amount | numeric | Required | Tax amount, monetary value. | 0 |
applied_to | varchar | Required | The setting which indicates how the fee is calculated. Possible values: • Subtotal; • Post-tax; • Pre-discount. | Subtotal |
is_pass_through | bool | bool | Indicates if a fee is a pass through fee. | TRUE |
pass_through_type | varchar | N/A | Type of the pass through fee: • Third party; • Employee; • Other; • N/A - if a fee is not pass through. | Third party |
created_at | timestamp | Required | Service fee applied date and time in UTC format. | 2022-11-08 16:37:41 |
updated_at | timestamp | Required | Service fee updated date and time in UTC format. | 2022-11-08 16:37:41 |
establishment_id | int4 | Nullable | Establishment unique Key. If a service fee is manually entered on POS, 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 |
Content details
- Service Fees allow either a percentage charge or flat fee to be added to an order and can be added manually or automatically applied, based on certain criteria. These can be used for things like delivery charges, bag fees, etc. See more here
- Pass Through Service Fees allow you to seamlessly collect money and pass it on to a third party, without it affecting your business, taxes, or profit in any way. Pass through service fees report as Liabilities instead of Sales. See more here
Sample SQL queries
--Show pass through and regular fee amount for the current year
SELECT CASE WHEN is_pass_through = 'True' THEN 'Pass through'
ELSE 'Regular'
END AS fee_type,
pass_through_type,
SUM(total_amount) AS fee_amount
FROM wide_service_fee
WHERE convert_timezone(timezone, created_at)::date >= '2023-01-01'
GROUP BY fee_type, pass_through_type
--Show pass through fee amount per establishment for the current year
SELECT establishment_id,
establishment_name,
COUNT(*) AS fee_count,
SUM(total_amount) AS fee_amount
FROM wide_service_fee
WHERE is_pass_through = 'True'
AND convert_timezone(timezone, created_at)::date >= '2023-01-01'
GROUP BY establishment_id, establishment_name
ORDER BY fee_amount DESC
Updated 9 months ago