Скачать или смотреть How to Calculate the 3-Month Rolling Sum of Emails in Google BigQuery

In this post, we explore how to effectively calculate the `3-month rolling sum` of emails sent per customer using SQL in Google BigQuery. Follow our step-by-step guide to handle NULL values and apply window functions correctly.
---
This video is based on the question https://stackoverflow.com/q/77783339/ asked by the user 'xboraxe' ( https://stackoverflow.com/u/22989248/ ) and on the answer https://stackoverflow.com/a/77783370/ provided by the user 'Thorsten Kettner' ( https://stackoverflow.com/u/2270762/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, comments, revision history etc. For example, the original title of the Question was: Sum of emails received in the last 3 months

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Introduction

Managing customer communications is crucial for any business, and understanding how many emails you’ve sent can provide insights into customer engagement. If you're working with Google BigQuery and have a dataset of emails sent to customers, you might be interested in calculating the 3-month rolling sum of emails sent per customer.

This can become particularly complex if your dataset includes NULL values for some months, which indicates that no emails were sent during that time. In this guide, we will break down the approach to achieve that using SQL in Google BigQuery.

Understanding the Problem

In our example, we have a dataset that tracks the number of emails sent to different customers over time. Here's a simplified structure of the table:

cust_id
eom_date
email_sent
1
2023-06-30
2
1
2023-05-31
NULL
1
2023-04-30
3
...
...
...

The goal is to compute the total number of emails sent to each customer over a rolling three-month period, taking into account months where the email count is NULL.

The desired output should look something like this:

cust_id
eom_date
email_sent
total_email_3month
1
2023-06-30
2
5
1
2023-05-31
NULL
5
...
...
...
...

Solution Breakdown

To tackle this problem effectively, we will use a combination of SQL aggregate functions and window functions. Below are the key sections of our solution:

Step 1: Prepare the Basic Query

We start with a basic aggregation to calculate total emails sent per customer for each date. This is achieved using the SUM() function:

[[See Video to Reveal this Text or Code Snippet]]

Handling NULL Values

We need to ensure that NULL values are handled correctly. In SQL, SUM() ignores NULL, so just counting on it will give us the appropriate outcome as far as totals are concerned.

Step 2: Calculate the 3-Month Rolling Sum

Next, we want to apply a rolling window to aggregate these sums over the last three months. We use the SUM(SUM(email_sent)) OVER (...) pattern to achieve this.

Important Note on Range Limitations

Google BigQuery does not support a three-month interval directly in the RANGE BETWEEN clause. Instead, we will consider a 90-day range. Here’s how the complete query will look:

[[See Video to Reveal this Text or Code Snippet]]

Explanation of Key Components

PARTITION BY cust_id: This ensures that the sum calculation is done for each customer separately.

ORDER BY UNIX_DATE(eom_date): Orders the emails by their date. Using UNIX_DATE allows for easy manipulation as it converts the date to a numerical format.

RANGE BETWEEN 90 PRECEDING AND CURRENT ROW: This specifies that the rolling sum should include the current row and the 90 days before the current date.

Conclusion

By following the steps outlined above, you should be able to calculate the 3-month rolling sum of emails for each customer in your dataset using Google BigQuery effectively. Properly handling NULL values and using window functions can make a significant difference in your analysis.

Now, armed with this knowledge, you can better track email correspondence and maximize your engagement strategies!