DTC jewelry/accessories brand

Executive Marketing Analytics Dashboard

Power BI / Looker Studio mockup BigQuery semantic layer
Selected view Executive Overview
Active drill Campaign
AI summary Revenue is pacing above target, but attribution variance needs review.
Total Revenue $1.84M +18.4% vs prior
Net Revenue $1.62M +15.2%
Orders 28,430 +9.1%
AOV $64.72 +3.8%
Total Ad Spend $412K +22.7%
Blended ROAS 4.47x +0.3x
MER 3.94x Target 3.5x
CPA $18.22 +6.5%
CAC $31.84 New customers
Gross Profit $1.07M 58.1% margin
Contribution Margin 35.7% +2.2 pts
Net Profit $382K 20.8% margin
Refund Rate 4.8% +0.6 pts
Discount Rate 11.3% -1.1 pts
Shopify vs GA4 Revenue Variance 7.6% Needs audit
Shopify vs Ad Platform Purchase Variance 12.4% Meta largest gap
AI Readiness Score 82/100 Production candidate

Revenue & Shopify Performance

Revenue trend by date

Ad Spend Trend by Date

Revenue vs spend combo chart
Revenue Ad spend

ROAS by Channel

Ranked bars
Google Brand8.2x
Email / SMS6.9x
Meta Prospecting3.8x
TikTok2.4x
Influencer1.8x

GA4 Funnel Drop-off

Session to purchase
Sessions 742K
Product views 475K
Add to cart 230K
Checkout 134K
Purchases 28.4K

AI Readiness Status

Model input health
82

Ready for weekly performance summaries

Needs work on identity joins and creative metadata

Blocked for missing transaction IDs in GA4

Campaign Performance Table

Conditional formatting
Campaign Spend Revenue ROAS CPA CM%
Meta | Bridal Core | Prospecting $92,480 $356,900 3.86x $21.40 34%
Google | Brand Search $28,200 $231,800 8.22x $7.62 52%
TikTok | Gift Guide | Broad $54,130 $129,500 2.39x $29.75 21%
Meta | Retargeting | Cart $31,440 $176,200 5.60x $12.12 45%

Ad Set / Ad Group Drill-down

Expandable matrix
Level Clicks CVR Purchases Variance
Meta Prospecting 94,210 2.8% 2,640 -8.1%
Broad | 25-44 | US 41,850 3.1% 1,298 -2.4%
Lookalike | VIP Buyers 27,520 2.4% 660 -16.8%
Interest | Wedding 24,840 2.7% 682 -9.5%

Creative Performance

Hook, format, offer, landing page
Creative Format CTR ROAS Thumb-stop Status
Stacked Rings UGC Reel 2.9% 4.6x 37% Scale
Gold Hoops Studio Carousel 1.7% 3.2x 24% Hold
Gift Box Offer Static 1.1% 2.1x 18% Refresh

Product Profitability Table

SKU-level contribution
Product / SKU Units Net Rev Gross Margin CM After Ads
Layered Necklace / LN-GD-18 5,420 $298K 64% $94K
Mini Hoop Set / HP-GLD-02 7,890 $237K 58% $61K
Pearl Bracelet / PB-WHT-01 2,980 $149K 46% $18K

Attribution & Discrepancy Audit

Dedicated validation layer for Shopify, GA4, and ad platform alignment
Revenue variance 7.6%

GA4 revenue excludes shipping and delayed refunds.

Purchase variance 12.4%

Ad platforms overreport vs Shopify orders.

Missing transaction IDs 1,284

Checkout events without order_id mapping.

Duplicate orders 86

Repeat order IDs across GA4 purchase hits.

Unmapped UTMs 14.1%

Traffic not mapped to reporting channel rules.

Timezone mismatch 4 hrs

Shopify store timezone differs from GA4 property.

Currency mismatch 3 markets

Orders require FX normalization in BigQuery.

Refund adjustment mismatch $38K

Refund timing is not aligned by transaction date.

Tax and shipping inclusion mismatch $74K

Revenue definitions differ by source.

Platform attribution difference 18.7%

Click/view windows need normalized comparison.

Shopify vs GA4 Discrepancy

Revenue comparison
Shopify$1.84M
GA4$1.70M
Variance-$140K

Purchase Variance by Platform

Shopify baseline
Meta+16.8%
Google+8.5%
TikTok+22.1%
GA4-7.6%

Data Quality Score by Source

Completeness, validity, joins
Shopify96
GA474
Meta Ads81
Google Ads88
TikTok Ads69

Customer & LTV Analysis

New vs returning cohorts
Cohort
Customers
90D LTV
Repeat Rate
New customers
12,940
$118
18%
Returning customers
8,520
$214
42%
VIP buyers
1,180
$486
71%

BigQuery AI-Ready Model Inputs

Reporting layer readiness

Unified transaction grain with order_id

Normalized channel grouping and UTM rules

Product, SKU, margin, refund, and discount facts

Creative taxonomy and hook metadata

Identity stitching for customer LTV features

GA4 transaction ID repair queue