12-16-2015, 09:38 AM
Thank you for sharing this. I was considering doing something very similar with some Amazon affiliate sites I am working on, but was hesitant to do so because I wasn't sure if it would actually bring in much traffic or not. It's good to hear that the method has worked for you.
I'll add a few suggestions, if you're building your site in Wordpress and trying to do something like this, since I encountered some unexpected problems while trying to make thousands of pages in bulk in WP.
First off, if you are on a basic shared hosting plan with a company such as Bluehost, do not attempt to bulk upload thousands of pages all at once. You'll have to break them up into smaller batches and do one batch at a time. Otherwise Wordpress will just crap out on you and only partially create the list of pages/posts in the bulk sheet. What's even worse is it's a mess trying to clean all that up and start over, so back up your site before attempting this. I've found that I can't do more than 1000 posts/pages at a time on my basic hosting plan with Bluehost.
Also, you will want to plan out carefully ahead of time as to how you want your posts catgegorized. It's a nightmare trying to move them all into different categories within the Wordpress interface after the fact. And depending on which bulk uploading plugin you are using, you may need to bulk create all the categories first, then create your posts.
Speaking of posts vs. pages, for whatever reason, posts seem to be more efficient in terms of page load times. So I would suggest adding all of these as posts instead of pages. Also, you may need to change your permalink structure to one of the first three options instead of that last one - since it's much more resource intense. I've noticed that once I did get the 20,000 or so city posts all into my site, the page load times were horrendous. Uploading them as posts and using the third permalink option instead helped out a bit.
You should also consider how you're going to update all these pages down the road, should you need to do so (such as tweaking the copy on them). In particular, if you were using the last permalink option, some bulk uploading plugins will instead create new pages instead of updating the existing ones. If you try to delete all the previous pages, make sure you empty out your trash, otherwise the new pages may just become duplicates instead of replacing the old ones. For example, if you deleted a page/post with the URL of "http://www.yoursitehere.com/flat-screen-tvs/visio-tvs" and it's still in your trash bin, when you try to create a new page with that same title, it will just append the Wordpress slug with a "-2" or "-3" so your new page will have a URL such as "http://www.yoursitehere.com/flat-screen-tvs/visio-tvs-2". The reason being, the original page is still in the trash bin, and theoretically could be restored, unless it's permanently deleted. So if you're trying to delete and create new posts in bulk, you can wind up with tons of broken backlinks and internal links that were trying to point to the old page instead of the new one with the different slug.
I actually scraped all the US city and state data from city-data.com a few months ago using the SEOTools plugin for Excel. I'll see if I can find the spreadsheet, and if so, I'll share it here.
Here it is, a list of all the states, cities, and zip codes in the US, scraped from City-Data.com.
http://www73.zippyshare.com/v/rMmuuFN3/file.html
https://www.virustotal.com/en/file/e9e5f...450223093/
I'll add a few suggestions, if you're building your site in Wordpress and trying to do something like this, since I encountered some unexpected problems while trying to make thousands of pages in bulk in WP.
First off, if you are on a basic shared hosting plan with a company such as Bluehost, do not attempt to bulk upload thousands of pages all at once. You'll have to break them up into smaller batches and do one batch at a time. Otherwise Wordpress will just crap out on you and only partially create the list of pages/posts in the bulk sheet. What's even worse is it's a mess trying to clean all that up and start over, so back up your site before attempting this. I've found that I can't do more than 1000 posts/pages at a time on my basic hosting plan with Bluehost.
Also, you will want to plan out carefully ahead of time as to how you want your posts catgegorized. It's a nightmare trying to move them all into different categories within the Wordpress interface after the fact. And depending on which bulk uploading plugin you are using, you may need to bulk create all the categories first, then create your posts.
Speaking of posts vs. pages, for whatever reason, posts seem to be more efficient in terms of page load times. So I would suggest adding all of these as posts instead of pages. Also, you may need to change your permalink structure to one of the first three options instead of that last one - since it's much more resource intense. I've noticed that once I did get the 20,000 or so city posts all into my site, the page load times were horrendous. Uploading them as posts and using the third permalink option instead helped out a bit.
You should also consider how you're going to update all these pages down the road, should you need to do so (such as tweaking the copy on them). In particular, if you were using the last permalink option, some bulk uploading plugins will instead create new pages instead of updating the existing ones. If you try to delete all the previous pages, make sure you empty out your trash, otherwise the new pages may just become duplicates instead of replacing the old ones. For example, if you deleted a page/post with the URL of "http://www.yoursitehere.com/flat-screen-tvs/visio-tvs" and it's still in your trash bin, when you try to create a new page with that same title, it will just append the Wordpress slug with a "-2" or "-3" so your new page will have a URL such as "http://www.yoursitehere.com/flat-screen-tvs/visio-tvs-2". The reason being, the original page is still in the trash bin, and theoretically could be restored, unless it's permanently deleted. So if you're trying to delete and create new posts in bulk, you can wind up with tons of broken backlinks and internal links that were trying to point to the old page instead of the new one with the different slug.
I actually scraped all the US city and state data from city-data.com a few months ago using the SEOTools plugin for Excel. I'll see if I can find the spreadsheet, and if so, I'll share it here.
Here it is, a list of all the states, cities, and zip codes in the US, scraped from City-Data.com.
http://www73.zippyshare.com/v/rMmuuFN3/file.html
https://www.virustotal.com/en/file/e9e5f...450223093/
![[Image: ow1uSRT.png]](http://i.imgur.com/ow1uSRT.png)