キーワードの抽出から記事リストを作る手順【56枚の図で完全解説】

サジェストワードって大量に出てくるから処理が大変

キーワードから記事を書く手順がよくわからない・・

サジェストワードの取得は、ラッコキーワードを使えば誰でも一瞬でできるので非常に簡単です。

ただ、サジェストワードは量が膨大に出てきたりするのでそこからどうすればいいのかわからなかったり、グルーピングに苦しんだりして時間がかかってしまうもの。

この記事では、実際にキーワードを抽出するところから、整理してグルーピングし、最終的に記事リストを作るまでの過程をライブで解説していきます。

解説図が56枚、エクセルを利用しますが、あまりExcelが得意じゃない人のために操作方法も含めて解説しましたので全く初めての人でもできるようになるはず。

このページでは、「ウォーターサーバー」の特化ブログを作る場合を前提としています。

このページの内容(目次)

最初にするのはメインのキーワード決定

最初にまずメインになるキーワードを決めます。

下記の大きく3つの視点からメインになるワードを選びます。

カテゴリワード(メインワード)を決める

今回例に挙げる「ウォーターサーバー」の例であれば、「ウォーターサーバー」で構いません。

もう少し限定するなら「ウォーターサーバー おすすめ」とか「ウォーターサーバー 比較」などですね。

案件のカテゴリ名などにすればOK。

ただし、ユーザーが知っていて、商品選定時に使いそうなワードであることが条件です。

検索ボリュームがかなり多いワードではシングルワードではなく、複合ワードをメインワードに据えるべきです。上位表示するのに必要な記事量が非常に多くなるためです。

商標ワード(というか案件そのもの)をリストアップする

カテゴリワードを決めたら、続いて商標ワードもリストアップしましょう。

商標ワードとは、単純に「商品名」「サービス名」です。

ウォーターサーバーなら

  • アクアクララ
  • プレミアムウォーター
  • 信濃湧水
  • クリクラ
  • アルピナウォーター
  • ピュアハワイアン
  • ウォータースタンド
  • フレシャス

などです。

狙うジャンルにもよりますが、10商品分くらいになるはずです。

関連ワードを選ぶ

上記2種類で大抵のジャンルは十分ですが、とりわけ競合が強い・多いジャンルではメインのキーワードから少しずらしたキーワードや同一の意味の別の言葉もキーワードのリストとして持っておいた方がいいでしょう。

キーワードプランナーを使って抽出します。

キーワードプランナーを使って関連ワードを抽出する方法

STEP1:サジェストワードの取得

STEP
ラッコキーワードにメインワードを入れてサジェストワードを抽出していく
メインキーワードのサジェストを取得

サジェストワードの取得ツールは多々ありますが、ラッコキーワードが最も使いやすいです。

サジェストを出力後、全キーワードをコピーしてください。

STEP
Excel にコピーしたキーワードを貼り付けていきます
エクセルにコピーしたキーワードを貼り付けていく

Excelを起動し、先ほどコピーしたキーワードを貼り付けます。

この作業をメインのキーワードの分だけ繰り返してください。

STEP
さらにサジェストのサジェスト(ミドルワード)を抽出する
サジェストのサジェスト(ミドルワード)を抽出

ラッコキーワードは非常に優秀で、サジェスト数の多いワードは、「サジェストのサジェスト」欄に一覧が出ます。

これらはミドルワードになるため、合わせて抽出します。図のように、くるくるマークのところをクリックすると、別ウィンドウに出力されます。

STEP
キーワードは下にどんどん貼り付けていく
貼り付けた最終行に続けて貼り付けていく

Excelで貼り付けた最終行に続けて貼り続けていきます。最終行に移動するには、[CTRL] + [ ↓ ] を押すと最終行まで飛べます。

どんどん追加していくと、1000行はあっという間に超えます。5000ワードくらいになることもあります。

かなりの数のキーワードが並ぶことになります

STEP2:検索ボリュームの取得

STEP1では、サジェストをとにかく出力・取得して、1つのエクセルにザーッと並べていくところまで行いました。

続いて、STEP2では、各ワードの検索ボリュームの取得を行います。極端に検索数の少ないワードで記事を書いても集客できる見込みが少ないためです。

STEP
キーワードプランナーにアクセスします。
キーワードプランナーに移動し、検索ボリュームの取得の方を選択

キーワードプランナーにアクセスし、<検索ボリュームと予測データを確認する>をクリックします。

STEP
Excelのキーワードをすべて貼り付ける
Excelのキーワードをすべて貼り付ける

キーワードを貼り付ける入力欄が出てくるので、そこにExcelにあるすべてのキーワードを貼り付けてください。

<開始する>ボタンを押すと、検索ボリュームの取得が始まります。

エラーが出たらそのキーワードは削除する

<開始する>ボタンを押したときに、キーワードでエラーが出ていたりする場合がありますが、その場合はキーワードを削除してしまってください(クリックしていけば該当のワードを削除できます)。

また、一度に3000ワードを超えると一度には取得できなくなるので、分割して行ってください。

STEP
出力された検索ボリュームをダウンロードする
検索ボリュームをダウンロードする

画面が切り替わり、検索ボリュームの一覧が表示されます。

ダウンロードボタンが右上にありますので、クリックし、開いたメニューの中から「過去のプラン指標」にある「.csv」をクリックするとダウンロードが始まります。

STEP3:キーワードリストの作成

元のキーワードに戻す

キーワードプランナーは検索ボリュームの出力時にキーワードを分かち書きして書き換えてしまいます。

今回の例であれば、「ウォーターサーバー」は「ウォーター(半角スペース)サーバー」と「ウォーター」と「サーバー」に分割されました。

他の単語も多数書き換えが発生してしまうので、ダウンロードしたサジェストの状態に戻しておきます。

STEP
ダウンロードしたファイルを開き、不要な部分を削除
ダウンロードした検索ボリュームのCSVファイルを開く

ダウンロードしたCSVファイルを開きます。ダブルクリックするとそのままExcelで開くことができます。

このファイルのフォーマットはやや特殊で不要な行・列があるので削除していきます。

不要な行・列を削除する

必要なのはA列(キーワード)・D列(検索ボリューム)・E列(競合性)なので、それ以外は全て削除します。

STEP
一旦CSVファイルをエクセル形式で保存しなおします
シートの見出し・シート名などを修正してエクセル形式で保存

ここから少しファイル形式やファイルの内容を整理していきます。

英語のままだと少し見づらいとおもいますので、見出しを日本語に変更し、シート名もやけに長いので修正して一旦エクセル形式で保存しなおします。

A列キーワード
B列検索ボリューム
C列競合性
シート名キーワードリスト
ファイル形式Excelブック(.xlsx)
STEP
検索ボリュームのシートと統合します
キーワードリストのエクセルに検索ボリュームを統合する

キーワードの抽出をしていたキーワードリスト.xlsxにあるSheet1を検索ボリュームのシートと統合します。

STEP
サジェストワードのシートに参照列を追加

検索ボリュームを出力する際に、勝手に半角スペースを挿入して分割したのなら、その半角スペースを抜けばいいのですが、どの位置にスペースが入ったかはわかりません。

しかし、元のキーワードと、出力後のキーワードの両方から全ての半角スペースを削除すると同じ値になります。

これを利用して、参照用の列を追加し、マッチングさせます。

A列を右クリックして列を追加します

まずは元のシートの方から。A列に参照用のシートを追加します。

マッチング用の文字列を作成します
オートフィルで残りの行にも反映します
残りの行にも反映された

マッチング用の文字列を作成します。元のキーワードから半角スペースを全て取り除いたものです。A1に下記を入力します。

=SUBSTITUTE(B1," ","")

残りの行にはオートフィルを使って反映させます。

今回は関数を使って出力しましたが、まるごと同じ列をコピーして置換する方法もあります(その方が手っ取り早かったかも・・)

STEP
検索ボリュームのシート(キーワードリスト)も同様に参照列を追加
同じ要領でキーワードリストにも参照用の列を追加
同じように全行に反映

先ほどと同じ手順でキーワードリストのシートにも同じように参照列を追加します。

STEP
元のサジェストに復元する列を追加
復元用の列を追加

キーワードリストのシートに復元した値を入れるための列を挿入します。この列を最終的に残すようにします。

STEP
マッチングして元のサジェストワードを復元
元のキーワードを復元
全行に反映

参照列同士をマッチングさせ、元のサジェストワードがB列に入るようにします。

B2セルに下記の数式を入れます。

=VLOOKUP(A2,Sheet1A:B,2,false)

残りの行をオートフィルで埋めたら復元完了です。

この状態だと、マッチングできなかったキーワードがいくつか出てエラーの状態になっている行が出てきます(キーワードプランナーでいくつかキーワードを削除したためです)。

エラーの出ている行を抽出する
C列の内容をそのままB列に貼り付ける

エラーの出ている行だけ修正するために、フィルターを設定し、「#N/A」となっている行だけが選択されるようにします。

絞り込んだらC列の内容をそのままB列に貼り付けて下さい。フィルターがかかっているため、C列をまとめて選択して貼り付けることができません。面倒ですが、1セルずつ順番にコピペしていってください。

STEP
不要な数式・シートを削除します

あともう少しです。

B列から数式を削除します

このままでは数式が残ったり、参照列が残ったままになってしまい使いにくいので、不要な列やシートを削除していきます。

まず、B列は現在「数式」の状態なので「値」の状態に変更します。B列全体をコピーし、B列を選択したまま、貼り付けオプションから<値の貼り付け>を選択してください。数式が消えます。

不要なシートと列を削除します

A列の参照列と、C列の分割された分かち書きキーワードの列は使わないので削除します。また、Sheet1もも不要なので削除します。

この状態でキーワードリストは完成なので一度保存しておいてください。

ここまでで、サジェストワードの抽出と、その検索ボリュームの調査まで完了しました。

STEP4:地域系ワードの整理

ここからキーワード毎の検索意図の整理をしていきます。

最初に地域系キーワードを全部処理する方法を紹介します。

地域系のキーワードは都道府県名での検索はもちろん、キーワードによっては市区町村レベルまでサジェストに上がってきます。

しかも、市区町村名だけで見た時にそれが地域系キーワードと気づけないものがたまにあるので、検索意図の整理の時に結構邪魔になることが多いのです。

そこで、市区町村名まで含めた一覧を使い、合致したものをすべて地域キーワードとしてマークしてしまいます。

STEP
最初にキーワードをスペース区切りで分割します
地域系ワードを処理するためにキーワードを分割します

A列のキーワード一覧を選択した状態から<データ>タブ→<区切り位置>を選択します。

区切り文字の種類と出力位置を指定します

ダイアログが開くので、<次へ>→タブのチェックをはずし、スペースにチェックが入った状態にして<次へ>→出力位置を$D$1に書き換えます。

キーワードが分割されて出力されます

<完了>ボタンを押せばキーワードが分割された状態でD列以降に並びます。

STEP
市区町村名のデータをダウンロードする
住所JPから市区町村名のデータをダウンロードする

住所.jpから住所データCSVをダウンロードします。

解凍して不要な列を削除します

読みの列など不要な列を削除します。

重複している行が多いので削除します

重複が多いので、重複行を削除します。

検索ボリュームのブックに移動します

検索ボリュームのシートに移動させます。

STEP
分割した列ごとに都道府県・市区町村の単語をカウントする
カウント用の列を追加します

先にマークを付けておくための検索意図列を追加します。

カウント用の列を追加し、F列の地域ワードが含まれているか確認します
オートフィルですべての行に反映

続いて、カウント用にE列を挿入し、下記の数式を入れます。

=COUNTIF(zenkoku!A:C,"*"&F2&"*")

COUNTIF関数は割と思い関数なので、数千行とかになってくると、完了するのに時間がかかります。

STEP
フィルタを使って地域ワードが含まれていたワードの検索意図に「地域ワード」と記入する
地域ワードが含まれた行だけに絞り込み表示
地域系ワードが含まれるワードをチェック

地域系のワードが選択されたら<検索意図>列に<地域ワード>と記載してください。

F列が終わったら同じ要領で他の列もしていきます

F列の処理が終わったらそれ以降の列も同様の方法でチェックしていってみてください。

ここまでで、地域ワードが大体整理できたはずです。最後に調査に使ったシートと列を削除しておきましょう。

不要になったシートや列を削除

STEP5:検索ボリュームの少ないワードを対象外にする

検索ボリュームがかなり少ないワードは3語以上、4語・5語となることが多く、あまりにドテールなので上位表示ができてもほとんどアクセスが増えません。

なので、極端に検索ボリュームが少ないワードについては専用ページを作る効果は低いため、対象外にしてしまいます。

STEP
先に正しい検索ボリュームに置き換えます
検索ボリュームを正しい数値に置き換えます

キーワードプランナーから出力した検索ボリュームの値は50とか500といった感じですべて5から始まる数字になっています。

画面では500なら100~1000と表示されます。CSVにする際に中間の数字をとって500に置き換えるものと思われます。これを正しい数字に置き換えておきましょう。

検索する文字置換文字
500000100000~1000000
5000010000~100000
50001000~10000
500100~1000
5010~100
STEP
検索ボリュームがついていない(少ない)キーワードを除外
検索ボリュームの少ないキーワードを除外

検索ボリュームが空欄になっているワードがあります。

検索ボリュームが少なすぎて数値が取れなかったワードになります。

フィルタを作成して空白のセルを選択し、検索意図欄に「検索ボリューム少ない」とマークしておきます。

STEP6:商標ワード・ミドルワードの整理

サジェストのサジェストとして出力したキーワードや、商標ワード(会社名や商品名)で抽出したワードを整理します。

STEP
キーワード列にフィルタをかけ、ミドルワード・商標ワードで絞り込む
ミドルワード・商標ワードで絞り込む

サジェスト数の多いワードで、追加したワードや、商標ワードを順にキーワード列で検索を行い、絞り込みます。

絞り込んだ結果の検索意図列にキーワード列を検索する際に使ったワードを記入していきます。

この作業を繰り返し、検索意図列でミドルワード・商標ワードをマークしていきます。

STEP
複数のキーワードが出てきそうな語句でも検索
複数のキーワードがありそうなワードで絞り込む

ミドルワードや商標ワードの分類が終わったら、検索意図列のフィルタで(空白セル)のみがチェックが入った状態にします。

複数のキーワードが出てきそうな単語をキーワード列のテキストフィルターに入力し、出てきたワードの検索意図列に検索した単語を入力していきます。

STEP7:残った検索意図列を埋めていく

複数のキーワードの検索意図をまとめて分類していけるのはこのくらいで、あとは一つ一つ見ながら地道に検索意図をまとめていく作業になります。

残りの検索意図も埋めていく

残りの検索意図を埋める作業というのがキーワードリストを作っていくうえでは最も大変な作業になります。

体感的にはサジェストワード全体の3割~5割が個別に検索意図をチェックしていかなくてはいけないワードです。

こればかりは割と気合なので、頑張りましょう。時間がかかる作業ですので、時間をかけて構いません。

STEP8:グループ毎に検索意図を深堀してグルーピング

検索意図の列の記入が終わると、ある程度グループが生まれます。

次の作業として、各グループ内でさらに深堀をしていく作業です。

STEP
検索意図の列をソートします
検索意図が埋まったらソートする

まず、検索意図列でソートを行います。

STEP
検索意図詳細列を追加
検索意図詳細の列を追加します
STEP
検索意図詳細列を、グループ単位で埋めていく
検索意図詳細列に検索意図を記入していきます

詳細列は結構細かい内容で文章的な書き方で構いません。注意点が1つだけあり、同じ意味を示すワードは同じ文言で記入するようにします。

そうすることで、詳細列を再度ソートした時に関係する内容で固まるからです。

STEP
検索意図詳細列でソートを行う
検索意図詳細でソートする

検索意図詳細でソートを行うと、近い意味のキーワードが固まります。

STEP9:記事グループから作成する記事を決める

STEP
グループ毎にメインになるキーワードを決める
記事グループ毎にメインキーワードを決める

各グループ毎にメインになるキーワードを決めていきます。

検索ボリュームが100~1000回となっているワードをメインワードにしていきます。

STEP
グループから作成する記事のタイトルを考える
グループから作成する記事のタイトルを決める

検索意図詳細の各グループが記事を作成する単位になります。

検索意図のグループが親となり、ピラーページと言われるまとめ記事のようなものを作ります。

全体として、シングルワードや複合ビッグワードに対してピラミッド型の構造を取り、力を集めていくような形のサイトに仕上げていきます。

STEP
記事リストの完成!
記事リストの完成

最後に、A列に記事列を追加して、記事名を入れていきます。

あくまでここでは仮のタイトル案で構いません。個別の記事を書く際にさらに記事構成を練るタイミングで正式なものにしますので、記事の要約というか、方向性のようなものでOKです。

まとめ:記事リストの完成は設計の第一歩

以上で記事リストの作成が完了しました。お疲れさまでした。

サジェストのワード数にもよりますが、10時間以上かかる作業になるはずです。

実はサイト設計における記事リストの作成は設計の第一歩にすぎません。

とはいえ、最も時間のかかる作業なので、ここが終われば半分以上完了したのも同然です。

おそらく、記事リストとしては100記事分以上の「ネタ」が出来上がっているはずで、実際のブログ執筆作業時もこのリストに優先度をつけながら順に記事を書いていくという感じになります。

シンプルにこれが出来上がった時点で、ある程度サイトのイメージや、記事作成のイメージが湧いている状態になるのではないでしょうか。

引き続き、サイト設計を進め、記事の作成に進みましょう。

このページの内容(目次)
閉じる