SEO Tips With Excel
Using Excel To Format Regex Code
If you are using excel like most people to analyze data pertaining to SEO then there is a pretty cool trick that I think you will benefit from. Not only will this help you save a lot of time, it will also help you track the progress of an SEO project that you or your outsourced SEO company may be managing. For example, if you contracted and SEO company to work on ranking your website for X number of keywords, wouldn’t it be helpful to have a report that you can look at each month showing only the Analytic data pertaining to visitors that organically found your website via that list of keywords? With advanced segments and Regex, this is possible. Today I will show you how to save time when using advanced segments to filter out specific data like the data we mentioned, by using excel “user defined functions”. There are many ways to use Excel for SEO but this one in particular I found to be very helpful which is why I wanted to post about this.
Excel short cuts for professional SEO reports
Excel is something that most SEO’s are comfortable with, so assuming that you know your way around excel, we will go over what a “user defined function” is. Like the “sum” function, which adds the values of a given range of cells, all functions are meant to carry out some sort of process. The “sum” function has a set of rules that it follows from start to finish which involves adding the value of one cell to the next, and the next and the next while storing the new value each time and displaying it once its done processing all cells.
Now assume you wanted to use a function to send a mass email out to someone but all you had was a spread sheet from your CRM that contained your client information like email, number, name, address ect… Your first thought might be that you have to manually take each email and add it to your email draft while separating each email with a coma. Such a mundane process can drive you crazy and cost you valuable time that you could be using doing something more important and that’s what life is all about, being efficient. So, for the sake of efficiency, I wanted to find a way to save time with this issue that I ran into a while back when trying to send a mass email. I found a user defined function that someone wrote and put it to good use with the mass email problem, I soon thereafter realized that this same function can be really useful when creating advanced segments in Google Analytics. How you ask? Well, if you use an advanced segment to create custom dashboards for your clients like we do, you know that it takes a long time to create Regex codes for filtering out long lists of keywords because you have to manually add each keyword and separate it with a line break.
Why Use Regex for Google Analytics?
By now you probably know where I am going with this, for those of you who don’t let me tell you. Lets assume that we were all marketing managers for a large company and the boss just asked for a report on how the SEO company has been performing for our 2013 SEO plan. Wouldn’t it be nice if you could put a linear graph that reflects the amount of visitors that your website has been receiving via the keywords that your SEO company proposed to rank your website for? I thought so, which is why I implemented that type of reporting into our business model. Now each of our clients, can log in to Google Analytics at any time and see a linear graph that shows the amount of traffic coming to their website via the keywords we are working on for them.
Using Regex To Pull A Custom Keyword Report./
Let’s go ahead and walk you through the process of how to do this and how to save time while setting up this kind of report by using Excel. First what you want to do is open your Google Analytics Organic traffic report and filter out your useless data like the (not provided), (not set), brand name search queries and URL search queries. The next thing you want to do is create an advanced segment. Some of you might be wondering what happened to the advanced segment button, relax guys its just the new Google Analytics interface and the button is actually located here:
Once you have your advanced segment window open, you are going to want to click on New Advanced Segment and if you are using the new version of Google Analytics, you will have a couple of different options to choose from. In this example, you are going to want to click on “Traffic Sources” and then change the drop down button (as shown in screen shot below) next to “Keyword” to “Matches Regex”.
Now the next thing you will need to do is prepare your Regex code that includes all of the keywords your want to see in your report separated by a line break and enclosed in a set of parenthesis. The code will look something like this:
(keyword1|keyword2|keyword3|keyword4|keyword5|keyword6|keyword7|keyword8|keywordEct…)
So to get this list of keywords, rather than manually entering each one and formatting it correctly which could take hours if you are trying to track thousands of keywords at a time for a e-commerce SEO project, you can use the function we discussed. The function can take your list of a keywords that are in one column like the type of spread sheet you get from Adwords, Semrush, Analytics or any SEO program for that matter, and this function will return the list of keywords formatted properly which you can then copy and paste into the field waiting to be filled in your Google Analytics window.
Setting Up a User Defined Function With Excel
To create a user defined function for this task you will need to have your keyword list ready and you will also need to open Visual Basic which can be done by hitting F11 on your keyword while in Excel. The next thing you will need to do is click on “Insert” on the top navigation followed by “Module” in the drop down menu. Next, you can simply copy this code that I found on the internet:
Function ConCatRange(CellBlock As Range) As String
Dim cell As Range
Dim sBuf As String
For Each cell In CellBlock
If Len(cell.Text) > 0 Then sBuf = sBuf & cell.Text & “|”
Next
ConCatRange = Left(sBuf, Len(sBuf) – 1)
End Function
After you entered the code, you can simply return to your excel spread sheet with the keywords and pick any cell to call the function. The function can be called by choosing any cell and entering “=ConCatRange(). Keep in mind that you will need to fill in the range between the set of parenthesis in order for the function to return a value. So lets say we are using a list of keywords in column A ranging from A1 to A150, what you can do is use a cell in column B to call the function by entering “=ConCatRange(A1:A150) and boom, there you have it, the nicely formatted list of keywords are now almost ready to be pasted into Google Analytics. All you really have to do is add a set of parenthesis around the list of keywords.
Video Demonstration
If you followed these steps correctly, you are probably wishing that you would have known how to do this a while ago. Especially if you are involved with sales or project management at an E commerce SEO company. The next thing I want to do is show you a demonstration of how this all works. Please feel free to comment or to email us via our contact us page with any questions, comments or concerns that you may have. Thanks for reading and watching everyone!
About The Author
Jason Hawkins / http://www.themiamiseocompany.com
Jason Hawkins is the CEO & Co-Founder of The Miami SEO Company. He has over ten years of experience in search engine optimization, conversion rate optimization and lead generation. His core responsibilities include identifying ways to increase value of services rendered, training staff on advanced SEO topics, and A/B testing internal processes to consistently improve client return on investment.
