mworks-product Migration — Schema and Naming Changes

Motionworks is consolidating its production data products into a single
BigQuery project, mworks-product, that will be the backbone of the new
API delivery channel. The migration applies a set of consistency
conventions across basecast, placecast, pathcast, popcast,
viewcast, and a new reference dataset.

This page lists every schema, table, and column change relative to the
existing client-services-delivery source layout, organized by product so
the docs team can update the corresponding product pages.

Scope and conventions

The migration is additive on the source side — client-services-delivery
tables are unchanged. All changes below describe the target side
(mworks-product.*).

The following conventions are applied throughout:

• Snapshot suffix _YYYYMMDD on every versioned product table (e.g.,
  _20260515). The unsuffixed view layer (basecast.by_date_v1) points at
  the current snapshot; the _previous view points at the prior snapshot.
• Reference tables carry a single schema-version suffix (_v1) and
  no date suffix (mworks-product.reference.*_v1) — they are populated
  with CREATE TABLE IF NOT EXISTS so re-runs are idempotent. _v1 is
  reserved for future schema-breaking changes (_v2, etc.).
• Two versioning systems coexist in mworks-product and are
  orthogonal. Product-version suffixes (_v2_2, _v2_3, _v2_4)
  apply to placecast tables, where Placecast methodology has internal
  version generations. Schema-version suffixes (_v1) apply to
  first-generation-in-mworks-product surfaces — basecast, pathcast,
  viewcast, and reference — and indicate that a future schema-breaking
  change would bump to _v2.
• yyyymm INT64 replaces (year, month) and is the partition key
  wherever the source has month grain. Annual tables keep year.
• place_id STRING globally (cast from source INT64 where required);
  same convention applies to homes_place_id and parent_place_id.
• day_type / day_part (snake_case) globally, including in pathcast
  and viewcast surfaces that previously used daytype / daypart.
• prizm_segment is STRING globally — the catalog and the
  digital-population fact were previously divergent (STRING vs INT64); both
  are now STRING.
• Geography columns are native GEOGRAPHY — geography,
  tenant_geography, path, gate_veh, and gate_ped were stored as
  Well-Known Text strings in source and are now native BigQuery GEOGRAPHY
  via SAFE.ST_GEOGFROMTEXT(...).
• Date and timestamp columns are typed — DATE, DATETIME, or
  TIMESTAMP rather than STRING or epoch INT64. Parsing happens in the
  SELECT projection of the migration CTAS.

basecast

• Dataset rename — source typo baseccast is corrected to basecast
  on the target side.
• Version mapping is off-by-one — internal _v2 / _v3 suffixes on
  the source side correspond to documented Versions 1 and 2 on the target.
• Geography vintage — by_date_v1 (2010 US Census geographies,
  US2010STCO / US2010XDMA prefixes) and by_date_v2 (2020 US Census,
  US2020STCO / US2020XDMA).
• The legacy unsuffixed baseccast.by_date source table is abandoned and
  not migrated.

Column changes

• date STRING → reporting_date DATE (parsed via
  SAFE.PARSE_DATE('%Y-%m-%d', date)).
• Source columns version / version_id are dropped from the target —
  version is encoded in the table name.
• pmt_per_trip_median is renamed to pmt_per_trip_per_day_median to
  match the median-naming convention.
• Partition switches from (year, month) to the new reporting_date DATE.

placecast

Placecast publishes three concurrent product versions in mworks-product:
v2.2 (2010 US Census geographies, full back-history), v2.3 (2020
US Census geographies, current production), and v2.4 (introduced in
this release as the next-generation methodology; column schema is
identical to v2.3). All three versions appear side-by-side in the
dataset so consumers can pin a version explicitly and migrate forward at
their own cadence.

Table renames

• The homes_annual_* family is reframed as trade-area summaries
  (homes are not the unit of measurement; they are summaries of the
  monthly trade-areas surface) — the new names align with this.
• Capitalization in first_party_data_V2_* is normalized to lowercase.
• Geography vintage split: v2.2 uses 2010 US Census geographies;
  v2.3 and v2.4 use 2020 US Census geographies.
• Year coverage on annual tables: the v2.2 Geopath-year and
  calendar-year targets carry years 2023, 2024, and 2025; the v2.3
  Geopath-year and calendar-year targets carry years 2024 and 2025. Year
  is a row column on the annual surfaces, not encoded in the table name.
• v2.4 surfaces in this release — by_date, profiles,
  validation, and validation_first_party. The trade_areas and
  trade_areas_annual_* families are not yet built for v2.4 and are
  planned for a future release; v2.4 consumers who need trade-area data
  should continue to use v2.3 trade_areas in the interim.

Placecast Profiles (v2.2 / v2.3 / v2.4) — column changes

(v2.4 column schema is identical to v2.3; the bullets below apply to
all three versions unless noted.)

• visit_observations → visits_observations (typo fix consistent with
  the rest of the visit-prefixed family).
• visits_avg_dwell is INT64 in source (doc says float); kept as INT64
  on the target — update the public doc.
• New columns certified (BOOL) and percentile (FLOAT64) are surfaced
  but not yet documented — add them to the public schema.
• place_modified_date and published_date change from STRING to
  DATE (parsed via PARSE_DATE('%Y-%m-%d', ...)).
• Every nested STRUCT (location, market, local_radius,
  percent_visits, history) and ARRAY sub-field has its own
  column-level description. Consumers who introspect the schema in
  Console / Catalog / API will now see meaningful metadata at every
  level of nesting.

Placecast by Date (v2.2 / v2.3 / v2.4) — column changes

(v2.4 column schema is identical to v2.3; the bullets below apply to
all three versions unless noted.)

• daytype → day_type (snake_case).
• year and month columns are dropped — yyyymm is the canonical
  month-grain key. Consumers can derive year / month from yyyymm with
  CAST(yyyymm/100 AS INT64) and MOD(yyyymm, 100).
• date_created and date_updated change from STRING to DATETIME.
• The doc currently mentions activities and exposures — those columns
  are not produced by the pipeline; remove them from the doc.
• place_process is added (production-fed, was undocumented).

Placecast Trade Areas (monthly, v2.2 / v2.3) — column changes

(Not yet built for v2.4 — see the table-rename section above.)

• Table name underscored: tradeareas → trade_areas.
• daytype / daypart → day_type / day_part.
• year and month columns are dropped in favor of yyyymm.
• homes_place_id carries the parent place from which the trade area
  was inherited, not the visitor's home — this corrects a long-standing
  doc inaccuracy. For focused places homes_place_id == place_id; for
  non-focused places it is the parent_place_id (often a county-tier
  region in placecast.regions). The visitor's home geography on a
  trade-area row is geography_id.

Placecast Validation (v2.2 / v2.3 / v2.4) — column changes

(v2.4 column schema is identical to v2.3; the bullets below apply to
all three versions unless noted.)

• Table renamed from qa_v2_X to validation_v2_X to match the public
  doc title "Placecast Validation."
• The public doc currently describes a current_* / previous_*
  delta-comparison schema that does not match what production
  materializes. The actual schema is the score / ksqft / place-pool form
  documented under validation_placecast_v22_bank; that content should be
  hoisted into the main placecast-validation page.
• v2.3 dropped the data_source column relative to v2.2 — document the
  drift. v2.4 inherits the v2.3 shape (no data_source).
• Score columns (visit_score, dwell_score, profile_score,
  footprint_score) carry authoritative definitions and the 0-1 scale
  in the target descriptions.

Placecast Trade Areas Annual (formerly homes_annual_*) — column changes

• Table renames as above, and across all four target tables:
  • BLOCKGROUP_FIPS (raw 12-digit FIPS in source) → geography_id
    (Motionworks-prefixed 22-char US<vintage>XXBG<fips>). The CTAS
    reconstructs the prefix via CONCAT('US2010XXBG', BLOCKGROUP_FIPS) for
    v2.2 and CONCAT('US2020XXBG', BLOCKGROUP_FIPS) for v2.3. The
    annual surface is now directly joinable to reference.geography and to
    the monthly trade_areas_v2_X.geography_id.
  • DAYTYPE / DAYPART / PCTHOMES / PLACE_ID / HOMES_PLACE_ID /
    YEAR → snake_case.
• The public doc should explicitly distinguish Geopath year
  (September of year N-1 through August of year N) from calendar year
  (January through December) — these are separate target tables, not a
  flag column.

placecast.places / placecast.regions

• place_id and parent_place_id change from INT64 to STRING (cast in
  the SELECT).
• geography and tenant_geography change from STRING (WKT) to native
  GEOGRAPHY.
• modified changes from INT64 (microseconds since epoch) to
  TIMESTAMP via TIMESTAMP_MICROS(modified).
• Schema is now fully enumerated (was SELECT *) so the doc can be
  aligned column-by-column.

placecast.customers / placecast.customers_xref

• create_ts / update_ts change from STRING (decimal seconds) to
  TIMESTAMP via TIMESTAMP_SECONDS(CAST(CAST(... AS FLOAT64) AS INT64)).
• is_active remains STRING pending confirmation of the value domain
  before forcing a BOOL conversion (Stage 2 task).

placecast.poi_mapped_places

• place_id change from INT64 to STRING.
• place_modified_date change from STRING to DATE.

placecast.placetype_age_factors

• ALL_CAPS column names → snake_case
  (IMX_PLACE_TYPE_ID → imx_place_type_id, etc.).

Planned: placecast.poi_universe_places

A poi_universe_places view exposing the deduplicated POI universe is
planned but not in the May 15 release. Consumers needing POI mapping
today should continue to use placecast.poi_mapped_places_v2_<snapshot>
(the 1:1 Motionworks-place ↔ Dataplor-POI mapping with match_type and
quality scores).

pathcast

Pathcast products measure viewsheds, not places. The migration aligns
column naming with that intent:

• place_id → viewshed_id (STRING) across every pathcast target.
• customer_place_xref → customer_viewshed_xref; inside the table,
  customer_place_id is renamed to customer_viewshed_id.
• customer_place_xref_id → customer_viewshed_xref_id.
• daytype → day_type (the public doc still spells it daytype without
  an underscore — flagged for the doc team).

Pathcast by Date / by Date Customers / by Year / by Year Customers

• place_id → viewshed_id STRING.
• year / month dropped on the monthly tables in favor of yyyymm.
• daytype → day_type.

Pathcast Path Library (paths) / paths_customers / paths_system / paths_aadt / paths_assessment

• place_id → viewshed_id STRING.
• path / gate_veh / gate_ped change from STRING (WKT) to native
  GEOGRAPHY.
• modified changes from STRING (e.g. "2026-01-26 23:46:25") to
  DATETIME.
• paths_aadt.geography and paths_aadt.{mw_aadt, client_aadt} ARRAY
  columns are now fully described at the sub-field level.
• paths_customers, paths_system, paths_aadt, paths_assessment lack
  public doc pages — these need to be authored before they become
  customer-facing API surfaces.

viewcast

viewcast.places is reframed as viewcast.viewsheds — the table holds
viewshed polygons and face attributes, not generic places.

Column changes (both surfaces)

• place_id → viewshed_id STRING.
• geography STRING (WKT) → native GEOGRAPHY (viewsheds).
• modified STRING → DATETIME (viewsheds).
• period_start / period_end STRING → DATE (profiles).

Viewcast Profiles — column descriptions at every nesting level

percent_circ, history.year/.month, geopath, market, by_day_hour,
assignments, and spots are all hardened with sub-field descriptions.

• by_day_hour.daytype → day_type (consistent with the global
  snake_case convention).

popcast

The Anytime / Cohorts surface is consolidated into a versioned pair:

• Documentation note: the doc page is titled "Placecast™ Cohorts" but the
  tables live in popcast and the website calls the product "Popcast
  Anytime." Resolve the naming inconsistency.
• anytime_v2_2 has no motionworks_segment_id (cohort-collapsed by
  design). anytime_v2_3 carries it. Document both.

popcast.anytime_v2_3 — column changes

• daytype → day_type.
• segment_id → motionworks_segment_id.
• Documented columns days, activities, visits, vintage are
  surfaced (NULL-filled) pending a pipeline change to populate them.
• vintage is typed as DATETIME (was STRING in source — parsed via
  PARSE_DATETIME('%Y-%m-%dt%H%M', ...)).

popcast.at_home — column changes

• vintage STRING → DATETIME.

popcast.us_population_digital — column changes

• prizm_segment INT64 → STRING (aligns the fact with the catalog).
• vintage stays STRING — it is a run identifier
  (YYYYMMDD_<commit-sha>), not a parseable datetime; the description is
  tightened to clarify this.

reference

The reference dataset is new. Tables are suffix-free (additive,
schema-versioned with _v1 only) and use CREATE TABLE IF NOT EXISTS
for idempotent population.

reference.geography_v1

• geography_type discriminator column identifies the tier — BG,
  STCO, MSA, MD, MCRO, ST, ZCTA, NATN.
• geography is native GEOGRAPHY.
• xx_country source has no land_area / water_area; those columns
  are NULL-filled for country (NATN) rows.

reference.motionworks_segment_v1

• Source client-services-delivery.customers.customer_segments has a
  nested STRUCT shape (customers ARRAY<STRUCT<...>> plus
  demographics STRUCT<...>). The target flattens via UNNEST so the row
  shape matches the public doc — one row per (customer, segment).
• prizm_segments changes from ARRAY<INT64> to ARRAY<STRING>
  (consistent with the catalog type).
• The doc lists language (singular); source has languages (plural,
  ARRAY<STRING>). The plural form is kept on the target — multiple
  permitted languages per segment is the real capability.

View layer

Every non-reference product table has a corresponding view in the same
dataset:

• <table>_v<N> — points at the current snapshot (_20260515) with a
  release_version = "20260515" literal column.
• <table>_v<N>_previous — points at the prior snapshot (_20260415)
  with release_version = "20260415".

Views replicate the underlying CTAS column descriptions verbatim and add a
release_version STRING column not present on the table itself.
popcast.us_population_digital_v1 is annual at vintage _20260101 and
has only a current view (no _previous).

What changes for the docs team

In priority order, the public docs need the following edits:

1. Placecast by Date — rename daytype to day_type in schema; remove
   year / month from the doc; add place_process, date_created,
   date_updated; remove non-existent activities / exposures.
2. Placecast Trade Areas — rename daytype / daypart →
   day_type / day_part; remove year / month; rewrite the
   homes_place_id description to reflect parent-place semantics.
3. Placecast Profiles — rename visit_observations →
   visits_observations; update visits_avg_dwell to INT64; add
   certified and percentile.
4. Placecast Validation — replace the current current_* /
   previous_* schema description with the production schema (score /
   ksqft / place-pool form); document the v2.2 → v2.3 drop of
   data_source.
5. Placecast Trade Areas Annual (new product family in the doc) —
   distinguish Geopath year from calendar year explicitly; document the
   geography_id block-group form.
6. Pathcast — rename place_id → viewshed_id; rename daytype →
   day_type (or hold the convention if Pathcast docs intentionally
   diverge); author public pages for paths_customers, paths_system,
   paths_aadt, paths_assessment, and by_year / by_year_customers.
7. Viewcast — rename viewcast.places to viewcast.viewsheds (the
   "Viewcast Viewshed Library" surface previously documented under
   pathcast-viewshed-library should move under viewcast).
8. Popcast Cohorts — resolve the dataset / product-naming ambiguity
   (popcast.anytime vs Placecast Cohorts doc title vs "Popcast Anytime"
   website framing). Document the v2.2 (cohort-collapsed) and v2.3
   (cohort-segmented) split.
9. Reference — add doc pages for the new reference.* tables
   (geography, motionworks_segment, and the four
   us_blockgroup_*_xref crosswalks).

The CTAS scripts in the data team's data/migration directory contain
inline DOC_UPDATE: flags adjacent to every change above, which can be
used as the working punch list.