SNSでは書けない長めの話

Googleトレンドで一度にたくさんのキーワードのトレンドを確認したいことがあるのですが、通常5つまでしか同時に検索することができないんです。
そこで今回はスプレッドシートで、検索したいキーワードのトレンドを取得できるような仕組みを作ってみました。

どうやって作るか
完成系
作ってみましょう
- 1.googleトレンドから元データを取得
- 2.元データから必要なデータを抽出する

どうやって作るか

スプレッドシートのimportxml()を使ってGoogle トレンドから元のデータを引っ張ってきます。
そのデータからleft(),right(),find()などを使いながらうまいこと処理して、トレンドの数字だけを引っ張ってくることができれば、完成です。

完成系

ちょっと複雑なので先に完成系を貼っておきます。コピーしてご利用ください。
検索数が多い場合はスクレイピングのスピードが下がるので、その場合は時間を空けて試してみてください。

f:id:iwa_k:20170301134702p:plain

作ってみましょう

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月	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()で該当の数字だけを取り除く処理はまた別の機会に書きたいと思います²:。
mid()を使うともっとシンプルにかけるよ。などございましたらコメントください。宜しくお願いします。

表内の数値は僕が独断と偏見で入力した値ですので、きちんと作成したシートで検索し直してください。↩
実は疲れてしまったとういうのはここだけの話です。↩

データコンペ用のデータベースを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 変更したいデータ型

データ型もっと知りたい！

データ型について知りたい方はこちらのリンクを参照してください。

データ型