You are paying $99/month to export CSVs
Ahrefs costs $99/month. SEMrush costs $129/month. Moz Pro costs $99/month. And most of the time, you export the data into a spreadsheet anyway. You run a keyword report, click Export, open the CSV in Google Sheets, and start sorting.
That is a lot of overhead just to see who ranks for your keywords.
What if you could skip the middleman? Type a keyword into a cell, get the top 10 results with titles, URLs, and snippets. Then drag the formula down 50 rows and get the same data for every keyword on your list.
That is exactly what =SERP() and =BULKSERP() do. And they work right now, inside Google Sheets.
=SERP("best project management software")Returns up to 20 Google search results with titles, URLs, snippets, and ranking positions. One formula, one keyword, structured data in your cells.
Try that formula with any keyword you are researching. The results show up in your sheet within seconds. No API keys to configure. No Python scripts to debug. Just a formula.
How SERP scraping works in SheetMagic
SheetMagic gives you two formulas for pulling search engine results.
=SERP() for vertical results
=SERP(A2)Returns search results vertically. Each result gets its own row with title, URL, snippet, and position.
=SERP() returns results in a vertical layout. Each search result takes up its own row. This is great when you want to dig deep into one keyword. You can see every result, scan the titles, and spot patterns in what Google rewards.
=BULKSERP() for horizontal comparison
=BULKSERP(A2)Returns results horizontally. One keyword per row, results spread across columns. Perfect for comparing 50 keywords at a glance.
=BULKSERP() keeps each keyword on a single row. The results spread across columns instead. This is the formula you want for bulk keyword research. Put 50 keywords in column A, put =BULKSERP(A2) in column B, and drag down. Your entire keyword landscape fits on one screen.
Use =SERP() when you are exploring a single keyword in depth. Use =BULKSERP() when you are comparing many keywords at once. Both use 1 integration credit per keyword.
Build your keyword research dashboard
This is where things get fun. You are going to build a keyword research dashboard that would cost you $1,188/year on Ahrefs. It takes about ten minutes.
Set up your seed keywords
Put your target keywords in column A. Start with 10 to 20 keywords. These can be terms you want to rank for, topics you are writing about, or keywords your competitors target.
For example:
- A2:
best crm for startups - A3:
project management tool comparison - A4:
free invoice template google sheets - A5:
how to track expenses in a spreadsheet
Pull SERP data for every keyword
=BULKSERP(A2)Put this in B2 and drag down to B21. Each row fills with the top search results for that keyword.
You now have the top-ranking pages for every keyword. Titles, URLs, and snippets, all in your spreadsheet.
Extract who ranks in position 1
=AITEXT("From these search results, extract only the URL of the #1 ranking result. Return just the URL, nothing else.", B2)Pulls the top-ranking URL for each keyword. Put in a new column and drag down.
Now you can see exactly who owns the top spot for each keyword. Look for patterns. Does one competitor dominate? Are there smaller sites sneaking into position 1?
Classify search intent
=AITEXT("Classify this keyword by search intent. Return one label: informational, navigational, transactional, or commercial. No explanation.", A2)Tags each keyword with its search intent. Filter by intent to prioritize your content calendar.
Search intent tells you what to create. Informational keywords need blog posts. Transactional keywords need product pages. Commercial keywords need comparison guides.
Score the opportunity
=AITEXT("Look at these search results. Are the top results from major authority sites (Wikipedia, Forbes, government sites) or from smaller niche sites? Return: HIGH OPPORTUNITY if smaller sites rank, LOW OPPORTUNITY if dominated by major authorities. Add one sentence explaining why.", B2)Scores whether you can realistically compete for this keyword based on who currently ranks.
This is your difficulty filter. Keywords where small sites rank well are the ones you should target first.
After five steps, you have a keyword research dashboard with SERP data, search intent, and opportunity scores. All of it is formula-driven. Change a keyword in column A and the entire row updates.
You can try all of this on the free tier. No credit card needed. You get 10 integration credits (one-time trial), enough to research 10 keywords and see how the workflow fits your process. Install SheetMagic free and build your first dashboard today.
Competitive SERP analysis: who ranks for your keywords?
Keyword research tells you what to write. Competitive SERP analysis tells you who you are up against and how to beat them.
Map your competitive landscape
=SERP("your target keyword")Pull the full SERP for a keyword you want to rank for. Copy the top 10 URLs into a new column for deeper analysis.
Once you have the top-ranking URLs, you can break them apart.
=GETMETATITLE(A2)Extracts the title tag from each competitor URL. Scan these to see what titles Google rewards for this keyword.
=GETH2(A2)Extracts all H2 headings from the competitor page. This gives you their full content outline without reading a single paragraph.
Now you know what the top results cover. Compare their outlines side by side. Look for topics that multiple competitors cover (table stakes) and topics only one or two mention (potential differentiators).
Find content gaps your competitors miss
=AITEXT("Here are the H2 heading outlines from 5 articles ranking for the keyword: best crm for startups. Identify 3 subtopics that none of them cover well. Be specific and actionable.", B2&" ||| "&B3&" ||| "&B4&" ||| "&B5&" ||| "&B6)Feeds competitor outlines into AI analysis. The gaps it finds become your content advantage.
The gaps are your opportunity. Every subtopic your competitors skip is a chance to create something more useful. Google rewards the most complete answer.
Feed the content gaps into a content brief. Use =AITEXT("Create a blog post outline that covers these underserved subtopics: "&C2) to generate a structure that fills the gaps. For a full content brief workflow, see the SEO guide.
Extract SERP features: People Also Ask, snippets, and more
Google search results are not just ten blue links anymore. Featured snippets, People Also Ask boxes, video carousels, and knowledge panels take up real estate. Understanding which SERP features show up for your keywords helps you target the right content format.
=AITEXT("Analyze these search results. Does this SERP contain any of the following: featured snippet, People Also Ask, video results, knowledge panel, image pack? List which features appear. If the results suggest a featured snippet, quote the text that appears to be the snippet.", B2)Identifies SERP features from your search results data. Helps you decide if your content should target a snippet, include video, or answer questions.
Target People Also Ask questions
=AITEXT("From these search results and snippets, extract any People Also Ask questions you can identify. Return them as a numbered list. If none are visible, list 5 related questions that searchers would likely ask about this topic.", B2)Pulls or generates PAA-style questions. Each one is a subheading opportunity for your content.
People Also Ask questions are free content ideas. Each question is a subheading you can add to your article. Answer them clearly and you have a shot at appearing in the PAA box yourself.
Identify the winning content format
=AITEXT("Look at the titles and snippets of these top 10 results. What content format dominates? Options: listicle, how-to guide, comparison, review, tutorial, tool/calculator. Return the dominant format and a one-sentence explanation.", B2)Tells you what content format Google prefers for this keyword. Match the format to improve your ranking chances.
If the top 10 results are all listicles, write a listicle. If they are all how-to guides, write a how-to guide. Matching the winning format gives you a structural advantage before content quality even enters the picture.
Bulk SERP scraping: 50 keywords in one go
The real power of spreadsheet-based SERP scraping is scale. You are not running one keyword at a time. You are running 50 or 100 at once, then slicing the data with filters and formulas.
Prepare your keyword list
Paste 50 keywords into column A. These can come from brainstorming, competitor research, or tools like Google Search Console.
Run BULKSERP across all keywords
=BULKSERP(A2)Put in B2 and drag down to B51. Process in batches of 25 if you prefer. Each keyword uses 1 integration credit.
Process large keyword lists in batches of 25 to 50 rows. This avoids timeouts and makes it easier to spot issues early. Run the first batch, check the results, then continue.
Tag each keyword with intent and opportunity
Use the classification formulas from the dashboard section above. Add columns for search intent and opportunity score. You now have a prioritized keyword list based on real SERP data.
Filter and sort
Use Google Sheets filters to find your best opportunities. Filter for "HIGH OPPORTUNITY" and "commercial" intent. Sort by whatever matters most to your strategy. The keywords that survive your filters are the ones worth writing about first.
At 1 credit per keyword, 50 keywords cost 50 integration credits. The Solo plan gives you 1,000 credits per month. That is 1,000 keywords of SERP data every month for $19.
Compare that to Ahrefs at $99/month or SEMrush at $129/month. And with SheetMagic, the data lives in your spreadsheet where you already do your analysis.
| Tool | Monthly cost | Keywords/month | Data location |
|---|---|---|---|
| Ahrefs Lite | $99 | 500 tracked | Separate dashboard |
| SEMrush Pro | $129 | 500 tracked | Separate dashboard |
| Moz Pro | $99 | 300 tracked | Separate dashboard |
| SheetMagic Solo | $19 | 1,000 SERP pulls | Your Google Sheet |
Scrape titles and descriptions for SEO audits
SERP scraping is not just about keyword research. You can also audit your own pages by pulling their metadata and comparing it against competitors.
=GETMETATITLE(A2)Extracts the title tag from your page URL. Check for length, keyword placement, and click-worthiness.
=GETMETADESCRIPTION(A2)Extracts the meta description. Verify it is under 155 characters and includes your target keyword.
=GETH1(A2)Extracts the H1 heading. Make sure every page has one and it aligns with the title tag.
Combine these with SERP data to see how your pages stack up against competitors:
=AITEXT("Compare my page title with the top 3 competitor titles for this keyword. My title: "&B2&". Competitor titles: "&C2&", "&C3&", "&C4&". Is mine more or less compelling? Suggest an improved version under 60 characters.", "")AI-powered title tag comparison. Generates a better title based on what competitors use.
For a complete walkthrough of SEO audits with SheetMagic, see the full SEO guide. For extracting headings and content from competitor pages, check the content extraction guide.
Ethical scraping and rate limits
A quick note on being a good citizen. SheetMagic handles rate limiting automatically. You do not need to worry about sending too many requests too fast. The system queues your requests and spaces them out.
That said, a few things to keep in mind:
Respect robots.txt. Some sites block automated scraping. If a formula returns empty results, the site may restrict access. Move on to another source.
Cache your results. SheetMagic caches scraping results for 30 minutes. If you tweak your AI processing formulas, the cached SERP data gets reused. You do not burn extra credits.
Use scraping for research, not copying. Pull titles, URLs, and snippets to inform your strategy. Do not scrape full articles and republish them. Use =VISIT() for analysis and summarization, not duplication.
Start small. Run 10 keywords first. Check the results. Then scale up to 50 or 100. This catches problems early and saves credits.
SERP results change constantly. Data you scrape today may not match tomorrow's results. For time-sensitive research, note the date you pulled the data. Create a new sheet tab each week or month if you want to track changes over time.
Your keyword research dashboard template
Here is the full column layout for your dashboard. Set this up once and reuse it for every keyword research project.
| Column | Formula | What it shows |
|---|---|---|
| A | (your keywords) | Seed keyword list |
| B | =BULKSERP(A2) | Top search results |
| C | =AITEXT("Extract the #1 URL...", B2) | Top-ranking domain |
| D | =AITEXT("Classify intent...", A2) | Search intent |
| E | =AITEXT("Score opportunity...", B2) | Difficulty estimate |
| F | =AITEXT("Identify SERP features...", B2) | Featured snippets, PAA |
| G | =AITEXT("What format dominates...", B2) | Winning content type |
Seven columns. One formula per column. Drag them all down your keyword list. You have a complete keyword research dashboard that updates live.
Want a head start? The Keyword Research Dashboard Template has all of these formulas pre-built. Input your seed keywords and the dashboard does the rest. Install SheetMagic to get started.
What to do with your SERP data
Scraping is step one. Here is how the data feeds into your content workflow:
Prioritize keywords. Filter your dashboard for high-opportunity, commercial-intent keywords. These are the ones most likely to drive traffic and conversions.
Build content briefs. Take the SERP data for your top keyword and run it through the content gap analysis. The gaps become your outline. For a full brief-building workflow, see the SEO guide.
Track competitors. Set up a sheet tab for each month. Re-run your SERP formulas to see who moves up, who drops, and what new content enters the top 10.
Feed into link prospecting. The URLs in your SERP data are potential link targets. Run =VISIT() on them and use =AITEXT() to find contact pages, author bios, and link opportunities. The web scraping guide covers this workflow in detail.
The spreadsheet is your command center. The SERP data is the raw material. And the AI formulas are your analysis engine.
Start scraping SERPs today
Open Google Sheets. Type =SERP("your keyword") in any cell. That is it. You are doing keyword research now.
The free tier gives you 10 integration credits to try it out. No credit card needed. Run 10 SERP queries, build a mini dashboard, and see if this replaces the $99/month tool you are thinking about canceling.
Install SheetMagic free from the Google Workspace Marketplace and pull your first SERP data in the next sixty seconds.
More scraping and SEO guides: Complete web scraping guide | Content extraction | SEO workflows | Getting started