Googleスプレッドシートでお手軽ダッシュボード運用【Sexy Tech for You #5】

データサイエンティストの阿部です。最近のマイブームは、COTEN RADIOです。高校で世界史を勉強したとき一緒に聞いてたら、何倍も身になっていたろうなと思えるコンテンツです。

さて、明日から使いたくなるデータハンドリング術を紹介する "Sexy Tech for You" の第5話を配信しました。BigQueryの集計結果を、Googleスプレッドシートでダッシュボードとして運用する方法を紹介しました。動画中に記載したクエリや補足事項を本ブログに掲載しますので、少しでも皆様のお役に立てたら幸いです。

Youtube

1:30 でアウトプット例を示します。

BigQuery クエリ

BigQueryの一般公開データセットbigquery-public-data.wikipedia.pageviews_2021での適用例

CREATE TEMP FUNCTION timezone() AS ("Asia/Tokyo");
CREATE TEMP FUNCTION today() AS (CURRENT_DATE(timezone()));
CREATE TEMP FUNCTION from_date() AS (DATE_SUB(today(), INTERVAL 7 DAY));
CREATE TEMP FUNCTION to_date() AS (DATE_SUB(today(), INTERVAL 1 DAY));

SELECT 
    ordinal_dt_num,
    dt,
    IF(wiki="ja.m", "モバイル", "デスクトップ") AS watch_type,
    IF(better_ranking<= 5, title, "その他") AS title,
    SUM(pv) AS pv
FROM(
SELECT 
    *,
    MIN(ranking)OVER(PARTITION BY dt,title) AS better_ranking
FROM(
SELECT
    *,
    DENSE_RANK()OVER(ORDER BY dt DESC) AS ordinal_dt_num, -- ピボットテーブルのフィルターで任意の日を指定するためのフィールド
    RANK()OVER(PARTITION BY dt,wiki ORDER BY pv DESC) AS ranking
FROM(
SELECT  
    DATE(datehour) AS dt,
    wiki,
    title,
    SUM(views) AS pv
FROM
    `bigquery-public-data.wikipedia.pageviews_2021`
WHERE
    DATE(datehour) BETWEEN from_date() AND to_date()
    AND wiki IN ("ja","ja.m")
    AND title NOT IN ("メインページ","特別:検索")
GROUP BY 
    1,2,3
)))
GROUP BY 
    1,2,3,4

補足

スプレッドシートのピボットテーブルのフィルターでは、日付を動的に選択する方法が限られます。例えば「3日前」を選択することができません。そのため、ordinal_dt_numのフィールドを用意して、柔軟にフィルタリングできるようにしています。

今回例題で扱ったWikipediaのトレンド可視化によって、世の中のニュースを少し斜めからチェックできそうなので、1回個人的に運用してみようかなと思いました。検索データに相当するようなデータはやっぱり面白いですよね。

以上、Moblie Tech for All のヤプリがお送りする Sexy Tech for You でした！次回またお会いしましょう。