Simple Period-to-Date Comparisons in BigQuery (via Colab or Looker)
Performing period-over-period analyses with SQL always seems to feel complicated. If you’re anything like me, each time you need to do some “Period-to-Date” comparisons you wind up searching Google for examples of queries you can leverage. Hopefully this post will make your searching a bit more fruitful!
Here, I’ll break down how we can use BigQuery to perform a simple analysis where we want to look at Week to Date (WTD), Month to Date (MTD), Quarter to Date (QTD) or Year to Date (YTD) aggregations and compare them to the prior period (i.e. last week / month / quarter / year up until the same day).
First, I’ll walk through an example in Python, using a iPythonNotebook in Google Colab. Next, I’ll show it in LookML, Looker’s modeling language. In this example, the current date is 2021–04–13.
Using Google Colab (full notebook here)
Let’s start with a user entered parameter that will represent the period of time we are considering — either week, month, quarter or year.
timeframe = "quarter" #@param ["week", "month", "quarter","year"]This code snippet allows you to select the timeframe from a drop down in the notebook. Here, we’ve select quarter.
The first thing we’ll do is determine the start date of the current period we’re considering. In BigQuery we can easily do this using the DATE_TRUNC function, which allows us to truncate a date value into a specific time period. BigQuery returns the first date of that period.
query = "SELECT DATE_TRUNC(CURRENT_DATE(),{timeframe}) as current_period_start".format(timeframe=timeframe)>>> 2021-04-01 (first day of the current quarter)
Next, we need to determine the start date of the prior period. In this case, we can just use DATE_SUB to subtract the time period from our current date and then use DATE_TRUNC function to again grab the first date in that period of time.
query = "SELECT DATE_TRUNC(DATE_SUB(CURRENT_DATE(), INTERVAL 1 {timeframe}), {timeframe}) as prior_period_start".format(timeframe=timeframe)>>> 2021-01-01 (first day of previous quarter)
Now we want to determine the last day in the prior period. We want our current period and prior period to be the same number of days — so we’ll need to first use DATE_DIFF to calculate the total number of days in the current period, then add use DATE_ADD to add the result to our prior period start.
query = """SELECT DATE_ADD(DATE_TRUNC(DATE_SUB(CURRENT_DATE(), INTERVAL 1 {timeframe}), {timeframe}), INTERVAL DATE_DIFF(CURRENT_DATE(), DATE_TRUNC(CURRENT_DATE(),{timeframe}), DAY) DAY) as prior_period_end""".format(timeframe=timeframe)>>> 2021-01-13 (last day that we will consider in prior quarter)
Finally, we can use a case statement to categorize each date as being in our current time period, prior time period, or NULL (outside of the time period’s we care about), and use this as our field to aggregate against.
query = """SELECT--my date is after the current period start
CASE WHEN {my_date_column} >= DATE_TRUNC(CURRENT_DATE(),{timeframe}) THEN 'This {timeframe} to Date'--my date is between the prior period start and end
WHEN {my_date_column} >= DATE_TRUNC(DATE_SUB(CURRENT_DATE(), INTERVAL 1 {timeframe}), {timeframe}) AND {my_date_column} <= DATE_ADD(DATE_TRUNC(DATE_SUB(CURRENT_DATE(), INTERVAL 1 {timeframe}), {timeframe}), INTERVAL DATE_DIFF(CURRENT_DATE(), DATE_TRUNC(CURRENT_DATE(),{timeframe}), DAY) DAY) THEN 'Prior {timeframe} to Date' ELSE NULL END as time_period,--sum up the specified column and group by period to aggregate
round(sum({my_summed_column}),2) as total
from {my_table}
group by 1""".format(timeframe=timeframe, # enter in your own values here!
my_table="ecomm.order_items", my_summed_column="sale_price", my_date_column="date(created_at)")>>> Prior quarter to Date: 174868.86
>>> This quarter to Date: 179155.37
Put it all together, and you have a self service “Period-to-Date” analysis!
Using Looker (full LookML file here)
In Looker, we can use these queries to create different dimensions in LookML. First, we create a parameter that allows the user to select the timeframe of interest.
parameter: timeframe {
view_label: "Period over Period"
type: unquoted
allowed_value: {
label: "Week to Date"
value: "Week"
}
allowed_value: {
label: "Month to Date"
value: "Month"
}
allowed_value: {
label: "Quarter to Date"
value: "Quarter"
}
allowed_value: {
label: "Year to Date"
value: "Year"
}
default_value: "Quarter"
}Next, we calculate the start date of the current period by truncating the current date to our selected timeframe
dimension: first_date_in_period {
view_label: "Period over Period"
type: date
sql: DATE_TRUNC(CURRENT_DATE(), {% parameter timeframe %});;
}Now, we’ll calculate the total number of days in the period by taking the difference between the current date and the first date in our time period
dimension: days_in_period {
view_label: "Period over Period"
type: number
sql: DATE_DIFF(CURRENT_DATE(),${first_date_in_period}, DAY) ;;
}Next, we get the first date in the prior period by subtracting one timeframe from the current date, and truncating that value to the first date in the timeframe.
dimension: first_date_in_prior_period {
view_label: "Period over Period"
type: date
hidden: no
sql: DATE_TRUNC(DATE_SUB(CURRENT_DATE(), INTERVAL 1 {% parameter timeframe %}),{% parameter timeframe %});;
}Now, get the last date in the prior period by adding the total number of days in the period to the start date of the prior.
dimension: last_date_in_prior_period {
view_label: "Period over Period"
type: date
hidden: no
sql: DATE_ADD(${first_date_in_prior_period}, INTERVAL ${days_in_period} DAY) ;;
}Finally, calculate which period each date belongs in.
dimension: period_selected {
view_label: "Period over Period"
type: string
sql:
CASE
WHEN ${my_date} >= ${first_date_in_period}
THEN 'This {% parameter timeframe %} to Date'
WHEN ${my_date} >= ${first_date_in_prior_period}
AND ${my_date} <= ${last_date_in_prior_period}
THEN 'Prior {% parameter timeframe %} to Date'
ELSE NULL
END ;;
}Now our users can hop into the explore, and select the time period of interest.
If you’re interested in learning more about BigQuery or Looker, follow me on Linkedin and Twitter @LeighaJarett. Feel free to DM me with questions or feedback!
