Turning Google Contacts into address labels for Christmas/New Year’s cards is a task that I expected to be simple. The plan was
- export to “Google CSV” format
- upload to avery.com to generate a PDF for printing
This fails because the Google export process produces a CSV file with nearly 100 columns, which is too many for the Avery system to handle.
No problem, open in Microsoft Excel and cut down to about 5 columns, right?
What happens when you combine programmer’s from two of the world’s smartest companies? Excel is not smart enough to recognize a column of 5- or 9-digit values as ZIP codes, even if they appear right after a column of two-character state abbreviations. The leading zeros are trimmed off, turning Massachusetts ZIP codes into four-digit values, e.g., “02138” to “2138” (the ZIP code of the great minds of Harvard and Harvard Square, who will soon be tapped by President Warren to optimize our government).
What if we keep this as a Google-only process? The people who built Contacts apparently don’t talk to the people who built Sheets. There is no way to export directly from Contacts to a Google spreadsheet.
Save to the local disk and then upload, right? The behavior is exactly the same as with Excel: leading zeroes of all of the five-digit ZIP codes are trimmed off. This is the company we’re going to trust with medical diagnoses? (“The doctor will Google you now” turning into “The Google will doctor you now.”)
As with most other challenges, if you’re a skilled user of Excel the solution is straightforward: create a blank workbook and then use the Data tab to import “From Text/CSV”. Even on the full automatic setting, it correctly infers that the ZIP column is text, not number. But if the fully automated import works, why doesn’t it work simply to open the CSV file in Excel?
(The whole process ended up taking way longer than if I’d simply addressed 180 envelopes by hand, of course.)
The particular challenge of wrestling with Google Contacts or generating addressed envelopes is not that interesting, but I think it is a good starting point for a discussion of how machine learning and AI can ever be integrated back into the computer systems we use day to day. Google Translate does some impressive stuff, but why isn’t it easy to enhance Google Sheets?
Separately, the Google Contacts software has a long way to go to reach the same level of quality as what Sharp was shipping with the Wizard organizer in 1989. A contact with a single street address, once exported, will appear in a CSV-file row without any street address. Why is it difficult for Google to do what Apple, Microsoft, Motorola, Nokia, and Sharp were doing successfully in the 1990s?
“Behold, the people is one, and they have all one language; and this they begin to do: and now nothing will be restrained from them, which they have imagined to do.
Let us go down, and there confound their language, that they may not understand one another’s speech”
and she called it CSV!
Nobody at google is going to get a promotion from the committee for making google contacts more usable for third parties.
> The whole process ended up taking way longer than if I’d simply addressed 180 envelopes by hand, of course.
Believe it or not there are still people who do this for a living! Not by hand if we can avoid it, and instead of AI we use good, old-fashioned “I” intelligence, some custom programs, and commercially available mailing software, which I guess you could call “AI” but it’s really just a good application that has been in continuous development for more than 25 years (see below).
I’m surprised that Google hasn’t made it easier for people to normalize/process and export this kind of data and even offer additional services like NCOA. I don’t know how powerful Google AI is, but I imagine if it was trained enough, eventually it would get quite good at fixing address files and giving you what you need. I don’t use it either Google Sheets or Avery’s software.
Back in the not-so-distant paleolithic age, however, my Dad spent a lot of time writing code in IBM 360/370 assembly (later 4341/81) to process mailing lists. Today, however:
The next time you want a mailing list processed and don’t relish spending a lot of time, send me an email. If you had 20 more friends, we could also do the whole mailing for you at bulk mail rates (you need a minimum of 200 addresses to qualify). We use Accuzip 6 V5.0, which also manages address correction through NCOA (National Change of Address), CASS processing, deduping, etc. It’s nice to know which addresses are no longer valid!
After processing we can export sanitized CSVs of your specification, print labels for you, etc., etc., or address and mail the pieces ourselves.
https://www.accuzip.com/products/mailing-software/
Video:
https://www.youtube.com/watch?v=aBQzVeBSeeg
I’d be happy to process a file for you as an experiment, gratis. I’m sure Google AI could be tweaked and improved to handle a lot of these tasks, particularly if they had some advice from people who have been doing it for a long time. In the meantime I’ll stick with Accuzip unless someone from Google would like to pay me to consult.
P.S. I am surprised that Google Sheets doesn’t offer a mailing wizard that says, basically:
“Would you like to export this data as a Microsoft Excel file with the zip code fields properly coded for input, export it as a CSV file for Avery, or both?”
One question I have about using Google Sheets to do this: Who owns the data? Mailing lists, particularly private ones, or lists that have been created with refined microtargeting are expensive and valuable, and as we have seen, very controversial for big companies like Google to be involved with.
That’s one of the reasons I don’t use it, at least right now.
P.S.S. (sorry for the multiple posts): Also, check out their RESTful API offering. If Google decided to disrupt this industry they could certainly start by checking this out:
https://www.accuzip.com/products/restful-api-for-data-cleansing-and-direct-mail/direct-mail-restful-api/
In other words, all the pieces to solve this problem already exist, and if Google wanted to, they could just buy Accuzip or roll their own and integrate it into Google Sheets. They may not have the resources to create a separate Google Doodle for Father’s Day, but I’m pretty damn sure they could throw a team of programmers at this problem and integrate it into Google Sheets with their AI as an adjuvant.
How does direct mail marketing perform in era of AdWords and Facebook ads? I once made a small fortune from a 3% response rate, does it still work?
@anon2: The relatively short, general answer is, “Yes, it can still work very well.” You have to understand your market, target effectively, have a well-designed mail piece that’s part of the rest of your marketing strategy, etc., but we have clients that regularly see response rates of 3%-4% and some has high as 8% or 9%. To keep it brief, some of our fundraising clients in particular explicitly want to assure their prospective donors/customers: “We don’t solicit online or by telephone.” They want to eliminate the potential fraud/spam, in other words. People who robocall or spam fraudsters do not want to spend money on postage, and a return address is a physical location. With a little imagination you can probably think of organizations that would not want the vulnerability of bad actors soliciting donations in their name.
For other mailings, there are very good response rates to be realized if the mailing is done well. Timing, demographics, the pull offer or “extras” included exclusively with the direct mail piece (e.g., “Bring this postcard and get 20% off any 6 inch sub.”) and seeking to break through the clutter of online offers and differentiate from the competition are important. In some cases, the fact that so many companies have shifted to exclusively online marketing works to the benefit of direct mail: if three of your competitors in local area never do it, and you haven’t in a while either, your piece shows up in people’s mailboxes, and it’s novel. You immediately get people’s attention. You have primacy and recency in your favor. We do mailings for a car dealership chain that work very well because of this. We also mail some truly jumbo-sized pieces for them, up to 11×17 folded down to 8.5×11. It’s impressive, and it gets people’s attention.
It also works well for political mailings, especially in local elections. In a tight race, a direct mail piece can easily decide the outcome, because voter turnout in a lot of local elections is meager, and in small towns a dozen votes can make the difference. Knowing who votes in your town and mailing to them can be crucial for GotV, especially if it’s tied into the rest of the campaign effort. You would be shocked to learn how many people don’t even realize or forget when their municipal elections happen. They don’t know who the candidates are. They have never even been to a town meeting. They see the signs on the road, but they’re busy. Off to work and it goes in one eye and out the other, but if you put a big, colorful postcard in their mailbox they remember, and they vote.
So the answer is “Yes.” If you haven’t done a direct mailing in a while, you should definitely try it again. A lot of people have neglected it, but it works. The bottom line is that you’re putting something in people’s hands, that they can also put on their kitchen table, read at their leisure, etc. If you do it right, that can be very powerful. Off my soapbox.
@anon2: (and also @Philg): One other thing: say your town has a controversial bond issue or budget issue that is divisive. Everyone sets up online message boards to discuss it, but relatively few people avail themselves of them, etc. Maybe some people don’t like your views and you get blocked, or people don’t know when the town meeting and election are. It’s important, a lot of money is at stake and relatively few people understand it, or the alternatives. Nobody reads the local newspapers, or they don’t exist any longer.
Maybe you and a few other like-minded people care enough about the issue to spend the money on a direct mailing, so you get together and spend a couple thousand dollars. I did one last year for a significant municipal bond issue that arrived in people’s mailboxes three days before the town meeting. We worked very hard on the piece to thoroughly explain what it meant. The issue passed and it was the highest turnout at the meeting anyone had seen in years.
The bigger question is why do spreadsheet programs still trim leading zeroes? This seems like a behavior rooted in the original Lotus 123 and kept around because “why not?”.
And ZIP codes have been around since 1944, so it’s even dumber that spreadsheets don’t even make the attempt by default.
They don’t really trim leading zeroes as such. The problem is that anything that looks like a number and isn’t specifically set to text formatting gets stored as a number. That’s more compact and allows calculations, but number storage formats never include leading zeroes. A solution would be for the spreadsheet to realize that anything that looks like a number but has a leading zero should be stored as text, not as a number. (The current version of Excel doesn’t seem to have that option AFAICT.)
Have you considered using zapier?
Either Apple Numbers or (Apple) FileMaker would behave better. Excel is riddled with egregious flaws like this, and Google’s stuff is designed to act more or less like Microsoft stuff.
I think the zeroes are still there. Format the column as a different format, general/text I think and they will be back.
They weren’t! I tried changing the format to text and the leading zeroes were not restored.
The only thing surprising here is that you are imagining an advertising company like Google would be good or even passable at producing application software.
I can’t think of any Google office application that is better than what Microsoft was shipping in 1992.
If you look at the csv file, I believe it will show the zip as 02138. By default, Excel imports it as a number, thus dropping the leading zero. If you then convert it to text, it is too late: the zero is gone.
Solution 1: When doing the import, select the field and specify it as a text field.
Solution 2: If left as a number in Excel, format the cells using the format code 0????
This works in LibreOffice and I believe Excel is the same.
Bingo.
As far as AI/ML on the desktop is concerned, right now I think the most ambitious company doing it is Adobe, with Sensei. Not just to take the drudgery out of creative tasks in Photoshop, but also for analytics, marketing and, according to Adobe, a lot more to come.
Selecting photos for an ad. campaign? Sensei can allegedly help:
“Adobe Sensei’s performance within Adobe Stock, its massive repository of images and video, is truly impressive: its visual search feature lets you drop a reference image into the search box and then fine tune criteria to find images with a similar composition, color scheme, contrast, etc. A traditional keyword search requires you to express with exactly the right words what it is that speaks to you in an image, and then relies on how stock images are tagged to find similar ones. With visual search, the AI can ‘read your mind’, seeing the image with your same sense of complexity and nuance, identifying features that can deliver specific artistic effects.”
https://www.forbes.com/sites/marcoannunziata/2019/07/16/creativity-and-ai-adobe-sensei-as-assistant-teacher-and-muse/#6cc586926750
There’s more:
https://theblog.adobe.com/the-next-chapter-of-adobe-sensei/
https://www.adobe.com/sensei/services.html
I really can’t evaluate how much of this stuff is glitzy, professionally-produced hype and how much of it is actually going to transform the way people do creative, marketing and CRM/CXM but Adobe seems to be “all in” on it and trying to build buzz around it. In a way they’re banking a large slice of the company’s future on it.
Nobody has reviewed it on SourceForge (https://sourceforge.net/software/product/Adobe-Sensei/). The few reviewers I can find read a lot like a marketing speak and don’t go into a lot of detail about how Sensei changed the world for their company. The whole thing started in 2016, and it’s almost four years later, so it hasn’t transformed the world yet. On the other hand, Xerox had the GUI as early as, what? 1979? before Steve Jobs found out about it and “borrowed” it. Then it took until 1984 for the Macintosh to appear, and a while longer before the Mac really became useful for anything other than wowing people at computer stores by writing their name on the screen in black and white. So maybe Sensei needs a couple more years before it goes critical and takes over the universe.
Finally, I have a question for your question:
You spent years reviewing camera equipment and trying to teach people how to be better photographers. You helped create Photo.net. Your reviews of early digital cameras were masterful and always fun to read. You traveled the country and a lot of the world taking pictures. If you had known about a product like the Arsenal, would it have dampened your enthusiasm for photography? I mean, it makes everything great for even the most incompetent photographer, right? Why bother learning the hard way, taking thousands of photos, buying expensive equipment, reading and improving one’s skills, becoming better than point-and-shoot, if you can just click an Arsenal on your DSLR and become the next Ansel Adams?
https://witharsenal.com/features
Is the Arsenal any good? They’ve marked it down from $250 to $150. I’d love to see you review it.
It sounds to me like Adobe Sensei takes “the drudgery” out of things like selecting photos by also taking the expertise out of it. What you might have paid a career professional to do for your marketing campaign can now be replicated by a 14 year old in Bangladesh? Is that progress?
If only the idiots who designed ZIP codes made them always start with digits 1-9, or alphanumeric, or include the state abbreviation as a prefix, philg wouldn’t have had this problem, and would have had nothing to write about.
I couldn’t resist. I’m sorry, but this took a long time to produce. It’s truly inspired and will make you weep if there’s a sentimental bone in your body.
Before the ZIP Code:
“There was a boy in New York City
Who loved a girl by Frisco Bay
He sent a card to say he loved her
To say he cared that special way
His letter zigged and zagged along the way
His lonely letter lagged day after day
She waited more and lonely hours
Just to hear what he would say
But when his words were finally spoken
All her love had gone astray
On a sad, sad Valentine’s Day”
After the ZIP Code:
“His letter flew across the country
In just one day it reached her hand
In just one day she knew the answer
The happiest girl in all the land”
Then head out to 11:50, it gets better! “It is as up to date as the computer!” Of course, no criticism of MS Excel screwing it all up yet!