SELECT
                 anid AS Anfrageid,
                 andatum AS Datum,
                 MONTH(andatum) AS Monat,
                 YEAR(andatum) AS Jahr,
                 anVerkaufsstatus AS VStatus,
                 angeschaeftskundenangebot as GK,
                 anFinanzierungswunsch AS Anfrageart,
                 IF(vaFinanzierungspartner = 'mobility concept',1,0) as Ratenabschluss,
                 IF(sa.Bezeichnung IS NOT NULL,1,0) as Deal,
                 ma.bezeichnung AS Marke,
                 mo.bezeichnung AS Modell,
                 abVAeingegangendatum AS VA_Eingang,
                 abhaendlerbestaetigtdatum AS VA_bestätigt,
                 abprovisionnetto AS VA_Wert,
                 reref AS ref,
                 reref2 AS Ref2,
                 if (anVerkaufsstatus = 'F',1,
  if (anVerkaufsstatus = 'E',1,
  if (anVerkaufsstatus = 'D',1,0))) AS Best_D,        
    IF(LEFT(SUBSTRING_INDEX(reref,'..',-1), 10) = 'adw-search', 'Search',
  IF(LEFT(SUBSTRING_INDEX(reref,'..',-1), 13) = 'google-search', 'Search',
  IF(LEFT(SUBSTRING_INDEX(reref,'..',-1), 3) = 'fb-', 'Facebook',
  IF(LEFT(SUBSTRING_INDEX(reref,'..',-1), 7) = 'adw-con', 'Display',
  IF(LEFT(SUBSTRING_INDEX(reref,'..',-1), 14) = 'google-display', 'Display',
  IF(LEFT(SUBSTRING_INDEX(reref,'..',-1), 3) = 'ysm', 'Bing',
  IF(LEFT(SUBSTRING_INDEX(reref,'..',-1), 6) = 'criteo', 'Criteo',
  IF(LEFT(SUBSTRING_INDEX(reref,'..',-1), 5) = 'affil', 'Affiliate','Rest')))))))) AS Paid_Channel,   
                  IF(LEFT(SUBSTRING_INDEX(reref,'..',-1), 22) = 'adw-search allg-sitzer', 'Search - Sitzer',
 IF(LEFT(SUBSTRING_INDEX(reref,'..',-1), 22) = 'adw-search+allg-sitzer', 'Search - Sitzer',
 IF(LEFT(SUBSTRING_INDEX(reref,'..',-1), 19) = 'adw-search-leasing-', 'Search - Leasing - Marken',
 IF(LEFT(SUBSTRING_INDEX(reref,'..',-1), 15) = 'adw-search-lea-', 'Search - Leasing - Marken',
 IF(LEFT(SUBSTRING_INDEX(reref,'..',-1), 19) = 'adw-search allg-lea', 'Search - Leasing - Allgemein - OCT',
 IF(LEFT(SUBSTRING_INDEX(reref,'..',-1), 19) = 'adw-search+allg-lea', 'Search - Leasing - Allgemein - OCT',
 IF(LEFT(SUBSTRING_INDEX(reref,'..',-1), 19) = 'adw-search-allg-lea', 'Search - Leasing - Allgemein - OCT',
 IF(LEFT(SUBSTRING_INDEX(reref,'..',-1), 16) = 'adw-search-konfi', 'Search - Konfi - Marken',
 IF(LEFT(SUBSTRING_INDEX(reref,'..',-1), 15) = 'adw-search-gek-', 'Search - Gewerbe - Marken',
 IF(LEFT(SUBSTRING_INDEX(reref,'..',-1), 15) = 'adw-search-fin-', 'Search - Finanzierung - Marken',
 IF(LEFT(SUBSTRING_INDEX(reref,'..',-1), 20) = 'adw-search allg-dsa-', 'Search - Filter DSA',
 IF(LEFT(SUBSTRING_INDEX(reref,'..',-1), 20) = 'adw-search+allg-dsa-', 'Search - Filter DSA',
 IF(LEFT(SUBSTRING_INDEX(reref,'..',-1), 23) = 'adw-search-elektro-allg', 'Search - Elektro - Allgemein',
 IF(LEFT(SUBSTRING_INDEX(reref,'..',-1), 18) = 'adw-search-elektro', 'Search - Elektro - Marken',
 IF(LEFT(SUBSTRING_INDEX(reref,'..',-1), 14) = 'adw-search bra', 'Search - Brand',
 IF(LEFT(SUBSTRING_INDEX(reref,'..',-1), 14) = 'adw-search+bra', 'Search - Brand',
 IF(LEFT(SUBSTRING_INDEX(reref,'..',-1), 14) = 'adw-search-bar', 'Search - Autokauf - Marken',
 IF(LEFT(SUBSTRING_INDEX(reref,'..',-1), 20) = 'adw-search allg-1 to', 'Search - Top Keywords - OCT',
 IF(LEFT(SUBSTRING_INDEX(reref,'..',-1), 20) = 'adw-search+allg-1+to', 'Search - Top Keywords - OCT',
 IF(LEFT(SUBSTRING_INDEX(reref,'..',-1), 19) = 'adw-search allg-dsa', 'Search - Allgemein DSA - OCT',
 IF(LEFT(SUBSTRING_INDEX(reref,'..',-1), 19) = 'adw-search+allg-dsa', 'Search - Allgemein DSA - OCT',
 IF(LEFT(SUBSTRING_INDEX(reref,'..',-1), 20) = 'adw-search allg-1 au', 'Search - Auto Vergünstigung - OCT',
 IF(LEFT(SUBSTRING_INDEX(reref,'..',-1), 20) = 'adw-search+allg-1+au', 'Search - Auto Vergünstigung - OCT',
 IF(LEFT(SUBSTRING_INDEX(reref,'..',-1), 18) = 'adw-search allg ne', 'Search - Neuwagen - OCT',
 IF(LEFT(SUBSTRING_INDEX(reref,'..',-1), 18) = 'adw-search+allg+ne', 'Search - Neuwagen - OCT',
 IF(LEFT(SUBSTRING_INDEX(reref,'..',-1), 18) = 'adw-search lea dsa', 'Search - Leasing DSA - OCT',
 IF(LEFT(SUBSTRING_INDEX(reref,'..',-1), 18) = 'adw-search+lea+dsa', 'Search - Leasing DSA - OCT',
 IF(LEFT(SUBSTRING_INDEX(reref,'..',-1), 18) = 'adw-search fin dsa', 'Search - Finanzierung DSA - OCT',
 IF(LEFT(SUBSTRING_INDEX(reref,'..',-1), 18) = 'adw-search+fin+dsa', 'Search - Finanzierung DSA - OCT',
 IF(LEFT(SUBSTRING_INDEX(reref,'..',-1), 15) = 'adw-search-soak', 'Search - Sonderaktionen',
 IF(LEFT(SUBSTRING_INDEX(reref,'..',-1), 15) = 'adw search-soak', 'Search - Sonderaktionen',
 IF(LEFT(SUBSTRING_INDEX(reref,'..',-1), 15) = 'adw-search+soak', 'Search - Sonderaktionen',
  'Rest')))))))))))))))))))))))))))))))) AS Kampagen
 ,if(anfragen.customer_user_agent LIKE '%mobile%', '1', '0') AS mobile
 ,if(anfragen.customer_user_agent NOT LIKE '%mobile%', '1', '0') AS desktop
  
  
 FROM meinauto.anfragen 
                 LEFT JOIN meinauto.modelle mo ON idmodelle= anprodukt2id
                 LEFT JOIN meinauto.marken ma ON idmarken= anprodukt1id
                 LEFT JOIN meinauto.abschluesse ON anid=abanfrageid 
                 LEFT join meinauto.statistik_ref on anid=reanfrageid 
                 LEFT JOIN meinauto.vermittlungsauftraege ON abVermittlungsauftragID = vaid
                 LEFT JOIN meinauto.anfragenkommentare ak ON ak.akAnfrageID = anfragen.anID
                 LEFT JOIN (SELECT modellid,Bezeichnung,Klassifizierung FROM meinauto.sonderaktionen_neu Group by modellid) sa ON anprodukt2id=sa.ModellId 
                 LEFT JOIN (SELECT condition_type,inquiry_id,condition_id FROM meinauto_local.vario) var ON var.inquiry_id=anfragen.anID
                 LEft JOIN (SELECT total_price,rate_base,duration,mileage,id FROM meinauto_local.vario_condition) varcon ON varcon.id = var.condition_id 
          
 
 
 WHERE andatum > DATE_ADD(Now(), INTERVAL - 90 DAY)
 AND anProneuwagen IS NULL and anverkaufsstatus != '0' 
 AND anInteressentID not in (SELECT inid FROM meinauto.interessenten where inemail = 'testkauf@meinauto.de') 
 AND (anquelle IS NULL OR anquelle='') 
 AND anid IN (SELECT reAnfrageID FROM meinauto.statistik_ref WHERE reRef LIKE '%')
 GROUP BY anid;
