Hands up who struggles building reports for their business? It’s a common challenge particularly if you need to split them into a weekly, monthly, and yearly basis.
However there is a way to collect that data and push it into a report so then all you need to think about is interpreting the data and taking the appropriate actions. Sound good? Read on.
Here I will explain with a simple example how you can pull data and display it graphically in a word document using just R, a programming language built specifically for manipulating data and graphics.
Keep in mind this is just one approach and there may be others that are more suitable for you but I find this the most straight forward.
Let’s get started
The best way to start is by thinking how you want to display that data for example, in pie charts, line graphs or just tables.
Most of the time I end up selecting the classic charts (check out ggplot2 and R for Data Science if you need to build your knowledge with R.) Now I want to show you a different way to check where your website is receiving the most traffic.
I’ll skip all the set up requirements but first you’ll need to download R and the best IDE in my humble opinion, RStudio; plus you’ll need to get access to a Google API. If you need help to do this check out this tutorial ‘setup R with Google Analytics.’
Once you have done this, you will need six packages: googleAuthR, googleAnalyticsR, rnaturalearth, sp and ReporteRs; These are my preferred ones, but you may have others you like to use that have the same output.
Connect with your API (if you don’t know what APIs are take a look at this) and then apply some variables which identify the time range, and the GAID of your selected view, finally apply the functions that are in the googleAnalyticsR package (I use the v4, but the package also supports v3) – in RStudio’s console type ?google_analytics_4 or ?google_analytics – If you need to understand the main differences check out this page.
Using this example, you should then save a data frame with Great Britain cities, plus longitude and latitude coordinates for each one, and the sessions in descending order (from the highest number to the lowest).
Now you should have the data from GA that you need, the next step is to plan the data visualisation part. If you want, for example, a digital map that explains where the top five sessions for March are, you need to create a new variable.
You may also want to apply colours in a format that is based in the data range, so in this example I want to apply a colour for cities that have sessions higher than 1,000, then between 1,000 and higher then 500, then again between 500 and higher than 250, and finally lower or equal to 250.
Then, if you want to save the image in a folder and directly into the document, you can do this by using the ReporteRs package.
Next you can take the data for the map and plot it in a visual way. To do this you can use rnaturalearth.
At the end
This is the image that will be created:
And this is a screenshot of the word document output:
If you want to skip straight to the full code without my commentary (but hopefully you found it useful!) here it is:
Make sense?! Job done. Trust me I’ve spent more time writing this article than the actual code, and it is much quicker than creating screenshots!
You can use other tools such as Excel, Tableau or Google Drive to get the same results or other languages to substitute R, so I suggest you find what works best for you and apply it.
I personally find R a great language, and I use it a lot with packages related with Google APIs. Look for Mark Edmondson, who is the developer of googleAuthR, googleAnalyticsR and more, he is definitely worth a follow in my opinion. However you may prefer other languages, Python for example, which are still perfectly fine to use, but I think R is the most straight forward language.
I would recommend that you use this approach if you need to build the same report each week or month – if it is a one off or occasional report then it may take you more time to write the script than actually collecting the data. You may also find it useful to use R if you need to integrate high statistical analysis (i.e. using Machine Learning approaches).
My final piece of advice is to use this article and code and start to have fun playing with R. If you don’t enjoy playing around with it then it might not be the right language or approach for you.
If you want to discuss further ideas, or f you want to offer alternative approaches to mine, then get in touch with me on LinkedIn or Twitter.
Have fun folks!