I am interested in learning how to create the IRI markets (based on either counties or zip codes) that can be shaded using sales data so that I can link 2 or 3 data sources together inside those markets using counties.
I have read the threads and watched Joe's Tumblr, and still lost. I have created the lat/long file and have attached it. Can someone please help me get the polygon information into it?
Thank you so much for any help!
countymap-iri.csv 384.2 KB
It sounds like you have an interesting situation, would you like a new thread dedicated to your situation?
From what I gather, you have a data set at the county FIPS code level (and apparently other data sources as well), and you want to draw polygons at the "IRI market" level, aggregating those counties together, and coloring the IRI polygons with these county level data sources. This is very doable, and there are a few routes to accomplish this, if this is what you are looking for. If this is not what you are looking for, please provide additional details.
1. What is an "IRI Market", and how do you map a FIPS county code to an IRI market?
2. Are counties contained within IRI markets, or is there overlap, and if there is overlay, what business logic do you want applied to resolve ambiguous situations?
3. What is your data source type for each relevant data source (a few Excel files, or a something like a SQL Server)?
4. What capabilities do you want the resulting visualization to have, what interactions do you want enabled?
I can understand your confusion and frustration, getting Tableau to map data to polygons is not a direct process, but I believe there is a good solution to your situation.
If you would like additional assistance you can contact me via email: joemako [at] gmail [dot] com
My situation is not unique to CPG companies (or at least it shouldn't be). I am not sure what a FIPS code is so I am just going to assume it has something to do with a county and agree with that statement. Overall, you have the correct assumption, here are the answer to your questions:
1. SymphonyIRI markets are their predefined marketing regions I have these regions defined by either counties or zip codes so I can use either.
2. Each county is unique for each market. For example the Bose, ID market is Canyon, Gem, and Ada counties
3. The data we will be using would be retailer specific and IRI data. Both of these will be maintained in a database.
4. The visualization ideally would be looking at both data sources and seeing where the retailer is either over or under indexed compared to the rest of the market in those predefined regions. I would want to be able to use it not only on a mapping format, but in a table format as well.
Thank you for your help!
In the CSV you attached above, one of your fields is named "Fipscode", this is a county level FIPS code, see http://en.wikipedia.org/wiki/FIPS_county_code for more details.
Here is one option:
1. In your database, and in the tables that have the data you want charted, add a column to each table with a IRI market ID. If you cannot do this in the database table itself, there are a variety of options depending on your constraints, including either an ETL process, or a join using Tableau's Multiple Tables or Custom SQL options.
2. Create a data source that allows table to draw polygons for each of the IRI markets. Again, there is a variety of routes to accomplish this, you can use GIS software to merge county shapes together, or in an ideal situation SymphonyIRI may provide a shapefile for all boundary areas.
3. In Tableau you can connect to polygon file as the primary (blue check mark) data source, and the additional data tables in your database as separate secondary (orange check mark) data sources, and perform a data blend.
There are other routes available depending on your constraints, along with lots of possibilities and interesting viewpoints you can create. If you would like more details for your specific data structure you will need to provide sample data that represents your situation.
Thank you so much for your help on this. I have looked into pre-built shape files from the vendor and there isn't one. Can I build the QuantumGIS software to build the county shapes? Or is there a piece of software that can help me with that?
Can you provide the file that lists the county to IRI market conversion?
And how would you like to handle the counties that are not listed? (there 3000+ counties, and this file has less than 1000) I am guessing that the non listed ones are rural areas.
Those are areas not looked at by IRI (Whitespace) and they can be either labeled that way, or ignored.