Analytics Canvas Google Sheets Add-On for Google Analytics
In just a few simple steps you will see how easy it is to get data from Google Analytics, create rich dashboards, update them in seconds, replicate them across accounts and views, and share your dashboards with others.
Part 1 – Get the add-on
From any Google Sheet, select Add-ons > Get add-ons and search for the Analytics Canvas add-on.
Click on the Analytics Canvas logo to open the app description and preview, then click the + FREE button to get the add-on and make Analytics Canvas available to your Google account.
An authorization window will pop-up that outlines the permissions required to connect your Google Analytics account with Google Sheets.
To grant permissions, click Accept and once your authorization is completed, you will be able to access the Google Analytics accounts and views that are associated with your Google account.
Once the app is authorized, a confirmation alert will display to confirm the installation was successful. Now simply go to Add-ons > Analytics Canvas > Get Google Analytics Data to start pulling data into your sheet.
Part 2 – Load An Example Dashboard
The first time you select Get Google Analytics Data you will be presented with a graphic that outlines the three steps to creating, building and sharing your dashboards.
You will see a Load an example dashboard selection box. When it is checked, it will pull the default example dashboard into your sheet. Click Get Started then select a Google Analytics view and your dashboard will automatically generate.
You will see both the formatted data and the query results below the dashboard on a new sheet called “Example Dashboard 1”. In some cases, the data that feeds the charts in the example template has been transformed slightly.
A new worksheet called “Example Dashboard” is created during the process. You can keep it there, or delete it as you create your own reports and dashboards.
Part 3 – Create Your Own Query
Click the Create Query button in the app side-bar to generate a new query. You will be prompted to select a location to insert the results, then a query window will pop-up allowing you to edit your new query or edit existing ones.
Click any of the fields below the query name to generate the configuration window on the right hand side. When you have created one or more queries, click the Save Only button to save the query to your Query Sheet, or click Save and Run to save your query and have the results written to your Google Sheet.
Note: if you started off with an example dashboard or another spreadsheet with queries, you will see them in the Query window and in the Query Sheet. You can delete them by removing the row in the Query Sheet or by clicking the “X” next to the Query name in the Query Window.
Part 4 – Updating Queries & Dashboards
There are two ways to update your queries:
1) To refresh your query directly by editing the Query Definition Sheet, simply make your changes, call up the Analytics Canvas add-on if it is not already open, and click Refresh Data in the Analytics Canvas sidebar menu.
2) To refresh your existing queries, click Edit Queries in the Analytics Canvas sidebar menu to call up the main app window, update queries as required, then select Save and Run.
Part 5 – Share Your Dashboards
There are two ways to share your dashboards: with collaborators or with the world.
Share with Collaborators
To share your dashboard with people within your organization or with an agency partner you are working with, simply click the Share button on the Google Sheet, just as you would with any other document.
Keep in mind that:
- Collaborators can see the data in your Google Sheets
- Collaborators with access to Google Analytics accounts can update your dashboard with their data
Share with the World!
Your dashboard is a work of art and you want others to be able to use it to analyse their websites, but you don’t want to share the data from your site. The Analytics Canvas add-on makes it easy to generate a template and share your creation with anyone.
First, using the Share As Template button, the add-on will remove all of your private data, leaving only the query, charts and related formatting. Give your sheet a name, then click Make copy for sharing. A new file will be created in your Google Drive.
Next go to your Google Drive folder, select the file and click share. A dialogue box will open where you can configure how you want to share your dashboard. To allow anyone to use your template with their data, set the permissions to Public and “view”. This will require anyone you share the URL with to create their own copy for editing.
To update the template, a recipient simply needs to open the file, add the Analytics Canvas add-on, and select Refresh Data.
FAQ
Are there any bugs I should be aware of?
We have become aware of an issue that is apparently related to how Google Sheets is handling timezones for all Google Sheets . When a sheet is very first created, and the user has not edited it in any way, dates written into the sheet are being converted to a different time zone by sheets, potentially resulting in dates being offset by a day. This only affects individual date (day level) queries, not yearly, monthly or weekly, and the data returned is correct- the day labels are incorrect due to the conversion. To eliminate this issue, edit any cell in your spreadsheet and then press “Refresh Data” – this only needs to be done when first creating a sheet, once the sheet is edited the issue no longer occurs. Using the add on within any existing sheet that has been edited by the user in any way will not have this issue.
Are there any limitations to the free version of the Analytics Canvas add-on for Google Sheets?
The free version of the Analytics Canvas add-on for Google Sheets allows you to:
- Access the core reporting API
- Make 10 queries within a sheet
- Return 10,000 rows per query
- Query multiple Google Analytics Accounts and Views in a single sheet
- Create and share templates
- Create dashboards and update them with data from multiple accounts
What are the benefits of Analytics Canvas desktop over the add-on?
Analytics Canvas desktop is a full featured solution designed for analysts and IT professionals who need to connect to Google Analytics, databases and files, automate processes, and manage a number of reporting processes. For GA reporting, benefits of the desktop solution include:
- Partitioning to eliminate or reduce sampling
- Access and analyze hundreds of profiles at once
- Visualize and document your analysis for sharing and collaboration
- Unlimited number of queries within a canvas
- Unlimited rows of data returned in your query results
- Access the multi-channel funnel API
- Upload cost data
- Connect multiple data sources including databases, Google Sheets, and text files
- Schedule one or more canvases to fully automate your reporting processes
What is the Master Time Period?
The master time period allows you to set a time period that will apply to all queries within your sheet. You can still edit the time period within each query independently even when the master time period is set.
Can I schedule updates instead of clicking “Refresh Data”
Scheduling is by far the most requested feature and is one of the drivers of success for our desktop application. However there is a limitation imposed by Google on Google Sheets Add-ons which prevents developers from executing time-based triggers. As a result, we are unable to offer real-time or scheduled updates of your Google Analytics queries within Google Sheets.
I’m still having difficulty or I found a bug. Who do I contact?
Our support team is happy to answer your questions via email. Please write to support @analyticscanvas.com and we will get back to you as soon as we can, usually within 1 business day.
My data is sampled, what should I do?
Sampling occurs when your query results includes more than 500K visits. Try adjusting your time period to include less visits in each result. If that works, create multiple queries and join them together to get your unsampled set. Alternatively, Analytics Canvas desktop is able to detect sampling and partition results to reduce or eliminate sampling from your data set.
Why can’t I see a certain dimension or metric?
Analytics Canvas uses the Google Analytics API to fetch data and bring it into your spreadsheet. We make every effort to include all available attributes as soon as they are released to the API by Google. Newly released dimensions and metrics can take around 6 months to be released to the API. If there is something you’re expecting to see in the application that is not there, please reach out to us and if possible, we will include it in an upcoming release.
How do I replicate a dashboard for a different Google Analytics account or view?
Simply click Switch Website in the add-on sidebar and when prompted, select the profile you’d like to see in your dashboard. Your current dashboard will be cleared of all data, then the dashboard and all related query results will automatically update with data from your chosen profile.
I’m using a browser other than Chrome and things aren’t working
For the best possible experience with the Analytics Canvas Google Sheets add-on, we highly recommend using Google Chrome. We are working to resolve usability and styling issues in other major browsers and hope to have them fully supported shortly.