We want to create a pie chart that breaks out members by city. In our state there are only 2 major metropolitan areas that are fairly close to one another, Lincoln, and Omaha. The existing chart properties does allow us to create a chart that can report Lincoln, Omaha, and then the rest of state aggregated together. However, there are 2 caveats that we want to account for. First, analytics is case sensitive when considering strings, so anyone who inputs their city as LINCOLN or OMAHA is considered in the Other group. Second, there are several suburbs and outlying towns surrounding Omaha that we want to include with the Omaha number. I was hoping for a way to alter the formula for which city its designated as with some sort of CASE WHEN statement, but it appears you cannot alter formulas for chart categories. Has anyone played around with charts enough to know if this is possible?
Do you use Geo Areas? Those are defined by zip codes, which are not case sensitive, and are defined by you in a stored procedure and system table. I am not at all proficient in stored procedures, but I found it pretty easy to set up. That way you could use the Geo Areas in your pie chart instead of the city names.
Anne RobichauxCRM AdministratorThe Historic New Orleans Collection
Hi Anne, do you or anyone else reading this know if these could be configured for non-US organisations? The entries in TR_GEO_AREA in our Tess instance all look like centred around New York/Boston. Or is there any UK organisation reading this already using them?
The examples that come pre-set up are based on what I'm guessing the Met Opera used, but they can be customized to whatever you want. In the US, our postal codes are all numeric, so it was very easy for me to set it up for areas relevant to us (Greater New Orleans area, Texas, Florida, etc.) with ranges, but if you can write code to encompass the postal codes you are looking for it should work for UK.
Thanks Anne, that's great although I suspect UK postcodes are a bit sillier than US numerical... For example if you want Birmingham you can't just say 'B' postcodes as there are a tonne that don't fit the rule and actually belong to some other local authority/area. Would be totally amazing if some other UK organisation has already gone through the pain and would be willing to share! Here's hoping...
You could try DataWrapper. They have tons of maps with postal codes. It will give you the postal codes. You can even get the map to show up in Analytics.
Hi Melanie,We didn't do it with TR_GEO-AREA we kept that pretty broad, but quite a few years ago we added a few other custom tables for location, one with County data (that will be out of date now!) and one with Postal Town, where we took the beginning area of postcodes and match it to a postal town e.g. Aberdeen AB, Bath BA, Birmingham B, Blackburn BB etc. We went very broad as we didn't need the breakdown within each area at the time. I'm happy to share that, but it might be too high level for what you want, especially if we you want to dig into the different areas around Birmingham.
When I updated our TR_GEO_AREA (and more importantly LP_UPDATE_GEOAREA) it was to connect them to the Local Government Area - which has statistical significance with Government census data I found that the mapping of Postcode to LGA was easy as the Aust Bureau of Statistics had that all downloadable. So it may be possible that the matchup exists online eg: in the Government but likely the prooven model is with a gem like Caryl. If you do end up with a huge table of numbers to insert into tessitura and you are in dispare about writing it by hand excell can be a good hack. for writing and opening Case statement and hitting FILL DOWN next to the data. Reach out if you need a hand.
Thanks very much for this Neil, Caryl and Heath, good tips for me to consider! Yes we're after things that match up with LGA really as that's what we're always asked to report on.