Instructions: ‘Keyword Magic 🪄’ Spreadsheet

Copy the ‘Keyword Magic’ spreadsheet

Click the button to the right to make a copy of the ‘Keyword Magic’ spreadsheet for your store

Copy The 'Keyword Magic' Spreadasheet

Kai’s Spreadsheet 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.

Keyword Research Webinar
(with Spreadsheet Video Demo):

Ilana Davis invited me to participate in a webinar on “Shopify Keyword Research Simplified.”

In this webinar recording (to the right), you’ll get insight on how to think about keyword research, and how to use this ‘Keyword Magic’ spreadsheet for your store.

Text instructions on setting up the Keyword Magic spreadsheet

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. 

  1. 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. 
  2. 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. 
  3. 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.) 
  4. Access your Keyword Magic spreadsheet and go to the tab labeled “1️⃣ Google Search Console Data Export”
  5. 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.)
  6. 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 ‘1️⃣ Google Search Console Data Export’
    • ‘Include fresh data’: leave this unchecked
  7. Click “Request Data” and the extension will chug-a-lug in the background and pull in your Search Console data
  8. Click into the “🔥 Quick Wins’ tab. You should see a list of the ‘quick win’ keyword opportunities on your website. 

And there you have it. Those are the Quick Win opportunities you should focus on with your keyword optimization efforts. 

Links & Resources

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 https://seotesting.com/ranktracker to track your keyword performance. That’s a free tool from SEO Testing that 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.  Would you like personalized 1-on-1 help setting up your Keyword Magic spreadsheet? Book a 30- or 60-minute call right here, and we’ll set up your Keyword Magic spreadsheet together: https://savvycal.com/dye.

Scroll to Top