| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
|
|
| 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: |
| 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: |
|
|
| 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: |
|
|
| |
| 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). |
| |
| |
| 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)." |
|
|
| |
| 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 |
|
|
| |
| 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 |
|
|
| |
| 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 |
|
|
| |
| 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: |
| |
| |
| 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). |
| |
| |
| 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. |
| |
| |
| 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_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: |
|
|
| - 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: |
|
|
| - 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: |
|
|
| |
| 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)" |
|
|
| |
| 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." |
|
|
| |
| 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: |
|
|
| 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. |
| |
| |
| 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" |
|
|
| |
| |
| |
| 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: |
|
|
| - 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_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." |
|
|
| |
| |
| |
|
|
