Googleトレンドをスプレッドシートから検索
Googleトレンドで一度にたくさんのキーワードのトレンドを確認したいことがあるのですが、通常5つまでしか同時に検索することができないんです。
そこで今回はスプレッドシートで、検索したいキーワードのトレンドを取得できるような仕組みを作ってみました。
どうやって作るか
スプレッドシートのimportxml()を使ってGoogle トレンドから元のデータを引っ張ってきます。
そのデータからleft(),right(),find()などを使いながらうまいこと処理して、トレンドの数字だけを引っ張ってくることができれば、完成です。
完成系
ちょっと複雑なので先に完成系を貼っておきます。コピーしてご利用ください。
検索数が多い場合はスクレイピングのスピードが下がるので、その場合は時間を空けて試してみてください。
作ってみましょう
1.googleトレンドから元データを取得
まずgoogleトレンドの検索結果の元データを取得します。
=importxml("https://www.google.com/trends/fetchComponent?q="&"検索したいキーワード"&"&geo=JP&hl=ja&cid=TIMESERIES_GRAPH_0&export=5")
試しにキーワード"楽天"のトレンドを取得してみました。
Google Trendsvar _gaq = _gaq || []; _gaq.push(['_setAccount', 'UA-4401283-1']); _gaq.push(['_setDomainName', 'google.com']); _gaq.push(['_setAllowLinker', true]);_gaq.push(['_setCookiePath', '/trends']);_gaq.push(['_trackPageview', window.location.pathname + window.location.search + window.location.hash]); (function() { var ga = document.createElement('script'); ga.type = 'text/javascript'; ga.async = true; ga.src = ('https:' == document.location.protocol ? 'https://ssl' : 'http://www') + '.google-analytics.com/ga.js'; var s = document.getElementsByTagName('script')[0]; s.parentNode.insertBefore(ga, s); })(); google.load('visualization', '1',{'packages':['corechart']});google.setOnLoadCallback(drawChart); function drawChart() { var chartData = {""columns"":[{""id"":""d"",""label"":""Date"",""type"":""datetime""},{""role"":""annotation"",""type"":""string""},{""p"":{""html"":true},""role"":""annotationText"",""type"":""string""},{""id"":""q0"",""label"":""楽天"",""type"":""number""},{""role"":""annotation"",""type"":""string""},{""p"":{""html"":true},""role"":""annotationText"",""type"":""string""},{""role"":""certainty"",""type"":""boolean""}],""headlineDataPoints"":[],""width"":485,""axisAnnotations"":[{""row"":84,""url"":""//support.google.com/trends/bin/answer.py?hl\u003dja\u0026answer\u003d1383240""}],""rows"":[ [{""v"":new Date(2004, 0, 16, 12, 0),""f"":""2004年1月""},null,null,19,null,null,true], [{""v"":new Date(2004, 1, 15, 12, 0),""f"":""2004年2月""},null,null,20,null,null,true], [{""v"":new Date(2004, 2, 16, 12, 0),""f"":""2004年3月""},null,null,19,null,null,true], [{""v"":new Date(2004, 3, 16, 0, 0),""f"":""2004年4月""},null,null,20,null,null,true], [{""v"":new Date(2004, 4, 16, 12, 0),""f"":""2004年5月""},null,null,21,null,null,true], [{""v"":new Date(2004, 5, 16, 0, 0),""f"":""2004年6月""},null,null,20,null,null,true], [{""v"":new Date(2004, 6, 16, 12, 0),""f"":""2004年7月""},null,null,20,null,null,true], [{""v"":new Date(2004, 7, 16, 12, 0),""f"":""2004年8月""},null,null,20,null,null,true], ... [{""v"":new Date(2016, 8, 16, 0, 0),""f"":""2016年9月""},null,null,49,null,null,true], [{""v"":new Date(2016, 9, 16, 12, 0),""f"":""2016年10月""},null,null,46,null,null,true],[{""v"":new Date(2016, 10, 16, 0, 0),""f"":""2016年11月""},null,null,47,null,null,true], [{""v"":new Date(2016, 11, 16, 12, 0),""f"":""2016年12月""},null,null,53,null,null,true], [{""v"":new Date(2017, 0, 16, 12, 0),""f"":""2017年1月""},null,null,48,null,null,true], [{""v"":new Date(2017, 1, 15, 0, 0),""f"":""2017年2月(集計途中のデータ)""},null,null,46,null,null,true],[{""v"":new Date(2017, 2, 16, 12, 0),""f"":""2017年3月""},null,null,null,null,null,true]],""showHeadlines"":false,""percentData"":false,""colors"":[""#3f85f2""],""height"":230}; var htmlChart = new trends.HtmlChart( 'time-chart', chartData.columns, chartData.rows, chartData.headlineDataPoints, null , chartData.showHeadlines,true, chartData.percentData, chartData.colors, chartData.width, chartData.height, chartData.axisAnnotations,true);htmlChart.render(); } body {margin: 8px};
上がトレンドの検索結果となります。
データの概要を簡単に説明すると、2004年からのデータで、100を検索ボリュームの最大値としてトレンド結果を返してくれています。
[{""v"":new Date(2017, 0, 16, 12, 0),""f"":""2017年1月""},null,null,48,null,null,true]
2017年1月のトレンドは48となります。(nullに挟まれている数字)
2.元データから必要なデータを抽出する
取得した元データから必要なデータは、「2017年1月のトレンドは48」というデータのみです。
それらを集めて、下の表のようなものを作っていきます1。
キーワード | 1月 | 2月 | 3月 | 4月 | 5月 | 6月 | 7月 | 8月 | 9月 | 10月 | 11月 | 12月 |
---|---|---|---|---|---|---|---|---|---|---|---|---|
楽天 | 20 | 40 | 60 | 80 | 20 | 40 | 60 | 80 | 20 | 40 | 60 | 80 |
amazon | 50 | 60 | 80 | 70 | 50 | 60 | 80 | 70 | 50 | 60 | 80 | 70 |
メルカリ | 10 | 20 | 40 | 90 | 10 | 20 | 40 | 90 | 10 | 20 | 40 | 90 |
元データからfind()してleft()やright()で該当の数字だけを取り除く処理はまた別の機会に書きたいと思います2:。
mid()を使うともっとシンプルにかけるよ。などございましたらコメントください。宜しくお願いします。
データベースを構築しよう〜PostgreSQL編〜
データコンペ用のデータベースをPostogreSQLで構築しました。 データ型さえきちんと頭に入れれば簡単に作れました。
作成手順
手順
- データベース作成
- テーブル作成
- データの挿入
今回のゴール
今回はレシートのデータを表にしてみます。 二行目の日本語は補足説明です。実際にはありません。 データベース名はposdata、表の名前はordersとします。
order_id | product_id | order_price | order_amount | order_date |
---|---|---|---|---|
レシート番号 | 商品番号 | 販売価格 | 購入量 | 購入時間 |
00001 | 101010 | 3000 | 1 | 2016/3/17 21:08 |
00002 | 202020 | 1000 | 2 | 2016/3/17 22:00 |
00002 | 303030 | 2000 | 1 | 2016/3/17 22:00 |
00003 | 123456 | 4000 | 2 | 2016/3/17 22:30 |
00004 | 223456 | 5000 | 1 | 2016/3/17 22:40 |
基本DB操作
まず表を入れる箱を用意します。 データベース作成
CREATE DATEBASE データベース名; CREATE DATEBASE posdata;
これで表を入れる箱ができました。 次にテーブルのアウトラインを先に作ります。
テーブル作成
CREATE TABLE テーブル名 (カラム名 データ型,カラム名 データ型,カラム名 データ型,・・・); CREATE TABLE テーブル名 (order_id text,product_id text,order_price integer,order_amount integer,order_date timestamp with time zone);
こんな感じの表のアウトラインができます。
order_id | product_id | order_price | order_amount | order_date |
---|---|---|---|---|
レシート番号 | 商品番号 | 販売価格 | 購入量 | 購入時間 |
text | text | integer | integer | timestamp with time zone |
下の二行は説明のためにあります。実際には一行目しかありません。
最後に中身(データ)を入れていきます。 今回は、CSVファイルからデータを挿入するのですが、PostgreSQL特有のCOPY文を使ってます。
copy テーブル名 from 読み込むCSVの絶対パス; copy orders from ’ORDER.csv’ WITH(encoding ’SJIS’,format csv,header true); # with以降は csvのエンコードがshift-jisかつcsvにヘッダーがあったためつけてるオプションになります。
完成ー
order_id | product_id | order_price | order_amount | order_date |
---|---|---|---|---|
00001 | 101010 | 3000 | 1 | 2016/3/17 21:08 |
00002 | 202020 | 1000 | 2 | 2016/3/17 22:00 |
00002 | 303030 | 2000 | 1 | 2016/3/17 22:00 |
00003 | 123456 | 4000 | 2 | 2016/3/17 22:30 |
00004 | 223456 | 5000 | 1 | 2016/3/17 22:40 |
補足
以下補足です。
データ型の変更
データ型の設定をミスって値が挿入できない場合は下のコマンドでデータ型を変えることできます。
ALTER TABLE テーブル名 ALTER COLUMN カラム名 TYPE 変更したいデータ型
データ型もっと知りたい!
データ型について知りたい方はこちらのリンクを参照してください。