The Most Active and Friendliest
Affiliate Marketing Community Online!

“Propeller”/  MyBid

Dynamic Insertion Question - Does city or state work better?

Ryan Bartman

New Member
affiliate
When it comes to dynamic insertion of a city or state in a headline, what have you had the most success with? Using city or the state? Looking forward to hearing about it. Thanks as always!
 
added**
IP Address API and Data Solutions - geolocation, company, carrier info, type and more - IPinfo IP Address Geolocation API

then there is ipinfo
barry@-DS-7:~$ ./ipinfo.sh
Pls enter your ip:
3.127.255.25
{
"ip": "3.127.255.25",
"hostname": "ec2-3-127-255-25.eu-central-1.compute.amazonaws.com",
"city": "Frankfurt",
"region": "Hessen",
"country": "DE",
"loc": "50.1167,8.6833",
"org": "AS16509 Amazon.com, Inc."
}
Code:
#!/bin/bash                                                                                          
#ipinfo.sh                                                                                          
                                                                                                    
echo "Pls enter your ip:"                                                                            
read ip                                                                                              
curl "https://ipinfo.io/$ip"                                                                          exit
\\\\\\\\\\\\\\\\\
This may work How to get city name from latitude and longitude coordinates in Google Maps?
if you can relate (server-side script) the ip address to the lat,lon
then get the city name.
Be forewarned; many IP's are in 1000 mile radii so they are not accurate in the free Geo-City-Lite
many are mobile proxy servers ...

Database
Country City
Application Segment your visitors by country When more granular data is needed
Continent
Country
Regional Subdivision
City
Postal Code
Approximate Latitude/Longitude*

Update Frequency Weekly Weekly
Site License Fee (Includes one Month of updates) $50 $370
Monthly Billing $24 per month $100 per month
Annual Billing $314 for the first year and $288 for each year thereafter. $1470 for the first year and $1200 for each year thereafter.
**this is the way it is commonly done--larger web businesses buy the MaxMind for credt card processing to verify identity of location ...
More Info

Geo-City-Light <<<free
network,geoname_id,
registered_country_geoname_id,
represented_country_geoname_id,
is_anonymous_proxy,
is_satellite_provider,
postal_code,
latitude,
longitude,
accuracy_radius

________________________________
My scope is larger that just Just 'meet girls in __insert your town'

I am currently creating US Zip code,IP,and state,county,city and census tract databases
using multiple sources.
with a little help from my 'friends' MySQL query to find zip-codes within given radius from any point (or zip-code)
Calculate distance and bearing between two Latitude/Longitude points using haversine formula in JavaScript
aka: Mysql haversine query
first
mysql> select zip, lat, lng from zip_radius where zip=60604;
+-------+-------------+--------------+
| zip | lat | lng |
+-------+-------------+--------------+
| 60604 | 41.87850000 | -87.63300000 |
+-------+-------------+--------------+
1 row in set (0.01 sec)

this calculation formula is well known
SET @location_lng60604 =(select lng from zip_radius where zip=60604);
SET @location_lat0604 =(select lat from zip_radius where zip=60604);

mysql>
SELECT zip,
ROUND( ( 3959 * acos( cos( radians(@location_lat60604) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(@location_lng60604) ) + sin( radians(@location_lat60604) ) * sin( radians( lat ) ) ) ) ,5)
AS distance FROM zip_radius
HAVING distance < 2 ORDER BY distance ASC;
2 mi radius :D
+-------+----------+
| zip | distance miles|
+-------+----------+
| 60604 | 0.00000 |
| 60603 | 0.24833 |
| 60661 | 0.55211 |
| 60605 | 0.56733 |
| 60606 | 0.64180 |
| 60601 | 0.91760 |
| 60654 | 0.97887 |
| 60607 | 1.35042 |
| 60611 | 1.39812 |
| 60610 | 1.71390 |
| 60642 | 1.84705 |
+-------+----------+
11 rows in set (0.06 sec)

==========
need to LEFT JOIN from the second table

mysql> select * from zip_names where zipcode_nb LIKE '606%' ORDER BY zipcode_nb asc LIMIT 10;
+------------+-----------+--------------------------+------------+---------+
| zipcode_nb | state_iso | metro_area | countyName | city |
+------------+-----------+--------------------------+------------+---------+
| 60601 | IL | Chicago-Naperville-Elgin | Cook | Chicago |
| 60602 | IL | Chicago-Naperville-Elgin | Cook | Chicago |
| 60603 | IL | Chicago-Naperville-Elgin | Cook | Chicago |
| 60604 | IL | Chicago-Naperville-Elgin | Cook | Chicago |
| 60605 | IL | Chicago-Naperville-Elgin | Cook | Chicago |
| 60606 | IL | Chicago-Naperville-Elgin | Cook | Chicago |
| 60607 | IL | Chicago-Naperville-Elgin | Cook | Chicago |
| 60608 | IL | Chicago-Naperville-Elgin | Cook | Chicago |
| 60609 | IL | Chicago-Naperville-Elgin | Cook | Chicago |
| 60610 | IL | Chicago-Naperville-Elgin | Cook | Chicago |
+------------+-----------+--------------------------+------------+---------+
10 rows in set (0.01 sec)

exact:

mysql> select * from zip_names where zipcode_nb = 60642;
+------------+-----------+--------------------------+------------+---------+
| zipcode_nb | state_iso | metro_area | countyName | city |
+------------+-----------+--------------------------+------------+---------+
| 60642 | IL | Chicago-Naperville-Elgin | Cook | Chicago |
+------------+-----------+--------------------------+------------+---------+
1 row in set, 1 warning (0.02 sec)

mysql>now wasn't that easy ... :eek::oops:o_O:rolleyes:

to do !
mysql> select zip, tract from zip_tract_crosswalk where zip =60642;
+-------+-------------+
| zip | tract |
+-------+-------------+
| 60642 | 17031242000 |
| 60642 | 17031243500 |
| 60642 | 17031243400 |
| 60642 | 17031842200 |
| 60642 | 17031832300 |
| 60642 | 17031241600 |
| 60642 | 17031243300 |
| 60642 | 17031832600 |
| 60642 | 17031842300 |
+-------+-------------+
9 rows in set (0.07 sec)

mysql> select * from zip_income_ranks where zip=60642;
+-------+-------+-------------+
| state | zip | Income Rank |
+-------+-------+-------------+
| IL | 60642 | 387 |
+-------+-------+-------------+
1 row in set (0.02 sec)

mysql> select AVG(`Income Rank`) "averageIncomeRank" from zip_income_ranks where zip LIKE '606%';
+-------------------+
| averageIncomeRank |
+-------------------+
| 284.3385 |
+-------------------+
1 row in set (0.02 sec)

;)
 
Last edited:
I sort of missed the point of your question, my bad.
State is really meaningless demographically -- only geographically.
Near me (possibly like me) City. Like me State or Nation. >>>
the long answer is complex (understatement) see:

mysql> select ZIP,
(`Income Rank`) "LowestIncomeRank-CA" from zip_income_ranks
WHERE state = 'CA' and `Income Rank` >100
ORDER BY `Income Rank` asc LIMIT 1;
+-------+---------------------+
| ZIP | LowestIncomeRank-CA |
+-------+---------------------+
| 93261 | 101 |
+-------+---------------------+
1 row in set (0.03 sec)

mysql> select ZIP,
(`Income Rank`) "HighestIncomeRank-CA" from zip_income_ranks
WHERE state = 'CA'
ORDER BY `Income Rank` desc LIMIT 1; +-------+----------------------+
| ZIP | HighestIncomeRank-CA |
+-------+----------------------+
| 94304 | 875 |
+-------+----------------------+
1 row in set (0.03 sec)

mysql> select ZIP,
(`Income Rank`) "LowestIncomeRank-NY" from zip_income_ranks
WHERE state = 'NY' and `Income Rank` >100
ORDER BY `Income Rank` asc LIMIT 1;
+-------+---------------------+
| ZIP | LowestIncomeRank-NY |
+-------+---------------------+
| 12492 | 101 |
+-------+---------------------+
1 row in set (0.03 sec)

mysql> select ZIP,
(`Income Rank`) "HighestIncomeRank-NY" from zip_income_ranks
WHERE state = 'NY'
ORDER BY `Income Rank` desc LIMIT 1; +-------+----------------------+
| ZIP | HighestIncomeRank-NY |
+-------+----------------------+
| 10282 | 989 |
+-------+----------------------+
1 row in set (0.03 sec)

mysql> select ZIP,
(`Income Rank`) "HighestIncomeRank-MO" from zip_income_ranks
WHERE state = 'MO'
ORDER BY `Income Rank` desc LIMIT 1;
+-------+----------------------+
| ZIP | HighestIncomeRank-MO |
+-------+----------------------+
| 64113 | 534 |
+-------+----------------------+
1 row in set (0.02 sec)

mysql> select ZIP,
(`Income Rank`) "LowestIncomeRank-MO" from zip_income_ranks
WHERE state = 'MO' and `Income Rank` >100
ORDER BY `Income Rank` asc LIMIT 1;
+-------+---------------------+
| ZIP | LowestIncomeRank-MO |
+-------+---------------------+
| 63106 | 106 |
+-------+---------------------+
1 row in set (0.02 sec)

mysql> select ZIP,
(`Income Rank`) "LowestIncomeRank-CO" from zip_income_ranks
WHERE state = 'CO' and `Income Rank` >100
ORDER BY `Income Rank` asc LIMIT 1;
+-------+---------------------+
| ZIP | LowestIncomeRank-CO |
+-------+---------------------+
| 81152 | 138 |
+-------+---------------------+
1 row in set (0.03 sec)

mysql> select ZIP,
(`Income Rank`) "HighestIncomeRank-CO" from zip_income_ranks
WHERE state = 'CO'
ORDER BY `Income Rank` desc LIMIT 1;
+-------+----------------------+
| ZIP | HighestIncomeRank-CO |
+-------+----------------------+
| 80238 | 633 |
+-------+----------------------+
1 row in set (0.02 sec)

The only similarity is a wide variation!

Even by city, even more distinct by shades of variation by zip code, there are many variations. What New York City means to a person depends on their circumstances --and not the city's characteristics.

In the NYC area has meaning geographically more that it does in NY State (perhaps).

So what are we talkin' 'bout places or people?

I just found data published enumerating and segmenting homeless populations by MSA (Metropolitan Statistical Areas ) and by State. There there might be a fair comparison state v. state normalized by population and city v. city normalized on the same basis. But the homeless are the homeless people regardless of where they are -- they are homeless for the same reasons barring as the result of some natural disaster <<< that is a geographical factor, and a temporary one (hopefully)

So psychological city would have greater affinity with personal unity and infer a shorter distance --Locally. State implies segmentation where National has more global meaning e.g.; nationalism or nationalistic.

Near me City. Like me State or Nation.

I am developing micro-GEO advertising and marketing -- and have been working on this data (maybe over-absorbed in it) for 6 weeks now.
 
Testing is a binary answer.
no works or works = 0 | 1

You missed the to whom part also. That is where Facebook excels to a degree --segmentation of ad media.


Subjectively, the state is used a lot in native ads that I see -- I think it is probably the blind leading the blind, ad spy tools :D But then, I am assuming subjectively
--but that is another discussion assumptive v. empirical positioning ;)
 
MI
Back