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)→ 14.2. Calculating Membership Duration with CURRENT_DATE()
→ 14.3. Advanced Date Range Selector
→ 14.4. WEEKDAY() Function
→ 14.5. DATETIME_ADD() in Looker Studio
→ 14.6. DATETIME_DIFF() in Looker Studio
→ 14.7. EXTRACT() and DATE()
→ 14.8. Embedding Videos in Looker Studio
→ 14.9. Embedding Forms in Looker Studio
→ 14.10. Embedding Google Docs
→ 14.11. Quicker Data Freshness with Blending & BigQuery
→ 14.12. Extract Data in Looker Studio
→ 14.13. Creative Use Cases for Extract Data in Looker Studio
→ 14.14. Discussion: Dashboard Types in Looker Studio
→ 14.15. Discussion: Dashboard Design and Layout Considerations
📩 Receive my weekly Looker Studio tips
🎥 Subscribe to my YouTube channel
🖇 Connect with me on LinkedIn