How to write an Excel macro that fetches google analytics data

The first question I asked was whether to build an Excel plugin or not. As part of my thought process, I reviewed what the steps would be needed to integrate Google Analytics data into Excel continuously.

I came up with these plugins: SEO Tools and ExcellentAnalytics. These plugins would enable me to pull the data when my spreadsheet is open. I then wrote a macro to refresh it (assuming that the file was open). I also set it so that the macro runs when the file opens, even though this could make my excel really slow when it had to fetch a lot of data. Below is an overview of what I did:

• Got the SEOTools plugin and wrote the query as a function.
• Wrote a macro to update the query when the file was opened. This is only if hte timestamp from the last query is older than 24 hours.
• If the query is updated, then write the new timestamp.
• Then I wrote a counter that checks every minute or so to make sure that if the current time is more than the old timestamp, then update the query and write the new time stamp.

All in all, this took me about one week of research with more than a lot of help from some of my excel guru friends. If you’ve never written a macro, here’s a step by step guide on how to write a macro in Excel. While this worked well, I still thought that this wasn’t the best way to go about things for the rest of my needs. If for some reason, I wanted to include other sources of information data, then I would have to go through this same process all over again. Thus my first attempt to adapt excel using current processes (i.e., plugin and macros) was insufficient.

I then asked my friends about whether this excel pain was something they experienced in their jobs in order to see how they were solving it. These are some of the answers I received (brace yourselves for ridiculousness):

• “There are 48 people in my department. It takes us 22 days to compile a report due to all of the data we try to integrate. No IT solution has worked for us so we just put up with it” (works at a major bank).
• “I needed to bring different sources of data into my report. IT tried to build me a solution but after 8 months, the solution they deployed ended up being completely different than what my department proposed. We just bit the bullet and went back to our spreadsheets” (works at food and beverage company).
• “I made an excel error that cost my company $200,000+ during the Holiday season. I almost lost my job as a result. If I had a tool, I would be using it by now” (works at major apparel company).

In short, everyone knew the problems and were putting up with them. Consequently, I decided to try and build a solution from scratch. My idea: to build an online spreadsheet that would hack the data gathering process to make reporting a breeze. If this worked for me, I already knew my friends would use it as well.

As I was starting to build it, I did some research and found an amazing tool that already did this; called Factivate. The company was listed as one of the top 50 companies to watch in 2015 by Colorado and claimed they were solving spreadsheet inefficiencies.

Factivate “was listed as one of the top 50 companies to watch in 2015 by Colorado and claimed they were solving spreadsheet inefficiencies.”

Since you had to request an invitation, I signed up for one here. Shortly after, I was contacted by their team and received access to my cloud-based spreadsheet which promised to give me my sanity back. When I logged in for the first time, I noticed a number of icons for Google Analytics, Facebook, Stripe, and so on on a data integration panel in my home screen so I clicked on one to find out more. The site asked for my Google login and after entering it, I entered a new spreadsheet and immediately saw all of my Google Analytics data on a tab immediately to the right. I then selected the information I wanted to populate on my spreadsheet and it began to fill my with numbers (HALLELUYA!) while updating every hour.

But wait, it gets better:

I not only added Google Analytics data but began adding new tabs with other analytics data from Facebook, Twitter, even Hubspot; all with a few clicks and NO PROGRAMMING. Hot damn!

It took me about 2 seconds to get access to that data and maybe a few minutes to choose which data to add to my spreadsheet. Compared to macros, plugins, and programming; this tool was my savior!

Since I knew spreadsheets, I immediately began building a template report that would summarize all of this data in a report I could share with my company on a daily, weekly, or even monthly basis. When I was done, not only did it look beautiful, but I had probably saved about 2 months of my year in building these things.

With this tool, I didn’t really need to hack anything else. At the same time, I was able to focus on my real job (not reporting!) and landed an entirely new account worth more than $250,000+; something I’m not sure I could’ve accomplished without Factivate. Thank you very much for helping me enjoy my marketing job once more!

About Factivate:

Factivate is a software company part of the Google for Entrepreneurs community in Durham, NC. Factivate provides business users with a cloud-based data analytics platform that can sync with different online tools to create automated, real-time spreadsheet reports and web dashboards with just-in-time business reactions. The result of our intelligent spreadsheet is accurate data analysis that drives better business decisions while reducing manual spreadsheet report time and errors by a factor of 150+ hours/employee/year. Factivate requires no learning curve or programming knowledge. In sharp contrast to the complex and expensive business intelligence tools on the market today, if you know spreadsheets, then you know 95% of what you need to use Factivate–we’ve got the rest covered.