Skip to content

Core Data Assets Changelog

Geographies

cbsa (no changes)
geography_registry

v1 > v2

  • The column geometry_projection_code has been renamed geometry_projection.

v2 > v3

In addition to the changes from v1 to v2, we have:

  • Geographies from the whole world instead of only the US.
  • Different naming conventions in the partition key geography_type_code: block_group → admin4.
  • New types of geographies not related to the usual census division for the US: CSA, CBSA, DMA, and timezone.
  • New format to represent the admin4 from admin1.admin2.admin3.fips_code to admin1.admin2.admin3.admin4 (e.g.: US.CA.037.060374082122 → US.CA.037.408212.2).
  • Extended coastlines: part of the sea has been assigned to the neighboring geographies so as to both simplify them and to be able to assign points in the sea (at least those close to the coast) to a certain country.
  • Addition of columns calculated based on GHS data, i.e. the geography population centroid coordinates (centroid_lat, centroid_lng).
  • Removal of the following columns: geometry_projection, census_year, geometry_geojson, geometry_wkb.

Please refer to the tutorial on geographies to see practical examples of what has just been mentioned.

POIs

brand
public_poi
public_poi_history
custom_poi
custom_poi_history

v1 > v2

Significative improvements have been introduced from v1 to v2. Please refer to the tutorial notebook dedicated to the POIs to deep-dive into details.
Here are listed the main points of attention:

  • A new structure of brand table and enrichment with the sic_code and sic_name columns.
  • The obsolete poi table has been dropped.
  • The 2 public tables have been renamed into poi and poi_history tables.
  • Addition of validity columns in the 2 poi_history tables (valid_from_timestamp, valid_to_timestamp) made to join with the new field place_version in the visit table.
  • Addition of columns: opening_date, closing_date, distributor_flag (the last just for Spectus POIs).

v2 > v3

  • No changes.

Device Metrics

monthly_distinct_devices
daily_distinct_devices
device_metrics
device_feature

v1 > v2

  • No changes.

v2 > v3

  • All missing tables in the v3 schema. This is a temporary solution: the first 3 tables will collapse into one single master table, namely the device_metrics.
  • The table device_feature has been dropped.

Home-work

hw_with_tag

v1 > v2

  • No changes.

v2> v3

  • The table has been renamed into device_recurring_area.
  • A new snapshot of thehw_with_tag table is computed weekly, while in device_recurring_area it is daily done.
  • The column processing_week_code has been therefore replaced by the column snapshot_event_date.
  • Addition of the columns os_name and confidence_level.
  • New partition column provider_id.
  • Improved algorithm: please refer to the dedicated tutorial to deep-dive into the matter.
  • The table now refers to the new geography table, hence a new notation for the Census block group has been adopted. See the Geography section of this very migration guide for details on how.

Device Location

device_location
device_location_uplevelled

v1 > v2

  • No changes.

v2> v3

  • Removal of the column lastseen_unixdatetime.
  • Removed the IP column from the device_location_uplevelled
  • Addition of the column os_name.
  • New partition column provider_id.
  • The value 'PERSONAL_AREA' of the classification_type field has been renamed into 'RECURRING_AREA' in the device_location_uplevelled table.
  • The table now refers to the new geography table, hence a new notation for the Census block group has been adopted. See the Geography section of this very migration guide for details on how.

Stops

stop
stop_uplevelled

v1 > v2

  • Addition of the column block_group_id.

v2 > v3

In addition to the changes from v1 to v2, we have:

  • Addition of the column os_name.
  • New partition column provider_id.
  • The value 'PERSONAL_AREA' of the classification_type field has been renamed into 'RECURRING_AREA' in the stop_uplevelled table. device_location_uplevelled table.
  • The table now refers to the new geography table, hence a new notation for the Census block group has been adopted. See the Geography section of this very migration guide for details on how.

Visits

visit

v1 > v2

  • Improved the visit algorithm being used to populate the table.
  • Addition of new columns brand_id and place_version.
  • Addition of new columns geohash and zipcode_id.
  • The start_timestamp field has been renamed into zoned_datetime.

v2> v3

In addition to the changes from v1 to v2, we have:

  • Addition of the column os_name.
  • New partition column provider_id.
  • The table now refers to the new geography table, hence a new notation for the Census block group has been adopted. See the Geography section of this very migration guide for details on how.

Socio-demo

device_user_labeling
segment_taxonomy

No changes from v1 to v2/v3.

Trips

trip

v1 > v2

  • No changes.

v2 > v3

  • The table trip has been dropped: you'll be able to fetch the same information from the trajectory table that will be released soon.