Note from Ann: This post is a nice summarization for our last week’s post on advanced Excel tips for Internet Marketers.
Most of us who are into the search marketing industry have our own set of favorite tools that we cannot do without. While I have never been a big fan of most of those so called SEO tools, there is one tool that I can never get enough of – you guessed it right, MS Excel.
This is probably the only tool that is equally relevant for SEO as well as PPC campaigns and is used in almost all the phases of the campaign. In this article I am trying to list some of the ways Excel can be used for SEO as well as PPC campaigns.
Use of Excel in SEO
- Keyword Research: The first step in any SEO campaign is keyword research and the use of Excel starts right from there. It’s a fantastic tool to list all your keywords, classify them in smaller groups, note their stats ( competition, traffic etc ) and if you would like to calculate KEI (keyword Effectiveness Index) for your keywords, Excel does that too for you.
- Keyword Mapping: While some SEOs might differ, I find this as an extremely crucial step where we basically plan out the entire campaign in respect to the different pages of the website and each keyword that are targeted to them. Again Excel is a great tool in creating an easy and clear map of the various keywords and the respective pages on website that they will be targeted at.
- Link Building – No SEO campaign is complete without link building and Excel is probably the best way to build a database of your prospective link opportunities as well as to maintain a systematic list of all the links your site gets. A database of link prospects would probably have information like Website/Page URL, Niche, Page Rank, Outbound Links, Inbound Links, Type ( Directory, Article Directory, Review site, Blogs, Paid etc), Contact information. How detailed you want to make your database would depend entire on you.
If you are a professional SEO like me, it is advisable that you maintain these databases with you even after your project gets over because you never know when you land up another project from the same/ related industry. You can then just use a simple Filter on your Excel File and pull out all the links that you have in any particular niche.
When you are using Excel to keep an inventory of your links, besides the above points, you must include Anchor Text used for link, The URL to which the link goes. Putting them all together in an Excel allows you to check at any point how many links do you actually have for each keyword and to which page they point to.
- Reporting – For most SEO projects clients insist on a ranking chart for their keywords and Excel here again is your friend. If you have some minimal understanding of Excel you can cook up some really nice charts showing the various keywords and their rankings and this can be further enhanced by retaining the historical data over the period of the campaign and plotting them on a graph.
For those of you who are into a more advanced and holistic form of SEO, you would probably like to show some traffic data as well to your clients. Now that Google and most analytics tools offer an “Export to Excel” option, you can use that with some customization to create your own dashboards.
Use of Excel in PPC
- Keyword Research : Though kind of similar to SEO, there are some major difference in keyword research for a PPC campaign compared to SEO, the first one being volume. PPC campaigns would typically have many more keywords than a SEO campaign and in this case Excel does not only help you to effectively list all the keywords and their statistics, so you can choose the most appropriate ones, but it does an awesome job in helping you segregate the keywords in relevant Campaigns and Ad groups, thereby helping you to plan your campaign structure. Hint: Filter by common seed words.
- Creating Your Ad Copies: Most search engines have their own formats and character limitations for Ad Copies. While writing an ad copy is an absolutely creative job, it might become a little tedious to keep track of the character count of each and every line of your ad copies, specifically when you are working on bigger accounts with hundreds of ad copies. Use the LEN formula to make Excel count the characters in your ad copy. Also, you can actually use Conditional Formatting in Excel to ensure all ad copies that goes beyond your stipulated character limits gets marked in Red/ or color of your choice.
- Upload Set up –For those of us managing big paid search accounts it often becomes too difficult to set up an account manually and in most cases you would end up using some tool like Adwords Editor ( For Google). All these tools allow you to create Excel files in specified formats and bulk upload to the search engine through their interface.
- Performance Reports – Search marketing and particularly PPC provides very detailed information on the campaign performance and these data actually work as the foundation of improving and optimizing your campaigns. While most of these reports can be exported in Excel format for viewing there is much more that you can do with Excel on these. Particularly, Excel can work as a very handy tool when you are trying to combine data from multiple sources (Multiple search engines, DART or other ad serving systems etc ). Pivot tables are also a great help in crunching these data and creating other useful reports as per requirement. To learn more on this you can read my article about how Pivot tables can be used for SEM campaign. It is also often helpful and makes you look more impressive and professional when you serve your client reports with some fancy charts that you can create in Excel in a jiffy.
The points that I wrote above are just the tip of the iceberg, there is no limit to what you can do with Excel. In case some of you are already doing something cool with Excel to help your search marketing campaign, feel free to share with us in the comments. And if you have not yet started using Excel extensively for your campaigns, it is high time, you start using this wonderful search marketing tool.
