Digital Analytics Expert
HTML, CSS & JS Lover
Apps Script Developer
Problem-Solving Oriented
I rapporti non campionati in Google Analytics permettono di ottenere dati basati sul 100% delle sessioni anche in quelle condizioni dove la piattaforma applica il campionamento. I rapporti non campionati sono attualmente disponibili solo per gli utenti di Google Analytics 360.
La richiesta di report non campionati in Google Analytics non fornisce una risposta immediata, per ottenere un report scaricabile possono infatti essere necessari da diversi minuti fino a più di un’ora.
Con l’Add-on per Spreadsheet ‘GA360 Unsampled‘ è possibile interrogare le Unsampled Reports API per ottenere direttamente i dati senza preoccuparsi di verificare l’effettiva generazione dei risultati, in quanto vengono comodamente recuperati in modo automatico e salvati all’interno di fogli dedicati dello Spreadsheet utilizzato.
Cosa puoi fare con l’add-on
Accedere a tutti i tuoi dati non campionati di Google Analytics 360 nei fogli di calcolo di Google.
Il componente aggiuntivo per Spreadsheet, GA360 Unsampled, offre la potenza dell’API dei rapporti non campionati unita alla potenza della manipolazione dei dati nei fogli di lavoro di Google.
Con questo strumento puoi:
- Interrogare i dati non campionati da più viste (profili);
- Creare calcoli personalizzati dai dati non campionati del tuo rapporto;
- Creare dashboard con visualizzazioni di dati incorporate;
- Pianificare l’esecuzione automatica dei rapporti in modo che i dati non campionati siano sempre aggiornati;
- Controllare facilmente chi può vedere questi dati sfruttando le funzionalità di condivisione e privacy di Google Spreadsheet.
Funzionamento
Allo stesso modo del Google Analytics Spreadsheet Add-on il componente aggiuntivo per l’interrogazione dei report non campionati (a breve disponibile nel G Suite Market Place) semplifica l’accesso, la visualizzazione, la condivisione e la manipolazione dei dati da parte degli utenti di Google Analytics 360 nei fogli di calcolo di Google.
Definendo i parametri obbligatori nella sidebar dello Spreadsheet, apribile tramite la voce di menu Componenti aggiuntivi » GA360 Unsampled Reports » Create new report, o direttamente nel foglio di configurazione (Report Configuration), Fig. 1, è possibile effettuare la richiesta dei dati non campionati a Google Analytics direttamente dal Foglio di Google:
Una volta eseguiti i report desiderati tramite la voce di menu Componenti aggiuntivi » GA360 Unsampled » Run reports, la chiamata verrà effettuata e lo status della richiesta risulterà PENDING.
Il componente aggiuntivo farà il controllo per voi, verificando ogni 60 minuti la disponibilità del report dal quale, una volta risultato COMPLETED, ne recupererà i dati per importarli in un foglio dedicato dello Spreadsheet, Fig. 2.
Il formato di questi dati è mantenuto nella sua struttura originale, allo stesso modo di come li trovereste in piattaforma o in Google Drive.
Una volta recuperati i dati non campionati è possibile facilmente gestirli per creare grafici o utilizzarli a piacimento per report, elaborazioni o dashboard.
Se vuoi vedere l’add-on in azione ti mostro il suo funzionamento al minuto 01:40 del seguente video:
L’add-on per Spreadsheet, GA360 Unsampled, è disponibile e scaricabile gratuitamente dal G Suite Marketplace, al seguente indirizzo:
https://gsuite.google.com/marketplace/app/ga360_unsampled/990419743199?hl=it
Note tecniche relative ai rapporti non campionati
Dal momento che i rapporti non campionati sono disponibili solo per gli utenti di Google Analytics 360, l’utilizzo dell’add-on con una Account Google al quale non è collegato nessun Account Google Analytics 360 non consentirà di effettuare alcuna interrogazione alla piattaforma.
I rapporti non campionati, per loro natura, hanno alcune restrizioni indicate nella documentazione ufficiale di Google. Per comodità le riporto di seguito.
Le seguenti restrizioni si applicano alla creazione di report non campionati:
- Puoi specificare solo fino a 4 dimensioni;
- Alcuni tipi di dati di report non sono supportati, ad esempio Google Ads Data;
- Le query considerate troppo costose non sono supportate;
- Se hai creato troppi rapporti non campionati e hai raggiunto il limite, puoi eliminare in modo sicuro le risorse dei rapporti non campionati, lasciando intatti i dati dei rapporti generati in Google Drive o Google Cloud.
La mancata osservanza delle restrizioni di cui sopra durante l’utilizzo del componente aggiuntivo GA360 Unsampled potrebbe non essere segnalata dall’interfaccia dell’applicazione.
I primi 7 parametri mostrati nella prima colonna del foglio di configurazione, Fig. 1, sono obbligatori. Tra essi sono presenti due date (start-date e end-date) in quanto, tutte le richieste di dati di Analytics devono specificare un intervallo di date.
I valori di data possono essere espressi per una data specifica utilizzando il modello YYYY-MM-DD (ad esempio: 2020-03-23) o relativa utilizzando today, yesterday o il modello NdaysAgo (ad esempio: 30daysAgo).
Il componente aggiuntivo accetta anche il formato DD/MM/YYYY provvedendo autonomamente alla conversione nel formato previsto.
Termini di servizio
Il nome, il marchio ed i riferimenti alla piattaforma di analisi di Google sono necessari per il fatto che il componente aggiuntivo funziona per offrire il recupero dei dati non campionati da Google Analytics 360. Il componente aggiuntivo è di mia realizzazione e non si tratta di un prodotto ufficiale Google.
L’add-on è gratuita ed il suo utilizzo è a piena responsabilità dell’utente che ne fa uso.
Ho realizzato personalmente il componente aggiuntivo con Google Apps Script.
Se vuoi approcciarti a questo linguaggio di scripting basato su JavaScript e realizzare, con poche righe di codice, script, add-on ed applicazioni che interagiscono con i prodotti della G Suite, ti consiglio il mio libro “Punta in alto con… Google Apps Script“, disponibile anche in formato Kindle.
Hi Michele,
I’ve just come across this addon and I was just wondering 3 things.
What is the average length of time for a unsampled report to run?
Does it start running instantly or is it the same as requesting an unsampled report from GA directly (you can never be sure when it will run).
Also, if I add a schedule to the addon, can that be used with it?
Thanks
Dino
Hi Dino,
in general, to obtain an unsampled report, it can take from several minutes to more than an hour, depending on the complexity of the report and the time interval indicated.
Querying reports with the add-on will require the same processing time than doing it by Google Analytics interface. The difference is that the add-on will automatically check every 60 minutes if the report is ready and when it finds it it writes the result directly in the Google Sheets (at the same time you will also find it in Google Analytics), otherwise you can manually force the scan.
You can schedule the add-on to have, for example, the report updated every day (increasing the date daily with Google Sheets formulas) without having to request it manually (in the same way as it works and as is usually done with the famous add-on for Google Analytics standard reports).
Let me know if I cleared up your doubts, thanks!
Thank you for the response Michele.
After a few runs I understand it better now.
The only thing I have noticed is the auto check every 5 minutes doesn’t appear to work so well for me. Quite often it indicates when the next check is but doesnt appear to run again. I have to manually force it to check.
Not sure if its anything to do with my browser (Version 79.0.3945.117 (Official Build) (64-bit)). Its not up to date as it control by the company I work for.
Many thanks
Dino
Hi Dino,
thanks for the feedback! Forgive the mistake, I adjusted the text, the scan every 5 minutes worked by running the code locally, since it was published as an add-on the minimum time allowed between one automatic run and another is 60 minutes (if you want, you can always manually force the scan at any time). So the scan is performed every 60 minutes (it can be 60:00 or 60:59 minutes) and it is transparent to the user, however the ‘Next scheduled check’ field is updated with the time of the next scan (60 minutes after the previous one) and the sheet is generated (if it does not already exist) when the report data is collected.
It is not a question of browser, in the case of automatic execution the modal does not appear because the action is not carried out by the user but by an activator. It is a behavior of the Google Sheets, the automatic scan and the scheduled scan do not show the modal (the scheduled scan is also present in the Google Analytics standard add-on, and works in the same transparent way).
On the other hand, the advantage is that while you wait for a report you can work on the data already collected from other reports in the other sheets without the screen freezing.
An idea could be to make the time update more evident in the relevant cell, for the moment I have opted for that non-invasive solution but if there are suggestions they will be considered. Many thanks!
Hey Michael,
The add-on looks terrific, congrats!
I have been trying it for a couple of days and today I’m noticing that when I run a report it stays in the “Report status” overlay in Google Sheets, with the circle going round, but the report is not run and doesn’t show up in the GA interface as “Pending”.
Any ideas?
Thanks!
Hi David,
thanks for the feedback 🙂
The situation you described happens when there is a blocking error during the request phase of the report (it can be a problem of privileges, incorrect syntax of metrics and dimensions, etc …).
To facilitate the recognition of the problem I just updated the add-on. Now, if an error occurs at runtime it is shown in a modal (I point out that Google’s description of errors is not always the actual cause of the problem).
Let me know if this can help you understand the reason for the error.
Thank you!
Hi Michele,
Thanks a lot for your reply and for taking the time. I’m still stuck with the “Report status” overlay and the report doesn’t run. I don’t get the modal with the error description.
As far as I can see, the query doesn’t contain any errors. In the past I have run this same query with success. You can find a screenshot here: https://drive.google.com/file/d/1Ov0dWX3BcQNwRqLoqVqz55CX7Wi8SO7E/view?usp=sharing
Thanks!
Hi David,
thanks for sharing the screenshot. I tried to replicate your report and I noticed that the problem was due to the lack of separation commas between the metrics. Now the add-on works with both commas and ALT+enter (for metrics and dimensions). Try closing the document and reopening it to reload the updated add-on.
Let me know if you managed to solve it.
Thank you!
That makes sense 🙂
I’m used to Google’s add-on, which allows to separate the queries with ALT+enter and I didn’t know that yours didn’t allow it. It’s awesome that you added it, especially with such short time!
I just tested it and it works perfectly.
Have a nice weekend and thanks again!
Thanks to you for the feedback 🙂
From your report, a valid idea was born to integrate that functionality into the add-on as well as a small challenge for me during the lunch break!
Good weekend to you too 🙂
Hi Michele
This looks great，but now I have an urgent problem，when I click run reports，I get an error. Information is :Invalid JSON payload received. Unknown name “title” at ‘body’: Proto field is not repeating, cannot start list. And I can’t create new report. Now I want to know why this is，because it is important.
Thank you very much for your time and look forward to your recovery.
Hi wuhui,
there appears to be an error with the ‘title’ field. What name did you use for the report? It could be due to the use of characters or symbols that the system is unable to decode correctly and therefore does not recognize them at the time of generation.
Hi Michele，
Thank you very much for your reply.My report name is “traffic” in chinese.but I can use it before. Now I change name in English,it also has problem.Could i ask you how can i solve it?Or is it being adjusted and is unusable？
Thank you very much and look forward to your reply.
Hi wuhui,
I did a check and I confirm that the problem was not due to either the title or the Chinese characters, for some reason the payload was interpreted incorrectly in general starting from a certain day. Now everything should work fine.
Try closing the document and reopening it to reload the updated add-on.
Let me know if the problem is solved.
Thank you!
Hi Michele，
Thank you very much for your reply ,Thank you for your help too.
Now my problem is solved，It can create new reports and run reports normally.
In short, your help is very useful.
Thank you for your time .
Thanks to you for the feedback 🙂
Hi Michele.
I seem to be having some trouble with the “2nd check” of the report. This is when it moves from Pending to Completed. This does not run automatically.
I waited for 60 minutes and nothing. I tried changing the “Next scheduled check” and still nothing. I have to keep manually running the report a second time.
Is there something off in my configuration? (The manual run works fine)
Also, I notice that the “Creation date” is in GMT and the “Next scheduled check” is in my local timezone. Does that cause a problem?
I am trying to set this up so it runs automatically once a day. But if the 2nd round does not run, then the report is not populated.
Can you please advise on this?
Thanks.
Anand R
Hi Anand,
I have made some inquiries into the problem you report to me.
Trying to query a couple of reports I can say that the add-on is working correctly and after 1h it performs the second check. However, I encountered a particular error in the logs, the following:
This add-on has created too many time-based triggers in this document for this Google user account.
The add-on automatically creates a trigger each time it runs. The Google Apps Script documentation regarding limits and quotas defines that no more than 20 triggers / users / scripts can be created (https://developers.google.com/apps-script/guides/services/quotas).
If this comes from your Spreadsheet I should try to understand why it occurs. How many reports are you running? How many manual runs? Also you tell me that nothing happens after 1h, do you mean that the status always remains PENDING without updating the ‘Next scheduled check’ date or does it update but the report never becomes COMPLETED?
As a suggestion in the meantime, I give you to create a new Spreadsheet and see if the problem gives you even if you start from a clean sheet. For the rest let me know, so based on your answers I will try to investigate more.
Hi Michele.
Thank you for your quick reply.
And thank you for the note about the limits and quotas. That may have been the reason.
About your question on how many reports and runs –
I have two reports. First I tried to do them in separate Google sheets. Then I thought to try doing both from the same sheet. Still the scheduling and automatic refresh was not working.
As you say, I did do quite a few manual runs to test it out.
Yes. After 1 hour, when I say nothing happens, I mean that the status always remains PENDING. And even though the time shown in the “Next scheduled check” passes, nothing is changed.
I will try creating a new Spreadsheet from start.
A couple of questions:
Would you suggest running both reports from the same spreadsheet? Or separate?
Will a scheduled report run even if there are values in the cells B12-B17? Or should they be cleared? (Like with a macro)
Will it help if I reduce the date range of the data I’m pulling?
When I schedule a report to run, in what timezone is it being considered? Also my question that the “Creation date” in B12 shows Zulu time (GMT) but the “Next scheduled check” shows my local timezone – How does this affect it? (Or no effect?)
I appreciate your support in this. This is a very helpful add-on you have created. I look forward to your suggestions and advice.
Thank you.
Anand.
Thanks Anand,
let me know if with a new Spreadsheet the problem is solved.
I answer your questions below:
– you can use the same sheet for the two reports since the trigger for the next run is unique for all reports;
– when the add-on starts the scheduled function, it automatically clears the contents of cells B12-Z16, so you don’t need to worry about clearing those values by hand;
– reducing the date range can be a valid test to verify that everything works correctly, since queries of too much data can have problems in generating reports even for unsampled ones;
– the timezone is the one in which your Spreadsheet is set (you can check it in File –> Spreadsheet settings…) and the values of the creation date, last update and next scheduled check are only informational labels, have no effect on the script.
Let me know if you need more information and if there are any updates for your reports. Thank you!
Hi ALL,
I wanted to inform you that I have detected an anomalous behavior of the add-on in the automatic execution of the reports which I have now completely solved!
It depended on the limitations of the triggers in the add-ons, anyway GA360 Unsampled is now fully functional with the schedule as well.
Note: the report date is the one provided with the GMT obtained in response from Google Analytics, while the schedule is based on the actual time when the action is performed.
I hope I have given you some good news and that the add-on can help you save time with automatic data extractions 🙂
Hi Michele,
Thanks for creating this add-on. I’m running into a couple issues:
1. Report scheduling hasn’t been working for the reports I’m running (each live in their own sheet). I’ve tried creating new sheets, and have adjusted the scheduling times but none of them are working. There are <20 reports, so I don't think I'm hitting any quotas.
2. When manually running reports, the second check isn't happening. The "Status" gets stuck on "PENDING" until I manually run a check. (Similar to the issue Anand mentioned).
Hi JB,
with the transition from embedded script to add-on to make it usable by everyone, the limits that Google imposes on add-ons have taken over. Since the Google Analytics unsampled reports are not immediate by definition, in order to guarantee a result that is as automatic as possible, the script works with a series of triggers, and the limits on triggers in an add-on are actually hardly exceedable.
In my opinion if you open a new Spreadsheet and replicate the reports it could work correctly since the limits in that case reset immediately. Manually forcing execution should work in all cases.