Multiple mailings at the same household

Robert Brown

Hi everyone,


I work at a JK-12 school, and we send out a newsletter twice a year and an annual report once a year.  Recently my boss has wanted mailings to only go to an address once, which is understandable to keep costs down.  The problem is that once children graduate/leave school after being here a few years, they get their own record, and where alumni still live at home the mailing is supposed to only go to the parent(s).


And "Head of Household" only works with spouses.


Does anyone know of a way to accomplish this without combing through the Excel export file (we send to a mailing house) to remove them manually (I do this now and I hate it), or manually going through all the alumni records to find the ones with the same address as the parents and marking them to not receive newsletters/annual reports....and then hoping I remember to take that off if/when they move?

Carolyn Moatz

Robert Brown:

Hi everyone,


I work at a JK-12 school, and we send out a newsletter twice a year and an annual report once a year.  Recently my boss has wanted mailings to only go to an address once, which is understandable to keep costs down.  The problem is that once children graduate/leave school after being here a few years, they get their own record, and where alumni still live at home the mailing is supposed to only go to the parent(s).


And "Head of Household" only works with spouses.


Does anyone know of a way to accomplish this without combing through the Excel export file (we send to a mailing house) to remove them manually (I do this now and I hate it), or manually going through all the alumni records to find the ones with the same address as the parents and marking them to not receive newsletters/annual reports....and then hoping I remember to take that off if/when they move?

We used to not send magazines to college-aged students with the idea that they would see their parents' copy of the magazine. There would still be some duplicates, but not nearly as many. The only thing I've been able to think of is adding a salutation of The Jones Family, sorting by address, then addressee Z-A, then removing duplicates, but I'd still run into needing to remember to update when the kid moves out.

Ryan Hyde

Part of Carolyn's suggestion made me just think of something.


What if you added an additional addressee to everyone's record that reads "The [last name] family" (you could manage this via global change, I think, once you've set up the appropriate addressee structure) and include this addressee text in your export. Then you could remove duplicates based on this custom addressee plus address lines, leaving you with, usually, only one instance per household. I say 'usually' because last names aren't always consistent among spouses and children, but this would probably get rid of most of the problems for you. You could then highlight dupliate addresses and sort them and manually pick apart whatever is left.

Alicia Barevich

Same situation here - I do this a few times a year for every large-scale mailing we send out!


I sort the spreadsheet by Address Line 1, then Const Code and and class year, so that the top line is the addresse who should receive the mailing.


Concatenate Addressline 1 + City, then Copy, paste-values


New column formula something like =if(A3=A2, "dup", "keep"), where A is the column with the concatenated string. Copy your dup/keep row, paste as values.


Glance through spreadsheet for addresses that look like college addresses. For example, all students at St. Olaf or Carleton use the Commons address as their mailing address. Therefore, we could have 5 separate students at the same address. This isn't foolproof, but they're often fairly easy to identify (1500 St. Olaf Avenue, and 5 recent grads at that address gives you a bit of an idea). For any found, manually change the tag to "keep"


Sort column by dup/keep. Delete all "dup" rows.


Done!


It sounds complicated, but as long as you don't scritinize for the colleges too much, it shouldn't take more than 30 minutes once you get the hang of it.

 

Ryan Hyde

Oh, I just realized that my solution wouldn't inform you at the end about whether to use the individual addressee or the new custom addressee. So to throw another wrinkle in, you could use COUNTIFS in a new column before you do any deduping to indicate how many instances this combo exists in the spreadsheet. Then just copy-special-characters-only this data over itself before you dedupe. If the nuber =1, then use the individual address, and if it's >1 use the family addressee. 


I don't know if that'll work for you or not though - this is all hypothetical, and your communications people might hate the "The [last name] Family" idea.

Steven Cinquegrana 2

Robert Brown:

Hi everyone,


I work at a JK-12 school, and we send out a newsletter twice a year and an annual report once a year.  Recently my boss has wanted mailings to only go to an address once, which is understandable to keep costs down.  The problem is that once children graduate/leave school after being here a few years, they get their own record, and where alumni still live at home the mailing is supposed to only go to the parent(s).


And "Head of Household" only works with spouses.


Does anyone know of a way to accomplish this without combing through the Excel export file (we send to a mailing house) to remove them manually (I do this now and I hate it), or manually going through all the alumni records to find the ones with the same address as the parents and marking them to not receive newsletters/annual reports....and then hoping I remember to take that off if/when they move?

Hi Robert,


To me, this screams out for the use of direct SQL and an adjunct Admin database. (Pull the data you want from BB DB, log your mailings and addresses, etc in Admin, etc, wash the first against the second ...) Much more maintainable than Excel spreadsheets and you have 100% control over your data. IMHO.


Cheers, Steve

JoAnn Strommen

Robert,

I used to do this manually until I found out our mailing house can do it with the snap of a finger! They dedup using address field all of our mailings for us now as we are often combining donors, Y members and other lists.  Ask your mailiing house!!!  Any fee we pay is much less than the time it takes to do it in house expecially for us as different lists often use different formatting for addresses.

Robert Brown

JoAnn Strommen:

Robert,

I used to do this manually until I found out our mailing house can do it with the snap of a finger! They dedup using address field all of our mailings for us now as we are often combining donors, Y members and other lists.  Ask your mailiing house!!!  Any fee we pay is much less than the time it takes to do it in house expecially for us as different lists often use different formatting for addresses.

Hi JoAnn,


I've asked them, and they do this, but they take the first one found.  There's no way I can guarantee that the first one found is the alumni parent, which is what my boss demands.  The one time we had them do it, most of the mailings ended up to the alumnus.  I got in some trouble for that one.


Robert

JoAnn Strommen

Robert,

Can you send them 2 separate lists? One of parents, second of alumni. Then they use parent file as priority and remove duplicates found in the second alumni file.

Karen Stuhlfeier

 We have our mail house remove duplicate addresses and change the addressee to the last name or last names and the word family.

Jessica Smith 4

I've asked them, and they do this, but they take the first one found.  There's no way I can guarantee that the first one found is the alumni parent, which is what my boss demands.  The one time we had them do it, most of the mailings ended up to the alumnus.  I got in some trouble for that one.


 

We had the same problem. We are a private high school and we have alumni who have alumni children and will get all fired up if we send to their kid and not them.  And we have a few persnickity folks that want one mailing for each alum in the household, no matter if there are 2 or 7! Even using HOH on our mailing lists doesn't really work for us and we don't allow the mailing house to dedup anymore for the removing the first one issue too. Our top dogs here do NOT want to mail to just The NAME Family.

We have been trying to figure out a way to use excel to combine the addressee fields into one when the address is duplicated but so far none of our formulas have worked the way we want. I am interested in the reply from Alicia about using the IF function with Keep and Dup values. It sounds interesting.

Our summer magazine mailing is coming up so I will have to play around with options. 

Elizabeth Johnson

This comes up in the community every year or so. Here is a link to an older thread with some more ideas.


I hope that helps. Some great ideas going on here too!

 

Alicia Barevich

I am interested in the reply from Alicia about using the IF function with Keep and Dup values. It sounds interesting.

Our summer magazine mailing is coming up so I will have to play around with options. 

If you have any specific questions about it, feel free to message me and I can help you out!

Amy Stubbings

JoAnn Strommen:

Robert,

Can you send them 2 separate lists? One of parents, second of alumni. Then they use parent file as priority and remove duplicates found in the second alumni file.

Yes! That's what we do! Maybe we're just lucky to have good mailhouses in the area.

Jennifer Lange

Robert Brown:

Hi everyone,


I work at a JK-12 school, and we send out a newsletter twice a year and an annual report once a year.  Recently my boss has wanted mailings to only go to an address once, which is understandable to keep costs down.  The problem is that once children graduate/leave school after being here a few years, they get their own record, and where alumni still live at home the mailing is supposed to only go to the parent(s).


And "Head of Household" only works with spouses.


Does anyone know of a way to accomplish this without combing through the Excel export file (we send to a mailing house) to remove them manually (I do this now and I hate it), or manually going through all the alumni records to find the ones with the same address as the parents and marking them to not receive newsletters/annual reports....and then hoping I remember to take that off if/when they move?

This isn't of much help, but can I encourage you to revisit this policy? We have a multi-generational household and we get one mailing from our Library to the house. I never see it. Yes - it may be a fault of my family members, but I don't like the one-per-household thing.

Robert Brown

Jennifer Lange:

Robert Brown:

Hi everyone,


I work at a JK-12 school, and we send out a newsletter twice a year and an annual report once a year.  Recently my boss has wanted mailings to only go to an address once, which is understandable to keep costs down.  The problem is that once children graduate/leave school after being here a few years, they get their own record, and where alumni still live at home the mailing is supposed to only go to the parent(s).


And "Head of Household" only works with spouses.


Does anyone know of a way to accomplish this without combing through the Excel export file (we send to a mailing house) to remove them manually (I do this now and I hate it), or manually going through all the alumni records to find the ones with the same address as the parents and marking them to not receive newsletters/annual reports....and then hoping I remember to take that off if/when they move?

This isn't of much help, but can I encourage you to revisit this policy? We have a multi-generational household and we get one mailing from our Library to the house. I never see it. Yes - it may be a fault of my family members, but I don't like the one-per-household thing.

 

Hi Jennifer,


Unfortunately, it isn't my policy to revisit.  This is a directive from my director and associate director.  We had many complaints about the multiple mailings, but we haven't had any since we moved to 1 per household.  I personally agree with you, but I'm not the one to make the policies.


Robert

Katherine Mannion

 

We use the mailhouse to de-dup.  We give them the entire list, and it seems they sort by address.  Where ever they see a duplicate address, they mail to all names with that address on one piece. (When we get the list back to review, they have moved all the names to fields on the same row as the address)

    Mr. Tom and Mary Jones

    Tim Jones, 2012

    Sarah Jones, 2014

    123 Main St

    Anytown NY 14345


This way no one is left out, but we cut down on mailing costs.  Also, we pull the addressee from Additional addressees.  Every constituent has one we call Publications Addressee.  For most, it is the same as their Primary.  But for married alumni couples, for instance, both records have the exact same edited Publications Addressee.  The mail house only puts it on the piece once in that case.  Mine, for example, is Mr. & Mrs. Timothy '80 and Katherine '88 Mannion.  It's a mystery to me how they do it, but I'm just glad I don't have to do it by hand!

Christine Cooke 2

Robert Brown:

Hi everyone,


I work at a JK-12 school, and we send out a newsletter twice a year and an annual report once a year.  Recently my boss has wanted mailings to only go to an address once, which is understandable to keep costs down.  The problem is that once children graduate/leave school after being here a few years, they get their own record, and where alumni still live at home the mailing is supposed to only go to the parent(s).


And "Head of Household" only works with spouses.


Does anyone know of a way to accomplish this without combing through the Excel export file (we send to a mailing house) to remove them manually (I do this now and I hate it), or manually going through all the alumni records to find the ones with the same address as the parents and marking them to not receive newsletters/annual reports....and then hoping I remember to take that off if/when they move?

Yup -- been there done that --  With Alums that are still living at home, I marked them with a Solicit Code of Duplicate in Household.  Then when pulling for newsletter/magazine you can exclude those with that Solicit Code -- it really does cut down on the number you mail !  at least it did for us.

Cara Perez

JoAnn Strommen:

Robert,

I used to do this manually until I found out our mailing house can do it with the snap of a finger! They dedup using address field all of our mailings for us now as we are often combining donors, Y members and other lists.  Ask your mailiing house!!!  Any fee we pay is much less than the time it takes to do it in house expecially for us as different lists often use different formatting for addresses.

Our mailhouse does the same thing. We ask them to de-dup all of our mailings so we are not over paying for postage. Our mailings continue to go to parents until we get a new address for our young alums. 

Jen Mackinder

Robert Brown:

Hi everyone,


I work at a JK-12 school, and we send out a newsletter twice a year and an annual report once a year.  Recently my boss has wanted mailings to only go to an address once, which is understandable to keep costs down.  The problem is that once children graduate/leave school after being here a few years, they get their own record, and where alumni still live at home the mailing is supposed to only go to the parent(s).


And "Head of Household" only works with spouses.


Does anyone know of a way to accomplish this without combing through the Excel export file (we send to a mailing house) to remove them manually (I do this now and I hate it), or manually going through all the alumni records to find the ones with the same address as the parents and marking them to not receive newsletters/annual reports....and then hoping I remember to take that off if/when they move?

I use Excel to accomplish this - I copy Address1 into a second column and use the text to columns feature in Excel to separate the address parts with space as a delimiter.  Once that is done I title the column headings 1, 2, 3, etc. then I sort by 1, 2, 3 so that everything is in order.  Next I use conditional formatting to easily identify the duplicate address (when I tried to use the built in conditional formatting for duplicates I was seeing red everywhere and my spreadsheet kept freezing up) - I will write out the formula based on the information in it, you would use the cell values, taking out any $, and then use the format painter to paint the whole column =OR(And((AddressNumber1=AddressNumber2), (StreetNumber1=StreetNumber2), And((AddressNumber2=AddressNumber3), (StreetNumber2=StreetNumber3))).  Once the dups are hightlighted it's just a matter of updating the Addressee (either stack the names or use Alumni at) and deleting the remining instances of the address.  If you would like to see a sample file with the conditional formatting formula please email me at jmackind@schoolcraft.edu.  I was able to get our alumni file (~70k records) cleaned up in a couple hours with this method (~5-6k dups) as opposed to days with my first attempt.

Amanda Antony

The organization I used to work for used a solicit code. For the "main" constituent, they put MCAA-primary (multiple constituents at address). For all constituents at the address they didn't want to include on the mailing, they used MCAA. For the primary record, they added an addressee, MCAA Addressee, which could be The Smith Family or John Doe and Family or whatever. Then when they pull a mailing list, they do not include MCAA.


To implement it, you could make your adjustments to your next mailing list, include the import codes, then import the list with the new solicit code and addressee infromation.


Hope that helps!