Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Mechanical export of up to 16 months of Google Search Console data in R
# Load packages #
library(searchConsoleR)
library(dplyr)
library(ggplot2)
library(writexl)
library(stringr)
# Authorize & choose Google profile #
scr_auth()
# Specify website --- Client Code or Line of Business Code Here #
client <- "CLCO"
website <- "https://www.twosigma.com"
############ SPECIFYING THE MONTHS ############
start1 <- "2020-04-01"
end1 <- "2020-04-30"
start2 <- "2020-05-01"
end2 <- "2020-05-31"
start3 <- "2020-06-01"
end3 <- "2020-06-30"
start4 <- "2020-07-01"
end4 <- "2020-07-31"
start5 <- "2020-08-01"
end5 <- "2020-08-31"
start6 <- "2020-09-01"
end6 <- "2020-09-30"
start7 <- "2020-10-01"
end7 <- "2020-10-31"
start8 <- "2020-11-01"
end8 <- "2020-11-30"
start9 <- "2020-12-01"
end9 <- "2020-12-31"
start10 <- "2021-01-01"
end10 <- "2021-01-31"
start12 <- "2021-02-01"
end12 <- "2021-02-28"
start13 <- "2021-03-01"
end13 <- "2021-03-31"
start14 <- "2021-04-01"
end14 <- "2021-04-30"
start15 <- "2021-05-01"
end15 <- "2021-05-31"
start16 <- "2021-06-01"
end16 <- "2021-06-30"
# # # Start mo 1
mo1_queries <- search_analytics(
siteURL = website,
startDate = start1,
endDate = end1,
dimensions = c("query", "page"),
searchType="web",
rowLimit = 1000000
)
# Dynamically get the number of rows from the GSC export so the created vectors match number of rows
rows <- nrow(mo1_queries)
# Make a vector that populates the client code
clientName <- rep(client,rows)
# Make a vector that populates the start date for Month filtering, segmenting, pivoting
date <- rep(start1,rows)
# Make a data frame from the created vectors
mo1_queries_merged_1 <- data.frame(clientName,date,mo1_queries)
############### QA CHECKPOINT ######################
# Write the data frame to an XLSX file
write_xlsx(mo1_queries_merged_1, "mo1_queries_1.xlsx")
# # # END mo 1
# # # Start mo 2
mo2_queries <- search_analytics(
siteURL = website,
startDate = start2,
endDate = end2,
dimensions = c("query", "page"),
searchType="web",
rowLimit = 1000000
)
rows <- nrow(mo2_queries)
clientName <- rep(client,rows)
date <- rep(start2,rows)
mo2_queries_merged_1 <- data.frame(clientName,date,mo2_queries)
write_xlsx(mo2_queries_merged_1, "mo2_queries_1.xlsx")
# # # END mo 2
# # # Start mo 3
mo3_queries <- search_analytics(
siteURL = website,
startDate = start3,
endDate = end3,
dimensions = c("query", "page"),
searchType="web",
rowLimit = 1000000
)
rows <- nrow(mo3_queries)
clientName <- rep(client,rows)
date <- rep(start3,rows)
mo3_queries_merged_1 <- data.frame(clientName,date,mo3_queries)
write_xlsx(mo3_queries_merged_1, "mo3_queries_1.xlsx")
# # # END mo 3
# # # Start mo 4
mo4_queries <- search_analytics(
siteURL = website,
startDate = start4,
endDate = end4,
dimensions = c("query", "page"),
searchType="web",
rowLimit = 1000000
)
rows <- nrow(mo4_queries)
clientName <- rep(client,rows)
date <- rep(start4,rows)
mo4_queries_merged_1 <- data.frame(clientName,date,mo4_queries)
write_xlsx(mo4_queries_merged_1, "mo4_queries_1.xlsx")
# # # END mo 4
# # # Start mo 5
mo5_queries <- search_analytics(
siteURL = website,
startDate = start5,
endDate = end5,
dimensions = c("query", "page"),
searchType="web",
rowLimit = 1000000
)
rows <- nrow(mo5_queries)
clientName <- rep(client,rows)
date <- rep(start5,rows)
mo5_queries_merged_1 <- data.frame(clientName,date,mo5_queries)
write_xlsx(mo5_queries_merged_1, "mo5_queries_1.xlsx")
# # # END mo 5
# # # Start mo 6
mo6_queries <- search_analytics(
siteURL = website,
startDate = start6,
endDate = end6,
dimensions = c("query", "page"),
searchType="web",
rowLimit = 1000000
)
rows <- nrow(mo6_queries)
clientName <- rep(client,rows)
date <- rep(start6,rows)
mo6_queries_merged_1 <- data.frame(clientName,date,mo6_queries)
write_xlsx(mo6_queries_merged_1, "mo6_queries_1.xlsx")
# # # END mo 6
# # # Start mo 7
mo7_queries <- search_analytics(
siteURL = website,
startDate = start7,
endDate = end7,
dimensions = c("query", "page"),
searchType="web",
rowLimit = 1000000
)
rows <- nrow(mo7_queries)
clientName <- rep(client,rows)
date <- rep(start7,rows)
mo7_queries_merged_1 <- data.frame(clientName,date,mo7_queries)
write_xlsx(mo7_queries_merged_1, "mo7_queries_1.xlsx")
# # # END mo 7
# # # Start mo 8
mo8_queries <- search_analytics(
siteURL = website,
startDate = start8,
endDate = end8,
dimensions = c("query", "page"),
searchType="web",
rowLimit = 1000000
)
rows <- nrow(mo8_queries)
clientName <- rep(client,rows)
date <- rep(start8,rows)
mo8_queries_merged_1 <- data.frame(clientName,date,mo8_queries)
write_xlsx(mo8_queries_merged_1, "mo8_queries_1.xlsx")
# # # END mo 8
# # # Start mo 9
mo9_queries <- search_analytics(
siteURL = website,
startDate = start9,
endDate = end9,
dimensions = c("query", "page"),
searchType="web",
rowLimit = 1000000
)
rows <- nrow(mo9_queries)
clientName <- rep(client,rows)
date <- rep(start9,rows)
mo9_queries_merged_1 <- data.frame(clientName,date,mo9_queries)
write_xlsx(mo9_queries_merged_1, "mo9_queries_1.xlsx")
# # # END mo 9
# # # Start mo 10
mo10_queries <- search_analytics(
siteURL = website,
startDate = start10,
endDate = end10,
dimensions = c("query", "page"),
searchType="web",
rowLimit = 1000000
)
rows <- nrow(mo10_queries)
clientName <- rep(client,rows)
date <- rep(start10,rows)
mo10_queries_merged_1 <- data.frame(clientName,date,mo10_queries)
write_xlsx(mo10_queries_merged_1, "mo10_queries_1.xlsx")
# # # END mo10
# # # Start mo 11
mo11_queries <- search_analytics(
siteURL = website,
startDate = start11,
endDate = end11,
dimensions = c("query", "page"),
searchType="web",
rowLimit = 1000000
)
rows <- nrow(mo11_queries)
clientName <- rep(client,rows)
date <- rep(start11,rows)
mo11_queries_merged_1 <- data.frame(clientName,date,mo11_queries)
write_xlsx(mo11_queries_merged_1, "mo11_queries_1.xlsx")
# # # END mo 11
# # # Start mo 12
mo12_queries <- search_analytics(
siteURL = website,
startDate = start12,
endDate = end12,
dimensions = c("query", "page"),
searchType="web",
rowLimit = 1000000
)
rows <- nrow(mo12_queries)
clientName <- rep(client,rows)
date <- rep(start12,rows)
mo12_queries_merged_1 <- data.frame(clientName,date,mo12_queries)
write_xlsx(mo12_queries_merged_1, "mo12_queries_1.xlsx")
# # # END mo 12
# # # Start mo 13
mo13_queries <- search_analytics(
siteURL = website,
startDate = start13,
endDate = end13,
dimensions = c("query", "page"),
searchType="web",
rowLimit = 1000000
)
rows <- nrow(mo13_queries)
clientName <- rep(client,rows)
date <- rep(start13,rows)
mo13_queries_merged_1 <- data.frame(clientName,date,mo13_queries)
write_xlsx(mo13_queries_merged_1, "mo13_queries_1.xlsx")
# # # END mo 13
# # # Start mo 14
mo14_queries <- search_analytics(
siteURL = website,
startDate = start14,
endDate = end14,
dimensions = c("query", "page"),
searchType="web",
rowLimit = 1000000
)
rows <- nrow(mo14_queries)
clientName <- rep(client,rows)
date <- rep(start14,rows)
mo14_queries_merged_1 <- data.frame(clientName,date,mo14_queries)
write_xlsx(mo14_queries_merged_1, "mo14_queries_1.xlsx")
# # # END mo 14
# # # Start mo 15
mo15_queries <- search_analytics(
siteURL = website,
startDate = start15,
endDate = end15,
dimensions = c("query", "page"),
searchType="web",
rowLimit = 1000000
)
rows <- nrow(mo15_queries)
clientName <- rep(client,rows)
date <- rep(start15,rows)
mo15_queries_merged_1 <- data.frame(clientName,date,mo15_queries)
write_xlsx(mo15_queries_merged_1, "mo15_queries_1.xlsx")
# # # END mo 15
# # # Start mo 16
mo16_queries <- search_analytics(
siteURL = website,
startDate = start16,
endDate = end16,
dimensions = c("query", "page"),
searchType="web",
rowLimit = 1000000
)
rows <- nrow(mo16_queries)
clientName <- rep(client,rows)
date <- rep(start16,rows)
mo16_queries_merged_1 <- data.frame(clientName,date,mo16_queries)
write_xlsx(mo16_queries_merged_1, "mo16_queries_1.xlsx")
# # # END mo 16
total_queries_by_page <- rbind(mo1_queries_merged_1,mo2_queries_merged_1,mo3_queries_merged_1,mo4_queries_merged_1,mo5_queries_merged_1,mo6_queries_merged_1,mo7_queries_merged_1,mo8_queries_merged_1,mo9_queries_merged_1,mo10_queries_merged_1,mo11_queries_merged_1,mo12_queries_merged_1,mo13_queries_merged_1,mo14_queries_merged_1,mo15_queries_merged_1,mo16_queries_merged_1)
write_xlsx(total_queries_by_page, "total_queries_by_page.xlsx")
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment