BI & data
Management dashboard
A business intelligence system on top of the CRM: panels for each area and lists for consultants, with real-time data and comparisons with previous years.
Tools
- SQL (Trino)
- Apache Superset
- Bitrix24
Illustrated with fictitious data. Real project. Screenshots and examples use fictitious data: no client data or business figures are shown.
Situation
Management had no consolidated view of the client portfolio, workload or profitability. Everything came from a tracking spreadsheet and from lists prepared by hand every month: per consultant, new and lost clients, extras per department and hours spent on shared tasks.
Solution
I built a business intelligence system on top of the CRM, with three SQL datasets read in real time (companies, tasks and deals) and panels for each area: overview, accounting and tax, payroll and administration. I added filtered lists so each consultant can prepare their monthly allocation, and CSS templates keep the reports in the format management already used.
Result
Management gets instantly what it used to request as reports, and can compare with up to three previous years, which was not possible before. Both departments save several hours a month on preparing lists.
How it works
CRM data
Companies, work-group tasks and deals.
SQL datasets
Trino queries that clean, join and aggregate the data in real time.
Panels per area
Overview, accounting and tax, payroll and administration, comparable with three previous years.
Lists for consultants
Filters to prepare the monthly allocation in seconds.
Management decides with instant data
No more report requests, and the same report format as before.
Demo
Panels per area, questions from management, year-on-year comparisons and the SQL query behind each chart. If you set up a client in the sales cycle, it shows up here.
One panel per area, fed by the CRM in real time. Ask what management used to ask, filter, compare with previous years and see the SQL query behind each chart.
Fictitious data and amountsQuestions from management
The client you set up in the sales cycle demo shows up here straight away: in the client base, in their consultant’s workload and in this month’s new clients.
Active clients
540
345 companies · 195 self-employed
Monthly billing
€122,225 / month
Fictitious amount
Profitability
€72.85/h
All fees ÷ consultant hours
With payroll
283
Accounting and tax
€83,860 / month
Fictitious amount
Payroll
€34,815 / month
Fictitious amount
Other services
€3,550 / month
Fictitious amount
Without payroll
257
Hours and clients per consultant · accounting and tax
Actual client hours per month, updated every quarter. The mark shows the reference capacity (150 h, fictitious).
- Consultora A178 h · 63 clients
⚠ Above capacity
- Consultor B108.75 h · 63 clients
- Consultora C137.75 h · 63 clients
- Consultor D152.75 h · 73 clients
⚠ Above capacity
- Consultora E138.75 h · 62 clients
- Consultor F101.5 h · 55 clients
- Consultora G131.5 h · 62 clients
- Consultor H137.75 h · 55 clients
See the SQL query
Invented tables and columns; the same kind of query as in the real dashboard (Trino and Superset templates).
SELECT cf_consultant AS consultant,
count(*) AS companies,
sum(cf_hours) AS hours_per_month,
sum(cf_fee) / nullif(sum(cf_hours), 0) AS fee_per_hour
FROM crm.companies
WHERE active
AND cf_consultant IS NOT NULL
{% if filter_values('billing_entity') %}
AND billing_entity IN {{ filter_values('billing_entity') | where_in }}
{% endif %}
{% if filter_values('country') %}
AND country IN {{ filter_values('country') | where_in }}
{% endif %}
GROUP BY 1
ORDER BY 1Hours and clients per consultant · payroll
Actual client hours per month, updated every quarter. The mark shows the reference capacity (150 h, fictitious).
- Consultora L111.5 h · 56 clients
- Consultor M136.75 h · 56 clients
- Consultora N122.75 h · 67 clients
- Consultor P80.5 h · 50 clients
- Consultora R139.5 h · 54 clients
See the SQL query
Invented tables and columns; the same kind of query as in the real dashboard (Trino and Superset templates).
SELECT lab_consultant AS consultant,
count(*) AS companies,
sum(lab_hours) AS hours_per_month,
sum(lab_fee) / nullif(sum(lab_hours), 0) AS fee_per_hour
FROM crm.companies
WHERE active
AND lab_consultant IS NOT NULL
{% if filter_values('billing_entity') %}
AND billing_entity IN {{ filter_values('billing_entity') | where_in }}
{% endif %}
{% if filter_values('country') %}
AND country IN {{ filter_values('country') | where_in }}
{% endif %}
GROUP BY 1
ORDER BY 1Full client list
What used to be the tracking spreadsheet, now always up to date.
| Client | Type | Acc. consultant | Acc. hours | Acc. fee | Per hour | Payroll consultant | Payslips | Payroll hours | Total fee |
|---|---|---|---|---|---|---|---|---|---|
| Ana Ejemplo 1 | Self-employed | Consultor B | 0.75 h | €60 | €80.00/h | — | — | — | €60 |
| Ana Ejemplo 2 | Self-employed | Consultora E | 1 h | €95 | €95.00/h | Consultora L | 1 | 0.5 h | €110 |
| Ana Ejemplo 3 | Self-employed | Consultor H | 0.75 h | €65 | €86.67/h | — | — | — | €75 |
| Ana Ejemplo 4 | Self-employed | Consultora A | 1.25 h | €105 | €84.00/h | Consultora L | 2 | 0.5 h | €130 |
| Ana Ejemplo 5 | Self-employed | Consultora A | 1 h | €85 | €85.00/h | Consultora L | 1 | 0.5 h | €100 |
| Ana Ejemplo 6 | Self-employed | Consultor F | 1.25 h | €110 | €88.00/h | Consultor P | 2 | 0.5 h | €150 |
| Ana Ejemplo 7 | Self-employed | Consultora G | 1 h | €85 | €85.00/h | — | — | — | €125 |
| Ana Ejemplo 8 | Self-employed | Consultora C | 1.25 h | €100 | €80.00/h | — | — | — | €110 |
| Ana Ejemplo 9 | Self-employed | Consultor H | 1 h | €60 | €60.00/h | — | — | — | €60 |
| Ana Ejemplo 10 | Self-employed | Consultora E | 1.25 h | €85 | €68.00/h | — | — | — | €85 |
See the SQL query
Invented tables and columns; the same kind of query as in the real dashboard (Trino and Superset templates).
SELECT name, client_type, legal_form,
cf_consultant, cf_hours, cf_fee,
cf_fee / nullif(cf_hours, 0) AS cf_fee_per_hour,
lab_consultant, payslips, lab_hours,
cf_fee + lab_fee + other_fee AS total_fee
FROM crm.companies
WHERE active
{% if filter_values('billing_entity') %}
AND billing_entity IN {{ filter_values('billing_entity') | where_in }}
{% endif %}
{% if filter_values('country') %}
AND country IN {{ filter_values('country') | where_in }}
{% endif %}
ORDER BY nameA recreation with fictitious clients, consultants, hours and amounts. In the real dashboard, the panels read three CRM datasets in real time (companies, tasks and deals) and could be compared with up to three previous years.