【Python×SEO】AhrefsのデータからURLごとのトラフィック上位５キーワードを抽出する

AhrefsやSearch Consoleは便利である反面、データが膨大過ぎるためそのままでは活用しづらいことがあります。

特にURL×クエリの組み合わせのデータでは、1URLあたり数百キーワードが紐づいているケースもあり、「URLごとに獲得できている代表的なキーワードが何なのか」を調べるだけでも、複数のステップが必要です。

1000行、2000行程度のデータであれば問題なく処理できますが、数万行～数十万行のデータに対して手動で作業を行うと、各ステップごとに数十秒～数分待たされてしまい、その間はPCも重たくなり仕事が止まってしまいます。

そこで今回は、Python（Polars）を使ってURLごとのトラフィック上位のキーワードを抽出するコードを紹介します。

また例によってPolarsを使用しているため、事前にpip install polarsを実行してインストールしておいてください。

手順①：AhrefsのオーガニックキーワードCSVファイルの準備

まずはAhrefsから、Organic Keywordsのデータをダウンロードします。

今回のコードは期間比較なしのデータを使用する前提で作成しているため、CSVダウンロード前に以下のドロップダウンを「なし（Don't compare）」に選択したうえで、CSVのダウンロードを行ってください。

ダウンロードできたら次の章で記載している.pyファイルと同じディレクトリに「organic_keywords.csv」という名前で保存してください。

手順②：pythonの実行ファイル（extract_top_keywords_by_url.py）を作成する

次のコードを任意のファイル名で、先ほどのCSVファイルと同じフォルダに格納します。
今回はChatGPTが考えてくれた「extract_top_keywords_by_url.py」という名前にしてみます。

コード上では４行目で読み込むCSVファイルのパス、５行目でURLごとにいくつのキーワードを抽出するのかを設定しているので、こちらは適宜変更してください。

import pandas as pd
import polars as pl

CSV_PATH = './organic_keywords.csv'
KEYWORD_LENGTH = 5 # URLごとに抽出するキーワード数

if __name__ == "__main__":
    df = pl.read_csv(CSV_PATH)
    df.columns = [column_name.replace(' ', '_').lower() for column_name in df.columns]

    # 必要なカラムだけ抽出する
    df = df.select(["keyword", "volume", "organic_traffic", "current_position", "current_url"])

    # dataframeをトラフィック降順でソート
    df_sorted_by_traffic = df.sort("organic_traffic", descending=True)
    
    # URLごとにランクを振る
    df_with_rank = df_sorted_by_traffic.with_columns(
        rank = pl.col("organic_traffic").rank(method="ordinal", descending=True).over("current_url"),
        total_traffic = pl.col("organic_traffic").sum().over("current_url")
    )
    
    # URLごとにトラフィックの合計を計算し、トラフィック降順でソート
    df_sorted_by_total_traffic = df_with_rank.sort("total_traffic", descending=True)
    df_filtered = df_sorted_by_total_traffic.filter(pl.col("rank") <= KEYWORD_LENGTH)

    # CSVに出力
    df_filtered.to_pandas().to_csv('./organic_keywords_rank.csv', index=False, encoding='utf-8-sig')

このextract_top_keywords_by_url.pyと、先ほどのCSVを保存すると、ファイル構造は以下のようになります。

ルートディレクトリ
├── extract_top_keywords_by_url.py
└── organic_keywords.csv

手順③：コードを実行する

準備はこれだけなので、あとはコードを実行するだけでOKです。

python extract_top_keywords_by_url.py

出力結果

コードを実行すると、同じフォルダ上に「organic_keywords_rank.csv」というファイルが生成されます。

CSVファイルには、

• keyword

• volume

• organic_traffic

• current_position

• current_url

• rank

• total_traffic

という７つのカラムがあります。total_trafficが大きい順にURLが並んでおり、各URLごとにorganic_trafficが大きなkeywordが５つまで記載されています。
これでURLごとにトラフィックを集めている主要キーワードを抽出することができました。

Polarsならコードの簡略化ができる

先ほどのコードでも問題なくデータの抽出が可能ですが、変数も多く、コードもやや読みづらい印象があります。

PolarsではDataFrameに対する処理をメソッドチェーンのように連結できるため、以下のように記載することも可能です。

import pandas as pd
import polars as pl

CSV_PATH = './organic_keywords.csv'
KEYWORD_LENGTH = 5 # URLごとに抽出するキーワード数

df = pl.read_csv(CSV_PATH)
df.columns = [column_name.replace(' ', '_').lower() for column_name in df.columns]
    
# メソッドチェーンを使って処理を行う場合のコード
(
    df.select(["keyword", "volume", "organic_traffic", "current_position", "current_url"])
    .sort("organic_traffic", descending=True)
    .with_columns(
        rank = pl.col("organic_traffic").rank(method="ordinal", descending=True).over("current_url"),
        total_traffic = pl.col("organic_traffic").sum().over("current_url")
    )
    .sort("total_traffic", descending=True)
    .filter(pl.col("rank") <= KEYWORD_LENGTH)
    .to_pandas().to_csv('./organic_keywords_rank2.csv', index=False, encoding='utf-8-sig')
)

余計な変数も減らせますし、そもそものコード量も少なくすることができるので、個人的にPolarsを気に入っている理由のひとつです。
Polarsを使っていく際は、積極的に使っていけると良いかなと思います。

まとめ

Pythonを活用することでExcelやスプレッドシートでは何十秒と待たなければならない作業でも、あっという間に処理可能です。
大量のデータを処理・加工する方は、ぜひ今回のnoteでご紹介したコードをご活用ください。

また今回は単一のCSVファイルが対象でしたが、実務では４サイト、５サイトと複数サイトのデータをまとめて調査・比較することが多いかと思います。
複数サイトのデータを一括で処理・統合する方法については、また次回以降のnoteで書いていこうと思います。