There are lots of Excel functions out there that can make managing and reporting on PPC campaigns easier. My favorite is Concatenate. Simply mastering this function can make so many laborious tasks quicker, which ultimately means there is more time for the juicier optimization opportunities.
First quick tip: you don’t need to write =CONCATENATE to string test sequences together. You can simply use an “&” to piece together your text.
Creating the Best-Structured Campaign and Ad Group Names
When running large accounts that have lots of campaigns and ad groups, you can easily get swamped with naming conventions. Having a clear naming convention across your account is paramount to running an account properly. It simply helps with reporting and optimizing, along with more detail in the anti-concatenate section.
In the example below we have specified country, gender, brand and product type in the ad group name. If we were looking at multiple countries, multiple brands and more than six product types, you can see how the volume of ad groups might soon snow ball.
Bulk Changing Ad Copy
Changing ad copy across an account can be a pretty huge job. We can use the table we used to help build our ad groups name to bulk create ad copy across an account. I have created three sets of ads for a summer sale.
We can use concatenate to pull in the brand and category (using the ad group naming table from point one). We can then pull the formulas down against all the ad groups and all-new ads will be created.
Use the =LEN function to count the characters in your ads so you can tweak any that go over character limit. You can then upload this table through AdWords editor.
Here is a closer look at how the formulas are working:
Building URL Tags
I’m going to specifically look at UTM tags here. Google Analytics gives us some great attribution tools. AdWords and Bing traffic is auto tagged, so it’s not such a big issue, but when looking at how search is tying into all other digital activity, it is important to ensure you have a clear naming convention. The use of concatenate and shared doc will help ensure all channels are following the right tag structure.
I have run through how using concatenate for constructing things is great, but it is also useful to know how to deconstruct them from a reporting point of view.
I have two main ways to do this:
- Using the ‘Text to Columns’ function in the data tab, choose ‘Delimited’ then you can select a symbol to do this. This is where the | symbol in the ad group naming comes in handy, as it will split the columns up. This function is also really handy for splitting destination URLs up from query strings.
- You can also use the Right and Left functions to pull info from a column. In the example below, the location of the campaigns is based on the 3 characters prefixed to the campaign name. Using the Left function allows you to pull this info into its own location column making it easy to report by territory.
Creating these columns allows us turn very granular ad group level data into something a lot more functional. We can quickly create amalgamated charts to help review overall performance for each segment.
Although it’s a simple function, concatenate proves powerful for making account management more efficient. It can assist with so many aspects, from building and bulk changes to optimisation and reporting. Have another useful way to use concatenate? I would love to hear it in the comments!