Menu
How To Make The Magic Happen
Instructions for the ‘Keyword Magic’ Spreadsheet
Welcome! Here are instructions on how to make a copy of + use my keyword magic spreadsheet.
First things first: click this button to make your copy of this spreadsheet in Google Sheets.
Video Instructions
Watch this video on how you can use this keyword magic spreadsheet to pull in data from Google Search Console and find the ‘quick win’ keyword optimization opportunities for your store.
Would you like 1:1 guidance on using this spreadsheet?
You can meet 1:1 with me over a video call for a Business Strategy Call and we can
- Walk through setting up the keyword magic spreadsheet
- Answer your questions about your keyword data or SEO
- Discuss which opportunities you should work on next
Book your call here: https://savvycal.com/dye/.
Keyword Research Webinar w/ Spreadsheet
In this webinar recording — from a presentation to Ilana Davis’s audience — you’ll get insight on how to think about keyword research, and how to use this ‘Keyword Magic’ spreadsheet for your store. You’ll also learn a lot about keyword research.
Text Instructions
This is a little technical, but not too technical. You’ll be installing an add-on for Google Sheets to export Google Search Console data into the spreadsheet.
- Make your copy of my Keyword Magic Spreadsheet (link). This is my ‘magic’ spreadsheet that will take your Search Console data (via Search Analytics for Sheets), do a little magic on it, and output your ‘
Quick Win’ keyword opportunities. - Confirm you have Google Search Console installed for your site (this is where all the data comes from, so it’s an essential part of the process). You’ll want to log into Search Console and confirm you see your site listed in the dropdown in the top left.
- Install the Search Analytics for Sheets plugin (https://searchanalyticsforsheets.com/) to your Google Workspace or Google Sheets account. This free plugin pulls your data from Search Console into a Google Sheet. (This is Google only, no Excel option.)
- Access your Keyword Magic spreadsheet and go to the tab labeled “
Google Search Console Data Export” - Click Extensions → Search Analytics for Sheets → Open Sidebar (don’t see Search Analytics for Sheets listed under Extensions? It’s likely not installed on your account. Go back to Step 2 and install Search Analytics for Sheets.)
- Configure the Search Analytics sidebar settings are set as follows:
- Verified Site: select your site (from Search Console) here
- Date Range: set this to the last 2 full months.
- Search Type: leave this as ‘Default (Web)’
- Group By: Select ‘Page’ and ‘Query’
- Filter By: leave this be, don’t do anything here
- Aggregation Type: leave this be, don’t do anything here
- Rows returned: leave this at 25,000
- Results Sheet: set this to ‘ Google Search Console Data Export’
- ‘Include fresh data’: leave this unchecked
- Click “Request Data” and the extension will chug-a-lug in the background and pull in your Search Console data
- Click into the “ Quick Wins’ tab. You should see a list of the ‘quick win’ keyword opportunities on your website.
Other Links & Resources
Links
- You can make your own copy of this ‘keyword magic’ spreadsheet right here: Keyword Magic Spreadsheet [Template]
- Google Search Console, a free tool from Google for tracking your keyword performance data (https://search.google.com/search-console).
- Search Analytics for Sheets, a free(mium) tool that lets you pull your Search Console data over to Google Sheets (https://searchanalyticsforsheets.com/).
- Keyword Rank Tracker, a free tool from SEO Testing that connects to Search Console and tracks your keyword rankings (https://seotesting.com/ranktracker).
Optimizing your keywords
Here’s the quick keyword optimization process you should follow:
- Pick a page/query to focus on (maybe the top one on your sheet)
- Google that query and look for your page (find it in the rankings)
- Look at the sites ~2-3 positions above you and read their content
- Then, read your content
- Are there obvious things you could do to improve the quality of your content and make it better than those competitors? Make those changes.
- Review these bits of content on your page and optimize them to reference your keyword:
- SEO Title
- Page Headline
- Page content
Tracking your keyword performance
I recommend you use Google Search Console to track your keyword performance.
Another great free tool is Rank Tracker from SEO Testing.
https://seotesting.com/ranktracker
That tool hooks into Google Search Console and lets you select keywords to track. This is a great way to see the fruits of your optimization efforts (without sifting through a dozen spreadsheets).
And there you have it!
I hope these resources help you understand keyword research and optimization and have more clarity on which pages + keywords to focus on optimizing.
Questions? Something broken? You can email me directly at kai@doubleyourecommerce.com.
SEO tips. Get regular ‘do this, not that’ tips on what to optimize, how to rank higher, and earn more traffic from Google.