Advanced SEO Reports You Can Generate Using BrightLocal and Google Analytics

Advanced SEO Reports You Can Generate Using BrightLocal and Google Analytics

August 23, 2017

Everyone likes data, especially in the SEO industry. We have used Brightlocal over the past few years to monitor keyword rankings for clients’ organic search engine visibility, because it allows us to monitor progress over time across all search engines:

  • Google
  • Google Local
  • Google Maps
  • Bing
  • Bing Local
  • Yahoo!
  • Yahoo! Local

When we send reports to clients, we try not to put too much emphasis on keyword rankings, especially for clients who we focus on for Local SEO. The local rankings tend to fluctuate over time, and while I’m fully aware that more local potential clients will call you once you’re included in Google’s Local “Snack Pack”, improved keyword rankings for a handful of keywords doesn’t always reflect overall search engine visibility efforts.

That being said, we include additional measurables in some advanced reports that we analyze, such as Google Insights actions, searches, and views, as well as organic search traffic according to Google Analytics.

By generating some of these advanced reports, we can not only give the client a better idea of progress that has been made over the past 6 or 12 months to improve overall search engine visibility, but we can also analyze the data to identify potential issues or changes that may have resulted in a drop-off during a certain date range.

By using BrightLocal’s cumulative data, and using Google Sheets, here are 5 advanced Local SEO reports that we’ve been able to generate:

Organic Traffic + Keyword Rankings (All Search Engines)

Let’s say you want to take a look at how keyword rankings across all search engines have progressed over time, compared to organic traffic.

In BrightLocal’s rank tracker tool, you have the ability to view aggregate ranking reports for all of the search engines mentioned above, for any of the following timeframes:

  • 1 month
  • 3 months
  • 6 months
  • 12 months
  • all time

So we can go into the rank tracker reports, and for this example we’ll do 12 months.

aggregate rankings chartOpen up a new Google Sheet, and copy all of the data below “Ranking History” going back to the past year:

Paste that data into a Google Sheet. Once the data is inserted, we’re going to use the following categories which you can specify as the row header of the sheet:

  • Date
  • #1 Rankings
  • #2-5 Rankings
  • Top 5 rankings (insert a new column)
  • #6-10 rankings
  • Top 10 rankings (insert a new column)

organic plus rankings

Next, well work on filling in the numbers for top 5 and top 10 rankings, which is fairly simple. But first, make sure the data is all sorted by date, with the most recent ranking reports showing up as the last row. Simply highlight the data, and click data – sort range – has a header row – sort by date

sort data by date

This will make it easier when we implement the weekly organic search traffic data from Google Analytics.

Next, let’s calculate the numbers for the top 5 and top 10 rankings. Simply:

Select D2. In D2 start typing “=(” and select the #1 ranking number for that date, type “=” and select the #2-5 number cell.

So top 5 rankings for 8/29/16 should be 33+25. Press enter, and the value will be calculated. You can then scroll down and have the rest of the values filled in using the same formula.

fill in the rest by selecting the first calculated cell and scrolling down

For Top 10, select sell D2. Enter the following formula: (D2+D8) ENTER, and follow the same process that we used for populating the data for Top 5.  Now we have all of our rankings data calculated. Next, insert 2 new columns to the left of #1 rankings column. This is where we will insert organic traffic data.

rankings data

In Google Analytics, click through to your organic search traffic over the past 12 months and hit export. Export the file to Google Sheets, and open a new Google Sheet.

Once the Google Analytics data is open in a new Google Sheet, delete all of the rows up until it starts listing the Week Index (which we’ll get to) and sessions. So it should look like this:

Week index obviously doesn’t help us much. What we need to do is take the organic search traffic data, and match it up with the actual dates of the ranking reports.

So if your keyword ranking reports run every week, it should be fairly simple to match the data. First, change the “0” under week index to the first date of the organic traffic report that was exported. So let’s say it’s August 28th, 2016.

Enter 8/28/16 in place of the 0.

In the cell under the new date, enter “=a2+7” which basically adds 7 days to the original date we entered:

calculate dates

Populate the second date, click the corner of cell A3, and scroll down to auto populate the new dates:

calculate dates

Now we have all of our dates for the weekly organic search traffic. Copy the data in these 2 columns, and paste it in the 2 columns that we left empty earlier in the Google Sheet with the keyword ranking data. Also, insert a new column to the right of the number of monthly sessions:

The dates should match up, if you’re running ranking reports on a weekly/monthly basis. You might have to go through and make some edits, but for the most part, we’ve seen the dates matching up when analyzing weekly data.

So in the new column, which should be labeled organic visits, we’re going to recalculate organic visits per week. In cell D2, type “=C2/10” and press enter. We’re going to take monthly organic visits and reduce them by 1/10, to make the charts more readable. Scroll down, and fill the rest of the data in for the remaining empty fields based on this equation.

Next, select columns A,D,E,G, and I, and select the icon that says “insert chart”:

insert chart

This will bring up a new chart with the selected data (organic traffic, #1 rankings, top 5 rankings, and top 10 rankings) populated.

In the editor on the right, click on “combo chart” as the type of chart, and then on the new chart itself click the three dots in the top right corner and select “move chart to a new sheet”.

We now have the following chart which compares #1 rankings, top 5 rankings, and top 10 rankings over the past 12 months to organic search visits:

rankings report final chart

Google Only + Organic Search Traffic

Want to generate a similar report, but only focus on Google rankings? Follow the same instructions above, but when you’re copying the data over from BrightLocal, make sure you only select Google/Google Local/Google Mobile:

google rankings plus organic traffic

Rankings + Organic Traffic + Phone Calls (GMB Actions)

By following the same formula in the first example, but changing the organic search traffic and keyword ranking reports to only populate data on a monthly basis versus a weekly basis, you can also generate charts which show the number of phone calls (and additional actions, according to Google My Business Insights report) per month, compared to rises/drops in organic search traffic and keyword rankings.

To do so, go to Google My Business and click on “download insights” on the top right corner. Download your Google Insights reports for each month, going back to the past 12 months (you’ll have to generate/download/import 12 different reports).

Go through the phone calls, insert the data (this is on a monthly basis versus a weekly basis, but still gives you some baseline) and you can generate a report similar to this:

phone calls plus traffic plus rankings

Google Local/Mobile/Organic Rankings Only

Let’s say you want to analyze Google Local rankings outside of BrightLocal’s aggregate ranking chart. Simply select Google Local in BrightLocal, follow the formula above in the first example, and when you go to create the chart select “stacked column chart” for the type of chart to come up with this:

google local rankings

These are just some of the examples of reports that you can generate using BrightLocal keyword ranking reports to not only give your clients more in-depth reporting, but to also have the ability to diagnose potential problems based on noticeable drop-offs.