Search results are sophisticated enough to show searchers not only the content they want, but in the format they want it. Being able to identify searcher intent and interest based off of ranking results can be a powerful driver of content strategy. In this week’s Whiteboard Friday, we warmly welcome Dana DiTomaso as she describes her preferred tools and methods for developing a modern and effective content strategy.

Click on the whiteboard image above to open a high resolution version in a new tab!

Video Transcription

Hi, everyone. Welcome to Whiteboard Friday. My name is Dana DiTomaso. I’m President and partner of Kick Point, which is a digital marketing agency based way up in Edmonton, Alberta. Come visit sometime.

What I’m going to be talking about today is using STAT for content strategy. STAT, if you’re not familiar with STAT Search Analytics, which is in my opinion the best ranking tool on the market and Moz is not paying me to say that, although they did pay for STAT, so now STAT is part of the Moz family of products. I really like STAT. I’ve been using it for quite some time. They are also Canadian. That may or may not influence my decision.

But one of the things that STAT does really well is it doesn’t just show you where you’re ranking, but it breaks down what type of rankings and where you should be thinking about rankings. Typically I find, especially if you’ve been working in this field for a long time, you might think about rankings and you still have in your mind the 10 blue links that we used to have forever ago, and that’s so long gone. One of the things that’s useful about using STAT rankings is you can figure out stuff that you should be pursuing other than, say, the written word, and I think that that’s something really important again for marketers because a lot of us really enjoy reading stuff.

Consider all the ways searchers like to consume content

Maybe you’re watching this video. Maybe you’re reading the transcript. You might refer to the transcript later. A lot of us are readers. Not a lot of us are necessarily visual people, so sometimes we can forget stuff like video is really popular, or people really do prefer those places packs or whatever it might be. Thinking outside of yourself and thinking about how Google has decided to set up the search results can help you drive better content to your clients’ and your own websites.

The biggest thing that I find that comes of this is you’re really thinking about your audience a lot more because you do have to trust that Google maybe knows what it’s doing when it presents certain types of results to people. It knows the intent of the keyword, and therefore it’s presenting results that make sense for that intent. We can argue all day about whether or not answer boxes are awesome or terrible.

But from a visitor’s perspective and a searcher’s perspective, they like them. I think we need to just make sure that we’re understanding where they might be showing up, and if we’re playing by Google rules, people also ask is not necessarily going anywhere.

All that being said, how can we use ranking results to figure out our content strategy? The first thing about STAT, if you haven’t used STAT before, again check it out, it’s awesome.

Grouping keywords with Data Views

But one of the things that’s really nice is you can do this thing called data views. In data views, you can group together parts of keywords. So you can do something called smart tags and say, “I want to tag everything that has a specific location name together.”

Opportunities — where are you not showing up?

Let’s say, for example, that you’re working with a moving company and they are across Canada. So what I want to see here for opportunities are things like where I’m not ranking, where are there places box showing up that I am not in, or where are the people also ask showing up that I am not involved in. This is a nice way to keep an eye on your competitors.

Locations

Then we’ll also do locations. So we’ll say everything in Vancouver, group this together. Everything in Winnipeg, group this together. Everything in Edmonton and Calgary and Toronto, group all that stuff together.

Attributes (best, good, top, free, etc.)

Then the third thing can be attributes. This is stuff like best, good, top, free, cheap, all those different things that people use to describe your product, because those are definitely intent keywords, and often they will drive very different types of results than things you might consider as your head phrases.

So, for example, looking at “movers in Calgary” will drive a very different result than “top movers in Calgary.” In that case, you might get say a Yelp top 10 list. Or if you’re looking for “cheapest mover in Calgary,”again a different type of search result. So by grouping your keywords together by attributes, that can really help you as well determine how those types of keywords can be influenced by the type of search results that Google is putting out there.

Products / services

Then the last thing is products/services. So we’ll take each product and service and group it together. One of the nice things about STAT is you can do something called smart tags. So we can, say, figure out every keyword that has the word “best” in it and put it together. Then if we ever add more keywords later, that also have the word “best,”they automatically go into that keyword group. It’s really useful, especially if you are adding lots of keywords over time. I recommend starting by setting up some views that make sense.

You can just import everything your client is ranking for, and you can just take a look at the view of all these different keywords. But the problem is that there’s so much data, when you’re looking at that big set of keywords, that a lot of the useful stuff can really get lost in the noise. By segmenting it down to a really small level, you can start to understand that search for that specific type of term and how you fit in versus your competition.

A deep dive into SERP features

So put that stuff into STAT, give it a little while, let it collect some data, and then you get into the good stuff, which is the SERP features. I’m covering just a tiny little bit of what STAT does. Again, they didn’t pay me for this. But there’s lots of other stuff that goes on in here. My personal favorite part is the SERP features.

Which features are increasing/decreasing both overall and for you?

So what I like here is that in SERP features it will tell you which features are increasing and decreasing overall and then what features are increasing and decreasing for you.

This is actually from a real set for one of our clients. For them, what they’re seeing are big increases in places version 3, which is the three pack of places. Twitter box is increasing. I did not see that coming. Then AMP is increasing. So that says to me, okay, so I need to make sure that I’m thinking about places, and maybe this is a client who doesn’t necessarily have a lot of local offices.

Maybe it’s not someone you would think of as a local client. So why are there a lot more local properties popping up? Then you can dive in and say, “Okay, only show me the keywords that have places boxes.” Then you can look at that and decide: Is it something where we haven’t thought about local SEO before, but it’s something where searchers are thinking about local SEO? So Google is giving them three pack local boxes, and maybe we should start thinking about can we rank in that box, or is that something we care about.

Again, not necessarily content strategy, but certainly your SEO strategy. The next thing is Twitter box, and this is something where you think Twitter is dead. No one is using Twitter. It’s full of terrible people, and they tweet about politics all day. I never want to use it again, except maybe Google really wants to show more Twitter boxes. So again, looking at it and saying, “Is Twitter something where we need to start thinking about it from a content perspective? Do we need to start focusing our energies on Twitter?”

Maybe you abandoned it and now it’s back. You have to start thinking, “Does this matter for the keywords?” Then AMP. So this is something where AMP is really tricky obviously. There have been studies where it said, “I implemented AMP, and I lost 70% of my traffic and everything was terrible.” But if that’s the case, why would we necessarily be seeing more AMP show up in search results if it isn’t actually something that people find useful, particularly on mobile search?

Desktop vs mobile

One of the things actually that I didn’t mention in the tagging is definitely look at desktop versus mobile, because you are going to see really different feature sets between desktop and mobile for these different types of keywords. Mobile may have a completely different intent for a type of search. If you’re a restaurant, for example, people looking for reservations on a desktop might have different intent from I want a restaurant right now on mobile, for example, and you’re standing next to it and maybe you’re lost.

What kind of intent is behind the search results?

You really have to think about what that intent means for the type of search results that Google is going to present. So for AMP, then you have to look at it and say, “Well, is this newsworthy? Why is more AMP being shown?” Should we consider moving our news or blog or whatever you happen call it into AMP so that we can start to show up for these search results in mobile? Is that a thing that Google is presenting now?

We can get mad about AMP all day, but how about instead if we actually be there? I don’t want the comment section to turn into a whole AMP discussion, but I know there are obviously problems with AMP. But if it’s being shown in the search results that searchers who should be finding you are seeing and you’re not there, that’s definitely something you need to think about for your content strategy and thinking, “Is AMP something that we need to pursue? Do we have to have more newsy content versus evergreen content?”

Build your content strategy around what searchers are looking for

Maybe your content strategy is really focused on posts that could be relevant for years, when in reality your searchers are looking for stuff that’s relevant for them right now. So for example, things with movers, there’s some sort of mover scandal. There’s always a mover who ended up taking someone’s stuff and locking it up forever, and they never gave it back to them. There’s always a story like that in the news.

Maybe that’s why it’s AMP. Definitely investigate before you start to say, “AMP everything.” Maybe it was just like a really bad day for movers, for example. Then you can see the decreases. So the decrease here is organic, which is that traditional 10 blue links. So obviously this new stuff that’s coming in, like AMP, like Twitter, like places is displacing a lot of the organic results that used to be there before.

So instead you think, well, I can do organic all day, but if the results just aren’t there, then I could be limiting the amount of traffic I could be getting to my website. Videos, for example, now it was really interesting for this particular client that videos is a decreasing SERP for them, because videos is actually a big part of their content strategy. So if we see that videos are decreasing, then we can take a step back and say, “Is it decreasing in the keywords that we care about? Why is it decreasing? Do we think this is a test or a longer-term trend?”

Historical data

What’s nice about STAT is you can say “I want to see results for the last 7 days, 30 days, or 60 days.” Once you get a year of data in there, you can look at the whole year and look at that trend and see is it something where we have to maybe rethink our video strategy? Maybe people don’t like video for these phrases. Again, you could say, “But people do like video for these phrases.” But Google, again, has access to more data than you do.

If Google has decided that for these search phrases video is not a thing they want to show anymore, then maybe people don’t care about video the way that you thought they did. Sorry. So that could be something where you’re thinking, well, maybe we need to change the type of content we create. Then the last one is carousel that showed up for this particular client. Carousel, there are ones where they show lots of different results.

I’m glad that’s dropping because that actually kind of sucks. It’s really hard to show up well there. So I think that’s something to think about in the carousel as well. Maybe we’re pleased that that’s going away and then we don’t have to fight it as much anymore. Then what you can see in the bottom half are what we call share of voice.

Share of voice

Share of voice is calculated based on your ranking and all of your competitors’ ranking and the number of clicks that you’re expected to get based on your ranking position.

So the number 1 position obviously gets more ranks than the number 100 position. So the share of voice is a percentage calculated based on how many of these types of items, types of SERP features that you own versus your competitors as well as your position in these SERP features. So what I’m looking at here is share of voice and looking at organic, places, answers, and people also ask, for example.

So what STAT will show you is the percentage of organic, and it’s still, for this client — and obviously this is not an accurate chart, but this is vaguely accurate to what I saw in STAT — organic is still a big, beefy part of this client’s search results. So let’s not panic that it’s decreasing. This is really where this context can come in. But then you can think, all right, so we know that we are doing “eeh” on organic.

Is it something where we think that we can gain more? So the green shows you your percentage that you own of this, and then the black is everyone else. Thinking realistically, you obviously cannot own 100% of all the search results all the time because Google wouldn’t allow that. So instead thinking, what’s a realistic thing? Are we topping out at the point now where we’re going to have diminishing returns if we keep pushing on this?

Are we happy with how we’re doing here? Maybe we need to turn our attention to something else, like answers for example. This particular client does really well on places. They own a lot of it. So for places, it’s maintain, watch, don’t worry about it that much anymore. Then that can drop off when we’re thinking about content. We don’t necessarily need to keep writing blog post for things that are going to help us to rank in the places pack because it’s not something that’s going to influence that ranking any further.

We’re already doing really well. But instead we can look at answers and people also ask, which for this particular client they’re not doing that well. It is something that’s there, and it is something that it may not be one of the top increases, but it’s certainly an increase for this particular client. So what we’re looking at is saying, “Well, you have all these great blog posts, but they’re not really written with people also ask or answers in mind. So how about we go back and rewrite the stuff so that we can get more of these answer boxes?”

That can be the foundation of that content strategy. When you put your keywords into STAT and look at your specific keyword set, really look at the SERP features and determine what does this mean for me and the type of content I need to create, whether it’s more images for example. Some clients, when you’re looking at e-commerce sites, some of the results are really image heavy, or they can be product shopping or whatever it might be.

There are really specific different features, and I’ve only shown a tiny subset. STAT captures all of the different types of SERP features. So you can definitely look at anything if it’s specific to your industry. If it’s a feature, they’ve got it in here. So definitely take a look and see where are these opportunities. Remember, you can’t have a 100% share of voice because other people are just going to show up there.

You just want to make sure that you’re better than everybody else. Thanks.

Sign up for The Moz Top 10, a semimonthly mailer updating you on the top ten hottest pieces of SEO news, tips, and rad links uncovered by the Moz team. Think of it as your exclusive digest of stuff you don’t have time to hunt down but want to read!

One of the more interesting reveals that Dan Francis, Senior Product Manager for LinkedIn Talent Insights, provided in a recent talk about the Talent Insights tool is how LinkedIn is using machine learning to determine skills of people. He says that there are now over 575 million members in the LinkedIn database and there are 35,000 standardized skills in LinkedIn’s skills taxonomy. The way LinkedIn is figuring out what skills a member has is via machine learning technology.

LinkedIn Using Machine Learning to Determine Skills

The skills data in Talent Insights comes from a variety of sources, mainly from a member’s profile. There are over 35,000 standardized skills that we have in LinkedIn’s skills taxonomy, and the way we’re figuring out what skills a member has is using machine learning. We can identify skills that a member has that’s based on things that they explicitly added to their profile.

The other thing that we’ll do is look at the text of the profile. There’s a field of machine learning called natural language processing and we’re basically using that. It’s scanning through all the words that are on a member’s profile, and when we can determine that it’s pertaining to the member, as oppose the company or another subject, we’ll say okay, we think that this member has this skill. We also look at other attributes, like their title or the company, to make sure they actually are very likely to have that skill.

The last thing that we’ll do is look at the skills a member has and figure out what are skill relationships. So as an example, let’s say that a member has Ember, which is a type of JavaScript framework, since we know that they know Ember, they also know JavaScript. So if somebody’s running a search like that, we’ll surface them in the results. I think that the most important reason why this is helpful and the real benefit to users of the platform is when you’re searching, you want to get as accurate a view of the population as possible. What we’re trying to do is look at all the different signals that we possibly have to represent that view.

575 Million People on LinkedIn Globally and Adding 2 Per Second

Today, LinkedIn has over 575 million members that are on the platform globally. This is actually growing at a pretty rapid clip, so we’re adding about two members per second. One of the great things about LinkedIn is that we’re actually very well represented in terms of the professional workforce globally. If you look at the top 30 economies around the world, we actually have the majority of professionals in all of those economies.

LinkedIn is the World’s Largest Aggregator of Jobs

I think there’s often a perception that most of the data’s directly from LinkedIn, stuff that’s posted on LinkedIn and job status is one notable exception to that. Plenty of companies and people will post jobs on LinkedIn, and that’s information that does get surfaced. However, we’re also the world’s largest aggregator of jobs. At this point there are over 20 million jobs that are on LinkedIn.

The way that we’re getting that information is we’re working with over 40,000 partners. These are job boards, ATS’s, and direct customer relationships. We’re collecting all of those jobs, standardizing them, and showing them on our platform. The benefit is not just for displaying the data in Talent Insights, the benefit is also when members are searching on LinkedIn.com, we’re giving them as representative a view of the job market as possible.

Many companies are working to build authentic and trusted brands with consumers. This is especially true with pharmaceuticals, biotech, and med-tech companies. The CEO of Sparrho, Dr. Vivian Chan, says that their approach combines artificial intelligence and 400,000 Ph.D.’s to deliver scientific data to companies. This data helps companies back up their marketing messages which enables them to more effectively build that vital trust with their customers.

Dr. Vivian Chan, Sparrho CEO, recently discussed on CNBC their unique hybrid AI approach to helping companies use science and information to back up their brands messaging:

AI Enables Humans to Make Better-Informed Decisions

Artificial intelligence is really about algorithms and how we can use data that we collect to enable humans to make better-informed decisions. I not at all about having computers make decisions on behalf of humans. In a way, I think it’s machines that will be helping evolve the tasks and not actually replacing the human roles. Human roles themselves will be evolving also as the technology improves. This allows humans to have more headspace to be thinking about things that machines can’t do right now.

Machines can’t necessarily summarize a lot of pieces of contextual analysis very well yet to a 100 percent accuracy and humans are still better at making nonlinear connection points. For example, being able to say that this mathematical equation is super relevant to an agricultural problem. If we don’t have the tagging and reference and citations humans are still better at making those nonlinear new connection points than machines.

Humans are still good at coming up with the questions. If you actually pose the right question and you train the data and the algorithms you might actually get the right answer. However, you still need to have the humans to be thinking about what the questions are in order to ultimately get the answers.

It’s About Using AI as a Means to an End

I think the angle is really thinking about using AI as a means to an end and not just the end. Ultimately, this is a hybrid approach and various different people are calling it differently. Even MIT professors are calling it a hybrid approach. We’re calling it augmented intelligence. We need to come up with a good relationship between humans and machines. Marketing is about building relationships. It’s about building relationships between brands and consumers and now how do we build that relationship digitally?

Using Science to Build an Authenticated Brand

In this digital age, consumers are a lot more tech savvy but are also information savvy. They want to know what the is science behind certain things. Even if you’re talking about CPG, consumer packaged goods, what is the science behind a shampoo product right now when it claims 98 percent prevention of hair loss? What is the real science behind that and how do we actually bring that simplified science-oriented message to the consumer? How can consumers educate themselves and make informed decisions based on the products and thereby build a stronger brand relationship?

Ultimately what we’re trying to do at Sparrow is simplify science to build trust in brands. Especially for marketing departments and brands, it’s really allowing them to have the evidence-based science and the facts because building a very authenticated brand is what is meaningful to consumers. Research says that about 71 percent of consumers immediately reject content that looks like a sales pitch. Building a relationship and having an authenticated brand and content is super important in building that relationship between brand and consumers.

Sparrho Provides Content as a Service On Demand

We’re going even wider with that by providing what we call content as a service or relevant content on demand. We then integrate that into the digital platforms or the brands. We have what we call augmented intelligence with over 16 million pieces of content that is augmented by a network of more than 400,000 monthly active PhDs in a150 countries. They curate and summarize what’s actually happening in the latest of science.

We know that in about 60 percent of pharmaceuticals, biotech, and even med-tech companies, are spending more than $ 50 million per year just in content. Content has been the major driver for a lot of their marketing. In pharmaceuticals, they’re trying to really bring that relationship that they have offline to online. It’s at the heart of this digital transformation age that we are going through. This is really helping bring that relationship online by using the right engaging content. Our goal with Sparrow is to drive more engagement and ultimately more sales.

Correlation studies have been a staple of the search engine optimization community for many years. Each time a new study is released, a chorus of naysayers seem to come magically out of the woodwork to remind us of the one thing they remember from high school statistics — that “correlation doesn’t mean causation.” They are, of course, right in their protestations and, to their credit, and unfortunate number of times it seems that those conducting the correlation studies have forgotten this simple aphorism.

We collect a search result. We then order the results based on different metrics like the number of links. Finally, we compare the orders of the original search results with those produced by the different metrics. The closer they are, the higher the correlation between the two.

That being said, correlation studies are not altogether fruitless simply because they don’t necessarily uncover causal relationships (ie: actual ranking factors). What correlation studies discover or confirm are correlates.

Correlates are simply measurements that share some relationship with the independent variable (in this case, the order of search results on a page). For example, we know that backlink counts are correlates of rank order. We also know that social shares are correlates of rank order.

Correlation studies also provide us with direction of the relationship. For example, ice cream sales are positive correlates with temperature and winter jackets are negative correlates with temperature — that is to say, when the temperature goes up, ice cream sales go up but winter jacket sales go down.

Finally, correlation studies can help us rule out proposed ranking factors. This is often overlooked, but it is an incredibly important part of correlation studies. Research that provides a negative result is often just as valuable as research that yields a positive result. We’ve been able to rule out many types of potential factors — like keyword density and the meta keywords tag — using correlation studies.

Unfortunately, the value of correlation studies tends to end there. In particular, we still want to know whether a correlate causes the rankings or is spurious. Spurious is just a fancy sounding word for “false” or “fake.” A good example of a spurious relationship would be that ice cream sales cause an increase in drownings. In reality, the heat of the summer increases both ice cream sales and people who go for a swim. That swimming can cause drownings. So while ice cream sales is a correlate of drowning, it is *spurious.* It does not cause the drowning.

How might we go about teasing out the difference between causal and spurious relationships? One thing we know is that a cause happens before its effect, which means that a causal variable should predict a future change.

An alternative model for correlation studies

I propose an alternate methodology for conducting correlation studies. Rather than measure the correlation between a factor (like links or shares) and a SERP, we can measure the correlation between a factor and changes in the SERP over time.

The process works like this:

Collect a SERP on day 1

Collect the link counts for each of the URLs in that SERP

Look for any URLs are out of order with respect to links; for example, if position 2 has fewer links than position 3

Record that anomaly

Collect the same SERP in 14 days

Record if the anomaly has been corrected (ie: position 3 now out-ranks position 2)

Repeat across ten thousand keywords and test a variety of factors (backlinks, social shares, etc.)

So what are the benefits of this methodology? By looking at change over time, we can see whether the ranking factor (correlate) is a leading or lagging feature. A lagging feature can automatically be ruled out as causal. A leading factor has the potential to be a causal factor.

We collect a search result. We record where the search result differs from the expected predictions of a particular variable (like links or social shares). We then collect the same search result 2 weeks later to see if the search engine has corrected the out-of-order results.

Following this methodology, we tested 3 different common correlates produced by ranking factors studies: Facebook shares, number of root linking domains, and Page Authority. The first step involved collecting 10,000 SERPs from randomly selected keywords in our Keyword Explorer corpus. We then recorded Facebook Shares, Root Linking Domains, and Page Authority for every URL. We noted every example where 2 adjacent URLs (like positions 2 and 3 or 7 and were flipped with respect to the expected order predicted by the correlating factor. For example, if the #2 position had 30 shares while the #3 position had 50 shares, we noted that pair. Finally, 2 weeks later, we captured the same SERPs and identified the percent of times that Google rearranged the pair of URLs to match the expected correlation. We also randomly selected pairs of URLs to get a baseline percent likelihood that any 2 adjacent URLs would switch positions. Here were the results…

The outcome

It’s important to note that it is incredibly rare to expect a leading factor to show up strongly in an analysis like this. While the experimental method is sound, it’s not as simple as a factor predicting future — it assumes that in some cases we will know about a factor before Google does. The underlying assumption is that in some cases we have seen a ranking factor (like an increase in links or social shares) before Googlebot has and that in the 2 week period, Google will catch up and correct the incorrectly ordered results. As you can expect, this is a rare occasion. However, with a sufficient number of observations, we should be able to see a statistically significant difference between lagging and leading results. However, the methodology only detects when a factor is both leading and Moz Link Explorer discovered the relevant factor before Google.

Factor

Percent Corrected

P-Value

95% Min

95% Max

Control

18.93%

0

Facebook Shares Controlled for PA

18.31%

0.00001

-0.6849

-0.5551

Root Linking Domains

20.58%

0.00001

0.016268

0.016732

Page Authority

20.98%

0.00001

0.026202

0.026398

Control:

In order to create a control, we randomly selected adjacent URL pairs in the first SERP collection and determined the likelihood that the second will outrank the first in the final SERP collection. Approximately 18.93% of the time the worse ranking URL would overtake the better ranking URL. By setting this control, we can determine if any of the potential correlates are leading factors – that is to say that they are potential causes of improved rankings.

Facebook Shares:

Facebook Shares performed the worst of the three tested variables. Facebook Shares actually performed worse than random (18.31% vs 18.93%), meaning that randomly selected pairs would be more likely to switch than those where shares of the second were higher than the first. This is not altogether surprising as it is the general industry consensus that social signals are lagging factors — that is to say the traffic from higher rankings drives higher social shares, not social shares drive higher rankings. Subsequently, we would expect to see the ranking change first before we would see the increase in social shares.

RLDs

Raw root linking domain counts performed substantially better than shares at ~20.5%. As I indicated before, this type of analysis is incredibly subtle because it only detects when a factor is both leading and Moz Link Explorer discovered the relevant factor before Google. Nevertheless, this result was statistically significant with a P value <0.0001 and a 95% confidence interval that RLDs will predict future ranking changes around 1.5% greater than random.

Page Authority

By far, the highest performing factor was Page Authority. At 21.5%, PA correctly predicted changes in SERPs 2.6% better than random. This is a strong indication of a leading factor, greatly outperforming social shares and outperforming the best predictive raw metric, root linking domains.This is not unsurprising. Page Authority is built to predict rankings, so we should expect that it would outperform raw metrics in identifying when a shift in rankings might occur. Now, this is not to say that Google uses Moz Page Authority to rank sites, but rather that Moz Page Authority is a relatively good approximation of whatever link metrics Google is using to determine ranking sites.

Concluding thoughts

There are so many different experimental designs we can use to help improve our research industry-wide, and this is just one of the methods that can help us tease out the differences between causal ranking factors and lagging correlates. Experimental design does not need to be elaborate and the statistics to determine reliability do not need to be cutting edge. While machine learning offers much promise for improving our predictive models, simple statistics can do the trick when we’re establishing the fundamentals.

Now, get out there and do some great research!

Sign up for The Moz Top 10, a semimonthly mailer updating you on the top ten hottest pieces of SEO news, tips, and rad links uncovered by the Moz team. Think of it as your exclusive digest of stuff you don’t have time to hunt down but want to read!

The short version of this post: Project management is a vital part of our job as marketers, but planning and visualizing projects over time is hard, so I’ve created a set of Google Sheets to make that work easier for you.

I’ve found this system helpful in a number of ways, so I’m sharing my templates here in case it’ll make your day a bit shorter. I’ll start off with a brief overview of what the sheets do, but in the latter section of this post I’ll also go into greater depth about how they work so you can change them to suit your own needs.

If you’d like to skip this post and get straight to the templates, you can access them here (but I’d recommend reading a bit about how they work first):

It’s worth mentioning: I don’t consider these sheets to be the only solution. They are a free solution that I’ve found pretty useful, but I have colleagues who swear by the likes of Smartsheet and Teamwork.

It’s also worth noting that different tools work better or worse with different styles. My aim with these sheets is to have a fairly concrete plan for the next three or four months, then a looser set of ideas for further down the line. When I’m filling out these sheets, I also focus on outcomes rather than processes – that helps cut down the time I spend updating sheets, and makes everything clearer for people to read.

The long version of this post is a lot like the short version above, but I talk more about some principles I try to stick to and how this setup fulfills them (shocker, eh?). As promised, the final section will describe how the sheets work, for anyone who runs into problems or wants to make something of their own.

The principles (which are about people as much as using the sheets)

Principle 1: We shouldn’t need to store all our information in our heads.

This is a simple one — if we have to regularly understand something complex, particularly if it changes over time, that information has to be on the page. For example, if I’m trying to plan a marketing strategy and I have to constantly look at the information on the screen and then shuffle it around in my head to work out what we have time for month to month, I’m going to lose the thread and, eventually, my mind.

The Planner Version sheet aims to solve this in a few ways. First, you write all the tasks down in the Task View tab, the time period you’re completing them in is on the far left (in my example, it’s the month the task is planned for), and there are other columns like status and category — but initially, it can just be a brain dump of what needs to happen. The idea here is that when you’re first writing everything out, you don’t have to think too much about it — you can easily change the dates and add other information later.

The Month View tab takes the information in the Task List tab and reorders it by the months listed in column A of the Task View (it could be other time periods, as long as it’s consistent).

This way you can look at a time period, see how much resource is left, and read everything you currently have planned (the remaining resource calculation will also take into account recurring tasks you don’t always want to write out, like meetings).

While the Month View tab can help you focus on specific time periods, it doesn’t give you a long-term view of the plan or task dependencies, so we have the two Gantt views. The Gantt View tab contains everything from sixty days ago and into the future, as long as you haven’t just marked the task as “Later.” The Category-Filterable Gantt only focuses on things that are planned for the next six months.

As the name suggests, you can filter this second Gantt to only show specific categories (you label tasks with categories in the Task View tab). This filter is to help with broader trends that are harder to notice — for instance, if the most important part of the project is a social campaign or a site change and you don’t get to it for six months, you may need to make sure everyone is aware of that and agrees. Likewise, if you need to be showing impact but spend most of your time reporting, you may want to change your plan or make sure everyone understands why things are planned that way.

Principle 2: No one knows everything (and they shouldn’t).

If you’re working on a project where you have all the information, then one of two things is likely happening:

You’ve really doubled down on that neuroticism we share

You’re carrying this thing — you should just quit and start your own company selling beads* or something.

We can trust that our clients/bosses have more context than we do about wider plans and pressures. They may know more about wider strategies, that their boss tenses up every time a certain project is mentioned, or that a colleague hasn’t yet announced their resignation. While a Google Sheet is never an acceptable substitute for actual communication, our clients or bosses may also have an idea of where they want the project to go which they haven’t communicated, or which we haven’t understood.

We can also trust that people working on individual tasks have a good idea of whether things are going to be a problem — for instance, if we’re allowing far too little time for a task. We can try to be as informed as possible, but they’re still likely to know something we don’t.

Even if we disagree that certain things should be priorities or issues, having a transparent, shared plan helps us kick off difficult conversations with a shared understanding of what the plan currently is. The less everyone has to reprocess information to understand it (see Principle 1), the more likely we are to weed out problems early.

This is all well and good, but expecting someone to absorb everything about a project is likely to have the opposite effect. We need a source of data that everyone can refer to, without crowding their thoughts or our conversations with things that only we as project managers have to worry about.

That’s why we have the Stakeholder Version of our sheets. When we write everything in the Planner Version, the Planned tab is populated with just the things that are relevant for people who aren’t us (i.e, all the tasks where the status isn’t “unpitched,” “cancelled,” “forgotten,” or blank) with none of the resource or project identifier information.

We never have to fill out the Stakeholder Version sheet — it just grabs that information from the Planned tab using importrange() and creates all the same Gantt charts and monthly views — so we don’t have to worry about different plans showing different information.

*Bees?

Principle 3: I’m going to miss stuff (less is more).

I’ll be honest: I’ve spent a bunch of time in the past putting together tracking systems that I don’t check enough. I keep filling them out but I don’t spend enough time figuring out what’s needed where. If we have a Stakeholder Version which takes out the stuff that is irrelevant to other people, we need the same for us. After all, this isn’t the only thing we’re thinking about, either.

The What-in-God’s-name-have-I-missed Version (God’s-I from now on) pulls in data from all of your individual project management sheets and gives you one place to go to be reminded about all the things you’ve forgotten and messed up. It’s like dinner with your parents in a Google Sheet. You’re welcome.

The three places to check in this version are:

Alerts Dashboard tab, which shows you the numbers of deadlines upcoming or missed, the work you need to budget for or brief, and how much unplanned budget you have per project, per month (where budget could just be internal people-hours, as that is still finite).

Task Issues tab, which gives a filterable view of everything over the next three months (so you can dig in to the alerts you see in step one).

Deadlines This Week tab so you have a quick reminder of what you need to complete soon.

An early conclusion:

Often, when I’m making a point, people tell me they hope I’ll wrap up early. This section is mainly proof of personal growth.

It’s also because everything after this is specific to using, changing, or understanding the project management sheets I’ve shared, so you need only read what follows if you’re interested in how to use the sheets or how I made them (I really do recommend dabbling with some uses of filter() and query(), particularly in conjunction with RegEx formulas).

Aside from that, I hope you find these resources useful. I’ve been getting a lot of value from them as a way to plan with people collaboratively and separate the concept of “project manager” from “person who needs to know all the things,” but I would be really interested in any thoughts you have about how to improve them or anything you think I’ve missed. Feel free to comment below!

Appendices & instructions

Some general notes

Quick notes on avoiding problems:

Make sure that when you copy the sheets, the sharing permissions for the Planner View is email- or at least organization-based (anyone with access to the Stakeholder View will see the Planner View URL). It’s a good idea to keep the God’s-I Version permissions email-based, too.

Try to follow the existing format of words and numbers as closely as possible when creating new information.

If you want a new row, I’d insert a row, select the one above, copy it down into the new row, then change the information — that way, the formulas in the hidden columns should still work for you.

If you want a new column, it might break one of the query() functions; once you’ve added it, have a quick look for formulas using =query() and consider changing the columns they reference that will have been affected by your change.

Quick notes on fixing problems:

Here’s a list of things to check for if you’ve changed something and it isn’t being reflected in the sheet:

Go through all the tabs in the stakeholder view and unhide any hidden columns

They usually just contain a formula that reformats text so our lookups work. See if any of those are missing or broken.

Try copying the formulas from the row above or next to the cell that isn’t working.

Try removing the =iferror portion of formulas.

A lot of the cells are set up to be blank if they break. It makes it easier to read the sheet, but can make it harder to know whether something is actually empty or just looks empty.

If one sheet isn’t properly pulling through data from another, look for the =importrange() formulas and make sure there is one that matches the URL of the sheet you’re trying to reference and that you’ve given permission for the formula to work — you’ll need to click a button.

Check the Task View tab in the Stakeholder Version and Project URLs tab in the God’s-I Version

Have you just called a task “Part 4” or similar? There is a RegEx formula which will strip that out.

Have you forgotten to give a task a type? If so, the Gantt view will warn you in the Status column.

The query function

The =query() function in Google Sheets is awesome — it makes tons of things tons easier, particularly in terms of automating data manipulation. Most of what these sheets do could be achieved with =query, but I’ve often used =filter (which is also very powerful) because =filter is apparently quicker in Google Sheets and at times these sheets have a lot to process.

RegEx

You shouldn’t need to know any RegEx for this sheet, but it is useful in general. Here the RegEx is mainly used to remove the “Part #” in multi-part tasks (see below) and look for anything that matches multiple options — for instance, when selecting multiple categories in the Category-specific forward-facing Gantt tab (see below). RegEx is only used here in RegExmatch(), RegExextract(), RegExreplace(), or as part of the query function where we say “matches.”

Query/filter and isblank

A lot of the formulas in these sheets are either filter() or query() or are wrapped in =if(isblank() — that’s basically because filter and query functions can fill more cells than just the one you put the formula in. For example, they can fill a whole row, column, or sheet. That means that other cells are calculating or looking up against cells which may or may not be empty, so I’ve added the isblank() check so that the cells don’t break when there isn’t information somewhere, but as you add information you don’t have to do as much copying and pasting of formulas.

Tick boxes

The tick boxes are relatively new in Google Sheets. If you need another one, just copy it from an existing cell or select from the “Insert” menu. Where I’ve used tick boxes, I often have another formula in the sheet which filters rows based on what boxes are ticked, then creates a RegEx based on the values that have a tick next to them.

You don’t need to understand this to use the sheets, but you can see it in the rows I’ve unhidden in the Category-specific forward-facing Gantt tab of the Stakeholder Version sheet.

Quick tip — if you want to change all the boxes to ticked/unticked and don’t want to have to do so one by one, you can copy a ticked or unticked checkbox across all the other cells.

How to add tasks to the list

In the task view, the most important things to include are the task name, time period it’s planned for, cost, and type.

For ease, when creating a new task I recommend inserting a row, copying the row above into it, and then changing the information, that way you know you’re not missing any hidden formulas.

Again, don’t bother changing the Stakeholder Version. Once you’ve added the URL of the Planner Version to the =importrange() function, it will pull automatically from the Planner Version.

Splitting tasks across multiple time periods

You can put more than one thing in the time period for a task, just by separating it with “, “ (comma space). That’s because when we get the full list of months, we join all the individual cells together with “, “ then split them apart by “, “ and then dedupe the list — so multiple months in one cell are treated the same as all the other months.

=unique(transpose(split(JOIN(", ",'Task view'!A:A),", ",0)))

The cost-per-month formula in the Task List tab counts how many commas are present in the month column for that row, then divides the planned cost by that number — meaning the cost is split equally across all of the months listed.

=H2/(len(REGEXREPLACE(A2,"[^\,]*",""))+1)

If you don’t want the task to be completely equally split between different time periods, you can write “Part 1” or “Part 2” next to a task. As long as you write just “Part” and then numbers at the end of the name, that’ll be stripped out in column O of the task list tab so the different parts of a task will be combined into one record in things like the Gantt chart.

=REGEXREPLACE(B2,"Part \d+$ ","")

Working with the Month View tab (Planner and Stakeholder version)

A few key things are going on in the Month View tab. First, we’re getting all of the time periods we have listed in the Task View.

Because the months don’t always show up in the right format (meaning later filters don’t work), we then use a =text() formula in the hidden column B to make sure the months stay in the format we need.

Then, in the “deliverables” section of this tab, we use the below formula:

What we’re doing above is checking if the “month” cell of this row is has anything in it. If there is a month there, we filter the tasks in the Task View to only those that contain that month in the text month column. Then we use the transpose() function to change our filtered tasks from a vertical list to the horizontal list we see in the sheet.

Finally, we use the below formula to filter the costs we’ve listed in the Task View tab, the same way we filtered the task names above. Then we add together all the costs for the month (plus the standing monthly costs) and subtract them from the total amount of time/hours we have to spend. That way we calculate how much we have left to play with, or if we’re running over.

We also pull this value through to our God’s-I Version to see at a glance if we’ve over/under-planned.

How to make the Gantt charts work (and add categories)

Column C in the Task View tab is the category; you also need to fill this out for the Gantt charts to work. I haven’t forced the kind of categories you have to use because each project is different, but it’s worth using consistent categories (down to the capital letter) because we deduplicate the task categories, and that relies on all of the names being consistent.

What’s happening in the Gantt chart is each cell is a combination of a filter and vlookup (the below looks more complicated than it is).

The formula first checks if the task or month cells are blank. If not, it looks in the month cell in its column and cross-references with the task cell in its row. Where the intersection of a month and task matches a task in our Task View (as in the task in that row is taking place during the month in that column), the filter formula will return the category. For those interested, this might also have been achieved with index-match, but filter lets us match with RegEx so we can give multiple matching options and they don’t have to match exactly. Because we split tasks across multiple months, we need to be flexible in our matching.

The reason we check whether the task or month cells are empty, as mentioned above is so we can paste the above formula in all the cells of the Gantt chart and have them fill out as we add more months and tasks, rather than having to copy and paste the formula each time.

When our filter formula returns the specific category of our task, we take that value and run a vlookup in the Status and color code tab. (That’s only necessary so I could set up the conditional formatting for you so it won’t break when you change the specific category names.)

At the moment, the Gantt charts are set up to color-code the first 7 categories, plus a Deadline category if needed. If you want to add more, they’ll show up initially in the Gantt chart as a black block and you’ll need to set up conditional formatting to color-code them.

To add automatic color formatting for more categories, repeat the below process for each of the Gantt chart views in the Planner and Stakeholder sheets:

Select all the cells in the tab

Select “Conditional Formatting” from the Format menu

Find the rule with the black box next to it and make a note of what number it’s currently targeting from

Create a new rule for anything which equals the number in step 3, then set the same color for both the background and text of that rule

Change the rule that’s got a black block next to it to target one number higher

How to make the Category-Filterable Gantt Charts work

This tab uses our old friends, the =filter() and =query() functions. First we use filter to grab the full list of categories from the Status and color code tab we mentioned before:

Then we put Google Sheets’ shiny new checkboxes next to them (that’ll help us filter our data easily).

Normally we’ll hide row one, but it’s visible to show you a formula that looks at all of the categories and filters them to just those where the tick-box next to them is ticked. If there are none, it returns “(\d|Deadline)” meaning “either a number, or the word Deadline” in RegEx-speak (so anything in our list), because the vertical pipe “|” means “or” and “\d” means “number.”

If there is a tick next to one or more of the categories, the formula will return those things, separated with the “|” that, again, means “or” in RegEx.

Then in cell E3 we have a query formula. The reason we’re using =query and not =filter here is that we need to look for things in more than one column; filter can only really handle one column at a time.

The query function then checks the first six columns of our original Gantt chart, each time looking for any of the category numbers we’ve ticked (what the conditional formatting hides is that the category numbers are in that original Gantt, they’re just the same color as the cell shading). When no tick-boxes are checked, it returns anything that has falls in to any category over the next six planned months. Once we start ticking checkboxes, this will return only the things over the next six planned months that are in one of the categories we’ve selected.

All the formula above is doing is taking the Month, Task, Description, Blocker, Status, Category, and Full task columns, then showing every record where the status isn’t “unpitched,” “cancelled,” “forgotten,” or empty. That gives us a tab with the information we’re ready to share. We could also achieve this with =filter() if we reordered the data in the Task View tab, but this ordering of data is easier to work with, so we just use =query() and select only the columns we want here, combining the ranges horizontally by listing them between {} at the start of this formula.

Then, the Task View tab in our Stakeholder Version sheet file uses =importrange() to target that cleaned list we’ve created. To make sure the Stakeholder Version keeps functioning when you create copies of both of these files, all you need to do is go to the new Planner Version sheet and copy the URL of the page, then go to the Stakeholder Version, find the Task View tab, and update the importrange() formula in cell A1 to have the new URL of your Planner Version sheet. The cell will recalculate, you’ll need to grant permission, then it should work as normal.

How to update the God’s-I Version

This view gives you the following:

A quick look at the total number of tasks in any project which:

Have a deadline within 10 days of now

Have passed a deadline (with the task not completed)

Don’t have a deadline set

Aren’t briefed or aren’t budgeted for the next three months

It’ll also give you a quick look at the amount of unplanned budget per project, per month, to make sure you haven’t forgotten to plan a month and haven’t overplanned a month.

The God’s-I Version works in a similar way to the Stakeholder Version in that it pulls in information using =importrange(), but a key difference is that we want to pull from multiple sheets. Rewriting the formula could get to be a pain, so instead we can generate the formula we need in the Project URLs tab.

The only things you need to do are:

Add the URL of the new Planner View sheet you want to include in the Project URLs tab of the God’s-I Version

Grant permission for this sheet to access that sheet (you can click on the alert that appears in column A)

Copy the value in Cell B1, go to the All Imported Task Views tab and select cell A2, then paste the value into the top bar. It’s important that we don’t paste straight into the cell or the sheet will run the concatenate formula rather than the query formula we’re making.

It’s worth noting that this sheet will have all the information about every project you’re managing. Once it’s set up, you shouldn’t share access to anyone unless you’re happy with them seeing all the budgeting details for each of the sheets.

A late conclusion:

Why are you looking for a conclusion down here? It’s in the middle of the post under the title of “An early conclusion,” of course. Have a nice day!

Sign up for The Moz Top 10, a semimonthly mailer updating you on the top ten hottest pieces of SEO news, tips, and rad links uncovered by the Moz team. Think of it as your exclusive digest of stuff you don’t have time to hunt down but want to read!