How Much of My Shopify Revenue Came from Meta Ads? Cross-Channel Attribution in One ChatGPT Query (via Kondado MCP)

How Much of My Shopify Revenue Came from Meta Ads? Cross-Channel Attribution in One ChatGPT Query (via Kondado MCP)

Meta Ads says one thing, Shopify says another, Google Analytics 4 says a third. Each platform uses different attribution windows, and the sum of the parts always exceeds the whole. When you connect ChatGPT to Kondado's MCP, the LLM reconciles everything in a single session — crossing facebook_ad_insights_actions with ga4_sessions and shopify_orders, all in the same data warehouse.

TL;DR:

• Cross-channel attribution reconciles Shopify revenue with Meta and Google touchpoints — the LLM identifies which sales had ad interactions in the last 7-30 days
• Platform differences come from distinct attribution windows — Meta uses 7-day click + 1-day view, GA4 uses data-driven model, Shopify attributes to last non-direct source
• Setup in 3 steps — replicate Meta Ads + Shopify + GA4 via Kondado, connect destination to MCP, ask in natural language

Why Meta, Shopify, and GA4 Never Match

Different Attribution Windows

Each platform defines "conversion" differently. Meta Ads considers a purchase attributable if the user clicked the ad in the last 7 days or viewed it in the last 7 days (7d click + 1d view). Google Analytics 4 uses data-driven attribution with default 30-day window for acquisition and 90-day for conversion. Shopify, by default, attributes to last non-direct click with 30-day window.

Result: one purchase can be counted by all three platforms (triple counting) or by none, depending on the customer journey.

Distinct Attribution Models

Meta uses positional models (some conversions first-click, others last-click, depending on algorithm). GA4 uses data-driven attribution that distributes credit throughout the funnel. Shopify is simplified last-click. When you sum the reports, the total exceeds 100% of actual revenue.

Timezones and Deduplication

Meta and GA4 use different timezones by default. Orders placed at 11 PM can fall on different days in reports. Additionally, none of the three platforms deduplicate natively — they don't know the same purchase was reported in the others.

Currency and Fees

Meta reports spend in account currency (usually USD for international accounts), but conversion values come from the pixel and may be in different currencies depending on store configuration. Shopify and GA4 have their own currency conversion rules. Comparing ROAS directly between platforms requires normalization no tool does automatically.

The Analysis MCP Solves

The E-commerce Operator's Question

"Of total Shopify revenue in April, how much came from carts with Meta touchpoints in the last 7 days, and what was the real channel ROAS?"

This question seems simple but requires three data sources:

1. Shopify: total revenue and session origin (UTM)
2. GA4: sessions and client_id for cross-device matching
3. Meta Ads: campaigns, spend, and attributed conversions

The Join LLM Executes via KSQL

When you ask ChatGPT connected to Kondado's MCP, the LLM generates a KSQL JSON query equivalent to:

SELECT 
  DATE(so.created_at) as date,
  fa.campaign_name,
  SUM(so.total_price) as shopify_revenue,
  SUM(fa.spend) as meta_spend,
  SUM(fa.a_offsite_conversion_fb_pixel_purchase) as meta_pixel_conversions,
  SUM(so.total_price) / NULLIF(SUM(fa.spend), 0) as real_roas
FROM shopify_orders so
LEFT JOIN ga4_sessions ga ON so.client_id = ga.client_id 
  AND DATE(so.created_at) = DATE(ga.event_date)
LEFT JOIN facebook_ad_insights fa ON ga.campaign = fa.campaign_name 
  AND DATE(so.created_at) = fa.metric_date
WHERE so.created_at >= '2024-04-01' 
  AND so.created_at < '2024-05-01'
  AND (ga.source = 'facebook' OR ga.source = 'instagram' 
       OR fa.campaign_name IS NOT NULL)
GROUP BY 1, 2
ORDER BY shopify_revenue DESC

What this query does:

• Gets all Shopify orders from April
• Joins with GA4 sessions on same client_id and date (cross-device matching)
• Joins with Meta Ads insights on same campaign and date
• Filters only orders that had Meta/IG origin or Meta touchpoint
• Calculates real ROAS (Shopify revenue / Meta spend)

The Natural Language Response

ChatGPT returns something like:

"In April, $8,450 of your Shopify revenue had Meta touchpoints in the last 7 days. Meta Ads attributed $9,750 in conversions (difference of 15.4% because Meta uses 7-day window vs your 7-day post-touchpoint criteria). Campaign 'Black Friday Preview' had real ROAS of 3.2x vs 4.1x reported by Ads Manager (difference explained by conversions duplicated with Google Ads)."

Kondado vs Official Meta MCP

The central point: the official Meta MCP (mcp.facebook.com/ads) only sees Meta data. It doesn't see Shopify. It doesn't see GA4. It cannot answer "how much did I really sell through Meta" because it has no access to actual store revenue.

Kondado's MCP (https://mcp.kondado.io/mcp) sees all your data in the same data warehouse. One LLM session, all your data.

Technical Caveat: Breakdowns and Off-Meta Conversions

For analysis by region, device, or time of day, there's a documented behavior of Meta Graph API: Type 1 breakdowns (like region, dma, hourly_stats_aggregated_by_advertiser_time_zone) silently zero out off-Meta conversion fields (a_offsite_conversion_fb_pixel_purchase, a_add_to_cart, etc.).

What this means: if you ask the LLM "what ROAS by state or region", and the Meta pipeline is configured with region breakdown, the LLM will receive zeros for conversions — and calculate ROAS = ∞ (division by zero).

Kondado's solution: Learning 20 documents that for region/time analysis, the pipeline must: 1. Run without Type 1 breakdown in main table 2. Enable __kdd_include_action_details=on in facebook_ad_insights_actions sub-table 3. Manually JOIN sub-table with main on ad_id

When your pipeline is configured this way, MCP returns correct data for geographic analysis.

Setup in 3 Steps (Summary)

1. Replicate the data: Create Meta Ads + Shopify + GA4 pipelines in Kondado, all pointing to the same destination (BigQuery, PostgreSQL, Redshift, or Via Kondado).
2. Connect to MCP: In ChatGPT (Desktop or web with OAuth 2.1 + DCR), add an MCP connection with URL https://mcp.kondado.io/mcp and authenticate via app.kondado.com.br/oauth/authorize.
3. Ask: "How much of my Shopify revenue in [month] came from Meta Ads, what's the real ROAS by campaign, and where does it diverge from Ads Manager?"

For complete setup details, see the full guide with 10 analyses.

Frequently Asked Questions

What is cross-channel attribution?

Cross-channel attribution is the practice of reconciling conversions between different advertising and analytics platforms. Since each platform uses different attribution windows and models, the numbers never match — and the sum of parts exceeds 100% of actual revenue. Cross-channel analysis identifies the overlap and calculates each channel's real value.

Why does Meta report more conversions than Shopify?

Meta Ads uses 7-day click + 1-day view attribution window. If a user clicked the ad and bought 5 days later directly on the site (no UTM), Meta counts the conversion — but Shopify attributes to last non-direct source (which may be "direct" or "organic search"). Additionally, Meta may count the same purchase multiple times if the user interacted with multiple ads.

Does Kondado MCP replace Business Manager?

No. MCP is an analysis channel — it exposes your replicated data to LLMs for ad-hoc questions. Business Manager remains the official campaign management interface. Use MCP for questions Business Manager doesn't answer natively: cross-channel reconciliation, joins with CRM/ERP data, cohort analysis.

Do I need to know SQL to use MCP?

No. You ask questions in natural language (Portuguese or English), and the LLM translates to KSQL JSON underneath. The response is in natural language with reasoning explanations. Knowing SQL helps understand what's happening, but it's not required.

Which destinations work with MCP?

Kondado's MCP works with any destination replicated by the platform: BigQuery, PostgreSQL, MySQL, Redshift, SQL Server, Amazon S3, Via Kondado, Google Sheets, and Excel. For use with ChatGPT/Claude, we recommend BigQuery, PostgreSQL, or Via Kondado for lower latency.

Try Via Kondado with MCP for 14 days — get started now