ZenotiHelp

Build common business reports

ZTZenoti Docs Team Updated April 10, 2026 4 min readReference

This article provides ready-to-use SQL query recipes for common business reports against your Zenoti Analytics — Amazon S3 data. Each recipe is provided in two forms: a star schema version that joins fact and dimension tables, and a note about the equivalent consolidated table if one is available.

Before you begin

  • Your organization must have Analytics — Amazon S3 enabled and data loaded into your warehouse.
  • You need a SQL-compatible analytics tool or query engine connected to your data warehouse.
  • The initial full data load (GroupID 1) must be complete.

Reminder: Always filter void = 0 on fact tables to return only current, active records. Consolidated tables also inherit the void column from their source fact table.


On this page


1. Revenue by center

Description: Total revenue across all centers, ranked by highest earner. Useful for regional performance reviews and benchmarking.

Tables: Bi_FactInvoiceItem, Bi_DimCenter

SELECT
  d.centername,
  SUM(f.finalsaleprice)   AS total_revenue,
  COUNT(DISTINCT f.invoiceid) AS invoice_count
FROM Bi_FactInvoiceItem f
JOIN Bi_DimCenter d ON f.centerwid = d.centerwid
WHERE f.void = 0
GROUP BY d.centername
ORDER BY total_revenue DESC;

Consolidated alternative: Use Sales_Fact_Consolidated. No joins required — centername is included directly.

2. Revenue by service category

Description: Breaks down revenue by service and category. Useful for identifying top-performing service lines.

Tables: Bi_FactInvoiceItem, Bi_DimService, Bi_DimCategoryServices

SELECT
  cs.categoryname,
  s.servicename,
  SUM(f.finalsaleprice) AS total_revenue
FROM Bi_FactInvoiceItem f
JOIN Bi_DimService s ON f.servicewid = s.servicewid
JOIN Bi_DimCategoryServices cs ON s.categorywid = cs.categorywid
WHERE f.void = 0
GROUP BY cs.categoryname, s.servicename
ORDER BY cs.categoryname, total_revenue DESC;

Consolidated alternative: Use Sales_Fact_Consolidated and group by the categoryname and servicename columns included in that table.

3. Appointment volume and utilization

Description: Counts appointments by center and date. Use to track booking trends, peak days, and provider utilization over time.

Tables: Bi_FactAppointment, Bi_DimCenter, Bi_DimDate

SELECT
  d.centername,
  dt.fulldate,
  COUNT(f.appointmentwid)                              AS total_appointments,
  SUM(CASE WHEN f.appointmentstatus = 'Completed'
           THEN 1 ELSE 0 END)                          AS completed,
  SUM(CASE WHEN f.appointmentstatus = 'No Show'
           THEN 1 ELSE 0 END)                          AS no_shows
FROM Bi_FactAppointment f
JOIN Bi_DimCenter d  ON f.centerwid = d.centerwid
JOIN Bi_DimDate   dt ON f.appointmentdatewid = dt.datewid
WHERE f.void = 0
GROUP BY d.centername, dt.fulldate
ORDER BY dt.fulldate DESC, d.centername;

Consolidated alternative: Use appointment_fact_consolidated for a pre-joined view that includes center and date attributes.

4. Employee performance

Description: Summarizes commission earnings per employee. Use for performance reviews, incentive planning, and payroll reconciliation.

Tables: Bi_FactEmployeeCommission, Bi_DimEmployee

SELECT
  e.employeefirstname,
  e.employeelastname,
  e.jobtitle,
  SUM(f.commissionamount) AS total_commission,
  COUNT(f.commissionwid)  AS commission_records
FROM Bi_FactEmployeeCommission f
JOIN Bi_DimEmployee e ON f.employeewid = e.employeewid
WHERE f.void = 0
GROUP BY e.employeefirstname, e.employeelastname, e.jobtitle
ORDER BY total_commission DESC;

Consolidated alternative: Use empcommission_fact_consolidated. Employee name and job title are included directly.

5. Membership lifecycle

Description: Tracks membership status transitions — activations, expirations, cancellations. Useful for monitoring membership health and churn.

Tables: Bi_FactMembershipStatusLog, Bi_DimMembership, Bi_DimMembershipStatus

SELECT
  m.membershipname,
  ms.statusname,
  COUNT(f.membershipstatuslogwid) AS status_count
FROM Bi_FactMembershipStatusLog f
JOIN Bi_DimMembership       m  ON f.membershipwid = m.membershipwid
JOIN Bi_DimMembershipStatus ms ON f.membershipstatuswid = ms.membershipstatuswid
WHERE f.void = 0
GROUP BY m.membershipname, ms.statusname
ORDER BY m.membershipname, ms.statusname;

Consolidated alternative: Use membership_fact_consolidated to access membership name and status without joining.

6. Guest retention and personas

Description: Classifies guests into behavior segments. Use for targeted marketing and retention campaigns.

Tables: Bi_FactGuestPersonas, Bi_DimUser

SELECT
  u.firstname,
  u.lastname,
  u.email,
  f.personaname,
  f.lasttransactiondate
FROM Bi_FactGuestPersonas f
JOIN Bi_DimUser u ON f.userwid = u.userwid
WHERE f.void = 0
ORDER BY f.personaname, f.lasttransactiondate DESC;

Note: There is no consolidated table for guest personas. Use the star schema join above.

7. Product inventory

Description: Current inventory levels by product and center. Use for stock management, reorder planning, and shrinkage reporting.

Tables: Bi_FactInventory, Bi_DimProduct

SELECT
  p.productname,
  p.productcategory,
  SUM(f.quantityonhand) AS quantity_on_hand,
  SUM(f.quantitysold)   AS quantity_sold
FROM Bi_FactInventory f
JOIN Bi_DimProduct p ON f.productwid = p.productwid
WHERE f.void = 0
GROUP BY p.productname, p.productcategory
ORDER BY p.productcategory, p.productname;

Consolidated alternative: Use inventory_fact_consolidated. Product name and category are included directly.

8. Gift card sales

Description: Tracks gift card issuance, redemptions, and outstanding balances. Use for liability reporting and promotion analysis.

Tables: Bi_FactGiftCards, Bi_DimGiftCard

SELECT
  gc.giftcardcode,
  gc.issueddate,
  SUM(f.issuedamount)   AS issued_amount,
  SUM(f.redeemedamount) AS redeemed_amount,
  SUM(f.balanceamount)  AS remaining_balance
FROM Bi_FactGiftCards f
JOIN Bi_DimGiftCard gc ON f.giftcardwid = gc.giftcardwid
WHERE f.void = 0
GROUP BY gc.giftcardcode, gc.issueddate
ORDER BY gc.issueddate DESC;

Consolidated alternative: Use giftcards_fact_consolidated. Gift card attributes are pre-joined.

9. Campaign effectiveness

Description: Measures reach and response for marketing campaigns. Use to evaluate ROI and prioritize future campaigns.

Tables: Bi_FactCampaignSummary, Bi_DimCampaign

SELECT
  c.campaignname,
  c.campaigntype,
  SUM(f.sent)        AS total_sent,
  SUM(f.opened)      AS total_opened,
  SUM(f.clicked)     AS total_clicked,
  SUM(f.converted)   AS total_converted,
  CASE
    WHEN SUM(f.sent) > 0
    THEN ROUND(100.0 * SUM(f.converted) / SUM(f.sent), 2)
    ELSE 0
  END AS conversion_rate_pct
FROM Bi_FactCampaignSummary f
JOIN Bi_DimCampaign c ON f.campaignwid = c.campaignwid
WHERE f.void = 0
GROUP BY c.campaignname, c.campaigntype
ORDER BY conversion_rate_pct DESC;

Consolidated alternative: Use campaign_fact_consolidated. Campaign attributes are included directly.

10. Daily traffic summary

Description: Summarizes daily footfall and transaction volume by center. Use for capacity planning, staffing, and trend analysis.

Consolidated table: Daily_Traffic_Consolidated

This report is available only via the consolidated table, which aggregates daily traffic metrics across appointments, walk-ins, and transactions.

SELECT
  trafficdate,
  centername,
  totalvisits,
  totalappointments,
  totalwalkins,
  totaltransactions
FROM Daily_Traffic_Consolidated
ORDER BY trafficdate DESC, centername;

Choosing between star schema and consolidated tables

Scenario Recommended approach
You need maximum flexibility and custom filtering Star schema joins
You want the simplest queries with fewer joins Consolidated tables
You need to combine metrics from multiple subject areas Star schema (join across multiple fact tables)
You are prototyping or doing ad hoc analysis Consolidated tables for speed

Both approaches use the same underlying data. Consolidated tables are pre-joined for convenience — they do not provide additional data beyond what is available in the corresponding fact and dimension tables.

Was this article helpful?
Your feedback helps us improve our documentation.