Excel for SEO

Earlier in the year, our PPC Manager, James, wrote a post about how he uses Excel for PPC. Following on from his post, I’m going to show you some useful formulas and shortcuts which can be used for SEO purposes.

When starting at Crafted 18 months ago, my Excel knowledge stretched to summing data within a column. Now, the majority of my know-how stems from using Excel on a daily basis and looking for tips and tricks in other blog posts. By finding quick ways to manipulate data, I can spend more time analysing the results to gain useful insights and plan actions based on these findings.

Below details many of the tools, formulas and shortcuts that I use regularly to perform data analysis efficiently.

Text to Columns

‘Text to Columns’ allows you to split up a text string, by ‘,’ ,‘ ’, ‘:’ or a character of your choice, / ? etc. This is a very useful tool when trying to isolate a portion of a URL.  After using ‘Text to Columns’, I often use the ‘Remove duplicates’ tool located next to the ‘Text to Columns’ button, to find unique cells.

Concatenate

The reverse of ‘Text to Columns’, this function combines cells. You also have the ability to add characters in between these cells, for example ‘/’, ‘.’, ‘-‘.

Conditional Formatting

There are many aspects to this section. I mainly use the duplicate values tool and ‘Text That Contains’, which highlights a particular keyword within a cell. This is useful for sorting through a website’s backlinks. Both of these functions can be found within the ‘Highlight Cell Rules’ within the ‘Conditional Formatting’ section.  

CountIF

When I need to know how many times a cell or a portion of a cell appears within hundreds of rows of data, the CountIF function is essential. For example, if you want to see how many times ‘dog toy’ is mentioned in cells, use =COUNTIF(A:A,”dog toy”) or  =COUNTIF(A:A,A4).

As an extension to this, if you’re just looking for a portion of the cell, for example ‘dog’, you would need to use: =COUNTIF(A:A,"*"&"dog"&"*").

Counting words in each cell

The LEN function tells you how many characters appear within a cell. For SEO purposes there isn’t a huge use for this, apart from counting cell length for title tags. A more useful extension is to use this to find out how many words are in each cell. The formula for this is: =IF(LEN(TRIM(A1))=0,0,LEN(TRIM(A1))-LEN(SUBSTITUTE(A1," ",""))+1).

A useful purpose for this would be to calculate the number of anchor texts with that number of words. For example, to find the number of keywords equal to and above say four keywords, use: =COUNTIF(A:A,">=4").

VLookUp

A really handy formula when trying to find a cell within a table and then displaying a corresponding value: =VLOOKUP(lookup_vaue,table_array, col_index_num,range_lookup). For example, if you were looking to see how many dog toys were sold, the formula would be: =VLOOKUP(D2,A2:B7,2,0).

So what does this formula show? D2 is the cell you’re interested in, A2:B7 is the table in which you want to look, ‘2’ is the number of the column in which your data is held and finally ‘0’ displays the exact match to the question. One limitation in using a VLookUP, is that the cells in which you’re interested have to be on the far left of the table, in this case ‘Product’.

Offset

When exporting annual comparisons from Google Analytics, the data is seldom in an easy format. By using the OFFSET function, the data can be transposed into an easy to use table. For example, if you wish to turn the format of the data from,

to,

You’ll need to use the =OFFSET($A$2,(ROW($A3)-2)*2,0). The screen shot below shows the formula for each of the cells.

Useful shortcuts

Aside from the obvious, copy, paste, find shortcuts etc., I’ve found the below useful to speed up everyday tasks:

• Ctrl D copies the cell above
• Ctrl R copies the cell to your right
• Ctrl - removing the whole row or column
• Ctrl + adds a new row or column

For more information on the above functions please see:

• Conditional Formatting
• CountIF
• VLOOKUP 
• OFFSET

By Rachel Tooke 

Digitally Crafted - a round-up of the week in digital

In this week’s round-up of the most important topics within digital marketing we learn that more businesses are redesigning their websites with tablet and smartphone use in mind, Facebook tests out its new Timeline design and new research reveals the dramatic differences in the digital reach across the UK’s most popular newspaper titles.

Social media

Instagram finally embraces the web
After Instagram shot to prominence as an iPhone-only photo-sharing app, the service has since expanded to Android and now has a full web presence. However, the new Instagram web profiles are not the complete Instagram experience online; there is still no option to upload photos via the web, but users now have a URL to share with friends and family. Read more

Brand adoption of Instagram continues to grow rapidly
Since Facebook’s acquisition of photo-based social network Instagram for a cool $1bn, the site’s number of registered users has grown rapidly, from 30m to more than 100m. The site enjoys more than 11m daily active users, and new research published by social analytics firm, Simply Measured, indicates brand adoption of Instagram has jumped by a staggering 35 per cent since August. Read more

Foursquare introduces new hundred-point rating scale for places
Foursquare has updated its iOS app by implementing a brand new rating system, giving locations a score from 1 to 10, including decimal points. The scores for each location appear in the app’s ‘Explore’ feature and are driven by a host of metrics, including the number of likes, dislikes, check-ins and so forth. Read more

Twitter plans to add photo filters to compete with Instagram
After Instagram enjoyed unprecedented success across its network with images of Superstorm Sandy, during which users uploaded as many as ten pictures per second, Twitter has intimated that in the coming months it will update its mobile applications to introduce similar photo filters. Sepia tones, retro looks and black and white filters will be available to a wider market. Read more

Facebook tests out new Timeline design on “small percentage of users”
Facebook is seeking to roll out further amendments to its Timeline design, according to the Telegraph. A makeover of profile pages is said to involve placing all wall posts in a single column on the left with friends, apps, activity updates and places located on the right – as opposed to the existing layout which uses a two-column design. Read more

Organic search marketing

Google to move search options above results
Google has confirmed this week that all of its American searchers will soon be getting a new interface that largely resembles its existing mobile search results pages. Options to search verticals like Images, News and Maps are moved above the top search results. Google says it plans to roll out the interface globally “as soon as it can”. Read more

Google releases Panda Update 21 – impacts 1.1% of US queries in English
Google also confirmed this week that it rolled out Panda Update 21, impacting about 0.4 per cent of all search queries a regular user would notice. However, for those searching in the US in English, this figure is likely to be higher at around 1.1 per cent. The 21st release has stayed in keeping with a 4-6 week release schedule of additions to its algorithm. Read more

Google’s market share ‘dips below 90%’ in UK
Although Google remains the leading search engine in many countries, the giant’s market share has dipped to its lowest percentage in the UK in five years, according to Experian Hitwise, a firm that monitors web traffic. Figures released by the company indicated that 89.33 per cent of all UK web searches were made using Google, while its main rival, Microsoft’s Bing, now has five per cent of the market share. This has been attributed by experts to the aggressive promotional tactics adopted by Bing in recent months. Read more

Paid search marketing

Bing Advertising to increase transparency with “Quality Impact”
Bing Ads is introducing a new feature for PPCs called Quality Impact, designed to inform advertisers how many new impressions they could gain by improving their Quality Score for each individual keyword. The tool compares competition for targeted traffic and volume and can be accessed via the Keyword Performance Report using Bing’s web interface or its API. Read more

eCommerce

Survey finds three quarters of UK consumers now grocery shop online
The latest survey of the digital economy by Nielsen has found almost three quarters (74 per cent) of UK consumers now head online to shop for groceries. Nielsen, a leading global information and measurement firm, interviewed 28,000 consumers in 56 countries and found that saving money was the main reason behind the growing trend of online grocery shopping. A further 27 per cent of Britons also suggested they would be purchasing food and drink online in the coming months. Read more

Web design

More companies are designing their sites for tablet and mobile
An increasing number of businesses are responding to mobile trends by ensuring their redesigned websites are made with smartphones and tablets in mind, but many are still not even testing how their sites look on mobile devices. In 2012 more than half (55 per cent) of companies surveyed had not tested the usability of their website on mobile or tablets, but there has been a 10 per cent increase in the number of companies now designing websites specifically for mobile users. Read more

PR

Newspaper, print and web readerships at a glance
The second release of the National Readership Survey’s Print and Digital Data (NRS PADD) has revealed the dramatic differences in digital reach across the UK’s largest newspaper titles. While more than half of The Guardian’s readers are now based purely online, The Times has just a four per cent digital-only reach due in no small part to its ‘pay wall’. The Daily Telegraph can hold claim to the biggest daily readership across print and web, with figures of 1.87 million. Read more

Motorsport, aviation and two commercial property websites

In 1907, wealthy landowner Hugh Locke King developed the world’s first purpose built motor racing circuit, Brooklands in Surrey.

In the years that followed, Brooklands became synonymous with major events in British aviation and motorsport history. The site was home to the first powered flight in a British built aeroplane, saw the world land speed record broken for the first time and accommodated the first ever British Grand Prix.

Today, Brooklands is one of the leading business destinations in the south west of England. It’s home to The Heights, a prestigious and historic grade A working environment which now benefits from a beautiful, effective and brand new website designed and built by Crafted.

The website is designed to reflect the motorsport heritage, whilst showcasing the impressive spaces available to potential occupiers through high-quality photography and detailed information regarding the development. Crafted's search team have been driving the website forward through targeted search marketing efforts, which will ensure significant visibility for related search terms such as 'commercial property Surrey'.

In addition to The Heights at Brooklands’ website, we’ve recently launched the available space for Norfolk House in Manchester with an elegant website solution that matches the quality of the imposing building and the existing high-profile occupiers, such as Zurich and the National Blood Service. 

Both properties are owned and managed by PRUPIM, a client of Crafted for more than five years and for whom we manage a growing portfolio of commercial property marketing websites.

Digitally Crafted – this week in online marketing

In this week’s round-up of the most important topics within digital marketing we learn that 70 per cent of Pinterest users use the site for retail inspiration, Google has launched its “disavow links” tool allowing webmasters to inform the search engine of low-quality links and LinkedIn profile pages have undergone a major overhaul.

Organic search marketing

Google launches ‘Disavow Links’ tool
Google has unveiled its widely anticipated ‘Disavow Links’ tool, with Matt Cutts, Google’s head of web spam, discussing the tool during a keynote at the Pubcon conference in Las Vegas this week. The tool has already been beta tested by selected SEOs. The premise behind the feature is to allow webmasters to alert Google to take low-quality links that are beyond their control into consideration when assessing a site within its search engine. Read more

Mobile

One in five recruitment searches are mobile
More people than ever are beginning to search for vacant jobs on their handheld mobile devices, according to a statistic provided by Emma Frazer, Google’s head of industry for recruitment, during an interview for the upcoming Mobile Recruitment Conference in London next week (Oct 23rd). One in five recruitment searches are now made via mobile handsets or tablets. Read more

PR

The Times uses bulk copies to keep circulation above 400,000
In order to keep its headline circulation above 400,000 last month, The Times buoyed its sales figures by handing out 17,000 free bulk copies across the country. These revelations come as six other national daily newspapers reported double-digit year-on-year declines in readerships. The Times only reported a 5.3 per cent year-on-year decline, primarily due to the bulk copies that were sold for a nominal fee to partners including airlines, railway operators and freebies. Read more

Social media

Facebook to expand its mobile security efforts
Facebook announced earlier in the week that it would be increasing its web and mobile security efforts in order to protect its users against malicious URLs, threats and numerous scams that hit the world’s most popular social network on an almost daily basis. Facebook’s existing platform of security providers is now offering software for web and mobile devices. Read more

Survey finds who uses Twitter, when, and how
Social media analysts, Beevolve, have conducted an extensive survey of Twitter usage, outlining who exactly uses Twitter. The company generated statistics range from bio, tweets, account types, categories and even the background colour preference of Twitter users. People in the United States were found to be the highest users of the service, at 50.99 per cent of Twitter’s user base, with the UK coming in second at 17.09 per cent. Read more

Facebook’s algorithm changes make engagement all the more important
Facebook has released an update to Edgerank – the social network’s algorithm – aimed at strengthening the importance of user engagement with its pages. A greater emphasis will be placed on the percentage of users that respond to page posts through likes, comments, shares and other interactions. Read more

LinkedIn strives to boost interaction with profile revamp
In a bid to encourage its 175 million members to interact with its service on a more frequent basis, LinkedIn has rolled out a major overhaul of its profile pages with a simpler design, making it easier for professionals to see what contacts in their network are up to. The site now has more than 10 million UK members, with the new visual design enabling users to make a more powerful first impression. Read more

Pinterest users can now block
Marketers increasing their activity on Pinterest will be interested to hear about the new account features being rolled out this week. Pinterest users will now have the ability to block and report other accounts if they think they may be violating the site’s code of conduct. The option will be available under a small flag icon situated on a user’s profile page. Read more

70 per cent of Pinterest users are looking for shopping inspiration
A recent insight study into the reasons why American and Canadian consumers use Pinterest revealed that almost three quarters (70 per cent) of users logged on to gain inspiration on what to buy in the shops. Almost half of Pinterest users also revealed that they used the site to associate with brands and retailers with which they identify. The study also suggests Pinterest should find it easier monetising its network, with so many users already in a commercially-based frame of mind on site. Read more

Why PPC loves Excel

PPC is all about data. Whether you're building a new campaign, writing new creative or optimising accounts, most of the time you'll be using Excel.

Excel is an extremely powerful tool for PPC Execs and Managers and can save us a lot of time and effort on tasks, and not just on large accounts. Successful PPC management is not just about the ROI and CPA figures, it's also about  efficient working. The less time you can spend manipulating data, the more time you can spend developing, optimising and crafting beautiful PPC campaigns. At Crafted, we like to find more efficient ways of doing things. For example, when we send over creative for clients to review, we send it over in excel. Sometimes there can be 100’s of ad groups, and, to make it easier on the eye, we separate each ad group with a row. Adding in hundreds of rows manually in Excel takes ages, so we built a macro which does this in seconds.

Other simple tasks, such as amending display URLs, across multiple ad groups can take forever if done manually, but, with Excel's concatenation function, it can be done in minutes. Finding themes in large SQRs can be simply done with conditional formatting, and 'Find and Replace' makes building out similar ad groups very easy. Excel also has some very powerful tools which, when used correctly, can unleash some amazing insights hidden away in the AdWords and Analytics data.

This blog is going to look at some of the everyday Excel functions that can help save time in the day to day management of running PPC campaigns. Part 2 will look at how to set up a detailed time analysis using pivot tables, and Part 3 will be a guide to using Vlookup and 'if statements'.

I've listed below some functions and macros that we use at Crafted to save time. These aren't obvious Excel functions, such as dedupe, subtotal, text to column or concatenate, but they'll prove extremely handy to have in your Excel toolbox.

Percentage increases

Often clients want to know how a campaign has done compared to last week, last month or last year. This formula allows for you to quickly compare multiple sets of data across campaigns. 

Insert Row Macro

As mentioned above, if you want to send data to a client, it needs to be easily digestible. Having an empty row inserted in-between ad group data, such as keywords or creative, makes it easier to read. This macro will insert a row at each change of ad group:

Sub InsertRows()
  Dim r As Long, mcol As String, i As Long
  ' find last used cell in Column B
  r = Cells(Rows.Count, "B").End(xlUp).Row
  ' get value of  last used cell in column B
  mcol = Cells(r, 2).Value
  ' insert rows by looping from bottom
  For i = r To 2 Step -1
    If Cells(i, 2).Value <> mcol Then
      mcol = Cells(i, 2).Value
      Rows(i + 1).Insert
    End If
  Next i
End Sub

Count Words

You may find that sometimes you want to know how many words are in a keyword or description line.

=IF(LEN(TRIM(cell))=0,0,LEN(TRIM(cell))-LEN(SUBSTITUTE(cell," ",""))+1)

Round up to 0.05

Sometimes when optimising certain campaigns I like the bid to be set at £0.05 increments. Excel rounds up or down to the nearest decimal place, so I use this function to get number to the nearest 0.05.

= round(cell/0.05,0)*0.05

Hyperlinks

There have been a few times when I've been sent an Excel file which consists of a long list of landing pages, but the URLs are in a hyperlink so you can't see the URL. If you need to build out campaigns for these landing pages, you need the URLs. This macro extracts the URL from the hyperlink.

Sub ExtractHL()
  Dim HL As Hyperlink
  For Each HL In ActiveSheet.Hyperlinks
    HL.Range.Offset(0, 1).Value = HL.Address
  Next
End Sub

Alternatively, you may need to convert URLs to hyperlinks, which can be done with the following macro:

Public Sub Convert_To_Hyperlinks()
  Dim Cell As Range
  For Each Cell In Intersect(Selection, ActiveSheet.UsedRange)
    If Cell <> "" Then
      ActiveSheet.Hyperlinks.Add Cell, Cell.Value
    End If
  Next
End Sub

Every PPC management process should be approached in the same way, with the same question asked: Can this be done, easier, faster and with less effort? Most of the time, it can using functions and macros.

In part 2 of this blog we're going to look at how Excel can help you view your PPC data in a new light and make the invisible visible. This will be a step by step guide of how to run a detailed time analysis using pivot tables.

Happy crunching.

James Giles

@jamesgiles