23/12/10（日）【SQL】GA4→bigqueryの日付データ確認

SQL

GA4からbigqueryエクスポートを使って連携すると、analytics.events_*というテーブルセットが用意され、そこにデータが格納される(ほぼリアルタイムのデータ仮受けはanalytics.evnet_intraday_*)。
日付として、event_dateとevnet_timestampがあるがこのタイムゾーンがどうなっているか確認してみた。
evnet_timestampは実数値でエクスポートされているので、日付データに変換。
evnet_dateはそのままに(GA4の登録タイムゾーンになる)、event_datetimeをタイムゾーンの指定なし/ありで比較すると、以下のようになった。

■タイムゾーン指定なし

■タイムゾーン指定あり

タイムゾーン指定なしだと、event_timestampは9時間前になり、日付がevent_dateと一致しない。
基本的なところだが、普段タイムゾーンを気にして生活することがないので、気をつけないと大きな間違いにつながってしまう。
処理系

bigquery

コードを表示

一番上に戻る

23/11/30（木）【LS】Looker Studioでの正規表現

Looker Studioでフィールドを作成するときにはまってしまった。
以下のようなタイトル文字列がある。

さば_かつお_たい_まぐろ：魚類だよ
かき_はまぐり_さざえ：貝類ですよ
魚類だよ
貝類だよ

この２つの文字列から「魚類だよ」「貝類でよ」を除いたデータに成形したいとき、正規表現で該当部分を除くことを考えて、まずは以下のようなコードを作成した。

REGEXP_EXTRACT(タイトル,'.*：')

タイトル文字列から、任意の文字(.)が0回以上の繰り返して(*)"："までを抽出する（REGEXP_EXTRACT）という動きをさせようとしたのだが、このコードではすべてnullがかえってきてしまった。

色々ウェブを探っていたが、なかなか答えが見つからなかったが、

REGEXP_EXTRACT(タイトル,'(.*)：')

で：から末尾までの文字を除いた文字列を抽出することができた。Looker Studio の正規表現によると"()"はグループ指定するときに使うということらしい。
.*という「任意の文字を0買い以上繰り返す」をグループ化したということである。

さらにタイトルが「魚類だよ」だけのものもあるので、その場合は「魚類だよ」を残す文字列にしたかった。
最終的なコードは以下のようになった。

ifnull(REGEXP_EXTRACT(タイトル,'(.*)：'),タイトル)

抽出結果がnullの場合はタイトルそのものをデータとする、という動きを"ifnull"で処理している。
このコードでタイトル一覧を作成した結果は以下のとおりとなった。

さば_かつお_たい_まぐろ
かき_はまぐり_さざえ
魚類だよ
貝類だよ

一番上に戻る

23/11/15（水）【SQL】１日のアクセス回数をグルーピングして計算

SQL

個人的にはwith句が好み。fromにselectでテーブルを呼び出してもできるが関連性をつかみづらい。
このコードはt1テーブルでformat_timestamp('%H',datetime)で訪問時刻を抽出し、
count(distinct FORMAT_TIMESTAMP('%H', datetime))で訪問時刻別の訪問者数を計測するためのフラグを設定している。
group by user_pseudo_id,訪問時刻で個人別（端末id別なんだけどね）かつ訪問時刻別にグルーピングしているところが味噌だと思っている。
これを基礎テーブルとして、t2→t3と集計区分を追加していき、t4でcount(user_pseudo_id)を使い、目的としていた訪問時刻別訪問者数を算出している。

処理系

bigquery

コードを表示

一番上に戻る

23/10/30（月）【R】Microsoft Formsのデータの日付データを読み込む

Ｒ

仕事で他部署がMicrosoft Formsで行ったアンケートを集計することがしばしばある。
Formsからダウンロードしたexcelファイルは日時が"月/日/年時:分:秒"になっていることが多く、そのままRに取り込むとcharacterクラスになってしまい、時系列分析ができない。
インポートする際はコードのようにした方が後々、時だけ抽出などの処理をするときに便利。
Rstudioの右下ペインのファイルからcsvファイルを選択、右クリックでインポートするとインタラクティブに確認してインポートできる。

使用パッケージ

readr

コードを表示

一番上に戻る

23/10/26（木）【R】グループ別集計

Ｒ

Rでよく使うグループ集計。group_byでグループ分けしたデータフレームをパイプ演算子（%>%）を使ってsummariseに引き渡すところが忘れがち。
id_num=n()でレコード数を計算しているがこれもよく忘れがち。
ついついcount(*)としてしまって「何でエラーなん？」ってなる。

使用パッケージ

tidyverse

コードを表示

一番上に戻る

23/07/13（木）【R】Rstudioのインストール備忘録

Ｒ

仕事ではいつも使っているRstudio。
自宅のPC（windows10）にも入れているが、あまり更新していなかったので最新版（Version: 2023.06.1+524）にアップデート。

positのRstudioDesktopからダウンロード、インストールは問題なくできたものの、起動したら「R」のロゴが出てきたまま、止まってしまった。
何度か再インストールを繰り返したが、ダメ。

ふと、思い立って「管理者権限で実行」をしたら、無事、コンソールまで起動した。

【手順】

1.C:\Program Files\RStudioに移動
2.rstudio.exeを右クリックして「プロパティ」を表示
3.「互換性」タブに移動
4.「管理者としてこのプログラムを実行する」にチェックを入れる
5.「OK」を押す

日々日記DEMO by Small Ocean