Digital Analytics Expert
HTML, CSS & JS Lover
Apps Script Developer
Problem-Solving Oriented
GA4 Magic Reports is a Google Sheets add-on that helps you to view and edit Google Analytics 4 data more easily in Google Sheets.
Install the Google Sheets add-on
To use the GA4 Magic Reports add-on, you must first add it to your Spreadsheet.
You can go directly to the add-on in the Google Workspace Marketplace and install the add-on: https://workspace.google.com/marketplace/app/ga4_magic_reports/6532356327
The add-on is then available in every Google Spreadsheet under “Extensions” menu.
Create GA4 reports
Select “Extensions” > “GA4 Magic Reports” > “Create new report” in the menu bar. This will display a sidebar on the right side with which you can easily create a report configuration selecting:
- Google Analytics 4 account and property among those linked to your account;
- Metics and Dimensions;
- Metric filters;
- Dimension filters;
- Sorting information.
Then, click on the button “Create Report”, your report configuration will appear in the sheet called “GA4 Reports Configuration” (if this sheet does not already exist, it will be created).
You can go through these steps again to add another report or copy the information in the sheet to a new column and edit it in the sheet.
Not all cells are filled by the sidebar. The fields that are not filled by the sidebar can be entered in the sheet.
Run reports
Go to “Extensions” > “GA4 Magic Reports” > “Run reports” in the menu to run all the reports you have created.
Schedule reports to run automatically
Reports can be scheduled to run automatically. To do this, go to “Extensions” > “GA4 Magic Reports” > “Schedule reports”.
A dialog will open in which you can indicate three things:
- Turning a schedule on and off.
- Choose the frequency, day and time.
The schedule uses the time zone of the spreadsheet.
Quotas
This Google Sheets add-on uses the Google Analytics data API. The use of this API is limited by Google. Here you can see which quotas apply: https://developers.google.com/analytics/devguides/reporting/data/v1/quotas
Report configuration options
Each field in the configuration file contains a note explaining what that feature is and how it can be used.
Terms
You can use this Google Sheets add-on for free. For more information please visit our terms and conditions and privacy policy.
Having trouble with the add-on?
Try to find the solution here: GA4 Magic Reports: FAQs, biases and common errors
Hi there
Thank you for your work
May I suggest an improvement?
As a date range to have the possibility to select “last 30 days” to “yesterday”
Thanks
Once you create the report, you can enter whatever you want in the begin and/or end date cell. You can get yesterday’s date with this =(today()-1).
Thank you Jonathan, I confirm that this is the correct approach, I personally used it also with the previous Google add-on of Universal Analytics 🙂
Hi Claudiu,
thanks for the suggestion!
Now the format NdaysAgo, yesterday or today is also accepted.
Currently you can enter it manually in the date fields. Soon I will implement the possibility to select these values from the sidebar.
Thanks!
Hello, please help.
I am trying to create a report from 01/01/2023 till yesterday
and it’s doesn’t works. A error msg on screen. If i use a real yesterday date or today for ex. 02/27/2023 – it’s works. How i can set it up ?
Thanks.
Hi Artem,
thanks for the report 🙂
I analyzed the code and indeed the mixed date format was not an expected condition, so it was generating an error.
I have now adjusted the code and it works properly.
Thank you!
Hi Michele.
Great product.
I have trouble sorting the output by date. I select the vies, sessions and users as my metrics and date as a dimension. Also i add sort by date descending by i get this JSON error.
What am i doing wrong? Here are some screenshots:
https://imgsh.net/a/X3lJdtE.png
https://imgsh.net/a/aSh99U7.png
Hi Marko,
thanks for the report.
I fixed the problem a few days ago, let me know if everything works fine on your side too 🙂 Thanks!
Working fine,
But I’ve also noticed even if i set the “include empty rows” to TRUE, i still don’t get the values with 0.
Any advise?
Hi Marko,
the result that the add-on shows is exactly what Google provides. Google API right now is in alpha and beta version, I expect that with the next updates they will be improved, for example with rows to 0 as it was on Universal and as you need.
Ciao Michele, grazie per la condivisione!
Non è possibile utilizzare le custom dimension?
Ciao marco,
dovrebbero comparire nell’elenco delle dimensioni della Property selezionata con il nome che hai definito.
Fammi sapere se sei riuscito a trovarle 🙂
Purtroppo no, non le vedo tra le dimensioni! 🙁 Ho provato a inserire anche il customEvent:parametro_nome manualmente nella riga “dimensioni” ma ho errori nella generazione dei report…
Che tipo di errore ti viene restituito durante la generazione del report?
Love the tool! I am having problems using the OR function with the Dimension filters. I am getting a “No rows returned.” message instead of any data. I have tested and can run two dimension filters separately but not together with the OR function.
Hi Eric,
I also quote here the answer I gave you in Google Group.
I was able to replicate the error and I confirm that there was indeed a bug in the code.
Your report was really helpful!
Now the problem is fixed.
Thank you!
Hello! Thanks for this tool. It’s very helpful! Now I have the same bug: Metrics: sessions Dimensions: defaultChannelGroup These are the only parameters. I am getting a “No rows returned.” A week ago, it wasn’t a problem.
Hi Dimitrii, thanks for your feedback! 🙂 Try to check if you also get the same result with this tool: https://ga-dev-tools.google/ga4/query-explorer/
Hello,
On GA4 it’s possible, in the explorer, to apply segments. Could we add this field in the report of the add-on to improve it ?
Hi Maud,
creating segments inside the report query is currently not supported in the Google Analytics Data API, so it is necessary to use the UI.
You can create a segment in the UI and then turn it into an Audience, then use AudienceName as a dimension.
Hi, Michele!
I appreciate your great work! It really saves me HUGE time.
I fount that the scheduled reports are generated in some European time. It would be an even better product if it followed the user’s own time zone, in the same way as the Google Analytics add-on.
Hi nao,
I have made a change to refer to the sheet settings.
Let me know if the schedule execution is now consistent with your timezone. Thanks!
Hi, Michele!
My scheduled reports now run exactly in accordance with my timezone. Fascinating! Thanks again!
Thank you for the feedback! 🙂
My date output is NOT formatted correctly. Instead of expected output of 2023-01-01 I’m getting 20230101 – is this a known issue or does someone have a workaround? I mean I can manually fix after import – but that could get painful.
Suggestions appreciated
Thanks
Hi Jim,
thank you for the report, I have checked and confirm that it is an expected behavior.
The result that the add-on shows is exactly what Google provides.
However, the date format of Google Analytics does not contain the “-“, the same result from GA4 is also found on UA.
Therefore, if the desired format of the date is different, it is necessary to manage it manually on the output.
I hope I took your doubt away 🙂
Thanks!
A great tool but I have run into a related problem to what Jim had. My imported table’s dates are not formatted as dates but as numbers for some reason and when converting, sheets fails to translate the number value to the correlating date value and instead chooses the last date in the built-in date table as the value. If I schedule the report, it overwrites the formatting of the column itself. Is there any way to solve this?
Hi Freddy,
a solution to this inconvenience could be to recall the data in another tab of the Sheet and apply the formatting and formulas there. In this way, the report Sheet will be updated on the next execution and your tab will automatically take the new data.
Hi Michele,
All the other GA4 gsheet plugins format the date correctly (2023-05-16) so there must be a way (like this one: https://workspace.google.com/u/0/marketplace/app/ga4_reporting_for_google_analytics_4/126881055683). I really appreciate your work, but this is the missing piece. Would you mind having another look? Would be appreciated.
Hi balint,
the result that the add-on shows is exactly what Google provides. I also performed a test with the Google tool (https://ga-dev-tools.google/ga4/query-explorer/) and the result is the same.
Anyway, this may be an interesting issue to evaluate as I am avoiding making custom changes so as not to risk invalidating the API result. In this case it could be an exception to be put into evaluation in the near future.
In the meantime, I’ll try to find out whether or not it might be something expected with the next Google releases.
Hi Michele,
I just want to add my thanks for the add-on – its great for what it is an the best solution I have seen. I’d love to add my support for the idea that the ‘YYYYMMDD’ format returned by the API is transformed into ‘YYYY-MM-DD’ by the add-on.
The former is not automatically processed as a date by Google Sheets, where the latter is – meaning any processing of data has to be done on top of additional transformations, rather than working on your add-ons output which is such a shame.
This is how the old Google-created add on worked for UA, and in general everything else I have found in your add-on has fantastic parity to that old UA add-on, that it would be fantastic to have this as well.
Hi Shaun, YYYYMMDD format is the actual one that is provided in response to Google API. I’m trying to understand how impactful it can be to make an adjustment only for the date dimension as the risk is to invalidate future upgrades of the API output by Google. Anyway, I’m working on this integration. For the moment, an alternative could be to manage the dates in a separate tab of the Google Sheet, which retrieves the data from the column where the result appears and converts them into the desired format.
Hi Freddy,
I implemented the feature that in case of presence of “date” dimension in the report, the output will be in YYYY-MM-DD format instead of YYYYMMDD.
Let me know!
Hi Jim,
I implemented the feature that in case of presence of “date” dimension in the report, the output will be in YYYY-MM-DD format instead of YYYYMMDD.
Let me know!
Hi Michele.
It’s a great add-on.
Can I set different [Schedule reports] dates for different files?
Currently, if you set a schedule in one file, the same settings are automatically displayed even if you open the schedule in another file.
(For example, I want to set like this)
Spreadsheet A: every month 1st 6 a.m. – 7 a.m.
Spreadsheet B: every week Thursday 8 a.m. – 9 a.m.
Thanks.
Hi Shin,
thank you for the report, I have checked and confirm that the issue should now be solved.
Let me know!
Hello Michel,
Thanks for this plugin!
I have a question about this plugin because I noticed a difference when importing the cost of my google ads campaigns in GA4 with the import on the spreadsheet and I would like to know where this could come from?
Hi Valentin,
if there is a discrepancy, it may be interesting to investigate, but it cannot be resolved by the add-on because the result that the add-on shows is exactly what Google API provides.
If there is an error in the API, I expect it to be fixed by Google with the next releases.
Hello! Is there a way to edit reports after they have been created? Thanks
Hi Kaitlin,
for the time being, I have not foreseen this functionality. Changes can be made manually in the configuration sheet.
I will however take the request into consideration because it may be something actually useful 🙂
Hi Michele
I’ve also noticed even if i set the “include empty rows” to TRUE, i still don’t get the values with 0.
Please advise
Hi Marko,
the result that the add-on shows is exactly what Google provides. Google API right now is in alpha and beta version, I expect that with the next updates they will be improved, for example with rows to 0 as it was on Universal and as you need.
Hi Michele!
Thank you so much for this awesome extension. I am running into problems with the Search Console data. I get the error “GoogleJsonResponseException: API call to analyticsdata.properties.runReport failed with error: The dimensions and metrics are incompatible.” I am setting the Metrics to “organicGoogleSearchAveragePosition, organicGoogleSearchClicks, organicGoogleSearchClickThroughRate, organicGoogleSearchImpressions”. Is the extension not compatible with GSC through GA4?
Hi Ellie,
you can check the compatibility between GA4 dimensions and metrics in the following official online tool: https://ga-dev-tools.google/ga4/dimensions-metrics-explorer/
You can select them by clicking the checkbox next to their name. Incompatible dimensions & metrics will be grayed out.
Hi Michele,
Is that possible to use in one dimension filters several OR and AND and NOT__AND please ?
Hi Caroline, as the official API documentation currently says, we can specify one type of filter expression. The expr can be only one of: AND, OR, NOT: https://developers.google.com/analytics/devguides/reporting/data/v1/rest/v1beta/FilterExpression?hl=en
I don’t exclude that with API updates from Google something could change in the future 🙂
Hi Michael,
At first, thank you so much for this add-on. It’s really valuable.
However i face an permission problem. If i run a report with ecommerce metrics, e.g. purchaseRevenue, I get this error: “GoogleJsonResponseException: API call to analyticsdata.properties.runReport failed with error: User does not have sufficient permissions for this property. To learn more about Property ID, see https://developers.google.com/analytics/devguides/reporting/data/v1/property-id.”
I’ve granted all the permissions when installing this add-on. How to deal with this?
Hi Willy,
that error is provided by Google answer so it is not recognizing your privileges. This can happen when there are multiple linked Google accounts on a single browser.
Try using a browser where you have connected only the account that contains the Analytics Properties of your interest.
Hi Michael,
Thanks for replying. I understand that’s not recognizing my privileges. However i use 1 account on a single browser. I deleted my cookies and used private modus and re-installed the add-on. Unfortunately this did’t work.
> How to proceed?
FYI
I can pull other metrics and dimensions via this add-on such as sessions, transactions, sessionCampaignName.
Hi Willy,
unfortunately it’s not a problem with the add-on, it’s the authentication process by Google that isn’t managed on the code side.
I don’t know if it can fix, anyway you could try uninstalling and reinstalling it by giving permissions again at install time. Let me know!
Hi Michael,
Thanks for checking. Hmm, i already tried that several times. I will test different things and if its fixed I let you know.
Cheers.
Thanks Willy, let me know, I’m curious. It could also be useful to try with another Google account and see if anything changes. Thx!
Hi Michael,
Is there a way to use last quarter or this quarter for the date range?
Hi Diana,
from the documentation the accepted formats appear to be NdaysAgo, yesterday, or today (where N is a positive integer) in addition to the format YYYY-MM-DD.
You can find it here: https://developers.google.com/analytics/devguides/reporting/data/v1/rest/v1beta/DateRange?hl=en
Thanks Michele!
Can’t we get data from the day (“today”)? Even the updating could be set for “every 2 hours”, the last date is the yesterday date. I’d like to understand. Thank you.
Hi Jessica,
GA4 can take 24-48 hours to process data in platform. This is the time in which the data will be available. Consequently you can pull they after that time (note: data can be considered stable after 72h).
It’s not the add-on’s problem, this is normal GA4 operation..
The 2 hours schedule is a feature that does not currently have a specific effect on the current extracted data (it was inherited from the schedule in Universal Analytics). I don’t exclude that with the next updates of the GA4 platform or the GA4 API it could be useful in the future.
What if I wanted to create a report that has three pages and shows their monthly views, but makes each day a column so I can create a line graph?
I tried using an array of dates, but it looks like I can only compare two days.
Hi Ian,
now you can indicate up to 4 dates to compare (that the Google API limit for GA4). One date in the fields “Start date*” and “End date*” and up to 3 dates in the fields “Compare start date” and “Compare end date”. Separate them with a comma in this field like this (the following values are for example purposes only):
Compare start date: 2023-02-01,2023-01-15,2023-01-01
Compare end date: 2023-02-11,30daysAgo,yesterday
I create a new custom dimension on an existing custom event. How can load all data for the event by this add-on? Currently, I can only load event record after this new dimension defined. I want to load all old and new event record no matter if it has this new dimension value.
Hi ccmoon, a new custom dimensions in Google Analytics do not take effect retroactively. It starts collecting data the moment it is created. Have you tried querying that event without specifying the custom dimension?
Ciao Michele, innanzitutto complimenti per questa implementazione, voglio chiederti, dopo aver impostato le metriche che mi servono e inserendo come “order by” il giorno, per i giorni in cui le metriche hanno valore pari a 0 questi giorni non vengono mostrati. Come posso mostrare comunque quei giorni con valore 0?
Ciao Nico,
grazie per il feedback e per la segnalazione. Quella che hai menzionato è una questione aperta, ho ricevuto altre segnalazioni su questo punto, tuttavia si tratta di un problema che deve essere integrato lato API di Google e che su Universal Analytics funzionava invece come atteso.
Il risultato che mostra il componente aggiuntivo è esattamente ciò che fornisce l’API di Google, senza alcuna elaborazione. Le API di Google in questo momento sono in versione alpha e beta, mi aspetto con i prossimi aggiornamenti che vengano migliorate, ad esempio proprio con l’integrazione che hai indicato.
L’integrazione ad hoc di questa funzionalità invece richiederebbe un workaround che potrebbe minare la solidità della risposta dell’API, senza contare che nel frattempo Google potrebbe rilasciare la funzionalità spontaneamente.
Ho visto in rete che potrebbero esserci delle soluzioni da applicare manualmente ai report per aggiungere date con valore 0, ad esempio qualcosa del genere (non l’ho provato): https://stackoverflow.com/questions/58117628/adding-missing-dates- a-fogli-google
Spero di averti dato un suggerimento utile:)
Grazie!
Hi Michele, thank you so much for making this tool! Is there a way to append new data to the end of the existing data when re-running a report? In other words, opt not to replace data that has already been pulled, and just add new lines to the end instead? If not, do you have any plans to add this functionality in the future? For reference, this is a link to SyncWith’s documentation for a similar feature on their Google Sheets add-on: https://syncwith.com/gs/support/replace-append-synchronize-h8XRQPnhYszSGPgXsK8ZCQ
Thanks!
Hi Claire, thanks for the feedback! 🙂
This functionality is not expected, the user experience of the add-on has tried to keep the pattern of the Universal Analytics add-on. It could be a good starting point for a future integration since the APIs currently have big query/quota limits. For now I suggest creating dynamic named reports and using one sheet to collect the data of all the various reports that will be created 🙂
Sono un fan giapponese del GA4 Magic Reports che hai sviluppato.
Grazie per il fantastico add-on.
Permettimi di farti una domanda.
Configurazione dei report GA4
Vorrei impostare i report su GA4 Reports Configuration e aggiornarli automaticamente secondo una programmazione, ma
c’è un limite al numero di report che posso impostare?
Inoltre, questo limite dipende anche dalla complessità dei filtri, dal numero di metriche e dimensioni?
Grazie in anticipo.
Hi Nakata, thanks for the feedback! 🙂
There is no real limit to the number of reports however, as was also the case with the official Universal Analytics add-on, generating too many reports in a single Spreadsheet may not work. In this case I suggest splitting the reports across multiple Google Sheets 🙂
Is it possible to select “past 7 days” date range?
Hi Shay, you can use “7daysAgo” notation in the date field.
Hi Michele, thanks for creating this great tool. I am trying to recreate a report I had from the UA plugin which had metric = ga:goal7Completions and dimensions = ga:date,ga:Hour,ga:Minute,ga:sourceMedium but keep getting incompatibility issues when i select the relevant custom conversion metric with these dimension, i also tried total users plus a filter for that metric but no joy. Any ideas how I can make this work?
Hi Michele, I have half solved this issue the only outstanding problem is getting the minute metric
Hi Cliodhna, Analytics stores some combinations of dimensions and metrics separately that can’t be queried together. You can check compatibile fields with this tool: https://ga-dev-tools.google/ga4/dimensions-metrics-explorer/
I dont see all my GA4 accounts I see about 12 of them but I dont see the one I am looking for. I have access to the account in GA4 and all under same email. What do i have to do to see my Account? Thanks
Hi jamal,
are you using many Google accounts in the browser?
Very often problems with add-ons (as well as in the official one of Universal Analytics) are related to the use of multiple accounts.
If so, try logging into your browser with a single account.
Let me know!
Hi. This add-on is great, thanks. Is there a way to set the date range to something like “past 7 days” until “yesterday”?
Hi Bryan, thanks for your feedback!
You can use “7daysAgo” and “uesterday” notation in the date field.
Hi. Can I set the samplingLevel?
Hi zx, what do you mean by “set”?
Hi great Tool. Is there a way to get an custom channel reporting as a Dimension?
Hi Corny, unfortunately, custom channel grouping is not still supported with Google Analytics API.
Thank you very much.
You’re welcome! 🙂
Hi Michael, I update my magic report setting to add a newly created custom dimension, then I found I lost all historical data for that event after running the report. How should I config to keep the historical data in my report with the empty value of the newly created custom dimension?
Hi ccmoon, the add-on returns data from the Google API, it doesn’t process it, so it’s a question that doesn’t depend on the add-on but on how you request the report. Try to get the result you want with the Explorer section of GA4 and then replicate the correct configuration in the add-on.
I installed the add-on and I tried to pull out some of my campaigns to Google Sheets and I tackle with a problem…
I have some Facebook campaigns and I see all the data of them on GA4.
When I tried to import the data with the add-on to Google Sheets, it gives me no data.
I didn’t get any problem messages. It happened to me only with Facebook campaigns (I have some Facebook campaigns the add-on import the data to Google Sheets).
Why it happened and how to fix it
Hi Sharon, have you tried to query your report with GA4 Query Explorer tool? https://ga-dev-tools.google/ga4/query-explorer/
If you get the result with that then you can get it with the add-on as well. Let me know!
The schedule does not work for me. I have to run the reports manually. I have the schedule set up properly and it is active
Hi Jamal,
the schedule issue should now be fixed.
Try saving the schedule back to Sheets where it didn’t work.
Let me know!
Hi Michele
First of all Great JOB. Appreciate your help
for query, in Universal GA, I use to schedule sessions and G-Ads data together date wise but in GA4 I can not do the same as it ask for campaignname.
Here is the matrix I use in Universal GA
“ga:sessions,ga:bounceRate,ga:sessionDuration,ga:impressions,ga:adClicks,ga:adCost,ga:CPC,ga:CTR,ga:pageviewsPerSession,ga:totalPublisherRevenue”
Dimention : ga:date
When I use the same in GA4, it return an error?
Any advice if you have for me.
Hi Zack,
you can check the compatibility between GA4 dimensions and metrics in the following official online tool: https://ga-dev-tools.google/ga4/dimensions-metrics-explorer/
You can select them by clicking the checkbox next to their name. Incompatible dimensions & metrics will be grayed out.
Thank You, Michele,
I have one small doubt if you allow me.
When I am trying to change the currency in GA4 via Property and run Magic report, it shows me an error ” Users do not sufficient permission for this property”
Although I am at the admin level in GA and I am confused about what permission level I need in order to schedule my report for GA4 and see the ads cost in “NTD”, “SGD” or “MX$”
Thanks and Regards
@michele , Please help
Hi Zack,
are you using many google accounts in the browser?
This issue is not directly due to the add-on and usually is related to the use of multiple accounts.
If so, try logging into your browser with a single account.
Let me know!
Hi Michele
I am using Sincle Manager account but under that manager account, I have 6 child account.
Problem still going on.
Please help
Hi Zack,
unfortunately I can’t help you with this error, it doesn’t depend on the code from the add-on but on how Google reads your permissions.
The conflict usually occurs when using different Google accounts in the same browser, this problem was also with the Universal Analytics add-on which was the official Google one at the time.
Hi
What option can I add to generate report to new work book instead of same workbook sheet. Like previously in Google Analytics Plugin I was using “Spreadsheet URL” to generate report in google docs url.
Hi Muhammad,
currently this feature is not included in the add-on. As a suggestion you can use IMPORTRANGE formula to view the generated data in the desired Sheet.
Hello, thank you for this wonderful tool. I have a problem and I would like to know how to deal with it. The scheduling function is not working. Currently, I am using the add-on with four spreadsheets, each with a schedule set, but only one sheet is functioning and all other sheets are not updated even when the scheduled time comes. What should I do about this? I am in trouble, so please help me.Thank you.
Hi AYA,
I looked at the code and everything is correct, unfortunately I think the problem depends on a Google update on Google Apps Script engine and the question is still open and not resolved. The time based triggers bug is a known bug, see Here: issuetracker.google.com/issues/150756612
I try to understand if there may be short-term solutions, the previous version of the engine is deprecated and trying a downgroad is not said to be successful in addition to the fact that it may not be stable.
If I find anything I’ll let you know.
Thank you for making this extension! When I try to create a report, the list of GA4 accounts associated with my email address never show up as options in the dropdown menus under 2) Google Analytics 4 Property. I’ve tried to let it work, but nothing ever changes. What can I do?
Thank you!
Hi James,
are you using many Google accounts in the browser?
Very often problems with add-ons (as well as in the official one of Universal Analytics) are related to the use of multiple accounts.
If so, try logging into your browser with a single account.
Let me know!
Hello Michele!
That did the trick. Thank you so much for the response!
Hi, i’ve set up scheduled reports though it seems the report is not updating?
Hi Patrick,
I looked at the code and everything is correct, unfortunately I think the problem depends on a Google update on Google Apps Script engine and the question is still open and not resolved. The time based triggers bug is a known bug, see Here: issuetracker.google.com/issues/150756612
I try to understand if there may be short-term solutions, the previous version of the engine is deprecated and trying a downgroad is not said to be successful in addition to the fact that it may not be stable.
If I find anything I’ll let you know.
Hi, Did you find a solution ? I have the same problem
Hi Edouard,
the schedule issue should now be fixed.
Try saving the schedule back to Sheets where it didn’t work.
Let me know!
It’s a great extension. Thank you very much.
I’m having a problem.
I have set the date range to be from 2022/03/28 to 2023/03/26, and for Metrics, I have selected totalUsers, newUsers, sessions, bounceRate, and screenPageViewsPerSession. For Dimensions, I have chosen isoWeek and deviceCategory.
When I apply the Dimension filter NOT__pagePath__BEGINS_WITH__, I can only retrieve data from ISO33 week onwards, for some reason. If I remove the Dimension filters, I can retrieve all the data within the specified period. I have confirmed that data exists within the specified conditions in GA4.
Is this a bug or limitation in the Google Analytics Data API?
Hi gettan, have you tried to query your report with GA4 Query Explorer tool? https://ga-dev-tools.google/ga4/query-explorer/
It should give you the same result with the add-on. Let me know!
Thank you for your response. I tried using GA4 Query Explorer, but it seems that in order to specify pagePath in the dimension filters, you need to include pagePath in the dimensions being retrieved. Therefore, I was unable to confirm if I could obtain the correct results using GA4 Query Explorer. I would like to include dimension filters only and not include the pagePath column in the retrieved results. This may deviate from the topic of browser extensions, but is this a limitation of GA4?
I apologize. It seems that the issue with GA4 Query Explorer is a known problem.
(https://github.com/googleanalytics/ga-dev-tools/issues/1099)
Does this extension you mentioned relate to this issue? I would like to apply filters using dimensions without including them in the dimensions to be retrieved.
We were able to confirm similar occurrences in LookerStudio as well. It doesn’t seem to be an issue stemming from extensions at least. We apologize for the inconvenience caused. Thank you very much.
Hi Michele, thank you for your great add-on.
I want to ask again for the segments. I currently need to use segments for my reports, and the UA Google add-on has the option to add segments. What is the workaround here?
Thanks
Hi Giorgios, creating segments on the fly (inside the report query) is currently not supported in the Google Analytics Data API. However, you can use the Google Analytics UI to define an audience which specifies a desired segment criteria (https://support.google.com/analytics/answer/9267572). Once the audience is created, you can use the Google Analytics Data API to query audiences using the audienceName dimension.
Hey, I am wondering if there is a way to pull the Attribution-Model Comparison report, into Google sheets?
Hi Baron, this feature is not currently part of Google’s API.
I have a problem with the data which i am exporting. For example, when i export the
Metrics
grossItemRevenue, itemsPurchased
and the dimensions:
itemBrand, itemCategory
The exported grossitemrevenue is in millions. For example, if I’ve sold 10K for a itemBrand, it’s visualized as 10.000.000.000. So, i could divide it with 1.000.000, but, some fields happen to be displayed correctly within the same column. And if i look in my GA4 data, all notations are exactly the same for those values (all in euro’s and thousands). So, what do i do wrong here?
Hi Davey,
that kind of problem shouldn’t be with the add-on but with the Google Sheets settings that misinterpreting the dot and comma. Try changing the locale settings in the menu File –> Settings and then run the report again.
Ciao Michele, grazie per questo add-on!
Ho un problema con la data dopo di fare il run, il risultato ha questo formato: 20230607 ¿sai cosa devo fare per vederlo cosí: 2023-06-07 ?
Saluti!
Ciao Paz, il formato YYYMMDD è quello effettivo che viene fornito in risposta alle API di Google. Cerco di capire quanto può essere impattante effettuare un adeguamento solo per la dimensione della data poiché il rischio è quello di invalidare futuri upgrade dell’output dell’API da parte di Google. Per il momento, un’alternativa, può essere quella di gestire le date in un tab sperato del Foglio Google, che recupera i dati dalla colonna dove compare il risultato e li converte nel formato desiderato 🙂
Ciao Paz,
ho implementato la funzionalità per visualizzare nei report la data nel formato atteso. Fammi sapere!
Hi Michele, is it possible to add event count as a Metric? Thank you.
Hi Aly, what do you mean?
Hi Michele, I have customised Events set up in GA4 that i wish to export as a Metric, but it is not set as a conversion.is there any way i could add these customised Events as a Metric? thank you.
Hi Aly, Events are dimensions while number of events is a metric, you can use eventCount metric based on your specifics eventName.
Hi there,
I am having issues with the scheduling tool. It keeps loading for me and does not save with the schedule. I have to manually run all reports.
Will there also be a possibitlity of running more than 9 dimensions in a nested request? I have data that I need atleast 17 fields to pull. It is for articles on a site that have specific categories tagged to them. Since I am only allowed 9, I am missing crucial data.
Currently requests are allowed up to 9 dimensions: https://developers.google.com/analytics/devguides/reporting/data/v1/basics?hl=en#dimensions
Hi Kelsie,
have you tried with a browser logged on a single Google Account?
One thing that could affect it is having multiple accounts on the same browser (a problem that was also with the official Universal Analytics add-on).
Let me know!
Has anyone gotten the dates to work correctly without rewriting them into another tab? I have to use something like =LEFT(A1, 4)&”-“&MID(A1, 5, 2)&”-“&RIGHT(A1, 2) to rewrite the dates to be usable anywhere. If I try to format the raw output as a date it shows it as 7/16/57289.
Hello, I’m looking for a new add-on for my home. I am currently using this add-on.
How can I set the following conditions in “Dimension filters”?
『How many sessions were received from other sites than my own」
In the Google Analytics add-on, I was able to do this by including “ga:sourceMedium=@URL”. However, I cannot do the same with GA4 Magic Reports.
Please let us know.
Hi kosu, this doesn’t depend on the add-on but on the metrics and dimensions used.
Thanks for the answer. Then how do I set it up in metrics and dimensions?
How to ensure we shouldn’t have any sampling of the data while using customEvent parameters in the dimensions.
You can find here some reasons about GA4 sampling: https://support.google.com/analytics/answer/13331292?hl=en
Scenario:
EventName: 100,000 in the GA4 reports.
Scenario with Event Parameter:
same event Name with Event Parameter showing as 200 count. what could be the reason and how can I avoid this?
It might depend on GA4 sampling, try reducing the date range to see if, with lower numbers, the values become the same.
I don’t see any date range issue as I took one day itself and still finding that as an issue? any alternative to tackle this scenario?
Try to check if you also get the same result with this tool: https://ga-dev-tools.google/ga4/query-explorer/
Absolutely love this tool. Is there still a limit of 128 characters for each regular expressions? Also does the limit of 4096 characters still apply as a maximum expression length?
Thanks for the feedback Kevan 🙂 For your question, the limits are set by Google, the add-on queries the API with what you set it. So, if the Google documentation doesn’t mention it, all that remains is to try to overcome these limits and see what happens 🙂
Thanks for developing such a great plugin. I have a question in using it, if GA4 data is sampled, does this plugin have a sample marker so that I know the data I get is sampled.
Hi Tigerzeng, thanks for your feedback 🙂 Sampling information is not currently expected in the Google API response.
Thank you very much for your response. I apologize for not understanding your meaning clearly. I just want to confirm which one of the following is your intention?
1. The data obtained through the API is not sampled.
2. The API does not have any fields to indicate whether the data has been sampled.
Hi Tigerzeng, I mean point 2 🙂
thank you:)
Having trouble with adding currency code as dimension, is there an equivalent in this tool? or is this a setup issue? have added a custom dimension for currency in my ga4 property but cant seem to find it in this tool’s list of dimensions.
If you created the custom dimension in GA4 after installing the add-on in your Google Sheets, try clicking Refresh Accounts and Properties under the Property combobox in the sidebar of the tool. In this way you update the dimensions and metrics including the custom dimensions.
Is it possible to have a report show the metric by a single day within a Start and end date? For example, I would like to see newUsers between June 1, 2023 – June 27, 2023. I would like to see the total and the individual day count. Right now when I pull the report with the add-on, I only get the total count in the time period.
Hi Ally,
in the report configuration use date as dimension to split users by date 🙂
Ciao Michele, complimenti per il tuo bellissimo lavoro.
Avrei però una domanda per te.
Ho appena creato un nuovo report ma non riesco ad scegliere “Account” e “Property” poiché praticamente si è fermato il sistema con la scritta “loading”.
Non so se sia un problema mio, visto che ho molti account dietro.
Oppure hai già ricevuto una segnalazione del genere?
E mi potresti per caso darmi qualche consiglio per risolvere la situazione?
Grazie in anticipo!
Ciao Natsumi,
grazie del feedback 🙂
Il problema che rilevi solitamente può essere dovuto al fatto che stai utilizzando un browser con più Account Google collegati.
Si può trattare quindi di una questione di conflitti dipendente da Google, tant’è che in alcuni casi succedeva anche con la precedente add-on di Google di Universal Analytics.
Prova a collegarti con un browser ad un unico Account Google, il problema dovrebbe non presentarsi in quel caso. Fammi sapere!
Ciao Michele,
Grazie mille per la tua risposta immediata!
Perfetto, adesso ci provo con solo un account google e ti faccio sapere 🙂
Grazie ancora, buona giornata!
Hello!
I have a question regarding the data coming back from the API, for example the ‘purchaseRevenue’. for every ‘month’ of this year. Some months return a value like 2256.16 (which is correct), and the next month the value is: 87840145499999900 which is supposed to be 878.40 Do you have an idea why this is? The data is not usable like this
Thank you in advance!
Hi Peter,
that kind of problem shouldn’t be with the add-on or Google API but with the Google Sheets settings that misinterpreting the dot and comma. Try changing the locale settings in the menu File –> Settings and then run the report again.
Hi Michele,
Thank you for the answer, it indeed worked! Also thanks for making this app, it is very helpfull!
Have a great day!
Thank you Peter for the feedback! 🙂
Glad you’ve created this app – very helpful.
With UA I was able to query data from GA for today – when I put todays date it won’t allow me to get anything for today to see how we are currently performing. Is this a limitation of the app or of GA4?
Cheers
Hi Mark,
Google Analytics 4 reports (excluding real-time and debugview) are pretty slow to process and display the data. It’s normal to wait 24-48 hours.
Hello, I have been trying to run this tool, but I keep getting erroneous data. For example, the users are grossly under-reported and the campaign-wise Google Ads spends also return just 3 rows
Hi Omkar, check if you are get data from at least 48 hours prior to today’s date. Data from the last 48h may not have been processed by Google Analytics yet. Check also if you get the same result with this Google online tool that queries the same Google APIs of the add-on: https://ga-dev-tools.google/ga4/query-explorer/
Hello! First of all I would love to thank you kindly for this tool. I use it and its amazing!
I wonder if I could ask about the thing I notice.
When using conversion count metrics for events with “-” in the name, report dont work.
My event name: “view_detail-kitchen”
Is that problem on my side, that I ever chose to put “-” in event name?
Hi Martin, right now I don’t have a similar situation to test on, what error do you get? Have you tried to check if you get the same error with this tool? https://ga-dev-tools.google/ga4/query-explorer/
Hello Michele! The error I get is:
GoogleJsonResponseException: API call to analyticsdata.properties.runReport failed with error: Metric names must only contain letters, numbers, or _. Received a metric name = conversions[view_detail-kitchen]
Yes, it is the same error on https://ga-dev-tools.google/ga4/query-explorer/.
I guess that there wont solution any soon then.
Hi Martin, in this case the error comes directly from Google, in fact the other tool I asked you to try is from Google and returns you the same error. It’s not something you (or I) can fix from the add-on since it doesn’t depend on it.
Hi! Thank you for your hard work! I was wondering why I couldn’t export a Full page url field – in fact all the GA$4> Sheets addons had the same error message: incompatible with export or something similar. Thanks!
Hi Emma, thanks for the feedback. I didn’t understand your request, can you give me more details? Thank you!
Hi Michele,
For some reason I am unable to save scheduling the report. The saving is stuck, see here https://imgur.com/a/LsEZLz2 If I try just to enable or disable scheduling, that saves correctly. But I need to change the schedule and even though it shows it as changed, based on the reviews it still runs at the previous set time… Could you check it please? Thanks.
Hi Karel,
are you using many Google accounts in the browser?
Very often problems with add-ons (as well as in the official one of Universal Analytics) are related to the use of multiple accounts.
If so, try logging into your browser with a single account.
Let me know!
Hi Michelle,
Thank you very mutch for your great work!!!
But it seams that your add-on not working for me for some reason. I just want to have a seassons by source/medium by date in google sheet, but there are two problems.
1) If I just set a date as dimensions and sessions as metrics, the report will give me a valid number in sumarize header. But if i manualy count the number in rows of report (show by date), it will give me lower number.
2) If I put to dimensions source/medium field, it will give me much much lower number in header and again, problem above will repeat.
Prints below:
Settings:
https://imgur.com/a/LPaot3t
Test 1:
https://imgur.com/a/W3b38aw
Test 2:
https://imgur.com/a/4ikb3Wa
Am I doing something wrong? Thank you very much! Vitek.
Hi Vitek, thanks for your feedback! Try to check if you also get the same result with this tool, please: https://ga-dev-tools.google/ga4/query-explorer/
Hi Michele. Thanks for quick reply!
Yes, it seems that tool in your reply works exactly same way as Add-on in google sheets.
I tried these two examples.
1) sessions by date
2) sessions by date and source/medium
By the same date range (just 2 days) from 2023-01-01 to 2023-01-02 it will give me these results:
1) 1012 sesions in total but counted per days in responese table it gives me 1013
2) 45 sessins in total, but counted per days and source/medium fileds gives me 49 (abolutely wierd numbers 🙁 )
1012 sessions in total is the correct number showed by orig GA4…
Thanks again!
Hi Vitek, in GA4 a session can be attributed to more than one channel, this could be the reason.
However this is an issue related to the functioning of GA4, not the add-on. The add-on returns data that is provided by Google.
Ok, thanks for tip! I understand.
But it does not explain why the total of sessions is diferent “just by date” versus “by date and source/medium” (1012 vs 45).
Hi,
I have been trying to build a report where I can show revenue and sessions based on our custom channel grouping.
When I look by dimension I actually find the dimension “firstUserCustomChannelGroup:4776529907”. However, I have now compared the numbers to the numbers that are displayed online at GA4 and they do not match for our smaller channels. Did I do something wrong or does this function simply not exist?
Thanks for the help!
Best regards
Melanie
Hi Melanie, try to check if you also get the same result with this tool, please: https://ga-dev-tools.google/ga4/query-explorer/
Hi Michele,
this works. Here I get the right numbers. What does that mean for me?
When I use the Magic Report add-on to request the table in which I want to know only sessions per channel group, the data is correct. They become incorrect only when I want to know the data per date and per host country.
As soon as I set date as dimension, the distribution between the channels is no longer the same. What could be the reason for this?
Hi Melanie, in GA4 a session can be attributed to more than one channel, this could be the reason.
However this is an issue related to the functioning of GA4, not the add-on. The add-on returns data that is provided by Google.
Hello! Thanks for your hard work!
Please help me deal with “Dimension filters”. I’m trying to display some links, but I can’t seem to find the exact solution through regular expression.
For example, I need to display 2 links at once through pagePath: /contact-us/ and /map/, i.e. “pagePath__CONTAINS__/contact-us/” and “pagePath__CONTAINS__/map/”.
Separately, they are displayed, but not together, I have already tried a lot. Tried the regex “|” – does not output pages together. Tried with a comma, also does not work.
Hi Igor,
you have to use regexp instead of contains operator.
Hi Michele, congratulations on the extension. Really appreciate your work on this, it’s been really helpful. I have a question. Can we set up many scheduled runs on the same file? I need the extension to run on the 1st, 11th, and 21st of the month, but not sure if the extension will show an option to have multiple schedules. Thanks for your support.
Hi Henry, thanks for the feedback!
Only one trigger can be set based on the options provided.
In general, Google add-ons don’t allow to insert 3 separate triggers in the same document 🙂
Hi Michele,
It’s a great product. Appreciate your help.
I am facing an issue with using the “Add dimensions filters” for custom dimensions. The report status shows “it’s not a valid dimension,” but when I use the GA filter, I can see the results in the system.
GoogleJsonResponseException:
analyticsdata.properties.runReport API 呼叫失敗 (錯誤訊息：Did you mean customEvent:category_id? Field category_id is not a valid dimension. For a list of valid dimensions and metrics, see https://developers.google.com/analytics/devguides/reporting/data/v1/api-schema )
Hi Nin,
you are probably using the event name directly as a filter, try something luike this: eventName__CONTAINS__category_id
Hi Michele,
Thanks, I’m using that get the result :D.
Hi Michele, great tool but reports scheduling fails to me.
I have set 3 basic reports from 3 different GA4 accounts (yesterday’s sessions, transactions and totalRevenue), and if I run the reports manually, I get the data. But, when I schedule them to run every day at 6 a.m. (it saves correctly); it doesnt seem to work as I don’t find any sheet/data added to the Google Sheet.
I have tried it with only one google account logged at the browser, re-installing the addon, using chrome/firefox/edge… but nothing solved it.
Any Idea?
Thanks!!!
Hi Jose,
have you tried creating the report on a new Google Sheets?
Hi Michele, great add-on, thanks for your work 🙂 I am facing the issue with advertising cost. When i’m blending session campaign name with month and cost metric, the report shows cost like 1 millions per campaig. I reffered costs to ‘advertiserAdCost’. In GA4 explorations costs for google ads campaig shows about 20k$ in this same time period. I have different currency in GA4 so maybe this is the problem with add-on, but i didn’t find the answer and maybe you know how to solve this issue. I would be grateful for help 🙂
Hi Simon,
the result that the add-on shows is exactly what Google provides. Try to check if with the Google tool (https://ga-dev-tools.google/ga4/query-explorer/) the result is the same.
Let me know!
I checked it with Google tool and it shows exactly same result. Maybe you have a knowledge what could be the reason of it?
Hi, I need to create a report that retrieves data for the previous month. I tried using ‘firstDayLastMonth’ for the start date but I am getting an error.
Hi Sarah, what error do you get?
This is the error: GoogleJsonResponseException: API call to analyticsdata.properties.runReport failed with error: Invalid startDate : firstDayLastMonth. startDate must be YYYY-MM-DD, NdaysAgo, yesterday, or today.
Hi Michelle,
Thank you for your response to my previous question. This tool has been very helpful to me.
I have a new question regarding “session source / medium.” When I use the “SessionSource” dimension in the GA4 system, I can see that the results are different in the report (as shown in the screenshot).
If I hope to make them the same, what actions can I take?
report Dimension filters：platform__CONTAINS__web__AND__sessionSource__Exact__facebook
screen shot↓
https://imgur.com/mHPldjP
Hi Michele,
First of all thank you for creating this! It helps a lot!
However, I have some trouble showing the number of purchases for Google Ads Campaigns. I tried ecommercePurchases as a metric (just like te ga4devtools website provided) but it says that it’s not compatible. So I tried conversions:purchase but it showed wrong numbers. What am I doing wrong?
Hi Stefan, thanks for the feedback! 🙂
Your issue does not depend on the add-on but on the correct use of dimensions and metrics in GA4. You can check the compatibility between GA4 dimensions and metrics in the following official online tool: https://ga-dev-tools.google/ga4/dimensions-metrics-explorer/
You can select them by clicking the checkbox next to their name. Incompatible dimensions & metrics will be grayed out.
Hi there,
When I run my report, the information does not populate and I get the error message “TypeError: Cannot read properties of undefined (reading ‘length’)” – what may be causing this?
Hi Alex,
have you checked if a property id is specified within the respective field in the report?
Hi Michele, yes I have specified property id in each of the fields listed – I just tried editing my report and it looked like the issue may have been in Dimensions specified, as changing my Dimensions has caused the report to run correctly – thank you (:
Also just wanted to thank you for the extension as it has made the change to GA4 a whole lot easier! You are the best!
Great! And thanks to you for the feedback! 🙂
Hi Michele, thanks for this extension. It works great for me. I am putting one of my query here:
The ‘sessions’ total is not matching the actual total sessions when I set my dimension and metric as follows.
Metrics- sessions, conversions
Dimensions- sessionDefaultChannelGroup, month, eventName
Do you know why is this happening? It would be very helpful if there is any other way I can get this right. Thanks a ton in advance.
Hi Yuvraj,
the result that the add-on shows is exactly what Google provides. Try to check if with the Google tool (https://ga-dev-tools.google/ga4/query-explorer/) the result is the same.
Hi,
using ‘firstDayLastMonth’ for the start date gives this error:
GoogleJsonResponseException: API call to analyticsdata.properties.runReport failed with error: Invalid startDate : firstDayLastMonth. startDate must be YYYY-MM-DD, NdaysAgo, yesterday, or today.
Hi Sarah, the answer is in the error itself.
The expected values are YYYY-MM-DD, NdaysAgo, yesterday or today instead you used firstDayLastMonth which is not a valid value for the Google APIs.
in the note of the start date cell, it says that it’s a possible option, so it should work.
That’s the note text:
The start date can be entered in multiple ways:
– In yyyy-mm-dd format e.g.: 2022-06-01
– XdaysAgo, e.g.: 7daysAgo
– Other options are: today, yesterday, sundayLastWeek, mondayLastWeek, fridayLastWeek, saturdayLastWeek, sunday2WeekAgo, monday2WeekAgo, friday2WeekAgo, saturday2WeekAgo, firstDayLastMonth, lastDayLastMonth, firstDay2MonthsAgo, lastDay2MonthsAgo, firstDayThisYear
Hi Sarah, you are right about the notes, however GA4 is constantly being updated and to date the Google API documentation, like the Google API error you reported, indicates that those described are the valid formats:https://developers.google.com/analytics/devguides/reporting/data/v1/rest/v1beta/DateRange?hl=en
Anyway, you can use Google Sheets formulas to determine your desired date, i.e. =EOMONTH(TODAY(),-2)+1
ok, thanks for your answer. Yes, I’ve implemented it now with google sheet functions to get the dates.
Hi Michele, I have a question regarding the schedules. I currently have every morning a schedule running for 5 reports(most of them around 1,000 rows, and only of them is bigger, around 3,500 rows). Most of the days the bigger one will only load till row 2,233(exactly this number) and then the rest of the rows will be blank. The report has 3 metrics and 6 dimensions.
Even when I try to run this specific report separate it still stops at that specific row.
The limit of the report is set to 99999999.
Any ideas on what is causing this?
Hi Giorgios,
the result that the add-on shows is exactly what Google provides. Try to check if with the Google tool the result is the same: https://ga-dev-tools.google/ga4/query-explorer/
Let me know!
Hi Michele,
Now the data is not even loading 🙁 Even though at the top it shows the number of rows “loaded”, the table below is empty, and this happens for 3/5 schedules daily.
Hi Giorgos,
have a screenshot to show? In this case I can understand the problem since this is a report that I have never received before, so the problem probably depends on some Google Sheet conflict.
I´m in doubt about how to configurate the currency, because it cames with more number than it should. Can someone help me?
It came like: 282.965.651.999
Should be like that: 282.965,65
Hi Patricia,
that kind of problem (as well as in the official one of Universal Analytics) shouldn’t be with the add-on but with the Google Sheets settings that misinterpreting the dot and comma.
To solve it, try changing the locale settings in the menu File –> Settings and then run the report again.
More details here: https://www.analyticstraps.com/ga4-magic-reports-faq-biases-and-common-errors/
Let me know!
Morning, hope you are all doing well. I was wondering if anyone can advise. When I run the report, all is goof apart from 13 reports ‘Incomplete configuration!’. How to solve that as I am not sure what is not configured. I haven’t had that issue before
Hi Petya,
have you configured the report using the “Create new report” function in the add-on menu? You are probably configuring it manually and the syntax is not the correct one.
Something is totally wrong with the Ext. I cannot pull any data , it says not rows return, also it has issue with new users and views. anyone can help here?
Hi Petya,
The result that the add-on shows is exactly what Google API provides.
Try to check if you also get the same result with this tool: https://ga-dev-tools.google/ga4/query-explorer/
If so it means you are querying different dimensions and metrics than what you are expecting or seeing in GA4 UI.
kindly suggest me How to create segment for pure organic Report, which one will i choose session or user or event segments.
Hi bhishma,
if you want a report of organic sessions, in general, you just need to select “sessions” as the metric and “Default Channel Grouping” as the dimension, then filter the dimension with a value that contains “Organic”.
You can then refine the filter if you want to obtain all the organic values (including for example Social ones) or only those from Search.
hi, in the UA version we COuLd filter with this value
ga:medium=~organic
but now I don’t know how to do this…I have tried but keep getting errors.
Hi Phil,
why don’t you use the interface to build filters? You can open the sidebar both to create a new report and to edit one already created. You don’t need to manually enter values into cells for filters.
Hi Michele,
Is it possible to pull funnel explorer-type reports in Google Sheets?
This was possible in UA > sheets by using segments such as the below and applying them against goal metrics.
sessions::sequence::ga:pagePath==PageA;->>ga:pagePath=~PagesB
Also, is it possible to next and previous page path within Google Sheets? Thanks
Hi George,
creating segments inside the report query is currently not supported in the Google Analytics Data API. It doesn’t depend on the add-on. Anyway, there is a workaround, you can find it here (in “I want use segments in the tool, is it possible?”: https://www.analyticstraps.com/ga4-magic-reports-faq-biases-and-common-errors/
Hi Michele,
I want to sort my data by date. How do I issue the command?
I attempted to enter “date_DESC” in the “order by” field, but an error occurred. How can I rectify this?
Hi mina,
an underscore is probably missing, however you can use the “Edit report” function to edit the report from the interface without doing it manually 🙂
Hi Michele,
I tried finding a solution to my problem in the previous comments but couldn’t.
I’ve scheduled the reports to run on the 1st of each month 6 a.m – 7 a.m. However, I noticed that the reports update but the data doesn’t update.
For example, on September 1st the data I’m supposed to see is for the whole month of August, instead it’s still showing the data for July.
I’ve tried entering firstDayLastMonth as the start date and lastDayLastMonth as the end date, as it’s shown in the notes, but it returns a message instead that my dates are wrong.
Any tips on how to fix that?
Hi Des,
have you tried this solution: https://www.analyticstraps.com/ga4-magic-reports-firstdaylastmonth-lastdaylastmonth/
Note: GA4 data takes at least 48 hours to stabilize, so it would be best to run the report at least from the 3rd of the month.
This solution worked, thank you!
Hi Michele, thanks for the great plugin! Question — does this support Item-level custom dimensions or is it only Event-level?
I created 2 new item-level dimensions this morning but dont see a way to add or filter on them when building a report in Sheets. I’m wondering if I need to wait 24 hrs first before it will show up in your plugin? I can see my existing event-level cdims just fine.
Thanks!
Hi Nick,
the add-on uses Google Data API to retrieve data from GA4. According with Google documentation the Data API can create reports on Event and User scoped Custom Dimensions: https://developers.google.com/analytics/devguides/reporting/data/v1/api-schema?hl=en#custom_dimensions
So I would say that it is a limitation of the API and that the custom dimensions at product level are not yet part of the information that can be queried.
Hi Michele,
Thanks for creating this add-on!
In GA4 I can’t select any date before last 14 months. I was able to extract data with the add-on for a previous period (awesome) but I’m not sure whether this is reliable data or whether it’s sampled. What are the add-on limits regarding historical data?
Hi Elena,
this is a limitation that depends on GA4, not on the add-on. In particular due to the Data Retention of user-level data which can be set to 2 or 14 months.
I provide you with the link to the official Google documentation for further information: https://support.google.com/analytics/answer/7667196?hl=en
Hi!
When I try to run a report with the “newUsers” metric, the report fails and shows this message: “Please remove newUsers to make the request compatible. The request’s dimensions & metrics are incompatible”
Can you help me?
Hi Luan,
your issue does not depend on the add-on but on the correct use of dimensions and metrics in GA4. You can check the compatibility between GA4 dimensions and metrics in the following official online tool: https://ga-dev-tools.google/ga4/dimensions-metrics-explorer/
You can select them by clicking the checkbox next to their name. Incompatible dimensions & metrics will be grayed out.
Hi Michele!
The issue was because I was filtering on the “page location” dimension, which is incompatible with the new users metric. Thanks!
Does the “schedule reports to run automatically” feature work? And if so, how specifically does it work? Can you explain in more detail whether it runs all reports that are in a sheet? Does it create a new tab(s) for each of those reports? I have yet to get it to work at all and am unclear what to even expect if it does work. Thank you!
Hi ss,
the “Schedule” function runs all reports on the sheet at the scheduled time.
If the report already has its own tab (with the same report name) it will update, otherwise a new tab will be created automatically 🙂
Hello! Is there a way to do incremental append sync ?
Thanks a lot great addon!
Hi Niko,
the report returns the request made as a result, so it cleans the sheet and generates a new report each time.
You could try using dates that update automatically with Google Sheets in order to always have updated reports or build a Sheet that retrieves information from different sheets and combines them.
In practice, you can obtain the result by playing with the features of Google Sheets 🙂
Hi, this tool is amazing! I am facing a small issue here, I have a field name on GA which has spaces for eg: ‘Form Visits’. The tool doesn’t seem to handle this directly. I et an error like ‘Metric names must only contain letters, numbers, or _.’.is there a way to get such fields?
Hi Dev,
thanks for the feedback! 🙂
That error you mentinoned comes directly from Google, it’s not from the add-on.
Unfortunately I fear that the only way is to pull all of the conversions (or the metrics you need) and pulling out the row I need by name.
And in general, the only effective solution (also suggested by Google devs) is to rename the conversions that have spaces because it is GA4 itself that does not support them.
Hello, very good complement.
I am currently generating data and I see that I am getting many rows of the same product. Would there be any way to group, for example by date + itemId? This would considerably improve the number of rows returned. Thanks and greetings
Hi Hector,
the result that the add-on shows is exactly what Google API provides.
Try to check if you also get the same result with this tool: https://ga-dev-tools.google/ga4/query-explorer/
If this is not the case, I will try to understand the difference taking inspiration from your excellent suggestion, otherwise it is better that the add-on does not interfere with the data, so the best solution is to manage the groupings directly with the Google Sheets formulas.
Is there a way to filter for null values? I want to create a report that includes only events where a certain dimension contains a blank or null value. It looks like the GA4 API has support for this now: https://developers.google.com/analytics/devguides/reporting/data/v1/rest/v1alpha/FilterExpression#Filter.FIELDS.null_filter
Hi Claire,
this feature is currently not available yet. I’ll take the cue based on updating the GA4 API for integration in an upcoming add-on release. Thank you!
Thank you very much for letting us use Ga4 Magic Reports for free. This is very important for small sellers like us. I have a question: how to get all the names of itemName? When I received it, it was limited to 100 characters.
Once again, thank you very much
Hi Tony, thank you for the feedback! 🙂
Limit to 100 characters is a GA4 limit (it doesn’t depend on the add-on).
The length of the event parameter value will be automatically cut off by GA4 if it exceeds 100 characters.
Hi there
Thank you so much for your work and effort.
just a little review : filtering of dimension and metric are case insensitive, it would be better to choose my case sensitivity.
Thanks you again for your amazing work.
Thank you Randa, great observation 🙂
I agree that the default case sensitive helps filter out those values where capitalization is present. I’m trying to understand how to integrate this functionality to avoid invalidating reports for those who are currently using the tool with the default case insensitive mode.
Hi there. Thanks for your convenient tools for GA4 🙂
However, i got this error message when I tried to run the report.
GoogleJsonResponseException: API call to analyticsdata.properties.runReport failed with error: Invalid value at ‘limit’ (TYPE_INT64), “”
Please tell me what should i do to solve this issue.
Hi Ran
thanks for the feedback!
Check if you entered a strange value inside the ‘limit’ field, such as a space.
Hi, I am not sure what’s happening but the refresh doesn’t seem to work anymore. It just shuffle for hours. Haven’t been able to refresh in the last 5 days.
Hi jen,
no updates to the add-on have been released since last September, however in the last week I have received other reports from users who have had some problems.
Most likely this is a temporary problem with the Google servers that host the add-on. All that’s left to do is wait for Google to unblock, in the meantime I would try resetting the schedule and reactivating it to check if the refresh starts again.
Hello Michele,
many thanks for the excellent work within GA4 data import to Google sheets. We are just in the decision phase which add-on to choose for our clients. We note that your tool is for free for now. We are considering possible security risks and possible pricing for the future. Of course, we are willing to pay for the tool in the future if it fulfills the functionality. Please could you comment and allay our concerns under security risks ? 🙂
Thank you for your reaction.
Good luck
Michail (CZ)
Hi Michail,
thanks for the feedback and nice to meet you.
The add-on is free and usable by the whole community, I made it to fill a hole due to the absence of the official Google component which, to date, even if it is there, from the reviews I would say that the need to plug that hole with GA4 Magic Report remains 🙂
Regarding security risks I am available to answer you, what concerns do you have about that?
Let me know. Thanks!
M
Hello Michele,
thank you for the quick and kind reply 🙂 We agree that the hole in Google’s official add-on is a pain in the ass. So we’re happy for creators like you. We have larger clients in the portfolio and we want to be clear within their cookie consent management and GDPR. So the question is whether the providing of data to your application carries any requirements to modify the conditions for the protection of personal or user data on the our clients side. We just want to make sure if it is legally safe under GDPR or cookie management. Your application is recommended by our no.1 analytics guru Jan Tichý on the Czech market. We are convinced that yes, we just would be interested in your comment.
Many thanks for replay and have a great day
Michail
Hi, thanks for the great add-on!
I just would like to know if there is a way to run the report from a custom button within the sheet? Kind of like a macro button, as an alternative to going to the Google Sheets menu ‘Extensions > GA4 Magic Reports > Run report’…
I’m guessing I would need to code this in Script Editor, but what is the function I should call to run the reports?
Alternatively, is it possible to auto run the report when the Google Sheet is opened?
Thanks,
Jamie
Hi AJ,
neither action is possible. The add-on code cannot be called from another script editor, for the same reason (and more) the functions cannot be executed when opening the add-on, only the one to add the menu in Extensions.
Considering that data in GA4 does not update very frequently and is stable after at least 48 hours, one could configure the schedule to update reports once a day, either in the morning or in the evening. In this case, opening the Google Sheet would already have the updated data.
Thanks for the reply and confirming neither is possible, it is still a great extension and you make a good point about the update rate of GA4 data, so I will just configure a schedule to update the report once daily – thanks again!
Hi AJ,
Great extension for GA4 and my preferred option over the official GA4 G sheets extension.
I wondered if there is a way to add offsets into the report, perhaps by adding as a row into the report configuration?
Thanks,
Gary
Hi Michele.
Im getting an error when trying to pull data for purchaseRevenue. If I try to extract as USD works perfectly but the default currency is BRL and then it gets error
GoogleJsonResponseException: Falha na chamada de API para analyticsdata.properties.runReport com o erro 0th request: Future currency exchange rate not exist. from_currency=BRL, to_currency=USD, end_date=2024-02-29; during GetSubquery; while processing ProcessStage CURRENCY_EXCHANGE_53 with label: CURRENCY_EXCHANGE_53(field_widener_line_2953) with debug: go/nodeserialize name: “CURRENCY_EXCHANGE_53” origin: “field_widener_line_2953” output_view { date_ranges { start_date: “2024-02-01” end_date: “2024-02-29” start_time: “00:00:00” end_time: “00:00:00” } dimension_filters { or_group { expressions { filter { field_name: “event_name” string_filter { match_type: CONTAINS value: “purchase” } } } expressions { filter { field_name: “event_name” string_filter { match_type: CONTAINS value: “cadastro_script” } } } } } metric_filters { filter { field_name: “conversions” numeric_filter { operation: GREATER_THAN value { double_value: 0 } } } } fields { stage_name:
Hi Pedro,
the error appears because you are querying a future date and there is no exchange rate for a date that is not yet there.
Hi Michele, Is there any way to avoid this error? Because of that it’s impossible to make automatically refreshing report for revenue data.
Hi Damian,
this is a Google error; you cannot query dates that don’t yet exist.
Anyway, you can create a formula on Google Sheets with the date that updates automatically.
Creating a filter using the dimension “Nth hour” doesn’t appear to work properly, I am unable to get any inputted value to work, and when Exclude is selected, all data is always returned. When Include is selected, then no data is returned.
Hi Jeremy, try to see if you get the same problem with this tool: https://ga-dev-tools.google/ga4/query-explorer/
If not, can you write here the exact details of the filter you are using so that we can try to recreate the situation? Thanks!
Hi Michele, I was able to resolve the problem with the help of the Google tool you linked to. The issue was that the Google Sheet add-on doesn’t include the two leading “00”s for Nth hour as Google’s tool does. For example, “0001” is how the Google tool reports it, and the Google Sheet add-on reports it as “01”. Changing the regex in the Google Sheet filter to include the leading 00s resolved it. For example, I was using “(0[0-9]|1[0-5])” (00 to 15) and I changed to “(0{3}[0-9]|001[0-5])” (0000 to 0015). It must be that the Google Sheet add-on removes the leading 00s after the query.
Thank you Jeremy for the feedback, I’m glad it was resolved. I will check based on what you told me. The Google component doesn’t change anything, it reports what the API provides. However, it could be that it is Google Sheets that is removing the 0’s. I will investigate 🙂
Hi there,
I’m unable to find certain GA4 reports in google sheets, while they exist in google analytics itself, and can linked to looker studio. They are newly added in google analytics, so I clicked the refresh button on GA4 report in google sheets several times and waited several days, while I’m still unable to see and add them.
Is there a possible solution for it? Thank you
Hi Emily, the refresh button under Property dropdown updates the list of Accounts and Properties. If it doesn’t appear, it means there is some inconsistency at the type and permissions level.
Hi Michele,
I’m curious if there is a limitation of reports? I’m unable to find all the recent new ones I added to google analytics.
Thank you
Hi, is there a way to transfer ownership & how can I achieve this?
Hi, what you mean with “transfer ownership”?
Michele,
Great product.
By the way, how to delete a report?
Best L
Hi Laurent, thanks for the feedback!
You can delete a report manually just cleaning data in the column or removing the column in the Google Sheets (and, if you want delete the relative tab).
hi sir great add on, thank you very much.
now a q: how can sort the report in specific cells?
like showing campaigns names in cell a1 and num of purchases in d4 for example?
Hi Ofer, thank you for the feedback 🙂
About sorting, you can use the appropriate function in the add-on’s navigation bar. To move values to other cells, on the other hand, you can use the native Google Sheets formulas. The advantage of having the reports in Google Sheets is just that. One suggestion I can make is, for example, to use another tab to manage the date display as you wish, taking the information from the tab where the report was generated.
Hello Michele,
and thank you very much for your solution! You make the life of the community really convenient.
Our team wants to use this solution, but we have some concerns. We would be happy if you could help clarify the situation.
I would like to know if you have any access to the data uploaded to Google Sheets? We need this to understand if we are then passing any data from our account to third parties.
In essence, our question overlaps with what was previously asked by Michail Svanidze about GDPR in this comment
https://www.analyticstraps.com/ga4-magic-reports-google-sheets-add-on/#comment-3920
Hi Ksenia,
the data are visible only to the user who uses the add-on and accesses GA4 data.
No GA4 data is shared or visible to other users.
If you have any other concerns, please let me know 🙂
Ciao, complimenti per l’addon veramente utile per riuscire ad automatizzare scarichi di dati da GA4. Volevo chiederti un informazione sui filtri. Sarebbe possibile e in che modo creare dei filtri annidati? Per esempio creare un filtro simile: customChannel=’Pippo’ AND (pagePath=’Pluto’ OR pageLocation=’Paperino’). Grazie mille
Ciao Alessio,
grazie a te del feedback! 🙂
In questo caso particolare suggerirei due strade:
1) eseguire due report (uno con ‘Pippo’ AND ‘Pluto’ e l’altro con ‘Pippo’ AND ‘Paperino’) ed unire i dati in un terzo tab con le formule del Foglio Google;
oppure…
2) eseguire un report dove tra le dimensioni c’è il customChannel (con filtro su ‘Pluto’ OR ‘Paperino’ e nessun filtro per ‘Pippo’) e filtrare i dati del risultato dove contengono ‘Pippo’ con i filtri del Foglio Google.
In entrambi i casi, una volta impostate le formule o il filtro avrai una struttura dove all’aggiornamento dei dati con nuovi timeframe i dati del tab di aggiorneranno di conseguenza.
I believe the add on is down today. I’ve trying running a report, but it can’t complete.
Thanks for your hard work.
Hi Javier,
I tested it and it seems to be working correctly. It could be a momentary problem with the Google server on which you are using the instance. Alternatively, verify that you are logged in with only one Google Account to the browser. Many problems with Google add-ons in general result from conflicts between multiple Accounts in the same browser. Let me know!
Hey Michele,
The add on works great! Something I’m trying to do is have new report results be appended to the existing report rather than have the report generate or overwrite the existing report
Hi Gil and thank you for the feedback,
this behavior is not provided in the add-on, however you can try managing the generated sheets and native Google Sheets functions (or scripts) to collect the various report in an additional sheet that you create manually.
Hey Michele,
Is it possible to change the aggregation of a metric?
In example: if I have a custom dimension in an event and I need count different instead of count. Is it possible?
Hi Gustavo,
This could be the next feature to be integrated, I opened this survey to get new insights and needs 🙂
https://www.linkedin.com/posts/michelepisani80_googleanalytics4-ga4-digitalmarketing-activity-7207283149851025409-VOWF?utm_source=share&utm_medium=member_desktop
Hey Michele, after using this great tool for few month i suddenly recieving this error:
GoogleJsonResponseException: API call to analyticsdata.properties.run
Report failed with error: Invalid value at ‘limit’ (TYPE_INT64), “” Invalid value at ‘currency_code’ (TYPE_STRING), 10000
I didn’t change anything in the parameters, what can cause this issue?
Thank you very much!
Hi Koral,
I released an update yesterday, I added the field “Offset”: https://www.linkedin.com/posts/michelepisani80_googleanalytics4-ga4-digitalmarketing-activity-7206905690987077633-TrmJ?utm_source=share&utm_medium=member_desktop
It may be that your report was run during this release.
If you try to run the report again do you still get the error?
Let me know. Thank you!
I am also having this issue. It seems to add an additional offset field to the Report Configuration sheet. It fails all reports.
Full error is: GoogleJsonResponseException: API call to analyticsdata.properties.runReport failed with error: Invalid value at ‘limit’ (TYPE_INT64), “” Invalid value at ‘currency_code’ (TYPE_STRING), 32
Hi Tom,
this may have been due to a conflict after the last update.
In this case you just need to remove a row where it says “Offset” or create a new Sheet and then Create a new report.
Let me know!
Hello Michele, thank you for the add!
Today I suddenly encountered an error on startup:
GoogleJsonResponseException: Calling the analyticsdata.properties.runReport API method returned an error: Invalid value at ‘limit’ (TYPE_INT64), “””
Everything was working fine before and I didn’t make any changes. Maybe google has released an update. Can this be fixed?
I found the reason 🙂
Great! 🙂
Hi Igor,
I released an update few day ago, I added the field “Offset”: https://www.linkedin.com/posts/michelepisani80_googleanalytics4-ga4-digitalmarketing-activity-7206905690987077633-TrmJ?utm_source=share&utm_medium=member_desktop
It may be that your report was run during this release 🙂
Hello there, thank you for a great tool.
It seems like I can’t access subproperties, is it due to incorrect settings on my part?
Thanks in advance
Hi PM GRIMOIN, try to click on “Refresh Accounts and Properties” under Property field in the sidebar. After few seconds the property list will update and you will also see the subproperties 🙂
Hello,
Just curious why the GA4 magic reports numbers are not matching what I see in Reports or Explore in my actual GA4?
Hi Jacob,
check whether the timeframe, dimensions, and metrics are the same.
Let me know, if you can send some screenshots and/or also try with the Google dev tool if you get the same result: https://ga-dev-tools.google/ga4/query-explorer/
Hi Michele, I emailed you some screenshots. Can we chat through there?
Hi Jacob,
I replied to your post and email.
Hi I would like to know how to use “order by.” I want to have the report with dimensions “year month and day by ASC.
What value should I input in order by section.
Open the sidebar to create a new report or edit an existing one, after which you can configure the various fields like date, dimensions, metrics, … then just click on “Add sorting” and set:
Year Ascending
AND
Month Ascending
AND
Day Ascending
All was good now from my previous comment. However, how can I filter i the magic reports for country = united states? I tried “country__EXACT__United States__AND__sessionDefaultChannelGroup__CONTAINS__organic search” but it is not matching what I see in Reports.
Hi Jacob,
I checked on a small property of mine and the numbers match. Try to see if the ratio in the Sheet has a sample rate. Also check whether or not you get the same result with this tool: https://ga-dev-tools.google/ga4/query-explorer/
Let me know!
Hi Michele
I noticed that even if I set the “include empty rows” to TRUE, I still don’t get the values with 0.
Did I miss any other setting in needed?
Hi z, this behaviour depends on the Google API, try seeing if you get the same result with this tool: https://ga-dev-tools.google/ga4/query-explorer/
Hi Michele,
Fantastic work here. I use the schedule on around 100 sheets and was wondering if there was a way to turn it off without having to go into each one individually.
Hi Michele,
Thanks for your product, just one quick question, is there any way we can add a trigger to launch it from apps script? Le me explain, I have a huge sheet that uses the data from analytics imported by you, and then it has a bunch of apps scripts to do certain kind of stuff. The issue comes when both (my scripts, and your extension) run at the same time, so then it runs out of memory and your extension stops mid-way of importing data. If I can launch your “run reports” command from a function, I can tell the others to do not run while yours is running for a few minutes. It will also help others that may need it in shorter periods than just 2 hours.
Hi Andres,
unfortunately, Google add-ons do not have this possibility, however the execution time, once it is started the first time, keeps the same time always (unless you delete the schedule and create another one).
Hi, Michael
I dont know what im doing bad, the error that appears to me is the following:
GoogleJsonResponseException: No se ha podido llamar a la API analyticsdata.properties.runReport; error: Did you mean cm360AdvertiserName? Field customEvent:step_name is not a valid dimension. For a list of valid dimensions and metrics, see https://developers.google.com/analytics/devguides/reporting/data/v1/api-schema
Before everything was working, but not anymore!
Thanks in advance
Hi Adam,
verify if the property that Event-scoped Custom Dimension is registered for the Property you using.
If it is not, it cannot be questioned in that way.
Alternatively, you can query event names and filter by name.
Let me know!
Hey, Michael!
Now looks partially solved, but the new error that comes up is the following:
GoogleJsonResponseException: No se ha podido llamar a la API analyticsdata.properties.runReport; error: Invalid value at ‘limit’ (TYPE_INT64), “”
I put 1000 in limit, i ve tryed with 10.000, changed the format of the cell to a number, etc. But still not working
Thanks a lot for you help!
Michele hello how are you? Do you know if an update was made today? because it started giving an error and Google blocked the application itself.
Hi Jonathan,
the problem is the update I did today. It has nothing to do with GA4. I added a function to send report by e-mail, and the app needs specific scopes to be verified by Google.
Within a few days, hopefully even today or tomorrow, as soon as Google verifies everything the error will disappear.
Sorry for the inconvenience, we are in Google’s hands at the moment, there is nothing more we can do.