最近Googleのサーチコンソールをのデータを抽出して、どんな語句検索されているのか調べようと思っていました。
ちなみに、Googleのサーチコンソールとは自分のサイトがGoogleがどう評価されているかを調べるツールです。
実際データを抽出して、例えば「WordPress」という語句を含んでいる語句だけ抽出しようと思って色々調べたんですが・・・。
なかなかきれいに抽出方法をまとめている記事が見当たらない！
ということで、自分でスプレッドシートから部分一致でデータを抽出する方法をまとめようと思い今回の記事を作ることにしました。
Googleスプレッドシートを部分一致でデータを抽出しようとして、フィルターを使って検索して「✔」を外して検索して抽出したりしていませんか？
数が少なかったら良いんですが、数が多かったらめんどくさくて仕方がありませんよね。。。
今回の記事では、そんな問題を一発で解決する「QUERY関数」について紹介します。
Googleスプレッドシートとは
まずはじめにGoogleスプレッドシートについて軽くご紹介致します。
Googleスプレッドシートとは、Googleから出ている「Excel」のGoogle版です。
このGoogleスプレッドシートの利点は他の人と、同じシートを共有したり修正したりするといったことができるという点です。
Excelだと、いちいちファイルを保存してそのファイルを誰かにメールして渡してみたいなことをする必要がありますよね。
Gooogleスプレッドシートを使えばそういった問題を解決することができます。
QUERY関数とは？
今回使用するQUERY関数についても最初に少し紹介します。
QUERY関数は、普段プログラミングをしている人には馴染み深いSQL形式の記述でデータを抽出することができる関数です。
SQLとは、データベースで使用されるプログラミング言語のことです。
今回でいうと、データベース＝Googleスプレッドシートのデータということになります。
データベースで使うプログラミング言語＝SQLと覚えておけばオッケーです。
普段から、Excelなどを使う人は「VLOOKUP関数」や「IF」など色々組み合わせて抽出すると思います。
ただ、QUERY関数を使うことでよりわかりやすい記述でデータを抽出することができます。
紹介も済んだことなので、次からは実際僕が行った作業を紹介していきます。
サーチコンソールからデータ抽出
まずは、サーチコンソールから検索クエリを抽出しましょう。
サーチコンソールにアクセスして、「検索トラフィック」から「検索アナリティクス」をクリックします。
クリックすると、検索アナリティクス画面が表示されます。
この画面したにスクールしていくと、ダウンロードボタンが表示されます。
ダウンロードボタンをクリックして、CSVのところにチェックを入れてダウンロードしましょう。
Googleスプレッドシートの作成
ダウンロードができたら、Googleスプレッドシートに移動します。
スプレッドシートは下記のリンクからアクセスして、「Googleスプレッドシートを使う」をクリックしましょう。
ちなみに、スプレッドシートを使うにはGoogleのアカウントが必要なので、事前に取得しておくようにしておきます。
アクセスしたら、左上の「＋」ボタンをクリックして新しいシートを作成します。
新しいシート画面に移動したら、左上の「ファイル」から「インポート」をクリックします。
インポートをクリックしたら、「現在のシートを置換する」にチェックを入れて「データをインポート」をクリックしましょう。
サーチコンソールのデータが貼り付けられると思います。
スプレッドシート関数の基本的なルール
スプレッドシートというよりは、Excelでのルールも同じなのですが、関数を書く時に使用する基本的なルールを紹介します。
行の指定方法
今回の関数ではセルA〜セルEの範囲を指定などのように書いていく必要があります。
セルの範囲をしている時は「：」を使って範囲を指定します。
例えば、セルBの２行目からセルHの２５行目というように範囲を指定する時は、下記のように記述します。
B2:H25
今回のQUERY関数を使う時に頻繁に出てくるので、ぜひ覚えておきましょう。
別のシートを指定する時
QUERY関数を使う時に使用する記述方法がもう１つあります。
それは、別のシートを参照する時です。
別のシートを参照する時は「’＋シート名＋’＋！＋セル番号」のように記述します。
「’シート３’！B2」と記述するとシート３にあるセルB２の値を参照という意味になります。
なので、これを記述するとシート３のセルBの2行目の値を参照することができます。
QUERY関数を使ったデータ抽出
では、実際にQUERY関数を使ってデータを使います。
QUERY関数はプログラミング言語のSQLのように記述することができる関数です。
QUERY関数は次のように記述します。
＝QUERY（条件１, 条件２）
条件１：参照するシートの範囲
条件２：抽出方法の記述。
抽出方法の記述について
ここでは、今回使用する基本的なものを紹介します。
SELECT
SELECTはそのままの意味で、「選択する」という意味です。
「SELECT A B」などのように記述することで、指定された範囲のセルAとセルBの値を抽出するという意味になります。
ちなみに、全てのセルを抽出したい場合は「SELECT *」と記述します。
WHERE
WHEREはそのままの意味で、「場所」という意味です。
例えば、「WHERE A = ’テスト’」と記述することで、セルAの中から「テスト」と一致するものを検索というように指定することができます。
LIKE
LIKEは「好きな」という意味ではなく、「〜ような」という意味です。
WHERE句は「＝」を指定することで完全一致。
「LIKE」を使用することで、部分一致と指定することができます。
ちなみに、「％」をつけるここで「前方一致」、「後方一致」、「全方一致」とわけることができます。
実際の抽出する時に使用するQUERY関数
基本的な説明を紹介したところで、実際に記述する関数を紹介します。
今回は次のように記述します。
「=QUERY((指定したいシートの範囲)!A1:E, “SELECT * WHERE A LIKE’%(検索したい語句)%'”)」
僕のシートの場合は、次のようにしています。
まとめ
いかがだったでしょうか？
今回はGoogleスプレッドシートで、部分一致でデータを抽出する方法を紹介しました。
QUERY関数は慣れないと大変ですが、慣れると実際にプログラミングするよりかはデータを抽出する時のハードルが低くなります。
なのでぜひ、今回紹介したQUERY関数を使って、他のデータの抽出や検索クエリを分析して次の記事を書く時の参考にしたりと約立てて見て下さい。
