Data model: wide_service_fee


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?

Data Objects

Column nameData typeModeDescriptionExample
service_fee_idint4RequiredUnique identifier for an applied service fee.123
service_fee_levelvarcharRequiredA service fee can be applied to:
• Order
• Item
order_idint4NullableAn identifier of an order the service fee was applied to. If the service_fee_level is Item, then it's Null.22
item_idint4NullableAn identifier of an item the service fee was applied to. If the service_fee_level is Order, then it's Null.Null
service_fee_namevarcharRequiredService fee name.Delivery Fee
total_amountnumericRequiredService fee amount, monetary value.2.5
taxedboolRequiredAn indicator if service fee is taxed.FALSE
tax_amountnumericRequiredTax amount, monetary value.0
applied_tovarcharRequiredThe setting which indicates how the fee is calculated. Possible values:
• Subtotal;
• Post-tax;
• Pre-discount.
is_pass_throughboolboolIndicates if a fee is a pass through fee.TRUE
pass_through_typevarcharN/AType of the pass through fee:
• Third party;
• Employee;
• Other;
• N/A - if a fee is not pass through.
Third party
created_attimestampRequiredService fee applied date and time in UTC format.2022-11-08 16:37:41
updated_attimestampRequiredService fee updated date and time in UTC format.2022-11-08 16:37:41
establishment_idint4NullableEstablishment unique Key. If a service fee is manually entered on POS, 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
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

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,
                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,
                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