I am passionate about all things strategy. On a practical level, that translates into a love for all things data because I believe in approaching strategy from a data-driven model. Having worked in house, with agencies, and now as a consultant, I've seen a LOT of really ugly data. So I've kind of made it my mission to help marketers learn how to both corral data and then make it sexy.Feel free to ask me anything about web analytics, Excel, or strategic marketing, with an emphasis on SEO from 11am to 1pm EDT.
Annie! Thanks for doing this.
What are the best resources to use for learning more advanced Excel functions?
I absolutely adore ExcelIsFun's YouTube channel. He has taught me most everything I know about Excel functions. He and MrExcel also do these dueling podcasts that are brilliant. They take a task and attack it two totally different ways. They're my faves. MrExcel doesn't make quite as many videos, but he's also freaking brilliant with Excel formulas.
Besides Excel, what's your favorite data analysis tool?
I just signed up for a statistics course with Coursera but have already started doing research on R. My understanding of it is very limited right now, but I have a strong feeling it's going to quickly become a tool I rely on heavily.
Is it the one run by the University of Toronto? If so, I found it very helpful since I come from a liberal arts background.
Princeton, actually. https://www.coursera.org/course/stats1
The curriculum looks totally different and much more relevant to web intelligence. Thanks!
Yeah, I've already watched the first video. I'm really looking forward to it.
R has tons of cool packages and libraries for every kind of data crunching and visualization. I use the ggplot2 library for all my data viz in R.
I've done some work with R before - nice to know I wasn't going into "overkill mode" as I thought at one point. Certainly has its uses!
If you had a time machine and traveled back to the "MS Office" meeting where they decided which default charts would make it into Excel, which one(s) would you tell them not to add?
Great question. The radar chart. I've found good use for it once, but most of the time it's too noisy to be valuable. Personally, I wish the pie chart would die a natural death, but too many people have an affinity for it. For better or for worse.
Yep, Pie Charts are the Nickelback of Data viz https://twitter.com/WaltHickey/status/345646754089291777
Hello, I am relatively new SEO and am trying to identify problems with our ecommerce site. I went through your site audit and found that we may have an index bloat problem. Only .12% of indexed pages generate at least 10 visits and any revenue. How would you go about trying to correct that problem?
Ah yes, this is very common. I'd run a Screaming Frog scan of it and compare those pages (broken down by top directories so it's manageable) to your landing pages report. Start using canonical tags, robots.txt, and/or noindex to get the junk out of Google and Bing's trunk.
Thank you! It's always a little hard to know where to start when you are teaching yourself. Always a joy reading your posts and learning from a female data master!
Google Analytics or Omniture, or does it all depend on the team setting up the tagging and categorization systems?
Omniture only if you need to collect personally identifiable info. Otherwise, I'm not a fan of its clunky infrastructure or its lack of support. Seriously, what company charges you extra to access their help tutorials?!? Especially for a product as expensive as Omniture's.
I definitely agree, but I was wondering if you're able to get similar functionality around page/site flow from Analytics that you do from Omniture. What are the GA workarounds (outside of the flowchart it provides)? Have you ever used something like event tracking to track flow between pages, and not just within pages?
thanks annie for doing this -
any tips for extracting / intrepreting / guessing <not provided>?
No. I think any attempts at this point is all junk science. However, I do highly endorse the approach I shared with Amir above (screenshot: http://screencast.com/t/R6BjUAlKDhY6).
Thanks for taking the time to do this!
I'm on client side of a company with a pretty good brand presence (retail locations, catalog, big e-mail program, etc) and we have an agency helping with our SEO. You were one of the first people I saw talk about how extrapolating provided data over the "not provided" isn't good practice, and in fact can really skew your data. You gave out some excellent solutions regarding how to uncover some of this missing data, but what I'm having trouble finding are good metrics to measure year over year success and putting a round number on the magnitude of SEO efforts - for now we're still using non-brand organic revenue. Any ideas?
Yeah, at this point those extrapolations are about as reliable as divining rods. I think organic has to be approached holistically now. You can't segment out non-branded. But the best place to focus is on your organic landing pages. And if you want to look at keywords, look at them in the context of landing pages. I shared a custom report in my MozCon presentation that allows you to drill down from landing page to keywords.
GWT's Search Queries and Top Pages reports also help you see what landing pages are ranking for each keyword (SQ report) and vice versa (TP).
Between these two reports, I can assure you that you will find more than enough things to focus on to better optimize your site for growth.
Beyond a website's analytics set-up, are there any other interesting data sources that can be applied to an analysis? Third-party, APIs, etc.?
I love the data I get from SEMRush and Searchmetrics. Both show you your visibility in the search engines by showing you the keywords your site is ranking on the first two pages of Google for. You can even see the landing pages for each keyword. They both tend to show more short-tail keywords, so you're not going to see as much of your long-tail keyword visibility. But still great data to analyze, esp when combined with data you get from GWT's Search Queries and Top Pages reports.
I also really like GTMetrix for site speed issues. If you haven't seen it, I have a huge list of tools for marketers: http://www.annielytics.com/hundreds-of-tools-for-marketers/
Awesome!!! Several questions:
1) What's the neatest data visualization tactic for Excel that you haven't written about but think people should know?
2) Same as above but for data analysis and not visualization?
3) If you had to use one other analysis tool other then Excel what would it be?
4) Excel macros, worth learning or better to learn other data analysis languages like Python?
5) How is the book coming?
1) Seriously, the series I'm doing right now on dashboards at Marketing Land. I will eventually do a video that will show marketers how to replicate a Google Analytics report in Excel. It'll be crazy. But I have to build upon the basics, or people will get lost. And I hate to frustrate marketers.
2) R. I just have to learn it first! But when I do, watch out.
3) R and Python
4) Microsoft insists it's not abandoning VBA. And Python only works in Excel if the recipient has the plugin as well, making it unfeasible. So yes, I'm learning VBA.
5) Just got back into it this weekend after taking a break to get through all the conferences I [over]committed to.
Your Marketing Land series is amazing, every time you have a new post I break off a little bit of time to learn the visualization technique. Loved the interactive charts!
Would love to hear more about your decisions to learn R. I made it a 2013 goal of mine to learn basic Python and SQL so I can start diving into this book http://shop.oreilly.com/product/0636920023784.do. I would love to hear your thoughts on R if you dive into it a little more, after doing some research and reading some feedback from people much more knowledgeable then myself I felt like Python was a better way to go since I can also use it to automate some tasks, do some crawling etc.
Yeah, I wanted to learn Python and made the mistake of signing up for Coursera's Python course. It was a big bucket of horrible, if you're not already a programmer. But I also don't have access to a good deal of actual "big" data. And I like that R works in Excel with plugins. But we'll see. I do eventually want to do more with database-driven data analysis. I just don't have access to databases as a consultant.
Ugh, I tried a couple of Coursera Python courses and they were all types of awful. Luckily through a combination of Udacity (their CS 101 class is Python based) and CodeAcademy I feel like I have a better start. I haven't really heard about R plugins for Excel. That sounds really interesting, I will definitely be looking into that more. What resources are you planning to use to start learning R? Coursera?
Yeah, I'm trying Coursera again for their statistics course: http://bit.ly/163cZ3n. For Python, I heard the best resource to learn is the book/course Learn Python the Hard Way. I've heard nothing but good things about it. And the video course was cheap.
I have a script that I use to export Adwords stats directly into a spreadsheet report template that I made in Google Docs. Everything works great, but on one particular account I have certain campaigns that are separated by Labels. Is there a way to pull the stats by label?
Let me know if you need to see the code that I'm using. I didn't want to paste it all in here.
I'm not sure what you mean by separated by labels. Do you want to send the code to my email, annie(at)annielytics.com, with more of an explanation of your issue?
Hi Annie! I love your Excel Annielytics topics and have a strategy question for you:
When advising SEO clients on IA/UX, what do you believe is the single most important design element that can effect other metrics?
Wow, this is a toughie. If I had to pick one I'd go with having super clean, organized pages that break content into easy-to-digest chunks (as Jakob Nielsen calls them) and generous white space. If visitors come to your site and see the online version of a circus or a solid wall of interminable text, they're going to hit the Back button, regardless of how awesome your product or company is.
Hey Annie, being an Excel geek myself, I always love reading your stuff. I still have a rpinted copy of your article on stripping down URLs to their domain name within Excel because I always forget that formula!
Anyway... when it comes to reporting back to clients on SEO performance, are there any specific Excel templates that you use? If you don't have ones that can be shared, are there any specific charts that you would consider a 'must' to include within them?
Looking forward to your response!
Thanks for the kind words, Matthew! And I'm glad my posts have resonated with you. :)
Because I don't just report on SEO performance but more demonstrate how it fits in a client's overall marketing ecosystem, I tend to create reporting dashboards that include things like top mediums (both month-over-month and year-over-year) and historical organic and overall (with a scrolling chart to keep it from swallowing up the dashboard). But it's important to me that I find out what KPIs keep my clients up at night and report on those. And they're going to change pretty dramatically from client to client.
Thanks, Annie - good idea. I use a similar technique with my Google Analytics custom reports and then tie them in to the overall project objectives.
Hi Annie, thanks for doing an AMA here on Inbound.org!
What's the most naïve/ridiculous statement you've ever heard anyone make undervaluing the power of data-driven strategy? Any instances of people saying "let's just go with our hunch" - stuff like that?
Oh yeah. The whole "I follow my gut" is probably the one I hear most. I always translate it as, "I have no idea how to analyze data and don't even know where to get the data I would need to analyze anyway, so I go with what I do have and hope more spaghetti sticks than falls to the floor." :)
Hi Annie, thanks for this.
If there was one fictional or serious metric you could obtain from a website, what would it be?
I would give my eye teeth to know true bounce rate. The closest anyone will ever come to that is probably Google b/c they at least have their toolbar, Chrome, Android, and logged in user data to see which visitors to your site bounce back in under x seconds to the SERP page. But I'd love to have that data across the board. GA's bounce rate is an imperfect measure b/c even if someone comes and spends 20 minutes reading your blog post, it's going to register as a bounce if s/he leaves the site afterwards.
Instead of just using time passed, you can fire GA events when the user is active, for example binding scrolls and hovers. This will let you understand if the user is engaging with the page and for how long, not just if they're keeping the page open in a background tab (which I do all the time).
what are the most useful custom excel reports you know, for webmasters especially of SMB sites, whether they be eCommerce, leadgen etc. (reason I ask: so much of what we put out as an industry is incredibly complex, and leaves 'the little guy' in the dark).
This is such a good question. If you check out Mirosoft's repository of custom Excel reports, you'll see that there is nothing (that I've found) for online marketers. But your question gives me an idea to actually ask my blog readers to send me requests for custom reports, and I could create them and explain how I created them. I wouldn't be able to do entire dashboards, but I could at least give SMBs some basic templates they could easily use by telling them, "paste your [whatever] report data here and select Refresh All." Something like that.
This is why we <3 you Annie! Look forward to seeing what your readers come up with!
Thanks for the idea, Martin!
I've got a question:
Why should new webmasters spend their time monitoring and analysing advanced analytics metrics instead of using the time for growing their brand, creating high-quality content etc.?
By advanced analytics metrics I mean things other than the most basic ones (pageviews, unique visitors, demographics, traffic sources, bounce rate, new vs. old visitors, page-specific stats, etc.).
Great question, Rohit. I think that, aside from forensic data dives, webmasters should only spend their time monitoring key performance indicators (metrics on 'roids) that they care about. So for one webmaster, this might be just the basics you listed. For another, they might be a brick and mortar who only cares about traffic from their sales regions, let's just say. That will require a custom report using some advanced(ish) metrics.
But I also think that when it comes to analytics, a reporting dashboard should be created that pulls in the data they need (preferably using the GA API), and then formatted in a way that helps key decision makers see what they need to effortlessly. But all too often marketers don't automate this process and do the same repetitive tasks week after week, month after month.
Ok my question is What will be Your Content Marketing Strategy for an App Development firm?
Since I take a data-driven approach to marketing strategies, I'd first take stock of what content the app dev firm already has and see how it's being leveraged and can be improved. Then I'd do extensive competitive analysis using the approach I outlined in this Moz video (http://bit.ly/14ubWJe). It allows you to reverse engineer your competitors' marketing strategies that you can take and improve. Or at least trigger other ideas for your content providers to take and run with.
hi annie, thanks for doing this
i know you've mentioned seo but i'd like to hear your thoughts on social media analytics - particularly when it comes to competitive analysis/intelligence. what are your favourite tools if you have much experience of using many? in general, what are the indicators you look for to assess strategy?
staying within the social spectrum - there's obviously already a lot of noise on what are worthwhile metrics and not and so on - so i want to ask you a slight tangent of that topic; how do you approach a client who only really wants visibility of top line/level metrics? by this i mean - how do you still offer insight while having one hand tied? if that makes sense..!
A colleague of mine, Kristy Bolsinger, is my main go-to person when it comes to all things social media. So knowing that she's forgotten more about social media than I've learned, I asked her to contribute to the Social Media tab of my ginormous tools for marketers doc. You can see great social media tools listed there (http://bit.ly/18p4Ln2).
As far as reporting goes, this may sound a bit morbid, but I really take a bit of a sharp shooter approach to competitive analysis. For example, I had a client who provided [ridiculously delicious] fine chocolates. But they were having a hard time competing against the likes of Ghiradelli and Godiva. But when I pulled a host of social media metrics from their top five competitors, we found all kinds of kinks in their competitors' armors. So, although they couldn't compete with them on Facebook, they had HUGE opportunities with YouTube, Pinterest, and Instagram. Their competitors were asleep at the wheel with these networks.
So taking an approach like that might engender some renewed interest for you. Good luck!
thanks for taking the time to reply annie - appreciate it and i've seen your great list before, of course! i was just curious about your favourite - might drop kristy a line about that.
Thank you for the reply. Now I'm going to implement it and will let you know the results. One more thing is What will your favorite Social Media Channel for this?
Good! I look forward to hearing about your experience. The best place to find me is Twitter: @AnnieCushing (https://twitter.com/anniecushing).
I'm already following you.. I meant which social media channel will you suggest to me for content marketing of App development niche.
Hi Annie! Thanks for taking the time to do this.
Can you recommend a good resource for an Excel/data analysis beginner?
A great way to get a good comprehensive overview of Excel, without wading through a stack of books, is lynda.com. I used it a lot when I was first learning b/c there are so many classes, from general overviews to specific task-oriented courses. And it's only $25/mo, so what I'd do is sign up, check out a few courses, cancel. Rinse, repeat. Probably not what they would want to hear but it works. :)
I also know that Distilled U has an Excel course for marketers. TBH, I never checked it out, but all of the modules I did do were off-the-charts awesome. I think it's $40/mo now.
Haha! I've actually been looking at lynda.com lately! I will check that and Distilled U out again. Thanks! :-)
Always a pleasure, lady! :)
What are some good alternatives to Google Analytics? I have noticed most others are pretty bad.
I've heard decent things about Piwik but haven't played with it. I would love to play with Unica. It offers a lot of customization for marketing campaigns, but its documentation is crap. Behind that I'd say Coremetrics and then Omniture. Unfortunately, the paid platforms have really lagged behind GA when it comes to innovation, but you need to use them if you want to collect PIP data.
Hi Annie -
Thanks for doing this! You're probably my favorite person to follow on Twitter. Do you have any good resources that explain the importance of social media for SEO?
Aww shucks! Thankeww!
I've been looking through my Evernote for a survey of Google and Bing done last year that asked about the impact of social media on SEO. It was the first time, I believe, both Matt Cutts and Duane Forrester both confirmed that they use social data in their algorithms. If I find it, I'd love to send it to you. What's your Twitter handle?
Thanks a ton! Sounds like it's exactly what I'm looking for. My Twitter handle is @HillsTwitts.
Great! I'll look for it. I can't figure out how I tagged it. Blerg.
Hi Annie- You can only have 5 metrics/reports/charts on your e-commerce Dashboard. What are they?
5 charts: Top Channels/Mediums, Top Landing Pages (segmented by medium), Mobile vs Tablet vs Desktop, Big Spenders (using custom variables), Visits w/ Transactions (to isolate the behaviors of visitors who made it to conversion)
Okay, folks. That's a wrap! A big thanks to all who participated!
what's the most surprising A/B test you've ever run?
what are your top 3 "web analytics" blogs or authors that you follow. (sorry i am late to submit these questions)
Two Random questions:1. What is the best method you have used to track Social Media ROI from Analytic's (I know it is a hard one at times to gain accurate measurement from) 2. What are your thoughts on mid tier businesses trying Attribution modelling, I know most big business gives it a shot but what about mid tier?
Thanks for this AMA :)
That one person who helped you in learning this excel/analytics thing?
I'm finding difficulty in learning custom variables, regular expression etc in analytics. How can I learn & remember it easily?
Thanks for doing this, Annie.
What is your biggest/most impactful find/success on analytics?
do you read bh seo blogs like http://blackhatpwnage.com or http://agentblackhat.com or you stay with just 'white infos'? I mean do you look at the other side too or totally ignore it [information wise - not usage]
haha I love the name of your company very funny !!!!! very creative and different !! sure is a good choice !!!
Great! Head over to login and use your credentials there