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.
I have not checked in here for some time since I thought it was getting boring, but the last few posts are good quality so I guess I will add you back to my everyday bloglist. You deserve it my friend 🙂
I absolutely love your blog and find nearly all of your post’s to be precisely what I’m looking for. can you offer guest writers to write content available for you? I wouldn’t mind publishing a post or elaborating on many of the subjects you write in relation to here. Again, awesome website!
Attractive component of content. I simply stumbled upon your web site and in accession capital to assert that I get in fact enjoyed account your weblog posts. Anyway I will be subscribing in your feeds or even I achievement you get right of entry to persistently rapidly.
You really make it appear so easy together with your presentation but I in finding this topic to be really one thing which I think I might by no means understand. It seems too complicated and extremely vast for me. I am looking forward on your subsequent publish, I?ll try to get the hold of it!
Thanks for your publication. I would also like to comment that the very first thing you will need to conduct is find out if you really need repairing credit. To do that you must get your hands on a copy of your credit rating. That should not be difficult, since government necessitates that you are allowed to have one no cost copy of the credit report yearly. You just have to request the right individuals. You can either read the website for the Federal Trade Commission or perhaps contact one of the leading credit agencies instantly.
I have noticed that of all types of insurance, health insurance coverage is the most debatable because of the clash between the insurance coverage company’s need to remain afloat and the user’s need to have insurance policy. Insurance companies’ profits on health plans are very low, therefore some businesses struggle to make money. Thanks for the suggestions you write about through this web site.
The other day, while I was at work, my sister stole my
iPad and tested to see if it can survive a 40 foot drop, just so she can be a youtube sensation. My apple ipad is now destroyed and she has 83 views.
I know this is completely off topic but I had to share it with someone!
Hi there, I discovered your web site via Google whilst searching for a similar matter, your website got here up, it seems great. I have bookmarked it in my google bookmarks.
Thanks for revealing your ideas. One thing is that learners have a choice between federal student loan and also a private student loan where it really is easier to select student loan debt consolidation reduction than with the federal student loan.
It is best to participate in a contest for one of the best blogs on the web. I will advocate this website!
These days of austerity and relative anxiousness about having debt, many individuals balk resistant to the idea of making use of a credit card to make acquisition of merchandise or perhaps pay for any gift giving occasion, preferring, instead only to rely on the particular tried and also trusted procedure for making settlement – raw cash. However, if you possess the cash available to make the purchase completely, then, paradoxically, that’s the best time for you to use the cards for several factors.
I have learned newer and more effective things by means of your web site. One other thing I would really like to say is always that newer pc operating systems tend to allow more memory for use, but they likewise demand more memory simply to perform. If people’s computer could not handle far more memory plus the newest application requires that memory space increase, it may be the time to buy a new Personal computer. Thanks
Good ? I should definitely pronounce, impressed with your site. I had no trouble navigating through all the tabs as well as related information ended up being truly simple to do to access. I recently found what I hoped for before you know it in the least. Quite unusual. Is likely to appreciate it for those who add forums or something, web site theme . a tones way for your customer to communicate. Excellent task..
My coder is trying to persuade me to move to .net from PHP. I have always disliked the idea because of the expenses. But he’s tryiong none the less. I’ve been using Movable-type on a variety of websites for about a year and am anxious about switching to another platform. I have heard great things about blogengine.net. Is there a way I can import all my wordpress posts into it? Any help would be really appreciated!
Thank you for sharing superb informations. Your site is very cool. I’m impressed by the details that you?ve on this web site. It reveals how nicely you understand this subject. Bookmarked this web page, will come back for extra articles. You, my friend, ROCK! I found simply the info I already searched all over the place and just could not come across. What a perfect web-site.