前置き
Redashで契約件数KPIを可視化したいとします。
作りたい表をこんな感じだとします。
n月の契約件数実績
| 国 | 新規 | 解約 | 継続 |
|---|---|---|---|
| 日本 | 40 | 4 | 500 |
| アメリカ | 5 | 1 | 20 |
| … |
件数の数字の部分がリンクになっていて、クリックすると内訳の表に飛びたい。とします
リンク先：日本の新規契約内訳
| 日付 | id | プラン名 |
|---|---|---|
| 20xx/xx/xx | 12345 | A |
| … |
こんなときどうすればよいでしょうか？
実装
行ごと列ごとに異なる数値を埋め込んでいく
n月の契約件数実績に関してはSQSを駆使し力技で行きます。黒魔術のよう
mysqlベースのデータソースならそのままコピペして使えます。sqliteだとだめかも
with japan_new as(
select
'Japan' country,
'new' category,
40 num -- count(*) num
-- 件数を抽出するクエリ
),
japan_cancel as(
select
'Japan' country,
'cancel' category,
4 num --count(*) num
-- 件数を抽出するクエリ
),
japan_continuation as(
select
'Japan' country,
'continuation' category,
500 num --count(*) num
-- 件数を抽出するクエリ
),
america_new as(
select
'America' country,
'new' category,
5 num -- count(*) num
-- 件数を抽出するクエリ
),
america_cancel as(
select
'America' country,
'cancel' category,
1 num --count(*) num
-- 件数を抽出するクエリ
),
america_continuation as(
select
'America' country,
'continuation' category,
20 num --count(*) num
-- 件数を抽出するクエリ
),
japan_union_table as(
select * from japan_new
union
select * from japan_cancel
union
select * from japan_continuation
),
america_union_table as(
select * from america_new
union
select * from america_cancel
union
select * from america_continuation
),
union_table as(
select
'1.Japan' country,
max(case when category = 'new' then num else 0 end) new,
max(case when category = 'cancel' then num else 0 end) cancel,
max(case when category = 'continuation' then num else 0 end) continuation
from japan_union_table
union
select
'2.America' country,
max(case when category = 'new' then num else 0 end) new,
max(case when category = 'cancel' then num else 0 end) cancel,
max(case when category = 'continuation' then num else 0 end) continuation
from america_union_table
)
select *
from union_table
order by country
こうなります
行ごと列ごとに異なるリンクを設定する
さらにリンクを張りたいと。。。
Redashではある程度リンクを設定することができます
クエリを保存し、左下のEdit Visualizationを開く
リンク化したいColumnを選択肢、Display asのLinkを選択する
Linkを選択するとどうするか選べるようになります。
redashのURL/queries/<飛ばしたいクエリNo>?id={{ @ }}
上記みたいにすることで、クエリの検索結果であるIDを動的にリンクとして生成し、ID詳細に飛ばすことができたりします
ここまでは知っている方も多いかと思います。
★ここのURL templateですが、「他のカラムの値を指定することもできます」
つまりURL用のカラムをそれぞれ力技で作り、各カラムにURLを埋め込むことが可能です。
やるとこんな感じ
with japan_new as(
select
'Japan' country,
'new' category,
40 num, -- count(*) num,
'url/xxx?p_at_month=' || date_format(date_add('month',0,date '{{ at_month }}'), '%Y-%m-%d') url
-- 件数を抽出するクエリ
),
japan_cancel as(
select
'Japan' country,
'cancel' category,
4 num, --count(*) num,
'url/xxx?p_at_month=' || date_format(date_add('month',0,date '{{ at_month }}'), '%Y-%m-%d') url
-- 件数を抽出するクエリ
),
japan_continuation as(
select
'Japan' country,
'continuation' category,
500 num, --count(*) num,
'url/xxx?p_at_month=' || date_format(date_add('month',0,date '{{ at_month }}'), '%Y-%m-%d') url
-- 件数を抽出するクエリ
),
america_new as(
select
'America' country,
'new' category,
5 num, -- count(*) num,
'url/xxx?p_at_month=' || date_format(date_add('month',0,date '{{ at_month }}'), '%Y-%m-%d') url
-- 件数を抽出するクエリ
),
america_cancel as(
select
'America' country,
'cancel' category,
1 num, --count(*) num,
'url/xxx?p_at_month=' || date_format(date_add('month',0,date '{{ at_month }}'), '%Y-%m-%d') url
-- 件数を抽出するクエリ
),
america_continuation as(
select
'America' country,
'continuation' category,
20 num, --count(*) num,
'url/xxx?p_at_month=' || date_format(date_add('month',0,date '{{ at_month }}'), '%Y-%m-%d') url
-- 件数を抽出するクエリ
),
japan_union_table as(
select * from japan_new
union
select * from japan_cancel
union
select * from japan_continuation
),
america_union_table as(
select * from america_new
union
select * from america_cancel
union
select * from america_continuation
),
union_table as(
select
'1.Japan' country,
max(case when category = 'new' then num else 0 end) new,
max(case when category = 'new' then url else '0' end) new_url,
max(case when category = 'cancel' then num else 0 end) cancel,
max(case when category = 'cancel' then url else '0' end) cancelurl,
max(case when category = 'continuation' then num else 0 end) continuation,
max(case when category = 'new' then url else '0' end) continuation_url
from japan_union_table
union
select
'2.America' country,
max(case when category = 'new' then num else 0 end) new,
max(case when category = 'new' then url else '0' end) new_url,
max(case when category = 'cancel' then num else 0 end) cancel,
max(case when category = 'cancel' then url else '0' end) cancel_url,
max(case when category = 'continuation' then num else 0 end) continuation,
max(case when category = 'continuation' then url else '0' end) continuation_url
from america_union_table
)
select *
from union_table
order by country
こんな感じになります。※飛ばし先のクエリxxxはすべて別で用意する必要があります。検索パラメータとして月で絞るようにしています。
at_monthをdate_addしているのは、最終的にダッシュボードにし、直近三ヶ月分の契約実績を出すときに役に立ちます。今月、先月、2ヶ月前もそれぞれクエリを作る必要があります。
ここまで来たら、URL templateをURL用に作ったカラムを指定し、そのカラムは非表示にすればいい感じになります
一つやるとこんな感じ。
地味なtips
カラム名の別名は日本語を使わないほうがいいです。結合時とかにエラーになります。使うなら、最後にやりましょう。
終わりに
淡々と書きました。
お金を抑えるフェーズは、Redashを使いつつも、Redashだから表現に限りがあるからと諦めずに、使われるダッシュボードを力技で作り上げることも、時には大事かと思います。
こういう黒魔術力技の積み重ねで、社内のデータ文化が進んでいくかと思います。
こうした積み重ねの後、データ文化が根づいた後は、予算を確保し有料のBIツールを導入し楽をしましょう。