How To Export Google Analytics Data From R Studio To Excel

Alright, so in the first R project related post, I looked at analyzing Google Analytics data for my blog based on day of week [via geom_boxplot]

The next question that popped up was ...ok, great...I have the data loaded and the visualization...what if I still wanted this in Excel? How would I go about it?

Note: I have a feeling most of these blog posts will be based on finding answers to questions that pop up as I move along with R.

The below script uses the gadata boxplot we created. Once the dataframe is ready to be exported, install the "xlsx" package and run the library.

Saving Excel file to default folder in R Studio

This bit of the code creates a file "mygadata20180526" and saves it in the default folder for R. In my case, the Documents folder. If you'd like it to save to a particular folder, write out the

write.xlsx(gadata, file = "mygadata20180526.xlsx",
           sheetName = "Day Of Week", append = FALSE)

Saving Excel file to a specific folder in R Studio

If you'd like to save it to a specific folder of your choice, you'd change the save query to:

#exporting the file to a specific defined folder
write.xlsx(gadata, file = "C:\\Users\\KhanAd\\Desktop\\mygadata20180526.xlsx",
           sheetName = "Day Of Week", append = FALSE)

And that's it.

To change the default save folder, you'd go to R Studio > Tools > Global Options

#loading library
library(googleAnalyticsR)
#authentication token
ga_auth()

#setting up the query
gadata <- google_analytics(viewId = 55290473, 
                           date_range = c(Sys.Date()-100, Sys.Date()-1),
                           metrics = c("users", "sessions", "pageviews"),
                           dimensions = c("date", "dayofWeek"),
                           anti_sample = TRUE)
#Examining first 6 rows
head(gadata)                          
#looking at structure of the data
str(gadata)

#Creating boxplot
boxplotchart <- ggplot(gadata, aes(x=dayofWeek, y = sessions)) +
            geom_boxplot()
#creating custom labels for x and y axis
boxplotchart + ylab("Sessions on that day") + xlab("Day of Week; 0 = Sun, 6 = Sat")


install.packages("xlsx")
library("xlsx")
#exporting the file to R's default folder
write.xlsx(gadata, file = "mygadata20180526.xlsx",
           sheetName = "Day Of Week", append = FALSE)

#exporting the file to a specific defined folder
write.xlsx(gadata, file = "C:\\Users\\KhanAd\\Desktop\\mygadata20180526.xlsx",
           sheetName = "Day Of Week", append = FALSE)