Buyer First Action

-- models/buyer_first_action.sql
{{ config(materialized='table') }}

SELECT
  buyer.id AS buyer_id,
  buyer.user_id AS user_id,
  buyer.created_on AS buyer_created_on,
  buyer.is_verified AS is_verified,
  buyer.verified_on AS buyer_verified_on,
  MIN(project.created_on) AS first_post,
  MIN(favorites.created_on) AS first_favorite,
  MIN(ideaboards.created_on) AS first_ideaboard,
  CASE WHEN MIN(project.created_on) THEN project.id END AS first_projectID,
  CASE WHEN MIN(project.created_on) THEN project.job_type END AS job_type,
  CASE WHEN MIN(project.created_on) THEN project.originating_subscriber_id END AS first_project_Maker,
  CASE WHEN MIN(project.created_on) THEN project.source_project_id END AS first_project_listing,
  CASE WHEN MIN(project.created_on) THEN project.status END AS status,
  CASE WHEN MIN(project.created_on) THEN project.postsource_type END AS first_project_postsourcetype,
  CASE WHEN MIN(project.created_on) THEN project.postsource_id END AS first_project_postsource_id

FROM
  tracking_affiliatelead AS project
  JOIN marketplace_partnerlead AS request ON request.id = project.affiliate_body_id
  RIGHT OUTER JOIN member_consumer AS buyer ON buyer.id = request.consumer_id
  LEFT OUTER JOIN favorites_favorite AS favorites ON favorites.user_id = buyer.user_id
  LEFT OUTER JOIN ideabooks_board AS ideaboards ON ideaboards.user_id = buyer.user_id

GROUP BY buyer_id

# models/schema.yml
version: 2

models:
  - name: buyer_first_action
    columns:
      - name: buyer_id
        tests:
          - unique
          - not_null
      - name: employee_id
      - name: first_action
      - name: is_verified
      - name: public_private
      - name: public_inquiry_post_contact_maker
      - name: tags
      - name: source_tag_CTA
      - name: source_tag_mobile_desktop_tablet
      - name: status

Assumptions

• materialized='table' inferred from a LookML persist hint (persist_for / datagroup / sql_trigger_value).
• schema.yml columns come from view dimensions; a primary_key dimension gets unique + not_null tests.

SELECT
  buyer.id AS buyer_id,
  buyer.user_id AS user_id,
  buyer.created_on AS buyer_created_on,
  buyer.is_verified AS is_verified,
  buyer.verified_on AS buyer_verified_on,
  MIN(project.created_on) AS first_post,
  MIN(favorites.created_on) AS first_favorite,
  MIN(ideaboards.created_on) AS first_ideaboard,
  CASE WHEN MIN(project.created_on) THEN project.id END AS first_projectID,
  CASE WHEN MIN(project.created_on) THEN project.job_type END AS job_type,
  CASE WHEN MIN(project.created_on) THEN project.originating_subscriber_id END AS first_project_Maker,
  CASE WHEN MIN(project.created_on) THEN project.source_project_id END AS first_project_listing,
  CASE WHEN MIN(project.created_on) THEN project.status END AS status,
  CASE WHEN MIN(project.created_on) THEN project.postsource_type END AS first_project_postsourcetype,
  CASE WHEN MIN(project.created_on) THEN project.postsource_id END AS first_project_postsource_id

FROM
  tracking_affiliatelead AS project
  JOIN marketplace_partnerlead AS request ON request.id = project.affiliate_body_id
  RIGHT OUTER JOIN member_consumer AS buyer ON buyer.id = request.consumer_id
  LEFT OUTER JOIN favorites_favorite AS favorites ON favorites.user_id = buyer.user_id
  LEFT OUTER JOIN ideabooks_board AS ideaboards ON ideaboards.user_id = buyer.user_id

GROUP BY buyer_id

view: buyer_first_action {
  derived_table: {
    sql: SELECT
  buyer.id AS buyer_id,
  buyer.user_id AS user_id,
  buyer.created_on AS buyer_created_on,
  buyer.is_verified AS is_verified,
  buyer.verified_on AS buyer_verified_on,
  MIN(project.created_on) AS first_post,
  MIN(favorites.created_on) AS first_favorite,
  MIN(ideaboards.created_on) AS first_ideaboard,
  CASE WHEN MIN(project.created_on) THEN project.id END AS first_projectID,
  CASE WHEN MIN(project.created_on) THEN project.job_type END AS job_type,
  CASE WHEN MIN(project.created_on) THEN project.originating_subscriber_id END AS first_project_Maker,
  CASE WHEN MIN(project.created_on) THEN project.source_project_id END AS first_project_listing,
  CASE WHEN MIN(project.created_on) THEN project.status END AS status,
  CASE WHEN MIN(project.created_on) THEN project.postsource_type END AS first_project_postsourcetype,
  CASE WHEN MIN(project.created_on) THEN project.postsource_id END AS first_project_postsource_id

FROM
  tracking_affiliatelead AS project
  JOIN marketplace_partnerlead AS request ON request.id = project.affiliate_body_id
  RIGHT OUTER JOIN member_consumer AS buyer ON buyer.id = request.consumer_id
  LEFT OUTER JOIN favorites_favorite AS favorites ON favorites.user_id = buyer.user_id
  LEFT OUTER JOIN ideabooks_board AS ideaboards ON ideaboards.user_id = buyer.user_id

GROUP BY buyer_id ;;
    persist_for: "60 minutes"
  }

  dimension: buyer_id {
    type: int
    primary_key: yes
    hidden: yes
  }

  dimension: employee_id {
    type: yesno
    hidden: yes
    sql: ${TABLE}.is_employee_id ;;
  }

  dimension: first_action {
    type: string
    sql: CASE
  WHEN ((${TABLE}.first_post IS NOT NULL AND ${TABLE}.first_favorite IS NOT NULL AND ${TABLE}.first_ideaboard IS NOT NULL) AND (TIMEDIFF(${TABLE}.first_post,${TABLE}.first_favorite) < 0) OR TIMEDIFF(${TABLE}.first_post,${TABLE}.first_ideaboard < 0)) THEN 'Post'
  WHEN (TIMEDIFF(${TABLE}.first_post,${TABLE}.first_favorite) < 0 OR TIMEDIFF(${TABLE}.first_post,${TABLE}.first_ideaboard < 0)) THEN 'Post'
  WHEN (${TABLE}.first_post IS NOT NULL AND (${TABLE}.first_favorite IS NULL AND ${TABLE}.first_ideaboard IS NULL)) THEN 'Post'
  WHEN ((${TABLE}.first_favorite IS NOT NULL AND ${TABLE}.first_post IS NOT NULL AND ${TABLE}.first_ideaboard IS NOT NULL) AND (TIMEDIFF(${TABLE}.first_favorite,${TABLE}.first_post) < 0) OR TIMEDIFF(${TABLE}.first_post,${TABLE}.first_ideaboard < 0)) THEN 'Favorite'
  WHEN (TIMEDIFF(${TABLE}.first_favorite,${TABLE}.first_post) < 0 OR TIMEDIFF(${TABLE}.first_favorite,${TABLE}.first_ideaboard < 0)) THEN 'Favorite'
  WHEN (${TABLE}.first_favorite IS NOT NULL AND ${TABLE}.first_post IS NULL AND ${TABLE}.first_ideaboard IS NULL) THEN 'Favorite'
  WHEN ((${TABLE}.first_ideaboard IS NOT NULL AND ${TABLE}.first_post IS NOT NULL AND ${TABLE}.first_favorite IS NOT NULL) AND (TIMEDIFF(${TABLE}.first_ideaboard,${TABLE}.first_post) < 0) OR TIMEDIFF(${TABLE}.first_ideaboard,${TABLE}.first_favorite < 0)) THEN 'Ideaboard'
  WHEN (TIMEDIFF(${TABLE}.first_ideaboard,${TABLE}.first_post) < 0 OR TIMEDIFF(${TABLE}.first_ideaboard,${TABLE}.first_favorite < 0)) THEN 'Ideaboard'
  WHEN (${TABLE}.first_ideaboard IS NOT NULL AND ${TABLE}.first_post IS NULL AND ${TABLE}.first_favorite IS NULL) THEN 'Ideaboard'
  ELSE 'None'
END ;;
  }

  dimension: is_verified {
    type: yesno
    sql: ${TABLE}.is_verified ;;
  }

  dimension: public_private {
  }

  dimension: public_inquiry_post_contact_maker {
  }

  dimension: tags {
    sql: (SELECT
GROUP_CONCAT(DISTINCT tag.name ORDER BY tag.name  SEPARATOR ', ' )
FROM taggit_taggeditem t
LEFT JOIN taggit_tag tag on tag.id = t.tag_id
WHERE t.object_id = ${TABLE}.first_projectid) ;;
  }

  dimension: source_tag_CTA {
    sql: CASE
  WHEN ${tags} LIKE '%inquiry_post%' THEN 'inquiry_post'
  WHEN ${tags} LIKE '%contact-me%' THEN 'contact-me'
  WHEN ${tags} LIKE '%homepage-form%' OR ${tags} LIKE '%gallery-form%' OR ${tags} LIKE '%show%' THEN 'showntell'
  WHEN ${tags} LIKE '%backbone_post%' OR ${tags} LIKE '%listing-get-started%' THEN 'get-started'
  ELSE 'BLANK'
END ;;
  }

  dimension: source_tag_mobile_desktop_tablet {
    sql: CASE
  WHEN ${tags} LIKE '%mobile%' THEN 'Mobile'
  WHEN ${tags} LIKE '%tablet%' THEN 'Tablet'
  ELSE 'Desktop'
END ;;
  }

  dimension: status {
  }

  dimension_group: created {
    type: time
    timeframes: [time, hour, date, week, month]
    sql: ${TABLE}.buyer_created_on ;;
  }

  dimension_group: first_favorite {
    type: time
    timeframes: [time, date, week, month]
    sql: ${TABLE}.first_favorite ;;
  }

  dimension_group: first_ideaboard {
    type: time
    timeframes: [time, date, week, month]
    sql: ${TABLE}.first_ideaboard ;;
  }

  dimension_group: first_post {
    type: time
    timeframes: [time, date, week, month]
    sql: ${TABLE}.first_post ;;
  }

  measure: count {
    type: count
  }

  measure: count_verified {
    type: count
    filters: [is_verified: "True"]
  }

  measure: count_percent_of_total {
    label: "Count (Percent of Total)"
    type: percent_of_total
    sql: ${count} ;;
  }

  measure: verification_rate {
    type: number
    sql: 100.00 * ${count_verified}/${count} ;;
  }
}

- view: buyer_first_action
  derived_table:
    sql: SELECT
  buyer.id AS buyer_id,
  buyer.user_id AS user_id,
  buyer.created_on AS buyer_created_on,
  buyer.is_verified AS is_verified,
  buyer.verified_on AS buyer_verified_on,
  MIN(project.created_on) AS first_post,
  MIN(favorites.created_on) AS first_favorite,
  MIN(ideaboards.created_on) AS first_ideaboard,
  CASE WHEN MIN(project.created_on) THEN project.id END AS first_projectID,
  CASE WHEN MIN(project.created_on) THEN project.job_type END AS job_type,
  CASE WHEN MIN(project.created_on) THEN project.originating_subscriber_id END AS first_project_Maker,
  CASE WHEN MIN(project.created_on) THEN project.source_project_id END AS first_project_listing,
  CASE WHEN MIN(project.created_on) THEN project.status END AS status,
  CASE WHEN MIN(project.created_on) THEN project.postsource_type END AS first_project_postsourcetype,
  CASE WHEN MIN(project.created_on) THEN project.postsource_id END AS first_project_postsource_id

FROM
  tracking_affiliatelead AS project
  JOIN marketplace_partnerlead AS request ON request.id = project.affiliate_body_id
  RIGHT OUTER JOIN member_consumer AS buyer ON buyer.id = request.consumer_id
  LEFT OUTER JOIN favorites_favorite AS favorites ON favorites.user_id = buyer.user_id
  LEFT OUTER JOIN ideabooks_board AS ideaboards ON ideaboards.user_id = buyer.user_id

GROUP BY buyer_id
    persist_for: 60 minutes
  fields:
  - dimension: buyer_id
    type: int
    primary_key: true
    hidden: true
  - dimension: employee_id
    type: yesno
    hidden: true
    sql: ${TABLE}.is_employee_id
  - dimension: first_action
    type: string
    sql: CASE
  WHEN ((${TABLE}.first_post IS NOT NULL AND ${TABLE}.first_favorite IS NOT NULL AND ${TABLE}.first_ideaboard IS NOT NULL) AND (TIMEDIFF(${TABLE}.first_post,${TABLE}.first_favorite) < 0) OR TIMEDIFF(${TABLE}.first_post,${TABLE}.first_ideaboard < 0)) THEN 'Post'
  WHEN (TIMEDIFF(${TABLE}.first_post,${TABLE}.first_favorite) < 0 OR TIMEDIFF(${TABLE}.first_post,${TABLE}.first_ideaboard < 0)) THEN 'Post'
  WHEN (${TABLE}.first_post IS NOT NULL AND (${TABLE}.first_favorite IS NULL AND ${TABLE}.first_ideaboard IS NULL)) THEN 'Post'
  WHEN ((${TABLE}.first_favorite IS NOT NULL AND ${TABLE}.first_post IS NOT NULL AND ${TABLE}.first_ideaboard IS NOT NULL) AND (TIMEDIFF(${TABLE}.first_favorite,${TABLE}.first_post) < 0) OR TIMEDIFF(${TABLE}.first_post,${TABLE}.first_ideaboard < 0)) THEN 'Favorite'
  WHEN (TIMEDIFF(${TABLE}.first_favorite,${TABLE}.first_post) < 0 OR TIMEDIFF(${TABLE}.first_favorite,${TABLE}.first_ideaboard < 0)) THEN 'Favorite'
  WHEN (${TABLE}.first_favorite IS NOT NULL AND ${TABLE}.first_post IS NULL AND ${TABLE}.first_ideaboard IS NULL) THEN 'Favorite'
  WHEN ((${TABLE}.first_ideaboard IS NOT NULL AND ${TABLE}.first_post IS NOT NULL AND ${TABLE}.first_favorite IS NOT NULL) AND (TIMEDIFF(${TABLE}.first_ideaboard,${TABLE}.first_post) < 0) OR TIMEDIFF(${TABLE}.first_ideaboard,${TABLE}.first_favorite < 0)) THEN 'Ideaboard'
  WHEN (TIMEDIFF(${TABLE}.first_ideaboard,${TABLE}.first_post) < 0 OR TIMEDIFF(${TABLE}.first_ideaboard,${TABLE}.first_favorite < 0)) THEN 'Ideaboard'
  WHEN (${TABLE}.first_ideaboard IS NOT NULL AND ${TABLE}.first_post IS NULL AND ${TABLE}.first_favorite IS NULL) THEN 'Ideaboard'
  ELSE 'None'
END
  - dimension: is_verified
    type: yesno
    sql: ${TABLE}.is_verified
  - dimension: public_private
  - dimension: public_inquiry_post_contact_maker
  - dimension: tags
    sql: (SELECT
GROUP_CONCAT(DISTINCT tag.name ORDER BY tag.name  SEPARATOR ', ' )
FROM taggit_taggeditem t
LEFT JOIN taggit_tag tag on tag.id = t.tag_id
WHERE t.object_id = ${TABLE}.first_projectid)
  - dimension: source_tag_CTA
    sql: CASE
  WHEN ${tags} LIKE '%inquiry_post%' THEN 'inquiry_post'
  WHEN ${tags} LIKE '%contact-me%' THEN 'contact-me'
  WHEN ${tags} LIKE '%homepage-form%' OR ${tags} LIKE '%gallery-form%' OR ${tags} LIKE '%show%' THEN 'showntell'
  WHEN ${tags} LIKE '%backbone_post%' OR ${tags} LIKE '%listing-get-started%' THEN 'get-started'
  ELSE 'BLANK'
END
  - dimension: source_tag_mobile_desktop_tablet
    sql: CASE
  WHEN ${tags} LIKE '%mobile%' THEN 'Mobile'
  WHEN ${tags} LIKE '%tablet%' THEN 'Tablet'
  ELSE 'Desktop'
END
  - dimension: status
  - dimension_group: created
    type: time
    timeframes:
    - time
    - hour
    - date
    - week
    - month
    sql: ${TABLE}.buyer_created_on
  - dimension_group: first_favorite
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.first_favorite
  - dimension_group: first_ideaboard
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.first_ideaboard
  - dimension_group: first_post
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.first_post
  - measure: count
    type: count
  - measure: count_verified
    type: count
    filters:
    - is_verified: True
  - measure: count_percent_of_total
    type: percent_of_total
    label: Count (Percent of Total)
    sql: ${count}
  - measure: verification_rate
    type: number
    sql: 100.00 * ${count_verified}/${count}