Keyboard shortcuts

Press ← or → to navigate between chapters

Press S or / to search in the book

Press ? to show this help

Press Esc to hide this help

What is ABAP?

ABAP (Advanced Business Application Programming) is SAP’s own programming language. SAP ECC is largely written in it, and so are the custom reports, interfaces and add-ons companies build on top of SAP. ABAP also defines how ECC stores its data, and that part is what this book reads.

Four terms

TermWhat it isIn this book
Rust structA named Rust type with a fixed set of typed fields. The compiler checks every use, so a field can’t be missing, misspelled or the wrong type.One struct per SAP table, generated from the Dictionary: sap::Kna1 has kunnr: String, erdat: Option<NaiveDate>, loevm: bool. Plus hand-written structs for what Google receives: merchant::Product, merchant_api::ProductInput.
DictionaryIn SAP, the ABAP Dictionary (DDIC): the metadata that describes every table, field, data element and type. Not to be confused with a Rust or Python “dictionary” (a key-value map, HashMap in Rust), or a JSON object.The DDIC export (DD02T, DD03L) is the input. Every struct, GraphQL type, Xano table and page is generated from it.
JSON formatterCode that turns a typed value into JSON text and back. In Rust that is Serde: #[derive(Serialize, Deserialize)] on a struct, serde_json for the JSON itself, and attributes or helper modules for any field whose JSON form differs from its Rust form.Two directions. ABAP format for ECC records: #[serde(rename = "KUNNR")] keeps SAP’s field names, and src/abap.rs formats flags as "X" / "" and dates as YYYYMMDD. Google format for the Merchant API: #[serde(rename_all = "camelCase")] writes offerId, skip_serializing_if leaves out empty fields, and prices are written as micros strings ("129000000").
Beautiful SoupA Python library that parses HTML or XML into a tree you can search by tag, class or attribute. It reads markup as it is, including imperfect markup, and writes nothing back to the source.Not used in this repo. It is the design-side counterpart of the DDIC parser: in the front-end pipeline (Webflow export → Beautiful Soup → component map → 11ty), it reads a Webflow export’s classes, data-* attributes and CMS rows into a JSON component map, as step 1 here reads DD03L into site/ddic.json. Its output is a Python dictionary (a key-value map) written out as JSON.

The same KNA1 customer in each form:

FormExample
Dictionary (DD03L row)KNA1,ERDAT,10,,ERDAT_RF,DATS,8,0,Created On
ECC record (JSON, ABAP format){"KUNNR": "0000012345", "ERDAT": "20240115", "LOEVM": ""}
Rust structKna1 { kunnr: "0000012345", erdat: Some(2024-01-15), loevm: false, … }

And a Webflow product card, read by Beautiful Soup:

FormExample
Webflow export (HTML)<div class="product-card" data-sku="HM-QUEEN-01"><h3 class="product-title">Heated mat, queen</h3></div>
Beautiful Soup (Python)card = soup.select_one(".product-card") → {"component": "product-card", "sku": card["data-sku"], "title": card.h3.get_text()}
Component map (JSON){"component": "product-card", "sku": "HM-QUEEN-01", "title": "Heated mat, queen"}

The design side and the ERP side meet on the identifier: the card’s data-sku is the material’s offerId (MARA-MATNR), so the page renders the converted record instead of a copy typed into the design.

And the same price, formatted for each side:

FormExample
ECC condition record (KONP-KBETR, KONP-KONWA)129.00, USD
Rust (merchant::Money)Money { amount: 129.00 (Decimal), currency: "USD", decimals: 2 }
Merchant API JSON (merchant_api::Price){"amountMicros": "129000000", "currencyCode": "USD"}

The ABAP Dictionary

Every table and field in ECC is described in the ABAP Dictionary (DDIC). Two of its own tables are the input to this book:

DDIC tableWhat it listsExample row
DD02TEach table and its descriptionKNA1: General Data in Customer Master
DD03LEach table’s fields: position, key flag, data element, ABAP type, length, decimalsKNA1-KUNNR: key, CHAR 10, Customer Number

Names carry ownership. SAP’s own tables have plain names (KNA1, MARA). A company’s additions start with Z or Y (ZZ_MKT_OPTIN, a custom field appended to KNA1), or sit in a registered namespace between slashes (/ACME/CONSENT). Naming shows how each becomes a Rust name.

ABAP types

ABAP typeWhat it holdsExampleRust type
CHARFixed-length text, padded with blanksNAME1 = Example Retail Inc.String
NUMCDigits stored as text; the leading zeros are part of the valueKUNNR = 0000012345String
CHAR 1 flagX is true, blank is falseLOEVM = X (deleted)bool
DATSA date as YYYYMMDD; 00000000 means noneERDAT = 20240115Option<NaiveDate>
TIMSA time as HHMMSSUTIME = 021500Option<NaiveTime>
CURR, QUAN, DECPacked decimals; a currency amount takes its decimals from the CUKY field it points toKBETR = 129.00Decimal
CLNT, LANG, CUKY, UNITClient, language key, currency key, unitSPRAS = EString
INT1 to INT8IntegersSEQNO = 1u8 to i64

ABAP has no null. An empty field holds its initial value: blanks, zero or 00000000. So the converter maps an initial value to None only where it means “not set” (a date), and keeps it as a value where it is one (a zero quantity). The full rule table is on Types.

The parsers

Three steps turn a DDIC export into Rust, JSON for Google and XanoScript for Xano. Each fails loudly on what it can’t translate, naming the field, rather than guessing.

DD02T + DD03L export (CSV)
   │
   ▼  1. DDIC parser: 11ty (web/_data/ddic.js + lib/naming.js)
site/ddic.json  ── tables, fields, Rust names, Rust types, Merchant map, consent map
   │
   ├─▶ 2a. Rust code generator: build.rs + src/codegen.rs ─▶ structs (sap::Kna1, acme::Consent …), schema.graphql, this book
   │
   ├─▶ 2b. Rust record parsers: Serde + src/abap.rs ─▶ an ECC record (JSON from RFC, IDoc or SE16) into those structs
   │
   └─▶ 3.  XanoScript generator: src/xano.rs ─▶ xano/tables/*.xs table definitions + CSV uploads

1. The DDIC parser (11ty)

web/_data/ddic.js reads fixtures/dd02t.csv and fixtures/dd03l.csv (as from SE16 or an RFC_READ_TABLE dump). lib/naming.js decides each name once: it splits a namespace (/ACME/CONSENT → namespace acme, struct Consent), makes field names valid in Rust (/ACME/GPC → acme_gpc), and picks the Rust type from the type rules. A DDIC type with no rule stops the build and names the field. The output is site/ddic.json; lib/naming.test.js tests the rules.

2a. The Rust code generator

build.rs reads site/ddic.json with Serde and writes one struct per table. Each field keeps its ABAP name for reading and writing, and says how to parse it:

/// `KUNNR` CHAR 10 (key): Customer Number
#[serde(rename = "KUNNR")]
pub kunnr: String,
/// `ERDAT` DATS 8: Created On
#[serde(rename = "ERDAT", default, with = "crate::abap::dats")]
pub erdat: Option<chrono::NaiveDate>,
/// `LOEVM` CHAR 1: Central Deletion Flag
#[serde(rename = "LOEVM", default, with = "crate::abap::xflag")]
pub loevm: bool,

Key fields are required; every other field defaults to ABAP’s initial value when it is missing. The same generator writes schema.graphql and these pages, and tests/book.rs fails if the book and the compiled code disagree.

2b. The Rust record parsers

src/abap.rs holds the Serde helpers for ABAP’s internal formats. They parse and write back, and refuse anything that isn’t a valid ABAP value:

HelperReadsWrites backRefuses
abap::xflag"X" → true; "" or " " → false"X" or ""Any other text, e.g. "Y"
abap::dats"20240115" → 2024-01-15; "00000000" or "" → NoneYYYYMMDD; None → "00000000"An impossible date
abap::tims"021500" → 02:15:00; "" → NoneHHMMSSAn impossible time
abap::timestamp20261103021500 (number or string, UTC) → a date-time; 0 → NoneThe same 14 digitsA malformed stamp

So the KNA1 record {"KUNNR":"0000012345","ERDAT":"20240115","LOEVM":"",…} parses into a sap::Kna1 with kunnr = "0000012345", erdat = Some(2024-01-15) and loevm = false, and writes back unchanged. tests/convert.rs checks that round trip, VBAK’s amount and time, the consent ledger’s newest row, and that missing keys and bad values are refused.

3. The XanoScript generator

XanoScript is Xano’s own language for tables, functions and endpoints. src/xano.rs writes one table definition per Xano table from the same map, and cargo run --bin xano-buildout writes them to xano/tables/*.xs with the CSV uploads:

table "merchant_product" {
  description = "Google Merchant Center products in the Merchant API v1 shape, flattened. No personal data."
  auth = false
  schema {
    int id { description = "Row ID" }
    text idempotency_key filters=trim { description = "SHA-256 of the row's source, subject and value: a replayed import writes nothing" }
    text offer_id filters=trim { description = "id (machine; MARA-MATNR)" }
    …
  }
  index = [ {type: "primary", field: [{name: "id"}]}, {type: "btree|unique", field: [{name: "idempotency_key", op: "asc"}]} … ]
}

The rules it applies:

  • Optional means nullable. ? on the type makes a column nullable and ? on the name makes it optional; an optional column here is always both, so a missing price is null, never 0.
  • Every row has an idempotency key, unique-indexed, so a replayed import writes nothing twice.
  • Personal columns are marked sensitive.
  • It generates, it doesn’t apply. The .xs files are written to the repo; changes to a live workspace are applied one confirmed step at a time, as in Plan: Xano buildout.

The XanoScript side generates Xano’s language rather than parsing it. The parsing happens on the ABAP side: the DDIC in step 1 and the records in step 2b.

Where it runs: the full stack

The parsers on this page are one layer of a larger stack. Design and ERP data enter on two sides, meet on the same identifiers, and land in one system of record.

 Design side                               ERP side
 Webflow (designers)                       SAP ECC: DD02T / DD03L + records (RFC, IDoc, SE16)
   │ export: HTML, CSS, CMS CSV              │
   ▼                                         ▼
 Python + Beautiful Soup                   11ty DDIC parser ─▶ site/ddic.json
   │ component map (JSON)                    │
   ▼                                         ▼
 11ty: HTML from the component map         Rust: structs, Serde record parsers, Merchant API JSON, XanoScript
   │                                         │
   └──────────────┬──────────────────────────┘
                  ▼
 Cloudflare: Workers + static assets serve the pages (petite-vue, UIkit, no compile step),
             carry consent at the edge, and run AI calls after the release gate
                  │
                  ▼
 Xano: the system of record ─ each instance in its own Docker container on Kubernetes, data in PostgreSQL
                  │
                  ▼
 Google (Merchant, Ads, BigQuery) · Shopify · other warehouses, through the release gate
LayerToolsJobStatus here
DesignWebflowDesigners keep designing; classes and CMS fields are the contractOutside this repo
Design parserPython, Beautiful SoupWebflow export → JSON component mapOutside this repo
ERP parser11ty, lib/naming.jsDD03L export → site/ddic.jsonBuilt
Typed coreRust, SerdeStructs, record parsing, Google’s JSON, XanoScriptBuilt: runs as cargo commands
Rust nested in other layersRust as a Python extension (PyO3), as WebAssembly in a Worker, or inside a Tauri / Dioxus appThe same conversion rules wherever the data is handledProposed
Edge and AICloudflare Workers, static assetsServe the pages, enforce consent before any tag, run AI calls behind the release gateThe CRM Sync Worker is live; the book is a static-assets Worker
System of recordXano on Docker and Kubernetes, PostgreSQLOne record for products, customers, consent and the ledgerLive; tables generated from this book
DestinationsGoogle Merchant, Google Ads, BigQuery, ShopifyListings, conversions, audiences, analyticsThrough the release gate; the Merchant sender is planned

Why PostgreSQL underneath matters. The record lives in PostgreSQL tables, not in a proprietary store, so its shape is ordinary relational data: tables export as CSV and back up with the workspace. How directly the database itself can be reached depends on the Xano plan. The rules (types, names, consent) live in this repo’s code, not in the database, so they move with it.

Xano’s description of its own platform: Xano technology, Xano, the scalable visual BaaS.

BigQuery is a destination in the stack above, not a system of record. Google’s BigQuery Data Transfer Service can extend the ECC data with commerce and media data, but each transfer brings consent gaps of its own, on top of the ECC consent gaps. Two transfers are covered here:

What each source is keyed by

SourceKeyGrainConsent it carries
ECC (CSV or IDoc extract)KUNNR, MATNR, VBELN (leading zeros)Header / item rows, delta batchesNone (see Consent gaps)
Shopify transferGIDs (gid://shopify/Customer/…)Nested objects landed as separate tablesEmail and SMS marketing on Customers; no browser tracking consent
YouTube Channel transferchannel_id, video_id, dateAggregates by country, age group, gender, subscribed status, traffic sourceNone needed: no viewer is identified

None of the three shares a key with another. The crosswalk (KUNNR ↔ Shopify GID ↔ consent subject, MATNR ↔ product GID ↔ video_id) belongs in Xano, next to the consent ledger, not in BigQuery.

A media segment built from YouTube data is a cohort, for example GB · 18–24 · subscribed · from YouTube search · watched video X. It is never a list of people: the channel reports have no viewer-level IDs. Consent therefore governs the other side of the join, the customer data a segment is matched or activated against.

GapWhere it shows upWhat’s missingClosest existing gap
Customer data lands without a consent filterShopify Customers, Orders, Segments, Metafields_Customer are copied on every run; the page states no PII handlingA filter or pseudonymisation at ingest, keyed to the consent ledgerPurpose
Two consents that never meetShopify carries email / SMS marketing; browser tracking consent (Customer Privacy API, Consent Mode v2) never reaches the Admin APIA join of both to one subject in the ledgerBefore there is a customer
Email consent is not ads consentA Shopify email opt-in is not ad_user_data or ad_personalizationA check of both ad signals before any Customer Match upload beside a YouTube segmentAudiences
Erasure doesn’t follow the copyNothing says a Shopify customers/redact reaches BigQuery; snapshots kept for history keep the personA purge job triggered by the redact webhooks, with a receiptData subject requests
Minors in the audienceYouTube age groups include 13–1713–17 excluded from every activation segment; reporting onlyMinor status
Region and residencyThe Shopify page states no data location; YouTube rows carry the viewer’s country, not the regimeThe dataset location chosen per regime (EU / UK data in an EU dataset; KR held until a KR-resident store exists)Region
Thresholded rowsSmall cohorts come back as country ZZ, province US-ZZ, or NULL age group, gender and traffic detailZZ / NULL kept as an explicit unknown cohort, not dropped(new)
Time that doesn’t line upYouTube days run midnight to midnight Pacific; Shopify uses the shop’s time zone; ECC uses server local timeEvery source converted to UTC before a date joinTime
Retention and backfillYouTube reports are kept 60 days; a backfill arrives as a new report ID for the same periodSnapshots in BigQuery, replaced (not appended) on backfill, with an expiry per purposeRetention

Why the gaps skew the weighting

Segments and the models built on them (bidding, recommendations, LLM retrieval) inherit the gaps:

  • Opt-in rates differ by market. YouTube aggregates count everyone who watched; the Shopify and GA4 side counts only consented shoppers. Comparing the two overstates the YouTube-to-purchase effect where opt-in is low (EU, UK).
  • Percentages are not counts. channel_demographics_a1 has only views_percentage; weight it by views from channel_basic_a3 for the same date, video and country, or a video with ten views counts as much as one with a million.
  • Demographics don’t cross. Age and gender can’t be split by traffic source or device: they are separate reports. A segment that combines them is a model, and should be labelled one.
  • The largest store dominates. One transfer per shop means a union of stores; without balancing by market, the biggest market sets the weights.
  • No product key on media. The channel reports have no SKU, so the video_id ↔ MATNR link is one more row in the Xano crosswalk.

API reference naming: Shopify and YouTube

Each source names things its own way. The Rust names follow the same rules as Naming: one module per source, UpperCamelCase structs, snake_case fields, the source’s name kept on the wire with #[serde(rename …)]. These structs are proposed; the generator covers ECC only today.

SourceSource nameBigQuery nameRust (proposed)Rule
Shopify Admin GraphQLObject Order, fields displayFinancialStatus, totalPriceSetTransfer object Ordersshopify::Order, display_financial_status#[serde(rename_all = "camelCase")] keeps GraphQL’s names on the wire
ShopifyMetafields_ProductMetafields_Productshopify::MetafieldsProductThe underscore joins owner and type; drop it in the struct name
ShopifyID gid://shopify/Order/5012345StringShopifyGid (newtype)Keep the full GID; parse the numeric tail only for joins
YouTube ChannelReport channel_basic_a3Table p_channel_basic_a3_<suffix>, view channel_basic_a3_<suffix>youtube::ChannelBasicA3p_ is the table, no prefix is the view; <suffix> is set when the transfer is created
YouTube Content OwnerReport content_owner_basic_a4p_content_owner_basic_a4_<suffix>youtube::ContentOwnerBasicA4Same pattern, content_owner_ in front
YouTube (both)Version _a1 … _a4Part of the namePart of the struct nameA new version is a new table: pin queries to views, and treat a version change as a schema change

Names to check in your own dataset, because Google’s Content Owner page lists them irregularly: the province view as content_owner_province_3_<suffix> (no a), the playlist playback-location view and the playlist traffic-source table without <suffix>.

What a Content Owner transfer adds. A channel transfer covers the brand’s own channels. A Content Owner (a YouTube partner managing many channels) also gets assets, claims, references, video metadata and the money: content_owner_estimated_revenue_a1 (estimated_partner_revenue, ad_impressions, estimated_cpm, by video_id, claimed_status, uploader_type, country_code), content_owner_ad_rates_a1, and system-managed ad, subscription (red), Shorts, payment and tax tables. Revenue is creator-side income, not shopper data, but it is finance data: it belongs next to the ledger, under the same retention rules.

One meaning, four names. The fields that join sources rarely share a name or a format:

MeaningECCShopifyYouTubeGA4 export
CountryLAND1 (SAP country key)countryCode (enum)country_code (ISO 3166-1, ZZ when thresholded)geo.country (a country name)
RegionREGIOprovinceCodeprovince_code (US- states only)geo.region (a name)
ProductMATNRVariant sku, product GIDnone (map video_id in Xano)items.item_id
OrderVBELN; Shopify order often in BSTNKOrder GID, name (#1001)noneecommerce.transaction_id
DayDATS, server local timeDateTime (UTC)Pacific dayevent_date in the property’s time zone
MoneyKBETR + KONWAshopMoney / presentmentMoneyestimated_*_revenueecommerce.purchase_revenue

Normalise once, in the Rust core: ISO 3166-1 / 3166-2 codes, UTC timestamps, ISO 4217 currency with an amount type, and one product key from the Xano crosswalk.

The funnel crosses from cohorts to people at one point: the click to the site. Above it, everything is aggregate; below it, a person can be followed only with consent.

#StageSource and fieldKey carried forwardGrainNeeded to record it per person
1ImpressionYouTube channel_reach_basic_a1.video_thumbnail_impressionsvideo_idCohortNothing: no person is recorded
2Viewchannel_basic_a3.views, engaged_views, by subscribed_status, country_codevideo_id, country_codeCohortNothing
3Click to siteLink with utm_source=youtube&utm_content=<video_id>; GA4 session_startvideo_id → sessionThe bridgeanalytics_storage granted; otherwise only a modelled conversion
4Product viewGA4 view_item, items.item_idMATNR / SKUPerson (session)analytics_storage
5Add to cart, checkoutGA4 add_to_cart, begin_checkoutSKU, sessionPersonanalytics_storage. The Shopify transfer has no cart or abandoned-checkout object, so these stages come from GA4 only
6OrderShopify Orders, OrderLineItems; GA4 purchaseOrder GID = transaction_idPersonThe order itself needs no consent (it is the contract); joining it to the session needs analytics_storage
7ERP orderECC VBAK / VBAPVBELN, Shopify order in BSTNKPersonNone, but the shopper’s consent still governs any use beyond fulfilment
8Fulfilment, return, refundShopify Fulfillments, Returns, Refunds; ECC deliveries and billingOrder, VBELNPersonNone for the transaction; the purpose limit carries over
9Re-engageGoogle Ads Customer Match beside a YouTube segmentHashed emailPersonad_user_data and ad_personalization, checked by the release gate; never email consent alone

Where the funnel breaks: if stage 3 has no consent, stages 4 to 9 can still be counted (orders are real), but not tied to the video. The YouTube-to-revenue figure is then modelled for that share, and the model should say so per market.

Globalised data governance: requirements per session and per segment

The same pages and segments serve several regimes, so the rules are decided twice: once per session, at the edge, before anything loads; and once per segment, before anything leaves BigQuery or Xano. These are the requirements the stack enforces; the legal reading of each market is for counsel to confirm.

Per session (the Cloudflare Worker, on the first request):

RequirementUS (state privacy laws)EU (GDPR, ePrivacy, DMA)UK (UK GDPR, PECR)KR (PIPA)
Default before a choiceAnalytics on; sale / share and targeted ads off if GPC is sentEverything off until opt-inEverything off until opt-inEverything off until opt-in
Tags before the banner is answeredNecessary only where GPC is sentNecessary onlyNecessary onlyNecessary only
Signals recordedGPC, sale / share opt-outanalytics_storage, ad_storage, ad_user_data, ad_personalizationSame fourSeparate consents per purpose, plus overseas transfer
Region and regimeISO 3166-2 at decision time (state matters)ISO 3166-2 at decision timeISO 3166-2 at decision timeISO 3166-2 at decision time
Where the session’s data is storedUSEUUK or EUHeld until a KR-resident store exists
Child age thresholdUnder 13 (COPPA)13 to 16, by member stateUnder 13Under 14 needs a guardian’s consent
Ledger entryAppend-only, UTC, sequence number, banner versionSameSameSame

Per segment (the release gate, before any export or activation):

RequirementRule
PurposeEach segment is labelled reporting or activation; only activation may leave for Google Ads, and only through the release gate
Minimum cohort sizeKeep YouTube’s thresholding (ZZ / NULL stay unknown) and add your own floor on segments built from your data; no segment below it is exported
Minors13–17 YouTube cohorts are reporting-only; any person under the regime’s child threshold is never in an activation segment
Consent per memberEvery person in an activation segment has ad_user_data and ad_personalization granted at export time, read live from the ledger, not from a cached copy
ResidencyThe segment is built and stored in the dataset of its regime; EU / UK people are not copied to a US dataset to be joined
Cross-border transferEU / UK to US needs a lawful transfer basis; KR to anywhere needs the shopper’s separate overseas-transfer consent
Mixed-regime segmentsA segment spanning regimes takes the strictest rule of any member’s regime, or is split by regime
Withdrawal and erasureA withdrawal or a customers/redact removes the person from every segment before the next export; the export records which version it used
RetentionEach segment has an expiry per purpose; YouTube snapshots are kept only as long as reporting needs them
ReceiptEvery export writes a receipt (segment, version, count, regime, gate decision) to the ledger, so any audience upload can be traced back

The yt_segment view

bigquery/yt_segment.sql applies the per-segment rules above to the YouTube Channel transfer. It is a draft: written against the documented report fields and not yet run against a live dataset (the checks to make first are in its header).

StepWhat it doesRule it applies
Read the viewschannel_basic_a3 and channel_demographics_a1, not the p_ tablesA backfill or version change is absorbed by the view
Normalise the mixShares of views_percentage within each date, video, country, subscribed and live / on-demand row, summing to 1Percentages are not counts
Weight by viewsEach share × views (and engaged views, watch time, subscribers gained) → est_* columnsA video with ten views doesn’t count as much as one with a million
Keep the unknownsZZ country and NULL age / gender become an explicit unknown cohort; a row with no demographics keeps all its views as oneThresholded rows are kept, not dropped
Convert the dayday_start_utc / day_end_utc from the Pacific (UTC-8) YouTube dayEvery source in UTC before a date join
Label the regimeUS, UK, EU / EEA, KR, unknown or other, from country_codeRules come from the viewer’s regime
Label the purposereporting_only for 13–17, unknown age or country, KR (on hold) and cohorts below the floor; otherwise activation_candidateMinors are never activated; activation still passes the release gate
Key the segmentsegment_key, a SHA-256 of the cohort’s dimensionsEvery export receipt can name the segment it used

Channel-level rows carry an empty video_id. The view keeps join keys as '' and turns them into NULL only in its output: a join on NULL matches nothing, and those rows would lose their demographics.

-- yt_segment: YouTube Channel cohorts, weighted by views, with governance flags.
--
-- Book: What is ABAP? → Extending the data → Segment consent gaps / Governance per segment.
-- Sources (YouTube Channel transfer, views not p_ tables, so a backfill or version change is absorbed by the view):
--   channel_basic_a3_SUFFIX         date, channel_id, video_id, live_or_on_demand, subscribed_status, country_code
--                                   + views, engaged_views, watch_time_minutes, subscribers_gained
--   channel_demographics_a1_SUFFIX  same dimensions + age_group, gender → views_percentage only
--
-- What this view is: aggregate cohorts. There are no viewer IDs in the source, so nothing here is personal data and
-- consent is not checked here. Consent applies when a segment is matched or activated against customer data, in the
-- release gate (Xano), never in BigQuery.
--
-- Rules applied (book: Governance per segment):
--   * views_percentage is weighted by views from channel_basic for the same row grain, never used raw
--   * thresholded rows (country ZZ, NULL age_group / gender) stay as an explicit 'unknown' cohort
--   * AGE_13_17 is reporting-only, always
--   * cohorts below the floor are reporting-only
--   * the YouTube day is midnight to midnight Pacific Standard Time (UTC-8, fixed), converted to UTC here
--
-- Replace before creating: PROJECT, DATASET, SUFFIX (the table suffix set on the transfer).
-- The dataset must be in the same location as the commerce data it will be joined to (EU / UK data → EU dataset).
--
--   sed -e 's/PROJECT/my-project/g' -e 's/DATASET/youtube_eu/g' -e 's/SUFFIX/brand/g' bigquery/yt_segment.sql \
--     | bq query --use_legacy_sql=false
--
-- To check on a real dataset before relying on it (not verified: no dataset was available when this was written):
--   1. The type of `date` (STRING 'YYYYMMDD' or DATE): handled both ways below, confirm with INFORMATION_SCHEMA.
--   2. Whether the transfer's views already keep only the latest report per day after a backfill. If they don't,
--      deduplicate on the full dimension set, keeping the most recent load.
--   3. The age_group / gender values (expected AGE_13_17 … AGE_65_, FEMALE / MALE / GENDER_OTHER).

CREATE OR REPLACE VIEW `PROJECT.DATASET.yt_segment`
OPTIONS (
  description = 'YouTube Channel cohorts weighted by views. Aggregate only: no viewer IDs. Activation only through the Xano release gate.'
) AS

WITH params AS (
  SELECT
    50  AS min_cohort_views,   -- own floor on top of YouTube's thresholding; tune per market
    ['AT','BE','BG','HR','CY','CZ','DK','EE','FI','FR','DE','GR','HU','IE','IT','LV','LT','LU','MT','NL',
     'PL','PT','RO','SK','SI','ES','SE','IS','LI','NO'] AS eea_countries
),

basic AS (
  SELECT
    COALESCE(SAFE.PARSE_DATE('%Y%m%d', CAST(date AS STRING)), SAFE_CAST(CAST(date AS STRING) AS DATE)) AS yt_date,
    channel_id,
    IFNULL(video_id, '')                    AS video_id,        -- '' = channel-level row; join keys stay non-NULL
    IFNULL(live_or_on_demand, '')           AS live_or_on_demand,
    IFNULL(subscribed_status, '')           AS subscribed_status,
    COALESCE(NULLIF(country_code, ''), 'ZZ') AS country_code,
    SUM(views)              AS views,
    SUM(engaged_views)      AS engaged_views,
    SUM(watch_time_minutes) AS watch_time_minutes,
    SUM(subscribers_gained) AS subscribers_gained
  FROM `PROJECT.DATASET.channel_basic_a3_SUFFIX`
  GROUP BY 1, 2, 3, 4, 5, 6
),

demo AS (
  SELECT
    COALESCE(SAFE.PARSE_DATE('%Y%m%d', CAST(date AS STRING)), SAFE_CAST(CAST(date AS STRING) AS DATE)) AS yt_date,
    channel_id,
    IFNULL(video_id, '')                    AS video_id,
    IFNULL(live_or_on_demand, '')           AS live_or_on_demand,
    IFNULL(subscribed_status, '')           AS subscribed_status,
    COALESCE(NULLIF(country_code, ''), 'ZZ') AS country_code,
    COALESCE(age_group, 'unknown')          AS age_group,       -- NULL = thresholded
    COALESCE(gender, 'unknown')             AS gender,
    SUM(views_percentage)                   AS views_percentage
  FROM `PROJECT.DATASET.channel_demographics_a1_SUFFIX`
  GROUP BY 1, 2, 3, 4, 5, 6, 7, 8
),

-- Normalise the mix within each row grain, so the shares sum to 1 whether views_percentage is 0–1 or 0–100.
demo_share AS (
  SELECT
    *,
    SAFE_DIVIDE(
      views_percentage,
      SUM(views_percentage) OVER (
        PARTITION BY yt_date, channel_id, video_id, live_or_on_demand, subscribed_status, country_code)
    ) AS share
  FROM demo
),

-- Every basic row gets a mix; a row with no demographics at all becomes one 'unknown' cohort with share 1.
weighted AS (
  SELECT
    b.yt_date,
    b.channel_id,
    b.video_id,
    b.live_or_on_demand,
    b.subscribed_status,
    b.country_code,
    COALESCE(d.age_group, 'unknown') AS age_group,
    COALESCE(d.gender, 'unknown')    AS gender,
    COALESCE(d.share, 1)             AS share,
    d.share IS NULL                  AS demographics_missing,
    b.views                * COALESCE(d.share, 1) AS est_views,
    b.engaged_views        * COALESCE(d.share, 1) AS est_engaged_views,
    b.watch_time_minutes   * COALESCE(d.share, 1) AS est_watch_time_minutes,  -- assumes watch time follows the view mix
    b.subscribers_gained   * COALESCE(d.share, 1) AS est_subscribers_gained,
    b.views                                       AS row_views
  FROM basic AS b
  LEFT JOIN demo_share AS d
    USING (yt_date, channel_id, video_id, live_or_on_demand, subscribed_status, country_code)
)

SELECT
  -- Segment identity
  TO_HEX(SHA256(CONCAT(
    channel_id, '|', IF(video_id = '', 'channel', video_id), '|', country_code, '|', age_group, '|', gender, '|',
    subscribed_status, '|', live_or_on_demand))) AS segment_key,
  w.yt_date,
  TIMESTAMP(DATETIME(w.yt_date), '-08:00')                            AS day_start_utc,  -- YouTube day, PST fixed
  TIMESTAMP(DATETIME(DATE_ADD(w.yt_date, INTERVAL 1 DAY)), '-08:00')  AS day_end_utc,
  w.channel_id,
  NULLIF(w.video_id, '') AS video_id,
  IF(w.video_id = '', 'channel', 'video') AS segment_level,
  w.country_code,
  w.age_group,
  w.gender,
  NULLIF(w.subscribed_status, '') AS subscribed_status,
  NULLIF(w.live_or_on_demand, '')  AS live_or_on_demand,

  -- Weighted metrics (estimates: demographics are a share, not a count)
  w.share,
  w.est_views,
  w.est_engaged_views,
  w.est_watch_time_minutes,
  w.est_subscribers_gained,
  w.row_views,

  -- Data quality
  w.country_code = 'ZZ'     AS country_thresholded,
  w.age_group = 'unknown'   AS demographics_thresholded,
  w.demographics_missing,

  -- Regime of the viewer's country (the regime the segment's activation rules come from)
  CASE
    WHEN w.country_code = 'US'                 THEN 'US'
    WHEN w.country_code = 'GB'                 THEN 'UK'
    WHEN w.country_code IN UNNEST(p.eea_countries) THEN 'EU'
    WHEN w.country_code = 'KR'                 THEN 'KR'
    WHEN w.country_code = 'ZZ'                 THEN 'unknown'
    ELSE 'other'
  END AS regime,

  -- Governance
  w.age_group = 'AGE_13_17' AS is_minor_cohort,
  CASE
    WHEN w.age_group = 'AGE_13_17'             THEN 'reporting_only:minor'
    WHEN w.age_group = 'unknown'               THEN 'reporting_only:unknown_age'
    WHEN w.country_code = 'ZZ'                 THEN 'reporting_only:unknown_country'
    WHEN w.country_code = 'KR'                 THEN 'reporting_only:kr_hold'   -- until a KR-resident store exists
    WHEN w.est_views < p.min_cohort_views      THEN 'reporting_only:below_floor'
    ELSE 'activation_candidate'                                                -- still needs the release gate
  END AS segment_purpose

FROM weighted AS w
CROSS JOIN params AS p;

The rule is the same as for ECC: BigQuery reads consent, it never decides it. Every segment export to Google Ads passes through the release gate in Xano, as every other destination in the stack does.

Why 11ty, and when Astro

Astro would work. 11ty is used because it is the simpler tool for what this book asks of it, and because the portability that matters sits in the Rust core, not in the site generator.

Why 11ty here

  • It is a data parser as much as a site builder. web/_data/ddic.js reads the DD03L export and writes site/ddic.json through 11ty’s data step: plain JavaScript, no component model, no bundler.
  • It ships no JavaScript of its own. The output is HTML; islands are added afterwards, after consent, by the Worker or one tag.
  • Its templates are ones teams already know. 11ty can use Liquid, the language of Shopify themes, or Nunjucks.
  • It has few moving parts. No Vite and no framework runtime, which matters for a handoff team.

Where Astro is stronger

  • Built-in islands (client:load, client:visible) and components for React, Vue or Svelte when a page needs them.
  • Typed content collections with schemas, useful when the content model grows.
  • The trade-off for this design: Astro islands are built by Vite, so they belong to an Astro build. The islands on this page load into any platform’s theme without one.

Rust works everywhere. The rules live in the Rust core, so the site generator is replaceable: 11ty and Astro would both read site/ddic.json and render it, and swapping them changes nothing about how a price, a date or a consent decision is converted.

The Rust core compiles toRuns in
A native command-line toolcargo run today: structs, Merchant API JSON, XanoScript
WebAssemblyA Cloudflare Worker, or a browser island
A Python extension (PyO3)The design pipeline, beside Beautiful Soup
A desktop or mobile appTauri or Dioxus

One rule is not in Rust yet. Naming (/ACME/GPC → acme_gpc) is decided in lib/naming.js at the 11ty step, and the Rust build only checks it. Moving naming into the Rust core is what makes “Rust everywhere” complete: then 11ty, Astro or anything else only renders.

One repository, islands per requirement

For a client handoff, the whole stack fits in one repository, one package per layer. Each page then loads only the pieces its requirements need (“islands”), instead of one framework build for everything.

client-repo/
├── design/        Webflow export + the Beautiful Soup script → component map (JSON)
├── ddic/          DD02T / DD03L export + the 11ty DDIC parser → site/ddic.json
├── core/          The Rust crate: structs, Serde record parsers, Merchant API JSON, XanoScript
├── site/          11ty: pages from the component map, data from Xano
├── worker/        The Cloudflare Worker: serves the site, consent first, loads islands, calls Xano
├── xano/          Generated .xs table definitions and CSV uploads
└── package.json   One npm install; scripts with plain names: dev:site, build:core, deploy:worker

On the page, each requirement is its own island, loaded by the Worker only where that page needs it and only after consent allows it:

RequirementIslandLoaded on
Static contentNone: 11ty HTMLEvery page
A small interactive part (a product card’s variant picker)petite-vue (about 6 KB) on that element’s v-scopePages with that component
A server round trip without client state (filters, a form)HTMX, opt-inPages that use it
UI behaviour (modal, offcanvas, tabs)UIkit components (uk-*), the design system the petite-vue islands render into, themed per brandPages with those components
MotionGSAPPages that animate
Heavy compute in the browser (pricing preview, a converter)The Rust core compiled to WebAssemblyOnly that tool’s page
Analytics and ad tagsThe tag itselfOnly after the consent decision allows it

UIkit is the design system for the Vue islands. UIkit (uk-* classes and attributes) supplies the components and the brand theme; petite-vue, the Vue layer, supplies the state and the templates. They fit without glue: petite-vue renders uk-card, uk-modal or uk-navbar markup, and UIkit’s own DOM observer initialises each component as it appears. UIkit itself is framework-independent JavaScript, so the same components also work in a page with no Vue at all, or with HTMX.

BEM modifiers are plain vanilla code. BEM (Block, Element, Modifier) names every class by what it is: a block (card), an element inside it (card__title), and a modifier for a variant or state (card--featured). UIkit follows the same idea with its prefix and hyphens: block uk-card, elements uk-card-body and uk-card-title, modifiers uk-card-primary and uk-card-small. A modifier is only a class, so changing state needs no framework:

<div class="uk-card uk-card-default uk-card-body product-card" data-sku="HM-QUEEN-01">
  <h3 class="uk-card-title product-card__title">Heated mat, queen</h3>
  <p class="product-card__price">129.00 USD</p>
</div>
/* The site's own block, in BEM, beside UIkit's: no specificity fights, because each class names one thing. */
.product-card__price { font-variant-numeric: tabular-nums; }
.product-card--out-of-stock .product-card__price { text-decoration: line-through; }
// Vanilla: a modifier is a class toggle.
const card = document.querySelector('.product-card[data-sku="HM-QUEEN-01"]')
card.classList.toggle('product-card--out-of-stock', !inStock)
card.classList.toggle('uk-card-primary', featured)
<!-- The same modifiers from a petite-vue island: state in, class out. -->
<div v-scope="{ inStock: false, featured: true }"
     class="uk-card uk-card-body product-card"
     :class="{ 'product-card--out-of-stock': !inStock, 'uk-card-primary': featured, 'uk-card-default': !featured }">

The class names are the contract between design and code: Webflow designers set them, Beautiful Soup reads them into the component map, and the stock state that drives product-card--out-of-stock is availability from the converted ECC record.

No compile step, on any live theme

The islands are plain <script> and <link> tags that bind to classes and data-* attributes already in the page. So they reach a live theme without rebuilding it, and they enhance progressively: the theme’s HTML works first, and each island improves it when it loads. A theme change that keeps the classes and data attributes keeps the islands working.

There are two ways in, and neither needs the platform’s build:

  • A Cloudflare Worker in front of the site rewrites the theme’s HTML on the way out (HTMLRewriter) and adds the islands after the consent check. The theme isn’t edited at all. This needs the site’s traffic to pass through Cloudflare.
  • One tag in the theme’s <head>, where a Worker can’t sit in front. It loads the same islands from the same place.
PlatformWhere the page’s HTML comes fromHow the islands arriveNote
ShopifyLiquid theme (theme.liquid, sections)A theme app embed or one tag in theme.liquidShopify serves its storefronts through its own edge, so the tag route is the usual one
WordPressPHP theme templatesWorker in front, or one enqueued script in the theme or a small pluginWorks the same with block and classic themes
AstroStatic or server-rendered pagesWorker in front, or one tag in the base layoutAstro’s own islands can sit beside these; nothing has to be rebuilt to add ours through the Worker
Next.jsReact, server- or statically renderedWorker in front, or one <Script> tag in the root layoutWithout the Worker, the layout tag is a one-time change and redeploy; the islands after that change without a rebuild
Adobe Experience ManagerHTL page components, client librariesWorker in front, or one tag in the page component’s headAuthors keep their components and templates
Magento / Adobe CommerceLayout XML and PHTML templatesWorker in front, or one script declared in the theme’s layout headWorks with Luma and Hyvä themes alike
Salesforce Commerce Cloud (B2C, SFRA)ISML templatesWorker in front, or one tag in the page’s htmlHead templateCartridges and the storefront code stay as they are
SAP Commerce (Hybris)JSP / tag files in the storefrontWorker in front, or one tag in the master templateThe back office and the integration to SAP are untouched

What every row shares: the islands, the consent check and the call to Xano are the same on every platform. Only where the one tag goes differs. The platform keeps rendering its theme; the islands add behaviour; the release gate in Xano decides what any of it may send.

Islands compared with a full framework build

Islands per requirementFull framework build (for example Next.js, Nuxt)
What ships to the browserThe HTML, plus each island the page usesThe framework runtime and the page’s bundle, then hydration of the page
Build step11ty renders HTML; islands need no compile step; only the Rust core is compiledThe whole site goes through the framework’s build
Who editsDesigners in Webflow; developers own the islands and the coreDevelopers, for most changes
Changing CMS or themeThe same islands load the same way under any themeThe front end is tied to the framework’s routing and data loading
ConsentThe Worker decides before any island or tag loadsServer rendering and client hydration can send data before the banner has been answered, unless that is designed in
Where it fits bestContent and commerce pages with a few interactive partsApp-like interfaces with a lot of shared client state

The rule: start with static HTML, add an island only for a requirement the HTML can’t meet, and keep every island optional, so a page still works without it. One repository keeps the islands, the Rust core and the generated Xano tables in step, and hands over with one npm install.