Scripts to process Reach Exports

Match_Reach_to_VF.sql

-- put "new" data with the rest of the reach data 
select
    matches.IDNumber,
    reach_data.ReachID,
    reach_data.AddedTimestamp,
    reach_data.LastReachedTimestamp,
    reach_data.LastReachedbyUserName,
    reach_data.AutoAppliedTag,
    reach_data.CampaignTags,
    reach_data.FirstName,
    reach_data.LastName,
    reach_data.Phone,
    reach_data.Email,
    reach_data.AddressLine1,
    reach_data.AddressLine2,
    reach_data.City,
    reach_data.State,
    reach_data.ZipCode,
    reach_data.SupportScore,
    reach_data.PetitionSigner,
    reach_data.YardSignRequest,
    reach_data.Error,
    reach_data.PageNumber

-- INTO OUTFILE '/tmp/reach_matched.csv' 
-- FIELDS TERMINATED BY ',' 
-- ENCLOSED BY '"' 
-- ESCAPED BY '\\' 
-- LINES TERMINATED BY '\n' 

from 
(
    select 
        -- variants: reach, number of matches, matches 
        -- reach.ReachID, reach.FirstName, reach.LastName, voter.IDNumber 
        -- count(distinct reach.ReachID, voter.IDNumber) as distinct_matches 
        distinct reach.ReachID, voter.IDNumber 
    
    -- get the reach record
    from
        (
            select 
                ReachID, FirstName, LastName, 
                AddressLine1, City, State, ZipCode 
            from reach_people_export
        ) as reach 
    -- get the voter file record
    join 
        (
            select
                IDNumber, Last, First, Middle,
                HouseNumber, Street, ApartmentNumber, Address2,
                City, State, Zip
            from afve_ppl_ind
        ) as voter
            on
                -- match up the name and the street name, like reach does.
                -- voter.Zip = reach.ZipCode and
                voter.First = reach.FirstName and
                voter.Last = reach.LastName and
                reach.AddressLine1 like concat('%', voter.Street, '%')
) as matches 
-- put it back together with the rest of the reach data 
join reach_people_export as reach_data on reach_data.ReachID = matches.ReachID;