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

Source client-services-delivery.baseccast	Target mworks-product.basecast
by_date_v2	by_date_v1_<snapshot>
by_date_v3	by_date_v2_<snapshot>

• 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

Source	Target
placecast.summaries_v2_2 / v2_3 / v2_4	placecast.profiles_v2_2_<snapshot> / v2_3_<snapshot> / v2_4_<snapshot>
placecast.tradeareas_v2_2 / v2_3	placecast.trade_areas_v2_2_<snapshot> / v2_3_<snapshot>
placecast.qa_v2_2 / v2_3 / v2_4	placecast.validation_v2_2_<snapshot> / v2_3_<snapshot> / v2_4_<snapshot>
placecast.first_party_data_V2_2 / V2_3 / V2_4	placecast.validation_first_party_v2_2_<snapshot> / v2_3_<snapshot> / v2_4_<snapshot>
placecast.by_date_v2_2 / v2_3 / v2_4	placecast.by_date_v2_2_<snapshot> / v2_3_<snapshot> / v2_4_<snapshot>
placecast.homes_annual{2023,2024,2025}_2_2 (UNION)	placecast.trade_areas_annual_geopath_year_v2_2_<snapshot>
placecast.homes_annual{2024,2025}_2_3 (UNION)	placecast.trade_areas_annual_geopath_year_v2_3_<snapshot>
placecast.homes_annual_calendar_year_{2024,2025}_2_2 (UNION)	placecast.trade_areas_annual_calendar_year_v2_2_<snapshot>
placecast.homes_annual_calendar_year_{2024,2025}_2_3 (UNION)	placecast.trade_areas_annual_calendar_year_v2_3_<snapshot>

• 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<STRUCT> 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<STRUCT> 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.

Source	Target
viewcast.places	mworks-product.viewcast.viewsheds_v1_<snapshot>
viewcast.profiles	mworks-product.viewcast.profiles_v1_<snapshot>

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:

Source	Target
delivery_geopath.anytimepop_bg	mworks-product.popcast.anytime_v2_2_<snapshot> (NEW — cohort-collapsed, paired with placecast v2.2)
popcast.anytime	mworks-product.popcast.anytime_v2_3_<snapshot> (cohort-segmented, paired with placecast v2.3)
popcast.at_home	mworks-product.popcast.at_home_v2_<snapshot>
popcast.us_population_digital	mworks-product.popcast.us_population_digital_v1_<vintage>

• 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.

Target	Source(s)
reference.geography_v1	UNION ALL of client-services-delivery.geography.{us_blockgroup, us_county, us_metro, us_metro_division, us_micro, us_state, us_zipcode, xx_country}
reference.claritas_prizm_segment_v1	client-services-delivery.popcast.catalog_claritas_prizm_segment
reference.claritas_consumer_segment_v1	client-services-delivery.popcast.catalog_claritas_segment
reference.motionworks_segment_v1	client-services-delivery.customers.customer_segments (flattened via UNNEST)
reference.us_blockgroup_dma_xref_v1	client-services-delivery.geography.us_blockgroup_dma_xref
reference.us_blockgroup_regions_xref_v1	client-services-delivery.geography.us_blockgroup_regions_xref
reference.us_blockgroup_yyyy_xref_v1	client-services-delivery.geography.us_blockgroup_yyyy_xref
reference.us_blockgroup_zip_xref_v1	client-services-delivery.geography.us_blockgroup_zip_xref

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.