Adding New Geo Areas to TR_Geo_Area?

Hi Chelsea,

Curious if you have looked at [Primary Address State] in Analytics already, as that is intended to enable filtering to just CT if you like.

Is that under the Seats & Tickets Dashboard? The data I need is total number of attended visitors (both for FY23 and last summer, 7/1-9/5 2022) from CT and then the same data for everyone else (out of state, out of country, etc.). Would using the Primary Address State be the best way of getting that data?

Sure. A dashboard filter on the CONSTITUENT field, Primary Address State, first set to include only CT and review your "Top 5 Zips by Ticket Count". Then change it to include all, but exclude only CT and review the widget again.

Or do this on the Attendance pre-built dashboard. Duplicate the dashboard to make an editable copy. Then add Primary Address State = CT to the dashboard filters. Change the Fiscal Current Year Offset from 0 to -1 to look at last year's numbers. Or disable that filter and add Performance Date or Attended Date as a filter to capture that 7/1-9/5 date range.

Just now realizing that isn't going to work to get ALL of the data. If it is constituent based, then we are going to lose more than half of our ticket count. The majority of our tickets get sold as "Gen. Pub." (General Public), which doesn't have a state or address or anything attached to it. It will only pull people who purchased tickets ahead of time, which most people do not. 

Just now realizing that isn't going to work to get ALL of the data. If it is constituent based, then we are going to lose more than half of our ticket count. The majority of our tickets get sold as "Gen. Pub." (General Public), which doesn't have a state or address or anything attached to it. It will only pull people who purchased tickets ahead of time, which most people do not. 

Hi Chelsea,

Good point. Do you capture post code by order survey question? If so:

Tessitura v15 Help System - T_DEFAULTS Entries for Tessitura Analytics (tessituranetwork.com)

And if you have more than just customer 0 as your general public constituent, then you can make this work on all such constituents with this settings:

And this is where it gets complicated. I'm currently working with our Extended Services team to build a dashboard that will work for us. What I need is a combination of everything all in one dashboard. So I need the total number of patrons from CT, whether that is via the Gen Pub Survey, pre-purchasing and using the zipcode off of their constituent record, or member card scans and pulling it from their constituent record. I need a 3 to be combined into one dashboard. So for example I need zipcode 06052 to have the total number from all 3 of those selections as its total, not 3 different sections where 06052 is listed 3 different times with 3 different responses. 

Please tell me there is an easy way to do that? And also to filter it by just CT. Because right now without the Geo_Area set up, I have to click on each zipcode starting with "06", and there are close to 500 of them. It would take me over an hour just to tick off each "06" zipcode, when I could just have a little check box for CT. And now you see where my dilemma is. 

We got there eventually with our Geo_areas, but it took a while. Using Extended Services, we had a database of zip codes and their counties imported into a system table, and a procedure that updates regularly to comb through our records and update the "this city" + "This zip" + "This state" = "this geo_area" formulas. It's been a long time since I've had to dive back into that setup, that's the basic gist.

After a lot of manual updating and some other procedures to manually update things, we've gotten our geo_areas to a usable state. It's been very helpful for us! A basic rundown of how it works (pulled from the support ticket):

1. I've populated the table TR_GEO_AREA with all the counties.  The state is also included because some states have the same county names
   • Note that id's 0, 4, and 6 are for special cases - Bad Zips, no Zips, Foreign Addresses
2. There is also a cross reference table LTR_GEO_AREA_ZIP that contains city, state, zip and which geo area from TR_GEO_AREA corresponds with the city, state, zip.  It's not just zip code because there are sometimes different counties depending on the city.
3. There is a stored procedure (LP_UPDATE_GEOAREA) that runs every time an address is updated or added that updates the geo area and sets the appropriate value based on the table LTR_GEO_AREA_ZIP
4. If you want to add a county or change the name of a county - you would change or add a row to TR_GEOAREA and then add or change the row in LTR_GEO_AREA_ZIP for that same corresponding row(s).

Short version: with a few system tables, and a set of procedures, we've been able to automate things and it's working behind the scenes.

Nathanael Pearson said:

we had a database of zip codes and their counties imported into a system table,

FYI to the thread in general, a Zip code update subscription from zip-codes.com includes counties, among other data. I regularly dip into that for Devo grant reporting and the like.

For us, we only care about counties in the state of Indiana.  So I just updated our TR_GEO_AREA table to be all 92 Indiana counties plus a few extra for Bad Address, Non-Indiana Domestic and Foreign.  But yeah, I additionally have a local table that connects the counties to zip codes which I also got from zip-codes.com and use as the basis for much of my aggregate and grant reporting.