I’m a fan of automation. It’s not something I generally see amongst other designers I’ve worked with, or otherwise it’s not something that is often spoken about in design circles.
That’s because automation isn’t sexy. It’s not bespoke. However automation – as most of my readers would know – is awesome because it helps you to do repetitive tasks really easily, saving you a bunch of time. Automation can also open up new opportunities for content creation and personalisation, as this little example shows!
Today I’ll be creating a dynamic birthday email message to sport club members with a photo of their favourite player, using Excel, a Text Editor, InDesign with Data Merge and your Email Marketing client of choice.
See the example below, it’s slightly cheesy and unrefined but it gives you a good idea of what you could achieve:
Here’s my basic recipe for making this dynamic for each member:
Step 1: Create Your Excel Sheet
However your CRM spits out the info, you should be able to get that as a CSV to open in Excel.
I’ve created one from scratch, see the picture below:
Column A :the fan’s First Name – we’ll use this in the InDesign Mail Merge.
Column B :the fan’s Email Address – for use in whatever Email Marketing client you use.
Column C: the fan’s Date of Birth – for use in whatever Email Marketing client you use.
Column D: the fan’s favourite player – this data won’t be needed in either your Email Marketing client or InDesign – it’s so we can match Column F up to make sure it’s accurate.
Column E: File Path – this is the path to the player photo in column F and is the same if every photo is in the same directory. To find the File Path (InDesign uses a variation to the normal file path), add one of your images to an InDesign file, and see the properties for the link under the Links Panel. You can copy the path directly from the links panel and paste it into Excel without any issues.
When we’ve completed all the data in column F we’ll combine the 2 columns into 1.
Column F: File name and extension of the player photo. For InDesign to recognise that it is an image, rather than just a string of text, you need to add the @ symbol to the Column header. Excel may give you issues adding the @ symbol, but we can always add it inside a text editor as we’ll see later.
Column G: The intended directory of the exported merged files.
Column H: Column H will have the filename and extensions of the merged files which I’ll show later. We’ll combine this into one column later.
After you’ve added all your data in, save the file as a CSV
Step 2: Format your CSV file
Open your CSV file in TextEdit or Notepad or your other favourite basic text editor (I use TextWrangler for Mac).
You’ll now see all the data and it’s a little hard to read because it’s not displayed in a cellular format.
What we want to do here is to:
- Add the @ Symbol to the front of our PlayerPhoto column header.
- Combine the File Path with the File name (in Find and Replace, add the file path (without the file name) and a comma after it, and then replace your file path with the same path minus the comma) – see the image below if that’s a little confusing.
- Remove the column header for File Path, so the @PlayerPhoto column matches the column with the combined File Path and File Name for the Player Photo.
- Save the file.
Step 3: InDesign time!
Open your InDesign file, and make sure your text area is flexible enough to accommodate both the shortest name and longest names you have on file.
After that, open up the Data Merge window (Window > Utilities > Data Merge). Click the options button and choose Select Data Source. In the pop up window pick your CSV file and click Open or Ok. It should then load the file into the data merge window with the available fields.
In your Design, highlight your Name text, and click on ‘FirstName’ in your Data Merge window. It will add the following: <<FirstName>> into your design.
Then highlight the image bounding box, and click ‘PlayerPhoto’ in your Data Merge window. This will make your image disappear, but don’t fear – it has probably worked.
To check that both fields have been added to the design, click on Preview in the Data Merge window. You can click through previews of all the exports via the arrow buttons in here too, to see whether you need to alter your template file.
Once you’re happy with everything, click the Option button and click Create Merged Document. For more information about the options in here consult the Adobe help files.
When you’re happy with the options, click Ok. This will create a new InDesign file with all the merged documents.
From here, go to File, Export, and select JPEG. Once you’ve customised your options, export all the files into your selected directory.
One downside of this basic recipe is that the file names won’t be customised with the fan’s name which would be really handy. I’m sure someone out there has a script to help this but for the moment I’ll continue.
Step 4: It’s time to customise for Email Marketing
In your Finder or Explorer window, find your exported merged JPEG files.
Highlight all the files, making sure they’re in order, and copy the files.
Open your CSV file in Excel, and add a new column at the end. For this example, I’ve called it ‘Final File Name’. In Row 2, paste your file names. If everything has been kept in the same order, the files will line up with the proper fan name.
Save the CSV file, and open it back up in your text editor. Use the same trick from Step 2 to combine the final file name with the web file path, removing the other column header as well.
From here, your CSV file will be ready for most email marketing clients to set up a birthday email message with your fan’s favourite player!
One downside here is that this is not dynamic – new members won’t be able to be added automatically. You may decide to just have the Favourite player field, and not address the fan by name which would help.
Other Creative Applications
Dynamic discount vouchers for custom merchandise – e.g. jerseys with the fan’s name on it. If you’ve seen the facebook ad promotions based on family names and t-shirts you’ll know there’s definitely a market here.
Fixture and result graphics – for pre-game graphics add the other team’s name, logo, the date and time of the match and you can spit out your 20 or so games in one hit.
Dynamic infographics – I’ll get into this more in the next post, but using the font FF Chartwell you can create nice infographics based on a template using Variables in Photoshop – it’s like Data Merge but a little more basic. Fantastic, engaging online content done without the time investment!