Additions to Standard Qubit Looker Block for Merchandising Analytics

transaction_sequence.txt

##Additional LookML view (transaction_sequence.view.lkml)##

view: transaction_sequence {
  view_label: "Product Interactions and Sales"
  derived_table: {
    sql: WITH view AS (SELECT
        * EXCEPT(event_number)
      FROM
        (SELECT
          TIMESTAMP_ADD(meta_serverTs, INTERVAL 0 MICROSECOND) AS property_event_ts,
          meta_recordDate,
          CONCAT(IFNULL(CAST(context_id AS STRING),
              '-'),IFNULL(CAST('-' AS STRING),
              '-'),IFNULL(CAST(context_viewNumber AS STRING),
              '-')) AS view_id,
          CONCAT(IFNULL(CAST(context_id AS STRING),
              '-'),IFNULL(CAST('-' AS STRING),
              '-'),IFNULL(CAST(context_sessionNumber AS STRING),
              '-')) AS session_id,
        CONCAT(IFNULL(CAST(context_id AS STRING),
            '-'),IFNULL(CAST('-' AS STRING),
            '-'),IFNULL(CAST(context_entranceNumber AS STRING),
            '-')) AS entrance_id,
          context_id,
          type,
          MAX(FORMAT_TIMESTAMP('%F %H:%M', TIMESTAMP_TRUNC(TIMESTAMP_SECONDS((UNIX_SECONDS(meta_serverTs ) - MOD(UNIX_SECONDS(meta_serverTs ), (60*5)))), MINUTE))) OVER (PARTITION BY DATE(meta_serverTs)) AS last_minute5_interval,
          -- deduplicate by getting first chronological view from a set of duplicates
          ROW_NUMBER() OVER (PARTITION BY context_id, context_viewNumber ORDER BY meta_serverTs ASC) as event_number
        FROM
          `qubit-client-{{q_view_v01.project._parameter_value}}.{{q_view_v01.site._parameter_value}}__v2.event_ecView`
        WHERE
          context_id IS NOT NULL
          AND context_viewNumber IS NOT NULL
          AND context_sessionNumber IS NOT NULL
          AND context_entranceNumber IS NOT NULL
         )
      WHERE
        event_number = 1
    )
  ,  transaction AS (SELECT
        TIMESTAMP_ADD(meta_serverTs, INTERVAL 0 MICROSECOND) AS property_event_ts,
        transaction_id,
        context_id,
        basket_total_baseValue,
        basket_quantity,
        CONCAT(IFNULL(CAST(context_id AS STRING),
          '-'),IFNULL(CAST('-' AS STRING),
          '-'),IFNULL(CAST(context_viewNumber AS STRING),
          '-')) AS view_id,
        CONCAT(IFNULL(CAST(context_id AS STRING),
          '-'),IFNULL(CAST('-' AS STRING),
          '-'),IFNULL(CAST(context_viewNumber AS STRING),
          '-'),IFNULL(CAST('-' AS STRING),
          '-'),IFNULL(CAST(transaction_id AS STRING),
          '-'))
          AS view_transaction_id,
        CONCAT(IFNULL(CAST(context_id AS STRING),
          '-'),IFNULL(CAST('-' AS STRING),
          '-'),IFNULL(CAST(context_sessionNumber AS STRING),
          '-')) AS session_id
      FROM
        `qubit-client-{{q_view_v01.project._parameter_value}}.{{q_view_v01.site._parameter_value}}__v2.event_ecBasketTransactionSummary`
    )
SELECT
  realtime.view_id as view_id,
  realtime.context_id as context_id,
  transaction.view_transaction_id as view_transaction_id,
  transaction.transaction_id  AS transaction_id,
  transaction.property_event_ts,
  transaction.basket_total_baseValue,
  transaction.basket_quantity,
  row_number() over (partition by realtime.context_id order by transaction.property_event_ts) as visitor_transaction_purchase_cycle,
  count(transaction_id) over (partition by realtime.context_id order by transaction.property_event_ts ROWS BETWEEN 1 FOLLOWING and UNBOUNDED FOLLOWING ) as visitor_subsequent_purchases,
  count(realtime.context_id) over (partition by realtime.context_id order by transaction.property_event_ts ROWS BETWEEN 1 FOLLOWING and UNBOUNDED FOLLOWING ) as visitor_subsequent_purchasers,
  sum(transaction.basket_total_baseValue) over (partition by realtime.context_id order by transaction.property_event_ts ROWS BETWEEN 1 FOLLOWING and UNBOUNDED FOLLOWING ) as visitor_subsequent_purchase_spend,
  count(transaction_id) over (partition by realtime.context_id order by transaction.property_event_ts ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) as visitor_previous_purchases,
  count(realtime.context_id) over (partition by realtime.context_id order by transaction.property_event_ts ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) as visitor_previous_purchasers,
  sum(transaction.basket_total_baseValue) over (partition by realtime.context_id order by transaction.property_event_ts ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING ) as visitor_previous_purchase_spend,
  sum(transaction.basket_total_baseValue) over (partition by realtime.context_id order by transaction.property_event_ts ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING ) / count(transaction_id) over (partition by realtime.context_id order by transaction.property_event_ts ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) as visitor_previous_average_order_value,
  sum(transaction.basket_total_baseValue) over (partition by realtime.context_id order by transaction.property_event_ts ROWS BETWEEN 1 FOLLOWING and UNBOUNDED FOLLOWING ) / count(realtime.context_id) over (partition by realtime.context_id order by transaction.property_event_ts ROWS BETWEEN 1 FOLLOWING and UNBOUNDED FOLLOWING ) as visitor_future_average_order_value,

FROM view AS realtime
LEFT JOIN transaction ON realtime.view_id = transaction.view_id

WHERE transaction_id is not null
GROUP BY 1,2,3,4,5,6,7

ORDER BY 1
 ;;
  }



  dimension: context_id {
    type: string
    hidden: yes
    sql: ${TABLE}.context_id ;;
  }

  dimension: view_id {
    type: string
    primary_key: no
    hidden: yes
    sql: ${TABLE}.view_id ;;
  }

  dimension: transaction_id {
    type: string
    hidden: yes

    sql: ${TABLE}.transaction_id ;;
  }

  dimension: pk {
    type: string
    hidden: no
    primary_key: yes
    sql: concat(concat(${TABLE}.view_id,${TABLE}.context_id),${TABLE}.transaction_id) ;;
  }

  dimension: view_transaction_id {
    type: string
    hidden: yes
    primary_key: no

    sql: ${TABLE}.view_transaction_id ;;
  }

  dimension_group: property_event_ts {
    type: time
    hidden: yes

    sql: ${TABLE}.property_event_ts ;;
  }

  dimension: basket_total_base_value {
    hidden: yes

    type: number
    sql: ${TABLE}.basket_total_baseValue ;;
  }

  dimension: basket_quantity {
    hidden: yes

    type: number
    sql: ${TABLE}.basket_quantity ;;
  }

  dimension: order_value {
   type: number
   hidden: yes
   sql: ${basket_total_base_value} * ${basket_quantity} ;;
  }



  measure: converter_average_transaction_purchase_cycle {
    group_label: "Merchandising Analytics"
    label: "Product Purchaser Average Transaction Cycle"
    type: average_distinct
    sql_distinct_key: ${pk} ;;
    sql: ${TABLE}.visitor_transaction_purchase_cycle ;;
  }

  dimension: converter_transaction_purchase_cycle {
    type: number
    hidden: yes
    sql: ${TABLE}.visitor_transaction_purchase_cycle ;;
  }






  measure: converter_total_subsequent_transaction_value {
    group_label: "Merchandising Analytics"

    value_format_name: gbp
    label: "Converter Total Future Order Value"
    type: sum_distinct
    sql_distinct_key: ${pk} ;;
    sql: ${TABLE}.visitor_subsequent_purchase_spend ;;
  }

  measure: converter_total_previous_transaction_value {
    group_label: "Merchandising Analytics"

    value_format_name: gbp
    label: "Converter Previous Total Order Value"
    type: sum_distinct
    sql_distinct_key: ${pk} ;;
    sql: ${TABLE}.visitor_previous_purchase_spend ;;
  }

  measure: converter_average_number_of_previous_purchases{
    group_label: "Merchandising Analytics"

    label: "Converter Average Previous Orders"
    type: average_distinct
    sql_distinct_key: ${pk} ;;
    sql: ifnull(${TABLE}.visitor_previous_purchases,0) ;;
  }

  measure: count_new_customers{
    group_label: "Merchandising Analytics"

    label: "Count of New Customers"
    type: sum
    sql: case when ifnull(${TABLE}.visitor_previous_purchases,0) = 0 then 1 else 0 end ;;
  }

  measure: count_future_repeat_customers{
    group_label: "Merchandising Analytics"

    label: "Count of Future Repeat Customers"
    type: sum
    sql: case when ifnull(${TABLE}.visitor_subsequent_purchases,0) > 0 then 1 else 0 end ;;
  }

  measure: converter_total_number_of_previous_purchases{
    group_label: "Merchandising Analytics"

    label: "Converter Total Previous Orders"
    type: sum_distinct
    sql_distinct_key: ${pk} ;;
    sql: ifnull(${TABLE}.visitor_previous_purchases,0) ;;
  }




  measure: converter_average_number_of_subsequent_purchases{
    group_label: "Merchandising Analytics"

    label: "Converter Average Repeat Orders"
    type: average_distinct
    sql_distinct_key: ${pk} ;;
    sql: ifnull(${TABLE}.visitor_subsequent_purchases,0) ;;
  }

  measure: converter_total_number_of_subsequent_purchases{
    group_label: "Merchandising Analytics"

    label: "Converter Total Repeat Orders"
    type: sum_distinct
    sql_distinct_key: ${pk} ;;
    sql: ifnull(${TABLE}.visitor_subsequent_purchases,0) ;;
  }

  measure: visitor_future_average_order_value{
    group_label: "Merchandising Analytics"
    hidden: yes
    value_format_name: gbp
    label: "Converter Future AOV"
    type: average_distinct
    sql_distinct_key: ${pk} ;;
    sql: ifnull(${TABLE}.visitor_future_average_order_value,0) ;;
  }

  measure: converter_previous_average_order_value{
    group_label: "Merchandising Analytics"
    hidden: yes

    value_format_name: gbp
    label: "Converter Previous AOV"
    type: average_distinct
    sql_distinct_key: ${pk} ;;
    sql: ifnull(${TABLE}.visitor_previous_average_order_value,0) ;;
  }



  measure: count {
    type: count
  }

##Additional join to be added to Model##

join: transaction_sequence {
    view_label: "Product Interaction and Sales"

    type: inner

    relationship: one_to_one
    sql_on: ${q_transaction_v01.transaction_id} = ${transaction_sequence.transaction_id}
        and ${q_transaction_v01.view_id} = ${transaction_sequence.view_id}
        and ${q_transaction_v01.context_id} = ${transaction_sequence.context_id};;
  }