Reconcile Facebook Ads Spend with Shopify Revenue
Attributing Facebook Ads spend to actual Shopify revenue fails at the join key. Shopify exports expose Order Name (#1042) but strip UTMs from top-level columns. Facebook groups spend by Ad Set ID. There is no shared key for a direct VLOOKUP. The bridge: Shopify stores UTM parameters in note_attributes as a stringified JSON blob: [{"name":"utm_campaign","value":"bf-2024"}]. Meta's Attribution Settings default to '7-day click, 1-day view'; Shopify uses last-click 30-day. These mismatched windows alone explain 20–30% of the discrepancy before data quality issues enter. This workflow extracts UTM tags from Note Attributes via regex, parses utm_campaign, and joins the two datasets using DuckDB-Wasm's hash join—entirely inside your browser tab.
Why This Matters
When media buyers can't tie Campaign IDs to Order IDs, they default to Shopify's last-click dashboard—which underreports social conversions by 30–40% due to attribution window mismatch. Profitable campaigns get paused due to fake negative ROAS. Attempting this join in Excel with 365 days of ad logs (500K+ rows) guarantees a crash. Uploading both datasets to a remote Python server violates GDPR. Do it locally—zero bytes leave your browser.
Why Excel & Python Fail Here
The fundamental problem: no shared primary key exists between Meta's Ads Manager export (keyed by ad_id) and Shopify's order export (keyed by Order Name). The ONLY bridge is UTM campaign strings buried in Shopify's note_attributes JSON blob. If UTMs were never tagged (common with boosted posts), reconciliation is mathematically impossible—flag these as 'unattributed' rather than forcing a lossy join. Excel cannot parse nested JSON across 500K rows. Python's json.loads() works but requires environment setup.
| Criterion | Excel / Sheets | Python / Cloud | DataPrep (This Page) |
|---|---|---|---|
| Max Rows | ~150K (crashes) | RAM-limited | 1M+ via Wasm |
| Setup Time | Manual formulas | 30–60 min env setup | 0 seconds |
| Data Privacy | Local but fragile | Uploads to cloud | 100% in-browser |
| Cost | License fees | EC2 / SaaS $49+/mo | Free forever |
Step-by-Step Solution
We will use the Local VLOOKUP (Joiner) to execute this entirely in your browser.
- 1
Upload both CSVs: Meta Ads export + Shopify orders
Drag and drop both files. The tool identifies the Meta export by detecting 'Campaign ID' and 'Amount spent' columns, and the Shopify export by 'Name' and 'Note Attributes'.
- 2
Extract UTM campaigns from Note Attributes
The tool applies regex to parse the JSON blob in Note Attributes: [{"name":"utm_campaign","value":"X"}] → extracts 'X' into a new column. Orders without UTMs are flagged as 'unattributed'.
- 3
Join and compute true ROAS
Hash join on utm_campaign → Campaign Name. Output shows: Campaign, Total Spend, Matched Revenue, True ROAS, and Unattributed Revenue. Compare against Meta's reported ROAS to quantify the attribution gap.
Privacy Guarantee: Zero Bytes Uploaded
All processing happens in your browser via WebAssembly. Your data never touches a server. Try it: disconnect your Wi-Fi right now—the tool will still work.
Common Errors & Fixes
| Error / Symptom | Root Cause | Fix |
|---|---|---|
ROAS shows 0.0 for all campaigns | UTM campaign names don't match Meta's Campaign Name due to case differences or trailing spaces | Normalize both sides to lowercase + trim before joining. The tool does this automatically. |
60% of orders show 'unattributed' | Boosted posts and dynamic ads don't carry UTM parameters in Shopify's note_attributes | This is expected. Unattributed orders cannot be reconciled without server-side CAPI integration. Report them separately. |
Frequently Asked Questions
Why does Meta's ROAS differ from my Shopify-based calculation?
Can I automate this with Shopify's API instead?
Ready to clean your data?
100% local processing · Zero uploads · Blazing fast
Trusted by 2,400+ data teams · 18M+ rows processed monthly