Guide to Excel VLOOKUP basics and top five rookie mistakes

Following on from our time saving Excel shortcuts, we continue offering updated advice for the time-sensitive spreadsheet enthusiast.

Back in 2013 John Gagnon wrote a very popular post about VLOOKUP basics and rookie mistakes.

We thought we'd update the piece to reflect some minor changes for accessing the functionality to VLOOKUP words and values in Excel 2016.

An Excel VLOOKUP can be a marketer's best friend because it can save you hours of work. Give this formula the information you have (a name) and it looks through a long list (list of names) so it can return the information you need (phone number).

The problem is we often struggle to remember how to use the formula – or worse make mistakes.

We're going to fix that now. This post will explain:

• How VLOOKUPs work.


• Using 'Tell me' to access VLOOKUP functionality in Excel 2016.


• Five rookie VLOOKUP moves to avoid.


• Limitations you might encounter.

Many of the tips are courtesy of John Gagnon, and are accurate as of September 2016.

How to use a VLOOKUP

Remember phone books? Phone books happen to give us a fantastic mental model to understand how VLOOKUPs work.

Basically, the phone book is a long list of just a few columns: names and phone numbers. You pick up a phone book with a clear intention – find a phone number (info you want) for a specific person (info you have).

Once you've found the person you're looking for, you look at over to the second column to find their phone number. Call made, problem solved.

It turns out this is the same principle for how a VLOOKUP works. Let's breakdown what each piece of the formula to understand what they mean:

There is an added piece of information needed for a VLOOKUP called a range_lookup. This basically is how accurate you want your results.

Excel 2016: Using 'Tell Me' to access VLOOKUP functionality

Excel 2016 comes with a new multi-purpose search box, the 'Tell me what you want to do' tool. Click the box, or ALT + Q to jump right to it. From there, if you type 'VLOOKUP' or any lookup or reference search term, really, then the function you need will appear in a dropdown menu.

For the purpose of this article, I want to VLOOKUP a value. Selecting this then offers the 'Function Arguments' box where you can add in the Lookup_value and Table_array etc.

As you can see, it's a little more helpful for newer users than in previous editions of Excel.

Five rookie VLOOKUP mistakes to avoid

Realizing VLOOKUPs work the same as a phone book is helpful. It's also helpful to know the common mistakes. Here are the top five mistakes made by VLOOKUP rookies.

1. Not having Lookup_Value in first column of your table array

VLOOKUPs only work when the info you have (lookup-value) is in the first column of data you're looking at (table array). To use the phone book, you need to start with a name first. You can't start with the phone number and find the name.

2. Counting the wrong number of columns for Col_index_num

Once Excel has found the value you gave it, it needs to know what give you back. This comes in form of a column number. Make sure to start counting from the first column of the range (table array).

3. [Range_Lookup] Not using FALSE for exact matching

Many marketers get the wrong values because they forget one step. Ninety-nine percent of the time we want exact match, which means a value of FALSE (here's why).

4. Forgetting absolute references (F4) when copying the formula

The power of a VLOOKUP is it can be copied down to hundreds or thousands of cells. But once you copy this down, the references change leading to errors. To fix this issue convert your range to an absolute value instead of a relative value, so cells don't move around (as they tend to do).

5. Extra spaces or characters

Occasionally when data is copied from one source to another, a few leading or trailing spaces tag along. This causes issue during the match, so use TRIM to delete any spaces added to the cell (except for any single spaces between words).

VLOOKUP 201: The John Smith problem and going left

After you use VLOOKUP enough, you'll encounter its limitations. For example:

• It only returns the first match it finds, even if there are hundreds of possible matches.


• It can only return a value in the table array to the right – it can't go left!

(There are simple solutions to these problems, creating unique keys and pasting – but we'll save those for another time.)

Back to the phone book for a moment. How many John Smiths are listed? Probably more than one. But with a VLOOKUP, only the phone number of the first John Smith is being returned! You're probably calling the wrong guy.

To make sure you're calling the right John Smith, you need to bring in additional information. Commonly for phone books it's an address (e.g., John Smith at 123 Acme Lane or John Smith at 765 NW Jones St.).

Again, it's the same for VLOOKUPs.

Let's say you want to know match type by keyword. Your match type column would be identical (all “broad”, in this case), and your second variable (keyword) would differentiate the data.

Create a new table with both pieces of data in columns, and insert “&” in the table_array field of your VLOOKUP. Then the VLOOKUP knows to return the combined data for your result.

What are your most common uses for VLOOKUPs? Is there anything about VLOOKUPs that stump you?

Just 36% of people entering the tech industry are women

The technology industry is lagging behind many other sectors when it comes to the proportion of women taking up entry level positions.

This is according to US research by McKinsey in their new report: Women in the Workplace

The study surveyed 132 companies which collectively employ more than 4.6m people. It shows that while 75% of CEOs in corporate America are saying gender equality is a top ten priority – and in the wake of the high-profile Gamergate controversy – tech is still woefully behind.

Women entering the tech industry are far outnumbered by men and they lose ground on every step of the ladder

The McKinsey report separates out key industries in the US and shows the proportion of women working at each stage of the corporate ladder.

For the tech industry (including electronics, hardware, software and IT) just 36% of entry level positions are accounted for by women. This proportion goes down to 31% at manager level and an even lower 19% at the C-Suite level.

Tech is certainly lagging behind other sectors…

For instance, the asset management and institutional investors industry sees 50:50 parity for men and women at entry level (but just 14% women in the C-Suite). Professional and information services sees a majority of women at entry level (59%) but just 22% at C-Suite.

The report also allows for comparison of the issue with corporate America as a whole. On average, 46% of all people going into corporate jobs in the US are women but this shrinks down to just 19% at the C-Suite level. For women of colour, the percentage is 17% at entry level and 3% at the C-Suite.

Gender inequality research in tech and digital is growing

McKinsey's research builds on wider research into gender disparities across tech and digital.

In February, Econsultancy released UK-centric career and salary data for those working across the marketing, digital, design and advertising industries. The research highlighted the inequality in average pay between men and women across the digital sector – from specialists to general marketers.

In 2016, the average female digital specialist earns £38,176 – around £8,000 less than her male peers. For women in general digital marketing roles, the average salary is £37,477 – again around £8,000 less than the £45,750 earned by the average man doing the same work.

Gender inequality as contributor to the digital skills gap

The latest McKinsey report and that by Econsultancy earlier in the year will be cause for concern for those in government.

As I wrote in an article last month, a recent report by the Science and Technology Committee highlights that 90% of jobs in the country today require digital skills to some extent and suggests that we need 745,000 workers with these skills to fulfil industry demand by 2017.

The report also looks at methods for overcoming gender inequalities in its chapter headed Role models and diversity in STEM, stating:

“There is continuing concern over the lack of diversity among computer science/IT graduates and in wider Science, Technology, Engineering and Maths (STEM) careers. Role models are an effective way of inspiring confidence to pursue a career path, but FDM Group highlighted that children and young people are more likely to identify with Bill Gates (Microsoft), Steve Jobs (Apple) and Mark Zuckerberg (Facebook) as technology role models than Baroness Lane-Fox, Sheryl Sandberg (CEO of Facebook) or Marissa Mayer (president and CEO of Yahoo).”

And:

“Despite long standing campaigns from Government and industry, however, there remains a marked gender imbalance in those studying computing-only 16% of computer science students at school are female (compared with 42% who studied ICT) and this low level of representation persists through higher education and in the workplace. A survey of more than 4,000 girls, young women, parents and teachers in 2015 showed that 60% of 12-year-old girls in the UK and Ireland thought that STEM subjects were too difficult to learn and nearly half thought that they were a better match for boys.”

Gender inequality as contributor to lost GDP

McKinsey back in April also conducted research into the actual monetary gains all US states can make should women attain full gender equality in the labour force.

Their report The power of parity: Advancing women's equality in the United States posits that collectively more than $4 trillion could be added to the US economy by 2025 if gender parity is fully realised.

Referring back to the Women in the Workplace data which sees the worst gender inequality in job roles further up the corporate ladder, it's notable that The power of parity report singled out inequality in leadership and managerial positions as one of six priority 'impact zones' for action to improve business opportunities for women and the economic benefits this will lead to.

So are things improving?

Women in the Workplace does show that CEOs seem increasingly keen to make their workplaces more equal and that things are moving in the right direction. But progress is slow at just one or two percentage points closer towards gender parity from 2015 to 2016.

That said, it is positive to see another report highlighting the issues of inequality in tech, as well as for providing evidence for the social and economic gains from better gender parity in business. But this latest McKinsey data shows there is still a lot of ground to be covered in the industry before the gap is closed and the benefits are realised.

13 time-saving Excel shortcuts & tips for marketers

Back in 2013 John Gagnon wrote a very popular post detailing some of his favourite Excel tips and tricks.

We thought we'd update the list of classic shortcuts with a few that reflect some new functionality in Excel 2016.

New Excel tips for 2016:

1. Navigate 'The Ribbon' with ALT

In Excel 2016 the ribbon refers to the menu of tabs (File, Home, etc.) at the top of your workbook. Simply hitting ALT will quickly highlight the related keys then used to jump to certain ribbon tabs.

For instance, ALT followed by M will take you to the 'Formulas' tab.

You can easily move between tabs by pressing ALT and using the left and right arrow keys, while CTRL + F1 toggles between hiding and showing the ribbon altogether.

2. Tell me what you want to do

Excel 2016 comes with a new helpful feature located in the ribbon, the 'Tell me what you want to do' search box.

Click the box, or if you are using Excel without a mouse hit ALT + Q to jump right to it. Whether it's adding rows or using VLOOKUP, the box is very useful for new and old Excel users alike.

3. New Excel Tip: smart lookup

If you want information from beyond the realms of Excel, another new function for 2016 is the 'Smart Lookup' tool which allows you to make a Bing-powered internet search without leaving the Excel pane.

Smart Lookup is located in the 'Review' tab and can also be accessed by ALT + R + S.

Classic Excel tips:

Courtesy of John Gagnon. The following tips are accurate and still work as of September 2016.

4. Automatically SUM() with ALT + =

Quickly add an entire column or row by clicking in the first empty cell in the column. Then enter ALT + '=' (equals key) to add up the numbers in every cell above.

5. Logic for number formatting keyboard shortcuts

At times keyboard shortcuts in Excel seem random, but there is logic behind them. Let's break an example down. To format a number as a currency the shortcut is CRTL + SHIFT + 4.

Both the SHIFT and 4 keys seem random, but they're intentionally used because SHIFT + 4 is the dollar sign ($). Therefore if we want to format as a currency, it's simply: CTRL + '$' (where the dollar sign is SHIFT + 4). The same is true for formatting a number as a percent.

6. Display formulas with CTRL + `

When you're troubleshooting misbehaving numbers first look at the formulas. Display the formula used in a cell by hitting just two keys: Ctrl + ` (known as the acute accent key) – this key is furthest to the left on the row with the number keys. When shifted it is the tilde (~).

7. Jump to the start or end of a column keyboard shortcut

You are thousands of rows deep into your data set and need to get to the first or last cell. Scrolling is OK but the quickest way is to use the keyboard shortcut CTRL + ↑ to jump to the top cell, or CTRL + ↓ to drop to the last cell before an empty cell.

When you combine this shortcut with the SHIFT key, you'll select a continuous block of cells from your original starting point.

8. Repeat a formula to multiple cells

Never type out the same formula over and over in new cells again. This trick populates all of the cells in a column with the same formula, but adjusts to use the data specific to each row.

Create the formula you need in the first cell. Then move your cursor to the lower right corner of that cell and, when it turns into a plus sign, double click to copy that formula into the rest of the cells in that column. Each cell in the column will show the results of the formula using the data in that row.

9. Add or delete columns keyboard shortcut

Managing columns and rows in your spreadsheet is an all-day task. Whether adding or deleting, you can save a little time when you use this keyboard shortcut. CTRL + '-' (minus key) will delete the column your cursor is in and CTRL + SHIFT + '=' (equal key) will add a new column. From an earlier tip, think about CTRL + '+' (plus sign).

10. Adjust width of one or multiple columns

It's easy to adjust a column to the width of its content and get rid of those useless ##### entries. Click on the column's header, move your cursor to the right side of the header and double click when it turns into a plus sign.

11. Copy a pattern of numbers or even dates

Another amazing feature built into Excel is its ability to recognize a pattern in your data, and allow you to automatically copy it to other cells.

Simply enter information in two rows which establish the pattern, highlight those rows and drag down for as many cells as you want to populate. This works with numbers, days of the week or months!

12. Tab between worksheets

Jumping from worksheet to worksheet doesn't mean you have to move your hand off the keyboard with this cool shortcut. To change to the next worksheet to the right enter CTRL + PGDN. And conversely change to the worksheet to the left by entering CTRL + PGUP.

13. Double click format painter shortcut

Format Painter is a great tool which lets you duplicate a format in other cells with no more effort than a mouse click. Many Excel users (Outlook, Word and PowerPoint too) use this handy feature, but did you know you can double-click Format Painter to copy the format into multiple cells? It's quite a time-saver.

How to take advantage of Facebook Video's creative opportunities

We are living in a video-first world, where moving images are at the heart of all apps and services.

Facebook recorded an increase of 800% in video consumption from 2015 to 2016, jumping from 1 billion views to 8 billion views in just a year.

During Social Media Week, Ian Crocombe, Regional Head of Facebook's Creative Shop presented all the opportunities that video content may offer, both for publishers and advertisers on Facebook.

Moving from TV to social media

Video ads on Facebook are different from the ones we knew on TV, and they also lead to a different behaviour from the audience.

Moreover, users have a shorter attention span when using their mobile devices, which also requires a different perspective when creating a video.

This creates the need for brands to adjust their content when they have to transfer their message from TV and traditional marketing to social media and Facebook in particular.

Adjusting content for Facebook videos

Facebook's Creative Shop aims to help brands understand what makes an effective Facebook video and how they can make their message heard to an increased audience.

They are responsible for “feed proofing”, which means the process of taking the brands' ideas and making them work on mobile.

It focuses on four main areas, all aiming to improve the success of the video – Capture, Design, Frame and Play…

1. Capture attention quickly

Facebook suggests you place the brand and the product at the centre of the video, keeping the message simple and clear from the very first seconds.

This informs the audience what the video is about, and if it's creative enough to grab their attention quickly, it makes them watch more of it.

2. Design for sound off

According to Facebook, 80% of videos are currently played without sound, which means that you cannot ignore this when creating a new branded video.

Instead of making customers turn the sound on, how about delivering your message through text?

Subtitles are better than nothing, but text overlays offer the best way to increase comprehension without a sound.

3. Frame your visual story

Cut extra footage from existing ads to reinforce the key message. Crop the video in square, pick the right frames and experiment with the best format that could be more appealing on Facebook.

4. Play more

Ian Crocombe's main tip is to “be really playful” with Facebook videos. Don't stop experimenting with new content, take a creative idea and play with it.

For example, how about starting your video with a question to spark curiosity?

Feed proofing is good, but it's quite defensive. There's also the need for a new creative approach when starting with mobile-first content.

It's time to work with new story arcs…

New creative approach 

A new creative approach needs its own tips to make sure that the audience is interested in the video content. Here's what Facebook suggests for an increased engagement.

1. Heartbeat frequency = attention

There's no need to have a theatrical narrative, following the traditional sequence of beginning, middle, end.

It may be more effective to deliver loops of the story every few seconds to keep your audience interested. A new vignette may start after a few seconds, creating multiple stories with a consistent message.

The duration of the content is not important, as long as you're able to keep the audience interested.

2. Zigs & Zags = hook

It has been observed that 65% people who watch 0.03 secs of Facebook videos can watch up to 0.10 secs and 45% who watch 0.10 secs can watch up to 0.30secs.

Zigs and zags try to move people across by hooking them at the beginning while getting the product visible. Once the hook occurs in the introduction, users can watch more of the video, with short and repeated stories contributing to the promotion of a brand's message.

3. Start with the end 

This tip is focused on “super logical people” with Facebook suggesting we start a video with the product shown in a visually appealing way, then end with the product's benefits.

This can be a clever way to capture the audience's attention by offering the emotional reward and the solution to the problem from the very first seconds while proceeding to the actual description of the problem through the rest of the video.

Takeaway

Ian Crocombe sums up his presentation by reminding us that “video on mobile is different”, and as we are heading into a more visually-focused world, it's time to experiment with more creative approaches.

This is an edited version of an article originally published in on our sister website ClickZ: New creative opportunites on Facebook for a video first world. 

Seven ways to future-proof your digital strategy

Nurcin Erdogan Loeffler, head of strategy and innovation, Vizeum China, outlines the seven ways businesses can future proof their digital strategies.

With more than two years in China and a background in strategy, Nurcin Erdogan Loeffler shared her thoughts on future proofing a business strategy with the audience at ClickZ Live Shanghai.

While the future for many of us is seen as something unpredictable and scary, the definition on Wikipedia is quite simple: “The Future” is the time after the present.

But what does that actually mean?

“When we look at the reality, especially in our industry, the future is now. We are already used to seeing science fiction as science fact,” says Erdogan Loeffler.

To understand the three stages of time, Erdogan Loeffler quoted from Steve Case's book: The Third Wave: An Entrepreneur's Vision of the Future.

The first wave: The first wave is the past. This was a period of infrastructure building to bring the technology to the people. Think Microsoft and Cisco.

The second wave: The second wave is the present. This is about the software. “It's about all those smart kids in their parents garages with a good idea that changed the world. It was about being agile, disruptive and empowering,” says Erdogan Loeffler.

The third wave: The third wave is the future which is already here. This will not be disruptive but constructive, says Erdogan Loeffler. “It will be different in terms of changing the world in terms of partnering up and growing further with existing partners.”

The futurist Gerd Leonhard, sums up how “change” itself will be disrupted in the future in this short video.

Digital transformation: are you ready for exponential change? Futurist Gerd Leonhard, TFAStudios

Here are Erdogan Loeffler's seven tips for future proofing your business strategy.

1. Don't have a digital strategy

It is very important at this moment in time where we are speaking about where we are in the future, that having a digital strategy doesn't make sense, says Erdogan Loeffler.

Having a business strategy that is built for the digital world on the other hand, is the future.

At the World Economic Forum in 2015, Eric Schmidt, executive chairman of Google (now Alphabet), caused a stir when he said the Internet would disappear.

“What he really meant is the Internet will become like air. The Internet will become everywhere. Today, when we talk about digital, we see it as medium, as a part of our marketing mix. But tomorrow, CEOs will become the head of digital… or vice versa,” says Erdogan Loeffler.

Once we start to accept that digital will be everywhere we will start to understand why it is not relevant to have a digital strategy.

“Having a digital strategy will be as absurd as having an electricity-enabled fridge. The Internet of things is almost over. The Internet of everywhere will become a reality very soon,” she adds.

2. People and their needs FIRST

Another of Erdogan Loeffler's favorite writers is James Harris and his book, Think More Analogue, Be More Digital. In it, Harris underlines five global human needs that drive all digital behavior.

These are:

• Distraction


• Learning


• Recognition


• Relationships


• Progression

To confirm whether China fits into this global stereotype, Vizeum looked at annual Chinese consumer research involving the surveying of 70,000 people across the country. And the China results are not so dissimilar to global digital behavior.

The top three reasons for spending time online for Chinese consumers are:

• They want to be kept updated with trends (64.4%)


• It's a good source of learning (61%)


• It puts me in a good mood (60%)

*Source: Vizeum / CCS 2015

The big difference however is that the sophisticated Chinese consumer has one key motivation – to stand out.

• Almost 50% of Chinese adults what to stand out as an individual.


• Almost 50% like to buy products that stand out as being different.


• 80% of millennials believe that to be successful it is worth expressing my true opinion in front of others.

“What is important for future proofing your business strategy, is that behind every device is a human. They don't really remember what you are saying or what you are doing. They only remember how you make them feel. So emotional connections will always mean a better return on investment,” says Erdogan Loeffler.

3. Turn data into actionable insights

Data is like a jungle – it's dangerous and it's so big you can get lost in it, says Erdogan Loeffler.

“And unless you know your route and which animal you want to hunt, you have to be very careful or data can hunt you.”

Data is only meaningful when it can really be used and leveraged as an actionable human insight, she says.

Here's a case study of how Vizeum worked with Marina Bay Sands to close the loop within the digital network.

Marina Bay Sands / Vizeum APAC

4. Content is the king

In 1996, Bill Gates first used the slogan, “Content is King” and it's a strategy still being used today – 20 years on.

Erdogan Loeffler says the global rule for creating content that works is content that:

• Lets you love


• Makes you cry


• Stimulates some desire

*Source: Vizeum

Here's how SK-II's emotional “left over women” campaign brought many viewers in China to tears.

Good content, inspired by good insight always works, she says.

5. Context is the queen

Every king needs a queen.

“It's not enough to have great content – it needs to deliver the right message to the right person, in the right medium at the right place and the right time – and this is what we call context marketing,” says Erdogan Loeffler.

With the average attention span of a person now at about three seconds, advertisers have a small window to 'catch' the consumer. Therefore, it's very important to have the right content mixed and married with the right context, she says.

Here's how Vizeum worked with BBC Earth to launch in Asia, using behavioral and contextual data.

BBC Earth / Vizeum APAC

6. Watch your body language

A large part of human interaction is non-verbal – 60%. And it's no different for brand marketing, says Erdogan Loeffler.

“As brands we are saying we are amazing but behind that are you lying? Through social media the consumer can really understand a brand's body language.”

She cites KLM's brilliant use of 'body language' through its digital customer service channels.

“KLM is openly talking. It creates a profile beyond the brand, being helpful, being reactive. Ask them a question and within one hour they will reply.”

(Read a case study on KLM's use of WeChat for customer service in China here.)

7. Close the loop (or somebody else will close it for you)

In China, ecommerce, social commerce, m-commerce and brand commerce are well established.

Path to purchase used to be quite simple whereas now it's quite complicated, says Erdogan Loeffler. Gone are the days of a direct route, today's marketer never knows when the transaction will happen so it's important that every interaction has the potential to close the loop.

Concluding her presentation, Erdogan Loeffler left the audience with one final piece of advice.

“I believe all of theses points are very important but one key takeaway is to remember that people are not cookies or devices or audiences or consumers. People are people.”

Why You Need Mentors

You cannot know everything there is to know about how to get where you want to be in life. While it's possible to learn the needed skills, this will take time and likely a lot of money. Instead, what if you had someone (or a group of people) who can help guide you to success?

What Is A Mentor?

A mentor is someone who currently is where you want to be. The relationship you have with a mentor can be an official one, or it can be informal such as following in the footsteps of someone you admire. Mentors have experience and have gone through the growing pains towards success. They will know what kinds of training you may need or skills to develop to take you to the next level in your life.

More importantly, mentors can give you needed feedback on what you are doing right and wrong during your development. This kind of advice is invaluable. You don't want a mentor who will hold back. Make sure you give them full permission to be completely honest with you. Most worthwhile mentors will have no problems doing this for you. It may be a hard pill to swallow, but you will be better off than having mentors who sugarcoat your progress. That won't help you in any way.

The mentors you choose should have the experience or skills you desire to learn. Be sure to ask lots of questions about what they expect from you and ask what you can expect from them. This expectation is important. Otherwise, you won't have any means of knowing whether the mentoring relationship is working. If someone you are considering to mentor you takes issue with you asking these questions, this is a sign they may not be right for you. It can also be more difficult to sever the relationship once it has begun.

Should you pay for mentors?

It depends on what you currently have available at your disposal and what you intend to receive from this kind of arrangement. If, as previously mentioned, there is someone who can act in a mentor capacity and they are willing to do this for free, then consider doing it. On the other hand, paying for someone to help you lets you hold them accountable. This is a crucial aspect of mentoring. Without it, you could be spinning your wheels, going nowhere. You don't want to waste time as the entire reason to take on a mentor is to save time in getting you further in your development.

 

Was this helpful? If so I would appreciate if you could comment below and feel free to share it with anyone who may benefit from it. 

**Are you ready to cut your learning curve and start attracting leads to you? Feel free to join our team here www.joinmarvin.com

 

Marvin Sykes

The post Why You Need Mentors appeared first on M.A.S. Marketing Team.

Six most important search marketing news stories of the week

Welcome to our weekly round-up of all the latest news and research from around the world of search marketing and beyond.

This week we have Google unleashing Penguin 4.0, AMP coming to organic mobile SERPs, Twitter making some changes for the better and the introduction of Google's own personal messenger service, Allo.

Penguin 4.0 is finally rolled out

As I reported earlier today, Google has finally confirmed that its Penguin algorithm update is now rolling out in all languages.

The spammy-link killing update will feature the following improvements…

Penguin is now real-time

Penguin data is refreshed in real time, so any changes will be made as soon as the affected page has been recrawled and reindexed.

Previously affected webmasters would have to wait for the next update before any penalties could be cleared.

Penguin is now more granular

Penguin now devalues spam by adjusting ranking based on spam signals, rather than affecting ranking of the whole site.

This means the specific page will be penalised, rather than the entire website.

Google AMP is coming to organic search results by the end of the year – confirmed

Google's Rudy Galfi, Google's lead product manager for AMP, revealed this week that the global rollout of AMP in mobile organic search would be complete by the end of the year.

In further remarks posted on Search Engine Land, Galfi clarified that AMP still isn't a ranking signal. And although Galfi wouldn't share specific user-response data, he did state, “We're seeing great user response.” He also said that Google has been “humbled” by the industry's adoption of AMP.

The Coalition for Better Ads to make digital ads great again

As Al Roberts reported this week, a new organization called The Coalition for Better Ads, has been launched to “leverage consumer insights and cross-industry expertise to develop and implement new global standards for online advertising that address consumer expectations.”

This basically means that the coalition will identify new standards that can improve the digital ad experience, implement the technologies needed for these standards, and promote the standards so that they're put into use.

Worth switching your adblocker off for? We'll see.

Twitter makes a few changes – good ones too!

Although Twitter has threatened various unpopular changes over the last 12 months – introducing verification for non-celebrities (celebrities hate this, and by celebrities I mean, Pewdiepie), killing share counts, a 10,000 character increase – here's one that we've been crying out for…

Actually, why tell you via this article, when I can show in embedded tweet form.

Very Important News everyone… You can now ramble for an entire 140 characters in tweets and still have room for this sweet Gif. What a day pic.twitter.com/QxDy1jOld5

- christopher ratcliff (@Christophe_Rock) September 20, 2016

Same goes for images too. Annoyingly links and @mentions still take up characters though.

Still, we got our sweet gifs though, huh gang?

Google launches its 'private' messenger service Allo

Because all the kids are leaving social in droves because they like private messenger apps and not being spied on, Google has realised it wants a piece of that action.

So say allo to Allo – another channel to be pinged on by one or two early adopters while desperately trying to keep up with your WhatsApps, Facebook Messages, texts, emails and all the notices on the office fridge to stop eating all the humous.

Yeah. Let's escape to the mountains together. Come on, I'll pack some sandwiches, you bring a torch.

Here's what Allo will look like…

Cute, although a few sloth emojis won't persuade Edward Snowden to use it anytime soon.

Google will continue to display Quality Score data until October 10

As reported by Matt Southern on Search Engine Journal, Google claimed it would be returning null quality scores for new and underperforming keywords in AdWords as of September 12th.

However as Matt points out, if you've logged in to your AdWords account lately you may have noticed you still have quality score information for new and underperforming keywords.

This is because Google has pushed the date back to October 10th, in order to “give advertisers more time to review their reports, filters, rules, and scripts that rely on Quality Scores, null Quality Scores will now roll out the week of October 10th, 2016.”

 

Lead generation programs | Get more lead For Your network marketing business

Lead generation programs | Get more lead For Your network marketing business

via https://www.youtube.com/channel/UCtEc-WTq7uPoVuEFP1FEY-Q

CTR as a ranking factor: what we know so far and how to action it

For the last two years there has been much discussion and debate around whether click through rate (CTR) is something Google uses to determine where to rank a site.

It all began way back when Rand Fishkin did an interesting experiment which provided some evidence behind his theory that CTR is in fact something that could influence rankings.

This theory makes sense; why wouldn't Google be looking at user based signals to improve search results?

Just as we do user testing on our websites to increase the number of conversions, Google will surely want to be looking at user signals to ensure they are fulfilling their goal of 'organising the world's information' in the correct order in SERPs.

Why CTR is not a ranking factor, from Google

However, many times when Google, and more specifically Gary Illyses, has been questioned on this we have received the response of the opposite. That Google does not use CTR as a ranking factor due to it being a 'noisy signal' and something that could be gamed by spammers.

See this response from Gary Illyses in an interesting interview with Kwasi Studios:

We also received the same response from Gary when he mentioned the topic in June 2015 at SMX Advanced.

At the event Gary did, however, say CTR is being used for personalisation, meaning search results change if you frequently click on the same site. This can be especially useful for ambiguous terms.

For example, if a user searched for 'Jaguars' a 'Query Deserves Diversity' (QDD) algorithm will take effect and display results for the car brand, the Jacksonville Jaguars as well as the animal.

If the user then clicks on the car brand and does that frequently, Google will be more likely to show the Jaguar car brand due to the high CTR for the Jaguar site. Other than that, Gary says click data is not being used.

Why CTR is an indirect ranking factor, from Google

Due to this negative feedback received from Google around the topic, some have dismissed CTR as something that Google does not make use of.

However, conversely we have had other statements from Google that specify they do in fact use CTR in some circumstances.

The primary evidence we have from Google that CTR is being used for more than just personalisation is in this excellent presentation by Paul Haahr titled 'How Google Works: A Ranking Engineer's Perspective'.

If you have not watched the presentation yet, I highly recommend it! You can find a video of it here.

From this video, we can gather Google is using click through rate, but not to adjust rankings directly. They are instead using it indirectly in controlled situations to validate the quality of search results.

They are also using it to verify that an algorithm change has the desired results. 'Controlled situations' means they are taking a portion of search results, testing changes and using CTR as a metric to measure if the changes had the desired impact on improving user engagement.

Following what Google has said regarding CTR, this means that it is not being used in a way to directly change search results. It is instead being used to test whether changes to direct ranking signals such as content and links are improving user engagement and the quality of search results.

This is explained in this slide on Paul Haahr's presentation:

Other ways Google is measuring the quality of search results

As explained in the presentation, CTR is only one of the ways Google is testing search results are showing up in the correct order.

They are also taking a more manual approach with human rater experiments. This is where Google will show an actual person a search result that has an experimental algorithm change on it, and they get them to score the page based on whether the needs have been met (does the page fulfil user intent), and the quality of the page.

What this means is gaming the system explained here by artificially increasing CTR becomes virtually impossible, as along with live experiments, there is a manual review of pages before algorithm changes take place.

I am sure CTR and human rater experiments are only some of the ways Google will be testing algorithm changes.

While this was never mentioned as a user engagement metric in the presentation, there is a high chance they are also paying attention to pogo sticking and dwell time. If this is true, it makes metrics such as average time on site and bounce rate important things to consider.

Here is a bit of data sourced from our in-house 'Roadmap' technology that looks at over 160 potential ranking factors and shows correlations in search performance.

And yes, I know correlation does not mean causation, but it can give us a good indication what Google is taking into consideration when ranking sites. Here is how average time on site correlates:

Source: Stickyeyes Roadmap

Of course, this does not mean Google is directly creating algorithms that change rankings based on things such as average time on site.

It does, however, inform us that Google is favouring sites that searchers stay on.

This metric could be used in a similar way to how Paul Haahr explains Google uses CTR, where they will adjust traditional signals such as content and links depending on whether the user stays on the site or not. Again, this is all speculation with a bit of data to back it up.

Why CTR Is a direct ranking factor (sometimes), from Rand

Despite what we know is 100% correct, as Google has confirmed it, we still have Rand Fishkin's CTR experiments that say Google is not telling us everything they use click data for.

This is where we have to speculate based on the information the SEO community has managed to get out of Google.

Thankfully some insight into this was given by Googler Andrey Lipattsev in a Q&A show back in March 2016 when Rand questioned him on it. Here is Andrey's response to Rand asking why exactly that happened:

“Andrey: It's hard to judge immediately, without actually looking at the data in front of me. But, in my opinion, my best guess here would be the general interest that you generate around that subject, and you generate exactly the sort of signals that we are looking out for, mentions and links and social tweets and social mentions, which are basically more links to the page, more mentions of this context. I suppose it throws us off for a while until we're able to establish that none of that is relevant to the user intent, I suppose.”

From Andrey's response, I believe he is explaining part of Google's algorithm that is a temporary ranking factor that identifies hot topics or searches and then adjusts rankings accordingly depending on social signals, and possibly user behaviour.

We already know Google tries to push rankings up for fresh content, and this algorithm seems similar to the freshness algorithm (announced way back in 2011).

When this ranking change occurs, it is more than likely that Google applies the technology they have to combat click fraud in Adwords. I believe this experiment that came to the result the CTR was not a ranking factor by using bot traffic backs this up.

What we know – summarised

From all of the above we can summarise what we know into the following things:

1. Firstly, CTR does seem to have an immediate and temporary impact on rankings in some cases. This is more than likely due to an algorithm that aims to spot trending content and hot topics, and then adjusts rankings depending on which page is the most popular and seems to be fulfilling user intent. Again, this ranking change is temporary, and I imagine will be tough to try and game. It worked so well when Rand ran his experiment because he did it with actual people, not bots. In this instance, CTR is a temporary direct ranking factor.


2. Secondly, Google is using CTR in live experiments to quality-check their results. This is not being done across all search results but in a controlled manner across a portion of different SERPs. Based upon the results, Google will then adjust other algorithms to do with links, content, etc. In this case, CTR is an indirect ranking factor.


3. Thirdly, Google is using CTR to personalise results for individual users.

How to improve click-through rate

It is evident user engagement and sending users to the correct website is important to Google, because of this we also know optimising CTR is something we should be doing.

The rest of the post will be about how to identifying click through rate opportunities, and then how to improve it.

Finding low CTR terms

The only reliable data we have on CTR comes from Google itself in the Search Console search analytics report.

To help with identifying these terms I have made this CTR Opportunity Analysis template Google sheet. This sheet finds out what the average CTR is for keywords ranking position 1 – 100 from a GSC export, and then compares each keyword against your site's average CTR for that position.

This allows you to identify if there is a significant opportunity to improve click-through for a particular query. You can see whether there is an opportunity as it will tell you in the 'Optimise CTR' column.

If you rank higher than position ten it will simply say 'Improve Rankings'. If you have your own data on average CTR for it each position, there is a hidden sheet called 'CTR Ref' where you can fill this information in. You could also replace it with data from this Moz CTR study.

To find queries that that have a low click through rate do the following:

1. Navigate to the search analytics report in GSC.


2. Add the below filters (change country depending on your location, you may also want to add a device filter if desktop or mobile is your focus):

3. Download the results as a CSV (bottom left of the page). Then open up the CSV and format the position column so it has 0 decimal places (this will make more sense after the next step).


4. Make a copy of this spreadsheet by going to File > Make A Copy.


5. Paste the corresponding columns from the Google Search Console CSV into the template. At this point, you can scrape Google for each keywords position instead of relying on GSC's average position if you want to.

6. Once you have this information set out like the above you can then filter the 'Optimise CTR' column to only show results that return a yes.


7. Next, you can sort from high to low on impressions. The high-impression keywords that also return low scores on the 'CTR Difference from Average' are your top opportunities for optimising CTR.


8. Copy one of these keywords, and head back to Google Search Console. In the queries filter add a filter for that keyword exactly.

9. After that select 'Pages' in the filter bar at the top to show pages ranking for that keyword.


10. The page listed first should be the page you need to optimise.

Top ways to optimise for CTR

By now, we have the keywords and pages that we need to optimise for. We just need to go through the process of actually improving our pages. Here are some different things you can do to improve CTR:

• Adjust title tags to contain the words or phrases you are getting a low CTR on. You can improve this further by combining your SEO focused keywords with other call to actions that will entice users to click your listing. Another thing you can do is compare your listing to what is currently in the titles for Adwords listings. What is in these titles has usually been optimised and tweaked heavily to improve CTR, because of this taking inspiration from them is a good idea.


• Optimise meta descriptions to include terms you are trying to improve CTR for. Including the term means they will be bolded in search results which will increase the chance of them being clicks. It is best practice to optimise each description individually to increase the chance users will click it, so try to avoid generating these based off of a couple of variables if possible. Again, for meta descriptions you can also look towards Adwords listings for inspiration.


• Optimise URLs to include target keywords. Similar to meta descriptions including target terms in URLs means they will be bolded.


• Add relevant schema markup to the page to generate rich snippets on your listing. This can have a pretty dramatic effect on CTR, especially for schema markup that substantially enhances your listing such as review and product markup.

Once you have done any or all of the above, check how organic traffic changes. Then go back and retest something different. You can rinse and repeat this process until you are seeing diminishing returns from improving CTR for a page.

Final words

Overall we can say that CTR is something that is being used by Google one way or another. Rand seems to have uncovered ways in which it is used directly and temporarily to modify search results.

Paul Haahr from Google has informed us it is used indirectly to measure the quality of search results. Because of the above, and because it can simply increase organic traffic, CTR is something you should be taking into consideration when optimising your site.

Along with focusing more on click through rate, SEOs need to become more aware that while getting a site technically fit for purpose, writing great content, and building quality links is important.

We should also spend more time thinking about user engagement and UX, as they are both becoming a more and more important aspect to consider when trying to perform well in search results.

Let me know how the CTR Opportunity Analysis template works for you, and any thoughts you have on CTR or user engagement as a ranking factor in the comments!

Sam Underwood is a Search and Data Executive at Zazzle Media and a contributor to SEW.