Query table o formula in Zoho Analytics: quale scegliere
Per scegliere tra query table e formule in Zoho Analytics, con SQL o senza, la regola è semplice. Serve una query table quando occorre una nuova vista che combina, filtra e raggruppa più tabelle. Basta una colonna formula quando il calcolo riguarda una sola riga. Una formula aggregata serve quando occorre un valore sul gruppo di righe mostrato dal report.
Una query table è una vista di dati che Zoho Analytics crea da un'istruzione SQL SELECT. La vista combina una o più tabelle di un'area di lavoro (workspace). Il risultato si usa nei report come una tabella qualsiasi, ma non si modifica a mano: si aggiorna con i dati di origine.
Le formule seguono una logica diversa. Zoho documenta tre tipi di formula per definire le metriche: colonna formula, formula aggregata e formula di report. Nessuna delle tre richiede SQL, ma ciascuna ha un ambito preciso.
Questa guida è la seconda di tre dedicate a Zoho Analytics. La prima, «Zoho Analytics come funziona e come collegarlo ai dati della Sua azienda», spiega il collegamento alle fonti. La terza, «Zoho Analytics sui dati Zoho CRM: la prima dashboard, dal collegamento al report», porta fino alla prima dashboard. Qui si entra nel calcolo: che cosa scrivere, dove e con quali limiti.
I tre tipi di formula in Zoho Analytics e che cosa calcola ciascuno
Zoho Analytics offre tre tipi di formula, e ognuno lavora a un livello diverso dei dati. Confonderli è la causa più comune di totali che non tornano.
Colonna formula: un valore per ogni riga
Una colonna formula crea una nuova colonna calcolata nella tabella dati, partendo da un'espressione. Il risultato viene salvato come colonna della tabella. Nei report si usa come qualunque altra colonna. Le funzioni disponibili sono logiche, statistiche, di data e di testo, combinabili con gli operatori +, -, * e /.
Formula aggregata: un valore per gruppo
Una formula aggregata restituisce sempre un valore numerico. Zoho la calcola per ogni record o gruppo del report in cui viene usata. A differenza della colonna formula, non si aggiunge alla tabella di base: resta associata alla tabella su cui è stata creata. Si usa in grafici, tabelle pivot e viste di riepilogo. Rientrano qui funzioni come sumif, countif, count, distinctcount, ytd, qtd e mtd.
Formula di report: solo dentro un report
Una formula di report usa gli operatori aritmetici di base e una condizione IF annidata, sulle colonne presenti nel report. Vale solo nel report in cui è stata creata e non in altri. È comoda per una prova veloce, ma non si riusa.
Che cosa accetta una query table: dialetti SQL, join e limiti documentati
Una query table accetta solo istruzioni SELECT, e Zoho fissa limiti precisi sulla loro struttura. Conoscerli prima di scrivere evita query che falliscono al salvataggio.
Zoho supporta otto dialetti SQL: ANSI, Oracle, SQL Server, IBM DB2, MySQL, Sybase, Informix e PostgreSQL. La guida alle query table raccomanda il dialetto ANSI per una copertura e un supporto migliori. I nomi di tabelle e colonne vanno tra virgolette doppie.
Questi sono i vincoli strutturali da rispettare:
- Join supportati: left join, right join e inner join.
- Nessuna subquery correlata, cioè nessuna subquery dentro la clausola WHERE.
- Al massimo tre CTE per query, solo non ricorsive. Una CTE (common table expression) è un risultato temporaneo definito nella query e richiamabile più volte al suo interno.
- Niente subquery dentro una CTE, e niente CTE dentro una subquery.
- PIVOT e UNPIVOT non si usano insieme alle CTE.
- Al massimo tre livelli di query costruite su una query table esistente.
Anche alcune funzioni MySQL mancano. La pagina sull'SQL supportato indica come non supportate DATE_ADD, DATE_SUB, TIMESTAMPADD e TIMESTAMPDIFF. SUBDATE non accetta la sintassi INTERVAL. In ADDDATE l'intervallo deve essere un numero, non un'espressione come 'INTERVAL 31 DAYS'. Per unire risultati, Zoho raccomanda UNION ALL invece di UNION, perché UNION applica implicitamente un DISTINCT.
Quando basta Auto-Join con le colonne di lookup, senza scrivere SQL
Se l'obiettivo è solo unire due tabelle in un report, Zoho Analytics lo fa senza SQL tramite Auto-Join. Zoho indica due metodi per unire tabelle nei report: Auto-Join e query table. Auto-Join unisce le tabelle in automatico quando sono collegate da una colonna di lookup.
Una colonna di lookup è una colonna che collega una tabella a un'altra attraverso un valore comune. Per definirla, le due tabelle devono avere almeno una colonna in comune. Zoho suggerisce i possibili lookup leggendo i metadati, come nomi di colonna e tipi di dato. Il lookup si imposta dall'Import Wizard, dal Table Designer o dal Reports Editor.
Di norma Zoho usa un left join: il report prende tutte le righe della tabella figlia e solo le righe corrispondenti della tabella padre. Il tipo di join si cambia dall'icona View Relationships nel chart designer. L'opzione Configure Lookup Path permette di scegliere il percorso tra tabelle, anche con più colonne di lookup per coppia.
Auto-Join ha un limite da conoscere. Si può configurare un solo percorso per collegare due tabelle. Non si possono configurare percorsi diversi per due colonne della stessa tabella nello stesso report. Quando serve questo, o serve raggruppare e filtrare prima del report, la query table diventa la scelta corretta.
Tabella decisionale: colonna formula, formula aggregata, formula di report o query table
La scelta dipende da tre domande: a che livello avviene il calcolo, dove deve essere riutilizzato e se serve combinare tabelle. La tabella seguente riassume le differenze documentate da Zoho.
| Strumento | Livello del calcolo | Dove resta il risultato | Riuso | Quando sceglierlo |
|---|---|---|---|---|
| Colonna formula | Ogni riga | Nuova colonna della tabella | In tutti i report della tabella | Anno, trimestre, testo pulito, condizione su una riga |
| Formula aggregata | Ogni gruppo del report | Associata alla tabella, non come colonna | In grafici, pivot e viste di riepilogo | Importo vinto, tasso di vittoria, valori da inizio anno |
| Formula di report | Colonne del singolo report | Solo nel report | Nessuno fuori dal report | Verifica veloce, calcolo usato una volta |
| Auto-Join con lookup | Unione di tabelle nel report | Relazione tra tabelle | In tutti i report che usano le tabelle | Unire due tabelle senza trasformarle |
| Query table | Nuova vista SQL | Tabella derivata nel workspace | Come una tabella, fino a tre livelli sopra | Filtrare, raggruppare e combinare prima del report |
In caso di dubbio conviene partire dallo strumento più semplice che risponde alla domanda. Una colonna formula si legge in una riga. Una query table va letta per intero, e ogni modifica può toccare i report costruiti sopra.
Esempio di query table: ricavi vinti per settore e per trimestre
Questa query table calcola i ricavi delle trattative vinte, suddivisi per settore dell'azienda cliente, anno e trimestre di chiusura. Si incolla nell'editor della query table, nel workspace in cui sono sincronizzati i dati di Zoho CRM.
SELECT "Accounts"."Industry" AS "Industry",
YEAR("Deals"."Closing Date") AS "Year",
QUARTER("Deals"."Closing Date") AS "Quarter",
SUM("Deals"."Amount") AS "Won Revenue"
FROM "Deals"
INNER JOIN "Accounts" ON "Deals"."Account Name" = "Accounts"."Id"
WHERE "Deals"."Stage" = 'Closed Won'
GROUP BY "Accounts"."Industry",
YEAR("Deals"."Closing Date"),
QUARTER("Deals"."Closing Date")La query unisce le trattative ai clienti con un inner join, tiene solo la fase 'Closed Won' e somma gli importi per gruppo. Prima di eseguirla, controlli tre punti nel Suo workspace:
- Il nome della tabella delle trattative: può comparire come "Deals" oppure come "Potentials", il vecchio nome API di Deals.
- Il contenuto di "Account Name": se la colonna contiene l'id del cliente, il join su "Id" è corretto; se contiene il nome, il join va fatto sul nome.
- Il valore esatto della fase vinta, scritto come appare nei dati.
Se la query restituisce un errore, corregga prima i nomi di tabelle e colonne. Nella maggior parte dei casi il problema è lì, non nella logica. Zoho usa query table dello stesso tipo nelle sue query della soluzione per Zoho CRM, dove la tabella Potential Conversion by Month filtra le trattative in fase 'Closed Won'.
Esempio di formule aggregate: importo vinto e tasso di vittoria
Le formule aggregate calcolano un valore sul gruppo di righe che il report mostra, senza creare una nuova tabella. Si aggiungono dalla tabella dei dati, come formula aggregata, e poi si usano in grafici e pivot.
Il primo esempio è quello documentato da Zoho per l'importo vinto sui dati CRM di esempio:
sumif("Potentials"."Stage" = 'Closed Won', "Potentials"."Amount")La funzione somma "Amount" solo sulle righe in cui la fase è 'Closed Won'. Nell'esempio la tabella si chiama "Potentials". Se nel Suo workspace la tabella si chiama "Deals", sostituisca il nome in entrambi i punti.
Il secondo esempio calcola il tasso di vittoria con le funzioni documentate:
countif("Deals"."Stage" = 'Closed Won')
/ countif("Deals"."Stage" in ('Closed Won','Closed Lost')) * 100La prima countif conta le trattative vinte. La seconda conta quelle chiuse, vinte o perse. Il rapporto moltiplicato per 100 dà la percentuale. In countif il secondo argomento è facoltativo e, se manca, viene trattato come nullo; qui basta la condizione.
Il vantaggio rispetto a una colonna è il livello di calcolo. Se il report raggruppa per commerciale, il tasso si calcola per commerciale. Se raggruppa per mese, si calcola per mese. Per le cifre da inizio anno, trimestre o mese esistono ytd, qtd e mtd.
Colonne formula per anno e trimestre, e la differenza tra QUARTER e quarter()
Una colonna formula con anno e trimestre di chiusura rende ogni trattativa filtrabile per periodo, in tutti i report della tabella. Si crea dalla tabella "Deals" come nuova colonna formula, una colonna per ciascuna espressione.
year("Deals"."Closing Date")
quarter("Deals"."Closing Date")La prima riga restituisce l'anno della data di chiusura. La seconda restituisce il trimestre. Cambi solo il nome della tabella e della colonna data, se nel Suo workspace sono diversi.
Il trimestre ha un dettaglio che conviene conoscere. Nella query table, la funzione SQL QUARTER restituisce un numero da 1 a 4. Nella colonna formula, quarter() restituisce un'etichetta come Q3. Se un report confronta i risultati della query table con quelli della colonna formula, i due valori non coincidono come testo. Scelga un solo formato per i trimestri e lo usi in tutti i report dello stesso workspace.
Le funzioni di data relative, come today(), now() e modified_time(), restituiscono sempre valori in GMT. Per allinearle al fuso locale, Zoho indica convert_tz() con lo scostamento orario corretto. Le date già presenti nei dati, come la data di chiusura, non richiedono questo passaggio.
Regole per report corretti e manutenibili nel tempo
Un report resta corretto nel tempo quando ogni calcolo ha un solo posto in cui vive. Se lo stesso importo vinto è calcolato in una query table, in una formula aggregata e in una formula di report, prima o poi le tre versioni divergono.
Queste regole riducono il rischio:
- Un calcolo per riga va in una colonna formula, un calcolo per gruppo in una formula aggregata.
- Una query table serve per filtrare, raggruppare o combinare, non per ripetere ciò che fa Auto-Join.
- Scrivere in ANSI, come raccomanda Zoho, anche se si conosce meglio un altro dialetto.
- Preferire UNION ALL a UNION, salvo quando serve davvero eliminare i duplicati.
- Restare sotto i tre livelli di query table sovrapposte e le tre CTE per query.
Zoho protegge già una parte del lavoro. Prima di eliminare una colonna di una query table, esegue un controllo delle dipendenze. Se esistono viste che dipendono da quella colonna, l'eliminazione viene interrotta. Il controllo evita report rotti, ma non segnala un calcolo duplicato altrove.
Nei progetti Svennis teniamo ogni query table dedicata a un solo scopo e annotiamo, accanto alla query, quali tabelle e quali valori di fase usa. Così chi la modifica mesi dopo sa subito che cosa controllare se i nomi in Zoho CRM cambiano.
Che cosa significa per un'azienda italiana: fuso orario, settimana e anno fiscale
Per un'azienda italiana tre impostazioni predefinite di Zoho Analytics meritano un controllo, perché partono da convenzioni diverse da quelle abituali in Italia.
Fuso orario e ora legale
Le funzioni di data relative restituiscono valori in GMT. Con convert_tz() si passa all'ora locale. Zoho precisa che solo gli identificativi di fuso orario gestiscono in automatico l'ora legale; abbreviazioni e scostamenti fissi no. Per l'Italia conviene quindi usare l'identificativo del fuso, non uno scostamento fisso.
Inizio della settimana e giorni lavorativi
Nella query table, la funzione SQL WEEK considera di norma la domenica come primo giorno della settimana. Per partire dal lunedì occorre passare un argomento MODE. Nelle colonne formula, business_days e le funzioni simili trattano sabato e domenica come fine settimana, se non si indica altro.
Anno fiscale
Le funzioni ytd, qtd e mtd hanno un parametro fiscal_start_Month, con valori da 1 a 12. È obbligatorio solo se nel workspace è impostato un mese di inizio dell'anno fiscale diverso. Se i dati commerciali vanno confrontati con le fatture registrate in Zoho Books, il mese di inizio deve essere lo stesso nei due sistemi. Lo stesso vale per gli abbonamenti gestiti con Zoho Billing, quando alimentano report per mese di sottoscrizione.
Prossimi passi per scrivere la prima query table e le prime formule
Il primo passo è verificare i nomi reali nel Suo workspace, prima di scrivere codice. Apra la tabella delle trattative e annoti se si chiama "Deals" o "Potentials", che cosa contiene "Account Name" e come è scritta la fase vinta.
Poi proceda in questo ordine:
- Crei le colonne formula per anno e trimestre di chiusura e controlli il formato del trimestre.
- Aggiunga la formula aggregata dell'importo vinto e confronti il totale con quello di Zoho CRM.
- Aggiunga il tasso di vittoria e lo provi con due raggruppamenti diversi, per commerciale e per mese.
- Scriva la query table solo se serve una vista che le formule e Auto-Join non danno, per esempio i ricavi per settore.
- Annoti accanto a ogni query table le tabelle e i valori che usa.
Se il collegamento dei dati non è ancora pronto, riparta dalle altre due guide della serie su Zoho Analytics. Per capire come il prodotto si inserisce nei sistemi della Sua azienda, e che tipo di supporto è disponibile, consulti la pagina di Svennis dedicata a Zoho Analytics.
