Blogalyzer Google Sheets Add-On

Problem

Google Analytics doesn't provide an overview of your blog, nor an intuitive way to analyse its performance.

At the company I worked for, we lost track of what we had written before and had to paginate through our own blog to find the history. A bigger issue still, was that we couldn't learn from the past: the previous marketing manager had an efficient way of releasing new blogposts with thousands of readers. however, we didn't know which articles or in which month.
 

Google Analytics was painstakingly inefficient in providing these answers. What we wanted was a visual, intuitive way of comparing our articles' traffic (& traffic sources) over time.

First steps

From static spreadsheet analysis to automated Google Sheets Add-On ready for testing with real users.

The first solution was to use the (powerful, yet expensive) tool "SuperMetrics" to get our Analytics data in Google Sheets. From there on we could separate the data per month and filter out only the blogposts. Using conditional formatting, we then had a visual overview of our blog.
 

However, it was static! I had to repeat the same 8 hours of work each time I wanted an update.

After leaving the company, I decided to automate this and offer it as an add-on. I was convinced that I could help other companies with a tool like this and I was up for the coding challenge. I started automating the data download from Google Analytics API with Google Apps Script, parsing & adapting the data, working with the sheet and sidebar API's and after 2 intensive months, I found my first testers...

Testing & Optimising

Getting to a functioning Google Sheets add-on (after 6 months of hard work).

The two first testers were immediately challenging: one had 50 million (!) pageviews and the second one had 28.000 blog posts.

I tackled this by connecting Apps Script with a noSQL database (Firebase) to store the data and had to overcome timeout issues with a workaround for the lack of asynchronous promises in Google Apps Script back then (I Couldn't have done it without Bruce Mcpherson's helpful GAS tutorials).

After fixing these issues - and hundreds of others problems related to the Analytics API bugs & the fact that every blog has a different setup - I published the add-on. I found another 60 testers which I guided through the application in order to collect feedback.

Next steps

The add-on got positive feedback! But technical & UX issues meant we had to build a web application.

The testers were very positive about the product and were eager to use it. However, we found quite a few UX issues. For example: Users don't know about Google Sheets Add-ons and where to find the tool, the sidebar was very limited in amount of data to show, Users had too much freedom to change data in the sheet,...

Technically, GAS is not built to handle this amount of data, making it slow. Also, just switching to another tab in the browser, would kill the application.

I knew what I had to do next: build a custom web application! Read about it here.