AdWords
5.1K members online now
5.1K members online now
Get started with AdWords - learn the basics to get set up for success
Guide Me
star_border
Reply

Apostrophe with BMM when downloaded in Excel .csv format

Collaborator ✭ ✭ ✭
# 1
Collaborator ✭ ✭ ✭

Hello,

 

I wanted to filter some keywords that contained a word , they were in BMM and I needed them to be downloaded to move them in an ad group as negative keywords, in order to avoid competition between ad groups.

 

I noticed that whatever BMM I downloaded from the interface in the "Excel .csv" format, I have an apostrophe in front of the first +keyword1 like this

 

'+keyword1 +keyword2

'+keyword3 +keyword4

 

Now I imported them with the adwords editor as a multiple negative keyword list, but is there a way to search and replace the Apostrophe on all of them ? They are more than 400.

 

Will there be an update where the BMM will be called "Broad modified match" instead of "Broad" , because from the excel all simple broad or BMM are called the same : broad.

1 Expert replyverified_user
2 ACCEPTED SOLUTIONS

Accepted Solutions
Marked as Best Answer.
Solution
Accepted by topic author Adrian B
September 2015

Re: Apostrophe with BMM when downloaded in Excel .csv format

Google Employee
# 2
Google Employee

1. Yes, you should be able to use Replace Text tool. Just replace an apostrophe with a blank (leave Replace With text box empty).

 

In case you wonder, the reason the report generator adds those apostrophes is that Excel insists on treating a field as a formula as soon as it starts with =, + or -; then complains that "+keyword text" is not a valid formula. Now, it probably would have been wiser to add a single space in front instead: this suppresses the undesirable Excel behavior just as effectively, but doesn't affect import into Editor. This is what Editor itself does in CSV export in a similar situation. I'll bring the matter to the attention of the relevant team.

 

2. BMM is really not a separate match type, as far as AdWords system is concerned. It's just a syntactic convention for keyword text. The ad serving system has some special handling for those pluses, but the account management interface does treat them in any special way - they are characters like any other.

View solution in original post

Marked as Best Answer.
Solution
Accepted by topic author Adrian B
September 2015

Re: Apostrophe with BMM when downloaded in Excel .csv format

Google Employee
# 8
Google Employee
In Windows, the UI language is separate from regional settings like decimal separator. Your UI may be in English, but your regional settings are Romanian (I notice that the month names in your screenshots are Romanian, like "ian" and "iun", not English Jan or Jun).

In Control Panel, open Region and Language dialog and check what's in Format drop-down on Formats tab. I'm pretty sure you have Romanian there. Again, this only affects the format of numbers, dates, times and such, but not the actual language in which the UI is displayed (you can change that on Keyboards and Languages tab of the same dialog). You could try changing the Format setting to English (US); you can always change it back.

I don't believe there's any way to set up AdWords interface to display in English, but to use commas for decimal separators in reports.

View solution in original post

Marked as Best Answer.
Solution
Accepted by topic author Adrian B
September 2015

Re: Apostrophe with BMM when downloaded in Excel .csv format

Google Employee
# 2
Google Employee

1. Yes, you should be able to use Replace Text tool. Just replace an apostrophe with a blank (leave Replace With text box empty).

 

In case you wonder, the reason the report generator adds those apostrophes is that Excel insists on treating a field as a formula as soon as it starts with =, + or -; then complains that "+keyword text" is not a valid formula. Now, it probably would have been wiser to add a single space in front instead: this suppresses the undesirable Excel behavior just as effectively, but doesn't affect import into Editor. This is what Editor itself does in CSV export in a similar situation. I'll bring the matter to the attention of the relevant team.

 

2. BMM is really not a separate match type, as far as AdWords system is concerned. It's just a syntactic convention for keyword text. The ad serving system has some special handling for those pluses, but the account management interface does treat them in any special way - they are characters like any other.

Re: Apostrophe with BMM when downloaded in Excel .csv format

Collaborator ✭ ✭ ✭
# 3
Collaborator ✭ ✭ ✭

I have also seen excel showing up Dates instead of numbers in the statistics by month downloaded from the keyword planner. Maybe this can also be fixed after this "coffee break chat" with you Smiley Happy I would love to raise bids by monthly trends , sometimes traffic varies even + / - 50 % per month, and that yearly average for every keyword doesn't help us much to optimise budgets.

 

For me BMM is very different since it does not include the synonims that broad match generates. Especially since there is no database from which to find out what synonims every word can generate in a broad match.

Re: Apostrophe with BMM when downloaded in Excel .csv format

Google Employee
# 4
Google Employee
If you could show a piece of CSV text where Excel interprets a numeric field as a date, that would help. I don't remember ever seeing such behavior myself.

Re: Apostrophe with BMM when downloaded in Excel .csv format

[ Edited ]
Collaborator ✭ ✭ ✭
# 5
Collaborator ✭ ✭ ✭

This is from downloading keyword suggestions , it seems that now avg. cpc and competition, have this "wrong format " issue :

 

keyword-suggestions.png

 

and this is from the "review estimates"

 

review-estimates-download.png

 

 

There is also a problem when I use the "review estimates" for all match types, the BMM over-rides estimated values for the simple broad like bellow, as if the simple broad would not generate more search term matches since it includes synonyms.

 

 

 

 

keyword-planner-mistery.png

 

 

If we only select the simple broad for statistics, here are the values for "..... s 4g "

 

keyword-planner-mistery-2.png

Re: Apostrophe with BMM when downloaded in Excel .csv format

Google Employee
# 6
Google Employee
Interesting. My guess is, this is what happens. You set up AdWords interface for English (US), so the reports use a period '.' as decimal separator. But your Windows regional settings are configured for Romanian, which uses comma ',' as a decimal separator, and period to separate components of dates, as in 18.06.2013. So some of those numbers just happen to look similar enough to dates to confuse Excel.

I'm not sure what can be done about this on AdWords end. It would probably help if you set up matching locales in the AdWords interface and in Windows, but I assume you have good reasons not to want to do that.

About your second question, where you think you are getting wrong stats - I'm afraid I can't help you with that. My expertise is basically in moving pieces of data back and forth between an advertiser and AdWords system - I don't really know where all these numbers come from nor what they mean. You might want to ask that as a new question; hopefully someone more knowledgeable will chime in.

Re: Apostrophe with BMM when downloaded in Excel .csv format

[ Edited ]
Collaborator ✭ ✭ ✭
# 7
Collaborator ✭ ✭ ✭

When I open the file with the csv extension and try to save it it shows up as a file type "Unicode-text" , instead of CSV.

 

I have no settings in romanian on that machine, windows and office all in english, romanian translations for software localisation are often confusing and I preffer not to use them, I don't even have them installed so I tried to dig some more into this.

 

I looked at the data with the PSpad and found the problem :

 

A value like 2.21 is interpreted by Excel as february.2012 because of the dot , if I manually type 2,21 the comma makes the data appear ok as a number. Converting those cells from excel from date to number or text didn't work.

 

I guess data like 0.21 could not represent a date since day 0 does not exist and this is why excel kept it in the number format even if excel wanted to have data with commas, because if I try to add two numbers in the "dot" format , addition is not performed.

 

I will post another question for the other issue.

 

 

Marked as Best Answer.
Solution
Accepted by topic author Adrian B
September 2015

Re: Apostrophe with BMM when downloaded in Excel .csv format

Google Employee
# 8
Google Employee
In Windows, the UI language is separate from regional settings like decimal separator. Your UI may be in English, but your regional settings are Romanian (I notice that the month names in your screenshots are Romanian, like "ian" and "iun", not English Jan or Jun).

In Control Panel, open Region and Language dialog and check what's in Format drop-down on Formats tab. I'm pretty sure you have Romanian there. Again, this only affects the format of numbers, dates, times and such, but not the actual language in which the UI is displayed (you can change that on Keyboards and Languages tab of the same dialog). You could try changing the Format setting to English (US); you can always change it back.

I don't believe there's any way to set up AdWords interface to display in English, but to use commas for decimal separators in reports.

Re: Apostrophe with BMM when downloaded in Excel .csv format

Collaborator ✭ ✭ ✭
# 9
Collaborator ✭ ✭ ✭

Yes you are right, I changed it back to english (US). It will work fine from now on.

Thank you for the help .

Re: Apostrophe with BMM when downloaded in Excel .csv format

Collaborator ✭ ✭ ✭
# 10
Collaborator ✭ ✭ ✭

I'm dealing with another puzzle regarding data downloaded from the keyword planner. Competition numbers can be higher than 1 and why ?

 

I don't understand the definition :

 

"Competition: The number of advertisers that showed on each keyword relative to all keywords across Google."

 

That number is "what1" divided by "what2" ?