Contact

view: contact {
  sql_table_name: contact_reporting ;;

  dimension: external_contact_id {
    type: int
    primary_key: yes
    hidden: yes
    sql: ${TABLE}.external_contact_id ;;
  }

  dimension: account_id {
    hidden: yes
    sql: ${TABLE}.account_id ;;
  }

  dimension: march_pricing_group {
    sql: ${TABLE}.pricing_group ;;
  }

  dimension: app_role {
    sql: ${TABLE}.role_text ;;
  }

  dimension: nurturing_availability {
    sql: ${TABLE}.available_for_nurturing_ ;;
  }

  dimension: promo_availablility {
    sql: ${TABLE}.available_for_promos_ ;;
  }

  dimension: business_fax {
    hidden: yes
    sql: ${TABLE}.business_fax ;;
  }

  dimension: lifecycle_stage {
    sql: ${TABLE}.lifecycle_stage ;;
  }

  dimension: phone_number {
    sql: ${TABLE}.business_phone ;;
  }

  dimension: phone_number_validity {
    sql: case when ${TABLE}.business_phone is not null
and (${TABLE}.business_phone ilike '1%' or ${TABLE}.business_phone ilike '0%' or ${TABLE}.business_phone ilike '222%' or ${TABLE}.business_phone ilike '333%'
or ${TABLE}.business_phone ilike '444%'
or ${TABLE}.business_phone ilike '555%'
or ${TABLE}.business_phone ilike '666%'
or ${TABLE}.business_phone ilike '777%'
or ${TABLE}.business_phone ilike '999%'
or ${TABLE}.business_phone ilike '900%') then 'Invalid'
else 'Valid' end ;;
  }

  dimension: company_employer_id {
    hidden: yes
    sql: ${TABLE}.company_employer_id ;;
  }

  dimension: employer_id {
    hidden: yes
    sql: ${TABLE}.employer_id ;;
  }

  dimension: company_account_id {
    hidden: yes
    sql: ${TABLE}.company_account_id ;;
  }

  dimension: admin_link {
    sql: ${TABLE}.company_employer_url ;;
  }

  dimension: product_updates_mailing_list {
    sql: ${TABLE}.company_product_updates::boolean ;;
  }

  dimension: contact_description {
    hidden: yes
    sql: ${TABLE}.contact_description ;;
  }

  dimension: salesforce_contact {
    label: "Link to Salesforce Contact"
    sql: ${TABLE}.contact_id ;;
  }

  dimension: contact_type {
    hidden: yes
    sql: ${TABLE}.contact_type ;;
  }

  dimension: how_did_you_hear_about_us {
    sql: ${TABLE}.how_did_you_hear_about_us ;;
  }

  dimension: onboarding_stage {
    sql: ${TABLE}.onboarding_stage ;;
  }

  dimension: createdby_id {
    hidden: yes
    sql: ${TABLE}.createdby_id ;;
  }

  dimension: call_opt_out {
    type: yesno
    sql: ${TABLE}.donotcall_flag ;;
  }

  dimension: email_address {
    sql: ${TABLE}.email_text ;;
  }

  dimension: email_domain {
    sql: LEFT(LOWER(SUBSTRING(${email_address} FROM POSITION('@' IN ${email_address}) + 1)),
  POSITION('.' IN LOWER(SUBSTRING(${email_address} FROM POSITION('@' IN ${email_address}) + 1))) - 1) ;;
  }

  dimension: email_opt_out {
    type: yesno
    sql: ${TABLE}.emailoptout_flag ;;
  }

  dimension: first_name {
    sql: ${TABLE}.firstname_text ;;
  }

  dimension: first_name_validity {
    hidden: yes
    sql: case when (
    (${TABLE}.firstname_text ~ '.*\d.*')
    or (${TABLE}.firstname_text ilike '% name %'
    or ${TABLE}.firstname_text ilike '% first %'
    or ${TABLE}.firstname_text ilike '% guest %'
    or ${TABLE}.firstname_text ilike '% blah%'
    or ${TABLE}.firstname_text ilike '% demo %'
    or ${TABLE}.firstname_text ilike '%http%'
    or ${TABLE}.firstname_text ilike '%www.%'
    or ${TABLE}.firstname_text ilike '%asdf%'
    or ${TABLE}.firstname_text ilike '%adsf%')
    or (${TABLE}.firstname_text !~* '.*[aeiou]+.*')
    or( ${TABLE}.firstname_text in ('abc','asdf','qwer','sadf','aaa','sss','qqq','xyz','?','!','test','testing','tester','testt','testtt','test test','testtest','testest'))
    or( ${TABLE}.firstname_text in ('a','b','c','d','e','f','g','h','i','j','k','l','m','n','o','p','q','r','s','t','u','v','w','x','y','z','a.','b.','c.','d.','e.','f.','g.','h.','i.','j.','k.','l.','m.','n.','o.','p.','q.','r.','s.','t.','u.','v.','w.','x.','y.','z.'))
  )
  and ${TABLE}.firstname_text not in ('J.T.','JT','JD','J.D.','RJ','R.J.','JR','J.R.','T.J.','TJ','D.J.','DJ','B.J.','BJ','C.J.','CJ','VJ','V.J.','JJ','J.J.','KT','K.T.','PJ','P.J.','MJ','M.J.')
  and ${TABLE}.firstname_text is not null
then 'Invalid name'
else 'valid' end ;;
  }

  dimension: last_name_validity {
    sql: case when (
    (${TABLE}.lastname_text ~ '.*\d.*')
    or (${TABLE}.lastname_text ilike '% name %'
    or ${TABLE}.lastname_text ilike '% first %'
    or ${TABLE}.lastname_text ilike '% guest %'
    or ${TABLE}.lastname_text ilike '% blah%'
    or ${TABLE}.lastname_text ilike '% demo %'
    or ${TABLE}.lastname_text ilike '%http%'
    or ${TABLE}.lastname_text ilike '%www.%'
    or ${TABLE}.lastname_text ilike '%asdf%'
    or ${TABLE}.lastname_text ilike '%adsf%'
    or ${TABLE}.lastname_text ilike '%qwer%')
    or ${TABLE}.lastname_text in ('abc','asdf','qwer','sadf','first','aaa','sss','qqq','xyz','?','!','last')
  )
then 'Invalid name'
else 'valid' end ;;
  }

  dimension: first_touch_attribution {
    hidden: yes
    sql: ${TABLE}.firsttouchattribution_text ;;
  }

  dimension: free_trial_resources {
    type: yesno
    hidden: yes
    sql: ${TABLE}.freetrialresources_flag ;;
  }

  dimension: contact_name {
    sql: ${TABLE}.fullname_text ;;
  }

  dimension: hiring_and_business_resources_mailing_list {
    sql: ${TABLE}.hiring_and_business_resources ;;
  }

  dimension: hiring_tips_mailing_list {
    sql: ${TABLE}.hiring_tips ;;
  }

  dimension: home_phone {
    hidden: yes
    sql: ${TABLE}.home_phone ;;
  }

  dimension: number_of_employees {
    hidden: yes
    sql: ${TABLE}.how_many_employees_ ;;
  }

  dimension: hires_per_year {
    hidden: yes
    sql: ${TABLE}.how_many_hires_per_year_ ;;
  }

  dimension: industry {
    hidden: yes
    sql: ${TABLE}.industry ;;
  }

  dimension: last_activity {
    hidden: yes
    sql: ${TABLE}.last_activity ;;
  }

  dimension: last_lead_source {
    hidden: yes
    sql: ${TABLE}.last_lead_source ;;
  }

  dimension: last_offer_type {
    hidden: yes
    sql: ${TABLE}.last_offer_type ;;
  }

  dimension: last_name {
    sql: ${TABLE}.lastname_text ;;
  }

  dimension: lead_source_notes {
    sql: ${TABLE}.lead_source_notes ;;
  }

  dimension: lead_source {
    description: "lead source of the contact"
    sql: ${TABLE}.leadsource_text ;;
  }

  dimension: lead_source_group {
    sql: case ${TABLE}.leadsource_text when 'Search Engine Marketing' then '1-SEM'
when 'Organic Search' then '2-Organic'
when 'Direct' then '3-Direct'
when 'Sales Generated' then '4-Sales Generated'
else '5-Other' end ;;
  }

  dimension: mobile_phone {
    hidden: yes
    sql: ${TABLE}.mobile_phone ;;
  }

  dimension: original_offer_type {
    hidden: yes
    sql: ${TABLE}.original_offer_type ;;
  }

  dimension: original_referrer {
    sql: ${TABLE}.original_referrer ;;
  }

  dimension: original_search_engine {
    sql: ${TABLE}.original_search_engine ;;
  }

  dimension: original_search_phrase {
    sql: ${TABLE}.original_search_phrase ;;
  }

  dimension: original_source_info {
    sql: ${TABLE}.originalsourceinfo_text ;;
  }

  dimension: original_source_type {
    sql: ${TABLE}.originalsourcetype_text ;;
  }

  dimension: other_email {
    hidden: yes
    sql: ${TABLE}.other_email ;;
  }

  dimension: other_email_addresses {
    hidden: yes
    sql: ${TABLE}.other_email_addresses ;;
  }

  dimension: other_phone {
    hidden: yes
    sql: ${TABLE}.other_phone ;;
  }

  dimension: owner_id {
    hidden: yes
    sql: ${TABLE}.owner_id ;;
  }

  dimension: email_action {
    sql: ${owner.email} ;;
  }

  dimension: ppc_ad {
    sql: ${TABLE}.ppc_ad ;;
  }

  dimension: ppc_ad_group {
    sql: ${TABLE}.ppc_ad_group ;;
  }

  dimension: ppc_keyword {
    sql: ${TABLE}.ppc_keyword ;;
  }

  dimension: ppc_search_engine {
    sql: ${TABLE}.ppc_search_engine ;;
  }

  dimension: ppc_search_string {
    hidden: yes
    sql: ${TABLE}.ppc_search_string ;;
  }

  dimension: referred_by {
    hidden: yes
    sql: ${TABLE}.referred_by ;;
  }

  dimension: registration_hours_tiers {
    type: tier
    sql: ${registration_hour_of_day}::int ;;
  }

  dimension: role {
    sql: ${TABLE}.role ;;
  }

  dimension: external_account_id {
    type: int
    hidden: yes
    sql: ${TABLE}.external_account_id ;;
  }

  dimension: special_offers {
    hidden: yes
    sql: ${TABLE}.special_offers ;;
  }

  dimension: timezone {
    sql: ${TABLE}.timezone_text ;;
  }

  dimension: title {
    sql: ${TABLE}.title ;;
  }

  dimension: twitter {
    hidden: yes
    sql: ${TABLE}.twitter ;;
  }

  dimension: email_signal {
    sql: ${TABLE}.emailbucket_text ;;
  }

  dimension: hdyh_signal {
    sql: ${TABLE}.hdyhaubucket_text ;;
  }

  dimension: qualification_status {
    sql: ${TABLE}.qualification_status ;;
  }

  dimension: buyer_signal {
    sql: CASE
  WHEN ${hdyh_signal} in ('giberish','seeker') then 'Not Buyer'
  WHEN ${email_signal} in ('company','function')
  OR ${hdyh_signal} in ('radio', 'advodate')
  or (${hdyh_signal} = 'wom' and ${email_signal} in ('other','company','function'))
  THEN 'Buyer'
  ELSE 'Not Buyer'
END ;;
  }

  dimension: cardrequestid {
    label: "CardRequestId"
    type: int
    sql: '' ;;
  }

  dimension: RecipientName {
    label: "RecipientName"
    hidden: yes
    sql: cul_initcap(${TABLE}.fullname_text) ;;
  }

  dimension: SenderName {
    label: "SenderName"
    hidden: yes
    sql: '' ;;
  }

  dimension: FontType {
    label: "FontType"
    hidden: yes
    sql: case ${owner} when 'Ciaran' then 'astFrank Regular'
when '<% parameter coach_name1 %>' then 'astTracy Regular'
when '<% parameter coach_name5 %>' then 'astTracy Regular'
else 'astFrank Regular' end ;;
  }

  dimension: requestDate {
    label: "RequestDate"
    type: date
    hidden: yes
    sql: current_date ;;
  }

  dimension: ShipDate {
    label: "ShipDate"
    type: date
    hidden: yes
    sql: current_date ;;
  }

  dimension: RecipientAddress_City {
    label: "RecipientAddress_City"
    hidden: yes
    sql: ${TABLE}.mailing_city ;;
  }

  dimension: RecipientAddress_Country {
    label: "RecipientAddress_Country"
    hidden: yes
    sql: ${TABLE}.mailing_country ;;
  }

  dimension: RecipientAddress_State {
    label: "RecipientAddress_State"
    hidden: yes
    sql: ${TABLE}.mailing_state_province ;;
  }

  dimension: RecipientAddress_AddressLine1 {
    label: "RecipientAddress_AddressLine1"
    hidden: yes
    sql: cul_initcap(${${TABLE}.company_name}) ;;
  }

  dimension: RecipientAddress_AddressLine2 {
    label: "RecipientAddress_AddressLine2"
    hidden: yes
    sql: ${TABLE}.mailing_street ;;
  }

  dimension: RecipientAddress_Zip {
    label: "RecipientAddress_Zip"
    type: string
    hidden: yes
    sql: ${TABLE}.mailing_zip_postal_code ;;
  }

  dimension: SenderAddress_AddressLine1 {
    label: "SenderAddress_AddressLine1"
    hidden: yes
    sql: '' ;;
  }

  dimension: SenderAddress_AddressLine2 {
    label: "SenderAddress_AddressLine2"
    hidden: yes
    sql: '' ;;
  }

  dimension: SenderAddress_City {
    label: "SenderAddress_City"
    hidden: yes
    sql: '' ;;
  }

  dimension: SenderAddress_State {
    label: "SenderAddress_State"
    hidden: yes
    sql: '' ;;
  }

  dimension: SenderAddress_Country {
    label: "SenderAddress_Country"
    hidden: yes
    sql: '' ;;
  }

  dimension: SenderAddress_Zip {
    label: "SenderAddress_Zip"
    hidden: yes
    sql: '' ;;
  }

  dimension: TriggerfoxId {
    label: "TriggerfoxId"
    hidden: yes
    sql: '' ;;
  }

  dimension: contractor_assignment {
    hidden: yes
    sql: case when ${TABLE}.external_contact_id%2 = 1 then 'latif'
else 'caeza'
end ;;
  }

  dimension: inferred_metro {
    sql: ${TABLE}.inferred_metropolitan_area ;;
  }

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

  dimension_group: registration {
    type: time
    timeframes: [time, date, week, month, time_of_day, hour_of_day, day_of_week]
    sql: ${TABLE}.registration_time ;;
  }

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

  measure: count {
    type: count_distinct
    drill_fields: [drill_fields*]
    sql: ${TABLE}.external_contact_id ;;
  }

  measure: share_of_contacts {
    type: percent_of_total
    sql: ${count} ;;
  }

  measure: buyers_contact {
    type: count_distinct
    filters: [buyer_signal: "Buyer"]
    filters: [app_role: "Owner"]
    drill_fields: [drill_fields*]
    sql: ${TABLE}.external_contact_id ;;
  }

  measure: buyers_percent {
    type: number
    value_format: "#.00%"
    sql: ${buyers_contact}::float/ nullif (${count}::float,0) ;;
  }
}

- view: contact
  sql_table_name: contact_reporting
  fields:
  - dimension: external_contact_id
    type: int
    primary_key: true
    hidden: true
    sql: ${TABLE}.external_contact_id
  - dimension: account_id
    hidden: true
    sql: ${TABLE}.account_id
  - dimension: march_pricing_group
    sql: ${TABLE}.pricing_group
  - dimension: app_role
    sql: ${TABLE}.role_text
  - dimension: nurturing_availability
    sql: ${TABLE}.available_for_nurturing_
  - dimension: promo_availablility
    sql: ${TABLE}.available_for_promos_
  - dimension: business_fax
    hidden: true
    sql: ${TABLE}.business_fax
  - dimension: lifecycle_stage
    sql: ${TABLE}.lifecycle_stage
  - dimension: phone_number
    sql: ${TABLE}.business_phone
  - dimension: phone_number_validity
    sql: case when ${TABLE}.business_phone is not null
and (${TABLE}.business_phone ilike '1%' or ${TABLE}.business_phone ilike '0%' or ${TABLE}.business_phone ilike '222%' or ${TABLE}.business_phone ilike '333%'
or ${TABLE}.business_phone ilike '444%'
or ${TABLE}.business_phone ilike '555%'
or ${TABLE}.business_phone ilike '666%'
or ${TABLE}.business_phone ilike '777%'
or ${TABLE}.business_phone ilike '999%'
or ${TABLE}.business_phone ilike '900%') then 'Invalid'
else 'Valid' end
  - dimension: company_employer_id
    hidden: true
    sql: ${TABLE}.company_employer_id
  - dimension: employer_id
    hidden: true
    sql: ${TABLE}.employer_id
  - dimension: company_account_id
    hidden: true
    sql: ${TABLE}.company_account_id
  - dimension: admin_link
    sql: ${TABLE}.company_employer_url
  - dimension: product_updates_mailing_list
    sql: ${TABLE}.company_product_updates::boolean
  - dimension: contact_description
    hidden: true
    sql: ${TABLE}.contact_description
  - dimension: salesforce_contact
    label: Link to Salesforce Contact
    sql: ${TABLE}.contact_id
  - dimension: contact_type
    hidden: true
    sql: ${TABLE}.contact_type
  - dimension: how_did_you_hear_about_us
    sql: ${TABLE}.how_did_you_hear_about_us
  - dimension: onboarding_stage
    sql: ${TABLE}.onboarding_stage
  - dimension: createdby_id
    hidden: true
    sql: ${TABLE}.createdby_id
  - dimension: call_opt_out
    type: yesno
    sql: ${TABLE}.donotcall_flag
  - dimension: email_address
    sql: ${TABLE}.email_text
  - dimension: email_domain
    sql: LEFT(LOWER(SUBSTRING(${email_address} FROM POSITION('@' IN ${email_address}) + 1)),
  POSITION('.' IN LOWER(SUBSTRING(${email_address} FROM POSITION('@' IN ${email_address}) + 1))) - 1)
  - dimension: email_opt_out
    type: yesno
    sql: ${TABLE}.emailoptout_flag
  - dimension: first_name
    sql: ${TABLE}.firstname_text
  - dimension: first_name_validity
    hidden: true
    sql: case when (
    (${TABLE}.firstname_text ~ '.*\d.*')
    or (${TABLE}.firstname_text ilike '% name %'
    or ${TABLE}.firstname_text ilike '% first %'
    or ${TABLE}.firstname_text ilike '% guest %'
    or ${TABLE}.firstname_text ilike '% blah%'
    or ${TABLE}.firstname_text ilike '% demo %'
    or ${TABLE}.firstname_text ilike '%http%'
    or ${TABLE}.firstname_text ilike '%www.%'
    or ${TABLE}.firstname_text ilike '%asdf%'
    or ${TABLE}.firstname_text ilike '%adsf%')
    or (${TABLE}.firstname_text !~* '.*[aeiou]+.*')
    or( ${TABLE}.firstname_text in ('abc','asdf','qwer','sadf','aaa','sss','qqq','xyz','?','!','test','testing','tester','testt','testtt','test test','testtest','testest'))
    or( ${TABLE}.firstname_text in ('a','b','c','d','e','f','g','h','i','j','k','l','m','n','o','p','q','r','s','t','u','v','w','x','y','z','a.','b.','c.','d.','e.','f.','g.','h.','i.','j.','k.','l.','m.','n.','o.','p.','q.','r.','s.','t.','u.','v.','w.','x.','y.','z.'))
  )
  and ${TABLE}.firstname_text not in ('J.T.','JT','JD','J.D.','RJ','R.J.','JR','J.R.','T.J.','TJ','D.J.','DJ','B.J.','BJ','C.J.','CJ','VJ','V.J.','JJ','J.J.','KT','K.T.','PJ','P.J.','MJ','M.J.')
  and ${TABLE}.firstname_text is not null
then 'Invalid name'
else 'valid' end
  - dimension: last_name_validity
    sql: case when (
    (${TABLE}.lastname_text ~ '.*\d.*')
    or (${TABLE}.lastname_text ilike '% name %'
    or ${TABLE}.lastname_text ilike '% first %'
    or ${TABLE}.lastname_text ilike '% guest %'
    or ${TABLE}.lastname_text ilike '% blah%'
    or ${TABLE}.lastname_text ilike '% demo %'
    or ${TABLE}.lastname_text ilike '%http%'
    or ${TABLE}.lastname_text ilike '%www.%'
    or ${TABLE}.lastname_text ilike '%asdf%'
    or ${TABLE}.lastname_text ilike '%adsf%'
    or ${TABLE}.lastname_text ilike '%qwer%')
    or ${TABLE}.lastname_text in ('abc','asdf','qwer','sadf','first','aaa','sss','qqq','xyz','?','!','last')
  )
then 'Invalid name'
else 'valid' end
  - dimension: first_touch_attribution
    hidden: true
    sql: ${TABLE}.firsttouchattribution_text
  - dimension: free_trial_resources
    type: yesno
    hidden: true
    sql: ${TABLE}.freetrialresources_flag
  - dimension: contact_name
    sql: ${TABLE}.fullname_text
  - dimension: hiring_and_business_resources_mailing_list
    sql: ${TABLE}.hiring_and_business_resources
  - dimension: hiring_tips_mailing_list
    sql: ${TABLE}.hiring_tips
  - dimension: home_phone
    hidden: true
    sql: ${TABLE}.home_phone
  - dimension: number_of_employees
    hidden: true
    sql: ${TABLE}.how_many_employees_
  - dimension: hires_per_year
    hidden: true
    sql: ${TABLE}.how_many_hires_per_year_
  - dimension: industry
    hidden: true
    sql: ${TABLE}.industry
  - dimension: last_activity
    hidden: true
    sql: ${TABLE}.last_activity
  - dimension: last_lead_source
    hidden: true
    sql: ${TABLE}.last_lead_source
  - dimension: last_offer_type
    hidden: true
    sql: ${TABLE}.last_offer_type
  - dimension: last_name
    sql: ${TABLE}.lastname_text
  - dimension: lead_source_notes
    sql: ${TABLE}.lead_source_notes
  - dimension: lead_source
    description: lead source of the contact
    sql: ${TABLE}.leadsource_text
  - dimension: lead_source_group
    sql: case ${TABLE}.leadsource_text when 'Search Engine Marketing' then '1-SEM'
when 'Organic Search' then '2-Organic'
when 'Direct' then '3-Direct'
when 'Sales Generated' then '4-Sales Generated'
else '5-Other' end
  - dimension: mobile_phone
    hidden: true
    sql: ${TABLE}.mobile_phone
  - dimension: original_offer_type
    hidden: true
    sql: ${TABLE}.original_offer_type
  - dimension: original_referrer
    sql: ${TABLE}.original_referrer
  - dimension: original_search_engine
    sql: ${TABLE}.original_search_engine
  - dimension: original_search_phrase
    sql: ${TABLE}.original_search_phrase
  - dimension: original_source_info
    sql: ${TABLE}.originalsourceinfo_text
  - dimension: original_source_type
    sql: ${TABLE}.originalsourcetype_text
  - dimension: other_email
    hidden: true
    sql: ${TABLE}.other_email
  - dimension: other_email_addresses
    hidden: true
    sql: ${TABLE}.other_email_addresses
  - dimension: other_phone
    hidden: true
    sql: ${TABLE}.other_phone
  - dimension: owner_id
    hidden: true
    sql: ${TABLE}.owner_id
  - dimension: email_action
    sql: ${owner.email}
  - dimension: ppc_ad
    sql: ${TABLE}.ppc_ad
  - dimension: ppc_ad_group
    sql: ${TABLE}.ppc_ad_group
  - dimension: ppc_keyword
    sql: ${TABLE}.ppc_keyword
  - dimension: ppc_search_engine
    sql: ${TABLE}.ppc_search_engine
  - dimension: ppc_search_string
    hidden: true
    sql: ${TABLE}.ppc_search_string
  - dimension: referred_by
    hidden: true
    sql: ${TABLE}.referred_by
  - dimension: registration_hours_tiers
    type: tier
    sql: ${registration_hour_of_day}::int
  - dimension: role
    sql: ${TABLE}.role
  - dimension: external_account_id
    type: int
    hidden: true
    sql: ${TABLE}.external_account_id
  - dimension: special_offers
    hidden: true
    sql: ${TABLE}.special_offers
  - dimension: timezone
    sql: ${TABLE}.timezone_text
  - dimension: title
    sql: ${TABLE}.title
  - dimension: twitter
    hidden: true
    sql: ${TABLE}.twitter
  - dimension: email_signal
    sql: ${TABLE}.emailbucket_text
  - dimension: hdyh_signal
    sql: ${TABLE}.hdyhaubucket_text
  - dimension: qualification_status
    sql: ${TABLE}.qualification_status
  - dimension: buyer_signal
    sql: CASE
  WHEN ${hdyh_signal} in ('giberish','seeker') then 'Not Buyer'
  WHEN ${email_signal} in ('company','function')
  OR ${hdyh_signal} in ('radio', 'advodate')
  or (${hdyh_signal} = 'wom' and ${email_signal} in ('other','company','function'))
  THEN 'Buyer'
  ELSE 'Not Buyer'
END
  - dimension: cardrequestid
    type: int
    label: CardRequestId
    sql: ''
  - dimension: RecipientName
    hidden: true
    label: RecipientName
    sql: cul_initcap(${TABLE}.fullname_text)
  - dimension: SenderName
    hidden: true
    label: SenderName
    sql: ''
  - dimension: FontType
    hidden: true
    label: FontType
    sql: case ${owner} when 'Ciaran' then 'astFrank Regular'
when '<% parameter coach_name1 %>' then 'astTracy Regular'
when '<% parameter coach_name5 %>' then 'astTracy Regular'
else 'astFrank Regular' end
  - dimension: requestDate
    type: date
    hidden: true
    label: RequestDate
    sql: current_date
  - dimension: ShipDate
    type: date
    hidden: true
    label: ShipDate
    sql: current_date
  - dimension: RecipientAddress_City
    hidden: true
    label: RecipientAddress_City
    sql: ${TABLE}.mailing_city
  - dimension: RecipientAddress_Country
    hidden: true
    label: RecipientAddress_Country
    sql: ${TABLE}.mailing_country
  - dimension: RecipientAddress_State
    hidden: true
    label: RecipientAddress_State
    sql: ${TABLE}.mailing_state_province
  - dimension: RecipientAddress_AddressLine1
    hidden: true
    label: RecipientAddress_AddressLine1
    sql: cul_initcap(${${TABLE}.company_name})
  - dimension: RecipientAddress_AddressLine2
    hidden: true
    label: RecipientAddress_AddressLine2
    sql: ${TABLE}.mailing_street
  - dimension: RecipientAddress_Zip
    type: string
    hidden: true
    label: RecipientAddress_Zip
    sql: ${TABLE}.mailing_zip_postal_code
  - dimension: SenderAddress_AddressLine1
    hidden: true
    label: SenderAddress_AddressLine1
    sql: ''
  - dimension: SenderAddress_AddressLine2
    hidden: true
    label: SenderAddress_AddressLine2
    sql: ''
  - dimension: SenderAddress_City
    hidden: true
    label: SenderAddress_City
    sql: ''
  - dimension: SenderAddress_State
    hidden: true
    label: SenderAddress_State
    sql: ''
  - dimension: SenderAddress_Country
    hidden: true
    label: SenderAddress_Country
    sql: ''
  - dimension: SenderAddress_Zip
    hidden: true
    label: SenderAddress_Zip
    sql: ''
  - dimension: TriggerfoxId
    hidden: true
    label: TriggerfoxId
    sql: ''
  - dimension: contractor_assignment
    hidden: true
    sql: case when ${TABLE}.external_contact_id%2 = 1 then 'latif'
else 'caeza'
end
  - dimension: inferred_metro
    sql: ${TABLE}.inferred_metropolitan_area
  - dimension_group: lastmodified
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.lastmodified_time
  - dimension_group: registration
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    - time_of_day
    - hour_of_day
    - day_of_week
    sql: ${TABLE}.registration_time
  - dimension_group: addresspopulated
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.addresspopulated_time
  - measure: count
    type: count_distinct
    drill_fields:
    - drill_fields*
    sql: ${TABLE}.external_contact_id
  - measure: share_of_contacts
    type: percent_of_total
    sql: ${count}
  - measure: buyers_contact
    type: count_distinct
    drill_fields:
    - drill_fields*
    filters:
    - buyer_signal: Buyer
    - app_role: Owner
    sql: ${TABLE}.external_contact_id
  - measure: buyers_percent
    type: number
    value_format: '#.00%'
    sql: ${buyers_contact}::float/ nullif (${count}::float,0)