Untitled

mail@pastecode.io avatar
unknown
plain_text
5 months ago
6.8 kB
7
Indexable
(CASE
 -- Blank Media Channel ----------------------------------------------------------------------------------------------------
 WHEN `Response Media Channel` = 'None' OR `Awareness Media Channel` = 'None' THEN 
 (CASE
  WHEN `Response Media Channel` = 'None' AND `Awareness Media Channel` = 'None' THEN 'Response and Awareness Blank Media Channel'
  
  -- Response
  WHEN `Response Media Channel` = 'None' AND `Awareness Count` = 0 THEN 'Response Blank Media Channel w/ No Awareness'
  WHEN `Response Media Channel` = 'None' AND `Awareness Count` = 1 AND `Awareness STA` = 'true' THEN 'Response Blank Media Channel w/ Awareness STA'
  WHEN `Response Media Channel` = 'None' AND `Awareness Count` = 1 AND `Awareness STA` = 'false' THEN 'Response Blank Media Channel w/ Awareness'
  
  -- Awareness
  WHEN `Awareness Media Channel` = 'None' AND `Response Count` = 0 THEN 'Awareness Blank Media Channel w/ No Response'
  WHEN `Awareness Media Channel` = 'None' AND `Response Count` = 1 AND `Response STA` = 'true' THEN 'Awareness Blank Media Channel w/ Response STA'
  WHEN `Awareness Media Channel` = 'None' AND `Response Count` = 1 AND `Response STA` = 'false' THEN 'Awareness Blank Media Channel w/ Response'
  END)
 
 -- No Response or Awareness ----------------------------------------------------------------------------------------------------
 WHEN `Response Count` = 0 OR `Awareness Count` = 0 THEN
 (CASE
  WHEN `Response Count` = 0 AND `Awareness Count` = 0 THEN 'No Response or Awareness'
  WHEN `Response Count` = 1 AND `Response STA` = 'true' THEN 'Response STA w/ No Awareness'
  WHEN `Response Count` = 1 AND `Response STA` = 'false' THEN 'Response w/ No Awareness'
  WHEN `Awareness Count` = 1 AND `Awareness STA` = 'true' THEN 'Awareness STA w/ No Response'
  WHEN `Awareness Count` = 1 AND `Awareness STA` = 'false' THEN 'Awareness w/ No Response'
  END)
 ELSE
 
 -- Has Response and Awareness ----------------------------------------------------------------------------------------------------
 (CASE
  -- No STA and Double STA
  WHEN `Response STA` = 'false' AND `Awareness STA` = 'false' THEN 'Response and Awareness No STA'
  WHEN `Response STA` = 'true' AND `Awareness STA` = 'true' THEN 'Response and Awareness Double STA'
  ELSE
  
  -- One Has STA
  (CASE
   -- Same Media Channel
   WHEN (`Response Media Channel` = `Awareness Media Channel` AND `Response STA` = 'true') OR
   
   -- Client Communications
   (`Response Media Channel` = 'Client Communications' AND `Response STA` = 'true') OR
   
   -- Direct Mail
   (`Response Media Channel` = 'Direct_Mail' AND `Response STA` = 'true' AND `Awareness Media Channel` != 'Referrals') OR
   (`Response Media Channel` = 'Direct_Mail' AND `Awareness STA` = 'true' AND `Awareness Media Channel` = 'Referrals') OR
   
   -- Email
   (`Response Media Channel` = 'Email' AND `Response STA` = 'true' AND (`Awareness Media Channel` != 'Client Communications' OR `Awareness Media Channel` != 'Event' OR `Awareness Media Channel` != 'PR' OR `Awareness Media Channel` != 'Referrals' OR `Awareness Media Channel` != 'Social Media' OR `Awareness Media Channel` != 'TV')) OR
   (`Response Media Channel` = 'Email' AND `Awareness STA` = 'true' AND (`Awareness Media Channel` = 'Client Communications' OR `Awareness Media Channel` = 'Event' OR `Awareness Media Channel` = 'PR' OR `Awareness Media Channel` = 'Referrals' OR `Awareness Media Channel` = 'Social Media' OR `Awareness Media Channel` = 'TV')) OR
   
   -- Event
   (`Response Media Channel` = 'Event' AND `Response STA` = 'true' AND (`Awareness Media Channel` != 'Client Communications' OR `Awareness Media Channel` != 'Direct_Mail' OR `Awareness Media Channel` != 'Referrals' OR `Awareness Media Channel` != 'TV')) OR
   (`Response Media Channel` = 'Event' AND `Awareness STA` = 'true' AND (`Awareness Media Channel` = 'Client Communications' OR `Awareness Media Channel` = 'Direct_Mail' OR `Awareness Media Channel` = 'Referrals' OR `Awareness Media Channel` = 'TV')) OR
   
   -- Online
   (`Response Media Channel` = 'Online' AND `Response STA` = 'true' AND (`Awareness Media Channel` != 'Client Communications' OR `Awareness Media Channel` != 'Direct_Mail' OR `Awareness Media Channel` != 'Event' OR `Awareness Media Channel` != 'PR' OR `Awareness Media Channel` != 'Referrals' OR `Awareness Media Channel` != 'Social Media' OR `Awareness Media Channel` != 'TV')) OR
   (`Response Media Channel` = 'Online' AND `Awareness STA` = 'true' AND (`Awareness Media Channel` = 'Client Communications' OR `Awareness Media Channel` = 'Direct_Mail' OR `Awareness Media Channel` = 'Event' OR `Awareness Media Channel` = 'PR' OR `Awareness Media Channel` = 'Referrals' OR `Awareness Media Channel` = 'Social Media' OR `Awareness Media Channel` = 'TV')) OR
   
   -- PR
   (`Response Media Channel` = 'PR' AND `Response STA` = 'true' AND (`Awareness Media Channel` != 'Client Communications' OR `Awareness Media Channel` != 'Event' OR `Awareness Media Channel` != 'Radio' OR `Awareness Media Channel` != 'Referrals' OR `Awareness Media Channel` != 'TV')) OR
   (`Response Media Channel` = 'PR' AND `Awareness STA` = 'true' AND (`Awareness Media Channel` = 'Client Communications' OR `Awareness Media Channel` = 'Event' OR `Awareness Media Channel` = 'Radio' OR `Awareness Media Channel` = 'Referrals' OR `Awareness Media Channel` = 'TV')) OR
   
   -- Radio
   (`Response Media Channel` = 'Radio' AND `Response STA` = 'true' AND (`Awareness Media Channel` != 'Event' OR `Awareness Media Channel` != 'Referrals')) OR
   (`Response Media Channel` = 'Radio' AND `Awareness STA` = 'true' AND (`Awareness Media Channel` = 'Event' OR `Awareness Media Channel` = 'Referrals')) OR
   
   -- Referrals
   (`Response Media Channel` = 'Referrals' AND `Response STA` = 'true') OR
   
   -- Social Media
   (`Response Media Channel` = 'Social Media' AND `Response STA` = 'true' AND (`Awareness Media Channel` != 'Client Communications' OR `Awareness Media Channel` != 'Event' OR `Awareness Media Channel` != 'Referrals' OR `Awareness Media Channel` != 'TV')) OR
   (`Response Media Channel` = 'Social Media' AND `Awareness STA` = 'true' AND (`Awareness Media Channel` = 'Client Communications' OR `Awareness Media Channel` = 'Event' OR `Awareness Media Channel` = 'Referrals' OR `Awareness Media Channel` = 'TV')) OR
   
   -- TV
   (`Response Media Channel` = 'TV' AND `Response STA` = 'true' AND `Awareness Media Channel` != 'Referrals') OR
   (`Response Media Channel` = 'TV' AND `Awareness STA` = 'true' AND `Awareness Media Channel` = 'Referrals') THEN 'Response and Awareness: Correct STA'
   
   ELSE 'Response and Awareness: Incorrect STA'
   END)
  END)
 END)
Leave a Comment