Semrush hack for “show backlinks”

For some reason I am too busy to find out Semrush has decided not to show competitor backlink data by pages.

I needed this data for aroung a hundred pages so here is what I did:

• Show all the backlink data on screen
• Pasting the page data into Notepad
• Organizing lines of data into columns

To get the lines of data into columns, I used the Excel formula for OFFSET. I am in a really weird locale so for me the formula is =OFFSET($A$1;(ROW(A1)*6)-1;0). Here the multiplies is the nth row I wanna put into column. The -1 tells me which is my starting line (-6 for first row, -5 for second row etc.)

I also needed to do some trick to manage the automatic formatting of data like 5.45 into a date. I did a manual replace where I replaced all commas and dots in the raw text file with a placeholder. I then did a search and replace for them when they were neatly in columns.

To finish off my data, I sorted it by an index that was (amount of backlinks / maximum backlink) * (amount of traffic / maximum traffic). This gave me a neat way to see where the competitor is extremely strong.