Kickstarter Lesson #164: How to Automate Backer Address Updates

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.

  1. 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.
  2. Open your backer address spreadsheet and the QuickCompareStone spreadsheet. Both must be open for the macro to work.
  3. 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.
  4. 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
  5. 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.
  6. 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.
  7. 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.
  8. 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.
  9. 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. :)

Amazon / Audible / Barnes & Noble / Independent BookstoresBerrett-Koehler

18 Comments on “Kickstarter Lesson #164: How to Automate Backer Address Updates

  1. That’s a great resource. With 1900 backers I too fear the “address update” moment; as your 7-8 hours is no joke. I was planning on using a series of “vlookup” functions the same way I verify and validate “amounts paid” in our system, but I’ll have to give this macro a try too. Thanks Brody!

  2. I’m glad to hear that Brody came to the rescue. As I started reading this I was horrified initially but was relieved that a programmer came to your aid. I do this kind of data analysis all of the time so if you ever need help and Brody isn’t available, please reach out. This type of thing should only take seconds. Thank you for the post and the new tool!

    1. Thanks Matthew, I appreciate your offer! I should have thought to go to programmers before the manual data entry. I’m comfortable with Excel (in fact, I love Excel), but clearly I’ve just barely seen a little of what it’s capable of doing.

  3. I was initially dismayed that you did this by hand but the Rocky Balboa finish made me smile. Sure you took a few hits but you rallied and won!

    If you ever have a task that requires any hand management of data, reach out to your fans because there is almost always a solution that will put hours back on your clock!

    If I had to check addresses for 7 hours, I’d flip the table and reach for Viticulture to unwind!

  4. The Hell of Address Correction is indeed a terrible fate — made even worse by the fact that some customers don’t know where they live (or more precisely, where they think they live and where the post office thinks they live are located in parallel universes). When I was messing around with my KS database project, I spent a lot of time automating it.

    What I ended up doing is:

    * Scanning for changes any time I reimported the KS database, updating entries that changed and most important, flagging changes for possible manual review and providing a “revert” option.

    * Integrating USPS address correction/reformatting, and having an easy search for US addresses that did not validate.

    * Automatically emailing people when their account changes (including address changes, giving before and after) so they can let me know about any errors that were not caught.

    * Building a very flexible address parser (it currently has about 30 parsing templates for both US and country-specific address formats, stuff like “{$Address1}¶{$Address2}¶{$City}, {$State==Validate(s,$$US.STATES)} {$Zipcode==Ziplike(s)}” so I can just roughly grab a block of text from an email or KS message and it finds and pastes in the address.

    Sending out a “we’re about to ship, check your address” email before entering The Hell of Shippiing.

    This turned out to make it remarkably painless. When someone emails me with an address change, it’s 15-20 seconds to fix it. All the automated stuff means that the manual fixes are concentrated just before shipping.

    Spreadsheets are great for lots of things but for a project with more than a couple of hundred backers, they are going to creak. Databases are so much more flexible.

  5. Hi Jamey, sorry if this question has been answered somewhere else: Does this mean that you should not allow backers to change their address on Kickstarter, and then send them a Google Form separately?

    1. Hannah: My preferred method–as someone who sends out the survey through Kickstarter almost immediately after the project ends, editing the spreadsheet over many months–is to lock address changes on Kickstarter when I download the spreadsheet, then send out an address update reminder through Kickstarter when the rewards are ready to ship.

  6. Thanks, you are so fast :) So practically this Kickstarter “new feature” did not really help project creators very much? https://www.kickstarter.com/blog/new-feature-managing-backer-address-changes

    I am also “the second type” who send survey out immediately, in my first project I did not allow address change because the delivery date was not very far away. However, I am toying with the idea of leaving address change open, download the first version of the spreadsheet asap to work on product, then close the address change right before shipping and merge the correct address to the corresponding backer number. Do you foresee any problem with this approach?

    1. Hannah: Well, it helps creators who don’t rely so heavily on the exported data as I do. The system you described should work fine. The only risk is if you don’t like it, if you go into Kickstarter to close address updates, Kickstarter will automatically send out an e-mail to backers to say, “Hey! This project is getting ready to ship” even if it’s nowhere near your actual ship date. You can’t edit or not send that message–for some reason Kickstarter links it to you making addresses uneditable.

      1. Hi Jamey, thanks again, I think this ““Hey! This project is getting ready to ship” email is the most troublesome and makes the whole “address change” feature obsolete. I hope Kickstarter knows about it and makes the small change, it would save creators and backers so much trouble.

Leave a Reply to Insights from Fulfilling Scythe, Part 1 | Stonemaier Games Cancel reply

© 2019 Stonemaier Games