BigQueryで学ぶ！実践SQLデータ分析：ITサービス改善につなげよう

BigQueryを使用するための手順を実際の画面と共に解説していきます。

このコースで分析対象とするITサービス Stackoverflow とは何か説明します。

レクチャー３の環境構築１で利用可能にした Stackoverflow のデータについて説明します。

ITサービス改善の全体的な流れを説明します。

サービスが改善するまでのモデルをどの様に作成するか理解します。

Stackoverflow での例とともに説明します。

現状把握をどの様に進めるか説明します。

SQLでの分析でよく使う「時間」に関するデータ型について学びます。

「時間」のデータ型の種類、各データ型の使い方、変換方法を説明します。

クエリ内で同じ記述を繰り返す時に便利な、ユーザー定義関数について学びます。

Stackoverflow のPV数の集計方法を解説します。

質問数、質問UU数、一人当たり質問数を集計する方法を解説します。

回答数、回答UU数、一人当たり回答数を集計する方法を解説します。

文字列に対して処理を行うことができる便利な関数を学習します。

文字列に対して複雑な処理を行いたい場合に利用できる正規表現を用いる関数を紹介します。

質問・回答の質の指標として文字数を集計する方法を解説します。

時間に関するデータを「計算する」ための関数を学習します。

質問・回答の質の指標としてユーザーから高評価を受けた質問・回答数を集計する方法を解説します。

質問者の満足度として、7日以内の被回答率を集計する方法を解説します。

現状把握分析の結果からイシューの特定を行う方法を解説します。

Window関数の書き方と挙動を解説します。

前の行や後ろの行のデータを取得することができるWindow関数を学習します。

サービス改善のステップの中の、「問題の原因特定」の進め方を解説します。

縦型のデータを横型に変換するクエリの書き方を学習します。

質問UU数がサービスのイシューだった場合の問題特定の分析を行います。

質問UU数を新規・既存などとセグメント分けして集計します。

質問UU数がサービスのイシューだった場合の問題特定の分析を行います。

新規質問ユーザー数を更に分解し、新規登録者数と利用率の集計を行います。

データにnullが含まれる時にnullを変換する方法と、0除算エラーを避ける方法を学習します。

質問UU数がサービスのイシューだった場合の問題特定の分析を行います。

新規・既存ユーザーの翌月継続率を集計します。

質問・回答の頻度がサービスのイシューだった場合の問題特定の分析を行います。

どの程度の時間間隔でユーザーが質問・回答を行っているか確認するために、ヒストグラムを作成します。

配列から要素を取り出す方法、配列の長さを取得する方法を学習します。

以下の基本統計量を算出する方法を学習します。

• 平均値

• 最大値

• 最小値

• 標準偏差

• 中央値

• 最頻値

質問・回答の頻度がサービスのイシューだった場合の問題特定の分析を行います。

直近の質問・回答日(Recency)と、過去の質問・回答回数(Frequency)を集計します。

質問・回答の頻度がサービスのイシューだった場合の問題特定の分析を行います。

過去３ヶ月の頻度と今月の頻度の関係を集計します。

質問・回答の質がサービスのイシューだった場合の問題特定の分析を行います。

過去の質問・回答の回数と質の関係を分析します。

質問者の満足度がサービスのイシューだった場合の問題特定の分析を行います。

現状確認として、被回答率と被回答までにかかる時間を集計します。

質問者の満足度がサービスのイシューだった場合の問題特定の分析を行います。

質問に付けられたタグ数が多いほど被回答率が高まるのではないか、という仮説を検証します。

解決策の検討の進め方の概要を説明します。

例として「質問者の継続率」を上げることを考えます。

まずは改善したい指標と相関する指標を探し出します。

質問への被回答数と質問者の継続率に相関があるか分析します。

質問への高評価数と質問者継続率が相関するか分析します。

質問投稿から回答までの早さと質問者継続率に相関があるか分析します。

質問・回答を両方利用していると、質問の継続率が高まるか分析します。

改善したい指標と相関する指標を元に、施策方針を策定する考え方を解説します。

施策方針の優先順位をどのように決めるか解説します。

施策方針の効果を見積もる方法を解説します。

例として「12時間以内の被回答率を高める」ことで得られる、質問者継続率の改善幅を見積もります。

施策方針をどのように具体化していくか解説します。

例として「１２時間以内の被回答率を高める」施策を考えます。

Google Anaaalytics4 プロパティ(GA4)の特徴を解説します。

GA4 のデータ構造と類似したFirebaseのサンプルデータを利用可能にします。

Google BigQuery に特有な機能として、複数のテーブルを一つの記述で表現できる機能があります。

ここでは、その機能である _table_suffix を紹介します。

配列をテーブルへと変換したい場合があります。

このような場合に、有効なunnest関数についてここでは学習します。

GoogleAnalytics4 の類似データを用いて、PV, UU, Session数を集計する方法を解説します。

GoogleAnalytics4 の類似データを用いて、ページ別にランディング数、直帰数、離脱数、滞在時間などを集計する方法を解説します。