# Costruzione delle analisi

La **costruzione delle analisi** all’interno di Metabase avviene a partire da query SQL personalizzate, sviluppate direttamente sui dati presenti nel database analitico ClickHouse.

L’obiettivo è trasformare i dati grezzi in indicatori sintetici (KPI) e viste analitiche utili al monitoraggio dei servizi digitali dell’Ente.

#### Definizione delle metriche

> Le **metriche** rappresentano gli indicatori utilizzati per misurare l’andamento dei servizi digitali.

Le analisi sono basate su metriche costruite tramite operazioni di aggregazione, tra cui:

* conteggi (es. numero totale di pratiche, richieste o appuntamenti);
* aggregazioni temporali (per anno, mese, giorno);
* calcolo di variazioni tra periodi (es. confronto tra due intervalli temporali).

Le metriche vengono definite direttamente nelle query SQL, garantendo trasparenza e controllo sulle logiche di calcolo.

{% code title="Definizione di una metrica tramite query SQL: esempio di conteggio del numero di segnalazioni chiuse" %}

```sql
SELECT COUNT(a.status_name) AS "Segnalazioni chiuse"
FROM applications AS a
LEFT JOIN services AS s ON s.id = a.service_id
JOIN tenants AS t ON a.tenant_id = t.id
WHERE {{Ente}}
AND s.identifier LIKE 'inefficiencies%'
AND a.status_name = 'status_complete'
```

{% endcode %}

#### Gestione delle relazioni tra tabelle

Per costruire analisi complete, le query prevedono l’utilizzo di join tra più tabelle del database, ad esempio:

* tabelle principali (es. applications);
* tabelle di supporto (es. services, tenants).

Le relazioni tra le tabelle sono gestite tramite chiavi condivise (es. `tenant_id`, `service_id`), utilizzate per arricchire le informazioni disponibili nelle analisi.

Particolare attenzione è posta nel garantire che le join non introducano duplicazioni nei risultati, verificando la coerenza tra cardinalità delle tabelle e logiche di aggregazione.

A seguito di tali interventi di ottimizzazione a livello di database, è stato possibile adottare join dirette tra le tabelle, evitando la necessità di applicare logiche di deduplicazione nelle query.

{% code title="Esempio di join tra tabelle (applications, services, tenants) per l’integrazione dei dati nelle analisi" %}

```sql
FROM applications AS a
LEFT JOIN services AS s ON s.id = a.service_id
JOIN tenants AS t ON a.tenant_id = t.id
```

{% endcode %}

#### Costruzione di analisi temporali

Una componente centrale delle analisi è rappresentata dalla dimensione temporale (es. trend mensile negli ultimi 12 mesi).

Le query sono progettate per supportare:

* analisi su singoli periodi (es. anno corrente);
* confronti tra due periodi (es. anno su anno, mese su mese);
* diverse granularità temporali (annuale, mensile, giornaliera).

Questo approccio consente di analizzare l’evoluzione nel tempo dei principali indicatori e identificare variazioni significative.

{% code title="Query SQL per la visualizzazione del trend mensile delle pratiche negli ultimi 12 mesi, con gestione della continuità temporale" %}

```sql
WITH month_series AS (
    SELECT arrayJoin(
        arrayMap(x -> addMonths(toStartOfMonth(now()), -11 + x),
        range(12))
    ) AS "Mesi"
),
helpdesk_data AS (
    SELECT 
        toStartOfMonth(a.created_at) AS mese,
        COUNT(*) AS cnt
    FROM applications AS a
    LEFT JOIN services AS s ON s.id = a.service_id
    JOIN tenants AS t ON a.tenant_id = t.id
    WHERE {{Ente}}
        AND a.status_name IN ('status_submitted', 'status_pending', 'status_complete', 'status_partially_complete', 'status_cancelled')
		    AND a.created_at >= addMonths(toStartOfMonth(now()), -11)
    GROUP BY toStartOfMonth(a.created_at)
)
SELECT
    ms."Mesi",
    COALESCE(hd.cnt, 0) AS "Numero di pratiche"
FROM month_series AS ms
LEFT JOIN helpdesk_data AS hd ON hd.mese = ms."Mesi"
ORDER BY ms."Mesi" ASC
```

{% endcode %}

#### Confronto tra periodi e calcolo del trend

Per alcune analisi viene implementato il confronto tra due periodi distinti, definiti tramite parametri (es. Anno 1 vs Anno 2, Mese 1 vs Mese 2).

In questi casi:

* vengono calcolati i valori per ciascun periodo;
* viene derivata una variazione percentuale (trend), utile per evidenziare aumenti o diminuzioni.

Questo tipo di analisi è utilizzato, ad esempio, per monitorare l’andamento delle richieste o delle pratiche nel tempo.

{% code title="Estratto della query per il confronto tra due periodi temporali e il calcolo della variazione percentuale (trend)" %}

```sql
SELECT
    COUNTIf(toYear(a.created_at) = anno_1 AND toMonth(a.created_at) = mese_1) AS "Numero totale delle pratiche - Periodo 1",
    COUNTIf(toYear(a.created_at) = anno_2 AND toMonth(a.created_at) = mese_2) AS "Numero totale delle pratiche - Periodo 2",
    IF(
            COUNTIf(toYear(a.created_at) = anno_1 AND toMonth(a.created_at) = mese_1) = 0,
            'Trend non calcolabile',
            concat(
                toString(
                    ROUND(
                        (
                            (
                                COUNTIf(toYear(a.created_at) = anno_2 AND toMonth(a.created_at) = mese_2) - COUNTIf(toYear(a.created_at) = anno_1 AND toMonth(a.created_at) = mese_1)) * 100.0
                            /
                            COUNTIf(toYear(a.created_at) = anno_1 AND toMonth(a.created_at) = mese_1)
                        ),
                        2
                    )
                ),
                '%'
            )
        ) AS "Trend in percentuale"
FROM applications AS a
LEFT JOIN services AS s ON a.service_id = s.id
JOIN tenants AS t ON a.tenant_id = t.id
WHERE {{Ente}} AND a.status_name IN ('status_submitted', 'status_pending', 'status_complete', 'status_partially_complete', 'status_cancelled')
```

{% endcode %}

#### Visualizzazione dei risultati

Una volta definite le query SQL, i risultati vengono rappresentati tramite le funzionalità di visualizzazione offerte da Metabase. A partire dallo stesso dataset è possibile applicare diverse modalità di rappresentazione (es. KPI, tabelle o grafici), in funzione dell’obiettivo dell’analisi.

La scelta della visualizzazione costituisce un passaggio fondamentale, in quanto consente di trasformare il risultato numerico della query in un’informazione facilmente interpretabile.

Le specifiche tipologie di visualizzazione e i relativi casi d’uso sono approfonditi nel paragrafo successivo.


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.opencityitalia.it/dashboard-di-monitoraggio-dei-servizi/analisi-dei-dati-metabase/costruzione-delle-analisi.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.