Spreadsheets import, visualize & share web data

By: Joan Cole & Peter Howley

Measurement is critical to making data-driven decisions, but web analytics and other data must be shared, visualized, and put in context to have the most impact in an organization. Google Analytics, for example, is a powerful tool with good “out of the box” tools to view your website data. But its reporting, charting, and collaboration features are relatively limited, so that your ability to factor in other data, identify findings, and convince others to take action is limited, as well.

Luckily, it’s easier than ever to import web analytics data into a tried-and-true tool for sharing, visualizing, comparing, and performing calculations on data: the spreadsheet. Tools such as Excellent Analytics, GA Data Grabber, and Nick Mihailovski’s Google Apps Script automatically access the Google Analytics API to drop data into your Excel or Google spreadsheets, without manual exporting or pasting. Now functionality like the ReportBuilder tool we use with the premium Adobe SiteCatalyst powered by Omniture is available to users of free web analytics.

Once data is in Excel and Google Spreadsheets, all of the usual functionality is available to manipulate data to your heart’s content, compare it to goals and other metrics, and share it with colleagues. This means that all of the spreadsheet’s formulas and functions can be used to slice and dice the data, and pivot tables and charts can be used to customize reports and dashboards to communicate the meaning behind the data.  More customized output translates to more buy-in, better recommendations, and better data-driven decisions.

google analytics excel

Imagine you just launched a campaign to increase engagement on your website. It’s the second campaign in two months, and the third campaign since this month last year. You want some of your reports to specify the exact landing page URLs, but you want other reports to roll up the data.  You also want to update these reports in a heartbeat.

How would you do this with Google Analytics standard reports?  The short answer is – you can’t.  Google Analytics will provide you with trend lines, and you can even compare them to one prior time period.  But you can’t create one report of the data the way you need it.

But with a spreadsheet import tool, all of the analysis you need is possible, if you are willing to do a fair amount of setup.  Some examples:

Empirical Path has two clients for whom Excellent Analytics and Microsoft Excel were the best fit for the analysis and reporting we recommended.  They are from two very different industries, identity protection and tourism, but both need Excel’s ability to auto-update “boss-ready” Word documents, and both require reports that pull from multiple Google Analytics Profiles.

  • We started by summing up pageviews and visits for each family of websites, then we drilled down into key dimensions like traffic source and landing page.
  • For top performers, we imported both volume metrics and engagement metrics, like Time on Site, then calculated month-over-month and year-over-year movements.
  • We also cOlor-coded the resulting tables to highlight potential issues or a job well done.
  • Now we update these reports monthly with a few clicks, so we can focus on the findings and recommendations that will help our client improve results.

Three other clients prefer the built-in sharing and version control features of Google Docs, so we deployed the new Google Apps Script from the Google API team to auto-update their highly customized reports with two clicks.

  • For a retail client, we incorporated the company’s monthly revenue goals – which web analytics tools just can’t know – to translate key metrics into “Percent Versus Goal.”
  • For a higher education client, we incorporated row filters to focus on key pages — such as Admissions — for each degree program, and used look-up tables to compare metrics for current page names with their prior-year equivalents.
  • For a government client, we created pull down menus in the Google Spreadsheet to allow easy analysis of the organization’s departments, as most users only care about their department (which is stored as a Custom Variable, and therefore can’t easily be used to create Profiles for each department).

google analytics google spreadsheet

Ultimately, we created reports that allowed each of these clients to visualize the success or failure of key sites, segments, or marketing efforts; share those visualizations in colleagues’ preferred formats; and to update those findings with just a few clicks.

So, what does all this mean?  To be sure, the setup and management of these reports is neither easy nor quick, but updating them each period is.  And the upfront effort is worthwhile: your data is put into context so that you can tell a story, in relative terms, that your marketing managers and decision makers will understand.  Your data will be richer and more available to others, which will enable more meaningful insights and better, more informed decisions.

Contact us to learn more about getting your web analytics data into the spreadsheets you use to run your business.

Joan Cole is a digital analytics and market research professional who has worked on several projects for Empirical Path.  She has over a decade of experience in the market research, technical support, and customer service industries and has helped numerous organizations like Eastman Kodak, ShopRunner, the University of Wisconsin and several non-profit organizations solve their problems by providing actionable insights from primary and secondary market research projects and website analysis.  She holds her B.S. degree from the State University of New York and her M.B.A. from the University of Wisconsin and will be completing the University of British Columbia’s Award of Achievement program in Digital Analytics this year.  Back into the market after a stint as Stay-At-Home Mom, Joan is looking for a role as a business team leader involved in strategic marketing.

Did this interest you? Reach out to learn
how we can help you.

Contact Us

When you reach out to Empirical Path, you get in touch directly with our fully qualified and experienced consultants.   Whether you need an analytics audit, training, support, or a custom quote on Google Analytics 360 licensing, let’s talk today.