Lightweight data visualization for low-budget publications
The tech-world is obsessed with Big Data, but even Small Data can be immensely useful for small businesses and publications – if the stakeholders have access to the right tools.
When I started at the Arkansas Times as their only technical staff-member, I made it a priority to dig up and expose various streams of data being generated by different parts of the company.
- Advertiser Analytics (DFP)
- Web Traffic Analytics
- Social Traffic and Metrics
- Ticketing & Events
- Subscribers (paying members)
- Email newsletters
- Videos (YouTube and Facebook)
Tableau was an obvious first choice and I really enjoyed the free version of their software, but the price tag was too hefty and the free version won’t let me save files locally. Coincidentally, as soon as I’d given up on Tableau, Google released Data Studio and I began digging in.
Most of this data was exported as CSV files from various tools in use by the staff. A good portion of it was already available from its respective service (Google Analytics, for example). We’ll be using exported DFP data and Google Analytics for this example.
Procure, standardize, and sanitize
For the DFP data, I created a custom query with all the dimensions and metrics requested by the Sales team. My report is historical (last six months) and provides filters to exclude inactive advertisers, units, and placements.
After setting up and testing your query to make sure you’re getting the data you want, check out the Scheduling and permissions section. Here you can set up your query to be ran at specific times on a repeat schedule. Our advertising runs by the week so my report sends me a CSV file every Monday.
Note that I’ve unchecked “Include header” and the others. These are extra headers/info that are exported in the CSV, but on top of the important data — so I’d just have to remove it anyway.
Once the CSV file is ready, I open it in Excel (or Sheets) and remove the bottom row of totaled data, ensure columns are formatted with the proper data type (dates as dates, numbers as numbers, etc.), check for errors, and clean up any extraneous rows.
Keep in mind that this guide is for non-programmers. Some of the manual steps I mention could be automated. If you have a developer on-staff, then you should look into the DFP API.
Meet Data Studio
Google Data Studio (GDS) is built on top of Google Drive so integration is fantastic and built-in for a lot of sources. Its interface shows off the newest in Material design and interaction.
Before we start visualizing data, we need to connect a data source. A data source, as its name implies, is a source of raw data. These sources can be Google services (Analytics, YouTube), third-party services (MySQL, BigQuery), or a simple file upload.
Click the Create New Data Source button on the bottom right to get started. You’ll be presented with the Data Sources panel that slides up from the bottom. Here we’ll be choosing File Upload.
Create a New Data Set, then click Add Files to upload your CSF from DFP. Next you’ll edit the fields for your data — broken into dimensions and metrics.
Here you can review your fields, add new ones, edit their data type, and even create complex computed fields (for example, creating a Profit field calculated from revenue and expenses).
Click Add to Report to get back to the Editor.
Now that we have the data source connected and our fields cleaned, let’s start with a “clicks over time” graph.
Start by clicking the Time Series tool in the toolbar.
Now draw a rectangle. GDS will attempt to automatically fill the Time Series with data from your source. Clicking the View button on the top-right will take you to the read-only screen where you can interact with your graph.
The right sidebar houses the Properties of the selected object. In this panel, there are two tabs: Data and Style. Their purposes are pretty obvious, the Data tab controls which dimensions and metrics are shown and the Style tab controls the aesthetics.
The same graph above can be completely restyled to appear as it does below.
Filters and controls
GDS has already done some heavy lifting, but we can do more and provide data filtering and controls for stakeholders. Let’s add a date picker to filter the graph we created.
Select the Date Range tool and draw a box. Now you’ve got a date picker – click View to test it out. By default the Date Range tool is scoped to your current page. You can group a Date Range with a graph (or graphs) to scope it only to that group.
Now that we’ve got the date range covered, let’s add the ability to filter by a dimension (in my case, Advertisers from DFP).
Select the Filter Control tool and, you guessed it, draw a rectangle.
GDS will attempt to create a Filter using your data source, but using the Properties panel (right sidebar), you’re able to change the dimensions and metrics used for the filter control.
A handy modification to the Filter Control is found in its Style palette. With the Filter Control selected, open up the Style tab and check the Expandable option. This changes the control to a dropdown to save space.
Tip of the iceberg
This is a very basic introduction to setting up a data analytics dashboard. Google Data Studio brings a lot of enterprise-level tools and methods to smaller business and organizations that don’t have the budget for Tableau or other high-priced alternatives.
Being proactive as a company requires the entire staff to know where they are. Raw data is being collected by everyone from every source, but making any meaningful decisions based that data relies on being able to quickly understand and visualize the impact of certain events. Spotting trends is infinitely easier when looking at a line chart compared to a table.
Give Small Data the attention it deserves and see how it can help your organization stay aware and make informed decisions.
Director of Digital Strategy