岡安裕一 こんにちは。
最近個人オフィスで仕事していると、ついついサボりがちになってしまう岡安です。
誰の目もない環境っていうのは、自分が試されますね。
そして、たいていダメな自分が勝ってしまいます。。
さて、今回は、データ解析系ネタの投稿です。
最近お仕事でマイクロソフトの無料でも使えるBIツール「PowerBI」をいじっております。
せっかくなので、何かブログ記事にもしようとネタを探していたところ、清水誠さんのこんな記事を発見しました。
サーチコンソールの詳細データをGoogleスプレッドシートに自動反映させてTableauにインポートする方法
「清水 誠」公式サイト（http://www.cms-ia.info/）のブログ
清水さんの記事では、自分のサイトにどんなキーワードでGoogle検索から流入しているかなどを測定できる、「Google Search Console」のデータを、自動で取得してBIツール「Tableau」に流し込む方法がまとめられています。
記事中でも触れられていますが、「Google Search Console」は、90日以上前のデータを閲覧できないなどの制約があり、過去データを蓄積したければ何らかの形でデータを引っこ抜いてきて、貯めておかなければいけません。
前から過去データをちゃんと取っておかないとなと思いつつ、放置してしまっていたので、ブログ記事にしつつ、自分の管理しているサイトの「Google Search Console」データを蓄積・整理してみようと思います。
なお、清水さんの記事中では、BIツール「Tableau」を使っています。
「Tableau」は非常に優れたツールですが、当然ながら利用にはライセンス料金がかかります。
今回の用途で使うのであれば、「Professionalエディション」が必要なので、年間で約10万円（2017年5月現在）。
さらにそのデータをオンラインで共有しようとすると「Tableau Online」でユーザあたり年間6万円（2017年5月現在）が必要になるという寸法です。
個人でも出せない金額ではありませんが、部門でデータを共有したり、自分＋顧客先でオンラインで共有したいなんて時に、気軽に導入できる費用ではないとお考えになる方も多いのではないかと思います。
そこで、私の記事では、「PowerBI」の無料版の範囲でどこまでできるかチャレンジしてみます。
サーチコンソールの詳細データをGoogleスプレッドシートに自動反映させてPowerBIにインポートする方法
基本的な手順は、清水さんの記事のまんまです。
まずは、
- まずアドオン「Search Analytics for Sheets」をインストール
- アドオンを実行してデータを取得
- 毎月自動でデータを取得する
まで実行してみてください。
しかし、このアドオンは便利ですな。
次の「4. Tableauに取り込む」が「PowerBI」に代わります。
4′.PowerBIに取り込む
事前準備1として、「PowerBI Desktop」をPCにインストールしてください。
インストールはこちらから。
残念ながらMacOS用の「PowerBI Desktop」は存在しないようです。（2017年5月現在）
もしかするとExcel上のPowerBI関連のアドインであればMacでも使える可能性はあるかもしれませんが、たぶん難しいとは思いますので、「PowerBI」を使いたい場合は、WindowsPCをご用意されるとよいでしょう。
なお、「PowerBI」で作成したレポートを閲覧するだけであればMacでもOKです。
次に事前準備2ですが、「PowerBI」はGoogleスプレッドシートを直接データソースとして指定できません。
そこで、データを抽出するために一工夫として、
・GoogleスプレッドシートをWebで公開
・公開したURLをPowerBIでソースとして指定
という手順を踏みます。
清水さん記事の3までの手順で作成したスプレッドシートを開いて以下の手順でURLを取得します。
メニューから、ファイル→ウェブに公開を選択。
対象をドキュメント全体、形式をMicrosoft Excelとして公開します。
ボックスの中に記載されているURLをコピーしておきます。（リンクは最後の部分が”?output=xlsx”という文字列になっている必要があります）
そしてPowerBIを立ち上げて、データを取得します。
メニューから、データを取得→Webを選択。
URLを記入する画面がポップアップされるので、先ほど取得したURLを貼り付けてOKボタンをクリック。
シートをすべて選択して、編集ボタンをクリック。
取り込むデータに対する処理を行っていきます。
ポイントは増えるシートを自動で取り込むようにすることです。
シートをすべて取り込んで、シート名に”20″を含むものをフィルタします。
コードで記載する場合は、以下のように記載します。
= Table.SelectRows(ソース, each Text.Contains([Name], “20”))
コードではなく、Excelでフィルタをかけるときのようにフィルタボタンをクリックして、”20″で絞り込むというGUIベースの操作を行うと、PowerBIがステップとして自動で記載してくれます。（このあたりのステップの処理の設定の簡易さがPowerBIや最近のBIツール・セルフサービスBIの便利なところです）
これでシートが増えても自動で取り込んでくれるはず。
もしうまく動かないなどありましたら、ご連絡ください。
次は、フィルタしたシートからデータを展開します。
先ほどの状態で「Data」列のところに矢印が左右に展開したアイコンがありますので、そこをクリックします。
するとこんな状態になるはずです。
上手くデータが展開できない場合は、コードが以下のようになっているか確認してみてください。
= Table.ExpandTableColumn(フィルターされた行, “Data”, {“Column1”, “Column2”, “Column3”, “Column4”, “Column5”, “Column6”, “Column7”, “Column8”, “Column9”})
このあと、データの最上部をヘッダーに昇格させたり、空行を削除したり、複数シートの展開のために発生する必要ないヘッダー行を削除したり、必要に応じて型を変更したりの細かい処理を行いますが、たぶんいろいろ触ってもらうとわかると思いますので、ここでは省略します。
5’. データの前処理
レポート内で使わない項目は削除します。
個人で使う分には非表示をあえてしなくてもよいと思いますが、複数の人が使うときは不要なデータは非表示にしたり、項目名が何のデータなのかが一目でわかる名称とするとよいでしょう。
非表示方法は、左側の「テーブル」のようなアイコンをクリックして実際のデータを表示し、右側に表示される項目一覧のところで、右クリック→「レポートビューの非表示」です。もう一度選択すると再度表示されます。
次に計算項目の作成を行います。
率の計算や平均の算出などは、取り込んだデータをそのまま集計すると正しいデータとなりません。
「PowerBI」では、計算項目を作成する方法が「メージャー」と「列の追加」の2種類あります。
「メジャー」は、率の計算や平均の算出など、集計時点で都度計算しないと正しい数値とならないものに使用します。
「列の追加」は、レコードごとに単純な計算などをしておいた方がよい場合などに使用します。
どちらを使った方がよいのかの判断は結構わかりづらいので、実際にいろいろと試してみてください。（どちらを使ってもよい場合もあります）
今回は、クリック率（CTR_cul）はメジャーとして追加、平均順位は列の追加で事前計算をさせておいて、メジャーで平均を出す形としてみました。
メジャーや列の追加は、以下のメニューから行います。
各項目の計算式は以下の通り。
CTR_cul = CALCULATE(sum(‘serchconsol'[Clicks]) / SUM(‘serchconsol'[Impressions]))
※メジャーとして追加
position×impression = ‘serchconsol'[Position] * ‘serchconsol'[Impressions]
※列として追加
平均順位 = sum(‘serchconsol'[position×impression]) / sum(‘serchconsol'[Impressions])
※メジャーとして追加
これで、分析する下準備がある程度できました。
6. おまけ
さて、ここまでの処理がうまくいくと、実際にいろいろな形データを分析できるようになりますが、Serch CosoleのデータはページがURL表記のみで、ページ名がわかりません。
これだと直感的な分析がしづらいので、余裕があればURLとページ名が一対一になったテーブルを用意して投入、Serch CosoleのデータとURLをキーとしてリレーションを張る、といったことを行うと、ページ名での分析が可能になります。
今回は、ページ名とURLの一覧は、GoogleアナリティクスからCSVで落してみました。
アナリティクスのデータは、PowerBIで直接取り込めるので、うまくやればページ名も自動で取得できるようになると思います。
オンラインでの共有
PowerBIでは、「PowerBI Desctop」で作成したデータをオンラインで利用できる「PowerBIサイト」に発行して複数のユーザに共有することが可能です。
「PowerBIサイト」は、無料版のアカウントでも利用できますので、ぜひ試してみてください。
また、わざわざPowerBIのアカウントを作成するのがめんどくさいという場合には、直接Webページに埋め込むことが可能です。
という訳で、今回作成したレポートを以下に貼り付けてみました。
データなどをちゃんと整理しきっていない部分はありますが、よろしければ参考としてみてください。
さいごに
いかがでしたでしょうか？
清水さんの記事に乗っかって楽にブログ記事を作成しようとよこしまな考えをもっていましたが、思ったより時間がかかってしまいました。
悪いことはできないものです。
今回「PowerBI」で作成した内容はすべて無料版の範囲で作成できてしまいます。
Microsoftさんもここまでの機能を無料で提供するとか太っ腹ですね、
そのうちGoogleアナリティクスのデータの取り込みとかも記事にしてみたいと思いますので、お楽しみに。
ではでは。