Royalty Fee Calculator: Looker Studio Build

Jebra franchise royalty reporting. Data setup, calculated fields, blend and Weekly Payments table.

1. Overview

The Royalty Fee Calculator works out the royalty, brand fund and tax that each franchise location owes its franchisor, week by week. It is built as a Looker Studio report on two datasets:

• Royalty Fee Input: a Google Sheet filled in manually, one row per location. It holds each location's fee structure.

• daily_revenue: an automatically integrated dataset (MtekReport sales data) that populates sales for every franchise location.

The build has three stages:

• Create custom fields in daily_revenue that turn raw sales into Gross Revenue and Net Revenue.

• Blend daily_revenue with Royalty Fee Input on location.

• Build the final Weekly Payments table, which calculates royalty, brand fund, tax and total payment.

Because each location has its own row in the fee sheet, every franchise can have a different fee structure without any change to the report itself.

2. Dataset 1: Royalty Fee Input

This is the configuration table. It is filled in by hand, one row per location.

Important:

• Royalty, Brand Fund and GST/HST are stored as percentage values (2% is stored as 0.02 and shown as 2%), so they can be multiplied directly by revenue.

• Location names must match the names in daily_revenue exactly. A mismatch (extra space, different spelling) means the location will not join and its fees will be blank.

3. Dataset 2: daily_revenue

This dataset is integrated directly and refreshes automatically for all locations. Three default fields feed the calculation:

Product_Name and Product_Type are used to classify each sale line into eligible and excluded revenue.

4. Custom Fields in daily_revenue

These fields are created first, in the daily_revenue data source. They start from the raw line total and strip out everything that should not count toward royalties.

4.1 Date Billed

DATETIME_ADD(DATETIME_TRUNC(Date, WEEK), INTERVAL 10 DAY)

Takes the start of the week and adds 10 days, so the billing date falls a few days after the week ends. In the current report, the week of Sep 28 to Oct 04, 2026 is billed on Oct 7, 2026.

The 10-day offset was requested by ModoYoga. Other franchises may need a different offset, so treat this field as configurable per franchise.

4.2 Total Karma Revenue

CASE
WHEN CONTAINS_TEXT(LOWER(Product_Name), "karma") THEN Total_Line_Total
ELSE 0
END

Karma products are charity sales and are excluded from royalty revenue. This field isolates their value. It appears in the report as Less Karma & Charity.

4.3 Total Gift Card

CASE
WHEN CONTAINS_TEXT(LOWER(Product_Name), "gift card")
OR CONTAINS_TEXT(Product_Type, "Gift")
THEN Total_Line_Total
ELSE 0
END

Gift card sales are not earned revenue until the card is redeemed, so they are excluded. Matching on both product name and product type catches items that are labelled inconsistently. It appears in the report as Less Gift Cards.

4.4 Third Party Revenue

CASE
WHEN CONTAINS_TEXT(Product_Name, "3P")
OR CONTAINS_TEXT(LOWER(Product_Name), "3 p")
THEN Total_Line_Total
ELSE 0
END

Third-party sales belong to outside partners rather than the franchise, so they are excluded. These products are identified by the "3P" or "3 p" naming convention.

4.5 Gift & Karma Taxes

CASE
WHEN CONTAINS_TEXT(LOWER(Product_Name), "gift card")
OR CONTAINS_TEXT(LOWER(Product_Name), "karma")
OR CONTAINS_TEXT(Product_Name, "GC")
OR CONTAINS_TEXT(Product_Name, "3P")
OR CONTAINS_TEXT(LOWER(Product_Name), "3 p")
OR CONTAINS_TEXT(Product_Type, "Gift")
THEN Total_Line_Taxes
ELSE 0
END

Captures the tax charged on the excluded products (gift cards, karma, third party). That tax leaves the calculation together with those products, so it must not be subtracted a second time.

4.6 Less Taxes

Total_Line_Taxes - Gift & Karma Taxes

The tax that remains on eligible sales. This is the only tax still to be removed from revenue.

4.7 Gross Revenue

Total_Line_Total - (Total_Gift_Cards + Total Karma Revenue + Third Party Revenue)

Total sales minus the excluded product categories. It still includes sales tax on the remaining products.

4.8 Net Revenue

Gross Revenue - Less Taxes

The base on which royalty and brand fund are calculated. It contains only eligible sales, with sales tax removed.

Calculation flow

5. The Blend

With the revenue fields ready, the two datasets are blended in Looker Studio.

A left join keeps every location from the fee sheet, even one with no sales in the period. The blend brings the fee percentages from the sheet into the same table as the revenue figures, so the fee calculations can be done in one place.

6. The Weekly Payments Table

The final table has one row per studio per week. It is made of two groups of columns: fields taken directly from daily_revenue, and fields calculated from the Google Sheet percentages.

6.1 Columns taken directly from daily_revenue

No further calculation is applied to these. They are used as they are.

Note on the "Less" columns: Gross Revenue already has gift cards, karma and third-party sales removed. Less Gift Cards and Less Karma & Charity are shown for transparency and are not deducted again. The check is Gross Revenue - Less Taxes = Net Revenue (for example, $2,032.62 - $96.80 = $1,935.82).

A footnote under the table title clarifies that "Less Charity" excludes any product name containing "Karma".

6.2 Calculated fields (using Google Sheet percentages)

Royalty, Brand Fund and GST/HST come from the Royalty Fee Input sheet and are stored as percentages.

In plain terms:

• Royalty is the royalty percentage applied to Net Revenue.

• GST/HST is charged on the royalty only. The brand fund is not taxed.

• Brand Fund is the brand fund percentage applied to Net Revenue.

• Total Payment is Royalty + GST/HST + Brand Fund.

6.3 Worked example

Winnipeg - Waverley, week of Sep 28 to Oct 04, 2026, with Royalty 2%, Brand Fund 1% and GST 5%:

Total Payment is calculated from the unrounded values, so it can differ by a cent from the sum of the rounded figures shown in the columns 

6.4 Grand total row

The table ends with a grand total row that sums each money column across all rows.

The grand total is for all studios and all weeks together. Filtering by studio or year updates it.

7. Reference: Field Summary