Data model: wide_service

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