Aug 05 2009

Ricomporre i dati in Excel (e una digressione sulla precisione)

autore: categoria: report tag: ,

Vorrei ora sapere come interpretare il tempo medio espresso con numeri del tipo 14799878978102900… Cosa significa? E come convertirlo in un formato familiare?

Elisabetta sta usando gli export in CSV e TSV per riproporre su Excel i dati di Google Analytics, ma il formato non è molto malleabile. Mentre cercavo una soluzione per un attimo ho creduto di aver fatto una scoperta, ma era tardi e oggi tutto si è ridimensionato, ma affrontiamo un problema alla volta.

Importare correttamente CSV e TSV in Excel

excel_logoGoogle Analytics esporta sempre i file dei report in formato inglese (anche se usate l’interfaccia in italiano), quindi usa i punti per separare i decimali, ma fortunatamente non le virgole per le migliaia. Importare direttamente tutto in un Excel “europeo” fa risultare il tempo medio (e in generale le cifre relative al tempo) in un numero come quello indicato da Elisabetta.

Una possibile soluzione è quella di impostare Excel nel formato inglese prima di importare i dati. Su Excel 2003: strumenti -> opzioni -> internazionale -> togliere la spunta da “utilizza separatori di sistema” e impostare il separatore decimale sul punto e quello delle migliaia sulla virgola. Bisognerà poi ricordarsi di rimettere tutto a posto prima di uscire, e comunque la modifica viene applicata a tutti i fogli Excel aperti e che si apriranno, per cui potrebbe non essere la soluzione ottimale.

L’altro metodo consiste nello scaricare il CSV o il TSV, aprirlo con un editor di testo e sostituire tutti i punti con delle virgole, poi salvare e importarlo in Excel. E’ un pochino laborioso, lo so, ma questo consente di non toccare le impostazioni e poter aprire e lavorare su altri file.
Usando questo metodo bisogna poi importare correttamente i dati: da Excel si seleziona il menu File -> Apri -> si seleziona il CSV o il TSV salvato precedentemente e si avvia la procedura.
selezionare su un foglio vuoto di Excel, cliccare Dati -> importa dati esterni -> importa dati… e selezionare il file scaricato in precedenza. Nella finestra di dialogo che si apre la prima scelta da fare è dire che i campi sono delimitati, poi è necessario selezionare 65001: Unicode (UTF-8) nell’origine file, per evitare problemi con le lettere accentate. Nella schermata successiva il delimitatore va impostato su Tabulazione se avete per le mani un TSV, su Virgola se avete un CSV. L’anteprima dati vi aiuterà a capire se state facendo la scelta giusta. Nella terza schermata potete lasciare il formato dei numeri su generale e cliccare su Avanzate… per impostare il separatore decimale corretto, il punto e quello delle migliaia, la virgola. (grazie a Stefano per il suggerimento nei commenti 🙂 )
(edit: esiste una terza via: scegliere “CSV per Excel. In questo caso non dovete specificare UTF-8 come codifica, e dovete lasciare la tabulazione come separatore, quindi l’unica cosa da impostare sono i separatori nella terza schermata. grazie Eloisa!)

A questo punto avrete il tempo medio espresso in secondi, con un numero tipo 47,0701754385964. Per riportarlo ad una forma più familiare è necessario dividere quel numero per 60 * 60 * 24 (cioé 86400) – magari copiandolo su un’altra colonna – e poi applicare il formato di cella personalizzato ore minuti secondi hh:mm:ss (che trasforma il numero di prima in 00:00:48)

La precisione dei dati in Google Analytics

lenteAppena ho iniziato a fare prove per rispondere a Elisabetta ho preso un grosso abbaglio. Mi sono detto “come è possibile che Google Analytics conosca il tempo medio sulla pagina preciso fino al microsecondo?” (tra l’altro sbagliando, perché guardando il valore esatto nella barra della formula, non troncato dalla visualizzazione, sarebbe preciso oltre il picosecondo, 10-12). Ovviamente la risposta è “non la conosce, ma essendo il tempo medio un valore calcolato con una frazione, possono esserci più decimali di quanti ce ne siano nei dati di partenza.

Voglio però farvi notare due cose: la prima è che nel database di Google Analytics – in questo caso, ma non vedo perché dovrebbe essere diverso su altri campi – le cifre non vengono troncate secondo quel che è necessario a Google per la visualizzazione dei report; potrebbe fare il calcolo e tenere il numero intero di secondi, o al massimo un paio di decimali, invece conserva decimali fino al tredicesimo e li converte al volo nel momento in cui l’interfaccia li mostra.
La seconda è che questo aspetto ci torna utile in altri campi: abbiamo detto che le percentuali che vediamo nell’interfaccia di GA sono arrotondate a due decimali, mentre nell’export di decimali ne abbiamo 15. Su grandi numeri il 65,711878 o il 65,7149999 potrebbero avere un significato diverso, anche se poi vengono entrambi arrotondati a 65,71%. Stesso discorso ad esempio guardando l’indice$ delle pagine: molte potrebbero avere 0,01 e sembrarci tutte uguali, ma avendo molti più decimali a disposizione su Excel siamo in grado di effettuare analisi più dettagliate.

Condividi l'articolo:

8 Commenti

  1. Un altro metodo per importare un file cvs con formattazione non europea dei decimali è il seguente (Excel 2003):

    – Aprire un nuovo foglio vuoto
    – Selezionare il menu Dati > Importa dati esterni > Importa dati…
    – Impostare Tipo File: File di Testo e selezionare il file esportato da GA
    – Seguire le istruzioni della importazione guidata testo: nella terza finestra cliccare su “Avanzate” ed impostare i spearatori dei decimali(“.”) e delle migliaia (bianco)
    – Cliccare su Fine

    Excel provvederà autometicamente ad interpretare i numeri decimali ed importarli correttamenti, senza preprocessare prima il file di testo o modifare le impostazioni globali del programma.

    Saluti

    Saluti

  2. mitico! non conoscevo la presenta del pulsante Avanzate…
    modifico il post perché così è ancora più semplice (UTF-8 però va lasciato)

  3. Grazie della dritta su excel!
    Riflettevo su questo difetto di importazione dei dati…ultimamente Google ha inserito anche il formato “csv per excel” (come in Adwords), ma i problemi sono rimasti sempre gli stessi. Chissà se prima o poi rilascerà un formato più malleabile in contesti come excel!

  4. credo che i “CSV per Excel” siano codificati in UTF-16. Excel riconosce le accentate senza bisogno di specificare la codifica. Aggiorno nuovamente il post (come farei senza voi lettori? 🙂 ) perché direi che siamo arrivati alla procedura ottimale!

  5. Marco,
    ho trovato utilissimo questo articolo che ho subito bookmarkato, pero’ occorre ancora una correzione: il tempo medio espresso in secondi deve essere diviso per 60 * 60 * 24, cioe’ 86400.

    Ciao
    Maurizio

  6. grazie, ho corretto 🙂

  7. grazie a tutti!!!! davvero grazie

Trackbacks

  1. RSS Week #64: letture per il weekend - Matteo Moro

Scrivi un Commento