Ahrefs- Mass Export Backlinks of URL List

#5

Yeah I can figure that out in excel, but how would I pull the referring domains for a list of 20k URLs?

#6

I figured out how I pull the referring domains for one URL, either by copy pasting the URL into the "URL" field, or by selecting a single cell in excel that contains an URL. Selecting a range of cells that contain URLs didn't work.

I still need help with that, how would I do that?

#7

See my example blow and use the same settings:

Then change the "https://seotoolsforexcel.com" part of the formula to A2 (or whatever the first URL is in your list) and expand the formula down.

#8

=Dump(Connector("Ahrefs.RefDomains";"https://seotoolsforexcel.com";"exact";;;"refdomain";"asc";RefDomains";TRUE;20);TRUE)

Throws an error "There is a problem with this formula."

#9

Did you create the formula from clicking Insert using "Formula mode"?

#10

I selected "Formula" on the right, then selected B2 and pasted this in the cell:

=Dump(Connector("Ahrefs.RefDomains";"https://seotoolsforexcel.com";"exact";;;"refdomain";"asc";RefDomains";TRUE;20);TRUE)

Is that what you meant?

#11

You should not paste anything, just click the Insert button after selecting formula. The formula should be generated for you.

#12

Oh okay I understand. I did that now and now the cell appears to empty but when I click on it, it shows the following at the top:

=Dump(Connector("Ahrefs.RefDomains","A2","exact",,"refdomain","asc",20,0,"RefDomains",TRUE),TRUE)

No referring domains tho.

#13

Almost there, just got to remove the quotes around A2 :slight_smile:

1 Like
#14

I didn't put the quotes around A2, I just put A2 in the "URL:" field on the left. However, now I removed the quotes around A2 in the field on the top and it put out the Referring Domains perfectly for A2!

Now all I need is this for the whole A column. Thanks for all the help so far!

#15

Just expand the formula to execute the formula on all URLs in your list

1 Like
#16

I did it *and it worked! Sorry, I know this is excel support by now and not directly related to the tool. Thanks!

Two more questions:

  1. Is there another way to expand the formula besides dragging it down? Wouldn't want to drag over 20k cells.

  2. Is there documentation for the "Where:" field of the tool? I'd like to paste in a number of domains that shouldn't be returned. For example I'd add "example1.com" to the "Where:" field and if one of the Referring Domains is "example1.com" it will be ignored and not returned.

EDIT: I found this " !substring(url_from,"seotools")" but it throws an error when I paste it into the "Where:" field saying a column with this name couldn't be found.

#17
  1. You can double click on the corner of the formula cell which will expand the formula down to the last row with data in adjacent rows.

  2. Try to add this string to the Where field and insert an updated formula. I suspect that the quotes are the issue here. May need to use double quotes:
    !substring(url_from,""seotools"")

#18
  1. Worked perfectly, thanks!

  2. Thanks found a way to work around it.

I now saved the finished excel sheet, opened it up again and it refreshed all the data costing me another 100k rows.... I just want to save the data what do I have to do???

#19

Yeah, that is annoying. I recommend copying the results and pasting it back as values. I usually copy to a notepad and copy it from notepad and paste it back in Excel. This way I get rid of any Excel formatting.

#20

Thanks this worked fine!

I noticed another issue, apparently the tool uses only "https://" and not "http://+https://" when looking up referring domains. It makes sense since the URL's all are "https://", but I'd like to see both, the referring domains for the http and the https version for each URL.

Can I change that somehow?

#21

You should be able to get all if you chose this option:
bild

#22

That would return all backlinks for the domain and not just for the URL, or is that setting different from the one in the Site Explorer?

EDIT: I think I found the solution. If you want backlinks for https and http the URL should look like this "www.example.com/example" without any protocol.

#23

Yeah, you are right :slight_smile:

1 Like
#24

Thanks for all the help, have a great weekend!!!!

1 Like