This process is for creating a list of leads in order to seed a new Facebook audience. Our goal was to identify a subset of leads that would be more likely to purchase our books.
We ran a contest using SumoMe, and once individuals signed up for our email list, they were asked a simple question "What is your favorite author?" Those who answered with a non-sequitor or who just said "can't wait to win your contest" should be filtered out.
We received over 900 entries and around 150 emails, plus we plan to run these contests every month, so it's imperative to have a great process to do this quickly!
-
Copy the "feedback" out of word press and paste it into a Google Docs spreadsheet, page by page (feedback items, like comments are listed 10 per page.
-
Skim through the comments and delete rows that don't meet our criteria (answering the question asked)
-
Remove duplicates // In Google Sheets, highlight the column B // Go to Format --> Conditional Formatting // Apply a "countif" formula (ref: http://stackoverflow.com/questions/21899516/highlight-cell-if-value-duplicate-in-same-column-for-google-spreadsheet) // In Excel, go to Data --> Remove Duplicates
-
Add a new sheet and use an array formula to split the cells in column B by line break (ref: https://productforums.google.com/forum/#!topic/docs/6MO1M585WQs)
-
Copy all the data and paste values. Delete extraneous column.
-
Filter remaining column for all cells containing @.
-
Capture the name next to the email by applying a formula to the right of the email address. If email is in B3, forumla in C3 = B2
-
Unfilter, copy and paste all values. Filter column C on empty cells (or column B on cells that Do Not Contain @), delete rows that show up after filtering.
-
Unfilter and sort data A-z.
-
Select column C and convert Text to Columns, using space as the deliminator. This will give you a column of First names and one of middle initials or last names. Clean up the remaining data.