More than 1 year has passed since last update.

フリューAdvent Calendar 2023

@m_kazu_min

フリュー株式会社

Tableauでサンキーダイアグラムを簡単に作りたい

Last updated at 2023-12-07Posted at 2023-12-07

はじめに

　この記事はフリュー Advent Calendar 2023 の8日目の記事となります。

早いもので今年も12月になり、2023年も年末を迎えようとしていますね。
前回のアドベントカレンダーに投稿したのがついこの間のように感じますｗ

さて、今回は「サンキーダイアグラム」というチャートを、SQLを使って作成コスト削減に取り組んだことを紹介したいと思います。

まず、サンキーダイアグラムとは？

　TableauやBIツールを使ってデータを可視化されている方でしたら、何となく聞いたことがあったりなかったりするやつです。検索してみると紹介が出てくるため概要だけ抜粋しておきます。

wikiペディアより
工程間の流量を表現する図表である。矢印の太さで流れの量を表している。特にエネルギーや物資、経費等の変位を表す為に使われる。

これではパッとイメージできないかもしれないため、分かりやすそうな例だとGoogleAnalytics（UAの時の）の「行動フロー」（下図参照）のようなものです。

ユーザーのページ遷移の流量を視覚的に分かりやすくすることができるような感じですね。

さて、ここで行動フローについて触れたため、今回サンキーダイアグラムについてアレコレしようと思ったきっかけをお伝えしておこうと思います。

取り組みのきっかけ

　理由としては2つあります。

GoogleAnalytics（GA4）ではデフォルトで行動フローが使えない
Tableauでサンキーダイアグラムを作るコストが大きい

まず1つ目について。

①GoogleAnalytics（GA4）ではデフォルトで行動フローが使えない

　そのままなのですが、UA時代に自由に使うことができた行動フローがGA4になってからは手作りする必要があります（「使えない」というのはやや言いすぎでしたm(_ _)m）

けれど、作るにあたってはGA4のレポート機能から作成しなければいけなく、レポート機能を普段から使っていないと、やや敷居が高いと思われます。

②Tableauでサンキーダイアグラムを作るコストが大きい

　Tableau・サンキーなどで検索すると、先人の方々の恩恵を受けることができ、作成方法を知ることは比較的容易です。

　けれど、サンキーダイアグラムを目にすることが少ないのかなぜか？それは 作成コストと作成後に得られる効果がいまいち釣り合いにくい ということが要因なのではと考えています。

　他にも理由はあるかと思いますが、作成に結構手間がかかる割に、作ったものはパッと見られて「ハイ終わり」ってなってしまいそうで、作る側の心理的障壁が高めです。。。※あくまでも私の個人的な見解ですので

　とは言うものの、ユーザーの行動と行動量を視覚的に分かりやすく表現できるチャートとしてはとてもよいと思っています。できることなら、簡単に作れて使いたい時にサッと使えるようになりたいです。

　さて、ではやったことを解説していきます。

Step1．仕組みの理解

　最初に始めたことはサンキーダイアグラムがどのように作られているかの理解からでした。既に色々と紹介されているものを参考にすれば作成自体はできるものの、仕組みの理解はできていなかったためです。

サンキーダイアグラムを作る構成要素
- シグモイド曲線を作るモデルデータ（下図イメージ参照）
  - Path
    - 「0～97」の連番で98個ある。これがないとシグモイド曲線の色塗りができない。
  - T
    - 「6」～「-6」までの「0.25」きざみの定数。
    - このきざみの値が小さいほど曲線が滑らかになる。だいたいは0.25でやることが多い模様。
    - 「6」～「-6」を「0.25」きざみにすると合計で98行になるためPathは98個に設定されている。
  - Min/Max
    - 曲線の上部分と下部分の線を引くための定義。「6」～「-6」の上半分が「Min」で「-6」～「6」の下半分が「Max」となっている。
  - KEY
    - 値は「KEY」が単純に入っているだけ。紐づけするデータとのjoinキーに使う。
    - 紐づけするデータの1レコードにこの98個を全て紐づける形になる。
    - そのため、単純にデータが98倍になる ので元データの量が多い時などは気を付けること。
  - ※データファイルの注意
    - 一見たんなる定義ファイルに見えるが、この並びの通りにデータを紐づけないとうまくいかないため注意が必要。
- Tableauの計算フィールド
  - Sigmoid
    - 1 / (1 + EXP(1)^-[T])
    - シグモイド曲線（サンキーダイアグラム）を作ってくれる関数。
    - これは数学的で解説が難しかったのでごめんなさい。。
    - 先述のモデルデータにある 「T」 フィールドを使用している。
  - FlowSize
    - SUM([レコード数]) / TOTAL(SUM([レコード数]))
    - 全体の構成比を表すフィールド。この後のフィールドが参照する大事なもの。
  - MaxPosition1
    - RUNNING_SUM([FlowSize])
    - 単なる表計算だが表計算の順序が決まっている。
    - 表計算の順番を「ページ1 → ページ2」とすること。
  - MaxPosition2
    - RUNNING_SUM([FlowSize])
    - MaxPosition1と中身は同じだが、表計算の方向が違う。
    - 表計算の順番を「ページ2 → ページ1」とMaxPosition1とは逆にすること。
  - MinPosition1
    - RUNNING_SUM([FlowSize]) - [FlowSize]
    - 累計のFlowSizeから現在行のFlowSizeを引いている。この差分が曲線の幅を持たせることになる。
    - 表計算の順番を「ページ1 → ページ2」とすること。
  - MinPosition2
    - RUNNING_SUM([FlowSize]) - [FlowSize]
    - MinPosition1と中身は同じだが、表計算の方向が違う。
    - 表計算の順番を「ページ2 → ページ1」とMinPosition2とは逆にすること。
  - CurveMax
    - [MaxPosition1] + (([MaxPosition2] - [MaxPosition1]) * ATTR([Sigmoid]))
    - 上側の曲線部分を表現してくれる関数。これは表計算ではない。
  - CurveMin
    - [MinPosition1] + (([MinPosition2] - [MinPosition1]) * ATTR([Sigmoid]))
    - 下側の曲線部分を表現してくれる関数。これは表計算ではない。
  - CurvePolygon
    - CASE ATTR([Min/Max]) WHEN 'Min' THEN [Curve Min] WHEN 'Max' THEN [Curve Max] END
    - Tableauの行に入れて使うフィールド。これを行に入れることで曲線が生まれる。
    - やっていることはcase文の通りで、先述のモデルデータにある 「Min/Max」 フィールドを使用している。

【モデルデータのイメージ】

　これで仕組みの理解ができたかどうかは少し怪しいが、これらをベースにして次はSQLでこれらを実現していく。

Step2．SQL作成

　最終的にTableauで必要になっているフィールドは

FlowSize
CurvePolygon
T
Path
Min/Max
ページ1　※流量を表現する元のディメンション。実際のデータから使用する。
ページ2　※流量を表現する先のディメンション。実際のデータから使用する。
である。必要最低限はこれで大丈夫。
SQLの最後のselectではこれらがあればよい。

サンプルデータが入ったテーブルと試行錯誤しながら書いたSQLが（↓）です。

テーブル名
- test-project.test_dataset.sankey_test_data
列定義
- 1～5のTRANSITIONにページ1～ページ3という値がランダムに入っている。
- PVはPV数のイメージ。値に深い意味はなくランダムにした。
- SQLの環境はBigQueryを使用した。

with model_t_1 as (
select
  t
from
  unnest(generate_array(6, -6, -0.25)) as t
),
model_t_2 as (
select
  t
from
  unnest(generate_array(-6, 6, 0.25)) as t
),
model_data as (
select
  row_number() over (order by priority) - 1 as path,
  t,
  min_max
from
  (
  select
    t,
    "Min" as min_max,
    1 as priority
  from
    model_t_1
  UNION ALL
  select
    t,
    "Max" as min_max,
    2 as priority
  from
    model_t_2
  )
)
/* ------------- */
/* (↑)WithBlocks */
/* (↓)QueryStart */
/* ------------- */
select
  1 as trans_flg,
  t,
  min_max,
  path,
  flow_size,
  pv,
  flow_src,
  flow_dst,
  case
    when min_max = "Min" then curve_min
    when min_max = "Max" then curve_max
  end as Curve_Polygon -- 先述のCurvePolygon相当のもの
from
  (
  select
    *,
    max1 + ((max2 - max1) * sigmoid) as curve_max, -- 先述のCurveMax相当のもの
    min1 + ((min2 - min1) * sigmoid) as curve_min  -- 先述のCurveMin相当のもの
  from
    (
    select
      *
    from
      (
      select
        path,
        t,
        min_max,
        1 / (1 + pow(exp(1), -1 * t)) as sigmoid -- 先述のSigmoid相当のもの
      from
        model_data
      ) as a
    CROSS JOIN
      (
      select
        row_num,
        flow_src,
        flow_dst,
        pv,
        pv_total,
        flow_size,
        max1,
        max2,
        max1 - flow_size as min1, -- 先述のMinPosition1相当のもの
        max2 - flow_size as min2  -- 先述のMinPosition2相当のもの
      from
        (
        select
          row_num,
          flow_src,
          flow_dst,
          pv,
          pv_total,
          flow_size,
          -- （↓）先述のMaxPosition1相当のもの
          sum(flow_size) over (order by flow_src, flow_dst, pv rows between unbounded preceding and current row) as max1,
          -- （↓）先述のMaxPosition2相当のもの
          sum(flow_size) over (order by flow_dst, flow_src, pv rows between unbounded preceding and current row) as max2
        from
          (
          select
            row_number() over () as row_num,
            flow_src,
            flow_dst,
            pv,
            sum(pv) over () as pv_total,
            pv / sum(pv) over () as flow_size -- 先述のFlowSize相当のもの
          from
            (
            SELECT
              transition1 as flow_src, -- ページ1　流量の元
              transition2 as flow_dst, -- ページ2　流量の先
              sum(pv) as pv
            FROM
              `test-project.test_dataset.sankey_test_data`
            group by
              1,2
            )
          )
        )
      ) as b
    )
  )

ほぼ全てSQLで書いた。
これをデータソースにしてTableauを作る。

※SQL内で使わないフィールドをselectしてたり、余計なネストをしたりしているかもしれませんので、その辺りはご容赦くださいm(_ _)m

Step3．Tableauでの作業

　Step2で書いたSQLをTableauで抽出する。
その後の作り方は既存の方法と同じで、各フィールドを配置していくとチャートが完成します。

「t」を列に配置。ディメンションに変換。
「flow_src」「flow_dst」「min_max」を詳細に配置。順番は←の通りにすること。
「Curve_Polygon」を行に配置。
グラフのタイプを「多角形」に変更。
「path」をパスに配置。

　こんな感じにできました（配色はお好みで）。ちゃんと再現できて良かった・・。

今回重要なポイント！

　なぜSQLを書いてまでサンキーダイアグラムを簡単に作りたいと思ったのか、それは数多い遷移を表現するため。

　上記のイメージは「ページ1 → ページ2」のシンプルなものですが、SQLを駆使することで計算フィールドを増産することなく（↓）が作成可能になりました！

SQLの修正ポイントは3つ

with句以外のselectブロックをUNION ALLして繋げていく
ページ1・2のselectしている箇所を変更する
- ページ1・2の次はページ2・3といった具合でselectを変えていく。
trans_flgの固定値を変更する
- trans_flg
- 1から順番に固定値をふっている。別に1でなくても何でもいい。
- selectブロックの単位で一意な値にしておく。
  - これをTableauのフィルタに配置して、ページ1・2の流量を表現したい時は「1」に、ページ2・3を見たい時は「2」にフィルタして絞り込むように使う。


～～with句は割愛～～

select
  1 as trans_flg, -- ここが重要☆彡 固定値で1としているが、（↓）のページ1・2をページ2・3に変更したあと、全体をUNION ALLしてこの固定値を2にする。
  t,
  min_max,
  path,
  flow_size,
  pv,
  flow_src,
  flow_dst,
  case
    when min_max = "Min" then curve_min
    when min_max = "Max" then curve_max
  end as Curve_Polygon
from
  (
  select
    *,
    max1 + ((max2 - max1) * sigmoid) as curve_max,
    min1 + ((min2 - min1) * sigmoid) as curve_min
  from
    (
    select
      *
    from
      (
      select
        path,
        t,
        min_max,
        1 / (1 + pow(exp(1), -1 * t)) as sigmoid
      from
        model_data
      ) as a
    CROSS JOIN
      (
      select
        row_num,
        flow_src,
        flow_dst,
        pv,
        pv_total,
        flow_size,
        max1,
        max2,
        max1 - flow_size as min1,
        max2 - flow_size as min2
      from
        (
        select
          row_num,
          flow_src,
          flow_dst,
          pv,
          pv_total,
          flow_size,
          sum(flow_size) over (order by flow_src, flow_dst, pv rows between unbounded preceding and current row) as max1,
          sum(flow_size) over (order by flow_dst, flow_src, pv rows between unbounded preceding and current row) as max2
        from
          (
          select
            row_number() over () as row_num,
            flow_src,
            flow_dst,
            pv,
            sum(pv) over () as pv_total,
            pv / sum(pv) over () as flow_size
          from
            (
            SELECT
              transition2 as flow_src, -- ここをtransition1から2に
              transition3 as flow_dst, -- ここをtransition2から3にする。外側はflow_srcとflow_dstとして参照しているので変更はここだけ。
              sum(pv) as pv
            FROM
              `snlog-test.matsumi_no_dataset.sankey_test_data_4`
            group by
              1,2
            )
          )
        )
      ) as b
    )
  )
UNION ALL
select
  2 as trans_flg, -- UNION ALLをするだけでTableauで見れるチャートが増える。
  t,
  min_max,
  path,
  flow_size,
  pv,
  flow_src,
  flow_dst,
  case
    when min_max = "Min" then curve_min
    when min_max = "Max" then curve_max
  end as Curve_Polygon
from
  (

～～省略～～

（↓）のように「trans_flg」をフィルタに入れて各流量のselectブロックの番号を絞り込みして使うイメージ。

感想

　大変だったことは、何よりサンキーダイアグラムが表現していることをSQLに変換することだった。
この計算フィールドは何をしてるのか？この値は何のためにあるのか？並べ替えって大事やん・・などなど。。
けれど、おおもとのサンプルデータテーブルからselectしている部分を別のデータに変更することで意外と柔軟に使えるのではと思うため、これからは積極的にサンキーしていきたいと思います。

さいごに

　こうした記事を書く機会があると、しっかり調べて学ぼうという意欲が湧いてくるため今後もTableau関連のことを書いていければと思っています。

最後まで見て頂いた皆さま、ありがとうございました！

実行環境

BigQuery
TableauDesktop2022.1.5

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up