SEOSHEETS is an official addon of Google Sheets. You can install our addon through the Google Workspace Marketplace.
Contact our support in case you can't find the answer you were looking for in our documentation. We're happy to help! In case you would like to suggest a feature, add or upvote your desired feature here.
Google Search Console is a free web service that allows website owners and webmasters to monitor and manage how their site appears in Google search results. It provides valuable insights into search performance, indexing status, and helps identify and resolve issues that might affect a website's visibility in Google's search engine.
Most users are familiar with the official user-interface provided by Google. However, this interface and its export functionality limits the number of viewable rows to 1000.
Any filtering done when the user interface has loaded is done via client-side javascript on the initial 1000 rows of data. This means you're likely to miss data.
The only way to get around these limitations is using the Search Console API. That's exactly what the SEOSHEETS addon does.
After installing our addon, you can query your search traffic data with filters and parameters that you define. The addon returns zero or more rows grouped by the row keys (dimensions) that you define. You must define a date range of one or more days. The data returned in the user interface is always ordered by clicks and impressions.
Results are sorted by click count descending. If two rows have the same click count, they are sorted in an arbitrary way.
Select a property which you would like to query for search traffic data. You can choose from a list of properties that your Google Search Console account has access to.
There are two types of properties:
sc-domain:example.com. This includes all subdomains (m, www, and so on) and multiple protocols (http, https, ftp).http://www.example.com/. This includes only URLs with the specified prefix, including the protocol (http/https).Each user for a Google Search Console property is assigned one of the following permissions:
To query search traffic data, your account must have at least restricted access to that property.
Select a date range to query search traffic data. You can choose from a list predefined periods or select a custom date range up to 16 months (the maximum Google Search Console keeps data). By checking 'compare last period with previous period', you can compare two periods up to eight months each with each other.
Data from one or two days ago can include non-finalized data, also known as fresh data. Each fresh data point will eventually be replaced with a final data point after a few days. Data from earlier dates will include only finalized data.
Add a sheet that shows the Search Analytics for the specified period.
Add a sheet that displays aggregated clicks, impressions and average CTR for all rounded positions. Based on current query settings.
Add a sheet that displays aggregated clicks, impressions and average CTR for five position groups:
Add a sheet that shows new and lost items in last period compared to previous period. This feature is only possible when you enable period comparisons.
Add a sheet that shows aggregated clicks, impressions, average CTR, average position and monthly differences for each month in the selected period. Based on current query settings. Note: select whole months to get complete monthly totals.
Add a sheet that shows a lookup table for an increase in conversions due to a percentage increase in clicks (x-axis) plotted against a conversion rate (y-axis). Based on total query clicks.
Choose any of the six predefined dimension(s) to group search traffic data by:
You can add multiple dimensions, but you cannot group by the same dimension twice. If no dimensions are specified, all values will be combined into a single row.
These are the query strings that users searched for on Google. Only non-anonymized query strings that returned your site are shown.
Anonymized queries
Some queries (called anonymized queries) are not shown in these results to protect the privacy of the user making the query. Anonymized queries are always omitted from the table. Anonymized queries are included in chart totals unless you filter by query (either queries containing or queries not containing a given string).
If your site has a significant number of anonymized queries, you may see a significant discrepancy between the total versus (count of queries containing some_string + count of queries not containing some_string). This is because the anonymized queries are omitted whenever a filter is applied. Learn more how anonymized queries can cause data discrepancies.
The final URL linked by a Search result after any skip redirects (see below).
Most performance data in this report is assigned to the page's canonical URL, not to a duplicate URL. This means that when a user clicks a duplicate URL in Search results, the click counts for the canonical URL, not the URL that the user visits. Therefore, the Performance report can show zero clicks for a duplicate URL even though your site logs show that users reached that page from Google Search. You can learn the canonical URL for a page using the URL Inspection tool.
The country where the search came from; for example, Canada or Mexico.
The type of device on which the user is searching: desktop, tablet, or mobile. If you have separate properties for your different device types (for example m.example.com for mobile and example.com for desktop), you might not be able to compare metrics unless you use a Domain property.
Groups data by the specific search result type or feature.
| UI name | Description | API value |
|---|---|---|
| AMP articles | A visually enhanced search result for an AMP page. The result can be free-standing or part of a carousel of similar result types. AMP article rich results are also counted as rich results. | AMP_TOP_STORIES |
| AMP non-rich results | A plain text and link search result for an AMP page. | AMP_BLUE_LINK |
| AMP on image result | An image hosted on an AMP page that appears in a search result. | AMP_IMAGE_RESULT |
| Android app | An Android app page result in a mobile search. | SEARCH_APPEARANCE_ANDROID_APP |
| Education Q&As | A Q&A page related to STEM education that focuses on providing answers to user-submitted questions. | EDU_Q_AND_A |
| FAQ rich results | Answers to FAQs that appear directly in search. | TPF_FAQ |
| Job details | An expanded description of a job posting that appears in a search result. Read about measuring clicks and impressions for jobs. | JOB_DETAILS |
| Job listings | A job posting that shows a summary of a job in search. Read about measuring clicks and impressions for jobs. | JOB_LISTING |
| Learning videos | Educational videos that display as a rich result. | LEARNING_VIDEOS |
| Math solvers | An expanded description of a math problem that appears in a rich result, indicates the type of math problem, and links to step-by-step walkthroughs. | MATH_SOLVERS |
| Media Actions | Audio or video content that can play directly in a rich result. Read more about Media Actions. | ACTION |
| Merchant listings | Results that include specific data about a product, such as price, availability, and shipping and return information. Includes all different shopping features (except product snippets), such as Popular products and the Shopping knowledge panel. Applies to listings that appear in general search results (type Web) and Google Images. | MERCHANT_LISTINGS |
| Practice problems | Educational material that appears as a quiz question in search. | PRACTICE_PROBLEMS |
| Product snippets | A visually enhanced search result for products that can include reviews, ratings, price, and availability. | PRODUCT_SNIPPETS |
| Q&A rich results | Answers to questions people commonly ask on Google that appear directly in search. | TPF_QA |
| Recipe galleries | A collection of Recipe rich result. | RECIPE_FEATURE |
| Recipe rich results | A visually enhanced search result for recipes that can include images, ratings, and cook times. | RECIPE_RICH_SNIPPET |
| Review snippet | Excerpts of reviews that can include average ratings, stars, and a review summary. Read about review snippets. | REVIEW_SNIPPET |
| Special announcements | A special announcement structured data element with information about COVID-19. | SPECIAL_ANNOUNCEMENT |
| Subscribed content | A page that appeared in a "From your subscription" rich result. This is a rich result that shows users content that they are subscribed to via Subscribe with Google. | SUBSCRIBED_CONTENT |
| Translated results | Search results in a language different from the query language, from selected sources. | TRANSLATED_RESULT |
| Videos | Videos that appear in either general search results (type Web) or Discover. Includes videos that appear in carousels within search. | VIDEO |
| Web Story | A visual storytelling format built on AMP that enables a user to tap through full screen images and videos. | AMP_STORY |
Groups your data by day. This data can include preliminary data. All dates are in Pacific Time Zone (PT)
By default, only complete days are included (days where we have data from midnight to midnight); if you want to include partial days (for example, today), you must change the date filter to specify the exact starting date.
When date is one of the dimensions, any days without data are omitted from the result list. To learn which days have data, issue a query without filters grouped by date, for the date range of interest.
The number of rows that any query returns can be influenced by several factors.
The maximum number of rows you've selected for a specific query.
The number of rows per request allowed by your plan.
The number of rows available in a property.
Some queries (called anonymized queries) are not included in Search Console data to protect the privacy of the user making the query.
Anonymized queries are those that aren't issued by more than a few dozen users over a two-to-three month period. To protect privacy, the actual queries won't be shown in the Search performance data. This is why we refer to them as anonymized queries. While the actual anonymized queries are always omitted from the tables, they are included in chart totals, unless you filter by query.
The Google Search Console API allows you to extract data up to 16 months back.
The limit of 10.000.000 cells per Google Sheet workbook. When your workbook contains multiple sheets with large numbers of data or when your query returns a very large number of rows, you could encounter an error stating you've surpassed the Google Sheets cell limit.
Which search tab contained the link seen by the user:
Grouping is not supported for search type because the results page layout is very different for different search types. For example, position 30 in image search results might be on the first result page, but position 30 in web search would be on page three.
For a given URL, all click, impression, and position data is stored separately for each search type. So, for instance, an image can appear in both web results and image results, but the click, impression, and position data for that image are recorded separately for web searches and image searches.
As an example, given an image hosted on the page example.com/aboutme.html, you might have this data:
| Search type | Impressions | Clicks | Position |
|---|---|---|---|
| Image | 20 | 10 | 23 |
| Web | 30 | 30 | 7 |