Help us understand the problem. What is going on with this article?

ディレクターがSQL書いてキーワード分析に取組んでみた

目次

  • やってることの説明(プロジェクト概要)
  • 最終的なアウトプット
  • 工夫した点
    • 頻出単語
    • トレンドワード
    • wikipedia
  • 使い道・フィードバック
  • 今後の展開

プロジェクト概要説明

 GMOアドマーケティングでは広告配信を行う上で、WEBサイトのコンテンツを解析し、コンテンツにマッチさせた広告を配信しています。
そこで解析した内容を活用し、さらに「広告訴求したいページ(広告主サイト)を閲覧しているユーザーがどんな内容に興味を持っているのか?」を分析することで、ユーザーの興味を事後的に類推する試みを行いました。
スクリーンショット 2019-12-17 23.17.47.png

最終的なアウトプット

上記の概要図内の、以下がアウトプットとなります。
1. 特徴キーワード
2. インサイトグラフ

スクリーンショット 2019-12-17 17.39.33.png
※「インサイトグラフ」のイメージ図

分析手順

  1. WEBサイトコンテンツの収集・クローリング

  2. 収集したコンテンツ(文章)をキーワード(単語)に分割し、文章毎におけるキーワードの特徴量(重要度)をポイント付け

  3. キーワード毎の以下を分析し、特徴が出るように加工

    • 特徴量(重要度)ポイント
    • 出現回数
    • 広告主サイトコンテンツを読了したユーザー数(ユーザー分子)
    • 収集した全WEBサイトにおける接触ユーザー数(ユーザー分母)

4.ビジュアライズ

このうち、1〜2は既に実施済みでデータが揃っていましたため、今回は3〜4について触れたいと思います。
※MeCabやWord 2 Vecを使った自然言語処理や、最近ですとテキストマイニングを行ってくれる便利なAPIがIBM Watson,google,Microsoft Azure等各クラウドサービスから出ており、比較的ハードル低く文章解析を行えるようになっています。

工夫した点

2までの状態で、データベース(Google BigQuery)には以下テーブルが存在します。

[A] 解析済コンテンツデータ( Analysis_data )
id(コンテンツID) URL text(コンテンツ文章) word(単語と単語の特徴量)
bc0da25d3c9dda3ec https://hoge.com/aaa MeCabへユーザー辞書を追加する方法 ["MeCab:0.374687","ユーザー辞書:0.109837","形態素解析:0.092837","追加:0.0372574"]
236e32fb880c61b https://example.com/123 プライドが邪魔!?男性が「好意がある女性」を避けてしまう理由 ["女性:0.184182","好意:0.114299","プライド:0.109852","周り:0.095720","男性:0.090242"]
0c24c49ad9d608a https://test.jp/bbb シンガポールの楽しい「写真映えスポット」4選 ["マーライオン:0.300851","シンガポール:0.224016","写真:0.094375","写真映えスポット:0.037698"]
7181e1a735d14b https://sample.co.jp/222 キャスター付けたら使いやすい!可動式にして動線が良くなった暮らしの実例 ["キャスター:0.346787","実例:0.123786","可動式:0.122526","台車:0.081578"]
[B]他サイト含むWEBサイトアクセスログ( All_log
id(コンテンツID) URL user_id
7181e1a735d14b https://sample.co.jp/222 pzzygJ8GPLANLoT8
5df8f8a9031d https://qiita.com/aaa Cy5CRevXE1kqc2Bt
0c24c49ad9d608a https://test.jp/bbb pzzygJ8GPLANLoT8
a933bfd6q2d31 https://techblog.gmo-ap.jp/ ZfkWMSc3sMXvJFJq
[C]広告主サイトのWEBサイトアクセスログ( advertiser_log
user_id read_flg
oJGbvrkR7RrjEHyr 0
pzzygJ8GPLANLoT8 1
sXAf0VvAn6xmkluE 1
4ywA9tt0fXDO9M0I 0

「広告訴求したいページ(広告主サイト)を閲覧しているユーザーがどんな内容に興味を持っているのか?」
を分析したいので、
 1.広告主サイトを閲覧しているユーザーadvertiser_log.user_id
 2.他のサイトでどのような内容に触れているかAnalysis_data.wordを付け合わせ
 3.単語(キーワード)毎の閲覧ユーザー数
を出すことで、より多くの「広告主サイトを閲覧しているユーザー」が外部サイトで興味を持っているキーワードが何かを調べます。

今回はあるファッションECサイトの例で分析してみることにします。

#standardSQL
WITH
  # 広告主訪問ユーザー
  USER AS (
    SELECT
      user_id
    FROM
      `advertiser_log`
    WHERE
      read_flg = 1
  ),
  # コンテンツを訪れたコンテンツIDとユーザー数
  ARTICLE_IDS_USER_NUM AS (
    SELECT
      id,
      COUNT(*) AS count
    FROM
      All_log
    GROUP BY id
   ),
 # 単語を含んだ記事と広告主訪問ユーザー
  KEY_ARTICLE_USER AS (
    SELECT
      SPLIT(tmp, ':')[OFFSET(0)] AS key,
      SPLIT(tmp, ':')[OFFSET(1)] AS importance,
      miun.id AS id,
      miun.count AS user_count
    FROM
      `Analysis_data`
    # joinし訪問ユーザー数取得
    INNER JOIN ARTICLE_IDS_USER_NUM AS miun
            ON miun.id = id
    WHERE
      id IN (
        SELECT
          id
        FROM
          ARTICLE_IDS_USER_NUM
     )
    ORDER BY
     miun.count DESC,
     key DESC
    )

SELECT
    key,
    importance,
    user_count
  FROM
     KEY_ARTICLE_USER
  ORDER BY
     user_count DESC

より濃いデータ抽出を行うために、read_flg = 1を指定し、広告主サイトを読了したユーザーを対象に絞り込みました。

しかしながらこの結果で得られるキーワード毎のユーザー数は以下のようになります。
どのコンテンツにも登場しそうな、一般的なキーワードが列挙されており特徴を捉えられていません。

key importance user_ct
こと 0.021883 121893
1 0.036271 84974
もの 0.025256 51238
2019 0.011956 31047
0.014465 29862
0.009523 26354
女性 0.010214 20908

そこで、一般的なキーワードはよく出現しますので、それらをノイズ除去するために「頻出単語」を割り出して、省けるようにします。

頻出単語

上記の抽出結果のkeyimportanceの通り、「単語と単語の特徴量」を把握できています。
この特徴量は、各文章毎に文章内におけるその単語の重要性(文章構成上の意味合いの強さ)を表すポイントです。
そのためキーワード毎に集計し、「全文章のポイント平均が低いキーワード」=「どの文章内においても重要度(特徴量)が低いキーワード」ということになります。

 SELECT
   RANK() OVER (ORDER BY COUNT(*) DESC) AS rank,
   key,
   AVG(importance) AS importance,
   COUNT(*) AS count,
 FROM ENTITY_TYPE_EXCEPT
 GROUP BY
   key
ORDER BY
 rank;

上記のようにキーワード毎の平均ポイントを出すことで、以下の結果を得ます。

rank key importance count
1 こと 0.021883 128693
2 1 0.036271 84974
3 もの 0.025256 51238
4 0.014465 29862
5 0.0131292 22441
6 0.009523 26354
7 女性 0.059770 21893

このテーブルを先ほどの結果とJOINしてあげ、importance > 0.05 rank > 100など低い値をフィルタしてあげますと頻出単語(ノイズ)が省かれた結果を得ることができます。

key importance rank user_count article_count
コーディネート 0.0556 251 709 16,292
神田沙也加 0.1341 207 7,895 1,351
彼女 0.0540 204 112 177,199
髪型 0.0703 189 749 15,605
プチプラ 0.0552 179 1,264 9,469
容疑者 0.0720 177 363 29,694
沢尻エリカ 0.1031 175 938 12,631
画像 0.0803 168 138 328,551
壇蜜 0.1184 163 282 11,650
レディースコーデ 0.0943 360 190 126,157

かなり頻出単語がフィルタされました。今回はファッション関連の商品ページを見ているユーザーの興味ワードを分析していたため、当初の仮説通りファッション関連のワードが浮かび上がってきました。

しかし、まだ一部のノイズワードが混ざってしまっています。
それは「トレンドワード」です。
トレンドワードは、特定の広告主のサイトに訪問したことがある人がよく閲覧しやすいわけではなく、全ユーザーの傾向として特定の時期に閲覧回数が増えるため、これが混ざっていると特徴としては捉えにくくなってしまいます。
そのため、「トレンドワード」を定義し、それを除外するようにします。

トレンドワード

今回使用した[B]他サイト含むWEBサイトアクセスログ( All_log ) のテーブルは日毎にテーブルを作成しBigQueryに保存していましたため、この日毎の出現回数の差分を比較し、増加率を求めたいと思います。
キーワード毎の増加率を上記の結果にJOINします。

#standardSQL
CREATE TEMPORARY FUNCTION
  TABLE_YMDH() AS (FORMAT_TIMESTAMP("%Y%m%d%H", TIMESTAMP "2019-12-06 07:00:00", "UTC"));
CREATE TEMPORARY FUNCTION
  CURRENT_YMD() AS (FORMAT_DATE("%Y%m%d", DATE (2019,12,04)));
CREATE TEMPORARY FUNCTION
  PREV_YMD() AS (FORMAT_DATE("%Y%m%d", DATE (2019,12,03)));
WITH
  CURRENT_ENTITYS AS (
    SELECT
      SPLIT(REGEXP_REPLACE(entities, r'[\[\]\"]', '')) AS entities
    FROM
      `Analysis_*` AS c
    WHERE
      _TABLE_SUFFIX = TABLE_YMDH()
      AND FORMAT_TIMESTAMP("%Y%m%d", created, "Asia/Tokyo") = CURRENT_YMD()
  ),
  CURRENT_ENTITY_SPLIT AS (
    SELECT
      SPLIT(tmp, ':')[OFFSET(0)] AS key,
      SAFE_CAST(SPLIT(tmp, ':')[SAFE_OFFSET(1)] AS FLOAT64) AS importance,
    FROM
     CURRENT_ENTITYS
  ),
  CURRENT_ENTITY_TYPE_EXCEPT AS (
    SELECT
      key,
      importance
    FROM CURRENT_ENTITY_SPLIT
    WHERE key != ''
  ),
  CURRENT_TMP AS (
     SELECT
       key,
       COUNT(*) AS count
     FROM CURRENT_ENTITY_TYPE_EXCEPT
     GROUP BY
       key
   ),
  PREV_ENTITYS AS (
    SELECT
      SPLIT(REGEXP_REPLACE(entities, r'[\[\]\"]', '')) AS entities
    FROM
      `Analysis_*` AS c
    WHERE
      _TABLE_SUFFIX = TABLE_YMDH()
      AND FORMAT_TIMESTAMP("%Y%m%d", created, "Asia/Tokyo") = PREV_YMD()
  ),
  PREV_ENTITY_SPLIT AS (
    SELECT
      SPLIT(tmp, ':')[OFFSET(0)] AS key,
      SAFE_CAST(SPLIT(tmp, ':')[SAFE_OFFSET(1)] AS FLOAT64) AS importance
    FROM
      PREV_ENTITYS
  ),
  PREV_ENTITY_TYPE_EXCEPT AS (
    SELECT
      key,
      importance
    FROM PREV_ENTITY_SPLIT
    WHERE key != ''
  ),
  PREV_TMP AS (
     SELECT
       key,
       COUNT(*) AS count
     FROM PREV_ENTITY_TYPE_EXCEPT
     GROUP BY
       key
   )
   SELECT
     ct.key,
     ct.count AS ct_count,
     pt.count AS pt_count,
     ct.count /  pt.count AS rate_up
   FROM CURRENT_TMP AS ct
   LEFT OUTER JOIN PREV_TMP AS pt
   ON ct.key = pt.key
   WHERE
   ((ct.count > pt.count OR pt.key IS NULL) )
   ORDER BY rate_up DESC, ct.count DESC
key importance rank user_count article_count rate_up
コーディネート 0.0556 251 709 16,292 1.431
神田沙也加 0.1341 207 7,895 1,351 15.357
彼女 0.0540 204 112 177,199 1.341
髪型 0.0703 189 749 15,605 1.501
プチプラ 0.0552 179 1,264 9,469 1.133
容疑者 0.0720 177 363 29,694 12.747
沢尻エリカ 0.1031 175 938 12,631 15.357
画像 0.0803 168 138 328,551 1.429
壇蜜 0.1184 163 282 11,650 23.500
レディースコーデ 0.0943 360 190 126,157 1.203

最終的に得られたrate_upについて、rate_up < 10 など増加率の高いワードをフィルタし除いてあげるとトレンドワードを省くことができます。

さらに、それでも「画像」「彼女」といった曖昧な単語が混ざってしまっております。
頻出単語で省ければ良いのですが、他の単語と比較するとそこまで頻出しないということもあり除きにくいワードでした。
そこで、wikipediaのデータをうまく活用することにします。

wikipedia

wikiediaには「曖昧さ回避」というカテゴリーが存在します。
これは文脈によって内容が異なる単語(曖昧な単語)を判別しやすくするためのラベルづけとしてWwikipediaが設定しているものです。

この「曖昧さ回避」ラベルがついている単語はノイズになりやすいので、除外できるようにします。
wikipediaのデータをBigQueryで使えるようにする方法については、こちらで紹介しています。
Wikipediaデータ MySQLを経由してBigQueryにLoadする

key(単語)を軸にJOINし、フィルタできるようにします。

key importance rank user_count article_count rate_up wiki
コーディネート 0.0556 251 709 16,292 1.431 0
彼女 0.0540 204 112 177,199 1.341 1
髪型 0.0703 189 749 15,605 1.501 0
プチプラ 0.0552 179 1,264 9,469 1.133 0
画像 0.0803 168 138 328,551 1.429 1
レディースコーデ 0.0943 360 190 126,157 1.203 0

使い道・フィードバック

上記の通り上手くノイズ除去していくとそれなりに意味がありそうなデータがアウトプットできました。
最終的なアウトプットで想定していた1. 特徴キーワードです。

key importance rank user_count article_count rate_up wiki
コーディネート 0.0556 251 709 16,292 1.431 0
髪型 0.0703 189 749 15,605 1.501 0
プチプラ 0.0552 179 1,264 9,469 1.133 0
レディースコーデ 0.0943 360 190 126,157 1.203 0
クリスマスデート 0.0871 394 168 141,868 1.577 0
ニットタイトスカート 0.1660 846 363 1,006 1.400 0

これをgoogleデータポータルや、スプレッドシートに出力し、バブルチャートでビジュアライズしたものが以下の2. インサイトグラフになります。
スクリーンショット 2019-12-17 17.39.33.png

縦軸「キーワード特徴量」はimportance
横軸「訪問ユーザー数」はuser_count(広告主サイト訪問ユーザー数)
バブルの大きさはarticle_countはキーワードが含まれるコンテンツの総数です。

こうしてみると、

  • 自社サイト(広告主サイト)へ訪問しているユーザーが、他サイトではどんな内容(キーワード)に興味を持っているのか?
  • そのキーワードはよく出てくるワードなのか、ニッチなワードなのか?

を調べられるので、キーワード分析からユーザーインサイトを発掘することができ、さらにそのキーワードに対しての重要性も判断できることになります。

実際にこちらのレポートを広告主の方にご覧いただいたところ、実際のWEBサイトコンテンツ戦略にかなり当てはまっており、かつ自社では把握できていなかったユーザーインサイトの発掘につながったとのフィードバックでした。

今後の展開

  • 近いキーワード毎にクラスタリング
  • キーワードのジャンル毎にラベルづけ
  • Google Trendsや、Googleキーワードプランナーなどの検索ボリュームのデータと付け合わせ

等できると、より分析のしがいがあるなと感じています。

明日は@sandatsさんの GKEとCloud SQLを使ってhelmでRedmineを建ててみる のお話です。
お楽しみに!

Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした