1. It’s dead simple
My previous blogs cover advice about how to analyse PPC data with Excel, from the genesis of a PPC Account to the more advanced Excel tips here. And it’s not just for PPC and AdWords, similar principles are common to both Analytics and SEO. However, where we use TRIM and other text manipulation formula on Adgroup and Campaign names, SEO data can apply similar manipulations on domain and subdomain URLs or the URL Query Strings.
It’s simply a case of using good principles from the outset. Deploy manual tagging on all of your SEO initiatives such as link-baiting, this will allow you to easily test data coming in from multiple URLs and combine the data for campaigns that are strategically linked.
2. The same pitfalls are common:
The first and foremost thing to acknowledge is that Excel has changed a lot over the years. The things that you can achieve and the speeds that you can achieve them in later versions are both reduced in older versions. Read the Warp-Speed section of my 3 Top Tips and keep in mind that best practice is often the fastest and most simple process.
3. Think in grids or stacks:
For most of the things you wish to achieve in Excel it is essentially a big table and you need to retain its shape and structure in order for it to work right. If you can imagine your table as sets of stacked blocks where each column or field is a different colour it will become easier to visualise what you are doing when you insert or delete a cell.
If you try to push a block into the stack, or indeed remove one, you will change the shape of your table. It will no longer be even and Excel will respond by creating uneven references and calculating incorrectly.
You want to keep your stacks straight. Consequently, insert a whole row or column of blocks when needed: add slabs rather than play Tetris.
4. Steer the Mark Twain:
Everything is misaligned, and you know there are a ton of miscalculations and you don’t know what is causing this. The likelihood is that someone has kicked a load of Tetris shaped holes into the report. The key here is to take a leaf from Singleton’s book, ignore the noise, pay attention to the basics and follow Excel’s navigation. In case this analogy hasn’t been laboured enough, I am comparing Excel to the sea and its users to a ship’s crew.
Excel doesn’t change, but over the course of thousands of records it is possible that the alignments established in the first case may have skewed. Rather than find the problem, find where it is correct and the alignments are straight. Excel uses quite colourful indicators that can help you determine alignments quickly, find the right formula and copy that back through the whole table so that everything refreshes.
5. Structure your pages and your data:
This is a simple but very important part of Excel and it comes in two parts. Firstly, Excel thinks top to bottom, left to right. Structure your data accordingly and charts become easy and calculations become quicker. Secondly, if you have a table shape, repeat it where possible and this will open options up for you to create 3D or Spearing formula. For example, say that you have a worksheet for every month of the year that details the site performance of each URL, by keeping the same shape you will be able to copy this sheet to the front and create a cover sheet. And in this cover sheet you will be able to sum all the fields with a Spearing formula that would read something like this “=SUM(‘January:December”.
Insights
