and,a株式会社 デジタルマーケティングエンジニア 佐藤洋太
GA4のSQLを開発で日付の出力の際にこんな経験はありませんか？
「日付が歯抜けで抽出されている？」
「飛び飛びで日付が生成されていて綺麗に1日おきにデータがない」
今回はデータ分析時における欠落日付の補完のお悩みを解決したいと思います。
準備
ごく簡単な例です。
まずはこのようなテーブルがあるとしましょう。
| 行 | date | url | count |
|---|---|---|---|
| 1 | 2022-09-01 | https://www.example.com/page01/ | 2 |
| 2 | 2022-09-02 | https://www.example.com/page01/ | 3 |
| 3 | 2022-09-03 | https://www.example.com/page01/ | 4 |
| 4 | 2022-09-05 | https://www.example.com/page01/ | 6 |
| 5 | 2022-09-07 | https://www.example.com/page01/ | 8 |
内容はpage01の日付ごとのページビュー数（ないしイベント数）とします。
注目するポイントは2022/09/04と2022/09/06のレコードがない点です。
このような欠落日付のレコードは分析時に取り回しが非常に悪いです。
では、2022/09/04と2022/09/06にcountが0と表示され分析時に取り回しがしやすいよう工夫をしてみましょう。
実践
SQLの処理順としては以下のようになります。
- 期間の日付を格納した配列を作成
- 欠落日付を含んだテーブルを作成
- 前述の２工程を結合。この際に値を変換
実際のコードはこちらです。
WITH
duration AS (
SELECT *
FROM UNNEST(GENERATE_DATE_ARRAY( DATE('2022-09-01'), DATE('2022-09-07') )) AS date
),
tbl AS (
SELECT date, url, count
FROM `project01.sample.table01`
)
SELECT
date,
COALESCE(url, "https://www.example.com/page01/") AS url,
COALESCE(count, 0) AS count,
FROM duration
LEFT JOIN tbl USING (date)解説
今回の肝となる部分です。
duration AS (
SELECT *
FROM UNNEST(GENERATE_DATE_ARRAY( DATE('2022-09-01'), DATE('2022-09-07') )) AS date
),2022/09/01〜2022/09/07の期間の配列をGENERATE_DATE_ARRAYというBigQueryの関数で生成し、UNNESTにより展開しています。
分析の期間を変更することで任意の期間の配列を生成できます。
GENERATE_DATE_ARRAYは非常に便利な関数ですので覚えておきましょう
https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators?hl=ja#generate_date_array
そうすることで、下記のような1日ずつの日付レコードが生成されます。
| 行 | date |
|---|---|
| 1 | 2022-09-01 |
| 2 | 2022-09-02 |
| 3 | 2022-09-03 |
| 4 | 2022-09-04 |
| 5 | 2022-09-05 |
| 6 | 2022-09-06 |
| 7 | 2022-09-07 |
次に先ほどの準備で用意したテーブルをそのまま抽出します。
tbl AS (
SELECT date, url, count
FROM `project01.sample.table01`
)最後に工程１、２で抽出したテーブルを結合します。
SELECT
date,
COALESCE(url, "https://www.example.com/page01/") AS url,
COALESCE(count, 0) AS count,
FROM duration
LEFT JOIN tbl USING (date)この時、コード上ではCOALESCEを使って値の置換を行っていますが、使わないとどうなるかの比較を以下に添付します。
置換なし
| 行 | date | url | count |
|---|---|---|---|
| 1 | 2022-09-01 | https://www.example.com/page01/ | 2 |
| 2 | 2022-09-02 | https://www.example.com/page01/ | 3 |
| 3 | 2022-09-03 | https://www.example.com/page01/ | 4 |
| 4 | 2022-09-04 | null | null |
| 5 | 2022-09-05 | https://www.example.com/page01/ | 6 |
| 6 | 2022-09-06 | null | null |
| 7 | 2022-09-07 | https://www.example.com/page01/ | 8 |
置換あり
| 行 | date | url | count |
|---|---|---|---|
| 1 | 2022-09-01 | https://www.example.com/page01/ | 2 |
| 2 | 2022-09-02 | https://www.example.com/page01/ | 3 |
| 3 | 2022-09-03 | https://www.example.com/page01/ | 4 |
| 4 | 2022-09-04 | https://www.example.com/page01/ | 0 |
| 5 | 2022-09-05 | https://www.example.com/page01/ | 6 |
| 6 | 2022-09-06 | https://www.example.com/page01/ | 0 |
| 7 | 2022-09-07 | https://www.example.com/page01/ | 8 |
分析時において取り回しがしやすくなっていることが想像できると思います。
GA4でページビュー数やイベント数を抽出する際に有用です。
今回は欠落日付を補完するテクニックのご紹介でした。
Major thanks for the article. Much obliged.
I really liked your blog post.Really looking forward to read more. Fantastic.
Thanks again for the blog article.Thanks Again. Really Great.
Thanks again for the blog post.Thanks Again. Really Cool.
Enjoyed every bit of your article post. Really Cool.
Thank you ever so for you article. Really Great.
It’s going to be end of mine day, however before end I am reading this wonderful piece of writing to increase my experience.
Really enjoyed this article.Much thanks again.
I appreciate you sharing this blog post. Will read on…
I am so grateful for your blog article.Really looking forward to read more. Awesome.
Fine way of telling, and fastidious piece of writingto obtain information regarding my presentation topic, whichi am going to convey in school.
Thank you for your blog article. Fantastic.
I do believe all the concepts you have offered to your post.They’re really convincing and can definitely work.Nonetheless, the posts are very quick for novices.Could you please extend them a little from subsequent time?Thanks for the post.
This is one awesome article post.Really looking forward to read more. Really Great.
I cannot thank you enough for the article post. Fantastic.
I really like and appreciate your article.Really thank you! Great.
Film, “Fly Girls.””Commending Females AirforcePilots,” U.S. Congressional Record.
I truly appreciate this article post. Really looking forward to read more. Much obliged. Flora Ive Moynahan
I blog frequently and I truly thank you for your content. Your article has really peaked my interest. I am going to bookmark your blog and keep checking for new details about once a week. I opted in for your Feed as well.
logan park apartments rentberry scam ico 30m$ raised apartments in california
Thanks for sharing, this is a fantastic blog post.Really looking forward to read more. Great.
Hello there! Would you mind if I share your blog with my zynga group? There’s a lot of folks that I think would really enjoy your content. Please let me know. Thank you
Awesome! Its actually awesome paragraph,I have got much clear idea about from this post.
Looking forward to reading more. Great article post.Much thanks again. Really Cool.
Fantastic article post.Really looking forward to read more.
Different types of stainless steel are created depending on the amount of nickel and also chromium in the alloy. Chrome, short for chromium,
Great read. This is an excellent, an eye-opener for sure! I really like your article. I enjoyed reading what you had to say.
These are actually impressive ideas in on the topic of blogging. You have touched some pleasant points here. Any way keep up wrinting.
You can certainly see your enthusiasm within the work you write. The arena hopes for even more passionate writers such as you who aren’t afraid to mention how they believe. At all times go after your heart.
college essay how to write an essay my homework type my essay
It is in reality a nice and useful piece of information. I’m glad that youshared this helpful info with us. Please stay us up to date like this.Thank you for sharing.
My relatives always say that I am killing my time here at net, except I know I am getting experience all the time by reading suchpleasant posts.
A round of applause for your article.Really looking forward to read more.
This is one awesome article post.Much thanks again. Much obliged.
Im grateful for the blog.Really thank you! Much obliged.
They might be either affordable or expensive (but solar sections are certainly worth considering) based on your requirements
You could also hold charity football games to raise money for local organizations as well.
Thanks-a-mundo for the article. Fantastic.
I wanted to thank you for this fantastic read!! I definitely enjoyed every bit of it. I have got you saved as a favorite to look at new stuff you postÖ
Thank you for the good writeup. It in fact was a amusement account it.Look advanced to far added agreeable from you! By theway, how can we communicate?
order generic vardenafil online – vardenafil prix generic vardenafil review
ivermectin pills human ivermectin cream 1 – ivermectin 250ml
Hello, all is going nicely here and ofcourse every one is sharing facts, that’sin fact fine, keep up writing.
best male enhancement pills legitimate online pharmacies india — ed medicine online
chloroquine phosphate generic name how does plaquenil work
ivermectin humans ivermectin for humans for sale
Hey, thanks for the article.Really looking forward to read more. Want more.
Major thankies for the article. Much obliged.
I need to to thank you for this good read!! I absolutely loved every bit of it. I have you book marked to look at new stuff you postÖ
rs also want to make revenue.It really is no wonder that lots of bettors domany factors to get and makeDiscord Free Accounts 2021 Discord Account And Passwordfree discord accounts
Fantastic blog article.Much thanks again. Great.
Im obliged for the blog article.Much thanks again.
Wow that was unusual. I just wrote an incredibly long comment but after I clicked submit my comment didn’t appear. Grrrr… well I’m not writing all that over again. Anyhow, just wanted to say superb blog!
Thanks a lot for the post.Much thanks again. Awesome.
Amazing! Its actually remarkable piece of writing, I have got much clear idea on the topic of from this paragraph.
ivermectin topical rosacea fenbendazole vs ivermectin
I’ll immediately clutch your rss feed as I can’t find your email subscription hyperlink ore-newsletter service. Do you have any? Please allow me know so that I could subscribe.Thanks.
I’m now not positive the place you’re getting your information, but great topic. I needs to spend some time finding out more or working out more. Thanks for excellent information I was on the lookout for this information for my mission.
Your comments may take several hours to import.
Do military services has started on YouTube, as for were trying to find your own video.
Thank you ever so for you article post.Really thank you! Will read on…
Great, thanks for sharing this blog article.Really thank you! Cool.
Appreciate you sharing, great post.Really looking forward to read more.
A round of applause for your article post.Really looking forward to read more. Fantastic.
Very neat post.Much thanks again. Fantastic.
Hello, yup this article is truly nice and I have learned lot of things from it about blogging. thanks.
Incredible story there. What happened after? Good luck!
Very neat blog article.Really looking forward to read more. Will read on…
Very nice post. I just stumbled upon your blog and wished to say that I’ve really enjoyedsurfing around your blog posts. In any case I will be subscribing to your rss feed and I hope you writeagain soon!
ivermectin over the counter – stromectol order ivermectin human
I need to to thank you for this very good read!! I certainly loved every little bit of it.I have you saved as a favorite to check out new stuff you post…
I do believe you will find there’s problem with your site making use of Internet explorer browser.
linetogel
sneakers
The information shared is of top quality which has to get appreciated at all levels. Well done…
Thanks for your article. I have continually observed that a majority of people are needing to lose weight as they wish to show up slim and attractive. Nonetheless, they do not often realize that there are additional benefits just for losing weight also. Doctors say that over weight people suffer from a variety of ailments that can be directly attributed to their particular excess weight. Thankfully that people who’re overweight and suffering from various diseases can help to eliminate the severity of their own illnesses by way of losing weight. It is easy to see a steady but notable improvement in health if even a minor amount of weight loss is accomplished.