SNS分析まとめ②Natural Language APIで数値化したデータBigQueryにアップし成形する

2022年11月21日2023年3月27日

Natural Language APIを使用して、ツイートを数値化し、それをBigQuery にアップして分析します。記事は３つに分けており、この記事は２番目のBigQuery にデータをアップして成形する部分です。

目的と目標

本分析の目的は「潜在ニーズの表出」と「SNS(twitter)からのサイトへの流入」であり、本分析の目標は「この単語を使うとインプレッション率（サイトURLクリック率）が上がりますよ！」と言えるようになることです。手順は以下の通りです。

GASを使用して、スプレッドシート経由でツイートを自然言語処理
数値化されたツイートをBigQueryにアップ
データポータルで可視化、BQMLで分析

この記事では手順２の数値化されたツイートをBigQueryにアップ部分の記事です。

BigQueryにデータをアップ

データをBigQueryにアップする際にいくつかの問題点がありました。今回はどの問題点の解決法補を載せておきます。

・BigQueryにスプシ形式をアップするやり方
・ツイートの数値化データとアナリティクスデータを結合しながらテーブルを作成
・フィールド名がバグってしまう。
・時間データが文字列型（String型）で入ってしまう。

BigQueryにスプシ形式をアップするにあたって

スキーマの自動検出 チェックを入れておくと、自動的にスキーマ（データ形式）を検出してくれます。

引用された改行 今回のデータの場合は「引用された改行」にチェックを入れないとアップすることができませんでした。もしエラーが発生するようであれば試してみてください。

ツイートの数値化データとアナリティクスデータを結合しながらテーブルを作成

「Natural Language API」の分析結果のnameごとにツイート本文をつけていたので、今回はツイート本文を使いテーブルを結合します。

SELECT
*
FROM
`テーブル名A`
JOIN
`テーブル名A`
ON
テーブル名Aのツイート本文のカラム名 = テーブル名Bのツイート本文のカラム名

二つのテーブルのを結合したいときに、同じ定義のカラムがあるなら簡単にテーブルの結合をすることができます。

フィールド名がバグってしまう

BigQueryではカラム名に日本語が入っていると日本語が「＿」に変換されてしまい、データの使い勝手が悪くなってしまいます。解決方法

--データセットIDとテーブルIDを指定する
ALTER TABLE `<datasetID>.<tableID>`
--<変更したいフィールド名>と<変更した後のフィールド名の名前を入力>
RENAME COLUMN <fieldNameA> TO <fieldNameB>,

このSQLはフィールド名をからに変換するものです。これを使用して英数字を使用したフィールド名に変換しましょう。

時間データが文字列型（String型）で入ってしまう

今回渡されたTwitterアナリティクスデータはスプシ形式でした。BigQueryにアップした際に「2022-01-01 00:00 +0000」がString型で入ってしまいっました。解決方法

SELECT
カラム名
,timestamp(concat(trim(rtrim(times,'+0000')),':00')) as DATETIME1
FROM
`テーブル名`

今回のデータが「2022-01-01 00:00 +0000」で入力されていたためこの形のコードになっています。時間型に変換するには、どんな形のString型で入ってきたかによってキャストを変えなくてはなりません。今回のコードはその一助としてお使いください。

まとめ

BigQueryを使用したのが初めてだったので、どういう風にデータが入るのかなどで戸惑う点が多くありました。時間型に関しては自分のデータ型に合わせてトリムしなくてはならないので、どうやれば指定ができるのかなどをしっかりと調べる必要があります。今回載せている問題点はすごく簡単な部分ではありますが、一度はつまずく点だと思いますので、その解決の手助けとして役立ててください。

URLをコピーしました！

URLをコピーしました！

この記事を書いた人

keito.ariji