Friday, September 21, 2018

Spectator to Partner: Turn Your Clients into SEO Allies - Whiteboard Friday

Posted by KameronJenkins

Are your clients your allies in SEO, or are they passive spectators? Could they even be inadvertently working against you? A better understanding of expectations, goals, and strategy by everyone involved can improve your client relations, provide extra clarity, and reduce the number of times you're asked to "just SEO a site." In today's Whiteboard Friday, Kameron Jenkins outlines tactics you should know for getting clients and bosses excited about the SEO journey, as well as the risks involved in passivity.

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

Video Transcription

Hey, everyone, and welcome to this week's edition of Whiteboard Friday. I am Kameron Jenkins, and I'm the SEO Wordsmith here at Moz. Today I'm going to be talking with you about how to turn your clients from spectators, passive spectators to someone who is proactively interested and an ally in your SEO journey.

So if you've ever heard someone come to you, maybe it's a client or maybe you're in-house and this is your boss saying this, and they say, "Just SEO my site," then this is definitely for you. A lot of times it can be really hard as an SEO to work on a site if you really aren't familiar with the business, what that client is doing, what they're all about, what their goals are. So I'm going to share with you some tactics for getting your clients and your boss excited about SEO and excited about the work that you're doing and some risks that can happen when you don't do that.

Tactics

So let's dive right in. All right, first we're going to talk about tactics.

1. Share news

The first tactic is to share news. In the SEO industry, things are changing all the time, so it's actually a really great tactic to keep yourself informed, but also to share that news with the client. So here's an example. Google My Business is now experimenting with a new video format for their post feature. So one thing that you can do is say, "Hey, client, I hear that Google is experimenting with this new format. They're using videos now. Would you like to try it?"

So that's really cool because it shows them that you're on top of things. It shows them that you're the expert and you're keeping your finger on the pulse of the industry. It also tells them that they're going to be a part of this new, cutting-edge technology, and that can get them really, really excited about the SEO work you're doing. So make sure to share news. I think that can be really, really valuable.

2. Outline your work

The next tip is to outline your work. This one seems really simple, but there is so much to say for telling a client what you're going to do, doing it, and then telling them that you did it. It's amazing what can happen when you just communicate with a client more. There have been plenty of situations where maybe I did less tangible work for a client one week, but because I talk to them more, they were more inclined to be happy with me and excited about the work I was doing.

It's also cool because when you tell a client ahead of time what you're going to do, it gives them time to get excited about, "Ooh, I can't wait to see what he or she is going to do next." So that's a really good tip for getting your clients excited about SEO.

3. Report results

Another thing is to report on your results. So, as SEOs, it can be really easy to say, hey, I added this page or I fixed these things or I updated this.

But if we detach it from the actual results, it doesn't really matter how much a client likes you or how much your boss likes you, there's always a risk that they could pull the plug on SEO because they just don't see the value that's coming from it. So that's an unfortunate reality, but there are tons of ways that you can show the value of SEO. One example is, "Hey, client, remember that page that we identified that was ranking on page two. We improved it. We made all of those updates we talked about, and now it's ranking on page one. So that's really exciting. We're seeing a lot of new traffic come from it.I'm wondering, are you seeing new calls, new leads, an uptick in any of those things as a result of that?"

So that's really good because it shows them what you did, the results from that, and then it kind of connects it to, "Hey, are you seeing any revenue, are you seeing new clients, new customers," things like that. So they're more inclined to see that what you're doing is making a real, tangible impact on actual revenue and their actual business goals.

4. Acknowledge and guide their ideas

This one is really, really important. It can be hard sometimes to marry best practices and customer service. So what I mean by that is there's one end of the pendulum where you are really focused on best practices. This is right. This is wrong. I know my SEO stuff. So when a client comes to you and they say, "Hey, can we try this?" and you go, "No, that's not best practices,"it can kind of shut them down. It doesn't get them involved in the SEO process. In fact, it just kind of makes them recoil and maybe they don't want to talk to you, and that's the exact opposite of what we want here. On the other end of that spectrum though, you have clients who say, "Hey, I really want to try this.I saw this article. I'm interested in this thing. Can you do it for my website?"

Maybe it's not the greatest idea SEO-wise. You're the SEO expert, and you see that and you go, "Mm, that's actually kind of scary. I don't think I want to do that." But because you're so focused on pleasing your client, you maybe do it anyway. So that's the opposite of what we want as well. We want to have a "no, but" mentality. So an example of that could be your client emails in and says, "Hey, I want to try this new thing."

You go, "Hey, I really like where your head is at. I like that you're thinking about things this way. I'm so glad you shared this with me. I tried this related thing before, and I think that would be actually a really good idea to employ on your website." So kind of shifting the conversation, but still bringing them along with you for that journey and guiding them to the correct conclusions. So that's another way to get them invested without shying them away from the SEO process.

Risks

So now that we've talked about those tactics, we're going to move on to the risks. These are things that could happen if you don't get your clients excited and invested in the SEO journey.

1. SEO becomes a checklist

When you don't know your client well enough to know what they're doing in the real world, what they're all about, the risk becomes you have to kind of just do site health stuff, so fiddling with meta tags, maybe you're changing some paragraphs around, maybe you're changing H1s, fixing 404s, things like that, things that are just objectively, "I can make this change, and I know it's good for site health."

But it's not proactive. It's not actually doing any SEO strategies. It's just cleanup work. If you just focus on cleanup work, that's really not an SEO strategy. That's just making sure your site isn't broken. As we all know, you need so much more than that to make sure that your client's site is ranking. So that's a risk.

If you don't know your clients, if they're not talking to you, or they're not excited about SEO, then really all you're left to do is fiddle with kind of technical stuff. As good as that can be to do, our jobs are way more fun than that. So communicate with your clients. Get them on board so that you can do proactive stuff and not just fiddling with little stuff.

2. SEO conflicts with business goals

So another risk is that SEO can conflict with business goals.

So say that you're an SEO. Your client is not talking to you. They're not really excited about stuff that you're doing. But you decide to move forward with proactive strategies anyway. So say I'm an SEO, and I identify this keyword. My client has this keyword. This is a related keyword. It can bring in a lot of good traffic. I've identified this good opportunity. All of the pages that are ranking on page one, they're not even that good. I could totally do better. So I'm going to proactively go, I'm going to build this page of content and put it on my client's site. Then what happens when they see that page of content and they go, "We don't even do that. We don't offer that product. We don't offer that service."

Oops. So that's really bad. What can happen is that, yes, you're being proactive, and that's great. But if you don't actually know what your client is doing, because they're not communicating with you, they're not really excited, you risk misaligning with their business goals and misrepresenting them. So that's a definite risk.

3. You miss out on PR opportunities

Another thing, you miss out on PR opportunities. So again, if your client is not talking to you, they're not excited enough to share what they're doing in the real world with you, you miss out on news like, "Hey, we're sponsoring this event,"or, "Hey, I was the featured expert on last night's news."

Those are all really, really good things that SEOs look for. We crave that information. We can totally use that to capitalize on it for SEO value. If we're not getting that from our clients, then we miss out on all those really, really cool PR opportunities. So a definite risk. We want those PR opportunities. We want to be able to use them.

4. Client controls the conversation

Next up, client controls the conversation. That's a definite risk that can happen. So if a client is not talking to you, a reason could be they don't really trust you yet. When they don't trust you, they tend to start to dictate. So maybe our client emails in.

A good example of this is, "Hey, add these 10 backlinks to my website." Or, "Hey, I need these five pages, and I need them now." Maybe they're not even actually bad suggestions. It's just the fact that the client is asking you to do that. So this is kind of tricky, because you want to communicate with your client. It's good that they're emailing in, but they're the ones at that point that are dictating the strategy. Whereas they should be communicating their vision, so hey, as a business owner, as a website owner, "This is my vision. This is my goal, and this is what I want."

As the SEO professional, you're receiving that information and taking it and making it into an SEO strategy that can actually be really, really beneficial for the client. So there's a huge difference between just being a task monkey and kind of transforming their vision into an SEO strategy that can really, really work for them. So that's a definite risk that can happen.

Excitement + partnership = better SEO campaigns

There's a lot of different things that can happen. These are just some examples of tactics that you can use and risks. If you have any examples of things that have worked for you in the past, I would love to hear about them. It's really good to information share. Success stories where maybe you got your client or your boss really bought into SEO, more so than just, "Hey, I'm spending money on it."

But, "Hey, I'm your partner in this. I'm your ally, and I'm going to give you all the information because I know that it's going to be mutually beneficial for us." So at the end here, excitement, partner, better SEO campaigns. This is going to be I believe a recipe for success to get your clients and your boss on board. Thanks again so much for watching this edition of Whiteboard Friday, and come back next week for another one.

Video transcription by Speechpad.com


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!



from The Moz Blog http://tracking.feedpress.it/link/9375/10368598
via IFTTT

Wednesday, September 19, 2018

How to Improve Your Link Building Outreach Pipeline

Posted by John.Michael123

Link building is probably one of the most challenging pieces of your SEO efforts. Add multiple clients to the mix, and managing the link outreach process gets even tricker. When you’re in the thick of several outreach campaigns, it’s hard to know where to focus your efforts and which tactics will bring you the most return on your time and resources.

Three common questions are critical to understand at any point in your link campaign:

  • Do you need more link prospects?
  • Do you need to revise your email templates?
  • Do you need to follow up with prospects?

Without a proven way to analyze these questions, your link building efforts won’t be as efficient as they could be.

We put together a Google Sheets template to help you better manage your link building campaigns. The beauty of this template is that it allows for customization to better fit your workflow. You'll want to make a copy to get started with your own version.

Our link building workflow

We've been able to improve our efficiency via this template by following a simple workflow around acquiring new guest posts on industry-relevant websites. The first step is to actually go out and find prospects that could be potentially interested in a guest blog post. We will then record those opportunities into our template so that we can track our efforts and identify any area that isn’t performing well.

The next step is to make sure to update the status of the prospect when anything changes like sending an outreach email to the prospect or getting a reply from them. It’s critical to keep the spreadsheet as up to date as possible so that we have an accurate picture of our performance.

Once you've used this template for enough time and you've gathered enough data, you'll be able to predict how many link prospects you'll need to find in order to acquire each link based on your own response and conversion rates. This can be useful if you have specific goals around acquiring a certain number of links per month, as you'll get a better feel for how much prospecting you need to do to meet that link target number.

Using the link outreach template

The main purpose of this template is to give you a systematic way to analyze your outreach process so you can drill down into the biggest opportunities for improvement. There are several key features, starting with the Prospects tab.

The Prospects tab is the only one you will need to manually edit, and it houses all the potential link prospects uncovered in your researched. You'll want to fill in the cells for your prospect’s website URL;, and you can also add the Domain Authority of the website for outreach prioritization. For the website URL, I typically put in an example of a guest post that was done on that site or just the homepage if I can’t find a better page.

There’s also a corresponding status column, with the following five stages so you can keep track of where each prospect is in the outreach process.

Status 1: Need to Reach Out. Use this for when you initially find a prospect but have not taken any action yet.

Status 2: Email Sent. This is used as soon as you send your first outreach email.

Status 3: Received Response

Status 4: Topic Approved. Select this status after you get a response and your guest post topic has been approved (this may take a few emails). Whenever I see this status, I know to reach out to my content team so they can start writing.

Status 5: Link Acquired. Selecting this status will automatically add the website to your Won Link Opportunities Report.

The final thing to do here is record the date that a particular link was acquired and add the URL where the link resides. Filling in these columns automatically populates the “Won Link Opportunities” report so you can track all of the links you acquire throughout the lifetime of your campaign.

Link building progress reports

This template automatically creates two reports that I share with my clients on a monthly basis. These reports help us dial in our efforts and maximize the performance of our overall link building campaign.

Link Pipeline report

The Link Pipeline report is a snapshot of our overall link outreach campaign. It shows us how many prospects we have in our pipeline and what the conversion/response rates are of each stage of our outreach funnel.

How to analyze the Link Pipeline report

This report allows us to understand where we need to focus our efforts to maximize our campaign’s performance. If there aren't enough prospects at the top of the funnel, we know that we need to start looking for new link opportunities. If our contact vs. response rate is low, we know we need to test new email copy or email subject lines.

Won Link Opportunities

The Won Link Opportunities report lists out all the websites where a link has been officially landed. This is a great way to keep track of overall progress over time and to gauge performance against your link building goals.

Getting the most out of your link building campaigns

Organization is critical for maximizing your link building efforts and the return on the time you're spending. By knowing exactly which stage of your link building process is your lowest performing, you can dramatically increase your overall efficiency by targeting those areas that need the most improvement.

Make a copy of the template


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!



from The Moz Blog http://tracking.feedpress.it/link/9375/10354355
via IFTTT

Monday, September 17, 2018

Follow the Local SEO Leaders: A Guide to Our Industry’s Best Publications

Posted by MiriamEllis

Change is the only constant in local SEO. As your local brand or local search marketing agency grows, you’ll be onboarding new hires. Whether they’re novices or adepts, they’ll need to keep up with continuous industry developments in order to make agile contributions to team strategy. Particularly if local SEO is new to someone, it saves training time if you can fast-track them on who to follow for the best news and analysis. This guide serves as a blueprint for that very purpose.

And even if you’re an old hand in the local SEM industry, you may find some sources here you’ve been overlooking that could add richness and depth to your ongoing education.

Two quick notes on what and how I’ve chosen:

  1. As the author of both of Moz’s newsletters (the Moz Top 10 and the Moz Local Top 7), I read an inordinate amount of SEO and local SEO content, but I could have missed your work. The list that follows represents my own, personal slate of the resources that have taught me the most. If you publish great local SEO information but you’re not on this list, my apologies, and if you write something truly awesome in future, you’re welcome to tweet at me. I’m always on the lookout for fresh and enlightening voices. My personal criteria for the publications I trust is that they are typically groundbreaking, thoughtful, investigative, and respectful of readers and subjects.
  2. Following the leaders is a useful practice, but not a stopping point. Even experts aren’t infallible. Rather than take industry advice at face value, do your own testing. Some of the most interesting local SEO discussions I’ve ever participated in have stemmed from people questioning standard best practices. So, while it’s smart to absorb the wisdom of experts, it’s even smarter to do your own experiments.

The best of local SEO news

Who reports fastest on Google updates, Knowledge Panel tweaks, and industry business?

Sterling Sky’s Timeline of Local SEO Changes is the industry’s premiere log of developments that impact local businesses and is continuously updated by Joy Hawkins + team.

Search Engine Roundtable has a proven track record of being among the first to report news that affects both local and digital businesses, thanks to the ongoing dedication of Barry Schwartz.

Street Fight is the best place on the web to read about mergers, acquisitions, the release of new technology, and other major happenings on the business side of local. I’m categorizing Street Fight under news, but they also offer good commentary, particularly the joint contributions of David Mihm and Mike Blumenthal.

LocalU’s Last Week in Local video and podcast series highlights Mike Blumenthal and Mary Bowling’s top picks of industry coverage most worthy of your attention. Comes with the bonus of expert commentary as they share their list.

TechCrunch also keeps a finger on the pulse of technology and business dealings that point to the future of local.

Search Engine Land’s local category is consistently swift in getting the word out about breaking industry news, with the help of multiple authors.

Adweek is a good source for reportage on retail and brand news, but there’s a limit to the number of articles you can read without a subscription. I often find them covering quirky stories that are absent from other publications I read.

The SEMPost’s local tab is another good place to check for local developments, chiefly covered by Jennifer Slegg.

Search Engine Journal’s local column also gets my vote for speedy delivery of breaking local stories.

Google’s main blog and the ThinkWithGoogle blog are musts to keep tabs on the search engine’s own developments, bearing in mind, of course, that these publications can be highly promotional of their products and worldview.

The best of local search marketing analysis

Who can you trust most to analyze the present and predict the future?

LocalU’s Deep Dive video series features what I consider to be the our industry’s most consistently insightful analysis of a variety of local marketing topics, discussed by learned faculty and guests.

The Moz Blog’s local category hosts a slate of gifted bloggers and professional editorial standards that result in truly in-depth treatment of local topics, presented with care and attention. As a veteran contributor to this publication, I can attest to how Moz inspires authors to aim high, and one of the nicest things that happened to our team in 2018 was being voted the #2 local SEO blog by BrightLocal’s survey respondents.

The Local Search Association’s Insider blog is one I turn to again and again, particularly for their excellent studies and quotable statistics.

Mike Blumenthal’s blog has earned a place of honor over many years as a key destination for breaking local developments and one-of-a-kind analysis. When Blumenthal talks, local people listen. One of the things I’ve prized for well over a decade in Mike’s writing is his ability to see things from a small business perspective, as opposed to simply standing in awe of big business and technology.

BrightLocal’s surveys and studies are some of the industry’s most cited and I look eagerly forward to their annual publication.

Whitespark’s blog doesn’t publish as frequently as I wish it did, but their posts by Darren Shaw and crew are always on extremely relevant topics and of high quality.

Sterling Sky’s blog is a relative newcomer, but the expertise Joy Hawkins and Colan Nielsen bring to their agency’s publication is making it a go-to resource for advice on some of the toughest aspects of local SEO.

Local Visibility System’s blog continues to please, with the thoughtful voice of Phil Rozek exploring themes you likely encounter in your day-to-day work as a local SEO.

The Local Search Forum is, hands down, the best free forum on the web to take your local mysteries and musings to. Founded by Linda Buquet, the ethos of the platform is approachable, friendly, and often fun, and high-level local SEOs frequently weigh in on hot topics.

Pro tip: In addition to the above tried-and-true resources, I frequently scan the online versions of city newspapers across the country for interesting local stories that add perspective to my vision of the challenges and successes of local businesses. Sometimes, too, publications like The Atlantic, Forbes, or Business Insider will publish pieces of a high journalistic quality with relevance to our industry. Check them out!

The best for specific local marketing disciplines

Here, I’ll break this down by subject or industry for easy scanning:

Reviews

  • GetFiveStars can’t be beat for insight into online reputation management, with Aaron Weiche and team delivering amazing case studies and memorable statistics. I literally have a document of quotes from their work that I refer to on a regular basis in my own writing.
  • Grade.us is my other ORM favorite for bright and lively coverage from authors like Garrett Sussman and Andrew McDermott.

Email marketing

  • Tidings' vault contains a tiny but growing treasure trove of email marketing wisdom from David Mihm, whose former glory days spent in the trenches of local SEO make him especially attuned to our industry.

SABs

  • Tom Waddington’s blog is the must-read publication for service area businesses whose livelihoods are being impacted by Google’s Local Service Ads program in an increasing number of categories and cities.

Automotive marketing

  • DealerOn’s blog is the real deal when it comes to automotive local SEO, with Greg Gifford teaching memorable lessons in an enjoyable way.

Legal marketing

  • JurisDigital brings the the educated voices of Casey Meraz and team to the highly-specialized field of attorney marketing.

Hospitality marketing

Independent businesses

Link building

  • Nifty Marketing’s blog has earned my trust for its nifty local link building ideas and case studies.
  • ZipSprout belongs here, too, because of their focus on local sponsorships, which are a favorite local link building methodology. Check them out for blog posts and podcasts.

Schema + other markup

  • Touchpoint Digital Marketing doesn’t publish much on their own website, but look anywhere you can for David Deering’s writings on markup. LocalU and Moz are good places to search for his expertise.

Patents

  • SEO by the Sea has proffered years to matchless analysis of Google patents that frequently impact local businesses or point to future possible developments.

Best local search industry newsletters

Get the latest news and tips delivered right to your inbox by signing up for these fine free newsletters:

Follow the local SEO leaders on Twitter

What an easy way to track what industry adepts are thinking and sharing, up-to-the-minute! Following this list of professionals (alphabetized by first name) will fill up your social calendar with juicy local tidbits. Keep in mind that many of these folks either own or work for agencies or publishers you can follow, too.

Aaron Weiche
Adam Dorfman
Andrew Shotland
Ben Fisher
Bernadette Coleman
Bill Slawski
Brian Barwig
Carrie Hill
Casey Meraz
Cindy Krum
Colan Nielsen
DJ Baxter
Dan Leibson
Dana DiTomaso
Dani Owens
Darren Shaw
Dave DiGreggorio
David Mihm
Don Campbell
Garrett Sussman
Glenn Gabe
Greg Gifford
Greg Sterling
Jennifer Slegg
Joel Headley
Joy Hawkins
Mary Bowling
Mike Blumenthal
Mike Ramsey
Miriam Ellis
Phil Rozek
Sherry Bonelli
Thibault Adda
Tim Capper
Tom Waddington

Share what you learn

How about your voice? How do you get it heard in the local SEO industry? The answer is simple: share what you learn with others. Each of the people and publications on my list has earned a place there because, at one time or another, they have taught me something they learned from their own work. Some tips:

  • Our industry has become a sizeable niche, but there is always room for new, interesting voices
  • Experiment and publish — consistent publication of your findings is the best way I know of to become a trusted source of information
  • Don’t be afraid of making mistakes, so long as you are willing to own them
  • Socialize — attend events, amplify the work of colleagues you admire, reach out in real ways to others to share your common work interest while also respecting busy schedules

Local SEO is a little bit like jazz, in which we’re all riffing off the same chord progressions created by Google, Facebook, Yelp, other major platforms, and the needs of clients. Mike Blumenthal plays a note about a jeweler whose WOMM is driving the majority of her customers. You take that note and turn it around for someone in the auto industry, yielding an unexpected insight. Someone else takes your insight and creates a print handout to bolster a loyalty program.

Everyone ends up learning in this virtuous, democratic cycle, so go ahead — start sharing! A zest for contribution is a step towards leadership and your observations could be music to the industry’s ears.


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!



from The Moz Blog http://tracking.feedpress.it/link/9375/10332693
via IFTTT

Friday, September 14, 2018

SEO Maturity: Evaluating Client Capabilities - Whiteboard Friday

Posted by HeatherPhysioc

Clients aren't always knowledgeable about SEO. That lack of understanding can result in roadblocks and delay the work you're trying to accomplish, but knowing your client's level of SEO maturity can help. In today's Whiteboard Friday, we welcome the brilliant Heather Physioc to expound upon the maturity models she's developed to help you diagnose your client's search maturity and remove blockers to your success.

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

Video Transcription

What up, Moz fans? My name is Heather Physioc. I'm Director of the Discoverability Group at VML. We are in Kansas City. Global ad agency headquartered right in the middle of the map.

Today we're going to talk about how to diagnose the maturity of your SEO client. I don't mean emotional maturity. I mean maturity as it pertains to SEO capabilities, their ability to do the work, as well as their organizational search program maturity. Now a lot of times when a client signs a contract with us, we make the assumption that they're knowledgeable, they're motivated, they're bought in to do the search work.

So we go dumping all these recommendations in their lap, and we're trucking full speed ahead. But then we're surprised when we start hitting blockers and the work doesn't go live. I actually surveyed over 140 of our colleagues in the search industry, and they reported running into blockers, like low prioritization and buy-in for the work, limited technical resources for developers or budgeting for copywriters, low advocacy, high turnover, and any number of different things that stand in the way.

I didn't just ask about the problems. I asked about the solutions, and one of the tools that came out of that was the ability to diagnose the client's maturity. So a maturity model is intended to evaluate an organization's capability to continuously evolve in a practice. The point, the purpose of this is to understand where they stand today, where they want to go, and the steps it's going to take to get there.


The SEO Capabilities matrix

Let's talk about the SEO capabilities first, the technical ability to do the job.

Harmful

On the low end of the scale, a client may be engaging in spammy, outdated, or harmful SEO practices that are doing more harm than good.

Tactical

From there, they may be tactical. They're doing some super basic SEO, think title tags and meta description tags, but nothing earth-shattering is happening here, and it's not very strategic or aligned to brand goals.

Strategic

From there, the brand moves into the strategic phase. They're starting to align the work to goals. They're starting to become a little more search savvy. They're getting beyond the titles and metas, and they're more thorough with the work. While good stuff is happening here, it's not too advanced, and it still tends to be pretty siloed from the other disciplines.

Practice

From there, the organization might move into a practice. Search is starting to become a way of life here. They're getting significantly more advanced in their work. They're starting to connect the dots between those different channels. They're using data in smarter ways to drive their search strategy.

Culture

Then from there, maybe they're at a level of culture for their SEO capabilities. So search here is starting to become a part of their marketing DNA. They're integrating across practices. They're doing cutting edge. They're testing and innovating and improving their SEO implementation, and they're looking for the next big thing. But these groups know that they have to continually evolve as the industry evolves. So we don't just look at their whole SEO program and figure out where the client goes on the map.

✓ Data-driven

We actually break it down into a few pieces. First, data-driven. Is the organization using information and analytics and combining it with other sources even to make really smart marketing decisions?

✓ SEO for content

Next is content. Are they doing any SEO for content at all? Are they implementing some SEO basics, but only during and after publication? Or are they using search data to actually drive their editorial calendar alongside other data inputs, like social listening or web analytics?

✓ Mobility

From there, mobility. Do they have no mobile experience at all, or do they have a fully responsive and technically mobile friendly site, but they're not investing any more in that mobile optimization? Or are they a completely mobile-first mindset? Are they continuously iterating and improving in usability, speed, and content for their mobile users?

✓ Technical ability

Beyond that, we could look at how technically savvy they are. Do they have a lot of broken stuff, or are they on top of monitoring and maintaining their technical health and accessibility?

✓ On-page/off-page SEO

Then some standard SEO best practices here. Are they limited or advanced in on-page SEO, off-page SEO?

✓ Integrating across channels

Are they integrating across channels and not having search live in a silo?

✓ Adopting new technology

Are they adopting new technology as it pertains to search? Some clients have a very high appetite for this, but they chase after the shiny object.

Others have a high appetite and a high tolerance for risk, and they're making hard choices about which new technology to invest in as it pertains to their search program. You may also want to customize this maturity model and include things like local search or international search or e-commerce. But this is a great place to start. So this does a very good job of choosing which projects to begin with for a client, but it doesn't really get to the heart of why our work isn't getting implemented.


The Organizational Search Maturity matrix

I developed a second maturity model, and this one is more traditional and you see it across other industries as well. But this one focuses on the search program inside the organization. This is the squishy organizational stuff.

✓ People

This is people. Do they have the necessary talent within the organization or within their scope? That might not just mean SEOs. That means are they scoping appropriately for content and development needs?

✓ Process

What about process? Are they actually using a defined and continuously improving process for the inclusion of search? Now I don't mean step-by-step best practices for implementing a title tag. This isn't instructions or a tutorial. This is a process for including organic search experts at the right moments in the right projects.

✓ Planning

What about planning? A lot of times we find that clients are doing search very reactively and after the fact. We want to reach a point with an organization where it's preplanned, it's proactively included, and it's always aligned to brand, business, or campaign goals.

✓ Knowledge

Next is knowledge. We know that this industry is complicated. There are a lot of moving pieces. We want to know how knowledgeable is the organization about search. That doesn't necessarily mean how to do SEO, but perhaps the importance or the impact or the outcomes of it. How committed are they to learning more through reading or trainings or conferences? At the very least, the organization they're hiring to do search needs to be extraordinarily knowledgeable about it.

✓ Capacity

Then capacity. Do they have the prioritization within the organization? Are they budgeting appropriately? Do they have the resources and the means and the capacity to get the work done?

Initial

When we've evaluated a client against these criteria, we could find them in an initial phase where the program is very new, they're not doing any search at all...

Repeatable

...to repeatable, meaning they're starting to include it, but it's not super cohesive yet. They're not enforcing the process. They don't have super dedicated resources just yet.

Defined

Up into defined, where they actually are documenting their process. It's continuing to iterate and improve. They're becoming more knowledgeable. They're dedicating more resources. They're prioritizing it better.

Managed

We can move up into managed, where that's continuing to improve even further...

Optimized

...and into optimized. So again, this is where search programs are part of the organization's DNA. It's always included. They are always improving their process. They are maintaining or even increasing the talent that they have dedicated to the work. They're planning it smarter and better than ever before, and they have adequate capacity to keep iterating and growing in their search program.


With that, the steps to complete this process and figure out where your client falls on either of these maturity models, I want to be clear is not a one-sided exercise. This is not a situation where you're just punching numbers into a spreadsheet and the agency is grading the client and our job is done. This needs to be a conversation.

We need to invite stakeholders at multiple levels, both on the client side and on the agency side, or if you're in-house, just multiple levels within the organization, and we should ask for opinions from multiple perspectives to paint a more accurate picture of where the client stands today and agree on the steps that we need to take to move forward. When you do these maturity assessments, this isn't enough.

This is step one. This isn't a finish line. We need to be using this as a springboard for a dialogue to uncover their pain points or the obstacles that they run into, inside their organization, that are going to keep you from getting that work done. We need to have honest and frank conversations about the things we need to clear out of the way to do our best work. With that, I hope that you can try this out.

We've got a great article that we published on the Moz blog to get into more detail about how to implement this. But try it out in your organization or with your client and let us know. Peer review this and help us make it better, because this is intended to be a living process that evolves as our industry does.

Video transcription by Speechpad.com


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!



from The Moz Blog http://tracking.feedpress.it/link/9375/10310944
via IFTTT

Wednesday, September 12, 2018

Local Business Transparency & Empathy for the Holidays: Tips + Downloadable Checklist

Posted by MiriamEllis

Your local business will invest its all in stocking shelves and menus with the right goods and services in advance of the 2018 holiday season, but does your inventory include the on-and-offline experiences consumers say they want most?

Right now, a potential patron near you is having an experience that will inform their decision of whether to do business with you at year’s end, and their takeaway is largely hinging on two things: your brand’s transparency and empathy.

An excellent SproutSocial survey of 1,000 consumers found that people define transparency as being:

  • Open (59%)
  • Clear (53%)
  • Honest (49%)

Meanwhile, after a trying year of fake news, bad news, and privacy breaches, Americans could certainly use some empathy from brands that respect their rights, needs, aspirations, and time.

Today, let’s explore how your local brand can gift customers with both transparency and empathy before and during the holiday season, and let’s make it easy for your team with a shareable, downloadable checklist, complete with 20 tips for in-store excellence and holiday Google My Business best practices:

Grab the Holiday Checklist now!

For consumers, even the little things mean a lot

Your brother eats at that restaurant because its owner fed 10,000 meals to displaced residents during a wildfire. My sister won’t buy merchandise from that shop because their hiring practices are discriminatory. A friend was so amazed when the big brand CEO responded personally to her complaint that she’s telling all her social followers about it now.

Maybe it’s always been a national pastime for Americans to benefit one another with wisdom gained from their purchasing experiences. I own one of the first cookbooks ever published in this country and ‘tis full of wyse warnings about how to avoid “doctored” meats and grains in the marketplace. Social media has certainly amplified our voices, but it has done something else that truly does feel fresh and new. Consider SproutSocial’s findings that:

  • 86% of Americans say transparency from businesses is more important than ever before.
  • 40% of people who say brand transparency is more important than ever before attribute it to social media.
  • 63% of people say CEOs who have their own social profiles are better representatives for their companies than CEOs who do not.

What were customers’ chances of seeking redress and publicity just 20 years ago if a big brand treated them poorly? Today, they can document with video, write a review, tweet to the multitudes, even get picked up by national news. They can use a search engine to dig up the truth about a company’s past and present practices. And… they can find the social profiles of a growing number of brand representatives and speak to them directly about their experiences, putting the ball in the company’s court to respond for all to see.

In other words, people increasingly assume brands should be directly accessible. That’s new!

Should this increased expectation of interactive transparency terrify businesses?

Absolutely not, if their intentions and policies are open, clear, and honest. It’s a little thing to treat a customer with fairness and regard, but its impacts in the age of social media are not small. In fact, SproutSocial found that transparent practices are golden as far as consumer loyalty is concerned:

  • 85% of people say a business' history of being transparent makes them more likely to give it a second chance after a bad experience.
  • 89% of people say a business can regain their trust if it admits to a mistake and is transparent about the steps it will take to resolve the issue.

I highly recommend reading the entire SproutSocial study, and while it focuses mainly on general brands and general social media, my read of it correlated again and again to the specific scenario of local businesses. Let’s talk about this!

How transparency & empathy relate to local brands

“73.8% of customers were either likely or extremely likely to continue to do business with a merchant once the complaint had been resolved.”
- GetFiveStars

On the local business scene, we’re also witnessing the rising trend of consumers who expect accountability and accessibility, and who speak up when they don’t encounter it. Local businesses need to commit to openness in terms of their business practices, just as digital businesses do, but there are some special nuances at play here, too.

I can’t count the number of negative reviews I’ve read that cited inconvenience caused by local business listings containing wrong addresses and incorrect hours. These reviewers have experienced a sense of ill-usage stemming from a perceived lack of respect for their busy schedules and a lack of brand concern for their well-being. Neglected online local business information leads to neglected-feeling customers who sometimes even believe that a company is hiding the truth from them!

These are avoidable outcomes. As the above quote from a GetFiveStars survey demonstrates, local brands that fully participate in anticipating, hearing, and responding to consumer needs are rewarded with loyalty. Given this, as we begin the countdown to holiday shopping, be sure you’re fostering basic transparency and empathy with simple steps like:

  • Checking your core citations for accurate names, addresses, phone numbers, and other info and making necessary corrections
  • Updating your local business listing hours to reflect extended holiday hours and closures
  • Updating your website and all local landing pages to reflect this information

Next, bolster more advanced transparency by:

  • Using Google Posts to clearly highlight your major sale dates so people don’t feel tricked or left out
  • Answering all consumer questions via Google Questions & Answers in your Google Knowledge Panels
  • Responding swiftly to both positive and negative reviews on core platforms
  • Monitoring and participating on all social discussion of your brand when concerns or complaints arise, letting customers know you are accessible
  • Posting in-store signage directing customers to complaint phone/text hotlines

And, finally, create an empathetic rapport with customers via efforts like:

  • Developing and publishing a consumer-centric service policy both on your website and in signage or print materials in all of your locations
  • Using Google My Business attributes to let patrons know about features like wheelchair accessibility, available parking, pet-friendliness, etc.
  • Publishing your company giving strategies so that customers can feel spending with you supports good things — for example, X% of sales going to a local homeless shelter, children’s hospital, or other worthy cause
  • Creating a true welcome for all patrons, regardless of gender, identity, race, creed, or culture — for example, gender neutral bathrooms, feeding stations for mothers, fragrance-free environments for the chemically sensitive, or even a few comfortable chairs for tired shoppers to rest in

A company commitment to standards like TAGFEE coupled with a basic regard for the rights, well-being, and aspirations of customers year-round can stand a local brand in very good stead at the holidays. Sometimes it’s the intangible goods a brand stocks — like goodwill towards one’s local community — that yield a brand of loyalty nothing else can buy.

Why not organize for it, organize for the mutual benefits of business and society with a detailed, step-by-step checklist you can take to your next team meeting?:

Download the 2018 Holiday Local SEO Checklist


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!



from The Moz Blog http://tracking.feedpress.it/link/9375/10293659
via IFTTT

Tuesday, September 11, 2018

Visualizing Time: A Project Management How-To Using Google Sheets

Posted by R0bin_L0rd

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.

Contents (for if you just want to jump to a specific section):

The 3 principles (which are about people as much as using the sheets)
An early conclusion
Appendices & instructions
How to add tasks to the list
Splitting tasks across multiple time periods
Working with the Month View tab (Planner and Stakeholder Versions)
How to make the Gantt charts work (and add categories)
How to make the Category-Filterable Forward-Facing Gantt Charts work
How to create the Stakeholder View
How to update the God's-I Version


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:

  1. You've really doubled down on that neuroticism we share
  2. 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:

  1. 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).
  2. 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).
  3. 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!

Access the template sheets here:


Appendices & instructions

Some general notes

Quick notes on avoiding problems:

  1. 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.
  2. Try to follow the existing format of words and numbers as closely as possible when creating new information.
  3. 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.
  4. 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:

  1. Go through all the tabs in the stakeholder view and unhide any hidden columns
    1. They usually just contain a formula that reformats text so our lookups work. See if any of those are missing or broken.
  2. Try copying the formulas from the row above or next to the cell that isn’t working.
  3. Try removing the =iferror portion of formulas.
    1. 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.
  4. 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.
    1. Check the Task View tab in the Stakeholder Version and Project URLs tab in the God's-I Version
  5. Have you just called a task “Part 4” or similar? There is a RegEx formula which will strip that out.
  6. 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:

=if(not(isblank(A12)), iferror(TRANSPOSE(FILTER('Task view'!B:B,RegExmatch('Task view'!A:A,B12))),""),"")

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.

=if(isblank(A12),"",((D12-SUM(FILTER('Task view'!I:I,RegExmatch('Task view'!A:A,B12))))-sum($D$6:$F$8)))

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).

=iferror(if(not(or(isblank($D6),ISBLANK(F$1))),vlookup(filter('Task view'!$C:$C,'Task view'!$O:$O=$D5,REGEXMATCH('Task view'!$A:$A,F$2)),'Status and colour code'!$C:$E,3,0),""),"")

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:

  1. Select all the cells in the tab
  2. Select “Conditional Formatting” from the Format menu
  3. Find the rule with the black box next to it and make a note of what number it's currently targeting from
  4. 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
  5. 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:

=FILTER('Gantt view'!A6:B,RegExmatch('Gantt view'!A6:A,".*[a-zA-Z].*"))

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.

=if(countif(C3:C,True)>0,CONCATENATE("(",JOIN("|",FILTER(B3:B,C3:C=True,not(isblank(B3:B)))),")"),"(\d|Deadline)")

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.

=query('Gantt view'!D1:1056,"Select D, E, F, G, H, I, J where D <> '' and (E matches '"&B1&"' or F matches '"&B1&"' or G matches '"&B1&"' or H matches '"&B1&"' or I matches '"&B1&"' or J matches '"&B1&"')",1)

How to create the Stakeholder View

The Planner Version sheet has a tab called Planned. You don’t need to fill out this tab — it has a query which extracts information from the Task View tab using a =query() function:

=QUERY({'Task view'!A1:F,'Task view'!O1:P},"Select * where not (Col6 contains 'pitched' or Col6 contains 'cancelled' or Col6 = '' or Col6 = 'Forgotten')")

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:

  1. Have a deadline within 10 days of now
  2. Have passed a deadline (with the task not completed)
  3. Don’t have a deadline set
  4. 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:

  1. Add the URL of the new Planner View sheet you want to include in the Project URLs tab of the God's-I Version
  2. Grant permission for this sheet to access that sheet (you can click on the alert that appears in column A)
  3. 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!



from The Moz Blog http://tracking.feedpress.it/link/9375/10285404
via IFTTT