In this week's post we bring you three Excel functions that you should know if you manage AdWords campaigns (and if you don't, too).
If you work with a million Excel documents every day that have more rows than you can count and whose cells are made up of an incomprehensible mixture of numbers and letters interspersed, then read on, this is of interest to you!
Although the approach and the examples I am going to add in this short post are based on real cases of Google AdWords campaign management, its possibilities are endless, so you will surely find a way to adapt it to your needs (contract elaboration, data association, database creation, etc.).
Concatenate
We will start with one of the easiest functions to apply and that you should master perfectly: concatenate. This is a text formula that combines the contents of two cells into a single cell. As an example, if we had a database with the names and surnames of our clients in different cells and we wanted to merge that information in a single cell, by means of concatenate we could move to have that data in a single cell and would be ready to insert in an email, for example. The concept is very simple, so let's take a look at a practical example and, most importantly, see how it can help us in our AdWords campaigns.
At Geotelecom we use this function almost daily. What for?you may ask. For change keyword matching massively. Very often, we receive or download large lists of keywords which we must match in order to be able to work with them in AdWords. The way to proceed is very simple:
- Let's imagine that our client sends us the following list of keywords and we want to create a campaign with them:
- The first thing we have to do is decide what type of concordance we are going to establish for our list. Usually these are long lists, with more than 50 keywords, so we are usually more interested in using phrase ("keyword") or exact ([keyword]) matching. Let's imagine that we are interested in using exact match, since we are using generic terms.
- What we will do is write in a contiguous cell the signs of the chosen concordance (square brackets, in this case) and proceed to create the formula: =concatenate. We will have something like this:
- Next, we complete the formula selecting each of the cells we want to merge (between cells, we will need a 😉 and this will be the result:
- We have set the cells B1 and C1 (by pressing F4 or by adding the symbol $ before the row and column to be set) so that when the formula is dragged down, the brackets are retained.
In this way, we have already managed to convert a list of words in keywords with a given match that is ready to be uploaded to AdWords.
Long:
Another of the easiest and most time-saving functions to apply is the following formula long. What it does is contar the characters in a given cell. By now, I'm sure you all know how it can help us when it comes to managing AdWords. In fact, in the creation of text ads. Many of us prefer to create ads in bulk in Excel and then upload them to the interface using the Editor. With this formula we will not exceed the number of characters and we will be able to make ads optimizing the space to the maximum. Let's see an example:
To apply the formula, just type =long and select the cell you want count. In this way, we know at a glance whether we need to trim our texts or, as in the case of the title 2if we can think of something that takes up more characters.
SearchV
Finally, I bring you a less known and less used formula, whose application is somewhat more complex, but once you know it you will not be able to stop using it. As its name suggests, thanks to BuscarV we will be able to search for a specific item within a range or table and retrieve the associated information to this element. An example makes it much clearer:
Let's say our client has selected a list of products that he wants to promote in AdWords and has given us a list with hundreds of MPNs (manufacturer's references). Obviously, when it comes to tagging them in AdWords, we need the IDs, since this is the reference we are working with. How can we find out which MPN corresponds to each ID, without it becoming an arduous task? The answer is: FindV.
In the image below we have the list that our client has sent us and, right next to it, another one in which we have the complete list of MPNs and IDs (we could work with our feed). What we need is to look for the values of the first image and the formula returns the ID associated with each of those MPNs.
- First we will write in the cell we are interested in =searchV. The first thing we will be asked is the searched value (search_value). In this case we will choose the range of MPNs sent to us by our customer.
- Next we need to indicate where to look for the value we are interested in (search_matrix_in). So we will choose the complete matrix, both the MPNs and the IDs columns.
- The third step is to indicate in which column is the information to be retrieved (column_indicator). In our case we will write 2, since this is where the IDs are.
- Finally, the formula needs to know if the match with the searched value has to be approximate or exact. In our case we want it to search for the exact reference we have provided, so we will write FALSE. Otherwise, if we are not sure how the data we are looking for can be reflected, we would write TRUE.
Once the formula is finished, we find something similar to this:
And, as if by magic, we will have recovered the information we need, we will have associated the IDs that correspond to each of the MPNs we are interested in promoting.
And this is the end of today's post, did you find it interesting? Leave us a comment and share with us which Excel functions you use to help you with AdWords management.
See you next time! 😉
