One of the most useful documents in the “Useful Stuff” folder I keep on my desktop is an Ad Copy Proposal Template. It’s not the sexiest Xcel file you’ve ever seen, but it is simple, efficient, and I make copies of it nearly daily (I write a lot of ad copy). I’m going to walk through why I love this doc and how to build your own. You’ll find that putting the time into building a great master template will save you time formatting and organizing proposals for your clients or boss.
Why I Love This Doc
We’re not reinventing the wheel here. You can use AdWords editor, the Google Ads interface, or mock-up websites to preview how your ads will look in the wild. I like using a template for consolidation and organization. Those other options are great for one-off examples, but when building an ad copy proposal my goal is to create a menu of options for my client to consider. This template incorporates two sheets – “Ads” (Fig. 2) and “Visuals” (Fig.1).
Fig 1. Example of “Visuals” Sheet
The other sweet piece of templating out your ad copy proposals is having a document that does twice the work in half the time/effort. Offsetting your “Ads”, basically your data sheet, to a pre-formatted “Visuals” sheet means that as you work through writing those bard-worthy bits of ad copy your “Visuals” sheet is doing the background work to gussy them up. By the time you’ve finished writing and click back to your visuals sheet, you have a well-formatted, well-organized, menu of creative to send to your client or boss. for feedback and final approvals. Look at you, you hard working so-and-so!
Fig. 2: Example of “Ads” Sheet
So, Let’s Do The Thing
Part 1 – The Ads Sheet
Start with formatting your “Ads” sheet. This will include the following columns:
- Labels
- Headline 1
- Len
- Headline 2
- Len
- Headline 3
- Len
- Description 1
- Len
- Description 2
- Len
- Final Url
- Display Url Root
- Path 1
- Path 2
- Display Url
Formulas
The only formulas that you’ll use on this sheet are in the Len columns and the Display URL column.
Len Formula
Fig. 3 LEN formula for a description cell. Column has Conditional Formatting to highlight values greater than 90
Use =LEN to keep track of your character counts while writing. I recommend using conditional formatting (Fig. 3) on your LEN columns with Highlight Cell Rules > Greater Than to save yourself from some heartbreak in the future upload process (having the perfect headline that’s 31 characters is pretty high up there on my “Well, This Sucks.” List).
IF Function with a Concatenate
Fig. 4
Using an IF function in Display URL showcases the path’s you’d use (Fig. 4).
What’s happening in this formula?
- If Final Url is blank then use Display Url. unless Final Url is not blank.
- If Final Url is not blank, then use Display Url Root concatenated with path 1 and path 2 to create new Display Url.
Another way to view it: =IF(Final URL=” “,Display Url Root, Display Url Root&”/”&Path1&”/”&Path2
Part 2 – The Visuals Sheet
Once you have your Ads Sheet set up, create another sheet titled “Visuals”
This is the sheet you’ll do all of your visual formatting on to make the creative look like Google Ads. On this template, we left column A blank and only work in columns B, C, & D.
Use the paint bucket to fill the entire sheet as white
Ad Labels
Merge a row of 3 cells across Columns B, C, & D. Use = and choose the first cell that contains your Labels on the Ads sheet to autofill the title bar.
Version Labels & The Invisible Reference Cell
In the next row, you’ll simply label your variations (V1, V2, V3).
Enter a 1 under the V1 cell, 2 under V2, and 3 under V3. Highlight the text and turn it white to match the background. These cells are extremely important because they provide a reference for your Offset formulas.
Offset Formulas For Headlines
In the row under your now invisible row, you’ll use the Offset formula to autofill with your ad’s headlines. In your Offset formula, you’ll need to include spaces and bars to make it look more like a real ad.
What’s happening in this formula?
- Offset Ads Sheet Headline 1 to under the referenced invisible cell on Visuals Sheet. Add a space, a bar, and another space, and do the same thing but with Headline 2. Add a space, a bar, and another space and follow those same rules for headline 3.
Another way to view it: =OFFSET(Ads!Headline1,Visuals Invisible Cell,0)&” | “&OFFSET(Ads!Headline 2,Visuals Invisible Cell,0)&” | “&OFFSET(Ads!Headline3, Visuals Invisible Cell,0)
Format the text to bolded 12 pt. Arial and change the color to blue. You’ve got your headlines done!
Offset Formula For Display Url
In the cell under your headlines do another Offset function to insert your Display Url. =OFFSET(Ads!Display Url, Visuals!Invisible Cell,0)
Format the text to a regular 12pt. Arial and change the color to green. Boom! Display Url.
Offset Formulas For Descriptions
I bet you’re noticing a theme here, and you’re right! For Description 1 we’ll use
=OFFSET(Ads!Description1,Visuals!Invisible Cell,0)
in the cell under your Display Url and
=OFFSET(Ads!Description2,Visuals!Invisible Cell,0)
to insert Description 2 under Description 1.
That was a lot of work. Fortunately, as long as you anchor ($$) all of your Ads Sheet cells, you’ll be able to copy these formulas over to columns C and D (aka V2 & V3). Once you’ve made your first block of ad variations, you can copy and paste it below for your next theme variation.
Important things to note:
- Once you’ve made a copy of your first variation block, you’ll need to update those invisible cells to keep the automation going for your Offset formulas. In V1’s invisible cell, change the 1 to =V3’s Invisible Cell + 1. Then change V2’s invisible Cell to =V1’s Invisible Cell +1 and V3’s Invisible Cell to =V2’s Invisible Cell +1. Now when you copy this table it will automatically update the Invisible Reference Cells and keep up with your Ads sheet.
Formula in Invisible Reference Cell is changed to =D5+1
Formula in Invisible Reference Cell is changed to =B13+1
- If you don’t want to have to type the Label into each Visuals section, change the =Ads! Referenced cell by 3 (i.e. =Ads!B5 to =Ads!B8)
Final Thoughts
This template takes a little bit of time to flush out, but once you’ve got it, it can be a time saver. Once the copy is approved you can quickly build an upload sheet and get moving with your next ad copy test. This is my go-to for writing ad copy because it’s easy for me to go data-blind looking at Xcel sheets. Having a clear visual helps me see where I’m being redundant or where what made sense as an individual description doesn’t quite fit when looking at the ad as a whole. It’s all about perspective.