Update KWR Filter

Once we have pulled in keyword metric data from SEMRush via update_kwr_semrush we can apply some final filters to the keyword pool. These filters are found in kwr_config and are market specific. The filters used in update_kwr_filter are:

Filter Name	Description
reduction_volume	Removes any keywords which have a low search volume.
relevance_score	Removes any keywords which have a low relevance score. These relevance scores are detailed in update_fabric_kwr_keyword_relevance

As the stored procedure is a fairly simple one, we can see the full code below:

DROP PROCEDURE IF EXISTS sp_update_kwr_filter;

CREATE PROCEDURE sp_update_kwr_filter()

BEGIN

INSERT INTO kwr_keyword (`keyword_market_id`, `status`, `created`, `updated`)
    SELECT 
        a.keyword_market_id, 
        0 as `status`, -- set as 0 (Pending Review) for new keywords but DO NOT update on duplicate as we don't want users having to re-review the same keywords
        NOW() as `created`,
        NOW() as `updated` 
    FROM kwr_semrush_volume a 
    JOIN seogd_market_keyword b on b.id = a.keyword_market_id
    JOIN seogd_market c on c.id = b.market_id
    JOIN kwr_config d on d.market_id = c.id
    LEFT JOIN kwr_keyword_relevance e on e.keyword_market_id = b.id
    WHERE a.search_volume >= d.reduction_volume
    AND e.relevance_score >= d.relevance_score
    AND d.active = 1
    ON DUPLICATE KEY UPDATE `updated`=VALUES(`updated`);

END;