How do I enable the add-on?
Once installed, the add-on will be available in all your spreadsheets via the Add-on menu, under “Search Analytics”. To enable it for the current spreadsheet, simply click on “Open Sidebar” (which, if all goes well, should open a sidebar).If you’re using any other add-on or script that uses a sidebar, Search Analytics for Google Sheets will override that.
How do I authorize it?
If you’re using the add-on for the first time with your current Google account, you’ll likely see a message requiring you to authorize. Simply follow the process to grant it access to your Google Search Console account required to function properly. Once authorized, the sidebar will reload automatically.The authorization is on a user basis, so you won’t need to repeat the process for other spreadsheets you access.
Requests Use
The first tab within the sidebar allows you to request data from Webmaster Tools / Search Console directly into one of the sheets of your current spreadsheet. Here’s some info on the available settings:
Verified Site
This is a list of the verified properties/websites you can get data for which is retrieved when the sidebar loads. The dropdown is alphabetically sorted and grouped by host, so it’s (hopefully) easy to find what you’re looking for, especially when you have a lot of sites in your account.
Defaults to the first site in the list.
Date Range
Date interval for the data request. Also retrieved when the sidebar is loaded, the add-on checks on minimum and maximum dates available in the Search Console’s Search Analytics.
That means that you can only select the dates available in Search Analytics (typically last 90 days).
Defaults to the first and last day of last month.
Search Type
Web search, Image search and Videos search. Fairly explanatory.
Defaults to Web.
Group By
This allows you to view the data in ways that aren’t possible with the standard web frontend.
If you’re familiar with Google Analytics, we’re basically talking about “dimensions” here (which is actually how they’re referenced in the official Search Analytics API docs).
By default, if you choose not to group your data, you’ll get one row of data with the following information: “Clicks”, “Impressions”, “CTR” and “Average Position”, which are aggregated over the date interval you selected earlier. Grouping your data allows you to get more granularity, with each dimension adding a new column.
With the web app, you can really only group by one dimension at a time: by default, it’s “Queries”, but you can alternatively select “Pages”, “Countries”, “Devices” or “Dates” (“Search Type” isn’t actually a dimension, because there’s no aggregate view, which you’ll notice if you try to select it).
With the API however, you’re able to select any combination of dimensions. Note that if one of the dimensions you’re grouping by is “Query”, then you’ll notice that the sum of all metrics of the retrieved data is less than the same sum without “Query”.
This is actually true in the web version as well, and is explained in the Search Analytics docs: “Very rare queries are not shown in these results to protect the privacy of the user making the query”. Other dimensions do not feature this limitation (as long as “Query” isn’t also added).
Defaults to no grouping.
Filter By
This feature is similar to the one available in the web frontend, except that you can add as many filters as you want.
For each filter you can choose the “dimension” that you’d like to filter by (“Query”, “Page”, “Country” and “Device”), and the type of filtering (“contains”, “equals”, “notContains”, “notEquals”). If you add multiple filters, the result will take into account all filters summed (each filter is added with an AND operator).
There’s no option to use “OR” between filters, since the API doesn’t support this yet (but it will in the future).
Defaults to no filters.
Aggregation Type
As explained in the official docs, this setting allows you to aggregate the data by property (site) or by page.
Simply put, when aggregating by property, if you site shows up with more than one URL for one query, Search Console will only count the data once (one impression, one click, highest position). When aggregating by page, data for each URL is counted individually (so if your site appears on the first page with 7 URLs for a single query, that single query will count 7 impressions).
By default, Search Console aggregates data by property as long as there’s no “Page” grouping in the request. For “Page” groupings, data is always aggregated by page.
Defaults to “auto”.
Rows returned
Fairly obvious. Options are 1000 rows (the API’s default) and 5000 rows (the API’s maximum).
Defaults to 5000 (why would you want less?).
Results Sheet
Where to write the data. Currently you can only choose one of the sheets available in the current spreadsheet.
Defaults to Active Sheet (the sheet where you make the request from).
Clear sheet before getting results
I recommend leaving this on, especially when doing multiple requests. If you want to add charts or other stuff, it’s better to use another sheet instead.
Defaults to checked.
Backups Use
The second tab within the sidebar allows you to backup data periodically from Webmaster Tools / Search Console directly in the current spreadsheet. Most of the available settings are the same as in the requests tab, so refer to the above list for details on those.
There’s also a backup status indicator that shows if there’s a backup running on the current spreadsheet.
Backup Interval
There’s are currently two options available, Monthly and Daily.
For each option, the add-on will first check if the necessary date range is available. If, for example, you choose Monthly, but, due to some delay, the Search Console data is only available up to the 29th (out of 30 days), the add-on will stop and try again the next day.
Currently, the add-on retrieves the data on the 3rd day after the selected backup interval (so for Monthly, the add-on runs on the 3rd of the month to retrieve data for the previous month; if it fails it will try again every day until it succeeds or you get to the next month).
Defaults to Monthly.
Also backup non-grouped data in a separate sheet
If you use grouping in your backups, it might be helpful to also backup the aggregate data somewhere (especially useful when you are grouping backup data by Query, in which case certain data is hidden so you won’t be able to simply add impressions/clicks to get the aggregate numbers).
Using this option the first time creates a separate sheet named “Aggregated Data”, where each backup cycle will create a separate row in the sheet with the non-grouped data (“Clicks”, “Impressions”, “CTR” and “Average Position”). Note that any existing filters are taken into consideration.
Defaults to checked.
Email me backup status after each run
If checked, you will receive an email every time a backup cycle is run, letting you know if the backup completed successfully or not. If there was an error, you’ll be able to see details for that error and, if you choose, you can reply to that email to get support for that specific issue.
Note though that you will receive one email per each backup, so if you have multiple backups that fail in the same day, you may receive multiple emails.
Defaults to unchecked.
One last thing to keep in mind regarding backups is that a sheet named “Backup Log” is generated in the spreadsheet used for data storage when the first backup cycle runs, and it’s used to store messages regarding backup cycles (whether they fail or succeed).
