Come analizzare i dati di Zoom su Excel

Analizzare i dati delle nostre riunioni Zoom su Excel è possibile, ma abbiamo bisogno di qualche passaggio aggiuntivo per ottenere un file veramente comodo e scalabile. Questo procedimento ci può essere utile quando abbiamo dei meeting e non siamo certi di chi abbia partecipato e per quanto tempo, dato che magari gli utenti cambiano nome in corsa e questo crea problemi in fase di revisione.

Per arrivare al risultato dobbiamo eseguire le seguenti operazioni:

• Scaricare i dati del nostro meeting in CSV da Zoom
• Importare i dati in CSV del nostro meeting su Excel utilizzando Power Query
• Creare una tabella di raccordo per evitare i doppioni
• Unire la tabella iniziale con i dati del meeting con la tabella di raccordo su Power Query
• Caricare la nuova versione della nostra tabella e verificare i risultati

Cominciamo!

Il file qui indicato è un esempio di come sistemare i dati, puoi chiaramente sostituire il CSV di origine con il tuo e poi rivedere la tabella chiamata TBL_TRASFORMAZIONE per arrivare velocemente al risultato.

Come scaricare i dati CSV da Zoom

Vediamo i singoli passaggi per scaricare i dati CSV da Zoom:

• Fare login all'interno di Zoom
• Accedere alla sezione Reports
• Accedere alla sezione Usage
• Selezionare il range di date che ci interessa e cliccare su Search
• Selezionare il meeting che ci interessa cliccando sul numero di Participants
• Cliccare su Show unique users e poi Export

Avremo un CSV con varie colonne, da importare su Excel per determinare la presenza all'interno della singola riunione.

Come importare un CSV con Power Query su Excel

Ora, possiamo importare il CSV sul nostro file Excel utilizzando Power Query. Apriamo un nuovo file Excel e clicchiamo su Dati > Recupera Dati > Da file > Da testo/CSV.

Ho rinominato il file ZOOM_MEETING_1, quindi il file da importare per me sarà ZOOM_MEETING_1.csv. Clicchiamo su Trasforma Dati.

Vediamo che abbiamo a nostra disposizione 4 colonne:

• Name (Original Name): il nome della persona che si è connessa al nostro meeting
• User Email: in questo caso è vuoto, ma potresti avere le email di chi si è registrato
• Total Duration (Minutes): i minuti nei quali una singola persona è rimasta connessa al meeting
• Guest: se la persona sia guest o meno

I dati potrebbero variare nel tuo caso, ma ricordati che ti bastano la prima, Name (Original Name) e la terza colonna Total Duration (Minutes): avendo il nome e la durata, possiamo fare i nostri calcoli con relativa semplicità.

A questo punto i passaggi su Power Query sono i seguenti:

• Cambiamo il nome della query, che sarà TBL_MEETING_START. Questo si può fare o cambiando il nome nella sezione Proprietà sul lato destro o cliccando con il tasto destro sul pannello di query sul lato sinistra > Rinomina
• Il CSV dovrebbe già avere le intestazioni corrette. Se così non è, basta cliccare su Home > Trasforma > Usa la prima riga come intestazione
• Con il tasto CTRL selezioniamo le colonne User Email e Guest, per poi cliccare su Home > Gestisci colonne > Rimuovi colonne
• Selezioniamo le singole colonne e rinominiamole come NOME e DURATA
• A questo punto clicchiamo su Chiudi e carica in > Nuova tabella in un nuovo foglio di lavoro

Vediamo come funziona lato codice:

let
Origine = Csv.Document(File.Contents("G:\ZOOM_MEETING_1.csv"),[Delimiter=",", Columns=4, Encoding=65001, QuoteStyle=QuoteStyle.None]),
#"Intestazioni alzate di livello" = Table.PromoteHeaders(Origine, [PromoteAllScalars=true]),
#"Modificato tipo" = Table.TransformColumnTypes(#"Intestazioni alzate di livello",{{"Name (Original Name)", type text}, {"User Email", type text}, {"Total Duration (Minutes)", Int64.Type}, {"Guest", type text}}),
#"Rimosse colonne" = Table.RemoveColumns(#"Modificato tipo",{"User Email", "Guest"}),
#"Rinominate colonne" = Table.RenameColumns(#"Rimosse colonne",{{"Name (Original Name)", "NOME"}, {"Total Duration (Minutes)", "DURATA"}})
in
#"Rinominate colonne"

La spiegazione è la seguente:

• Origine = Csv.Document(File.Contents("G:\ZOOM_MEETING_1.csv"),[Delimiter=",", Columns=4, Encoding=65001, QuoteStyle=QuoteStyle.None]): Questo codice legge un file CSV dal percorso specificato. Imposta la virgola come delimitatore per separare i valori in diverse colonne. Inoltre, indica che il file contiene 4 colonne, utilizza una codifica UTF-8 (Encoding=65001), e non considera alcun stile di citazione (QuoteStyle.None).
• "Intestazioni alzate di livello" = Table.PromoteHeaders(Origine, [PromoteAllScalars=true]): Questa riga prende la tabella "Origine" e promuove la prima riga a essere l'intestazione della tabella. L'opzione 'PromoteAllScalars' a true significa che se la prima riga contiene solo una colonna, quella colonna diventerà l'intestazione della tabella.
• "Modificato tipo" = Table.TransformColumnTypes(#"Intestazioni alzate di livello",{{"Name (Original Name)", type text}, {"User Email", type text}, {"Total Duration (Minutes)", Int64.Type}, {"Guest", type text}}): Qui, il tipo di dati delle colonne è modificato. "Name (Original Name)" e "User Email" vengono trasformati in tipo di testo, "Total Duration (Minutes)" viene trasformato in un tipo di intero a 64 bit e "Guest" viene trasformato in tipo di testo.
• "Rimosse colonne" = Table.RemoveColumns(#"Modificato tipo",{"User Email", "Guest"}): Questa riga rimuove le colonne "User Email" e "Guest" dalla tabella.
• "Rinominate colonne" = Table.RenameColumns(#"Rimosse colonne",{{"Name (Original Name)", "NOME"}, {"Total Duration (Minutes)", "DURATA"}}): Infine, le colonne "Name (Original Name)" e "Total Duration (Minutes)" vengono rispettivamente rinominate in "NOME" e "DURATA".

Il risultato finale è una nuova tabella, caricata con il nome TBL_MEETING_START, con due colonne ("NOME" e "DURATA").

Verifichiamo quanti siano i minuti totali facendo la somma dell'intera colonna:

=SOMMA(TBL_MEETING_START[DURATA])

A questo punto vediamo che il totale di minuti è 4935. Questo numero ci servirà per verificare eventuali errori.

Come rivedere i risultati da Zoom a Excel

Abbiamo caricato il nostro CSV da Zoom a Excel e i numeri sono in formato leggibile. Potremmo pensare di aver finito qui, ma chiaramente ci sono ancora degli aspetti da considerare.

Come sappiamo, Zoom considera in maniera diversa ogni utente che entra in un meeting. Quindi "Marco Filocamo", "Marco F.", "Marco Filocamo (Guest") sono tutti utenti diversi.

Nel nostro file vediamo 3 errori di questo tipo:

• Giulia - Villa, che dovrebbe essere Giulia Villa
• iPhone di Ludovica, che dovrebbe essere Ludovica Martini
• Tommaso Mariani (Guest), che dovrebbe essere Tommaso Mariani

Questo è uno dei motivi principali per i quali le aziende esortano i partecipanti ai meeting a inserire il proprio nome e cognome e a mantenere questa informazione per tutto il meeting. Se così non è, dobbiamo fare dei passaggi intermedi per sistemare il file e avere un risultato corretto - perchè non possiamo presentare questi dati, avremmo degli errori pesanti anche da giustificare con terzi (più persone del previsto, alcuni con dei nomi poco chiari, nessun dato certo sul tempo totale in meeting di tutti i partecipanti).

Sistemarli a mano è una pessima idea. Vediamo un'alternativa scalabile.

Come creare una tabella di raccordo su Excel

I nostri dati Excel hanno bisogno di una tabella di raccordo che aiuti a comprendere i casi da sistemare del nostro database iniziale. Vediamo come fare:

• Copiamo e incolliamo la colonna della nostra tabella con i singoli nomi delle persone, in un altro foglio. Mettiamo nella cella B1 il testo NOME_REALE, mentre in A1 avremo NOME
• Se siamo partiti dalla cella A1 (intestazione), inseriamo nella cella B2 la formula =A2 e trasciniamo fino in fondo. . Quindi ad esempio avremo Sofia Costa nella cella A1 e nella cella B1 avremo nuovamente Sofia Costa
• Rivediamo tutte le singole equivalenze, modificando quelle sbagliate. Ad esempio togliamo la formula che ci riporta iPhone di Ludovica e inseriamo, a mano, Ludovica Martini. In questo modo eliminiamo le equivalenze sbagliate e creiamo una chiave per raggruppare i singoli dati
• A questo punto, copiamo e incolliamo l'intera colonna B come valori: seleziona tutti i dati, poi CTRL + C per copiarli e CTRL+ALT+V. A questo punto seleziona Valori e clicca su OK. Non ci servono più le formule, ma avremo una lista di riferimenti corretti
• Clicchiamo all'interno del nostro riferimento e facciamo CTRL+T oppure Inserisci > Tabella. Creeremo così una tabella di raccordo che ci servirà poi per riportare tutti i valori correttamente
• La nuova tabella si chiamerà TBL_TRASFORMAZIONE, puoi cambiare il nome cliccando all'interno della tabella in Struttura tabella sul lato sinistro

Anche se sembra un procedimento lungo, tutto questo processo ti porterà via solo qualche minuto e potrai poi riutilizzare queste informazioni anche più avanti, nel caso ci fossero più meeting da riportare (caso che aggiornerò a breve, non presente sulla guida per il momento). Il problema di pulirli a mano è che dovremo farlo ogni volta che facciamo uno scarico di dati, perdendo tempo prezioso.

A questo punto, carichiamo anche la nostra tabella su Power Query cliccando sulla tabella e poi Dati > Recupera Dati > Da altre origini > Da tabella/intervallo. Clicca poi su Chiudi e carica > Carica in > Crea solo connessione.

Come creare un merge di query su Excel

Ora possiamo collegare i nostri dati iniziali con la nostra tabella di raccordo su Power Query. Per far ciò dobbiamo cliccare su Home > Combina query > Merge di query > Unisci query come nuova.

Dovremo selezionare la nostra tabella iniziale TBL_MEETING_START e la nostra tabella di trasformazione TBL_TRASFORMAZIONE. Queste due dimensioni si parlano perfettamente perchè in entrambe le query c'è una colonna chiamata NOME, che usiamo come chiave: ci servirà perchè almeno potremo recuperare esclusivamente i nomi corretti, eliminando i casi speciali. Chiameremo poi la nostra tabella finale TBL_MEETING_FINAL.

Prima di tutto, clicchiamo in alto su Power Query > Home > Combina > Merge di query > Unisci query come nuova.

I passaggi sono questi, per riassumere:

• Merge di query come nuova, come indicato
• Espandiamo la tabella chiamata TBL_TRASFORMAZIONE recuperando la colonna NOME_REALE
• Spostiamo la colonna NOME_REALE sulla sinistra rispetto alla colonna DURATA. Per farlo ti basta selezionare la colonna e muoverla con il cursore verso sinistra
• Selezioniamo la colonna NOME e la rimuoviamo cliccando su Home > Gestisci colonne > Rimuovi colonne
• A questo punto clicchiamo su Home > Trasforma > Raggruppa per e raggruppiamo per NOME_REALE. Il nome della nuova colonna sarà DURATA_TOTALE e faremo la Somma della colonna DURATA
• Selezioniamo la colonna DURATA_TOTALE e la ordiniamo in ordine decrescente (Z-A). Ti basta cliccare sul piccolo tooltip sulla destra dell'intestazione di colonna e selezionare la voce che ti interessa
• Come nel caso precedente, clicchiamo poi Chiudi e carica in > Nuova tabella in un nuovo foglio di lavoro

Vediamo come funziona, lato codice:

let
Origine = Table.NestedJoin(TBL_MEETING_START, {"NOME"}, TBL_TRASFORMAZIONE, {"NOME"}, "TBL_TRASFORMAZIONE", JoinKind.LeftOuter),
#"Tabella TBL_TRASFORMAZIONE espansa" = Table.ExpandTableColumn(Origine, "TBL_TRASFORMAZIONE", {"NOME_REALE"}, {"NOME_REALE"}),
#"Riordinate colonne" = Table.ReorderColumns(#"Tabella TBL_TRASFORMAZIONE espansa",{"NOME", "NOME_REALE", "DURATA"}),
#"Rimosse colonne" = Table.RemoveColumns(#"Riordinate colonne",{"NOME"}),
#"Raggruppate righe" = Table.Group(#"Rimosse colonne", {"NOME_REALE"}, {{"DURATA_TOTALE", each List.Sum([DURATA]), type nullable number}}),
#"Ordinate righe" = Table.Sort(#"Raggruppate righe",{{"DURATA_TOTALE", Order.Descending}})
in
#"Ordinate righe"

Vediamo la spiegazione:

• Origine = Table.NestedJoin(TBL_MEETING_START, {"NOME"}, TBL_TRASFORMAZIONE, {"NOME"}, "TBL_TRASFORMAZIONE", JoinKind.LeftOuter): questa riga esegue una Join (unione) tra due tabelle: TBL_MEETING_START e TBL_TRASFORMAZIONE. La Join viene eseguita sulla colonna 'NOME' in entrambe le tabelle e il tipo di Join è 'LeftOuter', il che significa che vengono mantenute tutte le righe della tabella TBL_MEETING_1 e vengono aggiunte le corrispondenze dalla TBL_TRASFORMAZIONE. Il risultato è salvato in una nuova colonna chiamata 'TBL_TRASFORMAZIONE'.
• "Tabella TBL_TRASFORMAZIONE espansa" = Table.ExpandTableColumn(Origine, "TBL_TRASFORMAZIONE", {"NOME_REALE"}, {"NOME_REALE"}): questo pezzo di codice espande la colonna 'TBL_TRASFORMAZIONE' della tabella 'Origine' in una nuova colonna 'NOME_REALE'. In pratica, se la colonna 'TBL_TRASFORMAZIONE' conteneva tabelle annidate, queste vengono "srotolate" in righe separate.
• "Riordinate colonne" = Table.ReorderColumns(#"Tabella TBL_TRASFORMAZIONE espansa",{"NOME", "NOME_REALE", "DURATA"}): questo comando riordina le colonne della tabella nel seguente ordine: 'NOME', 'NOME_REALE', 'DURATA'.
• "Rimosse colonne" = Table.RemoveColumns(#"Riordinate colonne",{"NOME"}): questo codice rimuove la colonna 'NOME' dalla tabella. NOME non ci serve più, dato che ora abbiamo il NOME_REALE, cioè quello sistemato dalla TBL_TRASFORMAZIONE
• "Raggruppate righe" = Table.Group(#"Rimosse colonne", {"NOME_REALE"}, {{"DURATA_TOTALE", each List.Sum([DURATA]), type nullable number}}): questo comando raggruppa le righe in base al 'NOME_REALE' e calcola la somma dei valori nella colonna 'DURATA' per ciascun 'NOME_REALE', creando una nuova colonna 'DURATA_TOTALE'. Dobbiamo seguire questa procedura perchè ci interessa sommare i valori totali per ogni partecipanti, altrimenti avremo un risultato con Ludovica Martini presente due volte (una per quando ha fatto l'accesso come Ludovica Martini, l'altro come iPhone di Ludovica)
• "Ordinate righe" = Table.Sort(#"Raggruppate righe",{{"DURATA_TOTALE", Order.Descending}}): Infine, questa linea ordina le righe della tabella in base alla colonna 'DURATA_TOTALE' in ordine discendente.

il risultato finale è una tabella con le colonne 'NOME_REALE' e 'DURATA_TOTALE', dove 'DURATA_TOTALE' rappresenta la somma dei valori 'DURATA' per ciascun 'NOME_REALE', ordinata in modo che i valori 'DURATA_TOTALE' più alti siano elencati per primi. Questo ci interessa per vedere velocemente quali siano state le persone più presenti al meeting come numero di minuti di connessione.

A questo punto non ci resta che fare un controllo finale per essere sicuri di non esserci persi per strada delle informazioni. Carichiamo quindi la nostra nuova tabella in un nuovo foglio di lavoro.

Come controllare i dati da Zoom a Excel

Verifichiamo che i dati presi da Zoom, sistemati poi da Power Query, siano corretti. Dato che abbiamo il totale dei minuti per la tabella iniziale TBL_MEETING_START e per quella finale TBL_MEETING_FINAL, possiamo iniziare da questo confronto.

=SOMMA(TBL_MEETING_START[DURATA])

Questa formula ci restituisce il totale dei minuti per la tabella iniziale TBL_MEETING_START.

=SOMMA.PIÙ.SE(TBL_MEETING_FINAL[DURATA_TOTALE];TBL_MEETING_FINAL[NOME_REALE];"<>"&"")

Per la tabella finale, sommiamo i minuti totali quando nel NOME_REALE c'è un valore che non sia vuoto. Se c'è vuoto, vuol dire che abbiamo saltato qualche passaggio di pulizia.

I due valori devono coincidere, altrimenti c'è stato un problema di qualche tipo nella trasformazione dei dati.

Nella cella F6 inseriamo =F4=F3. Se sono uguali, apparirà il testo VERO che, con la formattazione condizionale, diventerà verde. Se così non fosse, apparirà il testo FALSO che, sempre con la formattazione condizionale, diventerà di colore rosso.

=CONTA.VALORI(UNICI(TBL_MEETING_START[NOME])) e =CONTA.VALORI(UNICI(TBL_MEETING_FINAL[NOME_REALE]))

Contiamo i valori univoci della prima tabella e di quella finale, sempre tenendo in considerazione i nomi. Inseriamo poi nella cella F5 la differenza fra i due valori, trovando che sono 3 i nomi che sono stati modificati.

Non ci resta che scoprire quali siano i casi di nomi che sono stati sistemati:

=UNICI(STACK.VERT(TBL_TRASFORMAZIONE[NOME];TBL_TRASFORMAZIONE[NOME_REALE]);;1)

Creiamo un array con entrambe le liste di nomi, prendendo le due colonne della tabella TBL_TRASFORMAZIONE. A questo punto UNICI ci restituisce i valori che appaiono solmanete una volta, restituendo proprio i 3 valori che abbiamo modificato.

Questa formula è stata ispirata da un interessante post di Sergei Baklan, che ti invito a consultare > Excel - extract list elements which are not in another list | LinkedIn

E se ci fossimo dimenticati qualche nome? In questo caso ho rimosso, dalla TBL_TRASFORMAZIONE, la riga che segnalava che Tommaso Mariani (Guest) era Tommaso Mariani. Con una formula possiamo trovare questo valore e sistemare velocemente:

=LET(nomi;UNICI(STACK.VERT(TBL_MEETING_START[NOME];
TBL_MEETING_FINAL[NOME_REALE]);;1);
nomi_no_vuoti;FILTRO(nomi;nomi<>"");
nomi_rivisti;UNICI(STACK.VERT(TBL_TRASFORMAZIONE[NOME];
TBL_TRASFORMAZIONE[NOME_REALE]);;1);
nomi_fix;SE.ERRORE(
UNICI(STACK.VERT(nomi_no_vuoti;nomi_rivisti);;1);"");nomi_fix)

La formula effettua i seguenti passaggi:

• Definisce nomi come tutti i nomi inseriti una volta sola quando accodiamo i nomi della tabella iniziale e finale
• Filtra i nomi eliminando i valori vuoti, creando il nome nomi_no_vuoti
• Definisce i nomi_rivisti come quelli della formula precedente, recuperando i valori che sono stati modificati
• Alla fine accoda nuovamente i nostri due intervalli (nomi_no_vuoti e nomi_rivisti) recuperando esclusivamente i valori che appaiono una volta sola. Questi sono i valori che dovremmo aggiungere nella nostra tabella di raccordo per evitare errori
• Se ci fossero tutti i nomi, vuol dire che il nostro processo è chiaro e corretto. Quindi vedremo solamente un valore vuoto, grazie a SE.ERRORE

A questo punto, se troviamo dei dati in questo riferimento, dobbiamo procedere così:

• Riportiamo il valore nella colonna NOME della TBL_TRASFORMAZIONE
• Inseriamo il valore corretto nella colonna NOME_REALE della TBL_TRASFORMAZIONE
• Clicchiamo su Dati > Query e connessioni > Aggiorna tutti

In questo modo vedremo che il nostro file si è aggiornato e ora il risultato è correttamente indicato a sistema.