Chapter 14: Working with Dates, Embedding Content, and Extracting Data
In this chapter, we'll explore working with date fields in Looker Studio and discover how to manipulate and utilize them effectively in our reports. We'll then delve into the "Extract Data" connector, understanding its use cases and benefits, and finish by learning how to embed external content within Looker Studio dashboards. Follow along as we navigate through these essential topics in Looker Studio.
14.1. The Date Functions
In this first part, we’ll discuss Working with Dates. We’ll take a look at the different fields of date types, different ways we can work with dates, as well as the different date functions and their use cases.
Working with Date Fields
Date and time fields are commonplace in reporting as they typically represent vital data points for analysis. As such, understanding how to work with date fields and how they interact with reporting components, such as a date range selector, is essential.
In this lesson, we work with a dataset comprising user IDs, the date users become leads, and the date users become paid members. The goal is to calculate the number of days it takes for a user to convert from a lead to a paid member, as well as the conversion rate per week. Let's begin by learning about the DATE_DIFF function.
The DATE_DIFF() Function
The DATE_DIFF function calculates the difference in days between two date fields. By simply passing two date fields to this function, you can quickly determine the number of days between these dates. In our example, we used the following formula to obtain the desired result:
DATE_DIFF(Date_Become_Lead, Date_Become_Paid_Member)However, Looker Studio doesn't offer a custom function for obtaining the weeks directly. To calculate the number of weeks between the two dates, we need to divide the result of DATE_DIFF by seven and round it with zero decimals.
The result can be obtained using the following formula:
ROUND(DATE_DIFF(Date_Become_Lead, Date_Become_Paid_Member) / 7, 0)In the next Lessons, we will introduce DATETIME_DIFF() function in Looker Studio, which can help you determine the difference between two datetime fields. Similar to DATE_DIFF, it returns the difference in units like seconds, minutes, hours, days, etc.
📩 Receive my weekly Looker Studio tips
🖇 Connect with me on LinkedIn