Pimp Up Your SEO Reports with Import & Merge

Posted on: February 24th, 2015 by Patrick Hathaway in Guides

One of the least obvious updates in version 1.50 was the introduction of the function: ‘Import CSV & Merge’. This will allow you to populate the URL list with data from an existing CSV file, without you having to manually delete columns or copy URLs to clipboard.

Lots of our customers like our option to import Screaming Frog files, and our new feature is simply the logical extension of this.

To use it, right click in the big white box titled ‘URL List’ and select ‘Import from CSV and Merge data’

There are 2 requirements for this to work properly:

There must be a column in your CSV file with the heading URL, which contains your URLs (doesn’t need to be the first column)
The URLs must include http (or https)

Then, run your profile as normal and URL Profiler will append the data to the end of the original dataset.

Append URL Profiler Data to Ranking Results

This is easier to understand with an example. Imagine we tracked our rankings and had a spreadsheet that looked like this:

We could import and merge this CSV, and add in data like social shares, links, Majestic CF and TF, Google Analytics data – the works.

But the best thing about this feature is the data is appended to the original – which is a fancy term for ‘shoved at the end’.

After a bit of tidying in Excel, this is what the output might look like (click to view large image):

You can see the original data we had remains on the left, with the new link and social data shoved at the end on the right. Sorry – ‘appended’.

Basically, this allows you to combine data sets without faffing around in Excel using VLOOKUP (you can throw VLOOKUP in the bin actually, you won’t be needing that anymore).

Usage Ideas

This new feature opens out URL Profiler to combine with a host of other tools and datasets, and makes the software even more useful. I’ll run through a few example ideas you could play around with.

Buzzsumo

The latest darling of content marketing, Buzzsumo is an awesome tool for discovering highly shared content. However, you might want to combine that social data with link metrics to get a more complete picture of the content you are looking at.

This is perfect for the Import & Merge feature, as you can export their results, import them into URL Profiler and add in the link metrics you desire.

In order to export Buzzsumo results, you need a Pro account (they have a free 14 day trial so you can test it out).

We’ll work through an example here, searching their ‘Top Content’ database for a topic we’ve written about here a fair bit: ‘scraping Twitter.’

The results come in, and we can hit the ‘Export’ button alongside the search bar to get the results in CSV format.

Import and merge this export file, then you could select some link metrics such as Majestic, Moz or Ahrefs (fuck it, why not all 3?).

Then you’ll get back a nice spreadsheet with all their social stuff in the first 21 columns, then all the URL Profiler link metric goodness shoved on the end.

SEMrush

I know what you’re thinking – we already have an SEMrush integration built into URL Profiler. But that will only collect domain level data for you, and not really scratch the surface of what you can do with it on a URL level.

Here is as simple use case on the URL level. Let’s say we’re working with a client selling gadgets online – they might class Firebox as a competitor.

We can pop their URL into SEMrush, then click Organic Research -> Positions (on the left) to see all their top ranking pages.

Then we simply click the export button (top right) and select to export the data as a CSV, which we can then bring into URL Profiler using the import & merge function.

We want to get some additional data about these URLs, so we’ll check Majestic, Social Shares and Screen Capture.

Note: I added in my Majestic paid API key to move from [Free] to [Paid] because I was in a rush – it takes literally 3 seconds to get data for 1800 URLs from the lightning fast paid API.

This is a sample of the (sanitised) results; SEMrush data on the left, with Majestic data shoved on the end:

Notice one important feature of the results – they retain repeated URLs even though any duplicate URLs were deduped by URL Profiler on the import (in the screenshot of the tool above you won’t see any duplicates in the URL List).

So all the original structure is retained – 7281 rows in, 7281 row out.

No data is lost in the process, and no VLOOKUPs are required to piece it all back together (See, I told ya – chuck it in the bin).

SERPWoo

If you’ve not noticed the trend already, any tool that offers a CSV export can basically be imported into URL Profiler and pimped up with different metrics.

If your favourite SEO tool provider doesn’t offer an export, then maybe you can get them to do something about it.

@SERPWoo feature request – export (checked) ranked URLs on the focus date to a CSV. Easy to then take to @urlprofiler and get all metrics

— Ian Howells (@ianhowells) December 5, 2014

BTW – props to the guys at SERPWoo, who rolled this feature out only a couple of weeks later and replied straight back to Ian on Twitter. Sublime service.

Same deal, go to your keyword area and click to export to CSV, then import & merge in URL Profiler and add whatever metrics you want.

Merge & Conquer

If you can get data into CSV format, you can use this new feature for all sorts of interesting cases. It doesn’t even need to be SEO related – I used to work for a company that had a massive database of customers, where they had logged customer websites but not any social accounts.

Getting this data with URL Profiler would be trivially easy: export customer data from CRM, import and merge in URL Profiler, tick ‘Social Accounts’ under the domain level data, run the profile, import results back into CRM.

But we don’t want to talk about boring old CRM systems.

I’m sure that you, lovely customer, can think of far more interesting ways to use this feature – so get going!