Next up, I’m going to show you how to create a super-cool scrolling chart. These are especially helpful to show traffic trends over a long period of time, like years. They really add panache to your dashboard and save on space. It’s also a great way to show your boss or client(s) the progress they’ve made over a longer period of time.

And, let’s be honest. You will also look like an Excel aficionado — and no one has to know you learned it from me!

As I mention in the video, if you’re using an export from the Google Analytics (GA) interface, it works just as well. I just use the API to create dashboards for clients, which you should do, too. You set it up once and then just have to update it. I show you how using a free Excel plugin (PC-swim only).

Collect Data From Google Analytics

You will first need data to chart out. I will typically use a scrolling chart for overall traffic over time and organic traffic over time — but if I have a dashboard client who’s doing paid search, I’ll use a scrolling chart to show a panoply of PPC metrics as well.

I’m going to use GA data for my example. You will need to know how to create a custom report with a Month of Year dimension. If you haven’t created one of those before, I have a quick video tutorial on how to do that. Or you can use this custom report for overall traffic or this one for organic. You need to be logged in to Google Analytics in the browser you open those links in; otherwise, you’ll get a 404 error. If you’re not reading this post in the same browser you’re logged in to GA with, just copy the link and paste it into that browser.

Caveat: The date format for the Month of Year dimension is improperly formatted as yyyymm (e.g., 201307). Excel has no freaking idea what to do with that, so you’ll have to fix the formatting (as I demonstrate in this post) before you can chart it out. I know… data drama. Pfft.

Charting Prep

Because I work almost exclusively with the GA API, my export will look a little different from exports pulled via the UI. But it doesn’t matter. As long as you have column headings, you can rock this chart. Here’s what my raw data looked like:

If I’m charting historical data, I generally like to show 12 months at a time. Makes sense, right? To do this, we’ll need to create a subset of our data set. The perfect function to do that is the INDEX function.

The format for the INDEX function is as follows:

INDEX( array, row_number, [column_number] )

(The brackets indicate an argument that’s optional.)

Think of the INDEX function as playing the game Battleship or Bingo — the array is the board, and the row and column numbers help identify which value from a data set you want. To create the table, I won’t need the optional column number because I use separate INDEX functions for each column. It’s kind of like playing Bingo with only the B column. You don’t need to yell B each time if that’s the only column you’re calling numbers from. (Okay, fine. The analogy breaks down a bit, but you get the idea.)

Enable Developer Tab

Video Tutorial

From here, go ahead and view the video below to see how to create the scrolling chart. I discovered as I was creating the chart that Excel 2013 still has some bugs in it, which made it an exercise in frustration. So I redid the video using 2010.

Axis Formatting Alert

If you scroll through your chart and see the maximum value (or the number at the tippy top of the vertical axis) changes from one view to another, for the sake of clarity, you’ll want to update the axis so that it sets the highest value for the entire chart as the max.

For example, in the chart I used, the maximum value was 800,000 to start with and then bumped up to 1,200,000 because in Aug 2012, revenue came close to $1,000,000.You can see the difference in the screenshots below:

If you don’t standardize the axis, the increases in traffic, revenue, or whatever you’re charting won’t be as apparent because the scale will keep changing. You don’t want to be accused of being a data tease. (I think.)

Anyway, to change this, just select the axis and press Ctrl-1 (Mac: Command-1) to pull up formatting options. And format as you see in the screenshots below, replacing 1,200,000 with your highest value:

Click for larger image

Formatting Tips

I don’t go through all the steps to format the chart in this video to keep it to a reasonable length. However, here are some posts that I’ve written and videos I’ve created to help you:

Some opinions expressed in this article may be those of a guest author and not necessarily Marketing Land. Staff authors are listed here.

SMX Advanced is the only conference designed exclusively for experienced paid search advertisers and SEOs. You'll participate in experts-only sessions and network with fellow internet marketing thought leaders. Check out the tactic-packed agenda!

About The Author

Annie Cushing is an SEO and analytics consultant. Her areas of expertise are analytics, technical SEO, and everything to do with data — collection, analysis, and beautification. She’s on a mission to rid the world of ugly data, one spreadsheet at a time. If you just can’t get enough data visualization tips, you can check out her blog, Annielytics.com.

Sponsored

Analytics news and expert advice every Thursday.

Justin Goodman

Can the next tutorial be on using named ranges to count the range and index only based on values within the dataset so that you dont need to know your min/max and you can add data later knowing that the chart will still work!

http://www.annielytics.com/ Annie Cushing

I’m not following you, but whatever it is you’re asking, it sounds like you already know how to do it.

Sam Mazaheri

this is gold. scrollable gold.

http://www.annielytics.com/ Annie Cushing

Love this! :)

kasyallen

You may be a geek when you call excel charts sexy, but I’m proud to be one of those geeks!

Annie, you’ve really outdone yourself this time; I love how clean this is, and the GA API, I seriously don’t know how many hours I have wasted on reports before this came into my life – love at first sight!

kasyallen

Quick question, I want to take Google & Bing Shopping out of the organic traffic, do you know if there is a way to setup filters so that these are excluded?

http://www.annielytics.com/ Annie Cushing

Heh. Thanks! That means it’s only downhill from here. :)

http://www.annielytics.com/ Annie Cushing

This is a great question. I found this answer re: Google, which seems to be the most accurate:

As for Bing, I think you would need to use campaign parameters, e.g., utm_source=bing and utm_medium=shopping to distinguish them from regular ads, but I’m not 100% about that.

http://www.annielytics.com/ Annie Cushing

Great! Glad it hit the mark. Even a broken watch gets the time right twice a day. :)

http://www.annielytics.com/ Annie Cushing

Then create one?

William

Fabulous tutorial, and nothing wrong in being a perfectionist. One question I have as a very new user to dashboards. I have a set of data that is in weeks and months but as you know there a some months with 5 weeks and some with 4, so is there anyway you can do the scroll bar with this is mind.
I want to create the scroll bar via weeks but the middle by the month hence my problem.
Also love your very direct answers to other comments, my kind of girl