Database cleanup

From Korean Resource Center

Jump to: navigation, search

typed up in 2006 for KRC fundraiser mailing

5248103598_bea10f131a.jpg
magnify-clip.png
Sample shot of orderly process (from 2010 membership mailer)

KRC 12/8 Fundraiser Mass Mailing

Mailing Address Processing Workplan

  1. Receive
  2. Categorize
    1. Tag Source (PartD/Social/Natz/Voter/HealthDay/Computer/English/ORAnGE/HDVolunteer)
    2. No Addess, Yes Email
    3. No Address, No Email, Yes Fax
    4. No Address/Email/Fax, Yes Phone
  3. Standardize
    1. Source / Client ID / Case ID / Type / Gender / KorName / First / MI / Last / Street / City / State / ZIP / Phone1 / Phone2 / Fax / Email / DOB / CaseDate / CaseStatus
    2. Separate Name, Address
  4. Format
    1. Replace " " for "\t"
    2. Separate Street Field into: Number / Direction / Street / Type / Apt
  5. Romanization (~300)
    1. Use http://mccune-reischauer.org/downloads.html#1 (via http://en.wikipedia.org/wiki/Wikipedia:Korea-related_topics_notice_board/Programs )
    2. Replace
      1. Gim => Kim
      2. Sin => Shin
      3. O => Oh
      4. Ju => Joo
      5. Bak => Park
      6. I => Lee
      7. Im => Lim
      8. Jang => Chang
      9. Go => Ko
  6. Tidy up style
    1. UPPERCASE => Title Case
      Tip: need to do lowercase first and then Title Case
    2. City Names (LA => Los Angeles)
    3. Abbreviations (Street/Avenue/Place/Boulevard/Road/Lane => St/Ave/Bl/Pl/Rd/Ln)
  7. Remove duplicates (RE http://www.cpearson.com/excel/duplicat.htm )
    1. Replace Duplicates (RE Replacing Duplicate Entries)
    2. Estimates:
      1. Address Method: 6038->4308 (26 no Addr)
      2. Phone Method: 4158->2583 (1906 no Phone1)
  8. Remove Replcaments
    1. First run: 6066->4408 (1658 duplicates)
      Criteria: Same Street Address
    2. Second run: 4408-> 3981 (424 duplicates)
      Criteria: Same Phone1
      Memo:주소가 다름에도 같은 전화번호가 있는 경우 몇 건 있었음
  9. Chop
    1. No ZIP Codes (35 Core,
  10. Prioritize
    1. Core: Naturalization, Medicare Part D, LITC, ESL, Computer, HFP, Health Day, Supporters, ORAnGE, HAP
    2. NonCore: Elections, Social Service,
      1. RemDupli: Same Phone1, Phone2 (1542->1420)
    3. Cutoff: 10/21/05
  11. Last-Name Method Cleanup
    1. Ethnic Korean Last Names
      1. 164 last names from VAN (from 56,595 Korean Surname Voters in LAC and OC)
        Ahn Ain Ana Anh Ara Ason Au Baca Back Bae Baek Bahg Baik Bak Bake Ban Bang Boh Bok Boo Byon Byun Chae Cheon Cho Choe Choi Chon Chong Choo Chou Chua Chun Chung Dim Dom Doo Gam Gim Hah Hahm Han Hoh Hong Hoon Huch Huh Huk Hur Hwang Hyon Hyun Im In Ja Jae Jahng Jang Jeon Jeong Jeung Jo Joe Joh Jon Joo Joung Jun Jung Kae Kahng Kang Kap Kil Kim Kime Kimm Kook Koun Kown Kuk Kwak Kwon Kye Kyong Kyung Lee Li Lim Lym Lyu Magee Min Miu Moon Mun Myung Nam No Noh Oak Oh Pae Paek Paik Pak Para Park Pol-Lim Pong Pyo Pyon Pyun Qua Ra Ree Rhee Rheem Rhim Rho Rim Ro Roh Ryon Ryu Seo Shim Shon Sik Sim Sohn Sook Sookhoo Suh Suk Sul Sun Sup Ur Wan Ho Whang Woon Wun Yang Yim Yoo Yoon Youn Yung
      2. 299 last names from CMS (extracted & cleaned up from 1369 non-VAN last names)
        /Dong H. Park /Hong J. Baek /Hye S. Ahn /Jung N. Yoo /Kyu Hwan Yeon Abad An Asfada Azurdio Baeg Beak Carter Cha Chang Chi Choi Cha Choung Chu Cui Dickerson Doh Edward Garcia Gedle Gil Gilbert Goo Ha Hee Heo Howery Hu Hyeon Ito Jin K Katsuda Khunngam Kim+1 Kim-Bang Ko Kong Koo Kun Kwen Lee Leigh Lo Maben Maeng Melhado Mo Mok Na Namkung Nash Noche O Ohw Okada On Orellana Park Phipps Provost Revomir Seol Shelton Shin Sin Sohng Sok Son Song Su Sung Tak Underwood Van Heezik Vasquez Vaughn Veronica Lopez Wang Won Woo Yeom Yi Yom Young Yu Yum Yun Zheng Myeong Gang Byeon Uhm Ung Sean Marlene  ????????¢ç¡§u'?¢ç¡§¢®??c,  ????????¢ç¢®¢¯????¢ç¢®¢¯ Ahn Cheong Chung Hahn Han Harm He Huh Kang Kim Kimq Ko Ku Mitamura Mong Oh Oi Pakr Rkee Ryou Seh Son Sun Hwen Um Won Yi Yoo Soon Ann ./ Byun Kyun Bae_K Bahng Chai Chang Park Cheun Choh Ham Henderson Hong Lee Jheun Jong Jung Woo Keum Km Kweon Lyoo Ok Pang Pyio Rose Sang Cho Seong Seung Shryryer So Steffes Sun Young Um Wi Yae Yeo Young Sook Ji Gu Go Geureiseu Gi Janie Ki Hue Sang Gwak Ma Gwon Ryeon Eom Susana Ju Hyang Bo Fan Jeannie Yuk Cheung Ae Yank Bark  ??? Bahn Chin Eum Eun Hahn Haw Imanari Juing Kay Kiim Okuyama Rha Sir Soo Sown Sunwoo You Zong Juun Youn Burnett Chang Chen Chey Chyu Chyun Docko Dong Ersteniuk Gahng Ho Ihao Ikeda Il Il Soon Jee Jun Ka Kam Koh Kwan Lee Mah Marie Kim Minn Mitrovich Namgung Nan Noe Ohm Oun Pak Pang Park Pee Setoguchi Shim Song Sur Ti Wee Wei Weon Ynsfran Youm Gong Do Moo Medina Palk Taik Eunsook Harmon Yon Je Hwang /Stacy Yoo  ??? Alas Anderson Che Choy Chunez Chyung Gwun Jhon Johung Kay King Kwean Limb Min Namgoong Paeng Pan Park/Song Sahng Soe Suh Y SAHNG SOE SUH YIN Y
Personal tools
smallmenu
Toolbox2