BigQuery

背景 Google AnalyticsをBigQueryに連携してSQLで分析しようという時、event_params や user_properties が 1 2 3 4 5 6 7 8 9 10 11 ARRAY< STRUCT< key STRING, value STRUCT< string_value STRING, int_value INT64, float_value FLOAT64, double_value FLOAT64 > > > という大変扱い辛い型のため、以下のようにサブクエリを多用したSQLを度々書いていく必要があります。 1 2 3 4 5 6 7 8 9 SELECT TIMESTAMP_MICROS(event_timestamp) event_timestamp, (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS page_location, (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_title') AS page_title, ... FROM `analytics_123456789.events_*` WHERE _TABLE_SUFFIX BETWEEN '20240101' AND '20240131' このSQLの結果をデータマートにすれば解決かと思いきや、event_paramsにプロパティを追加する度にデータマートをメンテナンスする必要があり運用が非常に面倒です。...

BigQueryに格納されている行動ログを元にマーケティングオートメーションを構築、SendGridでメールを発射したいケースは多々あるかと思います。 MAツールはかなり高価なものが多いため、シュッと自前構築するためのサンプルコードを掲載します。 送信したメールの効果測定・分析方法についても少し触れます。 1. SendGridテンプレートの作成 公式ドキュメントを参考に、Dyamic templateを作成します。 詳細は割愛しますが、今回は以下のようなメールを送りたいとします。 1 2 3 4 5 6 7 foobar様への今週のおすすめ商品です！ 1. ほげほげ (1000円) 2. ふがふが (1500円) 購入はこちらから！ https://example.com テンプレートはこんな感じで記述します。 1 2 3 4 5 6 7 8 {{customer_name}}様への今週のおすすめ商品です！ {{#each products}} {{this.number}}. {{this.product_name}} ({{this.price}}円) {{/each}} 購入はこちらから！ https://example.com このテンプレートに対して、送信対象ごとに以下のようなデータを埋め込む事が目標になります。 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 { "customer_name": "foobar", "products": [ { "number": 1, "product_name": "ほげほげ", "price": 1000 }, { "number": 1, "product_name": "ふがふが", "price": 1500 }, ] } 2....

オペミスやat-least-onceセマンティクスによってINSERTされてしまった重複レコードを消すSQLです。 完全に同一な重複レコードを消す やる事は 重複レコードのうち最古のものを一時テーブルに退避 重複レコードを全て削除 一時テーブルから再度INSERT です。 Schema 1 2 3 4 5 6 7 8 9 10 11 12 [ { "mode": "REQUIRED", "name": "id", "type": "INTEGER" }, { "mode": "NULLABLE", "name": "value", "type": "STRING" } ] 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 CREATE TABLE project_name....