# ============================================================================= # why-agent semantic layer — 6-WEEK DEV/TEST VERSION # Dataset: REES46 E-commerce Multichannel Direct Messaging (demo slice) # # This is the development/test version of the semantic layer, built against # the 10M-row demo file (messages-demo.csv) which spans 6 weeks (Apr 30 – Jun # 14, 2021). The production version (semantic_layer.yml) targets the full # 721M-row, 2-year dataset. # # Mapo's agent code should work against either layer without modification — # only the YAML differs. This file makes claims about ONLY what's reachable # in the 6-week demo data. # # Source: https://www.kaggle.com/datasets/mkechinov/direct-messaging # ============================================================================= dataset: name: rees46_direct_messaging_demo_6w description: > Six weeks of multichannel messaging from a medium-sized Russian retailer. Single recipient cohort: all ~3.35M recipients first appear in the data between April 30 and June 14, 2021. Channels in use: email and mobile_push (web_push has 266 messages, SMS has 0). This is a longitudinal cohort study, NOT a panel — there is no acquisition of new recipients across months. Time-series questions ("why did X change month over month") are largely not viable; cross-sectional questions ("why is X different between segments / campaigns / topics") are. data_scope: dev_test production_layer: semantic_layer.yml time_grain: day primary_date_column: messages.date date_range: ["2021-04-30", "2021-06-14"] span_days: 46 # ============================================================================= # DATA SHAPE — characteristics specific to this 6-week slice # ============================================================================= data_shape: description: > The 6-week demo file is structurally different from the full 2-year dataset. The agent should be aware of these characteristics: characteristics: - name: single_cohort_longitudinal description: > Every recipient in the data first received a message between 2021-04-30 and 2021-06-14. There is no fresh-lead acquisition across months. Per-recipient tenure within the dataset (time since first message) is bounded at 0–46 days. - name: pre_purchase_only description: > 100% of messages were sent BEFORE the recipient's first purchase (or to recipients who never purchased). 0% of messages are post-purchase. The recipient_tenure_* dimensions defined for the production layer are degenerate here — every message falls into the "pre_purchase" / "never_purchased" bucket. - name: convertor_split description: > Of 3.35M unique recipients, 350,440 (10.45%) eventually made a purchase (sometime in 2021–2023, well after the 6-week window). The remaining 89.55% have no purchase record. This split is the primary composition dimension viable in this slice. - name: only_three_partial_months description: > Email volume by month: Apr (74K), May (4.25M), Jun (1.45M). April and June are partial months. Month-over-month comparisons are dominated by the May vs Jun comparison; April is too small to be statistically meaningful. - name: channel_distribution description: > email: 5,771,209 messages mobile_push: 4,228,525 messages web_push: 266 messages (effectively unused) sms: 0 messages (no data in this slice) # ============================================================================= # TABLES # ============================================================================= tables: messages: description: > Fact table. One row per individual message. The agent's primary working surface — almost every metric is defined over this table. source_file: messages-demo.csv source_size: 2.17 GB primary_key: message_id grain: one row = one message to one recipient row_count: 10_000_000 dropped_columns: description: > These columns existed in the raw CSV but were dropped during data prep. The agent will not see them and should not reason about them. list: - {name: id, reason: "Internal autoincrement; redundant with message_id"} - {name: category, reason: "100% null in source data"} - {name: created_at, reason: "Internal CDP bookkeeping; not analysis-relevant"} - {name: updated_at, reason: "Internal CDP bookkeeping; not analysis-relevant"} columns: # --- Identifiers --- message_id: type: string fill_rate: full description: "UUID, primary key" campaign_id: type: int fill_rate: full description: "FK to campaigns.id" client_id: type: int fill_rate: full description: | Anonymized recipient ID, stable per user. FK to client_first_purchase_date.client_id (LEFT JOIN — only ~10% of recipients in this slice eventually purchased and appear there). # --- Categorical dimensions --- message_type: type: enum values: [bulk, trigger, transactional] fill_rate: full value_meanings: bulk: | Mass send to a segment of recipients grouped by some condition (e.g., "visitors who didn't receive any bulk campaign in the last 3 months"). A bulk campaign is sent ONCE — start to finish, then done. Most campaigns in this dataset are bulk (1830 of 1907). Lower per-message engagement than trigger/transactional because recipients didn't ask for these. trigger: | 1:1 message sent as a reaction to a specific user action. Classic example: "abandoned cart" — if a user adds something to cart and doesn't purchase within 2 hours, a trigger fires. Triggers run continuously (forever) once configured. They have higher per-message conversion than bulk because they catch users at moments of intent, but in this dataset they don't necessarily open at higher rates than bulk. transactional: | Informational messages tied to specific events: "one-time password," "email confirmation," "bonuses added to your account," order receipts. Recipients EXPECT these messages and actively look for them. As a result, transactional has dramatically higher open rates (~32% in this slice vs ~17% for bulk/trigger) and higher conversion rates. Including transactional in "engagement" comparisons is a common analyst mistake — it's a structurally different message class. notes: | Industry baseline expectations: - Bulk: 17-22% open rate, 1-3% CTR - Trigger: 20-30% open rate, 4-8% CTR (intent-driven) - Transactional: 30-50% open rate, 5-10% CTR (expected mail) In THIS dataset's 6-week slice, bulk and trigger have nearly identical open rates (both ~17%), which is unusual. Triggers still outperform on CTR and conversion, just not on opens. channel: type: enum values_present: [email, mobile_push, web_push] values_absent: [sms] fill_rate: full value_meanings: email: | Has subject, body (usually HTML with offers and personalization), and the full set of metrics: opens, clicks, spam complaints, hard bounces, soft bounces, unsubscribes. Email is where almost all attributed conversions happen in this slice (12,340 of 12,340). mobile_push: | Mobile app push notifications. Has subject (the main line) and optional body. CRITICALLY: mobile push does NOT support unsubscribe, soft bounce, or spam complaint events — those columns will always be False/NULL for push messages. This is by design, not a data quality issue. Open rate is structurally lower than email (~12% vs ~19%) because push has different attention dynamics. In this slice, mobile push produces ZERO attributed conversions across 4.2M sends — possibly an attribution-pipeline issue or by-design (push is informational, not driving purchases through this channel). web_push: | Browser push notifications. Same metric structure as mobile push (no unsubscribe / spam / soft-bounce events). The retailer doesn't actively use this channel — only 266 messages exist in the entire 6-week window. Don't draw conclusions from web_push slices. sms: | Text messages. Lacks subject; body only. Has the smallest set of events: deliver, click, purchase, hard bounce. No opens (no way to detect when an SMS is read). No SMS messages exist in this slice (despite the schema supporting it). notes: | messages.channel does NOT include 'multichannel' — that value only exists in campaigns.channel (see campaigns table). Multichannel campaigns expand into individual messages on real channels (email, push, sms) at delivery time. email_provider: type: string fill_rate: 0.577 null_meaning: "NULL when channel != 'email'" pairs_with: channel description: "Email domain (mail.ru, yandex.ru, gmail.com, etc). Russian providers dominate." platform: type: string fill_rate: 0.074 null_meaning: "NULL when message was not opened" pairs_with: is_opened description: "Device form-factor at open (smartphone/tablet/phablet). Detected from user-agent at open time." stream: type: string fill_rate: full description: "Device type (desktop/ios/android/etc)." # --- Time --- date: type: date fill_rate: full description: "Date message was sent (YYYY-MM-DD). Use for grouping by day." sent_at: type: timestamp fill_rate: full # --- Engagement events (boolean + paired timestamps) --- is_opened: type: bool fill_rate: full opened_first_time_at: type: timestamp fill_rate: 0.161 null_meaning: "NULL when is_opened = false" pairs_with: is_opened opened_last_time_at: type: timestamp fill_rate: 0.161 null_meaning: "NULL when is_opened = false" pairs_with: is_opened is_clicked: type: bool fill_rate: full clicked_first_time_at: type: timestamp fill_rate: 0.024 null_meaning: "NULL when is_clicked = false" pairs_with: is_clicked clicked_last_time_at: type: timestamp fill_rate: 0.024 null_meaning: "NULL when is_clicked = false" pairs_with: is_clicked # --- Negative events (boolean + paired timestamps) --- is_unsubscribed: type: bool fill_rate: full unsubscribed_at: type: timestamp fill_rate: 0.030 null_meaning: "NULL when is_unsubscribed = false" pairs_with: is_unsubscribed is_hard_bounced: type: bool fill_rate: full description: | Permanent failure to deliver. The recipient's address does not exist (email is invalid, or mobile push token is invalid). High hard bounce rates damage sender reputation with email providers. hard_bounced_at: type: timestamp fill_rate: 0.003 null_meaning: "NULL when is_hard_bounced = false" pairs_with: is_hard_bounced is_soft_bounced: type: bool fill_rate: full description: | Temporary delivery failure. The recipient's address exists but can't accept the message right now (mailbox full, server timeout). Different from hard bounce — provider may accept later attempts. Email/push only; SMS does not have soft bounce. soft_bounced_at: type: timestamp fill_rate: 0.0004 null_meaning: "NULL when is_soft_bounced = false" pairs_with: is_soft_bounced is_complained: type: bool fill_rate: full description: | Recipient clicked the "SPAM" button in their email client. This is the strongest negative signal — high complaint rates rapidly destroy sender reputation with the ISP. Email only; mobile push, web push, and SMS do not have spam complaint events. complained_at: type: timestamp fill_rate: 0.0005 null_meaning: "NULL when is_complained = false" pairs_with: is_complained is_blocked: type: bool fill_rate: full description: | Provider TEMPORARILY refused delivery, with a "you're acting like a spammer" message to the postmaster. NOT a permanent ban — retailer can attempt again later, but the signal indicates the ISP's reputation system is concerned. Often correlates with warmup_mode being needed. blocked_at: type: timestamp fill_rate: 0.00002 null_meaning: "NULL when is_blocked = false" pairs_with: is_blocked # --- Conversion --- is_purchased: type: bool fill_rate: full description: | Recipient clicked a link in the message, opened a website or mobile app, and made a purchase. This is the attributed-purchase signal — last-click email/push attribution within whatever window the retailer's tracking applies. The attribution window is not documented in the dataset; investigate is_purchased timing patterns to infer it. purchased_at: type: timestamp fill_rate: 0.001 null_meaning: "NULL when is_purchased = false" pairs_with: is_purchased description: "When the attributed purchase happened. Lag from sent_at to purchased_at can be from minutes to weeks." # --------------------------------------------------------------------------- campaigns: description: Dimension table. One row per campaign. source_file: campaigns.csv source_size: 254 KB primary_key: [id, campaign_type] primary_key_note: | The primary key is COMPOSITE: (id, campaign_type). The id column alone is NOT unique — the same numeric id can appear twice with different campaign_type values (e.g. id=366 exists once as 'bulk' and once as 'transactional'). Always filter or GROUP BY both id AND campaign_type when querying campaigns directly. row_count: 1907 composition: by_campaign_type: {bulk: 1830, transactional: 50, trigger: 27} by_channel: {mobile_push: 1396, email: 483, multichannel: 27, sms: 1} notes: | The campaigns table covers the full 2-year period — campaigns started and ended throughout 2021–2023. But messages in the demo file only reflect activity for these campaigns within April 30 – June 14, 2021. Many campaigns in this table will have ZERO matching messages in the demo data. columns: id: type: int fill_rate: full description: | Part of the composite primary key (id, campaign_type). id alone is NOT unique. campaign_type: type: enum values: [bulk, trigger, transactional] fill_rate: full value_meanings: bulk: | Mass send to a segment, sent ONCE. Has start_at and finish_at. Most campaigns are bulk (1830 of 1907). See messages.message_type for full definition. trigger: | Behavioral 1:1 message that runs continuously after configuration. Has a `position` value indicating priority among triggers (lower position = higher priority — only one trigger fires per event, checked in priority order). 27 trigger campaigns in this dataset. transactional: | Informational message tied to specific events (OTP, confirmations, receipts). Runs continuously. 50 transactional campaigns in this dataset. notes: | The key operational difference: bulk runs once and finishes; trigger and transactional run forever. That's why the dataset has many bulk campaigns and few of the others — each bulk is a discrete event, while a single trigger campaign generates messages for years. channel: type: enum values: [email, mobile_push, web_push, sms, multichannel] fill_rate: full value_meanings: email: "See messages.channel for definition." mobile_push: "See messages.channel for definition." web_push: "See messages.channel for definition." sms: "See messages.channel for definition." multichannel: | A specific kind of TRANSACTIONAL campaign that delivers information through the cheapest available channel for each recipient, falling back to more expensive channels if needed. Typical sequence: try email → if no email, try mobile push → if no push, try web push → if no web push, send SMS. Used for important informational messages where reaching the recipient matters more than channel preference. 27 multichannel campaigns in this dataset. notes: | messages.channel does NOT include 'multichannel' — multichannel campaigns expand into individual messages on real channels at delivery time. So when decomposing engagement BY channel, use messages.channel (not campaigns.channel). topic: type: string fill_rate: 0.984 null_meaning: "NULL for ~30 campaigns where topic was not set" description: "Marketing intent of a bulk campaign. The 'why' behind the send." cardinality: 28 actual_values_in_data: # Counts from full campaigns table (1907 campaigns) # NOTE: real values use SPACES, not underscores (e.g., "sale out" not "sale_out") promotional: - {value: "sale out", count: 779, meaning: "Standard sale or discount promotion. The dominant topic in this dataset."} - {value: "event", count: 63, meaning: "Tied to a specific event (sale day, store event, holiday)."} lifecycle: - {value: "happy birthday", count: 332, meaning: "Birthday greeting/offer to a recipient on or near their birthday."} - {value: "offer after purchase", count: 316, meaning: "Follow-up offer sent some time after a purchase. Cross-sell or repeat-purchase nudge."} - {value: "leave review", count: 287, meaning: "Request to write a review of a recent purchase."} - {value: "welcome campaign", count: 27, meaning: "Welcome series for new sign-ups."} - {value: "welcome letter", count: 1, meaning: "Single welcome email (distinct from welcome campaign)."} - {value: "subscribed", count: 2, meaning: "Confirmation of subscription to a list or content."} - {value: "double opt in", count: 1, meaning: "Confirmation that the recipient confirmed their subscription."} - {value: "profile updated", count: 1, meaning: "Confirmation that recipient changed profile data."} behavioral_trigger: - {value: "abandoned cart", count: 6, meaning: "User added to cart but did not check out. Trigger fires after a delay."} - {value: "abandoned view", count: 3, meaning: "User viewed a product but did not add to cart."} - {value: "abandoned search", count: 2, meaning: "User searched but did not engage further."} - {value: "abandoned category", count: 2, meaning: "User browsed a category without engaging."} - {value: "added to wish list", count: 2, meaning: "Confirmation of adding an item to wishlist."} - {value: "removed from wish list", count: 1, meaning: "Confirmation of removing an item from wishlist."} - {value: "wish list status", count: 1, meaning: "Status update about wishlist items."} - {value: "back in stock", count: 1, meaning: "Notification that a previously-out-of-stock item is available."} - {value: "price drop", count: 2, meaning: "Notification that a watched item has dropped in price."} - {value: "user added to segment", count: 1, meaning: "Internal notification, may or may not be customer-facing."} - {value: "recent purchase", count: 1, meaning: "Acknowledgment of a recent purchase."} - {value: "bonuses expired", count: 1, meaning: "Notification that loyalty bonuses are expiring."} order_status: - {value: "order created", count: 10, meaning: "Order has been placed."} - {value: "order shipped", count: 10, meaning: "Order has been dispatched."} - {value: "order ready for pickup", count: 10, meaning: "Order is available for in-store/locker pickup."} - {value: "order pickup still pending", count: 4, meaning: "Reminder that an order is awaiting pickup."} - {value: "order reminder", count: 9, meaning: "Generic reminder about an order."} - {value: "order cancelled", count: 2, meaning: "Order has been cancelled."} notes: | Topic is the meaning/main goal of a bulk campaign. Strong determinant of every rate metric — composition shifts in topic mix are the most common confound when comparing engagement across periods or segments. The topic mix in this 6-week slice is heavily weighted toward "sale out" (779 of 1907 campaigns). # --- Bulk-only fields (null for trigger/transactional) --- started_at: type: timestamp fill_rate: 0.957 null_meaning: "NULL for trigger/transactional campaigns (those run continuously, no discrete launch)" pairs_with: campaign_type description: | Date/time in the marketing specialist's LOCAL TIMEZONE when the campaign was launched. Bulk campaigns typically take 30 minutes to 4 hours to send to their full audience. finished_at: type: timestamp fill_rate: 0.948 null_meaning: "NULL for trigger/transactional campaigns; also NULL for bulk campaigns that didn't complete" description: "Date/time when the campaign sent its final message." total_count: type: int fill_rate: 0.957 null_meaning: "NULL for trigger/transactional campaigns (no fixed audience size — they run continuously)" description: | Number of recipients in the bulk send (for bulk campaigns only). Reflects the campaign's full lifetime audience — may not match the count of messages in this 6-week demo file if the campaign ran longer than the window. # --- Tri-state booleans (CRITICAL: see gotcha) --- is_test: type: bool fill_rate: 0.014 value_distribution: {true: 6, false: 21, null: 1880} null_meaning: "NULL when never explicitly set. Both False and NULL effectively mean 'not a test campaign.'" encoding: tri_state description: | Tri-state column: True (6), False (21), NULL (1880). Filter for "exclude tests" must use `IS NOT TRUE`, NOT `= false`. The latter would return only 21 rows. See gotcha: null_means_not_applicable. ab_test: type: bool fill_rate: 0.006 value_distribution: {true: 12, false: 0, null: 1895} null_meaning: "NULL when never explicitly set" encoding: tri_state description: "Looks sparse-true (no False values present), but encoding is the same tri-state pattern as is_test." warmup_mode: type: bool fill_rate: 0.960 value_distribution: {true: 31, false: 1799, null: 77} null_meaning: "NULL for the 77 trigger/transactional campaigns (warmup doesn't apply)" pairs_with: campaign_type encoding: tri_state description: | True when a bulk campaign throttles its send rate to build sender reputation with email providers. Used when the retailer has a new recipient database and doesn't want to be flagged as a spammer due to high hard-bounce or complaint rates. Once a "mailing domain" is warmed up, the throttle limit can be lifted. hour_limit: type: int fill_rate: 0.016 null_meaning: "NULL means no hourly send limit configured (warmup_mode is False or no limit set)" pairs_with: warmup_mode description: | Max messages per hour during warmup. Typical values are 500 or 1000 messages per hour. Higher values are used once the domain earns provider trust. position: type: int fill_rate: 0.013 null_meaning: "NULL when campaign is not a trigger" pairs_with: campaign_type description: | Trigger priority. LOWER position = HIGHER priority. Why this matters: when a recipient's action could fire multiple triggers (e.g., they both abandoned a cart AND their wishlist item dropped in price), only ONE trigger fires. The system checks triggers in priority order, fires the first one whose conditions match, and ignores the rest. This prevents flooding the recipient with competing trigger messages and avoids unsubscribes/complaints. # --- Subject features (null for the 27 multichannel campaigns) --- # Author's note: due to anonymization the dataset doesn't expose actual # subject text, so these boolean characteristics are provided instead. # Each example below is from the dataset author's own documentation. subject_length: type: int fill_rate: 0.986 null_meaning: "NULL for the 27 multichannel campaigns (subject ambiguous when routing across channels)" description: "Length of the subject in characters. Sometimes correlates with open rate (very short or very long subjects can underperform a sweet spot)." subject_with_personalization: type: bool fill_rate: 0.986 null_meaning: "NULL for multichannel campaigns" description: | Subject includes recipient personalization (typically the recipient's name or other personal data). Author's example: "Hello, John Wick, there is the special delivery for you!" subject_with_deadline: type: bool fill_rate: 0.986 null_meaning: "NULL for multichannel campaigns" description: | Subject mentions a time-limit or urgency cue. Author's example: "Hey, John Wick! Only 4 days left when you can get your special delivery!" subject_with_emoji: type: bool fill_rate: 0.986 null_meaning: "NULL for multichannel campaigns" description: | Subject contains one or more emoji characters. Author's example: "Hello, John Wick, there is the special delivery for you 😵🍺" subject_with_bonuses: type: bool fill_rate: 0.986 null_meaning: "NULL for multichannel campaigns" description: | Subject mentions loyalty bonuses or points. Author's example: "Hey, John Wick! We added 300 gold coins to your bonus account!" subject_with_discount: type: bool fill_rate: 0.986 null_meaning: "NULL for multichannel campaigns" description: | Subject mentions a discount or percent off. Author's example: "Hey, John Wick! 40% discount in Continental for a low season!" subject_with_saleout: type: bool fill_rate: 0.986 null_meaning: "NULL for multichannel campaigns" description: | Subject announces a sale or sale-related event. Author's example: "Hey, John Wick! Black Friday is coming!" # --------------------------------------------------------------------------- holidays: description: > Calendar of holidays and retail events that drive bulk-campaign timing. NOT the full list of all official holidays — only those the retailer treats as actionable (most are official holidays; some are retail events like Black Friday, Cyber Monday, Back to School). Per the dataset author, retailers usually send bulk campaigns with offers 1-3 days BEFORE a listed date, so this table is useful for identifying scheduled volume spikes vs. real anomalies. source_file: holidays.csv source_size: 1 KB primary_key: date row_count: 48 columns: date: {type: date, fill_rate: full} holiday: {type: string, fill_rate: full} notes: | Holidays span 2021-2023 but only those falling near April 30 - June 14, 2021 are reachable from messages in this slice. Russian retail holidays in this window: probably Victory Day (May 9), Russia Day (June 12), and possibly other dates. Most holidays in the table are outside the demo window and will not match any messages. # --------------------------------------------------------------------------- client_first_purchase_date: description: > Per-client lookup of when each customer made their first purchase. In the 6-week demo slice, ~10.45% of recipients (350,440 of 3,352,422 unique clients) eventually made a purchase — but ALL of those purchases happened AFTER the 6-week messaging window. So this table tells us "did this lead eventually convert" rather than "is this a returning customer." See dimension: eventually_converted. source_file: client_first_purchase_date.csv source_size: 57.5 MB primary_key: client_id grain: one row per client who has ever purchased row_count: 1_854_736 columns: client_id: {type: int, fill_rate: full, description: "FK to messages.client_id"} first_purchase_date: {type: date, fill_rate: full} notes: | Of 1.85M clients in this table, only 350K are recipients in the demo messages file. The other 1.5M purchased without ever appearing in this 6-week messaging window (probably reached via different channels, or messaged outside the window). For demo purposes, this table is effectively a "did this lead eventually convert?" lookup. # ============================================================================= # JOINS # ============================================================================= joins: - name: messages_to_campaigns left: messages.campaign_id, messages.message_type right: campaigns.id, campaigns.campaign_type type: many_to_one join_kind: inner notes: "Every message has a campaign_id. INNER JOIN is safe. When joining to messages, use: JOIN campaigns c ON c.id = m.campaign_id AND c.campaign_type = m.message_type Never join on id alone or you will get duplicate rows for campaigns that share an id across campaign_type values." - name: messages_to_holidays left: messages.date right: holidays.date type: many_to_one join_kind: left notes: "LEFT JOIN; treat NULL holiday as 'normal day'." - name: messages_to_client_first_purchase left: messages.client_id right: client_first_purchase_date.client_id type: many_to_one join_kind: left notes: | LEFT JOIN — REQUIRED. Recipients absent from client_first_purchase_date never purchased (89.55% of the demo population). They are the "never_converted" segment, NOT missing data. Recipients with a row (10.45%) are the "eventually_converted" segment — but their purchase date is always AFTER the 6-week messaging window. # ============================================================================= # GLOBAL FILTERS — applied to every metric unless explicitly opted out # ============================================================================= global_filters: - name: exclude_test_campaigns description: > Test campaigns target small audiences and skew aggregates. The dataset author explicitly recommends excluding them. sql: "campaigns.is_test IS NOT TRUE" rationale: | CRITICAL: must be `IS NOT TRUE`, not `= false`. The is_test column is tri-state — out of 1907 campaigns, only 6 are True, 21 are False, and 1880 are NULL. We want all 1901 non-test campaigns (False AND NULL). A naive `WHERE is_test = false` would return only 21 rows. See gotcha: null_means_not_applicable. # ============================================================================= # METRICS # ============================================================================= metrics: # --- Volume metrics --- messages_sent: description: Total messages sent. type: count time_column: messages.sent_at sql: "COUNT(messages.message_id)" unique_recipients: description: Distinct clients reached. type: count_distinct time_column: messages.sent_at sql: "COUNT(DISTINCT messages.client_id)" campaigns_run: description: Distinct campaigns active in the period. type: count_distinct time_column: messages.sent_at sql: "COUNT(DISTINCT messages.campaign_id)" # --- Engagement rate metrics --- open_rate: description: > Share of messages opened. Email and push only (SMS has no open event; no SMS messages exist in this slice anyway). business_meaning: | Open rate measures whether the recipient saw the message — specifically, whether their email client loaded the tracking pixel (for email) or whether the push notification was tapped (for push). Industry treats open rate as a proxy for SUBJECT-LINE QUALITY and SENDER REPUTATION, since it's what happens before the recipient sees any of the body. WARNING: open rate is contaminated by tracking-pixel artifacts. Apple Mail Privacy Protection (since iOS 15, Sept 2021) auto-fetches pixels for Apple Mail users, inflating their measured open rate without any real engagement change. This dataset is mostly Russian email providers (mail.ru, yandex.ru) where this effect is smaller, but still present. For more reliable engagement signal, use click_through_rate or click_to_open_rate. type: ratio time_column: messages.sent_at numerator: "SUM(CASE WHEN messages.is_opened THEN 1 ELSE 0 END)" denominator: "COUNT(messages.message_id)" applicable_channels: [email, mobile_push] typical_baselines: email_bulk: "17-22% (in this dataset: 17.2%)" email_trigger: "20-30% industry; in this dataset oddly only 17.2%" email_transactional: "30-50% industry; in this dataset 32.2%" mobile_push: "lower than email — in this dataset ~12%" composition_warning: | Open rate baselines differ by channel, by topic (transactional much higher than bulk sale_out), and by eventually_converted status (eventually-converters ~21.3% vs never-converters ~18.3%). If aggregate open_rate moves between any two slices, ALWAYS check whether channel/topic/conversion mix shifted before concluding behavior changed. click_through_rate: description: Share of messages clicked. business_meaning: | A more reliable engagement signal than open_rate because clicks require active recipient action (whereas opens can be triggered by automatic pixel-fetching). CTR measures whether the recipient was compelled enough by the subject AND content to take action. Lower in absolute terms than open rate (most opens don't lead to clicks) but more diagnostic of message effectiveness. type: ratio time_column: messages.sent_at numerator: "SUM(CASE WHEN messages.is_clicked THEN 1 ELSE 0 END)" denominator: "COUNT(messages.message_id)" typical_baselines: email_bulk: "1-3% (in this dataset: 2.76%)" email_trigger: "4-8% (in this dataset: 4.96%)" email_transactional: "5-10% (in this dataset: 5.34%)" mobile_push: "very low — in this dataset 0.13% (effectively zero)" composition_warning: "Same as open_rate — strongly varies by channel, topic, conversion status." click_to_open_rate: description: Of opened messages, share that were clicked. business_meaning: | The most diagnostic engagement metric. Conditioning on opens removes both the inattentive recipient bias (wasn't going to engage anyway) and the tracking-pixel artifact (pixel-fetched without human seeing). What's left: of people who actually saw the message, how many cared enough to click? Industry uses click-to-open as the cleanest test of whether the BODY content of the message worked. type: ratio time_column: messages.sent_at numerator: "SUM(CASE WHEN messages.is_clicked THEN 1 ELSE 0 END)" denominator: "SUM(CASE WHEN messages.is_opened THEN 1 ELSE 0 END)" conversion_rate: description: > Share of messages resulting in an in-window purchase. business_meaning: | The ultimate ROI signal — did the message drive money? "Conversion" here means: recipient clicked, navigated to the website/app, and made a purchase (last-click attribution). Note: most attributed purchases happen WITHIN HOURS of send (median 1.65 hours in this dataset), but the long tail can stretch to 100+ days. The retailer's attribution window is not documented. type: ratio time_column: messages.sent_at numerator: "SUM(CASE WHEN messages.is_purchased THEN 1 ELSE 0 END)" denominator: "COUNT(messages.message_id)" typical_baselines: email_bulk: "0.5-2 per 1000 (in this dataset: 0.85)" email_trigger: "1-5 per 1000 (in this dataset: 2.18)" email_transactional: "5-15 per 1000 (in this dataset: 6.48)" mobile_push: "ZERO in this dataset — investigate before drawing conclusions" composition_warning: "Strongly varies by topic, channel, conversion status." # --- Negative-signal rates --- unsubscribe_rate: description: Share of messages resulting in unsubscribe. business_meaning: | The leading indicator of list health. When unsubscribe rate climbs, the retailer is sending content the recipient considers irrelevant or annoying — even if open rate looks fine. A spike on a specific day usually points to a single bad campaign (wrong audience, wrong message, wrong frequency). Healthy industry baseline is <2 per 1000; this dataset runs at 3-5 per 1000 (somewhat elevated). type: ratio time_column: messages.sent_at numerator: "SUM(CASE WHEN messages.is_unsubscribed THEN 1 ELSE 0 END)" denominator: "COUNT(messages.message_id)" hard_bounce_rate: description: Share of messages that hard-bounced. business_meaning: | Indicates list quality and provider acceptance. High hard-bounce rates damage sender reputation and can trigger ISP-level blocks. Healthy <2 per 1000; concerning >5 per 1000. This dataset's mail.ru runs ~6 per 1000 — already in the concerning range. type: ratio time_column: messages.sent_at numerator: "SUM(CASE WHEN messages.is_hard_bounced THEN 1 ELSE 0 END)" denominator: "COUNT(messages.message_id)" spam_complaint_rate: description: Share of messages flagged as spam. business_meaning: | The most damaging negative signal. ISPs treat complaint rates above ~1 per 1000 as a serious reputation hit. Anything above 3 per 1000 can result in ISP-level blocking. Email only — push and SMS have no spam-complaint mechanism. type: ratio time_column: messages.sent_at numerator: "SUM(CASE WHEN messages.is_complained THEN 1 ELSE 0 END)" denominator: "COUNT(messages.message_id)" delivery_rate: description: Share of messages successfully delivered. business_meaning: | Composite reliability metric. Failures here come from hard bounces (bad addresses), soft bounces (temporary failures), and ISP blocks (reputation issues). A healthy retailer hits 95%+ delivery rate; <90% indicates serious problems with list hygiene or sender reputation. type: ratio time_column: messages.sent_at numerator: | SUM(CASE WHEN NOT messages.is_hard_bounced AND NOT messages.is_soft_bounced AND NOT messages.is_blocked THEN 1 ELSE 0 END) denominator: "COUNT(messages.message_id)" # ============================================================================= # DIMENSIONS # ============================================================================= dimensions: channel: table: messages column: channel description: Delivery channel. cardinality: 3 typical_values: [email, mobile_push] notes: "web_push effectively unused (266 messages). SMS not present in this slice." message_type: table: messages column: message_type description: Bulk vs trigger vs transactional. cardinality: 3 notes: "Transactional has dramatically higher engagement. Including/excluding it in 'engagement' is a common analyst mistake." topic: table: campaigns column: topic description: Marketing intent of the campaign. cardinality: 28 top_values: [sale_out, happy_birthday, offer_after_purchase, leave_review, event] notes: "Strong determinant of every rate metric. Composition shifts here are the most common confound." email_provider: table: messages column: email_provider description: Email domain. cardinality: ~50 notes: "mail.ru and yandex.ru dominate (Russian retailer)." stream: table: messages column: stream description: Device type (desktop/ios/android/etc). cardinality: ~5 platform: table: messages column: platform description: Device form-factor (smartphone/tablet/phablet). cardinality: 3 notes: "NULL for unopened messages. Filter is_opened=true before decomposing." topic_grouping: derived: true description: Higher-level topic category for cleaner decompositions. sql_case: | CASE WHEN campaigns.topic IN ('sale out', 'event') THEN 'promotional' WHEN campaigns.topic IN ('happy birthday', 'offer after purchase', 'leave review', 'welcome campaign', 'welcome letter', 'subscribed', 'double opt in', 'profile updated') THEN 'lifecycle' WHEN campaigns.topic IN ('order created', 'order shipped', 'order ready for pickup', 'order pickup still pending', 'order reminder', 'order cancelled') THEN 'order_status' WHEN campaigns.topic IN ('abandoned cart', 'abandoned view', 'abandoned search', 'abandoned category', 'price drop', 'back in stock', 'added to wish list', 'removed from wish list', 'wish list status', 'recent purchase', 'bonuses expired', 'user added to segment') THEN 'behavioral_trigger' ELSE 'other' END bucket_meanings: promotional: "Sales-driven outbound — sale out, event-tied promos. The retailer's main marketing surface." lifecycle: "Customer-relationship messages tied to a recipient's lifecycle position — birthdays, post-purchase touches, welcome series." order_status: "Operational order updates. Highest engagement of any group (these are messages recipients want to see)." behavioral_trigger: "Reactive messages fired by recipient actions (abandoned cart, price drops, wishlist activity)." other: "Catch-all bucket for topics that don't fit cleanly. Should be small." is_holiday_window: derived: true description: Is the message sent within 3 days before a listed holiday? sql: "messages.date BETWEEN holidays.date - INTERVAL 3 DAY AND holidays.date" notes: "Within the 6-week window, expect coverage for May 9 (Victory Day), June 12 (Russia Day), and possibly other dates." eventually_converted: derived: true primary_for_demo: true description: > Whether this recipient eventually made a first purchase (anytime in 2021-2023, almost always AFTER the 6-week messaging window). This is the strongest composition dimension viable in the demo slice. sql_case: | CASE WHEN client_first_purchase_date.first_purchase_date IS NULL THEN 'never_converted' ELSE 'eventually_converted' END cardinality: 2 population_split: eventually_converted: {messages: 1941040, recipients: 350440, pct_of_messages: 33.6} never_converted: {messages: 3830169, recipients: 3001982, pct_of_messages: 66.4} engagement_baselines_email: eventually_converted: {open_rate: 0.213, click_through_rate: 0.0486, conversion_rate: 0.0049} never_converted: {open_rate: 0.183, click_through_rate: 0.0354, conversion_rate: 0.0007} notes: | The 3-percentage-point gap in open rate (21.3% vs 18.3%) and the 7x gap in conversion rate (0.49% vs 0.07%) make this the cleanest composition dimension in the data. ALWAYS check this dimension's mix before concluding aggregate metrics changed for behavioral reasons. subject_feature: table: campaigns description: Pseudo-dimension for subject-line feature flags. notes: | Each subject_with_* column is a separate boolean. WARNING: these features are heavily correlated with topic and channel. # --- DEGENERATE in this 6-week slice — kept for layer compatibility --- recipient_tenure_status: derived: true degenerate_in_demo: true description: | In the production layer, splits recipients by pre-purchase vs post-purchase status. In the 6-week demo slice, ALL messages are pre-purchase — this dimension always evaluates to 'pre_purchase'. Use eventually_converted instead for composition analysis on demo data. sql_case: "'pre_purchase' -- always, in this slice" recipient_tenure_bucket: derived: true degenerate_in_demo: true description: | In the production layer, buckets recipients by months since first purchase. In the 6-week demo slice, ALL messages are pre-purchase — this dimension always evaluates to 'never_purchased'. Use eventually_converted instead. sql_case: "'never_purchased' -- always, in this slice" # ============================================================================= # DIMENSION HIERARCHIES # ============================================================================= hierarchies: time: levels: [day, week] primary_column: messages.date notes: "Month/quarter levels excluded — only 3 partial months in this slice." campaign: levels: [campaign_id, topic, topic_grouping, message_type] # ============================================================================= # GOTCHAS — context that changes how the agent reasons # ============================================================================= gotchas: - name: null_means_not_applicable severity: critical description: > Several columns in `campaigns` are tri-state (True / False / NULL), where NULL means "not applicable to this row" rather than "false." Affected: is_test, ab_test, warmup_mode (booleans); hour_limit, position (integers). The False count is often small or zero, while NULL counts are large. Examples: is_test: True=6, False=21, NULL=1880 ab_test: True=12, False=0, NULL=1895 warmup_mode: True=31, False=1799, NULL=77 NAIVE QUERIES PRODUCE WRONG ANSWERS. `WHERE is_test = false` returns only the 21 explicitly-False rows, dropping the 1880 NULL rows that also represent non-test campaigns. The correct filter is `WHERE is_test IS NOT TRUE` (or `IS DISTINCT FROM true`), which correctly includes both False AND NULL. Errors here are silent — queries run, return data that looks plausible, and are wrong. This is the most failure-prone gotcha in the dataset. - name: time_series_questions_largely_invalid severity: critical description: > The demo slice spans only 6 weeks (April 30 – June 14, 2021). Email messaging covers 3 partial months (April 74K, May 4.25M, June 1.45M). Month-over-month and longer time-series questions ARE NOT ANSWERABLE from this data. The agent MUST detect when a user's question implies multi-month context ("compared to last quarter," "year-over-year," "trend over the past few months") and clarify or redirect rather than fabricate an answer. Cross-sectional questions (between segments, campaigns, topics, days within the window) ARE viable and should be the agent's primary mode. - name: pre_purchase_only_in_demo severity: high description: > ALL messages in the demo slice were sent before the recipient's first purchase (or to recipients who never purchased). Tenure-based questions that distinguish "new vs returning customers" are not answerable here. The recipient_tenure_status and recipient_tenure_bucket dimensions are degenerate (always 'pre_purchase' / 'never_purchased'). Use eventually_converted instead — that's the working composition dimension for this slice. - name: rate_metrics_need_composition_check severity: high description: > open_rate, click_through_rate, conversion_rate, unsubscribe_rate are all averages over a population. If the agent finds an aggregate rate differs across slices (e.g., between two campaigns, two topics, two days), it MUST check whether the population mix changed (across channel, topic, message_type, eventually_converted) before concluding that BEHAVIOR differs. Composition-shift confounds are the dominant analytical failure mode in this dataset. - name: sparse_event_columns_are_meaningful severity: medium description: > Many *_at and device columns are conditionally null based on whether a corresponding event happened. Low fill rates are by design, not data quality issues. Use the boolean is_* partner for filtering; use the timestamp only for time-of-event analysis. - name: subject_features_null_for_multichannel severity: medium description: > All subject_with_* columns and subject_length are NULL for the 27 multichannel campaigns. When decomposing by any subject feature, multichannel campaigns appear as a NULL bucket — this is correct behavior, but worth flagging in the agent's narration. - name: subject_features_are_confounded severity: medium description: > subject_with_emoji, subject_with_personalization, etc. correlate strongly with topic and channel. - name: campaigns_table_outlives_messages_window severity: medium description: > campaigns.csv contains 1907 campaigns spanning 2021-2023. The messages-demo.csv only covers 6 weeks of activity. Many campaigns in the table have ZERO messages in the demo data — they ran entirely outside the window. campaigns.total_count reflects the campaign's full lifetime audience, NOT the count of messages in the demo file. Always count messages from the messages table directly, never trust campaigns.total_count for in-window analysis. - name: campaign_channel_vs_message_channel severity: medium description: > campaigns.channel can be 'multichannel'; messages.channel cannot. Multichannel campaigns expand into individual messages on real channels. When decomposing engagement by channel, use messages.channel. - name: holiday_table_extends_beyond_window severity: low description: > The holidays table covers 2021-2023, but only those falling within the 6-week messaging window will match any messages. Don't expect every listed holiday to have impact in this slice. - name: web_push_and_sms_are_absent_in_demo severity: low description: > Despite the schema supporting them, web_push has only 266 messages and SMS has 0 messages in the demo slice. Don't compute open_rate for SMS. Don't draw conclusions from web_push slices. # ============================================================================= # EXTERNAL CONTEXT — events the agent uses to PROPOSE hypotheses # Only events within or near the 6-week window are listed. # ============================================================================= external_events: - date: 2021-05-09 name: "Victory Day (Russia)" affects_metrics: [messages_sent, conversion_rate] description: > Major Russian public holiday. May see send-volume changes around this date (some retailers pause sends; others promote). - date: 2021-06-12 name: "Russia Day" affects_metrics: [messages_sent] description: "Russian national holiday. May affect bulk send timing." # Note: iOS 15 (Sep 2021), Russian invasion (Feb 2022), Black Friday 2021/2022 # are all OUTSIDE this 6-week demo window and have been excluded. They live # in the production layer (semantic_layer.yml) for the full 2-year data.