キーワードのトレンドチェックツールを作ってみた

YahooショッピングAPIで取得してDBに貯めていた、EC関連の人気キーワードデータを使い、GoogleトレンドのようなツールをPHPとSQLとchart.jsで作ってみました。

実際に制作した画面は、「ECキーワードランキング」のキーワード詳細画面で見れるようにしてあります。まずは一度画面を確認してみて下さい。

キーワードのトレンドツールとはいえ、私が手元に保持するデータはYahooショッピングAPIで取得していた、その日の検索ワード上位100しかありませんので、昨日100位にあったキーワードが翌日ランクインしなければ、データとしては歯抜けになっています。

このような歯抜けの中途半端なデータを、なんとかトレンドをグラフで見れるレベルに仕立てるため、歯抜け日次データをカレンダーマスターと結合して月次集計することで、それっぽいデータに仕立てることを考えました。

この記事では、歯抜けデータをカレンダーとJOINすることで、それっぽく見せるサンプルSQLクエリもご紹介しつつ、トレンドツールを作った流れをご紹介しております。

キーワードのトレンド

Yahoo APIでは、日次で人気キーワードを100位までランキング形式で取得することができます。（※今はAPIサービスが終了となりました）

このデータを毎日DBに貯めて、直近一カ月、半年、一年間といった期間で集計して、キーワードをランキング形式で見えるようにしたサイトが「ECキーワードランキング」でした。

サイトの概要はこちらの記事でご紹介しています。

キーワードデータが存在しない

このキーワードツールで季節性のあるようなキーワード、例えば「クリスマス」といったキーワードを今（9月末頃に）検索してみても、直近30日には全くデータが入っていないので、日次の検索ボリューム推移には真っ平らなグラフしか表示されない状態になることに今更ながら気が付きました。

APIでは100件しかデータが取れない

キーワードツールのデータは、Yahoo!ショッピングAPIのキーワードランキングデータを日次で貯めたものを使っていたのですが、APIで取得できるキーワードデータは１００件／日までしか取ることができません。

このため、１００位以内にキーワードが現れない日は数値が０になってしまい、9月には「クリスマス」がランキング１００位に入るはずもなく、数値０が続くことになっておりました。

とはいえ、12月23日、12月24日だけでなく、10月末であっても「クリスマス」を検索する人は当然いるはずですが、APIの100件データしか取れない私にとってはどうしようもありません。

１００位以内に現れない日＝０を補完するためには、プログラムでどうにかして補正するしかありませんでした。

キーワード：クリスマスのログ

データを見てもらえればよく分かると思いますが、キーワード「クリスマス」でSELECTしてみると年末時期しかレコードが出てこないといった感じになっておりました。

データを月別集計で抽出する

そこで思いついたものが、googleトレンドのように過去12か月分、過去5年間分といった長い期間でトレンド推移を見る、まさにあのようなグラフでした。

日次で歯抜けがあるデータでも、月次で丸めたグラフにしてしまえば多少意味あるものが見れるだろうといった感じです。そこで、上記の歯抜けログデータを使って、月次集計されたデータの抽出をすることを試みました。

グラフ（chart.js）生成に必要となるデータ

キーワードツールでグラフ表示に使っているものはchart.jsですが、線グラフを作るためには、X軸に置かれるラベル側の配列と、そのラベル分に相当するY軸側のデータ配列を用意することが必要です。下のサンプルでいうと、’1月’, ‘2月’, ‘3月’…と、100,150,200…といった部分になります。

type: 'line',
data: {
　　　labels: ['1月', '2月', '3月', '4月', '5月'],
　　　datasets: [{
		　　label: '月間推移',
					backgroundColor: window.chartColors.red,
					borderColor: window.chartColors.red,
					data: [100,150,200,180,100],
					fill: false,
				},

このため、歯抜け日次データを月次でSUMし、それをchart.js用に配列にするといった流れで進めていけば良さそうです。

歯抜け日次データを月次集計してもダメ

ただし、このまま歯抜け日次データを月別にSELECT SUM～GROUP BYをしても、データが存在しない月は集計をされないため、下記のような結果ができてしまいます。

2017年10月、11月、12月の次は2018年10月、11月、12月といった感じで飛んでしまうので、グラフにしたてることができません。

歯抜け日次データの月次集計方法

欲しいデータを取得するためには、SQLで歯抜けを埋める作業が必要です。そこで、歯抜け日次データをSQLで埋めることを考えてみます。

歯抜けデータを埋める最も簡単なやり方は、日付マスタテーブルを必要期間分別途作り、その日付マスタ JOIN 日次データで、欠けがあるデータ日付分を０埋めしてしまうという方法です。

JOINした連続日付データを月別でまとめてSUMするといった流れでいけば、１月～１２月までの続いたデータにすることができそうです。ちなみに、この歯抜けデータの日付埋めについてはこちらのサイトがとても参考になると思います。

肝心の日付マスタですが、仕事で使ってるシステムのDBに数十年分のカレンダーマスタが置いてありましたので、今回はそのデータをそっくり流用してしまいました。

もちろん、手元にお持ちでなくても、「SQL　カレンダー　テーブル　作成」なんかで調べれば、テーブル作成のマニュアルが出ますので、簡単に作れると思います。

カレンダーマスターとJOINするSQL

カレンダーマスターが準備できればあとはクエリを書くだけです。まずは検索キーワードのIDでログから抽出を考えます。

SELECT 
  *
FROM
  log_keyword
WHERE
  keyword_id = **キーワードID**

これを先程用意したカレンダーテーブルにLEFT JOINしつつ…月別でSUM集計といった感じにしていけばほぼ完成です。

SELECT
	DATE_FORMAT(calender.YYYYMMDD, '%Y-%m') AS YYYYMM,
	SUM(IFNULL(Exdata.score,0)) AS score_summary
FROM
	calender
LEFT JOIN
	(SELECT
		*
	FROM
		log_keyword
	WHERE
		keyword_id = **キーワードID**) Exdata
ON
	calender.YYYYMMDD = Exdata.YYYYMMDD
GROUP BY
	DATE_FORMAT(calender.YYYYMMDD, '%Y%m');

2年間分程度をBETWEENで切りだせば元となるクエリの完成となりました。あとはこのクエリをPHPに組み込み、**キーワードID**に検索されたキーワードのIDをbindValue()すれば、検索ワードのデータを月別サマリーした形で二年分取得ができました。

取得データをchart.jsに設定

この抽出した月別スコアサマリの配列をChart.jsに必要なJavascriptに流し込めばグラフの完成です。完成したものがこちら。

なんとなく、キーワード「クリスマス」は11月、12月に盛り上がるキーワードだな～ということが分かるグラフができたのではないでしょうか。

他にも、キーワード「母の日」なんかで見ると、4月、5月頃に盛り上がるなぁといったことが分かるようにはなりました。

そんなものGoogleトレンドでもっと簡単に正確なものが見れるわ！とは私も思ってしまいましたが、何も考えずに思い付きで貯め始めてしまったデータを、少しでも有効活用する為に考えた苦肉の策ということで、是非参考程度にご活用いただければとは思います。

おわりに

APIデータを貯め始めてから早６６６日もたっておりました。時のたつのが早すぎで怖くなる今日この頃です。