Topics started by Masayuki_Yoshibe

10 Topics

ネイティブ派生テーブル(NDT)の活用方法

※ 本投稿はLooker Advent Caledar 2021 5日目の記事です。 派生テーブル LookerにはNative Derived Table(ネイティブ派生テーブル)という強力な機能があります。これを活用すると、わざわざ新規にSQLを書かなくても、既存のExploreを使うことで、かなり複雑なケースにも対応した派生クエリーを書くことができます。一方で、SQLとは異なりLookMLで記述する必要があるため、どうしてもSQLの方が書きやすいということで敬遠されることがあると感じています。そこで、ネイティブ派生テーブルがどのように利用されると良いか、今日は単純化した例を用いて説明させていただこうと思います。 ネイティブ派生テーブルの活用 以下のような注文ビュー(order_items)を基底ビューとしたシンプルな注文Exploreで考えてみます。注文Viewに対して、ユーザー情報、在庫製品、製品のビューをそれぞれ結合しています。explore: order_items {    join: users {    sql_on: ${order_items.user_id} = ${users.id} ;;    type: left_outer    relationship: many_to_one  }    join: inventory_items {    sql_on: ${order_items.inventory_item_id} = ${inventory_items.id} ;;    type: left_outer    relationship: many_to_one  }    join: products {    sql_on: ${inventory_items.product_id} = ${products.id} ;;    type: left_outer    relationship: many_to_one  }} ここで、たとえばカテゴリーごとの集計値を取りたい場合、どのようにすれば良いでしょうか。Exploreを使えば以下のような結果が簡単に手に入ります。 Explore実行結果1次に、カテゴリー及び個別のブランド別の集計が欲しい場合はどうでしょうか。この場合は、単純にブランドディメンションを追加すれば

Refinementsを使用して既存のExploreに期間比較 (Period Over Period) の機能を追加する

本記事は、Use Refinements to add Period over Period functionality to existing explores の翻訳になります。 この例では、よくご要望としていただく 期間比較 (Period Over Period) 機能を Refinements を使用して実装する方法をご紹介しております。 一般的なRefinements利用の背景 期間比較(Period over Period)の背景 refinementsを利用することで、主要なロジックと機能を実現するロジックをそれぞれ完全に分離することができ、しかも同時に対象となる元オブジェクトに対して機能を追加することができます。例えば、ここでご紹介するパターンを使って、元のコアオブジェクトには手を入れずに、既存のExploreの日付フィールドにPoP機能をシームレスに追加することができます。   これについての詳細はこちらの the lookml refinements doc page をご覧いただくか、Fabioの記事をご覧ください。(訳註:Fabioの記事には翻訳記事がございます) PoPはよくご要望として上がる機能で、いくつかのやり方があります。例えばこちら (Flexible Period-over-period Analysis) やこちら(An Explore filter based approach) の記事でも他のやり方がご紹介されています。   本記事でご紹介する方法は実装がシンプルで、またExploreに変更が入ったとしても特別なメンテナンスが不要です。ユーザーにとっては過度な複雑さを避けた柔軟な方法となります。  期間比較 (Period Over Period) の見た目はどのようになりますか? ユーザーは新しく作成したディメンションをピボットすることができ、また期間の長さと表示する数を選択することができます。 パラメータの入力例など他の2つの例をご覧になる場合はこちらをExpandしてください。 (訳註:フィールドピッカーに、Pivot for Period over Period という追加ディメンションが追加されており、また PoP Periods Ago to Include

新規のユーザーだけを Distinct しながら running_total (累計)を取得する

Lookerではテーブル計算でのrunning_total関数や、Measureでrunning_totalパラメータが準備されていますので、Explore上で簡単に累計値を取ることができます。ですが、例えばユーザーの新規純増分だけを抽出してその累計を出したい、と言うようなケースでは、少し工夫が必要になります。 こちらの記事にあるように、日付でソートし「一つ前の日付」を参照することで、そのユーザーが新規なのかどうかを判断することができます。これを使って、純増分のユーザー数をカウントすることでDISTINCTした累計を算出することができます。 考え方については前述の記事の通りなのですが、これをもう少し具体的に見ていきたいと思います。例えば、次のようなデータがあるとします。 田中 一郎は10-01に初回ログインをしており、10-03に2回目のログインをしています。同様に佐藤 次郎は10-01と10-02にログインをしています。このログインユーザーのUUを日別にとると、次のようなUUおよび累計になります。 ここで、10-01のUUは初日なので2で問題ありません。しかし、10-02の累計値は5になっています。10-02に着目すると佐藤 次郎は確かにログインをしているのですが、この人はすでに10-01にログイン済みなので、当該期間の延べ数としてカウントしたくありません。 そこで、それぞれのログインユーザーの「前回のログイン日」を参照することで、それぞれのユーザーが初めてログインしたのか、それとも以前にログインしたことがあるのかを区別するロジックを作って、これを実現してみます。ここではサブクエリーを書く必要がありますが、Lookerでは、このようなケースではネイティブ派生テーブルを利用すると、すっきりと記述することができます。 まず、先程のログイン日とログインユーザーIDを表示させている画面で、ギアマークから Get LookML をクリックします。 続いて、Derived Tableタブをクリックして、生成されたコードをクリップボードにコピーします。 得られたLookMLのコード(ネイティブ派生テーブルのコード)を、LookMLプロジェクトの中に記述します。このとき、View名をわかりやすいものに変更してください。 得られたネイティブ派生テーブルに二つの変更を追加しま

一つのフィルターを複数のViewに適用する

複数のViewを結合する際、複数のViewで同じフィルターをかけたいという場面はないでしょうか? 例えば、OrdersビューとUsersビューを結合する際、同じ日付で絞り込みを行いたいというようなケースがあったとします。 これは別々のViewに存在するディメンションなのでExploreではViewごとにそれぞれフィルターを設定してもらうことになると思います。 とはいえこれは面倒なので、一つのフィルターを同時にOrders ViewとUsers Viewに適用する方法はないでしょうか。Filterパラメータを使えばできそうなので、ちょっと考えてみました。 パターン1 orders.view に以下のようなフィルターを作成します filter: date_filter { view_label: "common filter" description: "Please use this filter" type: date } 次に model ファイルに以下の sql_always_where パラメータを設定します。 explore: order_items { always_filter: { filters: [date_filter: "7 days"] } sql_always_where: {% condition order_items.date_filter %} ${order_items.created_raw} {% endcondition %} AND {% condition order_items.date_filter %} ${users.created_raw} {% endcondition %} ;; join: users { type: left_outer sql_on: ${order_items.user_id} = ${users.id} ;; relationship: many_to_one } } 元のDateディメンションをフィルターに使って欲しくない場合は、can_filter: no を追加して明示的にフィルターとして使用できないようにしておくのも良いかと思います。 dimensi

Refinementsを使ってLookMLのコードを整理する

本記事はこちらの英語記事🇺🇸 の翻訳です。 Looker 7.6 より LookML に refinements が追加されました。本記事では refinements がどのように動作するのかを説明したいと思います。また、関連するコードを一緒に管理するために、どのようにLookMLコードを整理、または「レイヤー化」すれば良いのかについて書いてみたいと思います。 Refinements とは? Refinementsは、ベースとなるLookMLのオブジェクトに対して変更を加えることができると言う意味で Extends(継承) とよく似ていますが、新しい名前のオブジェクトを新規に作成するのではなく、既存のオブジェクトに対して直接上書きして変更を加える言う点が異なります。 基本的な例を見てみましょう。プラス(+)記号に注意してください。 view: fact { sql_table_name: fact ;; } view: +fact { label: "Facts" dimension: date { type:date } } view: +fact { label: "Quantified Facts" dimension: amount { type:number } } これら3つの独立した宣言は、Extendsと同一のマージ規則にしがって効率的に結合されます。これは、以下の記述と同等です: view: fact { sql_table_name: fact ;; label: "Quantified Facts" dimension: date { type:date } dimension: amount { type:number } } これは非常に小さな変更であると同時に、非常に大きな変更でもあります!Refinementsの使い所はたくさんあるでしょう。例えば、LookML Blocks やプロジェクトをまたいでインポートされたLookMLのモデルなどです。ただ、ここでは、もう少し面白い、かつ広く適用可能な使用方法に着目してみたいと思います:チームで開発しているときに、LookMLプロジェクトをより管理しやすくする方法です。 LookML のレイヤー化 通常、LookMLは

Google Sheetsを使ってデータディクショナリーを生成する

本記事は Generating a Data Dictionary in Google Sheets 🇺🇸 の翻訳記事です なぜGoogle Sheetsを使ったデータディクショナリーが必要なのですか? Lookerを使うことで、より多くのユーザーがデータにアクセスしたり探索したりすることができるようになる一方で、多くのユーザーにとって、定義されたフィールドの意味や元になっているデータソースについてはわからなくなってしまうケースもあり、混乱して間違ったクエリーを発行してしまうことがあります。こうした混乱を防ぐ一つの方法として、Looker APIを使ってデータディクショナリーを定義し、Google Sheet上から簡単にアクセスできるフィールド情報を生成する方法があります。本記事ではその方法を記載します。以下のソリューションはview上で description パラメータをフィールドに定義している場合に特に便利です。 スクリプト このスクリプトは Apps Script と Javascript の組み合わせで記述されており、こちらのリポジトリーにリストアップされている関数を使っています。 本スクリプトを使用する際の注意事項は以下の通りです: model_name と explore_name は大文字小文字を区別する必要があります このスクリプトは、それぞれのViewの情報を独立したGoogle Sheetに作成します。それぞれのGoogle Sheetの名称はViewの名称となります(全ての情報を一つのシートに作成する場合は、以下の注を参照してその動作を強制する方法をご覧ください)。 このスクリプトでは、Exploreの各ビューへのGoogleシートマッピングを使用します。各Google Sheetには対応するビュー名が付けられ、そのビューに関連するデータのみがGoogle Sheetに表示されます。 また、このスクリプトはシートを開いた6時間後にAPIをコールしてアウトプットを出力します。 // ベースドメインをご自身のインスタンス名で置き換えてください var BASE_URL = 'https://instance_name.looker.com:19999/api/3.1'; // API認証情報を置き換えてくださ

BigQuery Standard SQLで、ネストされたデータをモデリングする

本記事は Modeling nested data in Big Query Standard SQL 🇺🇸 の翻訳記事です。 Big Queryの標準SQL(Standard SQL Dialects)のリリースされ、LookMLでネストされたデータセットをモデル化することがはるかに簡単になりました。 ネストされたファイルと繰り返しファイルがBigQueryでどのように機能し、それらがなぜ重要なのかについての簡単な入門については、 ダニエルの投稿 をご覧ください(訳注:こちらはリンク切れです)。 また、こちらのリンクからGoogle Analytics PremiumとGA360用のBlocksを参照できます。こちらをご覧いただければ、以下に示すプラグ・アンド・プレイですぐに使えるLookMLのコンセプトがよく理解できると思います。 以下に示す例は、ネストされた繰り返しフィールドを多用するGoogle Analytics Premium(GAP)をBig Queryにエクスポートしたデータセットに基づいています。 ネストされた繰り返しフィールド(例:STRUCTの配列)の結合ベースのクエリー、FLATTENSの置き換え Google Analytics Premiumスキーマには、セッションレベルのデータが格納されたテーブルが1つあり、個々のヒット(例えばイベント)レコードがそれぞれのセッション内にネストされています。ヒットレベルとセッションレベルのデータを同時にクエリする(たとえば、ヒットレベルとセッションレベルのデータを同時にカウントする(例えば、セッションの合計数とその時間内のヒット数の合計数)ために、これらの2つの異なるレベルのネストを参照するための新しい結合ベースの構文があります(ドキュメントはこちら )。 この構文を参考にし、モデルのセッションフィールドとヒットフィールドに個別のビューファイルを定義し、それらをExploreで結合します。 view: session { sql_table_name: `google.com:analytics-bigquery.LondonCycleHelmet.ga_sessions_*` ;; # このViewファイルにセッションに関連する全てのディメンションとメジャーを定

テーブル計算を使った小計の計算方法

本記事は Subtotals with Table Calculations 🇺🇸 の翻訳記事です。 本記事はLookerの標準のテーブル計算機能を使って小計を算出する方法を紹介しています。Looker 6.10 では、テーブルネクストが追加されており、これを使うと小計がネイティブに算出できるようになります。より詳しい情報についてはこちらのドキュメントを参照してください。 小計(subtotal)はグループ分けされた項目の合計を手早く確認するのに使われ、例えば、全体の合計の売上数値を見つつ、同時にブランド別の売上の小計なども参照する、というようなケースで便利です。本記事ではこの実現方法についての手順をご紹介します。 これは暫定的なやり方になりますので、こちらのコミュニティーに投稿されている優れたクロス結合による小計(Subtotals with a CROSS JOIN)パターンも参照してください。より柔軟な魔法のようなやり方を学ぶことができます。 例 下記画像のような例を使って考えてみます。いくつかのブランドがあって、その中にカテゴリーとメジャーがあり、売上合計または利益が算出できるものとします。これを今回のデータセットとして考えます。 さて、ここで、例えばブランドマネジャーがブランド毎の売上合計がどのくらいなのか、つまりそれぞれのブランドの小計が欲しいというケースを考えます。 テーブル計算を使うと、それぞれのブランドの最後の行と同じ行に4つ目の列を生成することができます。 これをやってみましょう。非常にシンプルなやり方です。 方法 ステップ1 テーブル計算を作成して、それぞれのブランド毎に行番号を表示させます。 この番号はブランドが変わるたびに 1,2,3... のようにカウントしていきます。 テーブル計算は次のように書きます。 if(match(${products.brand},${products.brand})=offset(match(${products.brand},${products.brand}),-1) , 1+row()-match(${products.brand},${products.brand}) , 1) ここでは match() を使ってそれぞれのブランドが最初に登場するユニ

Firebase Blocks v3 (日本語翻訳記事)

本記事は github で提供されている Firebase Blocks 🇺🇸の Readme の翻訳です。 この block の役割 BigQueryにあるFirebaseデータには大きな分析ポテンシャルがありますが、クエリーできるようにするのは簡単ではありません。それは以下のような理由があります: 毎日新しいテーブルが(events_YYYYMMDDという名前で)作成されてしまうため。これによりBigQueryのコストは下がりますが、クエリーがより複雑になってしまいます。 どの event_name も、 event_params.key や event_params.value のようなネストされた Key-Value 型のペアになっており、クエリーするときに複雑な UNNEST 処理を行う必要があるためです。 user_properties も同様に Key-Value のペアになっていて何階層にもネストしているからです。 この block でできること 期間を選択して魔法のようにパーティションされたテーブルから必要なデータを選択できるネイティブな方法を生成します。 セッション化(Sessionization)を追加できます。(これはFirebaseデータにはありません) [訳注] 「セッション化」についてはこちらの記事も参考にしてください。 リテンションのコホート分析を追加できます。 event_name または event_params.key 、 user_properties の組み合わせのディメンションを作成できます。 データのモデリングを開始して価値を引き出すための最初のステップとして最適です。 仕組み どの Firebase スキーマも類似点は多いものの event type と user properties は異なるため、この block が event 構造に沿ったユニークなスキーマを生成します。Python notebook を使用して Looker API とご自身のデータベースに接続してLookMLを自動生成します。 利用方法 Looker側の準備 こちらの記事を参考にしてリポジトリーからクローンしてプロジェクトを新規作成します。 https://docs.looker

「最新の」値だけをメジャーにする方法

この記事は、Only measuring the “latest” values🇺🇸 の翻訳記事です メジャーとは何か? 非常に多くのケースで、私たちは、自分自身に不必要な制約をかけてしまって、メジャーやメトリック、あるいは集約するということは、SQLで提供される基本的な集約であるSUM、COUNT、AVERAGE(あるいは方言がサポートされている場合はMEDIANも含む)のことであると考えてしまいます。 しかしここでちょっと立ち戻って、なんのメジャーが本質的なのかを考えてみましょう - 通常、あるグループやディメンションの中で、潜在的に多数の基礎データポイントを取得して、それらを利用可能なデータに凝縮するメジャーを選びましょう。数千の注文を集約する際は? メジャーには総売上が良いです。数百万のセッションでは? サイトの平均滞在時間が良さそうです。何百万人ものセグメントあたりユーザー数は? 訪問頻度のヒストグラムになります。多数のキャンペーンと、たくさんの日付、訪問データは? 直近30日間の訪問回数のスパークラインなど。 上記の例はちょっと言い過ぎかもしれませんが、要点はつまり、あなたの想像力が制約になっているということです。ここでは、最新のデータポイントのみを使用する必要があるデータを要約するときの簡単なメジャー使用方法を次に示します。例としては、在庫のスナップショットや、バランス・シート、タイムスタンプ付きの変更リストが与えられた属性の状態履歴などのデータです。 「最新の」メジャー 今、以下のような在庫のスナップショットデータで、日毎にそれぞれ在庫数を持っているとします。 INVENTORY_SNAPSHOT inventory_id | taken_at | amount ------------------------------------ 1 | 2018-01-01 | 17 2 | 2018-01-01 | 9 3 | 2018-01-01 | 29 1 | 2018-01-02 | 13 2 | 2018-01-02 | 37 3

Badges

  • Friendly Face
    sbrylowskihas earned the badge Friendly Face
  • Helpful
    carriehas earned the badge Helpful
  • Community Star
    carriehas earned the badge Community Star
  • Friendly Face
    carriehas earned the badge Friendly Face
  • Community Star
    Dawidhas earned the badge Community Star
Show all badges