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

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

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

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

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

キーワードのトレンド

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

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

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

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

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

Yahoo検索APIから検索ボリュームデータが取れないので日別グラフが構成できない
キーワード:クリスマスの数値の無いグラフ

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

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

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

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

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…といった部分になります。

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-1050
2017-111200
2017-1215000
2018-1045
2018-111500
2018-1216000

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

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

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

歯抜けデータを埋める最も簡単なやり方は、日付マスタテーブルを必要期間分別途作り、その日付マスタ 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か月で見る
キーワード:クリスマスの24か月推移

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

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

母の日というキーワードの検索ボリュームを24か月で見る
キーワード:母の日の24か月推移

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

おわりに

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

APIで取得した検索キーワードのログが6万レコードをこえました
集計日数が666日・・・ログも66,700レコードに

ABOUT dtn.jp

この運営ブログをみてdtn.jpの運営サイトに興味をお持ちになった方は下記のリンク集からサイトをご参照ください。PHP・SQL・JS・HTML・CSSや様々なAPIでゼロから立ち上げたサイトを複数運営しています。

dtn.jp関連サイト

  • ディレクトリ型検索エンジンdtn大手サイトは全てサービスを終了し、中小でも運営を継続するところがほとんどなくなってしまったディレクトリ型検索エンジンを2002年から細々運営しており、未だに細かい機能改修も行っております。申請されたサイトは実際に訪問して細かく確認をしているので登録までかなりお時間いただきます。
  • 猫ネコサーチエンジン猫・ペットサイト専用のディレクトリ型検索エンジンです。登録サイトはサイトのスクリーンショット付きでカテゴリに掲載しています。
  • 画像掲示板の泉画像やYouTubeやニコニコ動画が貼り付け可能な匿名画像掲示板です。27ジャンルに分かれた掲示板に、それぞれ100スレッドまで立てることが可能です。
  • ツイータンTwitter APIを使って10分おきに収集したTwitter(ツイッター)のトレンドワード・ハッシュタグを月別ランキングでご紹介中。キーワードトレンド推移やハッシュタグツールもあります。
  • Keyword Tool++Yahoo APIや楽天サイトのデータを日次で収集したデータを元に、EC系キーワードをラインキング形式でご紹介しています。各ワードは検索ボリュームや関連語、24か月の検索ボリュームトレンドを確認も可能です。
  • 消費者金融の泉カードローン商品を取り扱う金融業者を一覧で比較可能としたサイトです。カテゴリ別、人気度別、限度額別、金利別に比較可能なサイトにしています。
  • TrendinGファッションモールに出店するブランドをアイテムヒット率でランキングとしたサイトです。まだβ版です。

各サイトの開発方法などについて

各サイトの開発方法や運用トラブルについては、当ブログで不定期にご紹介しています。Twitterで新着記事のお知らせもしています。宜しければフォローどうぞ。