Arbitrary Period Comparisons

[ernesto1]

Google Analytics has a date selector that allows for comparison of two arbitrary time periods on the same chart. Period over Period analysis is useful for looking at this 30 days vs the previous 30 days, but it doesn’t let you compare how two arbitrary periods might compare.

For example: What did registration for next week’s webinar look like in comparison to a similar one we did 3 months ago?

Arbitrary time period analysis in Google Analytics

One way to do this in Looker is to use liquid variables to create an explore that looks like this:

There’s a few things going on here:

1. First Period Filter & Second Period Filter: View filters to take input from the users
2. Days from First Period: The magic to make this possible; conditional logic to find the lowest non-negative value from each period.
3. Period Selected: used to pivot the chart and create the First Period and Second Period series

And here’s the LookML to put it together (for Redshift):

filter: first_period_filter {
    group_label: "Arbitrary Period Comparisons"
    type: date
  }
  
  filter: second_period_filter {
    group_label: "Arbitrary Period Comparisons"
    type: date
  }
  
  dimension: days_from_start_first {
    hidden: yes
    type: number
    sql: DATEDIFF('day',  {% date_start first_period_filter %}, ${created_date}) ;;
  }
  
  dimension: days_from_start_second {
    hidden: yes
    type: number
    sql: DATEDIFF('day',  {% date_start second_period_filter %}, ${created_date}) ;;
  }
  
  dimension: days_from_first_period {
    group_label: "Arbitrary Period Comparisons"
    type: number
    sql:
      CASE
       WHEN ${days_from_start_second} >= 0
       THEN ${days_from_start_second}
       WHEN ${days_from_start_first} >= 0
       THEN ${days_from_start_first}
      END;;
  }
  
  
  dimension: period_selected {
    group_label: "Arbitrary Period Comparisons"
    type: string
    sql:
        CASE
          WHEN ${created_raw} >=  {% date_start first_period_filter %}
          AND ${created_raw} <= {% date_end first_period_filter %}
          THEN 'First Period'
          WHEN ${created_raw} >=  {% date_start second_period_filter %}
          AND ${created_raw} <= {% date_end second_period_filter %}
          THEN 'Second Period'
          END ;;
  }

For BigQuery:

  filter: first_period_filter {
    group_label: "Arbitrary Period Comparisons"
    type: date
  }

  filter: second_period_filter {
    group_label: "Arbitrary Period Comparisons"
    type: date
  }

dimension: days_from_start_first {
hidden: yes
type: number
sql: DATE_DIFF( ${session_date}, CAST({% date_start first_period_filter %} AS DATE), DAY) ;;
  }

  dimension: days_from_start_second {
hidden: yes
type: number
sql: DATE_DIFF(${session_date}, CAST({% date_start second_period_filter %} AS DATE), DAY) ;;
  } 

    dimension: days_from_first_period {
type: number
sql:
  CASE
   WHEN ${days_from_start_first} >= 0
   THEN ${days_from_start_first}
   WHEN ${days_from_start_second} >= 0
   THEN ${days_from_start_second}
  END;;
}

  dimension: period_selected {
    group_label: "Arbitrary Period Comparisons"
    type: string
    sql:
        CASE
          WHEN ${session_raw} >=  {% date_start first_period_filter %}
          AND ${session_raw} <= {% date_end first_period_filter %}
          THEN 'First Period'
          WHEN ${session_raw} >=  {% date_start second_period_filter %}
          AND ${session_raw} <= {% date_end second_period_filter %}
          THEN 'Second Period'
          END ;;
  }