How to avoid lengthy CASE statement?
You can still make it data driven - you just need more data!

First some data setup:

drop table if exists #Lookup;
drop table if exists #AdCampaign;

with LookupData as
(
    select * from
    (
        values
        ('instagram', 'InstagramCampaign', NULL, NULL),
        ('instagram', NULL, NULL, '%foobar%'),
        ('google', NULL, NULL, '%foobar%'),
        ('bing', NULL, '%abc123%', '%foobar%')
    ) as T(Provider, CampaignEqual, CampaignLike, KeywordLike)
)
select * into #Lookup from LookupData;


with CampaignData as
(
    select * from
    (
        values
        (1, 'Instagram', 'InstagramCampaign', NULL),
        (2, 'bing', 'Contains abc123 internally', 'bazfoobarbix')
    ) as T(ID, Provider, Campaign, Keyword)
)select * Into #AdCampaign from CampaignData;


There's no magic in there. It's all just a convenient (for me) way to populate some tables without worrying about datatypes, constraints, keys and whatnot all. The pertinent bit is the columns in #Lookup. I've inferred that Provider is mandatory. Campaign is split in two depending on how the values are to be compared. I'll get to that in a minute.

I use NULL in #Lookup to represent "not applicable" or "not a part of this comparison".

And here's the query that does the matching:

select
   a.*,
   '|',           -- just a visual separator for SSMS
   l.*
from #AdCampaign as a
inner join #Lookup as l
on a.Provider = l.Provider      -- I'm taking this as mandatory.
and (l.CampaignEqual is NULL or l.CampaignEqual = a.Campaign)
and (l.CampaignLike is NULL or a.Campaign like l.CampaignLike)
and (l.KeywordLike is NULL or a.Keyword like l.KeywordLike);


The idea is to have one column in #Lookup per value-operator pair. These then translate quite directly into the WHERE clause of the actual update. So Lookup.CampaignEqual compares to AdCampaign.Campaign using and "equals" operator. That's why Lookup contains both CampaignEqual and CampaignLike - so it's obvious what to compare to and how. This design can be extended indefinitely with columns such as ValueEqual, ValueLessThan, ValueStartsWith etc. 

You could have fewer columns in #Lookup at the cost of more involved predicates. For example if we have just Lookup.Campaign then the predicate becomes

.. and (l.Campaign is NULL
     or a.Campaign = l.Campaign
     or a.Campaign like l.Campaign
     ... etc.
   )


The meaning's the same. I think the performance will be the same. It's a matter of style and what you're comfortable with maintaining.

It may be a little easier to get the lookup data correct with just a single column. Putting a wildcard in a ..Equal column or missing them from a ..Like would cause the matching to break.

OK, so more data and a somewhat lengthy where clause.