Converting City State List to Target ID's

[tom804]

So I've been given a list of locations to target in the city/state (abbreviated) .CSV format. Unfortunately bulk upload requires matching the city/state combo using a target/location ID (Which gets uploaded into the campaign). Anyone know of a way fast way to match the correct city/state combo to the correct target ID? I've tried using excel but formulas are Greek to me.

 

[Graybeard]

post a line or 2 of that csv ...
that is like asking you how many paper clips are in my tray
what are the target locations/ID about? paste a few lines to see.

 

[tom804]

Its actually two CSV files. The way Bing and Google work you can bulk upload a list of targeted locations using location codes (Basically like zips to work on a more granular level). The first CSV is from Bing which includes their list of targeted locations in there system (It's pretty big). The other csv file is the offers targeted locations.

 

[tom804]

Its actually two CSV files. The way Bing and Google work you can bulk upload a list of targeted locations using location codes (Basically like zips to work on a more granular level). The first CSV is from Bing which includes their list of targeted locations in there system (It's pretty big). The other csv file is the offers targeted locations.

So I guess the idea would be to compare both state and city on both CSV files. Then pull the data from the Location ID column. Problem is the offer CSV only list the states in abbreviated format.

 

[tom804]

If the city is on both lists

:~/temporary/tom$ offertarget="Buckeye,AZ|"; echo -n $offertarget; grep -i 'Buckeye|A' bing_geo_loaction_spreadsheet.csv

this is the output what formatting? the fields? you have to use a delimiter (column separator) other than , e.g.: | : ; -
Buckeye,AZ|43472,Buckeye|Arizona|United States,City,,Active,1013379

:~/temporary/tom$ offertarget="Buckeye-AZ,";echo -n $offertarget;grep -i 'Buckeye|A' bing_geo_loaction_spreadsheet.csv

Buckeye-AZ,43472,Buckeye|Arizona|United States,City,,Active,1013379
this works better

No actually both CSV's I posted are exactly as they were sent to me. I didny make any changes to them.

 

[Graybeard]

If the city is on both lists

:~/temporary/tom$ offertarget="Buckeye,AZ|"; echo -n $offertarget; grep -i 'Buckeye|A' bing_geo_loaction_spreadsheet.csv

this is the output what formatting? the fields? you have to use a delimiter (column separator) other than , e.g.: | : ; -
Buckeye,AZ|43472,Buckeye|Arizona|United States,City,,Active,1013379

:~/temporary/tom$ offertarget="Buckeye-AZ,";echo -n $offertarget;grep -i 'Buckeye|A' bing_geo_loaction_spreadsheet.csv

Buckeye-AZ,43472,Buckeye|Arizona|United States,City,,Active,1013379
this works better

 

[tom804]

No actually both CSV's I posted are exactly as they were sent to me. I didny make any changes to them.

The | is how Bing set it up.

 

[tom804]

No, what do you want?
I combined the term 'Buckeye-AZ' << offers +
the match >>>in the matching line of the Bing file

Buckeye-AZ,43472,Buckeye|Arizona|United States,City,,Active,1013379
offers-------,Bing data

What fields do you actually need from that line?
field1,field2,field3,field4,field5,field6

Buckeye-AZ,43472,1013379
f1,2,6 ?
Buckeye,AZ;43472;1013379
will load in a spreadsheet correctly de;limiter

No what you did is fine. Im just a biy confused by how you did it. Plus im on a mobile phone so im not seeing everything fully. What did you use to do this?

 

[tom804]

Thank you very much as well.

 

[Graybeard]

No, what do you want?
I combined the term 'Buckeye-AZ' << offers +
the match >>>in the matching line of the Bing file

Buckeye-AZ,43472,Buckeye|Arizona|United States,City,,Active,1013379
offers-------,Bing data

What fields do you actually need from that line?
field1,field2,field3,field4,field5,field6

Buckeye-AZ,43472,1013379
f1,2,6 ?
Buckeye,AZ;43472;1013379
will load in a spreadsheet correctly de;limiter

 

[tom804]

A bash on a LINUX terminal
I'll script it and run it
$ ./tom.sh

Thats amazing dude. Ive been working on learning powershell.

 

[tom804]

this is what you wanted? LOL 30 sec to run it too much time to script it
I have a massive XML/JSON to csv conversion this schema will work on -- so not time wasted ....

Apache Junction;AZ,43436,Apache Junction|Arizona|United States,City,,Active,1013373
Arlington;AZ,Black Canyon City;AZ,43480,Black Canyon City|Arizona|United States,City,,Active,
Buckeye;AZ,43472,Buckeye|Arizona|United States,City,,Active,1013379
Cave Creek;AZ,43439,Cave Creek|Arizona|United States,City,,Active,1013385
Chandler;AZ,43404,Chandler|Arizona|United States,City,,Active,1013387
Circle City;AZ,Fountain Hills;AZ,43454,Fountain Hills|Arizona|United States,City,,Active,1013408
Gila Bend;AZ,43465,Gila Bend|Arizona|United States,City,,Active,1013415
Glendale;AZ,43551,Glendale|Arizona|United States,City,,Active,1013417

open the zip then open with excel, office libre, google spreadsheets ....

Thank you. Ill give it a try once im home.

 

[Graybeard]

A bash on a LINUX terminal
I'll script it and run it
$ ./tom.sh

 

[tom804]

here cleaned up
csv and .ods opens with office libre or excel

:~/temporary/tom$ cat result.csv| rev|cut -d';' -f1-2 |rev|grep '|' |grep ';' |rev| cut -d',' -f1-7|rev >result2.csv
weird science

Thank you so much. I owe you one.

 

[tom804]

Hey thank you again so much. Yea i'm kinda of peeved at my my AM would give me a listing like that. I guess its not really any thing he controls but it worked out really well. I'm going to take a look at learning to use shell in the future for this sort of task. Never in my days of running campaigns had I seen this before so it was all like WTH? I really appreciate you helping me out. As I said before I owe you one.

 

[Graybeard]

this is what you wanted? LOL 30 sec to run it too much time to script it
I have a massive XML/JSON to csv conversion this schema will work on -- so not time wasted ....

Apache Junction;AZ,43436,Apache Junction|Arizona|United States,City,,Active,1013373
Arlington;AZ,Black Canyon City;AZ,43480,Black Canyon City|Arizona|United States,City,,Active,
Buckeye;AZ,43472,Buckeye|Arizona|United States,City,,Active,1013379
Cave Creek;AZ,43439,Cave Creek|Arizona|United States,City,,Active,1013385
Chandler;AZ,43404,Chandler|Arizona|United States,City,,Active,1013387
Circle City;AZ,Fountain Hills;AZ,43454,Fountain Hills|Arizona|United States,City,,Active,1013408
Gila Bend;AZ,43465,Gila Bend|Arizona|United States,City,,Active,1013415
Glendale;AZ,43551,Glendale|Arizona|United States,City,,Active,1013417

open the zip then open with excel, office libre, google spreadsheets ....

wc -l /home/barry/temporary/tom/result.csv
1040 /home/barry/temporary/tom/result.csv

1040 lines in 23 seconds + programming time

word manipulation robot

 

[Graybeard]

here cleaned up
csv and .ods opens with office libre or excel

:~/temporary/tom$ cat result.csv| rev|cut -d';' -f1-2 |rev|grep '|' |grep ';' |rev| cut -d',' -f1-7|rev >result2.csv
weird science

 

[Graybeard]

Attached is a 100% right (I hope) synchronized file.
The problem was you offer tag locations were 2 char state ISO codes AZ :: and Bing Ads spelled out the state "Arizona" so it was comparing green and red apples. there were about 3% mismatches in that other file.

Bourne shell - Wikipedia or a database not a task for Excel unless you have a few days.

If you use this and it works I would like to hear about it turns out. This correlation probably has other ad bidding uses.

Enjoy the freebee. I needed some distraction on a Sunday anyway.