Skill #3: Make the Data Usable and Easy for Your Team

After we pull our raw data into Sheets and turn it into something useful, it’s time to make it usable for our team.

When our goal is to communicate what we've learned from data, visualizations are our best friend:

Charts

Tables

Sexy combinations of charts and tables (aka baller dashboards)

You can build charts, tables, and dashboards directly within Sheets — but if you're really looking to make life easy on your team, connect Sheets to Google Data Studio to make beautiful, interactive reports.

Making Data Usable: Prepping your Sheet for Data Studio

Two things to prepare in your Sheet before pushing data up to Data Studio:

Dates must be in yyyy-mm-dd or yyyymmdd format. Dates like “Mon May 3 2002 12:23:00” won't be recognized

Keep headers in the first row of your Sheet, and name them how you’d like them to display in Charts or Tables

Each Google Sheets tab is equal to its own source in Data Studio, so anything you’d like to look at on the same chart needs to be in the same tab

How Zozi and Zapier Use Google Sheets: Manage a Distributed Content Team

Two companies. Two content teams. Two completely different approaches.

One company wrote blog posts, the other built landing pages.

But even with the differences, there was a similarity. Both managed a distributed team of writers remotely — and both relied upon Google Sheets to keep their editorial ship moving with a league of contributors.

Zozi: Solving for volume

Zozi was a marketplace of local activities and getaways (since acquired by Peek), so Cyndy Willis-Chun of the Zozi team needed to build unique landing pages for each new business who listed on their site.

Their volume was no joke: They regularly built hundreds of landing pages at a time.

To manage the process, Zozi decided to build a series of Google Forms and Sheets. Once optimized, this took Cyndy's time to prepare 200 freelancer assignments down from 10 hours to 10 minutes.

“Automating with Google Sheets meant that we could give writers feedback, and do higher quality work. Our moods improved considerably, and there was much less swearing in the office.”

Zapier: Solving for consistency

Matthew Guay and the Zapier team had to-do lists and deadlines which sounded good on paper, but didn’t fit how they actually published their content.

To help them focus on how content is actually created, Matthew and his team built an editorial calendar in Google Sheets. After they launched, they were able to keep an accurate view of their content pipeline.

“It's a great way to manage editorial process for smaller teams, when there's just enough clutter to make it tough knowing what's getting published and when.”

To help you manage your content, I worked with Matthew and Cyndy to produce a template.

How Webris Uses Google Sheets: Delete Useless Pages From a Site

On the web, so much new content is created every day. Older content goes stale quickly.

Ryan Stewart and his company WEBRIS help companies keep their content updated and delete remove stale, harmful SEO pages.

“We only want people and search engines to see what best represents our business.”

Manually finding pages to cut (and pages to improve) is a data challenge:

Do you look just at pageviews?

Include metrics like bounce rate, exit rate, or pages per session?

How do you measure what’s working and what’s not?

It gets messy quickly.

To easily tell which content to keep, delete, or refresh, Ryan and I developed an automated Sheet template:

Step 1: Raw data

Web data lives in so many different places, but this template pulls from all of them.

Traffic data lives in Google Analytics, and search data in Google Search Console. We pulled both into Sheets using Supermetrics.

The other data sources — backlink data from Ahrefs, keyword data from SEMrush, titles and meta descriptions from Screaming Frog — had to be imported via CSV, since they didn't have easily available APIs to pull data into Sheets.

Step 2: Formulas

Since the main goal of this template is to pull data together from different sources, it leans heavily on VLOOKUP to aggregate data on one line for each page on a site.

vlookup(B2:B,'Supermetrics Data'!A2:D,3,0)

The rest of the formulas are simple math with IF / THEN statements:

IF a page has links and organic traffic, THEN leave it

IF a page has no links and no organic traffic, THEN delete it

The “Getting Started” variables in this formula are just looking up the thresholds for organic traffic, bounce rate and conversion rate that you set when configuring the template:

My biggest takeaway is that I need to master Google Sheets if I'm to set achievable yet ambitious objectives that I can influence via monthly and weekly activities. Please, PLEASE, I'm dyin for the template vault so I can learn more quickly. Thank you!

Over the last year or so I've been using Google sheets for invoicing and manually keeping track of data. In the last couple of weeks, I've been thinking of ways to automate tasks related to marketing and the solution keeps coming back to Google Sheets. It's quite impressive. Thank you for the great post and the publishing automation template!

Mind. Blown. I never thought of using Google Sheets as such a robust automation tool. I've only just begun to scratch the surface with Google Sheets, but the first thing I'm going to be doing is using it to delete stale pages from my site. Thanks for the awesome tips!

Fantastic post! I've been following David since being introduced to his mastery of Google Sheets through Webris. This really is the future of data research and opens up the field to all non-developers.

Great tips, very inspiring. When you start building an army of Google Sheets, don't overlook IMPORTRANGE to suck in data to one spreadsheet from another. So useful and avoids repeating things over and over. Also helpful for getting round API call limits - get the data once, clone it from Sheet to Sheet.