エクセルで頻出単語を自動抽出！ 検索キーワードの分類・分析を簡単に

自然検索のフレーズは、ワードのかけ合わせや類義語が多く、ロングテールになるため、上位の20件～100件だけを見ていると傾向を読み誤ることがある。とはいえ、個別にたくさん見るのは時間がかかりすぎる。まずは全体の傾向をざっくりと見て、改善すべきエリアを絞り込んでから、個別キーワードの最適化に着手したいところだ。

そこで、今回は似たような検索フレーズをまとめて分析する方法について検討してみる。

ねらい：ざっくり分析したい

検索キーワードのデータは細かい。ひらがな、カタカナ、漢字、アルファベットなどのバリエーションや類義語に加えて、複数ワードのかけ合わせやタイプミスなどもすべて別のキーワードとして記録されるからだ。そうした細かいバリエーションは、個別の件数は少ないものの、足し上げると意外と大きな割合になることがある。

また、時期的な影響も受けるため、上位の検索キーワードだけを見ていると隠れた小さな変化を見過ごしてしまう。そもそも、上位にランクインするようなキーワードはそのサイトにとって定番であり、ある程度予測がつく。変化があった場合でも気がつきやすい。

来訪検索キーワード分析では、上位のキーワードで当たり前の結果を再確認するだけでなく、数は少ないけれどもコンバージョン率が高い穴場のキーワード、あるいは急落したキーワード群をいち早く見つけ、SEO施策を実行するなどの迅速な改善アクションに結び付けたい。

リスティング広告の場合は、管理画面で設定したキャンペーンや広告グループを作成する際に類義語やかけ合わせフレーズの指定ができる。スペルミスも吸収されるため、ざっくり分析できて便利だ。では、自然検索のキーワードも同様に意味でグルーピングして、全体をざっくりと分析できないものだろうか。

たとえば、こんなレポートを楽に閲覧できるようにしたい。

今回の流れ

検索キーワードを“ざっくり”分析するため、今回は以下のような流れで進める。

1. 解析ツールの検索キーワードレポートを、マクロを使ってExcelに落とし込む
2. Excel上で自動分類の仕組みを作る
3. 自動分類の結果をもとにレポートを作成する

1. 検索キーワードをExcelに落とし込む

まず、GoogleやYahoo!などの検索サイトで入力された検索キーワードを取得しよう。アクセス解析ツールでは、たとえばGoogleアナリティクスならば［トラフィック］＞［検索］＞［オーガニック検索］で確認できる。

しかし日々の作業を考えると、解析ツールのレポート画面からダウンロードする方法だと、手順が面倒すぎて長続きしないだろう。そこで、今回はスプレッドシートのセルに解析データを自動で落とし込めるツールを使うことにしよう。

今回は、「Excellent Analytics」を活用することにする。これは、Googleアナリティクスのデータを指定した条件で自動的に取得してきてExcelで使えるようにするもので、無料のExcelアドオンとして提供されている（Adobe SiteCatalystの場合は、ExcelクライアントやReportBuilderが利用できる）。

まずは、Excellent Analyticsをダウンロードしてインストールする。

Excellent Analyticsのサイトの右上にある［Download］ボタンをクリックして表示されるダウンロードページからダウンロード（メールアドレスを登録しておけば、最新情報がメールで送られる）。
Excellent AnalyticsはWindows専用（Mac版は用意されていない）で、Excel 2007以上が必要。インストールして正しく動作させるには、そのほかに.NET 3.5 SP1（.NET 4.0でもOK）、2007 Microsoft Office Primary Interop Assemblies（ダウンロードしたファイルを実行して展開されたファイルをインストール）、Tools for Office runtimeが必要だ。

Excellent Analyticsを正常にインストールすると、Excelに「Excellent Analytics」リボンが表示されるので、「Account」をクリックし、ログインする。

その後「New Query」をクリックしてデータ取得条件を設定する。

1. カレンダーで期間を指定する
2. カレンダー下の［Dimension］ボタンをクリックしてCampaign>Keywordを選んで再度［Dimension］ボタンをクリック
3. ［Metrics］ボタンをクリックしてSession>VisitsとSession>Visitorsなどを選んで再度［Metrics］ボタンをクリック

一度設定してしまえば、次回からデータを最新の状態に更新するのが楽になる

最後に「Execute」ボタンをクリックすると、データがExcelのセルに挿入される（少し時間がかかる場合もある）。

2. Excel上で自動分類の仕組みを作る

2-a. Excelのマクロを入力する

次に、Excel上で［Alt］+［F11］キーを押してVBAエディタを開く。

VBAエディタが開いたら、左上の［プロジェクト］ペインでいま開いているExcelブックを右クリックし、メニューから［挿入］＞［標準モジュール］を選んで標準モジュールを挿入する。すると右側のエディタ部分に標準モジュールが開くので、以下のコードを貼り付ける。

これは、このあとの作業でキーワード分類を自動化するための呪文だと思ってほしい。

Function myClassification(S1 As String, R1 As Range) As String
  Dim i As Integer, j As Integer
  Dim V1 As Variant
  V1 = R1.Value
  For i = LBound(V1, 1) To UBound(V1, 1)
    For j = LBound(V1, 2) + 1 To UBound(V1, 2)
      If V1(i, j) <> "" And InStr(1, S1, V1(i, j), vbTextCompare) > 0 Then
        myClassification = V1(i, 1)
        Exit Function
      End If
    Next j
  Next i
End Function

2-b. 分類用のルールを作成する

上記のマクロを挿入したのと同じExcelワークブックにExcelのシートを新規作成し（シート名を「rule」とする）、マクロが参照する分類用のルールを、以下のフォーマットに従って入力していく。

• A列に「分類名」を入れる。この分類名が、最終的なレポートの項目になる。
• その分類に含める検索キーワードを、B列以降に1セル1キーワードずつ、右横に並べていく

上記の例では、「デパート」「三越」「松坂」「西武」「高島」「そごう」「百貨」という文字列を含む検索キーワードを、すべて「デパート」（A列）という分類にまとめる（1行目）。
同様に、「さくら」「桜」「講演」を含むキーワードを、すべて「花見」という分類に（3行目）、「頒布」「領布」「毎月」「定期」「頻布」を含むキーワードを、すべて「頒布会」という分類にまとめるというルールになっている。

B列以降に指定したキーワードは部分一致で評価される。「全角と半角」「ひらがなとカタカナ」「アルファベットの大文字と小文字」は、いずれも区別されないので、そのバリエーションを指定する必要はない（漢字／ひらがな／アルファベットのバリエーションやよくある入力ミスは必要）。

また、上から順に評価して最初にマッチしたカテゴリに分類されるため、優先順位を考えてルールを作成しよう。細かいカテゴリを先に指定し、汎用的な分類カテゴリは後半で指定すると良い。

2-c. 検索ワードのシートにユーザー定義関数を追加する

Excelで、手順1でExcellent Analyticsを使って取得した検索キーワードのシートに戻り、入力されたデータの右側に、キーワード分類（さきほど作ったルールの各行A列に指定したもの）の列を追加する。見出しとして「KW分類」など指定しておいて、データが入力されている各行には、2-aで作成したユーザー定義関数を参照する式を入力する。

最初のデータ行に次のように入力し、あとはオートフィル（またはコピー＆貼り付け）するのがいいだろう。

=myClassification(A3, A$1:AV$39)

上記の式は、次のような意味だ：

• =myClassification ―― マクロとして作った「自動分類する関数」を呼び出す命令
• A3 ―― （かっこ内の1つ目の引数）分類対象の検索キーワードが記入されたセル（同じ行の左端の列のセルを指定する）
• rule!A$1:AV$39 ―― （かっこ内の2つ目の引数）2-bで作成した分類ルールのセル範囲（分類をたくさん指定している場合は、違う範囲指定になるはずなので注意）

各行に上記の式を入力していくたびに、その行のキーワードがどの分類に属するのかを自動的に判定してセルに表示してくれるはずだ。

3. 自動分類の結果をもとにレポートを作成する

分類されたカテゴリ別に指標を集計するため、ピボットテーブルを作成する。

こうして得られたカテゴリ別の検索キーワードレポートを見ると、上位の一部だけでなく、全体を見渡すことができるようになる。

ロングテール部分を合計することで、訪問回数は少ないがコンバージョン率が高い穴場のカテゴリが見つかるかもしれない。そのカテゴリに関するコンテンツを充実化させたり、リスティング広告を最適化したり、いろいろな改善施策が見えてくるだろう。

全体像を見渡した後は、必要に応じて個別のキーワードをさらに細かく掘り下げていく。

分類の精度を高める方法

とはいえ、分類のルールは最初から完璧なものを作れるわけではないはずだ。分類ルールの精度と分類カバー率を高めるため、分類結果を確認しながら分類ルールを調整していく必要がある。

分類されたキーワードの個数をピボットでグラフ化すると、分類のカバー率を確認できる。以下の例では、項目名が空の行が11.0％になっているので、検索キーワードの89％は分類できていることがわかる。この未分類の割合は今後増えていくので、10％などと基準値を決めて、それを上回ったら分類ルールを追加する、というような運用が必要だ。

分類ルールがうまく機能していることを確かめるために、Excelのフィルタを作成し、個別の分類カテゴリ毎にどの検索キーワードが分類されたのかを目視確認すると良い。

カテゴリ毎に「異質の項目が混ざっていないか？」をチェック

最後に

このようなExcelを一度設定してしまえば、今後は数クリックでデータを最新化できるようになるため、定期的なレポート確認が楽になる。なお、今回は検索キーワードをざっくりと分析する方法について紹介したが、ページをディレクトリ別やジャンル別でまとめる、リファラのドメインを独自ルールでまとめる、商品のIDを日本語の商品名に置き換えるなど、いろいろな応用が可能だ。

サイトの分析や改善にかけられる時間は限られている。だからといって、手が空いたときだけ場当たり的にレポートを眺めたり、一部のデータだけを見ていると、結論が偏ったり、大きな可能性を秘めた小さな変化 を見逃してしまうことがある。売り上げが下がらない場合は、見逃した機会損失に気がつくことすらないのだ。ツールをうまく活用し、定期的な確認を継続することで、変化にすばやく気づいて対応し、着実な成果を出せるようにしたい。