SELECT customers.id, IF(ROUND((DATEDIFF(customers.last_order_date, pc_since)/30), 1) >= 8, 1, 0) AS "active",
IF(customer_addresses.state = "CT" OR customer_addresses.state = "ME" OR customer_addresses.state = "MA" OR customer_addresses.state = "NH" OR customer_addresses.state = "NJ" OR customer_addresses.state = "NY" OR customer_addresses.state = "PA" OR customer_addresses.state = "RI" OR customer_addresses.state = "VT", "northeast",
IF(customer_addresses.state = "ND" OR customer_addresses.state = "SD" OR customer_addresses.state = "NE" OR customer_addresses.state = "KS" OR customer_addresses.state = "MN" OR customer_addresses.state = "IA" OR customer_addresses.state = "MO" OR customer_addresses.state = "WI" OR customer_addresses.state = "IL" OR customer_addresses.state = "IN" OR customer_addresses.state = "MI" OR customer_addresses.state = "OH", "midwest",
IF(customer_addresses.state = "AK" OR customer_addresses.state = "AZ" OR customer_addresses.state = "CA" OR customer_addresses.state = "CO" OR customer_addresses.state = "HI" OR customer_addresses.state = "ID" OR customer_addresses.state = "MT" OR customer_addresses.state = "NV" OR customer_addresses.state = "NM" OR customer_addresses.state = "OR" OR customer_addresses.state = "UT" OR customer_addresses.state = "WA" OR customer_addresses.state = "WY", "west", "south"))) AS "location",
IF(customers.gender >= 0, customers.gender, "") AS "gender",
IF(customers.birthday IS NOT NULL, ROUND(DATEDIFF("2021-10-22", customers.birthday)/365, 0), "") AS "age",
IF(customers.init_promocode IS NULL, "", IF(customers.init_promocode = "FOXNEWS" OR customers.init_promocode = "FOX NEWS" OR customers.init_promocode = "NEWSMAX" OR customers.init_promocode = "NEWS MAX" OR customers.init_promocode = "NEWS" OR customers.init_promocode = "CNN", "tv",
IF(customers.init_promocode = "GORKA" OR customers.init_promocode = "RADIO" OR customers.init_promocode = "MIKEG" OR customers.init_promocode = "MIKE G" OR customers.init_promocode = "MIKE" OR customers.init_promocode = "WOR" OR customers.init_promocode = "IHEART" OR customers.init_promocode = "LAURA" OR customers.init_promocode = "WABC" OR customers.init_promocode = "CHICAGO" OR customers.init_promocode = "PHILLY" OR customers.init_promocode = "KFI" OR customers.init_promocode = "KSFO" OR customers.init_promocode = "SACRAMENTO" OR customers.init_promocode = "SAN DIEGO", "radio",
IF(customers.init_promocode = "PODCAST" OR customers.init_promocode = "FACEBOOK" OR customers.init_promocode = "FACEBOOK35" OR customers.init_promocode = "35FACEBOOK" OR customers.init_promocode = "FACEBOOK20" OR customers.init_promocode = "BING35" OR customers.init_promocode = "YOUTUBE" OR customers.init_promocode = "AZMN35" OR customers.init_promocode = "AMZN", "misc", "other")))) AS "promo code",
customers.number_hc_consults
FROM customers INNER JOIN customer_addresses ON customers.default_address=customer_addresses.id
WHERE pc_since IS NOT NULL AND last_order_date IS NOT NULL AND ROUND((DATEDIFF(last_order_date, pc_since)/30), 1) > 0.5 AND customers.flag_testing <> 1 AND (customers.birthday > "1800-01-01" OR customers.birthday IS NULL)