When Do You Need a Data Warehouse for BI? — Addendum: Shop Campaigns via ShopifyQL

Executive summary

This guide helps you decide when to build a governed analytics foundation and explains the layers required (extraction, warehouse, semantic model). Update highlight: Shopify released shop_campaign_insights in ShopifyQL (Aug 10, 2026). It is queryable via Admin GraphQL shopifyqlQuery with read_reports scope. Immediate opportunities: ETL into your warehouse for modeling ROAS/CAC/AOV, embed merchant-facing charts with Shopify's Analytics Web Components, and add contextual annotations via the Analytics Annotations API. Plan for scope and PII constraints, incremental sync, timezone normalization, and reconciliation with ad platforms.

What changed (technical summary)

On August 10, 2026 Shopify added shop_campaign_insights to the ShopifyQL schema. The schema exposes campaign- and segment-level metrics (shop_campaign_ad_spend, shop_campaign_sales, shop_campaign_return_on_ad_spend, shop_campaign_average_order_value, shop_campaign_average_customer_acquisition_cost) and dimensions such as campaign name, customer segment, and time granularities from hourly to yearly in the merchant's shop timezone. Queries run through Admin GraphQL shopifyqlQuery which returns tableData (columns, types, rows) suitable for ETL.

Who is affected and when to use this source

Affected audiences: analytics/BI engineers, data engineers, CTOs, and product or engineering leads building merchant reporting or embedded analytics. Use this Shopify source when you need merchant-attributed campaign KPIs without building full connectors to ad platforms. Don’t assume it replaces platform-level data when you require exact platform attribution or external campaign IDs for one-to-one reconciliation.

ETL patterns and example implementation

Recommended ETL pattern: - Call Admin GraphQL shopifyqlQuery with FROM shop_campaign_insights and parse tableData.columns and rows. - Incremental sync: use shop_campaign_insights_last_updated_at to pull only changed rows since the last successful run. - Cadence: hourly for near-real-time dashboards (watch rate limits), daily for aggregated pipelines. - Timezone: store the merchant's shop timezone with each row; convert to your canonical warehouse timezone at aggregation time. - Currency: persist currency_code and convert at reporting time if you need cross-shop aggregates. - Storage model: keep raw tableData for replayability, or normalize into campaigns and campaign_metrics_timeseries tables. - Operational practices: log raw responses, implement retries/backoff on GraphQL errors, and test on dev stores and at least one high-volume store for performance.

ShopifyQL examples and calling shopifyqlQuery (copyable patterns)

Two canonical ShopifyQL examples (adapted from the official schema docs): 1) Rank campaigns by sales (monthly window): FROM shop_campaign_insights SHOW shop_campaign_name, shop_campaign_sales, shop_campaign_ad_spend WHERE date >= '2026-07-01' AND date <= '2026-07-31' GROUP BY shop_campaign_name ORDER BY shop_campaign_sales DESC LIMIT 50 2) Weekly spend timeseries for a single campaign: FROM shop_campaign_insights SHOW shop_campaign_ad_spend TIMESERIES week WHERE shop_campaign_name = 'SUMMER_PROMO' Example Admin GraphQL call (cURL): curl -X POST https://{shop}.myshopify.com/admin/api/2026-07/graphql.json \ -H "Content-Type: application/json" \ -H "X-Shopify-Access-Token: {access_token}" \ -d '{"query":"query { shopifyqlQuery(query: \"FROM shop_campaign_insights SHOW shop_campaign_ad_spend TIMESERIES week WHERE shop_campaign_name = \\\"SUMMER_PROMO\\\"\") { tableData { columns { name type } rows } } }" }' Node (fetch) pattern: const res = await fetch('https://{shop}.myshopify.com/admin/api/2026-07/graphql.json', { method: 'POST', headers: { 'Content-Type': 'application/json', 'X-Shopify-Access-Token': ACCESS_TOKEN }, body: JSON.stringify({ query: `query { shopifyqlQuery(query: "FROM shop_campaign_insights SHOW shop_campaign_ad_spend TIMESERIES week WHERE shop_campaign_name = 'SUMMER_PROMO'") { tableData { columns { name type } rows } } }` }) }); const json = await res.json(); // parse json.data.shopifyqlQuery.tableData Parse tableData.columns to map column names to row positions before ingesting into your warehouse.

Field-to-KPI mapping and definitions

Map Shopify fields to your KPIs (based on schema definitions): - shop_campaign_sales → Sales / Revenue (Shopify notes refunds excluded for shop_campaign_sales). - shop_campaign_ad_spend → Ad spend (reported spend attributed by Shopify). - shop_campaign_return_on_ad_spend → ROAS (defined: shop_campaign_sales / shop_campaign_ad_spend). - shop_campaign_average_customer_acquisition_cost → CAC (schema definition; Shopify uses customers attributed when last paid click occurred within seven days). - shop_campaign_average_order_value → AOV (schema-provided; conceptually shop_campaign_sales / shop_campaign_orders). Document these mappings in your semantic layer and record Shopify's attribution rules so analysts understand expected differences vs ad platform reports.

Timezone, currency and multi-shop aggregation

Important cross-shop considerations: - Time dimensions are in each merchant's shop timezone. Persist the original timezone and convert at aggregation time to avoid misalignment across stores. - Metrics are in store currency. Persist currency_code and apply historical FX conversions only when you understand the reporting implications.

Embedding dashboards: Analytics Web Components

Shopify's Analytics Web Components let you render Shop Campaign metrics inside your app UI without storing merchant metrics server-side. Use components for fast in-app presentation when single-shop rendering suffices. If you need cross-shop aggregation, long-term historical storage, or complex joins, prefer server-side ingestion and rendering. Consider CSP and authentication flows when embedding.

Annotations: adding context to merchant analytics

Apps can create annotations using Admin GraphQL mutations analyticsAnnotationCreate/analyticsAnnotationUpdate/analyticsAnnotationDelete. Requirements and behaviour: - Creating annotations requires write_analytics_annotations scope. - Shopify enforces per-app annotation limits (docs reference limits but do not publish exact quotas). Implementation tips: store annotation metadata in your app to link annotations to internal events, handle LIMIT_REACHED errors gracefully (inform the merchant or garbage-collect old app-owned annotations), and provide merchant controls for annotation visibility.

Reconciliation with ad platforms and attribution differences

Practical reconciliation guidance: - Expect differences: Shopify's documented CAC attribution uses "last paid click within 7 days," which may differ from Google/Meta windows and attribution models. - If external campaign IDs are absent from shop_campaign_insights, provide merchant workflows to map Shopify campaign names to platform IDs or apply heuristic string matching, then reconcile totals. - Start with aggregate comparisons and tolerance thresholds, then run sampled per-order audits to identify systematic differences. Maintain documentation of Shopify's attribution rules to explain discrepancies to merchants.

Operational considerations, limits, and security

Operational checklist: - Scopes: shopifyqlQuery requires read_reports; annotation mutations require write_analytics_annotations. Some response fields are Level 2 protected customer data — follow Shopify's PII rules and minimize storage of protected fields. - Rate limits and performance: Admin GraphQL limits and query complexity may constrain high-frequency hourly syncs on large stores — test on large merchants and implement batching or sampling strategies where necessary. - Error handling: implement retries/backoff, parse-error guards, and handle LIMIT_REACHED for annotations. - Monitoring: log query latency, failure rates, and data-volume trends; test in dev stores before production rollout.

Limitations, uncertainties and pre-production checklist

Known uncertainties and risks (require operational validation): - Shopify documents annotation quotas but does not publish exact per-app numbers — design for LIMIT_REACHED handling. - Practical throughput and latency for large hourly timeseries queries against shop_campaign_insights are not specified — empirically test on large stores. - The presence of third‑party ad platform campaign IDs in the schema is unclear; if absent, you will need merchant mapping flows. - The retention window for raw underlying event records or maximum history available via ShopifyQL is not explicitly stated — verify if you need deep historical retention. - Refund handling: docs state refunds are excluded for shop_campaign_sales but edge cases in reconciliation remain and should be tested. Pre-production checklist: test queries on a dev store, run end-to-end sync against at least one large merchant, measure latency and error modes, validate mapping of fields to KPIs, and verify PII handling policies.

Appendix: example data model and reconciliation SQL

Suggested target tables (illustrative): - campaigns(campaign_id PK, shop_campaign_name, shop_id, source_created_at, source_updated_at) - campaign_metrics(id PK, campaign_id FK, period_start, period_granularity, currency, ad_spend, sales, customers, orders, roas, cac, aov, raw_table_data JSON) Sample reconciliation SQL (compare Shopify spend vs platform export): SELECT s.shop_id, s.campaign_name, SUM(s.ad_spend) AS shopify_spend, SUM(p.exported_spend) AS platform_spend, SUM(s.ad_spend) - SUM(p.exported_spend) AS diff FROM campaign_metrics s LEFT JOIN exported_ad_spend p ON p.shop_id = s.shop_id AND p.external_campaign_id = s.external_campaign_id WHERE s.period_start BETWEEN '2026-07-01' AND '2026-07-31' GROUP BY s.shop_id, s.campaign_name; Adapt joins and matching keys according to whether external campaign IDs exist or whether you apply name-based matching.

Conclusion

shop_campaign_insights in ShopifyQL is a durable first-party source for merchant-attributed campaign KPIs and simplifies ingestion for many analytics use cases. Use it to speed product development for embedded dashboards and to provide reconciliation features, but plan for attribution differences, PII governance, annotation quotas, and empirical performance testing on large merchants. Treat Shopify's metrics as an authoritative merchant-facing view while building processes to reconcile with ad-platform data where needed.

Frequently Asked Questions

Does using shop_campaign_insights require new OAuth scopes? Querying shop_campaign_insights via Admin GraphQL shopifyqlQuery requires the read_reports scope. Creating annotations requires write_analytics_annotations. Some returned fields may be Level 2 protected customer data and must be handled per Shopify rules.

Can I rely solely on Shopify data and skip connecting ad platforms? Shopify provides merchant-attributed campaign KPIs which may reduce the need for some platform connectors, but differences in attribution windows and possible absence of external campaign IDs mean you should plan reconciliation workflows with ad platforms when exact platform-level attribution is required.

What sync cadence should I choose? Choose hourly for near-real-time dashboards (monitor rate limits and performance) or daily for aggregated pipelines. Use shop_campaign_insights_last_updated_at for incremental syncs.

Conclusion

The addition of shop_campaign_insights to ShopifyQL gives analytics teams a practical, vendor-supported source of campaign metrics to ingest and model. To operationalize it reliably, implement incremental syncs, respect timezone and currency metadata, follow Shopify's scope and PII requirements, and validate reconciliation strategies with ad platforms in testing before production rollout.