Tuesday , November 07, 2017 - 1:00 PM
Wrangling data sources into one cohesive report can be a huge challenge, especially if you have to create reports weekly or monthly. There are a number of very pricey services that support this type of work, but do you really need to pay hundreds, and in some cases, thousands of dollars a month, to do it? The short answer is no.
Google’s Data Studio is free and open to anyone with a Google account. I’ve been using it for just about a year now to track a slew of marketing activities each week, and the results are impressive. Before we tackle the basic steps of getting started, let’s talk about why a data visualization program can improve your workflow and, more important, how it can lead to a better understanding of performance.
We are overwhelmed with data. According to researchers at MIT, the amount of data that crosses the Internet every second is greater than all the data stored in the Internet just 20 years ago. The purpose of data visualization is to simplify data values, detect patterns, communicate important concepts that lead to action. Visualizations are the easiest way for our brains to receive and interpret large amounts of information. And you don’t need a degree in computer science to generate them — or a big budget.
A picture is worth a thousand words, and in this case, a picture is worth a thousand lines of data. If you’re not using a visualization tool, reporting can be an onerous task. You may have multiple sources of data, say Google Analytics, social media platforms, Salesforce, and internal reports generated from multiple departments — the list could be very long.
For each item you’re tracking, you likely have a spreadsheet inside Office or Google. You make pivot tables to aggregate data. You make charts, tables and graphs and export them to your report. Some of your service may provide their own dashboard online, which may look really good, but all you can do is take a screenshot for your own report. It’s a lot of cutting and pasting, and each week you’ll start with new sets of data and repeat the process. While you’re spending all of this time creating your report, important patterns can be missed.
Enter Google Data Studio, which acts as a warehouse for all of your source data. Because this is a Google product, it includes some “connectors” that will generate charts automatically from Google products, including Analytics, AdWords, YouTube. There is also a connector marketplace that provides additional connectors for social media platforms, Amazon sellers, some email programs and others. But you may not find what you need because it’s internal company data or a some other product. That’s where your spreadsheets come in and these will likely be your most frequently used data sources.
To use spreadsheets, you must put them in Google Drive. If you are a Microsoft Office user, you’ll need to copy and import your spreadsheet to Google Drive, which is a easy process—just drag and drop. I recommend setting up a new folder to house all of your spreadsheets for your report. While you can bring in a new spreadsheet for each reporting period, it’s much more efficient to create master spreadsheets for all reports that include a timestamp or date. This way you will avoid adding a new data source. You can simply add a data filter and adjust as needed. You will be able to quickly update your visualization for a week, a month, a quarter and for the full year. And that’s the beauty of a connected report: you can look at any period at any time by adjusting this filter. Further, one data source can be used for multiple visualizations by changing the dimension. A dimension corresponds to the fields in your spreadsheet.
To get started, open Google Studio and create a new report. On the top of your screen, you’ll see icons that represent the different types of visualizations you can create, including the standard options like bar charts and pie charts, plus maps and what Google calls a scorecard. Scorecards are useful for showcasing one piece of data such as the total number of products sold. Once you’ve selected your graphic, you will use the menu on the right to create it. First, select your data source. This will open a new panel where you can select Google Sheets and then your spreadsheet. Make sure the spreadsheet is open in a new tab. Data Studio will then ask you to review the fields. Here it is imperative that you have a way to count the data—each record must have a unique identifier such as an order number. If your data does not have one, go back to your spreadsheet and add a new column called ID. You can fill this is with consecutive numbers.
Once you get the hang of it, you’ll find Data Studio is easy to use, especially compared with other methods. What once took my team two days to prepare, now takes about two hours.
Leslie Meredith has designed and manages several international websites and now runs the marketing for a global events company. She writes about personal technology. You can email her at email@example.com.
Sign up for e-mail news updates.