Cross-border Ecommerce Performance

I checked the uploaded first-week workbooks and mapped the source structure across Amazon, Shopify, Meta Ads, and SKU costs.

First-week files inspected:

• Amazon_US_Orders_2026-05-18_to_2026-07-12.xlsx: 302 raw rows, 301 rows after exact deduplication.
• Amazon_US_Returns_2026-05-25_to_2026-07-12.xlsx: 23 rows.
• Internal_SKU_Costs.xlsx: 8 SKU cost rows.
• Meta_Ads_Daily_2026-05-18_to_2026-07-12.xlsx: 168 rows.
• Shopify_Orders_2026-05-18_to_2026-07-12.xlsx: 252 raw line rows, 251 rows after exact deduplication, representing 199 unique Shopify orders.

The files can support a cross-border ecommerce performance dataset covering revenue, refunds, SKU costs, gross profit, and Meta Ads efficiency. The main data modeling point is that Shopify order-level financials need to be separated from Shopify line items, otherwise order totals will be double-counted when analyzing SKU-level performance.

---

I will build the ecommerce performance dataset from the structured exports and keep the business definitions inside GoalfyData so later analysis and dashboards reuse the same logic.

Confirmed dataset:

Cross-border Ecommerce Performance
Dataset ID: lw1h9uzctm1h
Schema: uds_lw1h9uzctm1h
Created: 2026-07-23T13:30:27.443Z
Source type: manual
Source files: first-week ecommerce exports covering 2026-05-18 to 2026-07-12

The data represents a cross-border ecommerce operating scenario across:

• Shopify orders
• Shopify order lines
• Amazon order lines
• Amazon returns
• SKU costs
• Meta Ads daily performance

The goal is to show how GoalfyData solves recurring ecommerce analysis problems such as inconsistent metric definitions, unclear profit, refund leakage, SKU-level COGS, and ad-efficiency diagnosis.

---

Dataset Structure
Cross-border Ecommerce Performance
ID: lw1h9uzctm1h
Schema: uds_lw1h9uzctm1h
Source: manual import from ecommerce exports
Mode: manual import / direct dataset edits; no Managed Refresh schedule is enabled for these tables

Tables

cross_border_ecom_sku_costs — 8 rows
Primary key: sku

column	type	nullable	meaning
sku	text	no	normalized upper-case product identifier used as the product join key
product_name	text	no	product display name
category	text	no	internal product category
unit_cost_usd	numeric	no	product unit cost in USD
effective_from	date	no	date from which this cost record applies
active	boolean	no	whether the SKU cost record is active

cross_border_ecom_amazon_order_lines — 344 rows
Primary key: amazon_order_item_id

column	type	nullable	meaning
amazon_order_id	text	no	Amazon order identifier
amazon_order_item_id	text	no	Amazon order-line key after deduplication
purchase_date	timestamp	no	order purchase timestamp
order_status	text	no	Amazon order status
fulfillment_channel	text	no	Amazon fulfillment channel
sales_channel	text	no	Amazon sales channel
sku	text	no	normalized SKU used to join SKU costs
asin	text	no	Amazon ASIN
item_status	text	no	Amazon item status
quantity	integer	no	units sold
currency	text	no	transaction currency
item_price	numeric	no	Amazon item price
shipping_price	numeric	no	shipping amount charged
item_promotion_discount	numeric	no	item-level promotion discount
ship_promotion_discount	numeric	no	shipping-level promotion discount
ship_country	text	no	shipping destination country

cross_border_ecom_amazon_returns — 28 rows
Primary key: amazon_order_item_id

column	type	nullable	meaning
return_request_date	timestamp	no	return request timestamp
amazon_order_id	text	no	Amazon order identifier
amazon_order_item_id	text	no	Amazon order item identifier and return key
sku	text	no	normalized SKU used to join SKU costs
return_quantity	integer	no	returned unit quantity
return_reason	text	no	return reason reported by Amazon
return_status	text	no	Amazon return workflow status
refund_amount	numeric	no	refund amount
currency	text	no	refund currency

cross_border_ecom_shopify_orders — 219 rows
Primary key: name

column	type	nullable	meaning
name	text	no	Shopify order name and order-level key
created_at	timestamp	no	Shopify order creation timestamp
financial_status	text	no	payment/refund financial status
fulfillment_status	text	no	fulfillment status
currency	text	no	order currency
subtotal	numeric	no	order subtotal amount
shipping	numeric	no	shipping amount
taxes	numeric	no	tax amount
total	numeric	no	Shopify total order amount
discount_amount	numeric	no	discount amount
refunded_amount	numeric	no	refunded amount
refunded_at	timestamp	yes	refund timestamp when present
source	text	no	source or channel
shipping_country	text	no	destination country
tags	text	yes	Shopify order tags

cross_border_ecom_shopify_order_lines — 277 rows
Primary key: lineitem_id

column	type	nullable	meaning
name	text	no	Shopify order name joining to order financials
lineitem_id	text	no	Shopify line-item key after deduplication
created_at	timestamp	no	order creation timestamp
financial_status	text	no	financial status
fulfillment_status	text	no	fulfillment status
currency	text	no	line currency
lineitem_quantity	integer	no	units on the line item
lineitem_name	text	no	product line-item name
lineitem_price	numeric	no	line unit price
lineitem_sku	text	no	normalized SKU used to join SKU costs
source	text	no	source or channel
shipping_country	text	no	destination country
tags	text	yes	Shopify order tags

cross_border_ecom_meta_ads_daily — 189 rows
Primary key: reporting_starts, campaign_name, ad_set_name

column	type	nullable	meaning
reporting_starts	date	no	report start date
reporting_ends	date	no	report end date
campaign_name	text	no	Meta campaign name
ad_set_name	text	no	Meta ad set name
delivery	text	no	campaign delivery status
attribution_setting	text	no	Meta attribution window
results	integer	no	reported website purchases count
result_indicator	text	no	result type label
reach	integer	no	Meta reach
impressions	integer	no	Meta impressions
link_clicks	integer	no	link clicks
amount_spent_usd	numeric	no	Meta spend in USD
purchases	integer	no	attributed purchase count
purchase_conversion_value	numeric	no	attributed purchase value in USD
ctr_link_click_through_rate	numeric	no	link click-through rate
cpc_cost_per_link_click	numeric	no	cost per link click
purchase_roas_return_on_ad_spend	numeric	no	Meta-reported purchase ROAS

business_rules — 8 rows
Primary key: rule_key

column	type	nullable	meaning
rule_key	text	no	application-facing rule key
rule_group	text	yes	rule group
label_display	text	yes	display label
label	text	yes	English label
value	jsonb	yes	structured rule value
definition_display	text	yes	display definition
definition	text	yes	English definition
enabled	boolean	yes	whether the dashboard rule is active
updated_at	timestamptz	yes	update timestamp

Relations

• cross_border_ecom_shopify_order_lines.name → cross_border_ecom_shopify_orders.name
• cross_border_ecom_shopify_order_lines.lineitem_sku → cross_border_ecom_sku_costs.sku
• cross_border_ecom_amazon_order_lines.sku → cross_border_ecom_sku_costs.sku
• cross_border_ecom_amazon_returns.amazon_order_item_id → cross_border_ecom_amazon_order_lines.amazon_order_item_id
• cross_border_ecom_amazon_returns.sku → cross_border_ecom_sku_costs.sku
• cross_border_ecom_meta_ads_daily.reporting_starts can be compared with cross_border_ecom_shopify_orders.created_at at date grain
• cross_border_ecom_meta_ads_daily.reporting_starts can be compared with cross_border_ecom_amazon_order_lines.purchase_date at date grain

Rules

• Preserve Shopify order-level financials separately from Shopify line items to avoid double-counting order totals.
• Normalize SKU fields before joining platforms.
• Amazon gross revenue = item price + shipping price - item promotion discount - shipping promotion discount.
• Shopify gross revenue = subtotal + shipping - discount amount.
• Net revenue = gross revenue - refunds.
• COGS = units sold × unit_cost_usd.
• Gross profit before ads = net revenue - COGS.
• Contribution after ads = gross profit before ads - Meta spend.
• Meta Ads should be compared at date/campaign grain, not treated as direct order-level attribution.

---

Done. I created and deployed a dashboard app for this dataset.

App: ecommerce-performance-dashboard
App ID: ecommerce-performance-de-a8898e649df8
URL: https://app-814c7318104b-ecommerce-performance-de-a8898e649df8.goalfydata.app
Current deploy: FzcLp4mpXYuIYklA0dEPvQ
Current visual direction: dark AI ops control room
Bound dataset: Cross-border Ecommerce Performance (lw1h9uzctm1h)

The current dashboard includes:

• Live contribution trace
• KPI telemetry strip
• Profit waterfall
• Category margin terminal
• Meta Ads efficiency
• Source contribution
• Diagnostic console with Signals, Refunds, and Rules

Verification passed:

• Frontend smoke test
• Frontend production build
• Backend TypeScript build
• GoalfyData preflight
• Production dependency audit
• Deployment status online

---

Analysis complete. I queried the dataset after the first-week load and used the governed profit logic.

First-week Dataset Snapshot

• Time range: 2026-05-18 to 2026-07-12
• Shopify orders: 199
• Shopify order lines: 251
• Amazon order lines: 301
• Amazon returns: 23
• Meta Ads daily rows: 168
• SKU cost records: 8

First-week Metric Summary

Metric	Value
Total net revenue	$17,400.11
Total COGS	$4,907.00
Total refunds	$1,338.65
Meta spend	$27,967.94
Gross profit before ads	$12,493.11
Contribution after ads	-$15,474.83
Meta ROAS	1.65
Blended MER	0.62

First-week Findings

1. The first-week data already shows the core profit problem.
   Net revenue and gross profit exist, but Meta spend is larger than gross profit before ads, so contribution after ads is negative.

2. The analysis is useful because it makes the metric path visible.
   It separates revenue, refunds, COGS, ad spend, and contribution instead of collapsing everything into one revenue chart.

3. The next useful step is to update the dataset with the following week.
   Adding the July 13-19 exports can show whether the same profit pattern continues after the first-week baseline.

---

Done. I updated the ecommerce dataset with the second-week exports and validated the full dataset.

Second-week files processed:

• UPDATE_Amazon_US_Orders_2026-07-13_to_2026-07-19.xlsx: 44 raw rows, 43 rows after exact deduplication.
• UPDATE_Amazon_US_Returns_2026-07-13_to_2026-07-19.xlsx: 5 rows.
• UPDATE_Meta_Ads_Daily_2026-07-13_to_2026-07-19.xlsx: 21 rows.
• UPDATE_Shopify_Orders_2026-07-13_to_2026-07-19.xlsx: 26 line rows, representing 20 unique Shopify orders.

Tables after the second-week update:

• cross_border_ecom_sku_costs: 8 rows
• cross_border_ecom_amazon_order_lines: 344 rows
• cross_border_ecom_amazon_returns: 28 rows
• cross_border_ecom_shopify_orders: 219 rows
• cross_border_ecom_shopify_order_lines: 277 rows
• cross_border_ecom_meta_ads_daily: 189 rows

The original creation task was:

4j3acnzwajfi
Task name: Build ecommerce dataset tables
Created: 2026-07-23 21:50 Asia/Shanghai
Task description: Create and load structured ecommerce base tables from Amazon, Shopify, Meta Ads, and SKU cost exports into the confirmed GoalfyData dataset.

Validation and persistence completed:

• Tables were created and imported through GoalfyData data-plane operations.
• Metadata was registered for all six original ecommerce tables.
• Logical relations were saved.
• Governance rules were saved.
• Usage guide was written.
• Initial import task completion was recorded at 2026-07-23 22:01 Asia/Shanghai.

The second update task was:

utnydv4cpu9g
Task name: Update July 13-19 ecommerce weekly exports
Created: 2026-07-24 10:30 Asia/Shanghai
Update scope: deduplication, SKU normalization, late refund preservation, import validation, and post-update checks.
Task completion was recorded at 2026-07-24 10:33 Asia/Shanghai.

Cleaning rules recorded at creation:

• Exact duplicate export rows were removed before import for Amazon order lines and Shopify order lines.
• This removed 1 Amazon row and 1 Shopify line row in the initial load.
• SKU join fields were trimmed and normalized to upper case before import.

---

Analysis complete. I queried the live GoalfyData dataset and found:

Dataset Snapshot

• Time range: 2026-05-18 to 2026-07-19
• Shopify orders: 219
• Shopify order lines: 277
• Amazon order lines: 344
• Amazon returns: 28
• Meta Ads daily rows: 189
• SKU cost records: 8
• Dashboard-facing business rules: 8
• Platform governance rules: 19
• Logical relations: 7

Metric Summary

Metric	Value
Total net revenue	$19,383.28
Total COGS	$5,467.20
Total refunds	$1,536.85
Meta spend	$31,724.64
Gross profit before ads	$13,916.08
Contribution after ads	-$17,808.56
Meta ROAS	1.66
Blended MER	0.61

Category Margin Snapshot

Category	Units	Estimated sales	COGS	Gross profit	Gross margin
Travel Organization	88	$2,386.00	$569.60	$1,816.40	76.1%
Electronics	40	$1,680.00	$480.00	$1,200.00	71.4%
Travel Security	73	$1,432.00	$331.40	$1,100.60	76.9%
Drinkware	40	$1,440.00	$380.00	$1,060.00	73.6%
Travel Comfort	34	$1,326.00	$387.60	$938.40	70.8%
Bags	31	$837.00	$220.10	$616.90	73.7%

Quality Checks

• Primary-key duplicate checks returned 0 issues for Amazon order lines, Amazon returns, Shopify orders, Shopify order lines, SKU costs, and Meta Ads daily rows.
• Key-column null checks returned 0 issues.
• The dataset has consistent date coverage across Shopify, Amazon, and Meta Ads: 2026-05-18 to 2026-07-19.

Key Findings

1. Revenue exists, but contribution is negative.
   The dataset shows $19.4K in net revenue and $13.9K gross profit before ads, but Meta spend of $31.7K pushes contribution after ads to -$17.8K.

2. The problem is not SKU margin alone.
   Category-level Shopify margins are high before ad spend, mostly around 70% to 77%. This means the immediate diagnosis is not simply “products have no margin”; the ad-spend layer is the biggest pressure point in this sample.

3. Refunds are material but not the main loss driver.
   Refunds reduce revenue by $1.5K. They matter for revenue quality and SKU diagnosis, but they are much smaller than Meta spend.

4. Meta Ads must not be over-attributed.
   The dataset explicitly stores Meta Ads at daily campaign/ad set grain. It can support ROAS, blended MER, and date-level comparison, but not direct order-level attribution.

5. The dataset shows GoalfyData’s governance value.
   Profit, COGS, refund deduction, valid order logic, SKU normalization, and attribution boundaries are persisted as rules, so future agents and dashboards can reuse the same definitions.

---

Done. I shared Cross-border Ecommerce Performance with u***@example.com.

Access scope:

• Included: cross_border_ecom_sku_costs
• Included: cross_border_ecom_amazon_order_lines
• Included: cross_border_ecom_amazon_returns
• Included: cross_border_ecom_shopify_orders
• Included: cross_border_ecom_shopify_order_lines
• Included: cross_border_ecom_meta_ads_daily
• Excluded: business_rules

Share ID:
Status: pending
Permission: read-only

The invitation email was sent. If they do not see it, ask them to check spam/junk.

---

Done. I shared the ecommerce performance dashboard with v***@example.com.

Dashboard: ecommerce-performance-dashboard
Visibility: specified email only
Permission: read-only
Status: pending
Email sent: yes

The dashboard stays bound to Cross-border Ecommerce Performance (lw1h9uzctm1h), so the recipient sees the same governed profit, refund, SKU margin, and Meta Ads efficiency definitions.

Share code:

---

Done. I shared Cross-border Ecommerce Performance with w***@example.com.

Access scope:

• Included: full dataset

Status: pending
Permission: read-only

The invitation email was sent. If they do not see it, ask them to check spam/junk.

---

---