16 September 2015 | 18 Comments
A few weeks ago I tried a little experiment with my Between Two Cities and treasure chest backers.
We’re at the stage that we’re close to fulfilling backer rewards, so I sent out a Google Form to all backers to collect address updates. I sent the form via MailChimp to a little over 6,200 backers.
I was concerned that many backers would submit address updates even if their addresses hadn’t changed. I’ve been using and customizing backer data on my own spreadsheet for months now, so I would have to go through the spreadsheet line by line to check each updated address. Checking for unchanged addresses could take a lot of time.
So I sweetened the pot. I made an offer to both Between Two Cities backers and treasure chest backers: If there were fewer than 25 redundant address updates–that is, addresses that had already been updated or were the same on Kickstarter as they were on my spreadsheet–I would give away a copy of Between Two Cities and a treasure chest to one random backer from each group. I specifically asked backers to take a few seconds to look at their address on Kickstarter before filling out the form.
How did it work out? In total, there were 554 address updates (470 for B2C and 84 for TC). Over half (253) of the B2C address updates were redundant. Nearly half (39) of the TC address updates were redundant.
So, overall, a somewhat failed experiment (though it may have been much worse if I hadn’t offered the incentive). I’ll try it again in the future, but I’ll try to make the offer even more visible on the address update e-mail and project updates.
When I revealed this data to backers and told them how long it took me to manually check and update the addresses (7-8 hours total), a few of them were bewildered that I would do something like that. One of them, Brody Lipperman, kindly offered to build a macro that could check addresses for me.
Brody and I e-mailed back and forth a lot last week. He built the macro and I offered a bunch of feedback to help it cut down on mistakes and to be more effective, and the result is pretty amazing.
The macro looks at all updated addresses and compares them to the backer address spreadsheet you’ve been working from ever since sending out your backer surveys. It identifies addresses that have changed and those that haven’t changed. Then, with a click of a button, it takes all changed addresses and plugs them into your backer address spreadsheet so you don’t have to copy and paste anything. It cuts down on time and human error.
You can download the macro spreadsheet here. When you’re ready to use it, follow these instructions step by step. They’re very precise, but they will save you a TON of time compared to manually updating addresses.
- If you downloaded your backer address spreadsheet from Kickstarter a while ago and have made revisions to it over time, save a backup copy of it.
- Open your backer address spreadsheet and the QuickCompareStone spreadsheet. Both must be open for the macro to work.
- Temporarily open the spreadsheet containing backer address updates. For me, it’s a downloaded Google Doc. For others (those who have allowed backers to update their addresses on Kickstarter itself), it’s a current download of the Kickstarter survey data. Move around the columns until they fit the exact column format of the QuickCompareStone spreadsheet (columns with green header [B-I]), then copy and paste all of that data into QuickCompareStone (starting with row 12). If you need to remove data from a previous use of the macro, only delete the old cells, not entire rows of data (there are some codes on the far right of the spreadsheet you don’t want to delete). Then close the address update spreadsheet—you don’t need it anymore.
- On the QuickCompareStone spreadsheet, enter the name of the backer address spreadsheet in cell C2, as well as the name of the tab in the backer address spreadsheet where backer addresses are located. “Email Start Row” refers to the first row where e-mails appear on the backer address spreadsheet. You will also need to enter the columns from the backer address spreadsheet over their respective header names in row 10 (columns B-I). For example, if your zip code is in column X of the backer address spreadsheet, you would enter X into cell H10 on the QuickCompareStone spreadsheet
- Verify that all yellow cells on the QuickCompareStone spreadsheet have been populated/verified and that there is a formula in column M for every row of updated addresses.
- On the QuickCompareStone spreadsheet, click on the Compare Addresses button. It populates the table to the right (columns with red header [N-S]), giving you info about what changed (if anything). The macro looks at e-mail addresses to identify matches. It even reformats some incorrectly formatted zip codes. This process will take 20-30 minutes, and it may slow down certain programs on your computer. Do it during lunch.
- You can then manually review each line (this is optional), comparing the new addresses (green columns) with the old addresses (red columns). They’ll be sorted by “difference value”—that is, if an address had huge changes, it will appear at the top of the list, while addresses with small changes are listed at the end.
- When you’re ready for the macro to update your backer address spreadsheet with the new data (it will only use data where “Yes” is the word in the “Update?” column—you can manually change that word to No if needed for any data, or vice versa), then you click the Update Address button. This will take the “Yes” addresses and paste them into the proper row in your backer address update spreadsheet.
- You can click on Compare Addresses again and you should see all “No”s (as the original address has been updated).
Huge thanks to Brody for making this incredible tool! (Update: Adam Buckhingham made some great changes to the macro to make it faster and more dynamic. Thanks Adam!)
Last, I wanted to mention that my crowdfunding book was released on Monday! I’ll paste the links below where you can buy it (and hopefully review it on Amazon–that would be a huge help!)
The content is different than this blog, as the book focuses on stories from other creators and a behind-the-scenes look at my Kickstarter experiences. It also features some top 10 lists not on the blog and 125 of the most important Kickstarter Lessons condensed into 1 sentence each–much more concise than the blog. :)