Googleアナリティクスデータでも、どんなデータでもそうですがデータを集計する期間を例えば2020年1月1日から1月31日までとすると、当たり前ですがその期間のすべての数値を集計することになります。 BQMLなりAutoMLで購入予測をしたいときに、前述のような…

いつもは自身のアウトプットの意味も含めてクエリをポストしていますが、今回は番外編ということでQwiklabsを紹介したいと思います。 私自身は、SQLはUdemy にあったwebアナリティクス界の巨人、木田さんのSQL講座で学び、GoogleCloudPlatformについてはQwik…

BigQueryは配列関数をサポートしており、それを使ってセッション数やPV数を出すこともできます。例えばユーザー単位でvisitidを配列にして、そのを数えると、ユーザーごとのセッション数を出すことができます。ARRAY_LENGHはドキュメントを見ると「配列のサ…

BigQueryに送られるGoogleアナリティクスのデータには、関連するフィールドをグループ化してスキーマを広げることができるデータ型があります。そのことを構造体というらしく、概念的には、構造体はメインテーブルに事前に結合された別テーブルのようなもの…

「Googleアナリティクスのセッションの間隔をだす」でセッションの間隔を出したのですが、トランザクションにも応用できるのでポストします。 select round(avg(interval_session),2) as avg_interval_session from (select fullvisitorid, paese_date('%Y%m…

Googleアナリティクス上ではユーザーあたりのセッション数はでていますが、セッション間の間隔は集計されていませんのでそれを出したいと思います。 select round(avg(interval_session),2) as avg_interval_session from (select fullvisitorid, parse_date…

「訪問回数ごとのユーザー分布を集計する」でユーザーの訪問回数ごとの分布をだしました。このエントリーでは、その分布にコンバージョンしたユーザー数を追加して、訪問回数単位のユーザーコンバージョン率を出したいと思います。 手順としてはコンバージョ…

Googleアナリティクスでライフタイムバリューのメニューができて久しいですが、BigQueryで顧客ごとのLifeTimeValueの算出とABC分析を行いたいと思います。 LTVの算出 ユーザー単位での一定期間の売り上げ総額を出すのは比較的簡単です。 select fullvisitori…

kmeansによるクラスタリングのエントリーでBigQueryMLを使って、サイト訪問者のクラスタリングを行いました。クエリ自体は凝ったものではなく、サイトに訪問するユーザーのデバイス・ブラウザ・アクセス場所等の情報でクラスタリングしていますので、実務で…

BigQueryにはいくつか統計集計関数がサポートされていますが、CORR関数を使って相関係数をだしたいと思います。 「BigQueryでサンプルデータを使う」のエントリーで紹介したGoogleアナリティクスのサンプルデータを使って、CVRと直帰率の相関係数を出してみ…

BigQueryを自分のパソコンで自習したいときってないでしょうか？アカウントは自身で作成できても、データがありませんよね。会社のデータを使うわけにもいかないでしょうし。。 ですが、ご安心ください。一般に公開されているデータを使うことができるように…

BigQueryMLで機械学習モデルを構築する BigQueryには標準SQLを使って機械学習を利用することができますが、BigQueryAPIが有効なプロジェクトでなければいけません。有効になっていなければこちらの「APIsを有効にする」から設定してください。 リファレンス…

BigQueryでセッションの開始日・時間・分・秒を出す 「BigQuery Export のスキーマ」には「visitStartTime」がありますので、これを使えばいいのですが、DATETIME型ではなくタイムゾーンもUTCになっています。このままでは、いつの何時何分からセッションが…

BigQueryでGoogleアナリティクスデータの差分を更新する クエリのスケジュール化の方法は「クエリの定期的実行を自動化する」で紹介させていただきましたが、Googleアナリティクスデータを日次で差分取り込みしたい場合は、前日分だけ取得するように書く必要…

BigQueryでGoogleアナリティクスデータの期間を指定する BigQuery内にあるGoogleアナリティクスデータの期間を指定するには、このように書きます。 select date, count(distinct fullvisitorid) as user, sum(totals.visits) as session, sum(totals.newvisi…

BigQueryのクエリの実行を定期的にスケジュールする BigQueryのSQL実行結果のテーブルをデータポータで可視化してモニタリングしているような場合、数字更新を自動化したいときがあると思います。そんなときはクエリ実行を自動化すると便利です。 まずはAPI…

特集ページのコンバージョン貢献 特集ページが見られたセッションで予約に至ったかどうかを把握する方法を紹介します。以前のエントリーでも書きましたが、GoogleアナリティクスのUI上でも特集ページごとにセグメントを設定すれば可能ですが、数が多いと面倒…

BigQuery内のGAデータからeコマースタグで送信された値を取得する 機械学習用のテーブルを作成しようと、Googleアナリティクスの拡張eコマースで送られた値を取得しようとしていたのですが、なかなかうまくいかず難儀していました。 BigQuery Export のスキ…

BigQueryでGoogleアナリティクスデータのページカテゴリ単位のPV数、セッション数を集計する GoogleアナリティクスのUI上なら、ディレクトリ単位のPV数の把握は容易にできますが、ディレクトリ単位のセッション数となるとセグメントをかける必要があります。…

ユーザーが一定期間に何回訪問したかを集計し、訪問回数ごとのユーザー数の分布をだしてみた。 Googleアナリティクスでは、ユーザー数や新規ユーザー数はわかりますが、訪問回数ごとのユーザー数を把握するにはやや手間がかかります。 今はユーザーエクスプ…

BigqueryでGoogleアナリティクスのトラフィック分析だ！ 自らのスキルアップのためにもSQLを使えるようになってBigqueryで分析するぞ！と思い、オンライン講座でSQLの基本的なことを学びBigqueryを使い始めました。 当然、使う上でいろいろ分からないことが…