Query tables si formule in Zoho Analytics: cand alegeti fiecare
La query tables si formule in Zoho Analytics, cu SQL, regula de lucru este simpla. Un query table se potriveste cand aveti nevoie de o vedere noua a datelor, cu filtrare, grupare sau combinari pe care un raport nu le face singur. O formula se potriveste cand aveti nevoie de o singura metrica, calculata pe rand sau pe grupul afisat in raport.
Un query table este o vedere de date construita cu o interogare SQL SELECT peste unul sau mai multe tabele din acelasi workspace. Zoho Analytics il foloseste ca pe un tabel. Il puteti pune la baza rapoartelor si il puteti exporta in CSV, PDF, XLS sau HTML. Puteti construi chiar un alt query table peste el. O formula, in schimb, adauga un calcul fara sa creeze o vedere noua.
Acest ghid este al doilea dintr-o serie de trei despre Zoho Analytics. Primul ghid construieste primul dashboard pe datele din Zoho CRM. Aici trecem la nivelul urmator: SQL, formule si exemple de cod pe care le puteti adapta. Daca abia incepeti cu produsul, pagina despre Zoho Analytics si dashboard-urile BI ofera contextul general.
Ce SQL accepta un query table in Zoho Analytics si ce limite are
Un query table accepta doar instructiuni SELECT. Prin el nu modificati date, ci doar le cititi, le combinati si le agregati. Zoho accepta sintaxa din opt dialecte: ANSI, Oracle, SQL Server, IBM DB2, MySQL, Sybase, Informix si PostgreSQL. Zoho recomanda dialectul ANSI, pentru o acoperire si un suport mai bune.
Limitele care conteaza in practica sunt putine si clare. Le gasiti pe pagina Query Tables din documentatia Zoho Analytics, iar lista de mai jos le rezuma:
- Join-uri: sunt acceptate doar Left Join, Right Join si Inner Join.
- Subinterogari corelate: nu sunt permise subinterogari corelate in clauza WHERE.
- CTE: un common table expression este un rezultat temporar definit in interogare si refolosit in ea. Sunt acceptate doar CTE nerecursive, maximum trei pe interogare.
- Combinatii interzise: nu puteti pune subinterogari in CTE si nici CTE in subinterogari. PIVOT si UNPIVOT nu merg impreuna cu CTE.
- Niveluri: puteti construi maximum trei niveluri de interogari peste un query table existent.
In exemplele Zoho, numele de tabele si coloane stau intre ghilimele duble, de exemplu "Deals"."Amount". Valorile text stau intre apostrofuri, de exemplu 'Closed Won'.
Unele functii MySQL nu functioneaza. Pagina Supported SQL din documentatia Zoho arata ca DATE_ADD, DATE_SUB, TIMESTAMPADD si TIMESTAMPDIFF nu sunt acceptate in prezent. La ADDDATE, intervalul se da ca numar, nu ca expresie INTERVAL.
Cele trei tipuri de formule din Zoho Analytics si unde se calculeaza fiecare
Zoho Analytics ofera trei tipuri de formule pentru metrici: formula column, aggregate formula si report formula. Ele difera prin locul unde se calculeaza rezultatul si prin locul unde il puteti refolosi.
Formula column
O formula column este o coloana noua, calculata pentru fiecare rand al tabelului pe baza unei expresii. Rezultatul se salveaza ca o coloana noua in tabel. Coloana se foloseste in orice raport, ca oricare alta. Expresia poate combina functii logice, statistice, de data si de text cu operatorii +, -, / si *.
Aggregate formula
O aggregate formula este o metrica ce returneaza intotdeauna o valoare numerica. Valoarea se calculeaza pentru fiecare inregistrare sau grup afisat in raport. Formula nu se adauga ca o coloana in tabelul de baza, ci ramane asociata tabelului pe care a fost creata. O puteti folosi in grafice, pivot tables si summary views. Functii tipice sunt sumif, countif, ytd, qtd si mtd.
Report formula
O report formula se creeaza in interiorul unui raport si functioneaza doar in acel raport. Ea accepta operatorii de baza +, - si *, plus o conditie IF imbricata, peste coloanele din raport. Este utila pentru un calcul de moment. Pentru o metrica ceruta in mai multe rapoarte, alegeti o aggregate formula.
Tabelul de decizie: query table, formula column, aggregate formula sau report formula
Alegerea intre query table si formule depinde de trei intrebari: ce calculati, la ce nivel si unde refolositi rezultatul. Tabelul de mai jos rezuma raspunsurile, pe baza documentatiei Zoho Analytics.
| Instrument | Ce produce | Nivel de calcul | Unde il refolositi | Folositi-l cand |
|---|---|---|---|---|
| Query table | O vedere noua de date | Gruparea definita in SQL | In orice raport si in alte query tables | Aveti nevoie de filtrare, grupare sau combinari pe care raportul nu le face |
| Formula column | O coloana noua in tabel | Fiecare rand | In orice raport | Valoarea tine de un singur rand, de exemplu anul inchiderii |
| Aggregate formula | O metrica numerica | Fiecare grup afisat in raport | In rapoartele pe tabelul asociat | Aveti nevoie de o suma, un numar sau un procent pe grup |
| Report formula | O metrica in raport | Coloanele din raport | Doar in acel raport | Faceti un calcul rapid intr-un singur raport |
O regula practica: pentru a lega doua tabele, nu porniti de la un query table. Zoho Analytics are doua metode de join, Auto-Join si Query Table. Auto-Join leaga automat tabelele in rapoarte, daca sunt conectate printr-o coloana lookup. Implicit, Auto-Join foloseste Left Join, iar tipul de join se poate schimba din designerul de grafic.
Pentru o coloana lookup, cele doua tabele au nevoie de cel putin o coloana comuna. Zoho sugereaza singur relatii posibile, dupa numele coloanelor si tipurile de date.
Exemplul de query table: venituri castigate pe industrie si trimestru
Interogarea de mai jos calculeaza veniturile din oportunitatile castigate, grupate pe industria clientului, pe an si pe trimestru. O lipiti la crearea unui query table nou, in workspace-ul care contine datele sincronizate din 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")Inainte de rulare, verificati trei lucruri in workspace. Primul este numele tabelei de oportunitati. Unele workspace-uri afiseaza "Deals", altele "Potentials", nume folosit si in exemplele din documentatia Zoho. Al doilea este continutul coloanei "Account Name".
Daca coloana "Account Name" contine id-ul contului, join-ul pe "Accounts"."Id" este corect. Daca ea contine numele contului, legati-o de coloana cu numele din "Accounts".
Al treilea lucru este valoarea exacta a etapei castigate. Ea poate diferi de 'Closed Won' daca ati personalizat etapele in CRM. Daca interogarea returneaza eroare, corectati mai intai numele de tabele si coloane. De obicei schimbati doar liniile FROM, INNER JOIN si WHERE.
Zoho publica interogarile din solutia de analiza avansata pentru Zoho CRM, un model bun de urmat. De exemplu, query table-ul Potential Conversion by Month calculeaza conversia lunara si filtreaza oportunitatile in etapa 'Closed Won'.
Aggregate formulas pentru suma castigata si rata de castig
Doua aggregate formulas acopera majoritatea rapoartelor de vanzari: suma castigata si rata de castig. Le creati pe tabela de oportunitati, ca aggregate formula, apoi le folositi in grafice si pivot tables pe acea tabela. Prima este exemplul documentat de Zoho pentru suma castigata. Ea aduna valorile din "Amount" doar pentru randurile cu etapa 'Closed Won':
sumif("Potentials"."Stage" = 'Closed Won', "Potentials"."Amount")Exemplul Zoho foloseste tabela "Potentials". Daca workspace-ul dumneavoastra afiseaza "Deals", inlocuiti numele in ambele locuri. A doua formula calculeaza rata de castig, ca procent din oportunitatile inchise, cu aceeasi familie de functii documentate:
countif("Deals"."Stage" = 'Closed Won')
/ countif("Deals"."Stage" in ('Closed Won','Closed Lost')) * 100Numaratorul numara oportunitatile castigate, iar numitorul pe cele inchise, castigate sau pierdute. Schimbati 'Closed Won' si 'Closed Lost' daca etapele dumneavoastra au alte nume. Formula fiind agregata, rezultatul se recalculeaza pentru fiecare grup din raport. Aceeasi formula da rata pe agent de vanzari, pe luna sau pe industrie.
Pentru un rezultat independent de gruparea din raport, documentatia descrie expresiile Groupby Shifting, de exemplu Fixed Groupby. Functiile ytd, qtd si mtd primesc un parametru fiscal_start_Month. Parametrul este obligatoriu doar daca workspace-ul are o alta luna de start fiscal.
Formula column pentru an si trimestru, calculata pe fiecare rand
O formula column pentru an si trimestru adauga tabelei de oportunitati doua coloane noi, calculate rand cu rand. Creati cate o formula column separata pentru fiecare expresie de mai jos, in tabela de oportunitati:
year("Deals"."Closing Date")
quarter("Deals"."Closing Date")Prima expresie extrage anul din data inchiderii, a doua trimestrul. Schimbati doar numele tabelei si al coloanei de data, daca ale dumneavoastra difera. Coloanele rezultate apar apoi in orice raport, ca filtre sau grupari. Nu mai este nevoie de un query table doar pentru a obtine anul sau trimestrul.
Atentie cand combinati aceste coloane cu SQL. Functia QUARTER din query table si functia quarter() din formula column sunt documentate separat, fiecare cu formatul ei de iesire. Verificati valorile intr-un raport de test inainte sa le legati sau sa le comparati. Un trimestru stocat ca numar nu se potriveste cu unul stocat ca text.
O formula column nu este locul pentru sume pe grup. Coloana se calculeaza pe fiecare rand. Suma castigata pe trimestru o obtineti cu aggregate formula din sectiunea anterioara, grupata pe noua coloana de trimestru.
Greselile care incetinesc sau complica rapoartele Zoho Analytics
Multe rapoarte lente sau greu de intretinut in Zoho Analytics folosesc query tables pentru lucruri pe care produsul le face mai simplu altfel. Greselile de mai jos apar cel mai des:
- Query table doar pentru join: daca doua tabele au o coloana comuna, definiti o coloana lookup si lasati Auto-Join sa le lege in raport.
- UNION in loc de UNION ALL: Zoho recomanda UNION ALL, pentru ca UNION aplica implicit o operatie Distinct.
- Lanturi lungi de query tables: fiecare nivel depinde de cel de dedesubt, iar Zoho permite maximum trei niveluri.
- Functii neacceptate: DATE_ADD sau DATE_SUB copiate dintr-un script MySQL produc erori. Folositi ADDDATE cu interval numeric.
- Formula column pentru o metrica de grup: o suma pe grup se calculeaza cu o aggregate formula.
La Svennis, cand preluam un workspace cu rapoarte lente, cautam intai query tables create doar pentru a lega tabele. Le inlocuim cu coloane lookup si Auto-Join, iar utilizatorii pastreaza aceleasi rapoarte fara un strat de SQL de intretinut.
Configurarea caii de lookup cere si ea atentie. Optiunea Configure Lookup Path permite o singura cale intre doua tabele. Nu puteti configura cai diferite pentru doua coloane din acelasi tabel in acelasi raport.
Stergerea coloanelor are si ea o protectie. Inainte sa stearga o coloana dintr-un query table, Zoho verifica dependentele. Daca exista vederi care depind de coloana, stergerea este anulata.
Ce inseamna pentru o companie din Romania: fus orar, saptamana si an fiscal
Pentru o companie din Romania, trei comportamente implicite din Zoho Analytics pot deplasa cifrele din rapoarte. Toate se corecteaza din formule sau din SQL, cu conditia sa stiti de ele.
Fusul orar in formulele de data
Functiile relative de data, ca today(), now() si modified_time(), returneaza intotdeauna valori in GMT. Pentru ora locala, Zoho recomanda functia convert_tz() cu decalajul potrivit. Functia accepta identificatori de fus orar, abrevieri sau decalaje. Doar identificatorii de fus orar, de exemplu Europe/Bucharest, tin cont automat de ora de vara.
Inceputul saptamanii in SQL
In SQL, functia WEEK considera implicit ca saptamana incepe duminica. Daca raportati pe saptamani care incep luni, transmiteti un argument MODE ca al doilea parametru. Altfel, cifrele saptamanale din query table nu se aliniaza cu raportarile interne.
Zilele lucratoare si anul fiscal
Functiile business_days, business_hours si business_completion_day considera sambata si duminica zile de weekend, daca nu specificati alte zile. Pentru anul fiscal, functiile ytd, qtd si mtd au nevoie de fiscal_start_Month doar cand workspace-ul are o alta luna de start fiscal. Valoarea merge de la 1, pentru ianuarie, pana la 12.
Daca rapoartele combina datele de vanzari cu facturile din Zoho Books, verificati aceleasi trei setari si pe coloanele de data ale facturilor.
Urmatorii pasi pentru query tables si formule in workspace-ul dumneavoastra
Pasii de mai jos va duc de la un workspace cu date brute la rapoarte care se calculeaza corect si raman usor de intretinut. Parcurgeti-i in ordine:
- Verificati numele tabelelor: "Deals" sau "Potentials". Verificati si daca "Account Name" contine id-ul sau numele contului.
- Definiti coloane lookup intre tabelele pe care le legati des, inainte de a scrie orice query table.
- Creati aggregate formulas pentru suma castigata si rata de castig, apoi testati-le intr-un pivot table.
- Adaugati formula columns pentru an si trimestru, daca rapoartele grupeaza dupa data inchiderii.
- Scrieti un query table doar pentru vederile pe care un raport nu le poate construi, in dialectul ANSI.
- Verificati fusul orar, inceputul saptamanii si luna de start fiscal.
Rapoartele bune depind de date bine structurate in CRM. Daca datele din Zoho CRM nu sunt inca organizate, porniti de la ghidul practic de implementare Zoho CRM. Pentru o imagine de ansamblu asupra aplicatiilor, consultati ghidul despre ce este Zoho.
Daca pregatiti un proiect de rapoarte si dashboard-uri, pagina Zoho Analytics de la inceputul acestui ghid descrie ce acopera implementarea. Pastrati la indemana si documentatia Zoho din sursele de mai jos, pentru limitele exacte ale SQL.
