Zip Code Histogram Report - Available
We receive many requests for a "zip code report". Here is the SQL for a custom report to do just that. I'll probably stick this report in a future build, but for now here it is...
Go to Data Entry -> View -> Reports -> Custom Report and make a new report entitled "Zip Code Histogram" and paste in the following SQL.
NOTE: the report only includes zips that have at least 10 occurrences to keep the result set small. It also uses the first city name as entered by the customer for a zip code, thus if there happens to be a typo in the city name... that's where it came from. And if a zipcode is blank, it is excluded from the total
SQL BELOW
--------------------------------
SELECT LEFT(zip,5) as zip, city, state, COUNT(*) AS total_count from customers group by zip having zip<>"" and total_count>10 order by total_count desc
Comments
If you want JUST active members.... here is the SQL
SELECT LEFT(zip,5) as zip, city, state, COUNT(*) AS total_count from customers where CURRENT_STATUS='OK' and CUSTOMER_TYPE='MEMBER' group by zip having zip<>"" and total_count>10 order by total_count desc
Andy - if I want to run this report, but within a date range, what code to I add for that? I see {STARTDATE} and {ENDDATE} in the edit field, but I'm not sure what other syntax needs to be used to restrict the query to those dates. Thank you for your help.
There is now a standard zip/postal code report on the report tab, thus you may not need to use the custom SQL any more. And that report allows you to select the dates.
Thank you. I got the date range working, but I'm wondering if you can help me limit the search to customers who actually checked in during that time period. Could you share the SQL for basically "Checked in = true"?
Include the following in the where clause
and LAST_VISIT_DATE between '{STARTDATE}' and '{ENDDATE}'
Awesome - I think this is what I needed. Thank you so much. IS that easily modifiable to show how many visits between the date range? VISIT_COUNT or that sort of thing? I appreciate the help very much thank you.
That starts to get tricky unfortunately. It is possible, but it goes beyond the scope of basic help to get that deep into SQL because it combines other tables.
Thanks Andy - is it possible that you could provide a reference to the tables and columns in the database? This might be out of the scope of my SQL skills, but if I had a reference to the tables and columns I could at least sandbox a bit to see if I can work it out. That would be very helpful. Thank you very much for your help.
Wm
There are over 100 related tables and probably 2500+ fields, a reference guide is WAY out of the scope. Sorry.
I recommend you download Navicat for MySQL and explore the tables. Just be absolutely sure to NOT write or change any values or you will cause some serious grief that is probably unrecoverable.
The best bet is to create/use a read-only user when accessing the database directly - though you'll need to figure out how to do that on your own.
http://www.navicat.com/products/navicat-for-mysql
This should help. Thank you. I'll try this.
Hey Andy
let me try to explain what we are trying to do to better understand.
we are wanting to do a specific email Blast to certain zip codes, and wondering if we can get a list of emails associated with zip codes.
i noticed we have city, state, and country: but not zip
Thanks
View -> Customer Queries... and filter on specific zips.
Please sign in to leave a comment.