KPI Reference

These are the most commonly requested business metrics. Getting them right requires careful filtering — the most frequent mistakes involve forgetting to exclude reversed contracts, counting modules as members, or assuming ACTIVE status alone means a contract is currently running. The SQL example below shows the correct pattern for active member counts. For ready-to-run queries answering everyday operator questions, see the Use Cases.

Metric	Definition	Key Tables	How to Calculate
Active Members	Customers with at least one currently active main membership.	fct_contract, dim_rate, dim_contract_property	contract_type = 'CONTRACT' AND contract_status = 'ACTIVE' AND is_reversed = FALSE AND date range check.
New Sign-ups	Contracts created in a given period.	fct_contract	Use created_date_id + contract_type = 'CONTRACT'.
Churn Rate	Cancellations ÷ active members over a period.	fct_contract	Use cancellation_date_id for timing.
ARPM	Average Revenue Per Member = membership revenue ÷ avg active members.	fct_revenue, dim_revenue_group	Filter sub_group = 'MEMBERSHIP_FEE'.
Check-in Frequency	Average gym visits per active member per period.	fct_checkin	Combine check-in counts with active member count.
Class Utilization	Actual participants ÷ max capacity.	fct_class_appointment, dim_appointment	num_participants / max_participants. Filter status = 'PLANNED'.
Lead Conversion Rate	Won leads ÷ total resolved leads.	fct_lead_lifetime	COUNT(WIN) / COUNT(WIN + LOSS). Exclude PENDING.
Payment Collection Rate	Successful bookings ÷ total bookings.	fct_payment_run	COUNT(REGULAR_BOOKING) / COUNT(*). Filter archived = FALSE.

Active Members SQL Example

SELECT 
    ou.name AS gym_name,
    COUNT(DISTINCT c.customer_id) AS active_members
FROM fct_contract c
JOIN dim_organization_unit ou ON c.organization_unit_id = ou.organization_unit_id
JOIN dim_contract_property cp ON c.contract_property_id = cp.contract_property_id
JOIN dim_rate r ON c.rate_id = r.rate_id
WHERE ou.archived = FALSE
  AND c.is_reversed = FALSE
  AND cp.contract_status = 'ACTIVE'
  AND r.contract_type = 'CONTRACT'
  AND c.start_date_id <= CAST(TO_CHAR(CURRENT_DATE, 'YYYYMMDD') AS INTEGER)
  AND (c.end_date_id IS NULL OR c.end_date_id >= CAST(TO_CHAR(CURRENT_DATE, 'YYYYMMDD') AS INTEGER))
GROUP BY ou.name;

Common mistakes: forgetting contract_type = 'CONTRACT' (inflates with modules/flat fees), forgetting is_reversed = FALSE, assuming ACTIVE alone means "currently active".