Join tables based on variable value contained in column
With the way you've written that join condition, SQL server will search for exactly "table1.audience", not the column value. Column names within single quotes are just treated as string literals, not the underlying column value.

Try this instead:

SELECT * 
FROM table1 
LEFT JOIN table2 
ON ((table2.Channel like '%' + table1.audience + '%') 
AND (table2.date = table1.date))


This will concatenate the value contained within table1.audience and wrap it in wildcards. 

I'd recommend dropping the leading wildcard if it's not necessary, as this will significantly hinder performance if you're dealing with a large data set (as you will force a scan).