Скачать или смотреть How to Analyze Rolling Month Data in BigQuery

Learn how to retrieve rolling month data in BigQuery to track user engagement across multiple months, including SQL queries and practical examples.
---
This video is based on the question https://stackoverflow.com/q/62390791/ asked by the user 'Pramod yadav' ( https://stackoverflow.com/u/5048932/ ) and on the answer https://stackoverflow.com/a/62394641/ provided by the user 'Mikhail Berlyant' ( https://stackoverflow.com/u/5221944/ ) 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, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Bigquery Rolling month data

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: The Challenge of Rolling Month Data

When working with user engagement data, especially in scenarios like tracking page visits over different months, it can be challenging to aggregate and analyze this data efficiently. If you're tasked with analyzing how many times a user visited a particular page over a rolling three-month period, it's crucial to set up your SQL queries correctly in Google BigQuery.

In this guide, we'll walk through a practical example to demonstrate how to achieve this. We'll bring together a dataset that captures user visits to various pages—like the homepage and account page—and use SQL to retrieve how many times users engaged with these pages over a rolling three-month timeframe.

Understanding the Dataset

To begin with, we will work with a specific dataset that records the page visits by users. Here's an overview of the sample data we'll be using:

MMDDYYPageVisitedUsernameNo. of TimesMonth1/1/2019HomepageA1January2/21/2019AccountPageA1February2/25/2019AccountPageB5February3/1/2019HomepageA3March...............The goal here is to determine how many times users visited these pages in the current month and the two subsequent months. For instance, if we are looking at January, we want to sum the visits from January, February, and March.

Crafting the SQL Query

To achieve the rolling month data calculation, we'll utilize a SQL query in BigQuery that leverages window functions. Here’s how you can construct such a query:

Basic SQL Query Structure

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

Breakdown of the Query

Common Table Expression (CTE): We define our dataset within a CTE, which allows us to work with a temporary result set.

SELECT Statement: We select all columns from the dataset and add our rolling sum computation.

Window Function:

The SUM() function is used with the OVER() clause to compute the rolling total.

PARTITION BY: We group our results by username and pagevisited.

ORDER BY: We order the results based on the difference between the parsed date and a reference date (January 1, 1970).

RANGE: It defines a range of the current row and the next two rows to calculate the rolling sum.

Interpreting the Results

Once you've executed the query, you'll receive an output that includes the rolling sums for user engagements over the specified three months.

Here’s an example output structure:

mmddyyyypagevisitedusernameno_of_timemonthrolling_3_month1/1/2019HomepageA1January42/21/2019AccountPageA1February1..................Conclusion

By following the steps outlined in this post, you can efficiently retrieve and analyze rolling month data in BigQuery. Whether you're tracking user engagement for web pages or gathering insights for analytical purposes, knowing how to perform this analysis is invaluable.

Feel free to adapt the SQL query for your specific datasets and requirements. Happy querying!