#
Blog post
16/4/2025

How to calculate the date of Easter in BigQuery

How to calculate the date of Easter in BigQuery

Easter is a movable feast, and its date changes every year. If you work with data — whether you are analyzing seasonal traffic trends, comparing campaign performance, or planning marketing activities — it can be useful to know the exact dates of the Easter holidays. That is where a simple SQL script for BigQuery can come in handy.

How is the date of Easter determined?

Easter is a movable feast — unlike Christmas, it does not fall on the same date each year. To put it simply, Easter Sunday is celebrated on the first Sunday after the first full moon following the spring equinox (approximately March 21).

To calculate the exact date in our solar (Gregorian) calendar, we need to combine the lunar cycle (the phases of the Moon) with the calendar year. This process is historically known as computus paschalis (Latin for “the calculation of Easter”), and often simply referred to as the Computus. Because the lunar and solar cycles are not aligned, mathematician Carl Friedrich Gauss created a formula that translates the rules based on the full moon and equinox into an actual date in the Gregorian calendar.

The result is a surprisingly elegant set of calculations — widely known and available online in languages like Python, standard SQL, and more. However, when I needed a version for BigQuery, I could not find a ready-to-use script to copy & paste. But now you don’t have to search — we’ve prepared it for you!

What does our script do?

Using the Computus algorithm, it calculates the exact date of Easter Sunday for each year (by default between 2024 and 2100, though you can easily change the year range). It then also outputs the related Easter dates in the YYYY-MM-DD format:

  • Good Friday (2 days before),
  • Holy Saturday (1 day before),
  • Easter Monday (1 day after).

What is it useful for?

  • Filtering specific days in GA4 data – this helps remove holiday-related fluctuations from the analysis of regular trends (e.g. traffic, conversions).

  • Tagging holidays in dashboards – this allows you to add holidays as flags in dashboards (e.g. Looker Studio, Power BI) connected to BigQuery.

  • More accurate campaign comparisons – Easter holidays can be used as a segment when evaluating campaign performance — in GA4, Google Ads, or other systems connected to BigQuery.

How does the script work?

The BigQuery script generates a list of years using GENERATE_ARRAY and then applies a set of mathematical operations (MOD, CONCAT, FORMAT) to calculate the exact date of Easter using a well-known algorithm.

  1. Generating the years

In the first step (WITH years AS ...), the script creates a list of years for which we want to calculate Easter (by default, 2024 to 2100).

  1. Calculating intermediate steps (mods)

Using several MOD operations, the script calculates intermediate variables a, b, c, d, and e, which are part of Gauss’s formula.

For example, a = year MOD 19 represents the "Golden Number" in the lunar cycle.

  1. Determining the date of Easter Sunday

Using CASE WHEN logic, the script calculates the final date in yyyymmdd format, depending on whether Easter falls in March or April.

  1. Adding the surrounding days

With CROSS JOIN UNNEST(GENERATE_ARRAY(-2, 1)), the script adds the related days:

  • Friday (−2)
  • Saturday (−1)
  • Sunday (0)
  • Monday (+1)
  1. Final output

The script returns dates in YYYY-MM-DD format that correspond to Easter holidays for each year — ready to be joined to other tables or time dimensions.

You can find the full query on our GitHub.

Hopefully, Easter will never catch you off guard in your data again. Let us know — for example on LinkedIn — how you used the script in your own projects.

Authors

#
Blog post
How to calculate the date of Easter in BigQuery
16/4/2025

Easter is a movable feast, and its date changes every year. If you work with data — whether you are analyzing seasonal traffic trends, comparing campaign performance, or planning marketing activities — it can be useful to know the exact dates of the Easter holidays. That is where a simple SQL script for BigQuery can come in handy.

#
Podcast
Socials: Vašek Jelen discusses GA4, server-side tracking, BigQuery and connecting customer data with campaign performance
19/11/2024

Socials podcast and 80 minutes of conversation with Daniel Bauer and Otakar Lucák about digital analytics, with a focus on e-commerce. The guys deal with a number of specific topics in their client work and had some great questions. Thanks to this, I think we kept it very practical, and the podcast includes our opinions on how to resolve real-life situations from practice.

#
Blog post
MeasureCamp Prague 2024: Using Google Ads export in Google BigQuery
10/9/2024

On Saturday, the ČSOB building in Prague was buzzing with analytics topics. A large part of the MeasureDesign team showed up for the 10th anniversary edition of MeasureCamp Czech Republic — and Vašek and Anička gave a talk on working with the Google Ads dataset in Google BigQuery.

#
Blog post
Data retention: Storing data in Google Analytics 4
31/8/2024

Data retention in GA4 determines how long information about users and events will remain available. By default, this period is only two months, which can limit your analysis options. In this article, you'll learn how to extend this period to up to 14 months (or 50 months with GA4 360) and what the retention setting does not affect.

#
Blog post
Workshop: GA4 basics for the Tereza non-profit organization
3/6/2024

On the last day of May, we spent time with the team from the Tereza non-profit organization, focusing on the basics of Google Analytics 4. We concentrated on the practical use of data, especially for the Učíme se venku ("Learning Outdoors") program, which helps teachers bring lessons from the classroom to the outdoors.

#
Blog post
Reshoper 2024: New opportunities in analytics
20/5/2024

At the Reshoper conference, I had the opportunity to give a talk where I summarized new opportunities for e-commerce analytics. In the presentation, I shared my experience and approaches on how to get the most out of Google Analytics 4 — especially when combined with BigQuery and other Google Cloud services.

#
Blog post
Marketing Festival 2024: Learn to work with GA4 data in BigQuery and GCP
22/2/2024

This workshop focused on working with GA4 data in BigQuery and Google Cloud. My goal was to help participants move beyond the GA4 interface and show that working with raw GA4 data is not rocket science :) On the contrary, it is a valuable skill that is worth learning, because raw GA4 data hold huge potential for monetization and activation. I also shared real-world examples and reporting concepts from companies that rely entirely on BigQuery data. The participants were fantastic, and it was great to see how many people are actively exploring BigQuery and GCP. It felt like we were all on the same wavelength.

#
Webinar
Tips and tricks for GA4 not just for Shoptet users
25/11/2023

A recording of the public webinar we hosted with Marek Čech for Shoptet. The main topic was practical recommendations for evaluating campaigns in GA4 in connection with the upcoming Black Friday and Christmas season.

#
Webinar
Webinar: Evaluating GA4 Data in BigQuery
21/6/2023

Together with Vašek Ráš, we hosted a public webinar on evaluating campaigns using the GA4 dataset in Google BigQuery. Our guest speaker was Honza Tichý, who presented a section on DBT.

Anna Horáková
Analyst
Zuzana Mikyšková
Analyst & Co-Founder
Vašek Jelen
Lead Analyst & Co-Founder
Blanka Hejduková
Back Office
Markéta Svěráková
Analyst
Petra Súkeníková
Analyst
Klára Belzová
Analyst
Anna Horáková
Anna Horáková
Analyst

Anička has over 7 years of experience in the agency world, where she has managed social media ad campaigns for clients, and especially for content-driven websites, her favorite. Wanting to broaden her perspective beyond campaign data, she gradually shifted her focus toward web analytics. She joined our team in 2022 and now specializes in data analytics, using GA4, BigQuery, Looker Studio, and other tools to connect and dig deeper into data — delivering insightful analyses and valuable input for business decisions.

Zuzana Mikyšková
Zuzana Mikyšková
Analyst & Co-Founder

Zuzka's career path led her through corporate innovation and research management, running word-of-mouth projects, and later to a digital agency, where she managed website development projects. However, Zuzka is naturally curious and wanted to understand how a website actually works once it is launched into the world. That curiosity led her to study web analytics — and eventually to a key collaboration with Vašek. In 2019, they founded the company together.

Vašek Jelen
Vašek Jelen
Lead Analyst & Co-Founder

Vašek has been working in digital analytics for over 15 years — from setting up tracking to data storage, visualization, and interpretation. He helps companies bring organize their data and make full use of it. He focuses primarily on data from digital platforms such as websites, apps, and client zones, and on connecting that data with other business data like media and customer data. After years of freelancing, he co-founded the analytics studio MeasureDesign, where, in addition to working on analytics projects and bespoke training sessions, he also mentors and educates new analysts.

Blanka Hejduková
Blanka Hejduková
Back Office

Blanka joined our team in 2024 and has been responsible for back-office operations, including invoicing and administrative tasks, ever since. She draws on her experience from the Czech Post and her background in financial management to keep everything running smoothly. In her free time, she enjoys traveling with her two children and finds relaxation in working in her garden.

Markéta Svěráková
Markéta Svěráková
Analyst

Markéta started out in marketing, but then came maternity leave — and with it, total chaos. In an effort to hold on to the last bits of sanity, she turned to data. After all, numbers don’t yell, spill cereal into your keyboard, and at least they make some sense. She completed a data analytics course at Engeto Academy, where she bonded with SQL, Power BI, Excel, and Python, and started looking for patterns outside the bounds of children’s coloring books. Today, at MeasureDesign, she helps clients understand what their numbers are really saying.

Petra Súkeníková
Petra Súkeníková
Analyst

After finishing her studies in digital marketing, Péťa dived into the world of e-commerce. She was responsible for launching several new online stores and later managing their online marketing. Even during her studies, she was interested in web analytics and data work, and while working on e-shops, she wanted to deepen her knowledge in this field.

Klára Belzová
Klára Belzová
Analyst

Klára has been with the company since 2019. She focuses mainly on web analytics but is not afraid to dive into data work in BigQuery. What she enjoys most is guiding clients through the entire process — from defining their needs to implementing tracking and creating the final data visualizations. She gets an almost suspicious amount of joy from a clean and well-organized GTM container or a report full of useful data.