Methods for Extracting Data from Google Search Console

Analyzing Search Console data directly through the interface can be challenging. Therefore, to effectively analyze GSC data, it's essential to find a method to extract the data for more thorough analysis. Below are some of my favorite methods for extracting the data—no technical knowledge or expensive software required.

Once you have the data available (in the form of Excel or CSV), you can quickly analyze the data using AI tools (see examples of AI prompts designed specifically for Search Console data here).

1. Google Sheets Add-on

One of the easiest and fastest ways of extracting data from Search Console is by using a Google Sheets add-on called "Search Analytics for Sheets."

The add-on is free to use, but you won't be able to extract more than 25,000 rows at a time. However, that shouldn't stop you since you can repeat the processes, pull the data in smaller chunks, and then consolidate (e.g., pull only several months at a time).

2. Google Looker Studio (aka Data Studio)

Another easy way to get GSC data is by using Looker Studio (formerly known as Google Data Studio). Simply connect your Data Studio account to Google Search Console using the existing connector. Then, build a table with the necessary columns as mentioned earlier. Then export the table as csv (make sure it includes all rows).

3. R script 

This is a great way of getting GSC data if you do have a bit of technical knowledge (and it's also free).

All you need is access to R Studio (you can either install it locally on your computer, or use a free web based version of RStudio available via Posit Connect). You can find sample R script below

# This is an example R code which can be used to extract Query data from Google Search Console

########################################################################
# install necessary packages

if(!require(searchConsoleR)) {
  install.packages("searchConsoleR"); 
  require(searchConsoleR)
} 


if(!require(library(xlsx))) {
  install.packages("library(xlsx)"); 
  require(library(xlsx))
} 

########################################################################
#AUTHENTICATE
scr_auth()

########################################################################
#LIST ALL MY WEBSITES in SEARCH CONSOLE
list_websites()

########################################################################
#GET daily (global)


data_query <- search_analytics(siteURL = "https://www.analyticshacker.com"
                                   , startDate = Sys.Date()-93    # starting from 3 months ago
                                   , endDate =  Sys.Date()-3   # up to 3 days ago 
                                 # , endDate = '2020-10-26'
                                   , dimensions = c("query","date")
                                   , searchType = c("web")
                                   #, dimensionFilterExp = c("country==usa")
                                   , aggregationType = c("byProperty")
                                  , rowLimit = 25000   # default is 1000
                                   , prettyNames = TRUE
                                   , walk_data =  "byBatch")

# show number of rows returned
nrow(data_query)

# preview the data
head(data_query)

# drop ctr column
data_query <- data_query[, -which(names(data_query) == "ctr")]

# remove commas from the query column
data_query[] <- lapply(data_query , gsub, pattern=',', replacement='')

This script will fetch daily data for the entire year of 2023 for the specified website. The data includes dimensions for query and date. You can modify the dimensions (e.g. ‘device’, ‘country’, ‘page’). Also, ensure that you have the necessary permissions in Google Search Console for the website you're trying to access.

Analyse extracted data in R

Now that you have data in R, you can use R to analyse it. There are so many things you can do with the data, and below you have just a few examples of AI prompts that you could type in an AI tool of your choice in order to get R code ready

• write an R script which creates a bar plot showing Clicks on left axis, and Line showing Impressions (on the right axis)?