You can achieve your desired output by joining each table separately and then applying union on those results.You can try the below query :

Query:

create temp table main as (
 select 1 ranking, 'apple' keyword, 500 total_purchase union all
 select 2,'banana', 477  union all
 select 3, 'milk', 456
);
 
create temp table t1 as (
 select 1 ranking, 'apple' keyword, 55 purchase, 1 store union all
 select 2,'beer', 42 ,1  union all
 select 3, 'chips', 33 ,1
);
 
create temp table t2 as (
 select 1 ranking, 'apple' keyword, 51 purchase, 2 store union all
 select 2,'banana', 43 ,2  union all
 select 3, 'bread', 34 ,2
);
 
select m.*,t.purchase,first_value(Store ignore nulls) over
( order by m.ranking) store from main m
left join t1 t on m.keyword = t.keyword
union all
select m.*,t.purchase,first_value(Store ignore nulls) over( order by 
m.ranking) store from main m
left join t2 t on m.keyword = t.keyword

Output: