(Pilot) ZMS Campaign SKU Daily

Important note !!!

Pilot Phase: This dataset is currently in a pilot phase and is available exclusively to a selected subset of partners.

KPI Aggregation & Granularity: The calculated KPIs in this dataset—specifically ropi, roas, cvr, ctr, cpc, roas_direct, clicks_conversion_rate_direct, and cpsi_direct—are provided at the baseline granularity of campaign_id, dt, config_sku, and country. If you alter or roll up the granularity of your analysis, do not directly sum (SUM) or average (AVG) these KPI columns. Instead, recalculate the metrics using the explicit formulas mentioned in the schema description below (i.e., aggregate the base metrics first, then apply the formula). Example usage can be seen in the Example Measures section.

Overview

This dataset delivers daily insights for Zalando Partner Marketing Services (ZMS) campaigns, with a specific focus on Performance Objective Campaigns structured at the granular config_sku level.

By tracking key metrics—such as viewable_impressions, ad_clicks, budget_spent, and items_sold, partners can monitor exactly how distinct SKUs perform across various campaigns and target countries. These granular insights enable partners to evaluate marketing effectiveness, optimise campaign budgets, and seamlessly align inventory management with active marketing initiatives.

Property Description
Data granularity campaign_id, dt, config_sku, country
History available Data available from 2024-01-01
Update frequency Daily
Data retention Past 2 years + current year
Primary keys campaign_id, dt, config_sku, country
Partition columns dt
SLOs Daily around 1:00 PM UTC (for the previous day's data)
Notice period for changes See Versioning and Deprecation Policy
Support Support available via partner-care@zalando.de.
No 24/7 support available.

Data Refresh Strategy

This dataset requires a full load approach rather than incremental updates, meaning partners should overwrite their entire local table each time they retrieve the data.

  • Why full load? Historical campaign and attribution data can change retroactively for up to 2 years (for example, due to late-reported sales or financial adjustments).
  • Easy to manage: Because the overall dataset size is highly manageable, completely overwriting the data is fast, reliable, and keeps downstream systems simple.
  • Tracking refreshes: Partners can reference the updated_at timestamp column to verify exactly when the data was last refreshed.

Table Reference

zms_campaign_sku_daily_share.direct_data_sharing.zms_campaign_sku_daily

Schema

Column name Format Description
account_id string Zalando Partner Account ID (included as partition key for downstream processing).
merchant_id string Zalando Partner ID (BPID).
campaign_id string Unique ID of the campaign (n_code).
campaign_name string Name of the campaign.
campaign_objective string Objective of the campaign. One of: Conversion, Consideration, Awareness.
start_date date Campaign start date.
end_date date Campaign end date.
dt date Event record date.
country string ISO country code of campaign (e.g. DE, AT).
config_sku string Zalando article variant (e.g. ON321N1QS-K11).
merchant_sku string Partners reference code for a SKU (Stock Keeping Unit) at the article and colour level.
article_name string Name of the article (e.g. PL BB Cap).
brand_code string Brand code of the article.
brand_name string The name of the brand.
gender string The gender the product is intended for (e.g. female, male, unisex).
viewable_impressions bigint The number of times a user has been exposed to at least 50% of your ad content (25% for big ad format).
ad_clicks bigint The number of times a user has clicked on your ads.
budget_spent double Your total campaign budget, including discounts, vouchers and free media.
partner_spent double The amount you have invested in your campaign, excluding discounts, vouchers and free media.
items_sold bigint The number of items sold after users clicked on your ads (before cancellations and returns).
attributed_gmv double The value of items sold after users clicked on your ads (before cancellations and returns).
ropi double Return On Partner Invest: Calculated as Attributed GMV divided by Partner Invest and excluding any budget spent on TikTok, Pinterest and/or Snapchat.

Formula:SUM(attributed_gmv) /SUM(partner_spent)
roas double Return On Ad Spend: Calculated as Attributed GMV divided by Budget Spent and excluding any budget spent on TikTok, Pinterest and/or Snapchat.

Formula:SUM(attributed_gmv) /SUM(budget_spent)
cvr double Conversion Rate: The percentage of clicks on your ads that have led to sales (excluding clicks from TikTok, Pinterest and/or Snapchat).

Formula:SUM(items_sold)/SUM(ad_clicks)
ctr double Click-through Rate: Calculated as Clicks divided by Viewable Impressions.

Formula:SUM(ad_clicks)/SUM(viewable_impressions)
cpc double Cost Per Click: The amount you pay for each click on your ads.
Formula:SUM(budget_spent) /SUM(ad_clicks)
created_at timestamp Timestamp in UTC when the data was created.
updated_at timestamp Timestamp in UTC when the data was updated.
sales_direct bigint The number of items sold - before cancellations and returns - after a user clicked on an ad of the exact same SKU.
gmv_direct double The value of items sold - before cancellations and returns - after users clicked on an ad of the exact same SKU.
roas_direct double Return on Ad Spend: The GMV your business earns for each euro it spends on advertising. Calculated as Direct GMV divided by Budget Spent.

Formula:SUM(gmv_direct)/SUM(budget_spent)
clicks_conversion_rate_direct double The percentage of clicks on your ads that have led to a sale of the exact same SKU.

Formula: SUM(sales_direct)/(SUM(ad_clicks)
cpsi_direct double The advertising cost per individual item sold of the exact same SKU.

Formula:SUM(budget_spent)/SUM(sales_direct)

Example Measures

Campaign Overview at Article level (ROAS, ROPI)

This query evaluates campaign returns at the config_sku level . It calculates the Return on Ad Spend (ROAS) and Return on Partner Invest (ROPI). Optionally report can be expanded to different granularities like country, dt.

SELECT
    campaign_id,
    campaign_name,
    config_sku,
    SUM(viewable_impressions) AS viewable_impressions,
    SUM(ad_clicks) AS ad_clicks,
    SUM(budget_spent) AS budget_spent,
    SUM(partner_spent) AS partner_spent,
    SUM(attributed_gmv) AS attributed_gmv,
    SUM(attributed_gmv) / NULLIF(SUM(budget_spent), 0) AS roas,
    SUM(attributed_gmv) / NULLIF(SUM(partner_spent), 0) AS ropi
FROM zms_campaign_sku_daily_share.direct_data_sharing.zms_campaign_sku_daily
GROUP BY 1, 2, 3

Top Performing Article Variants (CTR, CVR, and CPC)

This query evaluates campaign performance at the config_sku level (product level). It calculates Click-Through Rate (CTR), Conversion Rate (CVR), and Cost Per Click (CPC) to identify which items convert best. Optionally report can be expanded to different granularities such as campaign_id, country, dt.

SELECT
    campaign_id,
    campaign_name,
    config_sku,
    article_name,
    brand_name,
    SUM(viewable_impressions) AS viewable_impressions,
    SUM(ad_clicks) AS ad_clicks,
    SUM(items_sold) AS items_sold,
    SUM(budget_spent) AS budget_spent,
    CAST(SUM(ad_clicks) AS DOUBLE) / NULLIF(SUM(viewable_impressions), 0) AS ctr,
    CAST(SUM(items_sold) AS DOUBLE) / NULLIF(SUM(ad_clicks), 0) AS cvr,
    SUM(budget_spent) / NULLIF(SUM(ad_clicks), 0) AS cpc
FROM zms_campaign_sku_daily_share.direct_data_sharing.zms_campaign_sku_daily
GROUP BY 1, 2, 3, 4, 5
ORDER BY items_sold DESC

Top SKUs by Direct Sales and Efficiency

Ranks advertised SKUs by sales_direct and roas_direct to identify which specific SKUs drive the most direct conversions. Use this to prioritize high-performing SKUs for future campaigns and budget allocation.

SELECT
    campaign_id,
    campaign_name,
    config_sku,
    article_name,
    brand_name,
    SUM(budget_spent)                                           AS budget_spent,
    SUM(sales_direct)                                           AS sales_direct,
    SUM(gmv_direct)                                             AS gmv_direct,
    SUM(gmv_direct)   / NULLIF(SUM(budget_spent), 0)            AS roas_direct,
    SUM(budget_spent) / NULLIF(SUM(sales_direct), 0)            AS cpsi_direct,
    SUM(sales_direct) / NULLIF(SUM(ad_clicks), 0)               AS clicks_conversion_rate_direct
FROM zms_campaign_sku_daily_share.direct_data_sharing.zms_campaign_sku_daily
GROUP BY 1, 2, 3, 4, 5
ORDER BY sales_direct DESC

Direct vs Attributed Performance by SKU

Compares direct attribution (gmv_direct, roas_direct) with broader click attribution (attributed_gmv, roas) at the SKU level. A large gap between the two signals a strong halo effect — customers purchasing other products after clicking your ad — which can inform how you evaluate the full value of a campaign.

SELECT
    config_sku,
    article_name,
    brand_name,
    SUM(budget_spent)                                           AS budget_spent,
    SUM(sales_direct)                                           AS sales_direct,
    SUM(items_sold)                                             AS items_sold_attributed,
    SUM(gmv_direct)                                             AS gmv_direct,
    SUM(attributed_gmv)                                         AS attributed_gmv,
    SUM(gmv_direct)     / NULLIF(SUM(budget_spent), 0)          AS roas_direct,
    SUM(attributed_gmv) / NULLIF(SUM(budget_spent), 0)          AS roas_attributed
FROM zms_campaign_sku_daily_share.direct_data_sharing.zms_campaign_sku_daily
GROUP BY 1, 2, 3
ORDER BY gmv_direct DESC

Changelog (non-breaking changes)

2026-07-08 — v1.1.0 (Additive schema change)

  • Summary: Added 5 new columns for direct attribution metrics at the SKU level
  • Details: New columns enable granular analysis of direct (same-SKU) attribution vs. broader click-through attribution at the article variant level. Added columns: sales_direct (bigint), gmv_direct (double), roas_direct (double), clicks_conversion_rate_direct (double), cpsi_direct (double). All columns are non-nullable and represent metrics specific to clicks on the exact same SKU.
  • Availability: Available from the release date 2026-07-08.
  • Impact: None expected for existing consumers. This is purely additive and does not modify existing columns or their behavior.
  • Action required: None. Existing queries will continue to work. Consumers can optionally add the new direct attribution columns to their pipelines to measure SKU-specific campaign effectiveness and identify halo effects.
Contact Support