Using Python to recover SEO site traffic (Part one)

12 Febbraio 2019 Rassegna Stampa, SEO

Helping a client recover from a bad redesign or site migration is probably one of the most critical jobs you can face as an SEO.

The traditional approach of conducting a full forensic SEO audit works well most of the time, but what if there was a way to speed things up? You could potentially save your client a lot of money in opportunity cost.

Last November, I spoke at TechSEO Boost and presented a technique my team and I regularly use to analyze traffic drops. It allows us to pinpoint this painful problem quickly and with surgical precision. As far as I know, there are no tools that currently implement this technique. I coded this solution using Python.

This is the first part of a three-part series. In part two, we will manually group the pages using regular expressions and in part three we will group them automatically using machine learning techniques. Let’s walk over part one and have some fun!

Winners vs losers

SEO traffic after a switch to shopify, traffic takes a hit

Last June we signed up a client that moved from Ecommerce V3 to Shopify and the SEO traffic took a big hit. The owner set up 301 redirects between the old and new sites but made a number of unwise changes like merging a large number of categories and rewriting titles during the move.

When traffic drops, some parts of the site underperform while others don’t. I like to isolate them in order to 1) focus all efforts on the underperforming parts, and 2) learn from the parts that are doing well.

I call this analysis the “Winners vs Losers” analysis. Here, winners are the parts that do well, and losers the ones that do badly.

visual analysis of winners and losers to figure out why traffic changed

A visualization of the analysis looks like the chart above. I was able to narrow down the issue to the category pages (Collection pages) and found that the main issue was caused by the site owner merging and eliminating too many categories during the move.

Let’s walk over the steps to put this kind of analysis together in Python.

You can reference my carefully documented Google Colab notebook here.

Getting the data

We want to programmatically compare two separate time frames in Google Analytics (before and after the traffic drop), and we’re going to use the Google Analytics API to do it.

Google Analytics Query Explorer provides the simplest approach to do this in Python.

Head on over to the Google Analytics Query Explorer
Click on the button at the top that says “Click here to Authorize” and follow the steps provided.
Use the dropdown menu to select the website you want to get data from.
Fill in the “metrics” parameter with “ga:newUsers” in order to track new visits.
Complete the “dimensions” parameter with “ga:landingPagePath” in order to get the page URLs.
Fill in the “segment” parameter with “gaid::-5” in order to track organic search visits.
Hit “Run Query” and let it run
Scroll down to the bottom of the page and look for the text box that says “API Query URI.”
1. Check the box underneath it that says “Include current access_token in the Query URI (will expire in ~60 minutes).”
2. At the end of the URL in the text box you should now see access_token=string-of-text-here. You will use this string of text in the code snippet below as the variable called token (make sure to paste it inside the quotes)
Now, scroll back up to where we built the query, and look for the parameter that was filled in for you called “ids.” You will use this in the code snippet below as the variable called “gaid.” Again, it should go inside the quotes.
Run the cell once you’ve filled in the gaid and token variables to instantiate them, and we’re good to go!

First, let’s define placeholder variables to pass to the API

_{metrics = “,”.join([“ga:users”,”ga:newUsers”])}

_{dimensions = “,”.join([“ga:landingPagePath”, “ga:date”])}

_{segment = “gaid::-5”}

_{# Required, please fill in with your own GA information example: ga:23322342}

_{gaid = “ga:23322342”}

_{# Example: string-of-text-here from step 8.2}

_{token = “”}

_{# Example https://www.example.com or http://example.org}

_{base_site_url = “”}

_{# You can change the start and end dates as you like}

_{start = “2017-06-01”}

_{end = “2018-06-30”}

The first function combines the placeholder variables we filled in above with an API URL to get Google Analytics data. We make additional API requests and merge them in case the results exceed the 10,000 limit.

_{def GAData(gaid, start, end, metrics, dimensions,}

_{segment, token, max_results=10000):}

_{“””Creates a generator that yields GA API data}

_{in chunks of size `max_results`”””}

_{#build uri w/ params}

_{api_uri = “https://www.googleapis.com/analytics/v3/data/ga?ids={gaid}&”\}

_{“start-date={start}&end-date={end}&metrics={metrics}&”\}

_{“dimensions={dimensions}&segment={segment}&access_token={token}&”\}

_{“max-results={max_results}”}

_{# insert uri params}

_{api_uri = api_uri.format(}

_gaid=gaid,

_start=start,

_end=end,

_{metrics=metrics,}

_{dimensions=dimensions,}

_{segment=segment,}

_token=token,

_{max_results=max_results}

₎

_{# Using yield to make a generator in an}

_{# attempt to be memory efficient, since data is downloaded in chunks}

_{r = requests.get(api_uri)}

_{data = r.json()}

_{yield data}

_{if data.get(“nextLink”, None):}

_{while data.get(“nextLink”):}

_{new_uri = data.get(“nextLink”)}

_{new_uri += “&access_token={token}”.format(token=token)}

_{r = requests.get(new_uri)}

_{data = r.json()}

_{yield data}

In the second function, we load the Google Analytics Query Explorer API response into a pandas DataFrame to simplify our analysis.

_{import pandas as}_pd

_{def to_df(gadata):}

_{“””Takes in a generator from GAData()}

_{creates a dataframe from the rows”””}

_{df = None}

_{for data in gadata:}

_{if df is None:}

_{df = pd.DataFrame(}

_{data[‘rows’],}

_{columns=[x[‘name’] for x in data[‘columnHeaders’]]}

₎

_else:

_{newdf = pd.DataFrame(}

_{data[‘rows’],}

_{columns=[x[‘name’] for x in data[‘columnHeaders’]]}

₎

_{df = df.append(newdf)}

_{print(“Gathered {} rows”.format(len(df)))}

_{return df}

Now, we can call the functions to load the Google Analytics data.

_{data = GAData(gaid=gaid, metrics=metrics, start=start,}

_{end=end, dimensions=dimensions, segment=segment,}

_token=token)

_{data = to_df(data)}

Analyzing the data

Let’s start by just getting a look at the data. We’ll use the .head() method of DataFrames to take a look at the first few rows. Think of this as glancing at only the top few rows of an Excel spreadsheet.

data.head(5)

This displays the first five rows of the data frame.

Most of the data is not in the right format for proper analysis, so let’s perform some data transformations.

First, let’s convert the date to a datetime object and the metrics to numeric values.

_{data[‘ga:date’] = pd.to_datetime(data[‘ga:date’])}

_{data[‘ga:users’] = pd.to_numeric(data[‘ga:users’])}

_{data[‘ga:newUsers’] = pd.to_numeric(data[‘ga:newUsers’])}

Next, we will need the landing page URL, which are relative and include URL parameters in two additional formats: 1) as absolute urls, and 2) as relative paths (without the URL parameters).

_{from urllib.parse import urlparse, urljoin}

_{data[‘path’] = data[‘ga:landingPagePath’].apply(lambda x: urlparse(x).path)}

_{data[‘url’] = urljoin(base_site_url, data[‘path’])}

Now the fun part begins.

The goal of our analysis is to see which pages lost traffic after a particular date–compared to the period before that date–and which gained traffic after that date.

The example date chosen below corresponds to the exact midpoint of our start and end variables used above to gather the data, so that the data both before and after the date is similarly sized.

We begin the analysis by grouping each URL together by their path and adding up the newUsers for each URL. We do this with the built-in pandas method: .groupby(), which takes a column name as an input and groups together each unique value in that column.

The .sum() method then takes the sum of every other column in the data frame within each group.

For more information on these methods please see the Pandas documentation for groupby.

For those who might be familiar with SQL, this is analogous to a GROUP BY clause with a SUM in the select clause

_{# Change this depending on your needs}

_{MIDPOINT_DATE = “2017-12-15”}

_{before = data[data[‘ga:date’] < pd.to_datetime(MIDPOINT_DATE)]}

_{after = data[data[‘ga:date’] >= pd.to_datetime(MIDPOINT_DATE)]}

_{# Traffic totals before Shopify switch}

_{totals_before = before[[“ga:landingPagePath”, “ga:newUsers”]]\}

_{.groupby(“ga:landingPagePath”).sum()}

_{totals_before = totals_before.reset_index()\}

_{.sort_values(“ga:newUsers”, ascending=False)}

_{# Traffic totals after Shopify switch}

_{totals_after = after[[“ga:landingPagePath”, “ga:newUsers”]]\}

_{.groupby(“ga:landingPagePath”).sum()}

_{totals_after = totals_after.reset_index()\}

_{.sort_values(“ga:newUsers”, ascending=False)}

You can check the totals before and after with this code and double check with the Google Analytics numbers.

_{print(“Traffic Totals Before: “)}

_{print(“Row count: “, len(totals_before))}

_{print(“Traffic Totals After: “)}

_{print(“Row count: “, len(totals_after))}

Next up we merge the two data frames, so that we have a single column corresponding to the URL, and two columns corresponding to the totals before and after the date.

We have different options when merging as illustrated above. Here, we use an “outer” merge, because even if a URL didn’t show up in the “before” period, we still want it to be a part of this merged dataframe. We’ll fill in the blanks with zeros after the merge.

_{# Comparing pages from before and after the switch}

_{change = totals_after.merge(totals_before,}

_{left_on=”ga:landingPagePath”,}

_{right_on=”ga:landingPagePath”,}

_{suffixes=[“_after”, “_before”],}

_{how=”outer”)}

_{change.fillna(0, inplace=True)}

Difference and percentage change

Pandas dataframes make simple calculations on whole columns easy. We can take the difference of two columns and divide two columns and it will perform that operation on every row for us. We will take the difference of the two totals columns, and divide by the “before” column to get the percent change before and after out midpoint date.

Using this percent_change column we can then filter our dataframe to get the winners, the losers and those URLs with no change.

_{change[‘difference’] = change[‘ga:newUsers_after’] – change[‘ga:newUsers_before’]}

_{change[‘percent_change’] = change[‘difference’] / change[‘ga:newUsers_before’]}

_{winners = change[change[‘percent_change’] > 0]}

_{losers = change[change[‘percent_change’] < 0]}

_{no_change = change[change[‘percent_change’] == 0]}

Sanity check

Finally, we do a quick sanity check to make sure that all the traffic from the original data frame is still accounted for after all of our analysis. To do this, we simply take the sum of all traffic for both the original data frame and the two columns of our change dataframe.

# Checking that the total traffic adds up

_{data[‘ga:newUsers’].sum() == change[[‘ga:newUsers_after’, ‘ga:newUsers_before’]].sum().sum()}

It should be True.

Results

Sorting by the difference in our losers data frame, and taking the .head(10), we can see the top 10 losers in our analysis. In other words, these pages lost the most total traffic between the two periods before and after the midpoint date.

_{losers.sort_values(“difference”).head(10)}

You can do the same to review the winners and try to learn from them.

_{winners.sort_values(“difference”, ascending=False).head(10)}

You can export the losing pages to a CSV or Excel using this.

_{losers.to_csv(“./losing-pages.csv”)}

This seems like a lot of work to analyze just one site–and it is!

The magic happens when you reuse this code on new clients and simply need to replace the placeholder variables at the top of the script.

In part two, we will make the output more useful by grouping the losing (and winning) pages by their types to get the chart I included above.