Best way to analyse webmaster tools data

Recently, I read an article on Neil Patel’s blog about using the data from Google Webmaster tools. In that article, he categorized URLs in a list using colour codes for CTR and rankings. It took me a while to actually find a way to do that. So I thought of sharing the method that others can follow to get the excel sheet in the desired format.

There are three steps attached to this:

Step 1: Changing the numbers

According to the article, the first step that one needs to take is change the position of all the URLs/queries which rank below 10 to 10. This is easy. By using the IF function, one can easily get the result.

– IF(*cell which contains position* > 10, 10, *cell which contains position*)

– IF(D2>10,10,D2)

using if function in webmaster tools data

Step 2: Colour coding the cells

In the next step, Neil asked us to “Highlight all rankings in the 1st, 2nd, 3rd, or 4th position in green. Highlight all rankings below that in red”.

It took me some time to figure it out but I finally managed to find the answer.

Conditional Formatting!

– First copy-paste the values from the last step into the positions column. Select, copy and paste (Alt+E+S+V).

– Click on conditional formatting -> New Rule

– Select the second option “Format only cells that contain”, add values in the boxes and then click on format to select the colour.

using conditional formatting to filter webmaster tools data

– Do it similarly for colouring a cell red for position between 4.1 and 10.

– Shortcut to use conditional formatting and select new rule: “Alt + H + L + N”.

Step 3: Colour Coding CTR column using data from position column

This was the toughest task and again Conditional formatting came to the rescue.

In the next step, Neil said, “As for click-through rates, highlight keywords that rank in the 7th, 8th, 9th, or 10th spot and have a click-through rate greater than 3% in green, and, of course, use red for scores that are lower than 3%.

As for keywords that rank in the 4th, 5th, and 6th spot, highlight the click-through rate in green if it’s greater than 8% and in red if it’s lower than 8%.

For keywords in the 1st, 2nd, or 3rd spot, highlight them in green if the click-through rate is greater than 20% and highlight them in red if it’s lower than 20%.”

For this, you need to create rules in conditional formatting using formulas.

creating rule in excel conditional formatting

I used the formulas below to get the colour codes Neil was talking about.

=(AND(D2>7,C2<3%))

=(AND(D2>=4,D2< =7,C2<8%))

=(AND(D2<4,C2<20%))

=(AND(D2>7,C2>3%))

=(AND(D2>=4,D2< =7,C2>8%))

=(AND(D2<4,C2>20%))

Note: You need to add each formula individually and add the colour. Phew!

And this is how the sheet finally looks:

analysing webmaster tools data

This is a great method to filter out the pages/queries which are working well and the ones you need to work on. Analysing and working on this should help you in improving your website’s traffic.

6 Excel Functions that every SEO Expert must know

A good SEO professional knows his data well and also knows how to simplify it. When you are working on huge Excel sheets, you can’t do all the things manually. If you try, you will end up wasting a lot of time and doing wrong calculations and therefore Excel has some very interesting functions that can be very helpful to SEO professionals. The list goes as below:

CONCATENATE function in Excel

CONCATENATE function helps you to combine strings of different cells. This function proves to be very useful to the SEO professionals as you can easily combine the pieces of URLs (see image), can create sitemaps, can create link building queries etc.

Using Excel’s CONCATENATE function in SEO

Find Duplicates and Remove Duplicates in Excel

When you are working on too many keywords, there may be instances where you add certain keyword that already exists and that is where the Find Duplicate function of Excel will help you the most. Find duplicate function will help you to highlight the duplicate values in your data, so if there are any duplicate keywords, you can easily highlight it by using this function. You can get the Find Duplicate option in conditional formatting.

Using find duplicate function for SEO

If you think that there are too many duplicates and deleting them will take too much of time then you can use Remove Duplicate function of Excel which will remove all the duplicates at one go. Like we had 2 duplicate values in the above example, so next we delete those duplicates using the Remove duplicate function and you will see that only 14 unique values are remaining.

removing duplicates in Excel

Pivot Table in Excel

Working in the field of SEO, you will often come across excel sheets that can scare you as they contain huge data. Analyzing so much of data at a time can create a lot of confusion. Pivot table helps you to break down the data into smaller part which makes it easier to deal with it. With the help of Pivot Table you will be able to see only the data that concerns you. You can also manipulate the data and include the metrics that you want.

PROPER function in Excel

PROPER function in Excel capitalizes each word in a string. This function can be of great help when you are creating the page titles. So you can just write your page titles without worrying about the Caps lock key and at the end, use the PROPER function and you will see that your data looks perfect.

Using Excel’s PROPER function in SEO

LEFT, RIGHT, MID function in Excel

In the CONCATENATE function above, we saw that how we can join the pieces of URL but what if you want to separate the URL into pieces. With the help of LEFT, RIGHT and MID function you can exactly do that. These functions help you to extract leftmost, middle or the rightmost characters from a string.

Using Excel’s LEFT, MID, RIGHT functions in SEO

LEN function in Excel

Whether you are creating a meta description or title, making sure that it does not exceed the character limit is a task. You can’t check again and again and that is why the LEN function in Excel is very handy. LEN function helps to find the length of a string i.e number of characters in a string. So you can easily make sure that characters in your title are not more than 60 and characters in your meta description are not more than 160.

Using Excel’s LEN function in SEO

You can learn to use CONCATENATE, PROPER, LEFT, MID, RIGHT, LEN and many more text functions here

So that was the list of the functions that we feel are very important for SEO experts, if you think that we missed out something, feel free to mention it in the comments box below.

Date and Time functions in Excel

Date and Time functions in Excel are sometimes the most under rated functions in Excel even when they can be of great help. Imagine inputting dates of whole year manually or counting the difference between the two days manually, we are sure that no one has so much of free time and that is where Date and Time functions will help. So here is the list of Date and Time functions in Excel that will make your work quick.

Year, Month and Day function in Excel

When you want to know only the year of a particular date then you can use the Year function. Syntax =YEAR(date)

Similarly, you can also use the Month and Day function to know the month or day of a particular date.

Year, month and day function in excel

TODAY function in Excel

As the name suggests, Today function in Excel will help you to display the current day’s date. Syntax =TODAY()

TODAY function in Excel

DAYS360 function in Excel

When you want to find the difference between the two dates, you can use the DAYS360 function. Syntax =DAYS360(start date, end date, method).

While calculating the difference, you have two method options namely US method and the European method. If you want to calculate in US method then mention FALSE in the method and if European then mention TRUE in method.

US method (False) – If the starting date is the last day of a month, it becomes equal to the 30th day of the same month. If the ending date is the last day of a month and the starting date is earlier than the 30th day of a month, the ending date becomes equal to the 1st day of the next month; otherwise the ending date becomes equal to the 30th day of the same month.

DAYS360 function in Excel (US method)

European method (True) – Starting dates and ending dates that occur on the 31st day of a month become equal to the 30th day of the month.

DAYS360 function in Excel (European method)

NETWORKDAYS function in Excel

NETWORKDAYS function in Excel helps you to find out the number of working days between the two mentioned dates, that is, it excludes the weekends while calculating the number of days. You can also mention any other holiday or a list of holidays that you would like to exclude. Syntax =NETWORKDAYS(Start Date, End Date, Holidays)

.

NETWORKDAYS function Excel

WEEKDAY function in Excel

WEEKDAY function in Excel tells you the day of the week from the given date. This function starts counting from Sunday that is Sunday becomes 1, Monday becomes 2, Tuesday becomes 3 and so on. Syntax =WEEKDAY(Date)

WEEKDAY function in Excel

If you want to know the exact day instead of number then you can use =TEXT(A2, “dddd”) and you shall get the answer as Wednesday.

Text function in Excel

Hour, Minute and Second Function in Excel

To return the hour value of a particular time, you can use the HOUR function in Excel. Syntax=HOUR(Time)

Similarly you can use the MINUTE and SECOND function to return the minute or second value of particular time.

Hour, Minute and Second function in Excel

Now function in Excel

If you want to display the current date as well as current time then use the NOW function. Syntax=NOW()

NOW function in Excel

So that was the list of some important Date and Time functions which can actually save a lot of time. Which is your favorite function???

6 Excel functions that every PPC Marketer Must Use

In our last article, we told about the excel functions that every Social Media Marketer must know. Today, we bring to you some important excel functions that will make the life of all the PPC marketers easier. PPC marketing involves a lot of excel sheets and the following functions will help you analyze them easily.

TRIM and PROPER Function

When you have too many descriptions or ad copies in front of you, it is not possible to check spaces of each one manually and thus using the TRIM function helps. It removes all the unnecessary spaces in a string. Along with the TRIM function, you can also use the PROPER function when you are working headlines as that will capitalize the each word of the sentence making your headline look more presentable.

Using PROPER and TRIM function in Excel

After using the PROPER and TRIM functions, the headline of your ad will automatically become more presentable. You can also use the keyword insertion function to capitalize the first letter or the whole keyword.

VLOOKUP Function

The VLOOKUP function proves to be very helpful when you want to compare your data of two different timeframes. Even when you want to add new keywords in your list, you can use the VLOOKUP functionto compare the old keywords with the new keywords. Sometimes you may accidently add a keyword which already exists and this may increase your ad spends without any improvement in the results. With the help of VLOOKUP function, you can be sure that none of your keywords are repeated.

Using VLOOKUP in PPC marketing

In the example above, by using the VLOOKUP function we found out that 2 of keywords added already exist so now that we are aware of this, we can immediately pause one of the keywords to avoid conflict and track the clicks better.

NETWORKDAYS Function

It is not necessary that all your clients will want to run ads on all the days. Sometimes we come across clients, who want their campaigns to run only on weekdays. Usually, we allocate the budget per day by dividing it by 30/31 but when you are not advertising on weekends or some other holidays, this may not work. You can either calculate the number of days manually or simply use the NETWORKDAYS function. NETWORKDAYS function will help you to find the number of days in a month after excluding the weekends and other holidays mentioned by you.

Using NETWORKDAYS function in Excel

By using the NETWORKDAYS function in the above example, we came to know that we are supposed to allocate our budget for 21 days. These 21 days do not include any weekends and also the 24th April since we considered it as a holiday. You can also add a range of holidays if you want.

CONCATENATE Function

CONCATENATE function helps you to combine data from different cells. This function is extremely useful when you want to create a fully functional display URL. Even when you want to add tags to your URL, this function can prove to be very useful.

You can also use this formula to create standard ad copies. If you have a list of 100 products and you are making ad copy as ‘Buy this device now’ then you can easily use CONCATENATE function and create hundred such ad copies in one go.

CONCATENATE function for PPC marketers

LEN Function

LEN function in Excel helps you to determine the number of characters in a string. LEN function is very helpful when you are drafting your ad copies as ad copies should not exceed certain number of characters. You can also add a conditional formatting option, so that you come to know whenever you exceed the character limit.

LEN function for PPC marketers

In the example above, through LEN function we come to know that the characters in the headline of 1st Ad copy are 32 which exceeds the limit of 25 so you can make the changes accordingly.

Conditional Formatting

When you are working on too much data, there may be things that you may want to highlight and in such instances conditional formatting can be really helpful. For example, if you want to highlight the keywords that received more than certain number of clicks then you can easily do so within seconds by using conditional formatting.

conditional formatting in Excel

We have formatted column B in such a way that whenever the number of clicks are less than 100, the particular cell will get highlighted. You can format your data as per your requirement.

You can learn more about the TRIM, PROPER, CONCATENATE, LEN and other text function here.

If you think, that there are other functions that are helping you in a great way then do share with us and others by mentioning them in the comments box below.

Statistical Functions in Excel

People usually have love-hate relationship with statistics. When you get your formulas right you are in love with it and when your answers go wrong, your feelings take the opposite route. But when you are working on statistics in Excel, things are simpler and less complicated. So today, we bring to you the most used statistical functions of Excel.

Average Function in Excel (Mean)

One of the most used statistical functions in Excel is Average. Calculating the Average in Excel is much simpler than it was in the school. Simply use the Average function and select the range which needs to be averaged. In the example below we want to know the average of the marks obtained by the students so we use =AVERAGE(B2:B12).

finding average in excel

Median in Excel

Median is a function which is used to find the middle number in a given range of numbers. When you are finding median manually, you need to sort the data in an ascending order but in Excel, you can simply use the Median function and select the range and you will find your median. We take the same example as above to find the median of marks obtained by students. So we use =MEDIAN(B2:B12).

median in excel

Mode in Excel

Mode helps you to find out the value that occurs most number of times. When you are working on a large amount of data, this function can be a lot of help. To find the most occurring value in Excel, use the MODE function and select the range you want to find the mode of. In our example below, we use =MODE(B2:B12) and since 2 students have scored 55 we get the answer as 55.

calculating mode in excel

Standard Deviation in Excel

Standard deviation in Excel helps you to understand, how much your values deviate from the Average or Mean that is it tells you that whether your data is somewhere close to the average or fluctuates a lot. If the value received is on the higher side then that means that your data has a lot of fluctuations and vice versa. To calculate standard deviation in excel we use STDEV function. In the same example we shall use the STDEV function so our formula will be =STDEV(B2:B12). Our answer is around 20 which indicates that the marks of the students fluctuates a lot.

calculating standard deviation in excel

As you can see the answers of Average and Standard Deviation contain too many decimals, you can easily get rid of them by using the rounding functions

.

We hope that your concepts about the statistical functions in Excel have been clear. If you still have any queries then feel free to mention them in the comments box below.

5 Excel Functions that every Social Media Marketer Must Know

‘Social Media is all about being creative.’ Yes, we too agree with this but being a social media marketer also involves a lot of numbers. No, we are not lying. Just ask a marketer who got a good earful from his boss as he could not generate the number of likes promised to the client. You see the ‘NUMBER’ of likes. Yes may be this is a very silly example but trust me social media includes a lot of numbers, whether it is Facebook insights report or twitter analytics report, all are filled with innumerous data. So in order to simplify it to some extent we shall share with you some Excel functions that will help you with your data.

1. LEN

You wrote a really nice post for putting it up on Twitter and then twitter tells you that you are exceeding the 140 character limit. We know that it is really mean of twitter but we can’t help it. So next time you can draft your tweets in Excel and use the LEN function. LEN function in Excel helps you to find out the length of the string or sentence. So not just for tweets, you can use this function for knowing the length of descriptions, meta description etc as well.

Learn Len and various other Text functions here.

Using LEN function Excel

We have used an example of tweets and their characters. If you prepare your tweets in advance then, you can easily know their length by using this function.

2. VLOOKUP

Suppose you have two Facebook contest participants report with you of and you want to find out the common participants in both the contests so that you don’t end up giving the prize to same participant again and again. Instead of finding it manually, you can use VLOOKUP. VLOOKUP in Excel helps you to look up for a certain set of values in same or another Excel sheet or Excel workbook. So next time you get stuck up with multiple excel sheets, you know where you need to look up.

VLOOKUP function for Social Media Marketers

In the above example, by using VLOOKUP we found out that Participants A, D and F have participated in both the contests whereas others are 1st time participants.

3. COUNTIF

You just downloaded a Facebook insights report and you want to know how many of your posts are doing well so you will obviously check the number of likes, comments and share generated. You can easily know the number by using the COUNTIF function. COUNTIF function in Excel helps you to count certain number if they fulfill certain criteria. By using the COUNTIF function, you can save a lot of time that can be spent on some creative work.

COUNTIF function for Facebook reports

In the above example, by using the COUNTIF function we came to know that 7 posts out of 10 received more than 10 comments.

4. SUM/SUMIF

If you want to know the total number of people who liked, commented or shared your posts, you can easily find it out by using the SUM function. If you want add certain criteria like sum only if liked between certain dates then you can use the SUMIF function . These functions can add all your data within seconds and you don’t have to worry about the accuracy as well.

Learn to use the SUMIF function here.

SUMIF function in Excel

In our example, we have used SUMIF function to know the SUM of likes but only if they were above 25. So in our sum of 232 the likes of Post 1, 2, 8 and 9 is not included.

5. Formatting /Conditional Formatting

Want to send the social media report to the client?? You cannot send them the report you just downloaded from Facebook or Twitter. There may be data which is important, the data which needs to presented in tabular form, the data that needs to be highlighted. To put it in simple words, your data requires formatting . Formatting will make your data look more presentable and you never know your client might just get impressed with your formatting skills. If you want to add conditions while formatting then use the function of conditional formatting.

Using conditional formatting function

Again in the same example, I want to highlight the posts where the shares are more than 15 so I use the conditional formatting option.

So do let us know if using these excel functions are helping you or no. If you are using some other a function that is making your life easy then do share with us that as well.