キーワードのトレンド

先日、キーワードツールを多少修正してみたとブログに書きました。

Yahoo APIでは、日次で人気キーワードを100位までランキング形式で取得することができるので、そのデータを毎日DBに貯めておき、直近一カ月、半年、一年間といった期間で集計して、キーワードをランキング形式で見えるようにしたものでした。詳細はこちらの記事でご紹介しています。

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

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

キーワード:クリスマスの数値の無いグラフ

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

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

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

12月23日、12月24日だけでなく、10月末であっても「クリスマス」を検索する人は当然いるはずですが、100位以内に現れない日=0を補完するためには、プログラムでどうにかして補正するしかありません。

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

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

log_idword_iddate
2225 252017-12-21
2328 252017-12-22
33986252018-11-02 (←ここから2018年)
33998 252018-11-03
キーワード:クリスマスのログデータ

とはいえ、プログラムで数値をうまく増減させて架空データをこねくりだすのも少々微妙な気がしたので、別の何かを考えてみることにしました。

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

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

Googleトレンドのキーワード:クリスマスの5年推移

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

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

キーワードツールでグラフ表示に使っているものはchart.jsですが、線グラフを作るためには、X軸に置かれるラベル側の配列と、そのラベル分に相当するY軸側のデータ配列を用意することが必要です。

下のサンプルでいうと、’1月’, ‘2月’, ‘3月’…と、100,150,200…といった部分になります。この歯抜け日次データを月次集計し、それをchart.js用に配列にするといった流れで進めていけば良さそうです。

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,
				},

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

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

年月検索ボリューム
2017-1050
2017-111200
2017-1215000
2018-1045
2018-111500
2018-1216000

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

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

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

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

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

肝心の日付マスタですが、仕事で使ってるシステムの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に流し込めばグラフの完成です。完成したものがこちら。

キーワード:クリスマスの24か月推移

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

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

キーワード:母の日の24か月推移

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

おわりに

それにしても、APIデータを貯め始めてしまってから早666日もたっていました。時のたつのが早すぎですね。

集計日数が666日・・・ログも66,700レコードに

キーワードツール: https://www.shop.dtn.jp