Installation
SEO Sheets is a Google Sheets add-on. Install it from the Google Workspace Marketplace, open any spreadsheet and go to Extensions → SEO Sheets → Show sidebar. The sidebar opens on the right.
The first time you run it, Google asks you to grant access to Search Console and Google Sheets. SEO Sheets uses that access to read your Search Console data and write it to the sheet you have open.
Can't find an answer here? Mail hello@newnorth.nl.
Search Analytics
Google Search Console shows how a site appears in Google Search. Its interface, and the export in it, stops at 1,000 rows. Filters in the interface run on those first 1,000 rows only, so you are likely to miss data.
SEO Sheets queries the Search Console API instead. You choose the dimensions, filters and date range; the add-on returns every matching row, grouped by the dimensions you picked. Rows are sorted by clicks, descending. Rows with equal clicks come back in no fixed order.
Property
Pick one of the Search Console properties your Google account has access to. There are two property types:
- Domain properties, for example
sc-domain:example.com. These cover all subdomains (m, www and so on) and all protocols. - URL-prefix properties, for example
https://www.example.com/. These cover only URLs that start with that prefix, protocol included.
Your account needs at least restricted access to a property to query it. Owners, verified owners and users with full or restricted access can all use SEO Sheets; unverified users can't.
Period
Choose a preset period or a custom range of up to 16 months, the maximum Search Console keeps. Tick compare last period with previous period to put two periods of up to eight months each side by side.
Data from the last one or two days can be fresh, non-final data. Google replaces it with final numbers after a few days. Older dates only contain final data.
Reports
Every report you tick is written to its own tab, built from the same query.
Search Analytics overview
All rows for the selected period, with deltas when you compare periods.
Rank distribution
Clicks, impressions and average CTR per rounded position.
Grouped rank distribution
The same totals for five position groups: 1–3, 3–10, 10–20, 20–50 and 50+.
CTR curve
Average CTR per position, so you can see how your click-through rate compares across rankings.
New and lost
Items that appeared or disappeared in the last period compared to the previous one. Needs period comparison.
Month by month, week by week, day of week
Clicks, impressions, CTR and position aggregated per month, per week or per weekday. Select whole months for complete monthly totals.
Hourly performance
Performance per hour for the last ten days, the hourly data Search Console makes available.
ROI
A lookup table of extra conversions for a percentage increase in clicks (x-axis) against a conversion rate (y-axis), based on total clicks.
Cannibalization
Queries where several of your pages compete for the same rankings, with each page's share of clicks and impressions.
Dimensions and filters
Group data by one or more of these dimensions. You can't use the same dimension twice. Without dimensions, everything is combined into one row.
Queries
What people searched for. Google leaves out anonymized queries: queries too rare to show without risking someone's privacy. They count towards totals, but disappear as soon as you filter on query. That is why the sum of "contains x" and "doesn't contain x" can be lower than the total.
Pages
The final URL of the search result. Most data is assigned to the canonical URL, so a duplicate URL can show zero clicks even when your own analytics show visits from Google.
Countries and devices
Where the search came from, and whether it was made on desktop, mobile or tablet.
Search appearance
The type of result or feature that was shown. Values as returned by the API:
| Name | API value |
|---|---|
| AMP articles | AMP_TOP_STORIES |
| AMP non-rich results | AMP_BLUE_LINK |
| AMP on image result | AMP_IMAGE_RESULT |
| Android app | SEARCH_APPEARANCE_ANDROID_APP |
| Education Q&As | EDU_Q_AND_A |
| FAQ rich results | TPF_FAQ |
| Job details | JOB_DETAILS |
| Job listings | JOB_LISTING |
| Learning videos | LEARNING_VIDEOS |
| Math solvers | MATH_SOLVERS |
| Media Actions | ACTION |
| Merchant listings | MERCHANT_LISTINGS |
| Practice problems | PRACTICE_PROBLEMS |
| Product snippets | PRODUCT_SNIPPETS |
| Q&A rich results | TPF_QA |
| Recipe galleries | RECIPE_FEATURE |
| Recipe rich results | RECIPE_RICH_SNIPPET |
| Review snippet | REVIEW_SNIPPET |
| Subscribed content | SUBSCRIBED_CONTENT |
| Translated results | TRANSLATED_RESULT |
| Videos | VIDEO |
| Web Story | AMP_STORY |
Dates
Groups data per day, in Pacific Time. Only complete days are included by default. Days without data are left out, so query by date without filters first if you need to know which days have data.
Search type
The search tab the result appeared in: Web, Image, Video, News, Google News or Discover.
Data is stored per search type. An image on example.com/about can collect impressions in both web and image results; Search Console records those separately, with their own clicks and positions. You can't group by search type because positions mean different things per tab: position 30 in image search can be on the first screen, in web search it is page three.
Data limitations
The number of rows a query returns depends on:
- Your row limit: the maximum you set for the query, up to 2,000,000.
- The Search Console API: Google returns at most 50,000 rows per day per search type. Large exports need a longer period or the Date dimension.
- The property: how many rows Search Console has for it.
- Privacy: anonymized queries are never returned as rows.
- History: the API returns up to 16 months of data.
- Google Sheets: a spreadsheet holds at most 10,000,000 cells. Large queries across several tabs can hit that limit.
Chrome UX Report
Get real-user Core Web Vitals for up to 50 URLs or origins per run from the Chrome UX Report History API: a weekly time series covering roughly the last six months. For every week you get the p75 value and the share of good, needs improvement and poor experiences for:
- Largest Contentful Paint (LCP)
- Interaction to Next Paint (INP)
- Cumulative Layout Shift (CLS)
- First Contentful Paint (FCP)
- Time to First Byte (TTFB)
Choose all devices together or desktop, phone and tablet separately. Optional extras: a device breakdown, a sparkline per metric and colour coding by the Core Web Vitals thresholds.
The API needs a Google Cloud API key with the Chrome UX Report API enabled. Create one for free and paste it in the API key field each time you run a request. SEO Sheets doesn't store it.
URL Inspection
Paste up to 2,000 URLs from a property you have access to. For each URL, SEO Sheets combines the Search Console URL Inspection API with a fetch of the page itself:
- From Google: index verdict, coverage state, robots.txt status, last crawl date and whether the URL is in a submitted sitemap.
- From the page: HTTP status and redirect chain, title, meta description, meta robots, canonical (and whether it points to itself), H1–H6 counts, hreflang tags and structured data.
Google allows 2,000 inspections per property per day.
Sitemaps
Extract all URLs from an XML sitemap. Pick a property to use the sitemaps submitted in Search Console, or enter a sitemap URL. Sitemap index files are followed, so you get the URLs of every child sitemap in one sheet.