Beauty Salon BI System

The Challenge

A beauty salon using the Trinks platform needed real-time visibility into business operations, professional performance tracking, and proactive client retention management to stay competitive in a crowded market.

📊 No Data Visibility

Manual spreadsheet updates were error-prone, time-consuming, and always outdated by the time they were completed.

👥 Performance Tracking

No way to accurately track individual professional revenue contribution and productivity metrics.

🔄 Client Retention

Reactive approach to client retention - only noticed churned clients after they had already left.

The Solution

Built a complete end-to-end BI pipeline that automatically extracts data from Trinks API every morning, transforms it through N8N workflows, stores it in a MySQL data warehouse with constellation schema, and delivers insights via Power BI dashboards and automated WhatsApp reports to the salon owner.

Data Warehouse Architecture

Constellation Schema Design

DIM_CLIENTES: Customer dimension with status tracking (Active/At Risk/Inactive based on visit recency)
DIM_PROFISSIONAIS: Professional dimension with performance metrics and earnings data
DIM_SERVICOS: Service catalog with pricing, categories, and duration information
FATO_TRANSACOES: Transaction fact table at header level with totals and discounts
FATO_TRANSACOES_SERVICOS: Service line items with proportional revenue attribution algorithm
FATO_TRANSACOES_PRODUTOS: Product line items with quantity and pricing

Key Features

⚡ Automated Pipeline

Runs daily at 7 AM, fetching the last 48 hours of transactions. Processes 500-800 records daily with zero manual intervention and automatic error notifications via WhatsApp.

💰 Revenue Attribution

Proprietary proportional algorithm distributes total transaction value across services based on price ratio, enabling accurate professional earnings tracking even for shared transactions.

📱 WhatsApp Reports

Owner receives automated weekly reports with key metrics, top performers, at-risk client alerts, and revenue trends directly on WhatsApp.

🎯 Client Retention

Automatic status classification system: Active (≤40 days since last visit), At Risk (41-90 days), Inactive (>90 days). Enables proactive retention campaigns.

Results & Business Impact

49.7%

Client Recurrence Rate

258

Unique Clients/Month

R$ 50.6K

Monthly Revenue Tracked

100%

Automation Rate

The dashboard provides real-time insights into revenue by day of week, top-performing services, professional productivity comparison, month-over-month growth trends, and client retention metrics. Interactive filters allow deep-dive analysis by time period, professional, and service type.

Technical Stack

Data Source

Trinks REST API - Beauty salon management platform

ETL Layer

N8N - Self-hosted workflow automation with custom JavaScript transformations

Data Warehouse

MySQL 8.0 - Constellation schema with 6 tables optimized for analytical queries

Visualization

Power BI - DirectQuery mode for real-time dashboard updates

Notifications

WhatsApp Business API - Automated reports and error alerts via Evolution API

Infrastructure

VPS Ubuntu 24 + Docker - Self-hosted for full control and data privacy