Build common business reports
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 = 0on 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
- 2. Revenue by service category
- 3. Appointment volume and utilization
- 4. Employee performance
- 5. Membership lifecycle
- 6. Guest retention and personas
- 7. Product inventory
- 8. Gift card sales
- 9. Campaign effectiveness
- 10. Daily traffic summary
- Choosing between star schema and consolidated tables
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.