Find the first day of the week in Looker Studio

Why did the data analyst get lost in Looker Studio while searching for the first day of the week?

Because every time they thought they found it, it turned out to be just another “Monday”!

However, there are solutions for every case. While using time line charts in Looker Studio you may have noticed that the dates look weird and not really understandable. I have a solution – we need to find the first day of the week, and it will bring your chart to the next level!

Check out the video tutorial:

Usage cases

In data analysis and reporting, understanding weekly trends is crucial for making informed decisions. Looker Studio provides a powerful tool to extract the first day of the week from a given date using the DATETIME_TRUNC function.

Imagine you’re analyzing sales data and want to track weekly performance. By utilizing DATETIME_TRUNC, you can easily group sales data by the first day of each week, providing a clear picture of weekly trends and fluctuations. This functionality is particularly useful for comparing week-over-week performance, identifying patterns, and making strategic adjustments to optimize sales efforts.

For instance, if you’re analyzing e-commerce sales, you can use DATETIME_TRUNC to aggregate sales data by the first day of each week, allowing you to pinpoint which weeks exhibit the highest sales volume or the most significant changes in customer behavior. This information can then inform marketing strategies, inventory management, and resource allocation for future weeks.

Creating the formula

We can have a very simple table, containing dates and active users. Looks fine, but when you create a time series chart, it will look confusing enough. 

What formula do we use?

Luckily, Looker Studio has plenty of formulas to rescue us from different situations. 

If we want to show the first day of the week starting on Sunday, we need to add a new column to our table and create a new dimension field.

Let’s call it “Sunday”. We need to write a formula:

DATETIME_TRUNK(Date, Week)

Make sure the data format to be “Date”, not “Date Time”.

Here is what the formula syntax needs to be. 

However, if you want to present the first day of the week starting on Monday, you need to modify this formula a tiny bit:

DATETIME_TRUNK(Date, ISOWEEK)

So, let’s see how our table looks now.

And here is how our chart looks like. Much simpler and easier to understand, isn’t it?

Summary

Understanding and effectively working with dates is essential for any data analyst or business professional. Whether you’re tracking sales performance, analyzing user behavior, or monitoring trends over time, having the ability to aggregate data at different time intervals is crucial. That’s where DATETIME_TRUNC comes in handy.

If you work with Looker Studio, I cannot miss a chance to mention that in the Gaille Reports template store you can find various report templates that will save you much time and effort. The most popular of them are: Cohort Analysis Template. LTV and Customer Retention, Plan & Fact spending trajectory template for paid ads traffic sources and RFM analysis for Customer Segmentation.

Hope you liked this article! Share your opinion about it, I will be very glad to hear it from you!