先日、キーワードツールを多少修正してみたとブログに書きましたが、季節性のあるようなキーワード、例えば「クリスマス」といったキーワードを今(9月末頃に)検索してみても、直近~30日前、その前期間(30日前~60日前)にはデータが入っていないので、真っ平らなグラフしか表示されていないといった状態になることに今更ながら気が付きました。

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

その理由ははっきりしてまして、キーワードツールのデータは、Yahoo!ショッピングAPIのキーワードランキングデータを日次で貯めたものを使っていたのですが、APIで取れるデータは日の上位100件分までしか最大でも取ることができず、100位以内にキーワードが現れない日は=0と同じになってしまう、ということが原因とは見てすぐ分かりました。

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

log_idword_iddate
2225 252017-12-21
2328 252017-12-22
33986252018-11-02 (←ここから2018年)
33998 252018-11-03

もちろん、2017年12月23日や24日、9月末であっても、キーワード「クリスマス」を検索する人はいるはずですが、こちらはTOP100位分しかデータをいただけないデータ乞食の身ですので、100位以内に現れない日=0を補完するためには、プログラムでどうにかして補正するしかないといった感じです。

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

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

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

日次で欠けがあるデータであっても、月次幅で丸めたグラフにしてしまえば多少意味あるものが見れるだろうといった、適当な思い付きですね。。

さて、このgoogleトレンドのようなグラフを作るのに必要となるものが月次で集計された配列のデータです。

dtnキーワードツールでグラフ表示に使っているものは皆さんお馴染みの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,
				},

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

ただし、このまま歯抜けデータを月別にSELECT SUM~~~GROUP BYをしても、2017年11月,2017年12月のあとが飛んでしまい、次は2018年11月、12月といった感じのデータが並んでしまうだけですので、歯抜けを埋める作業がまず必要そうです。そこで、歯抜け日次データをSQLで埋めることをまずは考えてみます。

歯抜けデータを埋める最も簡単なやり方は、日付マスタテーブルを必要期間分別途作り、日付マスタ LEFT JOIN 検索スコアデータで、欠けがあるデータ日付分を0埋めしてしまうという方法です。この連続日付データを月別でまとめてSUMするといった流れでいけば、1月~12月までの続いたデータにすることができそうです。ちなみに、歯抜けデータの日付埋めについてはこちらなんかが参考になると思います。

肝心の日付マスタは、仕事で使ってるシステムDBに数十年分のカレンダーマスタが置いてあったので、そのデータをそっくり流用してしまいましたが、お持ちでなくても「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に必要なJavascriptに流し込めばグラフの完成です。完成したものがこちら。

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

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

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

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

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

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

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

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

スポンサーリンク