Archive by Oliver Wheaton

Index Match Match – an Alternative to Vlookup for SEO Analysis

20Nov

You would be hard-pressed to find an SEO specialist that has never used VLOOKUP. Nowadays we have access to a massive amount of data from various tools, and the most powerful SEO analysis can be achieved when we connect all these data points, which often means looking up values from different sources.

In this post, we want to give you examples on how and when you can use INDEX MATCH and its more powerful version INDEX MATCH MATCH to replace VLOOKUP.

VLOOKUP is a great formula, but it does have its limits. The main one being that it will only work when it’s pulling data in a left to right fashion. This is where INDEX MATCH comes in. This formula has the freedom to pull from right to left as well as left to right.

An important thing to note is that by switching the columns around in the master sheet we can complete this task with VLOOKUP, however, doing this may break other formulas tied to those cells. Plus, it’s always easier to work with the original output format of a document, enabling you to paste new values into the document without breaking the formula.

Here’s an example of how we’ve used it recently. We were given a list of product numbers, with which we needed to find the associated keyword.

This was the spreadsheet we had to work with in order to find the data we were looking for, and as you can see it contains both the Product No. and the Keywords we are looking for. A VLOOKUP will not work here as we have a list of product numbers and need to find the keywords associated (which are further left than the product numbers).

First things first, we created a column for the Product Numbers which we have on hand. Then we go about setting up the formula. The formula should be placed in the first empty cell in the “Keyword” column of the second table in the image below (cell C14).

The formula is as follows:

=INDEX(C3:C11,MATCH(B14,E3:E11,0)).

INDEX (Range of cells that you want to find values from),

MATCH (Cell you want to find the corresponding value ofrange of cells from which the previous cell value is from, 0)

Once the formula has been entered, you can then drag it downwards to the bottom of the table, allowing you to put in your Product No. values and have the formula pull the keyword automatically.

Now that we know why and when we should use INDEX MATCH instead of VLOOKUP, we can look at a more powerful Excel lookup formula combination which is INDEX MATCH MATCH.

How can it be useful for SEO?

“Internal – All – Export” – a regular Screaming Frog user can easily recognise these steps for exporting crawl data. We all know what a great tool it is and cannot imagine our work without it. But sometimes we wish it could only give us the data we need depending on what metrics we want to look up. And this where INDEX MATCH MATCH can come very handy.

The number of columns in the exported spreadsheet from the Internal tab can vary depending on the number of that were found on the site (rarely, but sometimes you can see sites with two titles added to their pages, or even two canonical tags), and if any additional data is included or not (e.g.: Google Search Console and Google Analytics).

INDEX MATCH MATCH allows to perform two-ways look-up – a vertical one (to find certain URLs among all URLs from the very left “Address” column of the crawl) and a horizontal one (to match data from certain columns in your Screaming Frog crawl for these URLs).

An example will help us to see how it actually works.

The table below represents a Screaming Frog crawl. In this example we want to look up the values from the “Meta Robots” and “Canonical Link Element” columns for URLs 4, 7 and 9 (all marked in orange).

To do so we are using INDEX MATCH MATCH as shown below:

The result is as expected will be the meta robots and canonical URLs values for the three URLs:

So, how do we actually get to this result using INDEX MATCH MATCH formula combination?

Below you will find the same images as above but with a coloured markup that should help further explain the formula. However, for clarity, let us review it step-by-step.

We added the above formula to the row C16 as per image below (or add it next to the first URL you would want to look up the metric values for):

  1. INDEX (mark up for your entire crawl),
  2. MATCH (lookup value – your first URL to look up, the column we want to look up against, 0),
  3. MATCH (lookup value – your first metric to look up, rows we want to look up against, 0))

As you can see, we are using $ sign in our INDEX MATCH MATCH formula combination, which allows us to lock columns and rows. When added to the formula, as shown above, you can easily drag it down to populate the lookup values for the remaining URLs.

We hope you will give INDEX MATCH MATCH a go in your next SEO analysis if you haven’t already done so. Here at Passion Digital we love to share knowledge and learn from our peers, so if you have any other use cases when these formula combinations can be applied for everyday SEO analysis, feel free to get in touch and let us know about them!

What we’ve learnt in the world of digital in 2018

06Nov

What we learnt in digital 2018

One of the core principles here at Passion Digital is that we should never stop learning, as an agency or as individuals. It’s part of the reason that we launched Digital Kitchen, our digital marketing training service.

We also make sure every staff member at Passion Digital has the ability to attend seminars, courses and conferences in order to ensure their knowledge and personal development never falters. To put it simply – we love to learn. While there will always be something else we can swot up on, here are just some of the incredible things we didn’t know at the start of 2018, but we are lucky enough to understand and  in our professional lives now.

PPC – AI and automation

In 2018 we’ve learnt that AI and automation within PPC is needed more than ever. With a platform like Google Ads there is access to so much data, meaning more complexities, more considerations and more outcomes.

As an agency we were initially hesitant of using tools such as Search Ads 360 (formerly DoubleClick Search), but we quickly realised the value in these tools. They can reduce the margin of error and allow our team to focus more on the strategic side of PPC marketing and ensure we are delivering our clients good results.

SEO – Log file analysis

This year we learnt the importance of log file analysis, a very technical side of the work we do from an SEO point of view.

Every request made to your hosting web server for content is being recorded in a log file. The analysis of these log files is important because you can see exactly what resources search engines like Google are crawling on your site. Analysing these files will help to understand how much “crawl budget” is being wasted.

What is Crawl Budget and Wasted Crawl Budget?

  • Crawl budget refers to the number of pages a search engine will crawl each time it visits your site.
  • Crawl budget or crawl allocation is determined based on the authority of the domain, the link equity through the site and number of pages. The higher the authority, the more URLs crawled.
  • Letting search engine bots crawl thousands of irrelevant, content-thin URLs leads to Wasted Crawl Budget –as a consequence, the requests from these bots that could had been spent on the pages we want to be noticed, crawled and indexed by search engines, are spent on pages that either have no SEO potential or are not important for the business.

Recently we worked with a client that had thousands of pages on their site, and the insights we found from the log file analysis were quite daunting:

  • Only 7% of all crawled URLs were returning clean 200 status codes ad being properly indexed and bringing SEO value.
  • The rest of the total crawl budget was spent on crawling URLs that were eventually resolving in JavaScript Redirects, were canonicalized, noindexed or plainly wrong.

As a result, we have been recommending changes to improve the way search engines crawl the site, and as soon as these change are implemented we’ll have a lovely case study to show!

Content marketing – Ask journalists what they want

This year we’ve been thinking a lot about how to find out exactly what kind of content journalists and influencers want to cover and share. It may not exactly be a ‘new’ technique, but we’ve recently discovered the power of reaching out to journalists before starting a content project, and giving them a rough pitch of the idea.

This has many benefits. Firstly, the journalist will see this as an opportunity to get some original content before his or her competitors, which they won’t have to do any research for. With this in mind they are also likely to suggest edits or changes that should be made to the plan in order to achieve the best results and make it more likely to win coverage and backlinks.

Secondly, if you start a dialogue with a journalist at a larger publication (something with a wide readership and healthy Domain Authority) and create a bit of content that they assure you they will cover, a huge amount of your outreach is already done for you. Other journalists will see this content and approach you about covering it.

Thirdly, becoming known to journalists or influencers as a source of good content – as well as being known as willing to collaborate on creating decent content – is invaluable. If you are able to establish this relationship with journalists then they will always answer your messages and read your press releases. They might even contact you about the type of content they are looking for in the near future, influencing your content strategy.

This approach may seem daunting, and you are certain to be rebuffed by many journalists and influencers you approach, however you only need one to turn your next content project into a success.

Social – Remarketing for Facebook Lead Generation

A popular objective for paid social, lead generation forms allow a user to register their interest natively within the platform they are using.

However one of the largest issues we notice with Lead Generation campaigns on channels such as Facebook or LinkedIn is that a lot of the time advertisers will use the lead generation forms as the first advert a user will see from the brand. Put it in real-life context, if someone came up to you with a form asking you for your data, you’d most likely pretend to be getting a phone call and run off. It’s just not realistic to expect anyone to give you their details when they’ve only just found out about your brand and service.

One of the most effective methods we’ve seen at getting higher completion rates is by taking a simple funnel approach and taking into account the user’s journey from start to finish. A basic way of breaking this down is in two stages, but could be widened out even further to improve results and make sure you’re getting the most bang for your buck.

1. Website traffic and building audience sizes.

By sending people to your site, you’re allowing the user to do their own research, giving them time to understand what you offer and whether or not they’re interested.

Once you begin to gather traffic to your site and see longer sessions taking place, you’re ready to start remarketing.

2. Custom audiences

Custom audiences within Facebook are essentially remarketing lists, but you get a number of segmentation options open to you to hone and improve the likelihood of compiling the best audiences for your next step of marketing.

There are a number of options available, but for starters we create audiences for the top 25% of website visitors of the past 30 days, all the way down to 1, 3, 7, 14 days so you have all the options available to you.

3. Lead generation

Once you have these audiences created with your Facebook Ads Manager, you’re ready to start remarketing to them.

People would have previously seen your brand and checked the website, viewing a number of pieces of content, therefore they are much more likely to convert and register their interest.

If you want to take your career in digital to the next level, you can learn from experts in the industry on a Digital Kitchen course. Find a course that works for you today.

We're recognised by
02034321369
Digital Marketing Agency London +Mike Grindy