Our Top 5 Excel Time Saving PPC Tips

Once your PPC account scales to a certain level, using a spread sheet programme like Excel can really speed up the process of analysing stats and making mass changes to your account.


We use many different Excel functions to help really speed up bulk changes. Instead of changing each cell individually or doing a lot of copy-and-paste, you can use Excel’s functionality to do all the hard work for you. In this article, we share our top Excel tips for streamlining tasks to help save you time and reduce the potential for making mistakes.


1. Use filters to produce better SQRs and EPC reports


Search query reports (SQRs) show which search terms have been paired with the different keyword matching options, and are a great way to help optimise the performance of a campaign. While Earnings-Per-Click (EPC) reports help you to measure how effective a campaign is at turning clicks into sales. Use filters to quickly identify problem areas within your campaigns, for example;

  • Choose the filter “does not contain 0” under the “sale” heading to see keywords which have converted and eliminate the worst performing ones
  • Use the “fill colour” icon in filters to colour in cells to distinguish “good” and “bad” keywords and filter by colour
  • Use the filter function to sort by a specific product, and copy and paste the same ads or URLs to similar ad groups


2. Use “VLOOKUP” to look up and match data from different tables


Formula: =VLOOKUP(look up value, table, table column, exact or general match)

If you have one set of information in one table which you want to match to data in another (e.g. keyword and campaign name or URL), use “VLOOKUP” to look up data in rows. “HLOOKUP” can be used to lookup data in different columns. Use the “VLOOKUP” formula to;

  • Assign URLs from a master table to a list of keywords
  • Collect campaign clicks and sales from different sources


In addition, if you have multiple conditions you want to match before summing the data such as, the sum of the sales from brand keywords in London at weekends, use “SUMIFS” to drill down further on specific criteria.


3. Use Pivot Tables to save time when gathering data for charts


Pivot tables can enable you to quickly create tables and see the total for a certain metric related to a campaign, for example;


4. Use the “concatenate” formula to join cells or text strings together


Formula: =CONCATENATE(A1, “text”,A2, A3,…)

This formula enables you to join cells together, or append/insert text into new cells and is therefore extremely helpful when deep-linking, creating ad copies or building keywords. Use this formula to;

  • Add the brand term to all of your keywords quickly when creating a list of brand generic keywords, rather than adding the brand term to each keyword individually
  • Build tracking strings from multiple inputs e.g. keyword, campaign name


5. Use the “Substitute” formula to save time when creating new ads or optimising landing pages


Formula: =SUBSTITUTE(text, old text, new text)

This formula is very useful when adding tracking data to the URLs when deep-linking. Use this formula to;


The above tips are Excel tools that we use every day to help increase efficiency and reduce the likelihood of errors occurring within our campaigns. We hope that you find these useful for your own Paid Search campaigns.


Good luck! As always, if you have any questions please don’t hesitate to get in touch.


Shining a light on China – Emma Herrod of Internet Retailing explores the Chinese opportunity with Sri Sharma of Net Media Planet

Emma Herrod of Internet Retailing talks to Sri Sharma, Managing Director of Net Media Planet, and Johnny Zhu, CEO of Chinese advertising agency CharmClick, to explore the rise of e-commerce in China and the online marketing opportunities for British brands.


The key highlights from this article are below, to read a full version in the current issue of Internet Retailing please click this link; http://viewer.zmags.co.uk/publication/39607222#/39607222/12


As UK brands increasingly explore international business opportunities, of all of the overseas markets it is China that potentially offers the greatest opportunity.


Chinese consumers are expected to spend £177BN online this year, and by 2020 the market is expected to be worth nearly £1BN to the UK e-retail industry*. Given the growth projections for China’s retail market, it’s no surprise that UK brands are increasingly raising their investment in the region.


The Chinese market and the importance of Baidu

For companies aiming to crack the online retail opportunity in China, it’s imperative to understand that the country’s e-commerce market is very different to the established markets of the United States and Europe.


Much of the ecommerce opportunity comes through Baidu, which is the largest search engine in China. Baidu provides a massive opportunity for brands to create awareness and subsequently drive sales. For brands that are looking to grow their business in China, Baidu offers the best opportunity to do so.


However, until recently Baidu has been largely closed to overseas businesses, making it extremely challenging for Western brands to promote their products and services online in China.


But all of this is changing, as Baidu has finally opened its doors to the West. Recently, Baidu has been extending its reach internationally and working with third parties, such as CharmClick, and its UK service provider Net Media Planet, to help reach potential international customers. With the aim being to open up the Chinese market to overseas companies and help brands to leverage the existing demand.


Understanding the Chinese consumer

For brands looking to get online in China, it is important to understand China’s unique online retail ecosystem, and to learn how potential consumers are searching for and buying your products or services. For example, typically Chinese online consumers are much younger than Western online consumers, and have different expectations, preferences and patterns of search and purchasing behaviour.


This means that barriers that exist in some markets may not be there in China. As Johnny Zhu of CharmClick explains, “Although some UK brands don’t have Chinese websites or offline stores, people do search for UK brands in China, for example Mulberry, Alexander McQueen or ASOS.” Baidu offers the opportunity for UK brands to benefit from the consumer demand that already exists in China, without necessarily having a presence locally.


Net Media Planet is in the unique position of being able to help UK brands capitalise on their international brand reputation in China. As Sri Sharma, Managing Director of Net Media Planet explains. “For any brand looking to explore the international opportunity, and are looking to access new markets, Baidu can be a great mechanism for that. You can get results quickly, using display ads to create brand awareness and use SEO as well as Paid Search as you would on Google.”


The opportunity for UK brands is there. So where should brands start?

Sri and Johnny explain that the strategy and the approach should be the same as for any market entry, and share 5 tips advice for brands looking to access the Chinese market through Baidu:


1. Capitalise on the existing reputation of your brand – If your brand is well known in China, use that to broaden your presence further. For example, Tommy Hilfiger learned that though their shirts sold well, they were being purchased by a completely different demographic in China. The team used this insight to tailor their messaging and maximise their presence to their new target audience.


2. Understand search behaviour – Chinese consumers may well be searching for your brand but in many cases consumers may use a combination of Chinese and English words. For example, with ‘Gap’ and ‘trousers, ‘Gap’ will be in English and ‘trousers’ in Chinese. Research consumer behaviour to produce more keywords that are relevant to what and how your potential consumers are searching.


3. Don’t assume that bidding on just your brand will drive traffic – In many cases there are multiple variations of how consumers write your brand name with Chinese symbols. For example, ‘Marks & Spencer’ is hard for Chinese consumers to write so there are a number of different variations of how Marks & Spencer would be written in Chinese. It’s important to research and understand those variations so that you appear in high positions for all variations.


4. Don’t assume you need a Chinese language site – British retailers can easily add source code to ensure they appear on Baidu, and they can also purchase Chinese keywords and creative that takes consumers to an English site. Chinese consumers are quite familiar with this approach and so not having a local language site is not necessarily the turn off that it can be in other markets. However, it should be said that a localised site will always maximise conversions.


5. If you have little brand awareness in China take an incremental approach – Using a combination of advertising, SEO and Paid Search activity will help to drive brand awareness whilst increasing conversions. By periodically testing performance you will soon understand how your brand is growing as more people search on your keywords and you will be able to adapt your strategy accordingly.


The Chinese opportunity for UK brands has clearly arrived. Through the partnership of CharmClick and Net Media Planet, UK brands are well placed to overcome some of the challenges that have existed previously for those looking to explore the Chinese market.


To read the full article please click; http://viewer.zmags.co.uk/publication/39607222#/39607222/12


To talk to someone at Net Media Planet about exploring the Chinese market further then please call 0870 3926951 or email Digby at [email protected]


* http://viewer.zmags.co.uk/publication/39607222#/39607222/12

Understanding the Full Value of Mobile – the latest tools and insights from Google


We check our mobiles up to 150 times each day and 90% of people use their mobile devices to solve problems in their personal and work life. Despite this, many brands are still unsure about how to bring Mobile into their marketing campaigns.


To try and tackle this challenge, Google, last week, hosted an information exchange on the Full Value of Mobile. The event gave some fascinating insights on the proliferation of Mobile devices and ever evolving consumer behaviours. Delegates also heard from a number of companies who are starting to bring Mobile into their mainstream marketing activities, and learned about the latest tools available from Google for Mobile-specific campaigns.


Here we share some of the key points from the event;


Firstly, brands are starting to move in the right direction and examples of best practice are cropping up. Examples include Disney which has an excellent responsive site designed to cater for masses of content and video. As well as Currys, which although not glamorous, is potentially the first large scale ecommerce responsive site for Mobile.


We also heard from Simply Business, an insurance company who launched their Mobile site 3 years ago. They launched the site with PPC Mobile ads and a small outbound sales team to help drive traffic and to monitor activity. As a result they were able to understand, for the first time, how their consumers moved across devices during the research and engagement process. The Mobile site also helped to drive and close new business, with conversion rates increasing to around 50% from Mobile.


As well as case studies, Google showed us some of the tools available to help marketers improve the effectiveness of their Mobile campaigns, which include;

  • Advanced call reporting on the Enhanced Campaigns platform, with call forwarding numbers available on Call Extensions to track and monitor ‘click to call’ activity
  • The ability to schedule Call Extensions for higher relevance. For example, to only show phone numbers at times when call centres are open


The team also gave us a snapshot of some of their brand new features for Mobile, which include;

  • Offline conversions (beta) which allows users to upload offline sales data into Adwords with a unique Google Click ID number to monitor online-offline activity
  • In-App Adwords conversion tracking which tracks specific user engagement after an app has been downloaded
  • Mobile App Analytics which shows end-to-end Mobile app metrics such as acquisition, engagement and conversion
  • A new Mobile Remarketing tag which has been improved to increase the value of conversions by more easily segmenting consumers and targeting them with highly relevant messages


In addition, Google showcased their new Full Value of Mobile tool, which is designed specifically to calculate how much Mobile could be worth to your business. A fantastic tool, and a must for anyone trying to understand and quantify what the value of Mobile could be to their business; http://fvm.withgoogle.com/fvm/en-gb/d/


Although Mobile tracking and attribution remains a challenge – how do we really know who researched a product online and then went and bought it in-store? There is progress in this area. We heard from Walkbase, which is a company that specialises in monitoring in-store traffic. The team have developed technology to track and monitor wifi enabled phones so that retailers can monitor in-store behaviour, track the ratio of new versus repeat customers, traffic volumes and conversion rates.


Clearly the tools to help support, track and monitor Mobile activity have taken a step forward. This coupled with the expertise that is becoming more widespread as brands and specialists test the channel, is bringing Mobile to the forefront of Marketers minds.


How much marketers should commit to spending on Mobile remains uncertain. But what is clear is that brands who want to stay close to their customers can no longer avoid or delay the inevitable move to Mobile.


The event provided a very interesting update on the developments of the Mobile opportunity, and some useful insights on how brands are exploring and testing this channel. Watch this space!


Two takeaways for Mobile Search:


We couldn’t resist sharing two titbits gleaned from the Google team on how to drive more performance from your Mobile campaigns;

  • Use Search and Display together to maximise impact – this approach will capture intent and then drive search volume to your site. Remarketing on Mobile is a very easy win
  • Focus on conversion tracking – try and track as many conversions as possible on mobile (calls, apps, in store, cross device) as this will enable you to attribute value to your mobile investment and see a better ROAS


On a final note we were delighted that Carolyn, our Marketing Manager came first in the Mobile quiz to win herself a new Nexus 7 – congrats on knowing your Mobile stats Carolyn!


Top 5 ways to reach new horizons for the Travel sector with PPC

The travel sector is one of the most prolific spenders in Paid Search marketing. Indeed, travel companies spent more on pay-per-click advertising than any other industry except for finance in 2012*. As the market becomes more and more saturated, brands are under increasing pressure to drive efficiency and performance from this channel.


Here are some of our key insights specifically on how brands can use Paid Search to drive online performance.


1. Be smart with your Keyword Coverage

Selecting keywords should be an important part of your strategy, and it is important to consider keywords that will ensure you have coverage across all stages of the buying cycle. High visibility throughout the buying cycle will reinforce your brand so that you will stay in the forefront of the consumer’s mind. We would recommend that you develop a strategy that is aligned to the consumer buying cycle as follows;

  • During the awareness stage: use keywords that are location specific – example ‘holidays in Spain’
  • During the research stage: use promotional keywords with the location as secondary – example ‘cheap holidays in Tenerife’
  • At the intent to purchase stage: use keywords that are destination specific – example ‘all inclusive deals to x resort’
  • At the purchasing stage: Promote the brand name as the primary keyword


Top tip: Keywords in the awareness and research phases may appear to have low conversion volumes, but may be fundamental in the path to conversion so it is essential to track and monitor the click path. By reviewing the click path you will be able to quantify the effect of bidding on these keywords to see if they are truly having an effect on account performance.


2. Develop a bidding strategy that maximises your ROI

Understandably, not all brands want to target expensive generic keywords such as ‘holidays’ or ‘cheap holidays’, and in some cases it may not always be profitable to appear in the top position. We would recommend that you conduct positional tests on keywords and monitor performance to determine what positions perform the best for your keywords.


Top tip: It is important to consider your margins when developing your bidding strategy. For products with low margins, ROI can often be improved by appearing in lower positions with carefully selected keywords such as long tail or brand specific.


3. Make your Ad copy stand out

Why would a consumer choose you over your competitors? Why should a customer click on your ad and visit your site? What do you have to offer that your competitors don’t? With so many ads competing in the search results, you need to ensure that your ad stands out over and above the competition. To help do this, we would recommend that you;

  • Use price points in your ad copy and update these regularly to show you are competitive
  • Incorporate USPs into the messaging e.g. low deposits, price match guarantee, number of different packages on the website
  • Utilise ‘ATOL Protected’ to show users they can safely book with you
  • Implement sitelinks to make your ad stand out, take up more of the results space and increase your ad messaging. Incorporate USPs into sitelinks so that the ad text can push price points


Top tip: Make a conscious effort to ask users to review their experience with your company to ensure you meet the threshold for reseller stars to appear. This will give the user extra confidence that you are a trusted website and increase your CTR.


4. Ensure your landing pages are designed to enhance the user experience as this will drive more conversions

The travel industry typically has a long research phase, so ensuring that your landing pages are helpful and memorable to the user will keep your company in the forefront of their mind during the path to conversion. We would recommend that you include destination specific pages that not only detail the great offers available, but can also provide descriptive information or images on the destination and sub-destinations.


Top tip: Include additional information on the landing page such as local weather forecasts, insights into the local culture, available activities and insider tips. This will show the consumer that you are an expert in the sector. This in turn will help to increase the length of time consumers are spending on your site and the likelihood of returning.


5. Ensure you target the right people at the right time

All sectors are influenced by seasonality factors, and none more so that the travel industry. Therefore, it’s important to ensure that you are promoting the right messages to the right audience at the right time. There are a number of strategies you can adopt to help ensure that you are messages are promoted in the most effective way, these include;

  • Incremental bidding – to enable you to increase your bids and give your ads extra visibility when the conversion rate is higher. This could be based on geo-location or time of day
  • Day parting – to enable you to pause activity when there are minimal conversions and shift this spend into more profitable times of the day
  • Demographic Search (currently a Google beta) – to enable you to target by age or sex with specific messaging or promotions


Top tip: Consider Remarketing, as this approach is very effective for this sector, for example if your company also sells travel insurance or car hire, you can remarket users who have bought a particular holiday, hotel or flight with this type of complementary product.


To sum up

In this competitive market, things are changing quickly and brands are struggling to keep up. Driving the highest performance from your PPC activity will enable you to stay one step ahead of your competitors. We hope that the tips in this article will go some way to helping you run an effective and efficient campaign.


Good luck, and please do contact us if you have any questions!


* http://www.tnooz.com/2012/11/01/news/google-adwords-gets-stronger-but-travel-keywords-struggle-with-conversion/

Improving results by spending time with customers


As PPC specialists we are always looking for ways to improve the performance of our PPC campaigns.


One way to help maximise the performance of a PPC campaign is to build a sound understanding of the client and their customers. Therefore for each client we always build a detailed customer profile map – Who are they? How do they buy the client’s products? What are they motivated by? How do they engage with the brand? We therefore welcomed the opportunity to spend a day on site with one of our clients, Sk:n, and their customers.


We spent a day with the team learning about the types of different treatments offered, and all of the stages of the consumer journey – pre and post visit. During the visit we also tested a number of different treatments, which included having a UV skin scan and trialling microdermabrasion, in order to better understand the customer experience. We learned about the concerns customers normally have, and the best ways to communicate and help the customer in their research and booking of a treatment.


The key points we brought back to our Paid Search campaign:

  • We have a much clearer understanding of each treatment and how each customer is advised. This has provided us with new ideas for keywords based on changing search behaviour.
  • We learned more about the customer demographic profile, and that for example, the split between male and female customers is much higher than we expected. We will definitely utilise targeting techniques such as demographic search to reach specific audiences and tailor ad copies.
  • We saw how customers engage with the brand and the combination of treatments and services that people purchase. This has helped us evolve our landing pages and sitelinks to send people to the best converting areas of the site.
  • Speaking with both Sk:n personnel and customers has given us a much deeper understanding of each treatment and the terminology used to describe them. This in turn has given us lots of new ideas for ad copy testing.
  • We also learned more about how customers engage with social media – the sites used, the kind of content that works well, and the purchasing prompts – this has helped to inform our supporting social media advertising strategy.


This is valuable knowledge that will allow us to communicate and engage more effectively with potential new customers, to ensure that our campaigns drive higher performance levels for Sk:n.


Thank you Sk:n!


For more information on Sk:n and their treatments please visit www.sknclinics.co.uk