Clean up zen locale information so it's easily reportable

[conoramurphy-zz]

Business Case CoderDojo locale information is very poorly stored. We need to clean this up so it's easy for the community to report on and contact people around it.

Detail Currently, regional data, as you see in the API request https://zen.coderdojo.com/api/2.0/dojos, is very messy and is only useful on the exact location and the country fields.

My current proposal of what we should store

• County in it's various formats that we currently do (no need to break anything)
• Town/city as in current format
• direct location, long lat
• state & county (to be checked if these should be one field or two)

Tasks

• [x] Decide on what data we take from google maps and save in our DB
• [x] Change the code so we now record that
• [x] Migrate all historical data to the new format
• [x] Review that this does not impact search negatively
• [x] Add that data into data studios
• [x] Add that data into mailchimp integration
• [x] Conor to test it in Data Studios

To monitor

• Number of Dojos in MC without county
• Number of Dojos in Db without county (currently, 1)

[Wardormeur]

There is an inherent problem here for updating current data :

• it means we either need to have retro-compatibility due to the leads having the location information and hence we need to update dojos and leads (which is json-based and quite the extra work)
• or we do not care about leads and only about dojos (which.. is a mediocre idea considering a dojo is just a formatted and saved lead, and both depends on the same frontend components)
• or we accept that old leads will not have the data and we will re-run the update script from time to time to update the dojos only

[Wardormeur]

First LEET PR for front-end: https://github.com/CoderDojo/cp-zen-platform/pull/1337

[conoramurphy-zz]

For me "It means we either need to have retro-compatibility due to the leads having the location information and hence we need to update dojos and leads (which is json-based and quite the extra work)" is the only option that looks sensible above. Would the frontend not break if Leads and Dojos aren't using the same info?

[Wardormeur]

Well, it's code, everything breaks at some point and needs fixing. Now, for sure i'd prefer to avoid that scenario as it branches the code even more and makes this task to be less efficient as it provides the data only at one point, but that's another alternative than the one I started.

[Wardormeur]

Geocoding works by "level" of administration, where we use the first level as a state, and the second level as a county. Considering country>state>county as a hierarchical representation of a location, and in case (like Dublin) where no state is returned, should we map the first level of hierarchy to the county, or the state ? @conoramurphy

[conoramurphy-zz]

To me I would map Dublin && Alaska, for example, both to the second level. I might be missing some negative implications of that if you can think of any?

Do you think both would map to region here https://support.google.com/datastudio/answer/7065037?hl=en?

[Wardormeur]

None, apart than accepting that state can be null. For example, Germany doesn't have states, nor county, but only Provinces, which mean we can either map them to a county or a state. At the same time, google returns sometimes returns the city as the 2nd level of administration. Hopefully that would be consistent in the same region : as long as grouping is possible in a equivalent area, i think that both solutions are sort of fine (apart from losing the semantic of what a state really is..) Accepting that state can be null means however that the default value becomes county as being the always filled info. For stats, it will require a "case" scenario to backfill state with the value of county. Regarding the "Region" thing: for US, state would be used. For other countries, I don't know.

Edit : I think we should accept that state can be null to keep the semantic value. If Google returns the 2 values, then be it and map them to state and county (even if it's the city). If there is only one value, map it to the nearest level to the city which is county. The analytics aspect would be handled by the warehouse migration or GDS if necessary Agreed ?

[conoramurphy-zz]

Ya that makes sense. We can transform the data if needed for GDS. Seperate concerns.

[Wardormeur]

Another issue I bring here is that not all dojos have proper coordinates, which is the easiest way to get information on the location. Solution 1 is to manually solve those only for verified (65) dojos Solution 2 is to fallback to another call to find from the placeName, but there's not guarantee it'll work as the front-end didn't find it in the first place.

[Wardormeur]

Data has been backported. New dojos will have this data as well Will work now on the port to the warehouse

[Wardormeur]

Added to MC. I think we're good ?

[conoramurphy-zz]

There's a few GB countries with no state (england etc.) in mailchimp. Is that meant?

[Wardormeur]

Ok, I'm back onto it..

[Wardormeur]

76 of those subscribed still don't have a county. My understanding of it is that they'll be updated at next dump.

[conoramurphy-zz]

Cool. I'd say close and move to omintoring and we can do a final check next week. Thanks a million!

[Wardormeur]

Added monitoring