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
| Term | What it is | In this book |
|---|---|---|
| Rust struct | A 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. |
| Dictionary | In 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 formatter | Code 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 Soup | A 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:
| Form | Example |
|---|---|
| Dictionary (DD03L row) | KNA1,ERDAT,10,,ERDAT_RF,DATS,8,0,Created On |
| ECC record (JSON, ABAP format) | {"KUNNR": "0000012345", "ERDAT": "20240115", "LOEVM": ""} |
| Rust struct | Kna1 { kunnr: "0000012345", erdat: Some(2024-01-15), loevm: false, … } |
And a Webflow product card, read by Beautiful Soup:
| Form | Example |
|---|---|
| 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:
| Form | Example |
|---|---|
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 table | What it lists | Example row |
|---|---|---|
DD02T | Each table and its description | KNA1: General Data in Customer Master |
DD03L | Each table’s fields: position, key flag, data element, ABAP type, length, decimals | KNA1-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 type | What it holds | Example | Rust type |
|---|---|---|---|
CHAR | Fixed-length text, padded with blanks | NAME1 = Example Retail Inc. | String |
NUMC | Digits stored as text; the leading zeros are part of the value | KUNNR = 0000012345 | String |
CHAR 1 flag | X is true, blank is false | LOEVM = X (deleted) | bool |
DATS | A date as YYYYMMDD; 00000000 means none | ERDAT = 20240115 | Option<NaiveDate> |
TIMS | A time as HHMMSS | UTIME = 021500 | Option<NaiveTime> |
CURR, QUAN, DEC | Packed decimals; a currency amount takes its decimals from the CUKY field it points to | KBETR = 129.00 | Decimal |
CLNT, LANG, CUKY, UNIT | Client, language key, currency key, unit | SPRAS = E | String |
INT1 to INT8 | Integers | SEQNO = 1 | u8 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:
| Helper | Reads | Writes back | Refuses |
|---|---|---|---|
abap::xflag | "X" → true; "" or " " → false | "X" or "" | Any other text, e.g. "Y" |
abap::dats | "20240115" → 2024-01-15; "00000000" or "" → None | YYYYMMDD; None → "00000000" | An impossible date |
abap::tims | "021500" → 02:15:00; "" → None | HHMMSS | An impossible time |
abap::timestamp | 20261103021500 (number or string, UTC) → a date-time; 0 → None | The same 14 digits | A 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
.xsfiles 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
| Layer | Tools | Job | Status here |
|---|---|---|---|
| Design | Webflow | Designers keep designing; classes and CMS fields are the contract | Outside this repo |
| Design parser | Python, Beautiful Soup | Webflow export → JSON component map | Outside this repo |
| ERP parser | 11ty, lib/naming.js | DD03L export → site/ddic.json | Built |
| Typed core | Rust, Serde | Structs, record parsing, Google’s JSON, XanoScript | Built: runs as cargo commands |
| Rust nested in other layers | Rust as a Python extension (PyO3), as WebAssembly in a Worker, or inside a Tauri / Dioxus app | The same conversion rules wherever the data is handled | Proposed |
| Edge and AI | Cloudflare Workers, static assets | Serve the pages, enforce consent before any tag, run AI calls behind the release gate | The CRM Sync Worker is live; the book is a static-assets Worker |
| System of record | Xano on Docker and Kubernetes, PostgreSQL | One record for products, customers, consent and the ledger | Live; tables generated from this book |
| Destinations | Google Merchant, Google Ads, BigQuery, Shopify | Listings, conversions, audiences, analytics | Through 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.
Extending the data: BigQuery transfers and segment consent gaps
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:
- The Shopify transfer, in Preview: 57 Admin GraphQL objects, one shop per transfer, through a custom Shopify app.
- The YouTube Channel transfer: 20 channel,
playlist and reach reports, each landed as a
p_table plus a view. The fields are in the YouTube Reporting API reference.
What each source is keyed by
| Source | Key | Grain | Consent it carries |
|---|---|---|---|
| ECC (CSV or IDoc extract) | KUNNR, MATNR, VBELN (leading zeros) | Header / item rows, delta batches | None (see Consent gaps) |
| Shopify transfer | GIDs (gid://shopify/Customer/…) | Nested objects landed as separate tables | Email and SMS marketing on Customers; no browser tracking consent |
| YouTube Channel transfer | channel_id, video_id, date | Aggregates by country, age group, gender, subscribed status, traffic source | None 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.
Segment consent gaps
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.
| Gap | Where it shows up | What’s missing | Closest existing gap |
|---|---|---|---|
| Customer data lands without a consent filter | Shopify Customers, Orders, Segments, Metafields_Customer are copied on every run; the page states no PII handling | A filter or pseudonymisation at ingest, keyed to the consent ledger | Purpose |
| Two consents that never meet | Shopify carries email / SMS marketing; browser tracking consent (Customer Privacy API, Consent Mode v2) never reaches the Admin API | A join of both to one subject in the ledger | Before there is a customer |
| Email consent is not ads consent | A Shopify email opt-in is not ad_user_data or ad_personalization | A check of both ad signals before any Customer Match upload beside a YouTube segment | Audiences |
| Erasure doesn’t follow the copy | Nothing says a Shopify customers/redact reaches BigQuery; snapshots kept for history keep the person | A purge job triggered by the redact webhooks, with a receipt | Data subject requests |
| Minors in the audience | YouTube age groups include 13–17 | 13–17 excluded from every activation segment; reporting only | Minor status |
| Region and residency | The Shopify page states no data location; YouTube rows carry the viewer’s country, not the regime | The dataset location chosen per regime (EU / UK data in an EU dataset; KR held until a KR-resident store exists) | Region |
| Thresholded rows | Small cohorts come back as country ZZ, province US-ZZ, or NULL age group, gender and traffic detail | ZZ / NULL kept as an explicit unknown cohort, not dropped | (new) |
| Time that doesn’t line up | YouTube days run midnight to midnight Pacific; Shopify uses the shop’s time zone; ECC uses server local time | Every source converted to UTC before a date join | Time |
| Retention and backfill | YouTube reports are kept 60 days; a backfill arrives as a new report ID for the same period | Snapshots in BigQuery, replaced (not appended) on backfill, with an expiry per purpose | Retention |
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_a1has onlyviews_percentage; weight it byviewsfromchannel_basic_a3for 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↔MATNRlink 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.
| Source | Source name | BigQuery name | Rust (proposed) | Rule |
|---|---|---|---|---|
| Shopify Admin GraphQL | Object Order, fields displayFinancialStatus, totalPriceSet | Transfer object Orders | shopify::Order, display_financial_status | #[serde(rename_all = "camelCase")] keeps GraphQL’s names on the wire |
| Shopify | Metafields_Product | Metafields_Product | shopify::MetafieldsProduct | The underscore joins owner and type; drop it in the struct name |
| Shopify | ID gid://shopify/Order/5012345 | String | ShopifyGid (newtype) | Keep the full GID; parse the numeric tail only for joins |
| YouTube Channel | Report channel_basic_a3 | Table p_channel_basic_a3_<suffix>, view channel_basic_a3_<suffix> | youtube::ChannelBasicA3 | p_ is the table, no prefix is the view; <suffix> is set when the transfer is created |
| YouTube Content Owner | Report content_owner_basic_a4 | p_content_owner_basic_a4_<suffix> | youtube::ContentOwnerBasicA4 | Same pattern, content_owner_ in front |
| YouTube (both) | Version _a1 … _a4 | Part of the name | Part of the struct name | A 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:
| Meaning | ECC | Shopify | YouTube | GA4 export |
|---|---|---|---|---|
| Country | LAND1 (SAP country key) | countryCode (enum) | country_code (ISO 3166-1, ZZ when thresholded) | geo.country (a country name) |
| Region | REGIO | provinceCode | province_code (US- states only) | geo.region (a name) |
| Product | MATNR | Variant sku, product GID | none (map video_id in Xano) | items.item_id |
| Order | VBELN; Shopify order often in BSTNK | Order GID, name (#1001) | none | ecommerce.transaction_id |
| Day | DATS, server local time | DateTime (UTC) | Pacific day | event_date in the property’s time zone |
| Money | KBETR + KONWA | shopMoney / presentmentMoney | estimated_*_revenue | ecommerce.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 event funnel, and where consent decides
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.
| # | Stage | Source and field | Key carried forward | Grain | Needed to record it per person |
|---|---|---|---|---|---|
| 1 | Impression | YouTube channel_reach_basic_a1.video_thumbnail_impressions | video_id | Cohort | Nothing: no person is recorded |
| 2 | View | channel_basic_a3.views, engaged_views, by subscribed_status, country_code | video_id, country_code | Cohort | Nothing |
| 3 | Click to site | Link with utm_source=youtube&utm_content=<video_id>; GA4 session_start | video_id → session | The bridge | analytics_storage granted; otherwise only a modelled conversion |
| 4 | Product view | GA4 view_item, items.item_id | MATNR / SKU | Person (session) | analytics_storage |
| 5 | Add to cart, checkout | GA4 add_to_cart, begin_checkout | SKU, session | Person | analytics_storage. The Shopify transfer has no cart or abandoned-checkout object, so these stages come from GA4 only |
| 6 | Order | Shopify Orders, OrderLineItems; GA4 purchase | Order GID = transaction_id | Person | The order itself needs no consent (it is the contract); joining it to the session needs analytics_storage |
| 7 | ERP order | ECC VBAK / VBAP | VBELN, Shopify order in BSTNK | Person | None, but the shopper’s consent still governs any use beyond fulfilment |
| 8 | Fulfilment, return, refund | Shopify Fulfillments, Returns, Refunds; ECC deliveries and billing | Order, VBELN | Person | None for the transaction; the purpose limit carries over |
| 9 | Re-engage | Google Ads Customer Match beside a YouTube segment | Hashed email | Person | ad_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):
| Requirement | US (state privacy laws) | EU (GDPR, ePrivacy, DMA) | UK (UK GDPR, PECR) | KR (PIPA) |
|---|---|---|---|---|
| Default before a choice | Analytics on; sale / share and targeted ads off if GPC is sent | Everything off until opt-in | Everything off until opt-in | Everything off until opt-in |
| Tags before the banner is answered | Necessary only where GPC is sent | Necessary only | Necessary only | Necessary only |
| Signals recorded | GPC, sale / share opt-out | analytics_storage, ad_storage, ad_user_data, ad_personalization | Same four | Separate consents per purpose, plus overseas transfer |
| Region and regime | ISO 3166-2 at decision time (state matters) | ISO 3166-2 at decision time | ISO 3166-2 at decision time | ISO 3166-2 at decision time |
| Where the session’s data is stored | US | EU | UK or EU | Held until a KR-resident store exists |
| Child age threshold | Under 13 (COPPA) | 13 to 16, by member state | Under 13 | Under 14 needs a guardian’s consent |
| Ledger entry | Append-only, UTC, sequence number, banner version | Same | Same | Same |
Per segment (the release gate, before any export or activation):
| Requirement | Rule |
|---|---|
| Purpose | Each segment is labelled reporting or activation; only activation may leave for Google Ads, and only through the release gate |
| Minimum cohort size | Keep YouTube’s thresholding (ZZ / NULL stay unknown) and add your own floor on segments built from your data; no segment below it is exported |
| Minors | 13–17 YouTube cohorts are reporting-only; any person under the regime’s child threshold is never in an activation segment |
| Consent per member | Every 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 |
| Residency | The 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 transfer | EU / UK to US needs a lawful transfer basis; KR to anywhere needs the shopper’s separate overseas-transfer consent |
| Mixed-regime segments | A segment spanning regimes takes the strictest rule of any member’s regime, or is split by regime |
| Withdrawal and erasure | A withdrawal or a customers/redact removes the person from every segment before the next export; the export records which version it used |
| Retention | Each segment has an expiry per purpose; YouTube snapshots are kept only as long as reporting needs them |
| Receipt | Every 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).
| Step | What it does | Rule it applies |
|---|---|---|
| Read the views | channel_basic_a3 and channel_demographics_a1, not the p_ tables | A backfill or version change is absorbed by the view |
| Normalise the mix | Shares of views_percentage within each date, video, country, subscribed and live / on-demand row, summing to 1 | Percentages are not counts |
| Weight by views | Each share × views (and engaged views, watch time, subscribers gained) → est_* columns | A video with ten views doesn’t count as much as one with a million |
| Keep the unknowns | ZZ country and NULL age / gender become an explicit unknown cohort; a row with no demographics keeps all its views as one | Thresholded rows are kept, not dropped |
| Convert the day | day_start_utc / day_end_utc from the Pacific (UTC-8) YouTube day | Every source in UTC before a date join |
| Label the regime | US, UK, EU / EEA, KR, unknown or other, from country_code | Rules come from the viewer’s regime |
| Label the purpose | reporting_only for 13–17, unknown age or country, KR (on hold) and cohorts below the floor; otherwise activation_candidate | Minors are never activated; activation still passes the release gate |
| Key the segment | segment_key, a SHA-256 of the cohort’s dimensions | Every 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.jsreads the DD03L export and writessite/ddic.jsonthrough 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 to | Runs in |
|---|---|
| A native command-line tool | cargo run today: structs, Merchant API JSON, XanoScript |
| WebAssembly | A Cloudflare Worker, or a browser island |
| A Python extension (PyO3) | The design pipeline, beside Beautiful Soup |
| A desktop or mobile app | Tauri 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:
| Requirement | Island | Loaded on |
|---|---|---|
| Static content | None: 11ty HTML | Every page |
| A small interactive part (a product card’s variant picker) | petite-vue (about 6 KB) on that element’s v-scope | Pages with that component |
| A server round trip without client state (filters, a form) | HTMX, opt-in | Pages that use it |
| UI behaviour (modal, offcanvas, tabs) | UIkit components (uk-*), the design system the petite-vue islands render into, themed per brand | Pages with those components |
| Motion | GSAP | Pages that animate |
| Heavy compute in the browser (pricing preview, a converter) | The Rust core compiled to WebAssembly | Only that tool’s page |
| Analytics and ad tags | The tag itself | Only 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.
| Platform | Where the page’s HTML comes from | How the islands arrive | Note |
|---|---|---|---|
| Shopify | Liquid theme (theme.liquid, sections) | A theme app embed or one tag in theme.liquid | Shopify serves its storefronts through its own edge, so the tag route is the usual one |
| WordPress | PHP theme templates | Worker in front, or one enqueued script in the theme or a small plugin | Works the same with block and classic themes |
| Astro | Static or server-rendered pages | Worker in front, or one tag in the base layout | Astro’s own islands can sit beside these; nothing has to be rebuilt to add ours through the Worker |
| Next.js | React, server- or statically rendered | Worker in front, or one <Script> tag in the root layout | Without the Worker, the layout tag is a one-time change and redeploy; the islands after that change without a rebuild |
| Adobe Experience Manager | HTL page components, client libraries | Worker in front, or one tag in the page component’s head | Authors keep their components and templates |
| Magento / Adobe Commerce | Layout XML and PHTML templates | Worker in front, or one script declared in the theme’s layout head | Works with Luma and Hyvä themes alike |
| Salesforce Commerce Cloud (B2C, SFRA) | ISML templates | Worker in front, or one tag in the page’s htmlHead template | Cartridges and the storefront code stay as they are |
| SAP Commerce (Hybris) | JSP / tag files in the storefront | Worker in front, or one tag in the master template | The 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 requirement | Full framework build (for example Next.js, Nuxt) | |
|---|---|---|
| What ships to the browser | The HTML, plus each island the page uses | The framework runtime and the page’s bundle, then hydration of the page |
| Build step | 11ty renders HTML; islands need no compile step; only the Rust core is compiled | The whole site goes through the framework’s build |
| Who edits | Designers in Webflow; developers own the islands and the core | Developers, for most changes |
| Changing CMS or theme | The same islands load the same way under any theme | The front end is tied to the framework’s routing and data loading |
| Consent | The Worker decides before any island or tag loads | Server rendering and client hydration can send data before the banner has been answered, unless that is designed in |
| Where it fits best | Content and commerce pages with a few interactive parts | App-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.