The SEO Content Audit Template

A Sheets data pipeline recipe

What am I going to do?

It’s the first question we ask ourselves when setting out to improve our work.

And if you’re like me, that journey begins with data.

It’s a dangerous first step though - wrangling data can deceptively feel *like work in itself*.

But it’s not.

And that feeling of swimming in your data - often called analysis paralysis, or data puke - is a disease that impairs our decision making.

It’s the fundamental struggle of marketers, project managers, analysts and founders everywhere.

How can we free up time to actually make decisions and take action, versus just wrangling data?

Enter the Personal Data Pipeline

So a while back, I floated a simple idea to CIFL members:

What if a Google Sheet wrangled your data for you, so that you could focus on taking action?

This idea evolved into the Personal Data Pipeline a Google Sheets framework for transforming data from your favorite tools into the answers to your questions.

And the template I share below, an update to the SEO Content Audit template I built with Ryan Stewart, is the first free and public example of PDP in action.

If you’d like to head straight to the template, grab it from the CIFL Template Vault.

In practice, it looks super wonky and dull (screenshot below) - but let’s talk about how and why it simplifies your work.

alt text

Get off the treadmill

When you’re doing an SEO audit, or any complex spreadsheet process, you can quickly scale the hedonistic treadmill of complexity.

You aggregate more data, from more sources.

More time spent watching the Google Sheets progress bar scroll interminably.

alt text

More time debugging increasingly complex formulas.

alt text

All of this means less time for decision-making.  Less time for action.

PDP eliminates this headache in three ways: data modeling, snapshotting and testing.

But WTF are those?

Part 1: Data modeling

Most of the time we waste in spreadsheets is in wrangling raw data into a clean and cohesive dataset.

This is the work that saps our will to take action.

But since we all use roughly the same data sources - web and social media analytics, SEO tools, etc - why are we all running around writing our own formulas?

Shouldn’t we be able to wrestle this beast to the ground, once and for all?

For example, in the Content Audit template I mentioned above, raw data from 6 sources is woven together into one table (Google Analytics, Google Search Console, Screaming Frog, Ahrefs, Majestic SEO, and Moz).

I imagine many of you have done some variation of that weaving (and probably not had much fun doing it).

PDP does this work for you with a standardized set of formulas, so that you can focus on taking action and making decisions:

Raw Google Search Console data…

alt text


Becomes metrics by Landing Page…

alt text


And is displayed in a table with one vlookup…

alt text

What those formulas say or do isn’t necessarily important.

What is important is that, in coordination, they strip your raw data down to only the pieces you need to take action.

Think of it like butchering a cow into just the tenderloin.

OK, that’s enough - I’m ready for the template!

Part 2: Snapshotting

Remember that pesky Sheets progress bar?

Sheets lets formulas run constantly in the background, which slows down your Sheet in an

infinite loop of calculation.

It makes working in a Sheet of any size basically impossible.  I’ve pulled my hair out over many afternoons spent in progress bar purgatory.

Excel has the F9 key to save us from this, but Sheets doesn’t - so we built it.

Using a little bit of Google Apps Scripting, PDP calculates formulas only when you tell it to.

alt text

For this SEO Content Audit template, that means when you import sitemaps into the Sheet, that data will stay consistent (versus the formula re-running every time you open the Sheet).

Part 3: Testing

Being confused is never fun, especially when you’re dealing with a complex spreadsheet.

You should never be confused whether a data import or function run worked or not.

Which is why testing is central to PDP - each step of executing a template contains a test to confirm that it ran.

In the case of this Content Audit template, it tests the number of records populated from each source that you pull in:

alt text

You’ll notice that each test also includes a date + time when the metric was last aggregated (thanks to PDP’s audit log).

Just the Beginning

This is about more than just this one Content Audit template, and the snazzy things we built into it to save you time.

It’s about our human freedom, as people who work with data in Google Sheets.

What if you could ditch your spreadsheet, and spend the afternoon laying in the grass at the park, staring at the sky?  How much would your decision-making improve?

The more we can standardize the way we aggregate + analyze data, the more time we free up to actually use our brains - to decide what we’re going to do.

Get started below by diving into the CIFL Template Vault on Trello, which includes the SEO Content Audit template featured in this post.