1 / 41

Analyst in a Can

Discover how to navigate data overload in SEO with this comprehensive guide. This document outlines a structured approach to utilizing Google Analytics (GA) and Excel for effective data management and analysis. Learn how to calculate penetration, gather variable data, and visualize results. Understand local perceptions across various platforms like Urbanspoon, Zillow, and Yelp. The manual breaks down processes into four parts, ensuring you can analyze correlation coefficients and the impact of variables on your SEO strategies effectively.

gaetan
Download Presentation

Analyst in a Can

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. A self-administered SEO colonoscopy using GA, Excel & a stick to bite on. Analyst in a Can

  2. Going From This . . .

  3. . . . To This

  4. Data Overload

  5. Apples and Oranges

  6. Part I: Calculate Penetration Part II: Gather Variable Data Part III: Put them Together and Plot Part IV: Impact the Variable Process

  7. Part I: Calculate Penetration

  8. wtf is Local?

  9. What your Site thinks Local Means

  10. What your Site Thinks Local Means

  11. What Urbanspoon Thinks Local Means

  12. What Avvo thinks Local Means

  13. What Zillow Thinks Local Means

  14. What Yelp Thinks Local Means (hmmmmm)

  15. What GA thinks Local Means

  16. 1. Filter your GA Data

  17. 2. Download GA Data 1. Sort by City 4. Find table.rowCount%3D500 in URL 2. Ad “Region” as Secondary Dimension 5. Change 500 to 10000 6. Export CSV 3. Specify show rows = 500

  18. 3. Download Population Data URL: http://ezlocal.com/blog/post/Top-5000-US-Cities-by-Population.aspx

  19. 4. Clean Up Data in Excel

  20. Key Excel Functions • LEN – count number of characters in a cell • LEFT – define number of characters in a cell to display (from left) • CONCATENATE – put two cells together • SEARCH – find position of a specific character within a cell (from the left)

  21. Clean Up Data Examples (City)

  22. Clean Up Data Examples (Metro)

  23. 5. Standardize local and move data to same Worksheet

  24. 6. Merge Data (Vlookup crash course) 1. Sort Metro’s by Ascending order 2. Add column for population 3. Vlookup your metro

  25. Merge Data 4. Table Array – where you are looking for the data 5.Define column w/data and use FALSE if you haven’t sorted by ascending

  26. Merge Data 6. Add strings so you can pull down 7. Pull down -

  27. 8. Calculate Penetration

  28. Warning: Exogenous Variables

  29. Warning Example:Country as an Exogenous Variable

  30. Penetration List

  31. Part II: Gather Variable Data

  32. 9. Gather Variables • Use the URL’s to assess effectiveness of: • Inbound links • Volume • Domain diversity • Quality (links with higher rank in Majestic or SeoMOZ) • Local(through IP ID) • Anchor text matching • The latest blackhat technique your executive team dreamed up • Social • Long Tail: # of indexed pages

  33. Gather Variables: Social

  34. Gather Variables: Local Links

  35. Part III: Put them Together and Plot

  36. 10. Calculate Correlation Coefficient

  37. Correlation Coefficient Warnings • Causation vs. Correlation • Correlation between variables

  38. 11. Plot It via Scattergraph

  39. Part IV: Impact the Variable

  40. 12.Impact the Variable

  41. Stay In Touch Conrad Saam Atticus Marketing 206 486 2087 @conradsaam Slides available at: www.atticusmarketing.com/slides

More Related