From Editorial Calendars to SEO: Setting Yourself Up to Create Fabulous Content

Posted by Isla_McKetta

Quick note: This article is meant to apply to teams of all sizes, from the sole proprietor who spends all night writing their copy (because they’re doing business during the day) to the copy team who occupies an entire floor and produces thousands of pieces of content per week. So if you run into a section that you feel requires more resources than you can devote just now, that’s okay. Bookmark it and revisit when you can, or scale the step down to a more appropriate size for your team. We believe all the information here is important, but that does not mean you have to do everything right now.

If you thought ideation was fun, get ready for content creation. Sure, we’ve all written some things before, but the creation phase of content marketing is where you get to watch that beloved idea start to take shape.

Before you start creating, though, you want to get (at least a little) organized, and an editorial calendar is the perfect first step.

Editorial calendars

Creativity and organization are not mutually exclusive. In fact, they can feed each other. A solid schedule gives you and your writers the time and space to be wild and creative. If you’re just starting out, this document may be sparse, but it’s no less important. Starting early with your editorial calendar also saves you from creating content willy-nilly and then finding out months later that no one ever finished that pesky (but crucial) “About” page.

There’s no wrong way to set up your editorial calendar, as long as it’s meeting your needs. Remember that an editorial calendar is a living document, and it will need to change as a hot topic comes up or an author drops out.

There are a lot of different types of documents that pass for editorial calendars. You get to pick the one that’s right for your team. The simplest version is a straight-up calendar with post titles written out on each day. You could even use a wall calendar and a Sharpie.

Monday Tuesday Wednesday Thursday Friday
Title
The Five Colors of Oscar Fashion 12 Fabrics We’re Watching for Fall Is Charmeuse the New Corduroy? Hot Right Now: Matching Your Handbag to Your Hatpin Tea-length and Other Fab Vocab You Need to Know
Author Ellie James Marta Laila Alex

Teams who are balancing content for different brands at agencies or other more complex content environments will want to add categories, author information, content type, social promo, and more to their calendars.

Truly complex editorial calendars are more like hybrid content creation/editorial calendars, where each of the steps to create and publish the content are indicated and someone has planned for how long all of that takes. These can be very helpful if the content you’re responsible for crosses a lot of teams and can take a long time to complete. It doesn’t matter if you’re using Excel or a Google Doc, as long as the people who need the calendar can easily access it. Gantt charts can be excellent for this. Here’s a favorite template for creating a Gantt chart in Google Docs (and they only get more sophisticated).

Complex calendars can encompass everything from ideation through writing, legal review, and publishing. You might even add content localization if your empire spans more than one continent to make sure you have the currency, date formatting, and even slang right.

Content governance

Governance outlines who is taking responsibility for your content. Who evaluates your content performance? What about freshness? Who decides to update (or kill) an older post? Who designs and optimizes workflows for your team or chooses and manages your CMS?

All these individual concerns fall into two overarching components to governance: daily maintenance and overall strategy. In the long run it helps if one person has oversight of the whole process, but the smaller steps can easily be split among many team members. Read this to take your governance to the next level.

Finding authors

The scale of your writing enterprise doesn’t have to be limited to the number of authors you have on your team. It’s also important to consider the possibility of working with freelancers and guest authors. Here’s a look at the pros and cons of outsourced versus in-house talent.

In-house authors

Guest authors and freelancers

Responsible to

You

Themselves

Paid by

You (as part of their salary)

You (on a per-piece basis)

Subject matter expertise

Broad but shallow

Deep but narrow

Capacity for extra work

As you wish

Show me the Benjamins

Turnaround time

On a dime

Varies

Communication investment

Less

More

Devoted audience

Smaller

Potentially huge

From that table, it might look like in-house authors have a lot more advantages. That’s somewhat true, but do not underestimate the value of occasionally working with a true industry expert who has name recognition and a huge following. Whichever route you take (and there are plenty of hybrid options), it’s always okay to ask that the writers you are working with be professional about communication, payment, and deadlines. In some industries, guest writers will write for links. Consider yourself lucky if that’s true. Remember, though, that the final paycheck can be great leverage for getting a writer to do exactly what you need them to (such as making their deadlines).

Tools to help with content creation

So those are some things you need to have in place before you create content. Now’s the fun part: getting started. One of the beautiful things about the Internet is that new and exciting tools crop up every day to help make our jobs easier and more efficient. Here are a few of our favorites.

Calendars

You can always use Excel or a Google Doc to set up your editorial calendar, but we really like Trello for the ability to gather a lot of information in one card and then drag and drop it into place. Once there are actual dates attached to your content, you might be happier with something like a Google Calendar.

Ideation and research

If you need a quick fix for ideation, turn your keywords into wacky ideas with Portent’s Title Maker. You probably won’t want to write to the exact title you’re given (although “True Facts about Justin Bieber’s Love of Pickles” does sound pretty fascinating…), but it’s a good way to get loose and look at your topic from a new angle.

Once you’ve got that idea solidified, find out what your audience thinks about it by gathering information with Survey Monkey or your favorite survey tool. Or, use Storify to listen to what people are saying about your topic across a wide variety of platforms. You can also use Storify to save those references and turn them into a piece of content or an illustration for one. Don’t forget that a simple social ask can also do wonders.

Format

Content doesn’t have to be all about the words. Screencasts, Google+ Hangouts, and presentations are all interesting ways to approach content. Remember that not everyone’s a reader. Some of your audience will be more interested in visual or interactive content. Make something for everyone.

Illustration

Don’t forget to make your content pretty. It’s not that hard to find free stock images online (just make sure you aren’t violating someone’s copyright). We like Morgue File, Free Images, and Flickr’s Creative Commons. If you aren’t into stock images and don’t have access to in-house graphic design, it’s still relatively easy to add images to your content. Pull a screenshot with Skitch or dress up an existing image with Pixlr. You can also use something like Canva to create custom graphics.

Don’t stop with static graphics, though. There are so many tools out there to help you create gifs, quizzes and polls, maps, and even interactive timelines. Dream it, then search for it. Chances are whatever you’re thinking of is doable.

Quality, not quantity

Mediocre content will hurt your cause

Less is more. That’s not an excuse to pare your blog down to one post per month (check out our publishing cadence experiment), but it is an important reminder that if you’re writing “How to Properly Install a Toilet Seat” two days after publishing “Toilet Seat Installation for Dummies,” you might want to rethink your strategy.

The thing is, and I’m going to use another cliché here to drive home the point, you never get a second chance to make a first impression. Potential customers are roving the Internet right now looking for exactly what you’re selling. And if what they find is an only somewhat informative article stuffed with keywords and awful spelling and grammar mistakes… well, you don’t want that. Oh, and search engines think it’s spammy too…

A word about copyright

We’re not copyright lawyers, so we can’t give you the ins and outs on all the technicalities. What we can tell you (and you already know this) is that it’s not okay to steal someone else’s work. You wouldn’t want them to do it to you. This includes images. So whenever you can, make your own images or find images that you can either purchase the rights to (stock imagery) or license under Creative Commons.

It’s usually okay to quote short portions of text, as long as you attribute the original source (and a link is nice). In general, titles and ideas can’t be copyrighted (though they might be trademarked or patented). When in doubt, asking for permission is smart.

That said, part of the fun of the Internet is the remixing culture which includes using things like memes and gifs. Just know that if you go that route, there is a certain amount of risk involved.

Editing

Your content needs to go through at least one editing cycle by someone other than the original author. There are two types of editing, developmental (which looks at the underlying structure of a piece that happens earlier in the writing cycle) and copy editing (which makes sure all the words are there and spelled right in the final draft).

If you have a very small team or are in a rush (and are working with writers that have some skill), you can often skip the developmental editing phase. But know that an investment in that close read of an early draft is often beneficial to the piece and to the writer’s overall growth.

Many content teams peer-edit work, which can be great. Other organizations prefer to run their work by a dedicated editor. There’s no wrong answer, as long as the work gets edited.

Ensuring proper basic SEO

The good news is that search engines are doing their best to get closer and closer to understanding and processing natural language. So good writing (including the natural use of synonyms rather than repeating those keywords over and over and…) will take you a long way towards SEO mastery.

For that reason (and because it’s easy to get trapped in keyword thinking and veer into keyword stuffing), it’s often nice to think of your SEO check as a further edit of the post rather than something you should think about as you’re writing.

But there are still a few things you can do to help cover those SEO bets. Once you have that draft, do a pass for SEO to make sure you’ve covered the following:

  • Use your keyword in your title
  • Use your keyword (or long-tail keyword phrase) in an H2
  • Make sure the keyword appears at least once (though not more than four times, especially if it’s a phrase) in the body of the post
  • Use image alt text (including the keyword when appropriate)

Finding time to write when you don’t have any

Writing (assuming you’re the one doing the writing) can require a lot of energy—especially if you want to do it well. The best way to find time to write is to break each project down into little tasks. For example, writing a blog post actually breaks down into these steps (though not always in this order):

  • Research
  • Outline
  • Fill in outline
  • Rewrite and finish post
  • Write headline
  • SEO check
  • Final edit
  • Select hero image (optional)

So if you only have random chunks of time, set aside 15-30 minutes one day (when your research is complete) to write a really great outline. Then find an hour the next to fill that outline in. After an additional hour the following day, (unless you’re dealing with a research-heavy post) you should have a solid draft by the end of day three.

The magic of working this way is that you engage your brain and then give it time to work in the background while you accomplish other tasks. Hemingway used to stop mid-sentence at the end of his writing days for the same reason.

Once you have that draft nailed, the rest of the steps are relatively easy (even the headline, which often takes longer to write than any other sentence, is easier after you’ve immersed yourself in the post over a few days).

Working with design/development

Every designer and developer is a little different, so we can’t give you any blanket cure-alls for inter-departmental workarounds (aka “smashing silos”). But here are some suggestions to help you convey your vision while capitalizing on the expertise of your coworkers to make your content truly excellent.

Ask for feedback

From the initial brainstorm to general questions about how to work together, asking your team members what they think and prefer can go a long way. Communicate all the details you have (especially the unspoken expectations) and then listen.

If your designer tells you up front that your color scheme is years out of date, you’re saving time. And if your developer tells you that the interactive version of that timeline will require four times the resources, you have the info you need to fight for more budget (or reassess the project).

Check in

Things change in the design and development process. If you have interim check-ins already set up with everyone who’s working on the project, you’ll avoid the potential for nasty surprises at the end. Like finding out that no one has experience working with that hot new coding language you just read about and they’re trying to do a workaround that isn’t working.

Proofread

Your job isn’t done when you hand over the copy to your designer or developer. Not only might they need help rewriting some of your text so that it fits in certain areas, they will also need you to proofread the final version. Accidents happen in the copy-and-paste process and there’s nothing sadder than a really beautiful (and expensive) piece of content that wraps up with a typo:

Know when to fight for an idea

Conflict isn’t fun, but sometimes it’s necessary. The more people involved in your content, the more watered down the original idea can get and the more roadblocks and conflicting ideas you’ll run into. Some of that is very useful. But sometimes you’ll get pulled off track. Always remember who owns the final product (this may not be you) and be ready to stand up for the idea if it’s starting to get off track.

We’re confident this list will set you on the right path to creating some really awesome content, but is there more you’d like to know? Ask us your questions in the comments.

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!

Reblogged 4 years ago from tracking.feedpress.it

5 Spreadsheet Tips for Manual Link Audits

Posted by MarieHaynes

Link auditing is the part of my job that I love the most. I have audited a LOT of links over the last few years. While there are some programs out there that can be quite helpful to the avid link auditor, I still prefer to create a spreadsheet of my links in Excel and then to audit those links one-by-one from within Google Spreadsheets. Over the years I have learned a few tricks and formulas that have helped me in this process. In this article, I will share several of these with you.

Please know that while I am quite comfortable being labelled a link auditing expert, I am not an Excel wizard. I am betting that some of the things that I am doing could be improved upon if you’re an advanced user. As such, if you have any suggestions or tips of your own I’d love to hear them in the comments section!

1. Extract the domain or subdomain from a URL

OK. You’ve downloaded links from as many sources as possible and now you want to manually visit and evaluate one link from every domain. But, holy moly, some of these domains can have THOUSANDS of links pointing to the site. So, let’s break these down so that you are just seeing one link from each domain. The first step is to extract the domain or subdomain from each url.

I am going to show you examples from a Google spreadsheet as I find that these display nicer for demonstration purposes. However, if you’ve got a fairly large site, you’ll find that the spreadsheets are easier to create in Excel. If you’re confused about any of these steps, check out the animated gif at the end of each step to see the process in action.

Here is how you extract a domain or subdomain from a url:

  • Create a new column to the left of your url column.
  • Use this formula:

    =LEFT(B1,FIND(“/”,B1,9)-1)

    What this will do is remove everything after the trailing slash following the domain name. http://www.example.com/article.html will now become http://www.example.com and http://www.subdomain.example.com/article.html will now become http://www.subdomain.example.com.

  • Copy our new column A and paste it right back where it was using the “paste as values” function. If you don’t do this, you won’t be able to use the Find and Replace feature.
  • Use Find and Replace to replace each of the following with a blank (i.e. nothing):
    http://
    https://
    www.

And BOOM! We are left with a column that contains just domain names and subdomain names. This animated gif shows each of the steps we just outlined:

2. Just show one link from each domain

The next step is to filter this list so that we are just seeing one link from each domain. If you are manually reviewing links, there’s usually no point in reviewing every single link from every domain. I will throw in a word of caution here though. Sometimes a domain can have both a good link and a bad link pointing to you. Or in some cases, you may find that links from one page are followed and from another page on the same site they are nofollowed. You can miss some of these by just looking at one link from each domain. Personally, I have some checks built in to my process where I use Scrapebox and some internal tools that I have created to make sure that I’m not missing the odd link by just looking at one link from each domain. For most link audits, however, you are not going to miss very much by assessing one link from each domain.

Here’s how we do it:

  • Highlight our domains column and sort the column in alphabetical order.
  • Create a column to the left of our domains, so that the domains are in column B.
  • Use this formula:

    =IF(B1=B2,”duplicate”,”unique”)

  • Copy that formula down the column.
  • Use the filter function so that you are just seeing the duplicates.
  • Delete those rows. Note: If you have tens of thousands of rows to delete, the spreadsheet may crash. A workaround here is to use “Clear Rows” instead of “Delete Rows” and then sort your domains column from A-Z once you are finished.

We’ve now got a list of one link from every domain linking to us.

Here’s the gif that shows each of these steps:

You may wonder why I didn’t use Excel’s dedupe function to simply deduplicate these entries. I have found that it doesn’t take much deduplication to crash Excel, which is why I do this step manually.

3. Finding patterns FTW!

Sometimes when you are auditing links, you’ll find that unnatural links have patterns. I LOVE when I see these, because sometimes I can quickly go through hundreds of links without having to check each one manually. Here is an example. Let’s say that your website has a bunch of spammy directory links. As you’re auditing you notice patterns such as one of these:

  • All of these directory links come from a url that contains …/computers/internet/item40682/
  • A whole bunch of spammy links that all come from a particular free subdomain like blogspot, wordpress, weebly, etc.
  • A lot of links that all contain a particular keyword for anchor text (this is assuming you’ve included anchor text in your spreadsheet when making it.)

You can quickly find all of these links and mark them as “disavow” or “keep” by doing the following:

  • Create a new column. In my example, I am going to create a new column in Column C and look for patterns in urls that are in Column B.
  • Use this formula:

    =FIND(“/item40682”,B1)
    (You would replace “item40682” with the phrase that you are looking for.)

  • Copy this formula down the column.
  • Filter your new column so that you are seeing any rows that have a number in this column. If the phrase doesn’t exist in that url, you’ll see “N/A”, and we can ignore those.
  • Now you can mark these all as disavow

4. Check your disavow file

This next tip is one that you can use to check your disavow file across your list of domains that you want to audit. The goal here is to see which links you have disavowed so that you don’t waste time reassessing them. This particular tip only works for checking links that you have disavowed on the domain level.

The first thing you’ll want to do is download your current disavow file from Google. For some strange reason, Google gives you the disavow file in CSV format. I have never understood this because they want you to upload the file in .txt. Still, I guess this is what works best for Google. All of your entries will be in column A of the CSV:

What we are going to do now is add these to a new sheet on our current spreadsheet and use a VLOOKUP function to mark which of our domains we have disavowed.

Here are the steps:

  • Create a new sheet on your current spreadsheet workbook.
  • Copy and paste column A from your disavow spreadsheet onto this new sheet. Or, alternatively, use the import function to import the entire CSV onto this sheet.
  • In B1, write “previously disavowed” and copy this down the entire column.
  • Remove the “domain:” from each of the entries by doing a Find and Replace to replace domain: with a blank.
  • Now go back to your link audit spreadsheet. If your domains are in column A and if you had, say, 1500 domains in your disavow file, your formula would look like this:

    =VLOOKUP(A1,Sheet2!$A$1:$B$1500,2,FALSE)

When you copy this formula down the spreadsheet, it will check each of your domains, and if it finds the domain in Sheet 2, it will write “previously disavowed” on our link audit spreadsheet.

Here is a gif that shows the process:

5. Make monthly or quarterly disavow work easier

That same formula described above is a great one to use if you are doing regular repeated link audits. In this case, your second sheet on your spreadsheet would contain domains that you have previously audited, and column B of this spreadsheet would say, “previously audited” rather than “previously disavowed“.

Your tips?

These are just a few of the formulas that you can use to help make link auditing work easier. But there are lots of other things you can do with Excel or Google Sheets to help speed up the process as well. If you have some tips to add, leave a comment below. Also, if you need clarification on any of these tips, I’m happy to answer questions in the comments section.

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!

Reblogged 4 years ago from tracking.feedpress.it

Demystifying Data Visualization for Marketers

Posted by Annie Cushing

I presented on wrangling and demystifying the data visualization process for marketers at MozCon this year, and it turns out there was far more to talk about than could fit into that half-hour. For the sake of those who couldn’t make it and those who could but want to learn more, I pulled together this overview of my presentation, offering more detail than I could in the slides.

To see all of the links shared in this post, check out my
MozCon Bitly bundle.

You may want to open the SlideShare file in another tab or browser window, so you can easily toggle between the post and the SlideShare.

I’m going to go through the presentation slide by slide to bring the narrative to print.

Slide 3

I have a confession: Although it’s probably safe to say I’m a fairly advanced Excel user — at least among marketers — until recently I had no real charting strategy. In fact, I signed up to do this presentation partly to force me to carve out a strategy, particularly with Google Analytics data.

Slide 4

In this presentation I have focused on Google Analytics data for a couple reasons:

  1. If you can wrangle Google Analytics, other marketing data is a walk in the park.
  2. It has naming conventions that map beautifully to Excel, making it an ideal tutor.

Slide 5

My approach may seem a bit Karate Kid-esque, but if you can grasp the interplay between Google Analytics and Excel, you’ll never be left wondering how to visualize your data.

Although there are many aspects to data visualization, I focus primarily on charting.

Slide 6

In Excel there are two components to charts that are critical to understand: data series and categories. They are always used together.

Think of categories as buckets for your data and data series as the data itself.

Slide 7

If you dumped a pile of Legos in front of a group of kids and told them to organize them by color into their corresponding, labeled containers and then count them, the containers would be categories. And the data series would be the count of Lego bricks.

Slide 8

First let’s peek under the hood on a PC by cracking open the Select Data Source dialog. You get to it by right-clicking on your chart and choosing Select Data.

Slide 9

Excel for Mac also has data series on the left and categories on the right. And that’s about all they have in common.

Slide 10

But, as with most features in Excel for Mac, the functionality of the Mac’s Data Source dialog is far inferior to that of the PC.

Slide 11

This sort option is helpful if you have a stacked chart and want to sort the individual data series. I like to put the larger series on the bottom and smaller ones on the top. But if you have a stacked chart on the Mac and you want to reorder the data series, you actually have to delete the series you want demoted and manually add it back in.

It’s kind of like that game, Hand on Hand, you might have played as a kid where kids go around in a circle putting their right hands in the middle, followed by the left hands. Then they go around the circle moving the bottom hands to the top of the pile as fast as possible.

Although in this case, you’re moving the data series to the
bottom of the pile.

Slide 12

To move the Sessions data series to the bottom of the pile, first select it from the Series list.

Slide 13

Then click the Remove button to delete it from the list.

Slide 14

Then click the Add button to add it back to the list of data series.

Slide 15

Click the data selector button to the right of the Name field and select the series name, as directed in the screenshot.

Slide 16

Click the data selector button to the right of the Y values field and click-and-drag over the values. If the column is long, just click the first cell and press Ctrl-Shift-Down Arrow (Mac: Command-Shift-Down Arrow) to select the entire column without scrolling. (We are nothing if not efficient.)

Slide 17

And finally you need to click-and-drag over the category axis labels. Which brings us to the Mac’s other issue ….

Slide 18

In the PC version, there’s one place for the category axis labels. On the Mac you have to choose the axis labels for each series. It’s counter-intuitive.

Slide 19

Categories end up along the horizontal axis — or the vertical axis for horizontal bar charts.

Slide 20

The data series ends up in the legend and is usually a metric (from GA). But there are a couple exceptions, which we’ll get to in a minute. The categories populate to the horizontal axis label or vertical axis label with the bar chart.

Slide 21

Transition to Google Analytics.

Slide 22

The two major players in Google Analytics – that we’ll be mapping to Excel – are dimensions and metrics. They’re (practically) inseparable.

Slide 23

Dimensions are the buckets your data is broken up into. These come into Excel as text – even if they’re values – like you get with the Days to Transaction dimension (which you can get from Conversions > Ecommerce > Time to Purchase). They are always the far-left column of the table.

  • Add a secondary dimension in any report (standard or custom).

  • Create a custom flat table with two dimensions. Learn how in this post.
  • Use the API. This is the only option that will allow you to use more than two dimensions. You can pull up to seven dimensions in one API call.

Slide 24

Metrics are anything that can be measured with a number.

Slide 25

If you’re in a custom report (or have clicked the Edit link at the top of most standard reports), metrics always show up to a party in blue.

Slide 26

And dimensions show up as green.

You can learn more about custom reports from the
video tutorial I created to help marketers.

Now it’s time to marry Google Analytics and Excel.

Slide 27

In most cases dimensions in Google Analytics map to categories in Excel.

Slide 28

And metrics map to data series in Excel.

Slide 29

I’m going to break this down systematically, based on the number of dimensions and metrics you’re wanting to visualize.

Slide 30

Dimensions: 0

Metrics: Multiple

You want this if you want to know aggregate numbers, e.g, sessions for the month, or revenue, or goal completions.

Slide 31

I hate to start on a downer, but you need the API to do this. The GA interface requires at least one dimension.

Slide 32

As with most things, if you prod enough, you’ll discover hacks and workarounds. But the name of the game here is to come up with a dimension that will only have one bucket. Going back to the Legos analogy, it would be kind of like saying, “Put all the plastic Legos in this bucket and count them.”

Slide 33

Workaround: Set dimension to something that will encompass all of your data, meaning you’ll only have one row in the report. One example of that would be the User Defined dimension (under Audience > Custom > User Defined).

As you’ll see in the screenshot, all of the values are consolidated as (not set) since this profile (now called view) doesn’t use the User Defined dimension.

Slide 34

If you’re still using the User Defined dimension (and, therefore, have multiple rows reporting), you really need to update.

If you’re using classic GA, you should be using custom variables and custom dimensions if you’re using Universal.

Slide 35

Another option is to use the Year dimension with a custom report. This is ideal if you are gathering data for a single month. You can aggregate data beyond one month, as long as the date range you choose doesn’t straddle more than one year.

Slide 36

Here’s what the custom report looks like under the hood. Learn how to 
create custom reports in Google Analytics in a video tutorial I did.

Slide 37

You can access this report 
here while logged in to Google Analytics.

Slide 38

This data isn’t conducive to charting, but you can sexy up a table with
sparklines and conditional formatting.

Slide 40

Dimensions: 1

Metrics: 1

An example of this might be revenue segmented by country or bounce rate segmented by device category.

Slide 41

Pie Chart Basics

Here are some highlights about the pie chart:

  • They use angles to show the relative size of each value.
  • You should put data in descending order to put the most significant data point at 12:00 and radiate clockwise.
  • Avoid 3D pie charts. They distort data.
  • Data points must add up to 100%. So you can’t take traffic from 5 of your 8 campaigns and chart them.
  • Microsoft says no more than seven categories; I say no more than five.
  • None of the values in your data series can be negative.
  • Learn more

Pie Chart Tricks

Ways to trick out your chart:

  • You can grab a piece of the pie to isolate it and drag it out slightly to draw attention to it. This is called exploding pie pieces.
  • You can also change the values to percentages in the data labels or even add the categories, thereby negating the need for a legend.

Slide 42

Donut Chart Basics

Here are some highlights about the donut chart:

  • Donut charts show data in rings, where each ring represents a data series
  • It uses the length of the arc to indicate the size of the value.
  • You should put data in descending order to put the most significant data point at 12:00 and radiate clockwise.
  • Data points must add up to 100%. So you can’t take traffic from 5 of your 8 campaigns and chart them.
  • Microsoft says no more than seven categories; I say no more than five.
  • None of the values in your data series can be negative.
  • Learn more

Donut Chart Tricks

Ways to trick out your chart:

  • You can put the title or the value you want to highlight in the center. 

  • I don’t recommend using the donut chart for multiple series or dimensions. They’re more difficult to interpret. 

  • Like the pie chart, you can pull one out to draw attention to it.
  • You can use a donut chart to create a speedometer chart.
  • You can fill it with an image that resembles the surface of a donut to make it look like a … Okay, yeah, never mind …

Slide 43

Column Chart Basics

  • Should sort in descending order.
  • The axis should start at 0.
  • Categories don’t have to add up to 100%
  • Learn more

Column Chart Tricks

  • You can add a trendline to make trends stand out.
  • Consider going totally minimalist with the techniques I demonstrate in this video tutorial. (You can skip to the 15:53 mark.)
  • Don’t be afraid to move the legend around.
  • Excel’s default axis tends to be dense. I typically double the Major Unit, so if the major unit is set to 100, I typically up it to 200. Learn more about the major unit from the Microsoft site. (But I also show how in the above-mentioned video tutorial.
  • You can use a column chart to create a bullet graph to show current data vis-à-vis goals or projections.
  • You can use a column chart to create a waterfall chart.
  • You can add a target line to your chart.
  • If you have many categories to chart, you can use a scrollbar.
  • You can use a column chart to create a thermometer chart.
  • Just remember safety first when working with column charts.

Slide 44

Bar Chart Basics

  • You need to sort your data in ascending order to put the longest bars at the top.
  • Bar charts are good for categories with longer labels.
  • You shouldn’t use bar charts if your dimension is time based (date, month, etc.).
  • Learn more

Bar Chart Tricks

  • You can use all of the tricks (except the last two) listed in the Column Chart Tricks list.

Slide 45

Radar Chart Basics

  • Category labels are at the tip of each spine.
  • You can use a fill with your radar charts.

Radar Chart Tricks

  • Radar charts can be compelling when you compare multiple entities at once. For example, I saw a set of 50 radar charts that compared metrics like crime rates for different types of crime for each state.
  • If you don’t want the axis labels to show, you can set the number formatting to ;;; to hide them altogether. You can then include an annotation on your chart that lets viewers know the intervals. 

Slide 46

Notes about the Heat Map

Learn how to create a heat map in
this video tutorial I did.

Slide 47

And now let’s look under the hood at a typical chart that uses 1 dimension and 1 metric. Let’s say we have this table of analytics data ….

Slide 48

If we create a column chart from this table, this is what it’s going to look like (with some cleanup).

Slide 49

Now if we look at the data source this is what we’ll see ….

Slide 50

The mediums show up over here in the categories …

Slide 51

And the sessions values show up here in the data series …

Slide 52

Which populates to the legend. But you can delete the legend when you only have one metric (or data series). You’ll then want to include the metric in the chart title.

Slide 53

And the mediums populate the horizontal axis labels.

A little piece of Excel trivia: The Select Data Source dialog still says Horizontal Axis Labels, even for bar charts where the labels are on the vertical axis. #pedantic

Slide 54

Example of 1 dimension and multiple metrics: Sessions, goal completions, and revenue broken down by Device Category (mobile, tablet, desktop)

BTW, the Device Category dimension is one of the most important in Google Analytics. By itself it’s pretty useless, but in the context of other data, it’s very useful. You should be segmenting all of your data by it.

Slide 55

Notes about the Clustered Column Chart

  • Clustered column charts are good for showing comparisons (e.g, sessions vs revenue for each month or ROI vs Margin by campaign (or keyword).
  • You could transform the clustered column chart into a combination chart by adding a line chart on the secondary axis that adds a percent value.

Slide 56

Notes about the Stacked Column Chart

  • The stacked column chart is good for showing how each data series contributes to the whole.
  • An example might be revenue broken down by medium.
  • If you want to order the columns by overall height, you can create a total column for the series. You just won’t chart that column.

Slide 57

Notes about the Clustered Bar Chart

  • All of the notes in the above-mentioned stacked column chart.
  • Like the [single] bar chart, the clustered bar chart is better for categories with long labels.
  • You can hack the clustered bar chart to create a double-sided bar chart. You can view a video tutorial I did on how to do this.

Slide 58

Notes about the Stacked Bar Chart

  • If you want to sort the bars so that the longer bars are on top, create a totals column and sort it in ascending order.
  • You shouldn’t use the stacked bar chart if your dimension is time oriented (date, month, etc.).

Slide 59

Notes about the 100% Stacked Column Chart

  • Use the 100% stacked column chart when you are working with percentages.
  • The data series must add up to 100%.
  • For example, if you wanted to see what percentage of social referrals came from desktop, tablet, and mobile devices.

Slide 60

Notes about the 100% Stacked Bar Chart

All of the notes under the 100% stacked column chart apply here.

Slide 61

Notes about the Radar Chart

  • Category labels are at the tip of each spine.
  • You can use a fill with your radar charts.
  • Radar charts can be compelling when you compare multiple entities at once. For example, I saw a set of 50 radar charts that compared metrics like crime rates for different types of crime for each state.
  • If you don’t want the axis labels to show, you can set the number formatting to ;;; to hide them altogether. You can then include an annotation on your chart that lets viewers know the intervals. See the screenshot under the Slide 45 note above.

Slide 62

Notes about the Combination Chart

Learn all about combination charts in
this post I wrote on the Search Engine Land site.

Slide 63 – 69

Self-explanatory as they follow the same dialog as slides 46 – 52.

Slide 71

Notes about the Line Chart

  • In a line chart, category data is usually distributed evenly along the horizontal axis and value data is distributed evenly along the vertical axis.
  • Line charts can show continuous data over time, so they’re ideal for showing trends in data at equal intervals, like months, quarters, or fiscal years.
  • You can add markers and set the lines to none to use them in ranking charts.
  • Avoid using stacked line charts. It’s not always apparent that the data series are stacked. If you want to stack, use an area chart instead.
  • You can add interesting line markers like the ones I created in this video tutorial to replicate the charts in Moz’s tool set

Slide 72

Notes about the Stacked Area Chart

  • Ideal for showing stacked data series over time, especially if you want to demonstrate a fluid trend. Stacked column charts should be used if you want to keep each of the categories more disparate.
  • You should order the data series so that the larger series are at the bottom of the stack with the smaller series being clustered together at the top because people’s eyes naturally travel from the horizontal axis upward with stacked area charts.
  • If you keep the gridlines, make them significantly lighter. A light gray works well.
  • Make sure you have adequate contrast between contiguous data series. Sometimes Excel puts two colors next to each other that blend.
  • If you have smaller data series that are difficult to see, use stronger colors to make them easier to view.
  • If you have all larger data series and you want to add some finesse, give your data series a line (what would be called a stroke in graphic design programs) that’s slightly darker than the fill.
  • You can create a combination chart with a stacked area chart. Just don’t use a line chart for the other style. I like to use a chart style that stands out from the area chart, such as a column chart. You may want to increase the transparency of its fill so that you can easily see through to the stacked area chart.

Slide 73

Notes about the Clustered Column Chart

  • You use the clustered column chart to show comparisons between data series (as opposed to how they contribute to the whole).
  • The clustered column chart is especially effective for showing year-over-year data. The categories would just have the name of the month (I abbreviate to three letters, which you can learn how to do in this tutorial), and one column would be used to show data from one year and the other colored column would indicate the previous year. To show the month from each year as a disparate data series, you would have to make each year a separate column in your data.
  • You can add a line chart on the secondary axis that highlights the percent change between values.
  • You can play with the gap width and overlap settings to adjust the series. You get to those by selecting a column, pressing Ctrl-1 (Mac: Command-1), and navigating to the Series Options (Mac: Options) area of the Format Data Series dialog.
  • Excel doesn’t provide the option to add a data label that indicates the total of all the data series for each column. You can hack one by adding a total column that you include in the clustered column but then change to a line chart. From there, remove the line and add data labels above the line.

Slide 74

Same as Slide 60.

Slide 75

Same as Slide 58

Slide 76 – 77

Self-explanatory.

Slide 78

Things get more complicated when you want to chart two dimensions. There are three ways to get 2 dimensions:

Slide 79

So here we have two dimensions (Device Category and User Type). I picked these dimensions to demonstrate because they have a finite number of options. I LOVE the device category dimension and use it frequently to segment my data in Google Analytics.

Note: When you chart two dimensions, you can only use one metric (or data series in Excel).

Slide 80

Here’s an example of what a clustered column chart might look like.

Slide 81

We now have a dimension in the legend — or category in Excel.

Slide 82

Using the Switch Row/Column button ….

Slide 83

This is what the chart would now look like. Notice we now have three data series and two categories.

Slide 84

Now let’s take a peek under the hood.

Slide 85

Again, here you see we have dimensions, not metrics, in the data series. The metrics should be included in the chart title.

Slide 86

And now the Device Category dimension is in the category area.

Slide 87

Your chart options are the same as when you had one dimension and multiple metrics. These options are not exhaustive.

Slide 88

Slide 89

The data in this table is in report format. If only marketing export data came in this format. (It doesn’t.)

Slide 90

This is how marketing data actually comes out of different marketing tools. It’s called tabular format.

Slide 91

Just as in a database, rows in tabular data are called records.

Slide 92

Columns are called fields.

Slide 93

And the column headings are called field names. But if I were to select two dimension columns and one metric and select a chart, here’s how Excel digests the data …

Slide 94

Gross, I know. I’m a child.

Slide 95

Here’s what it actually looks like. A royal mess.

Slide 96

Excel requires that data be in a report format in order to chart two dimensions. And the one metric (sessions, revenue, impressions, whatever) goes into the green area. There’s only one way to corral an export with two dimensions and one metric into report format …

Slide 97

Pivot tables sound scary and intimidating but not if you think about what pivot means.

Slide 98

When a soldier pivots, s/he very simply goes from standing facing one direction to turning at a 90 degree angle. That’s what a pivot table does. By moving one of your dimensions into the Columns field (Mac: Column Labels field), Excel puts that dimension’s values across the top of your data. 

Once you have your data in report format, and you can chart it. You typically want to put the dimension with fewer values into the columns area.

Learn how to create pivot tables in this comprehensive video tutorial I did.

Slide 99

Although pivot tables come with a lot of junk in the trunk, you can see the pivot table puts the data into report layout, which Excel can then use to chart the data. If you’re on a PC, you can create a pivot chart. If you’re on a Mac, you can create a static chart from the pivot table because Excel for
Mac still doesn’t support pivot charts. Still. Ridic.

Slide 100

Now you’re ready to look at GA data — nay, all marketing data — with a more strategic eye… And spend less time tooling around in Excel trying to figure out how to visualize your data!

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!

Reblogged 5 years ago from feedproxy.google.com