Dynamic Cohorts

[kellipeluso]

Hello,

Our team is currently working on several requests that involve the ability to create custom cohorts. Here are some examples of cohort related problems we’re working on:

1. If customer bought x product in time frame A, what products did they buy in time frame B (time frame b can be in the future or in the past)
2. Flow of products purchased from first order, to second order, to third order.

I created a derived table for cohorts based off of information from the Looker Community such as:

https://community.looker.com/lookml-5/analytic-block-dynamic-previous-period-analysis-using-date-sta...https://community.looker.com/lookml-5/analytic-block-cohorting-users-by-creation-date-and-order-tran...https://community.looker.com/lookml-5/analytic-block-cohorts-as-filters-using-templated-filters-6065

Using the info from these articles I was able to create a cohort based on their purchase date if the comparison date range is always the same. For example, if the user selects date range A when the product purchase was made, the comparison date range is a static 2 previous years. This works, but we are trying to gain the capability of making these cohorts dynamic for our end users instead of creating custom work for them each time we get a request. 

Any help or guidance would be greatly appreciated!

Thanks

[Ryan_Johnson11]

Do you have any experience using liquid for dates? Without seeing your LookML/SQL that would be my first approach. Then you could label the filters as timeframe A and timeframe B.

You also can decide where to use the liquid based on performance; if this is a small table that you do not mind rebuilding frequently, I would suggest the liquid embedded inside the derived table. If larger, I would try working within dimensions/measures.

[kellipeluso]

hi @Ryan_Johnson11 thanks for the response! we have been using templated filters so far for our date ranges. I’ve been trying to work with liquid parameters so we’re able to create labels for time frame A and time frame B to toggle between, however I haven’t gotten that to work just yet. I’m currently using a yesno filter to toggle between timeframe A and timeframe B. 

This below code is currently working, however it only works with a static date range for time frame b: 

view: customer_product_cohort {

  derived_table: {

    sql:

      select
        distinct fct_line_items.user_identifier
      from
        --if dev -- {{_user_attributes['dbt_schema']}}.fct_line_items
        --if prod -- analytics.fct_line_items

      where {% condition cohort_purchase_period %} order_completed_at {% endcondition %}
        and is_completed = 1
        and order_type = 'ecom'
        and ({% condition cohort_filter_class_style %} class_style {% endcondition %})
        and ({% condition cohort_filter_color %} color {% endcondition %})
        and ({% condition cohort_filter_department %} department {% endcondition %})
        and ({% condition cohort_filter_class %} class {% endcondition %})
        and ({% condition cohort_filter_otb_class %} otb_class {% endcondition %})
        and ({% condition cohort_filter_sku %} sku {% endcondition %})
        and ({% condition cohort_class_style_color %} class_style_color {% endcondition %})
        ;;
    }

    dimension: user_identifier {
      description: "Unique ID for each user that has ordered"
      type: number
      sql: ${TABLE}.user_identifier ;;
      primary_key: yes
      hidden: yes
    }

    filter: cohort_filter_class_style {
      description: "Class Style to filter cohort - filter on all users that purchased this class style"
      type: string
      suggest_explore: order_items
      suggest_dimension: order_items.class_style
    }

    filter: cohort_filter_color {
      description: "Color to filter cohort - filter on all users that purchased this color."
      type: string
      suggest_explore: order_items
      suggest_dimension: order_items.color
    }

    filter: cohort_filter_department {
      description: "Item department to filter cohort - filter on all users that purchased an item from selected department."
      type: string
      suggest_explore: order_items
      suggest_dimension: order_items.department
    }

    filter: cohort_filter_class {
      description: "Class to filter cohort - filter on all users that purchased an item from this class."
      type: string
      suggest_explore: order_items
      suggest_dimension: order_items.class
    }

    filter: cohort_filter_otb_class {
      description: "OTB Class to filter cohort - filter on all users that purchased an item from this OTB Class."
      type: string
      suggest_explore: order_items
      suggest_dimension: order_items.otb_class
    }

    filter: cohort_filter_sku {
      description: "SKU to filter cohort - filter on all users that purchased an item with this SKU."
      type: string
      suggest_explore: order_items
      suggest_dimension: order_items.sku
    }

  filter: cohort_class_style_color {
    description: "Class, style and color concatenated - filter on all users that purchased an item with this combination of class, style and color."
    type: string
    suggest_explore: order_items
    suggest_dimension: order_items.class_style_color
  }

    filter: cohort_purchase_period{
      type: date
      suggest_explore: order_items
      suggest_dimension: order_items.order_completed_date
    }

  filter: same_time {
    type: yesno
    sql:
        case
                when {% condition same_time %} TRUE {% endcondition %}
                  then {% condition cohort_purchase_period %} order_completed_at {% endcondition %}
                when {% condition same_time %} FALSE {% endcondition %}
                  then order_completed_at >= dateadd(month, 1, {% date_start cohort_purchase_period %})
                    and order_completed_at < {% date_end cohort_purchase_period %}
              end ;;
    }
  }

Ideally we want to be able to give the end users the ability to pick a range for both time frame A and time frame B instead of it just being a static time frame B. 

This derived table is currently joined to our order items table with an inner join.