← back to Ads Dashboard
Cycle 4 fix (Cody): 30d window + net-of-refunds revenue + unattributed row + auto-campaign flag
bf73874f7022f14e3ea319fa841d26e86131760c · 2026-08-02 19:27:07 -0700 · Steve Abrams
Files touched
M public/index.htmlM server.js
Diff
commit bf73874f7022f14e3ea319fa841d26e86131760c
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Sun Aug 2 19:27:07 2026 -0700
Cycle 4 fix (Cody): 30d window + net-of-refunds revenue + unattributed row + auto-campaign flag
---
public/index.html | 4 ++--
server.js | 45 ++++++++++++++++++++++++++++++---------------
2 files changed, 32 insertions(+), 17 deletions(-)
diff --git a/public/index.html b/public/index.html
index 540496d..be239a5 100644
--- a/public/index.html
+++ b/public/index.html
@@ -150,10 +150,10 @@ async function boot(){
if (camps.length) {
$('campUtmEmpty').hidden = true;
$('campUtmBody').innerHTML = camps.map(c=>`<tr>
- <td><a href="/api/acquisition">${c.campaign}</a></td>
+ <td><a href="/api/acquisition">${c.campaign}</a>${c.automated?' <span class="badge" title="automated feed traffic (product_sync / shop), not a marketing campaign">auto</span>':''}</td>
<td>${c.source}</td><td>${c.medium}</td>
<td>${c.orders}</td><td>$${c.revenue.toLocaleString()}</td>
- </tr>`).join('') + `<tr style="font-weight:650"><td colspan="3">${acq.totals.utm_tagged||0} of ${acq.totals.orders} orders UTM-tagged</td><td colspan="2"></td></tr>`;
+ </tr>`).join('') + `<tr style="font-weight:650"><td colspan="3">${acq.totals.utm_tagged||0} of ${acq.totals.orders} orders UTM-tagged · ${acq.window} · ${acq.totals.refunded_netted||0} refunds netted</td><td colspan="2"></td></tr>`;
} else {
$('campUtmBody').innerHTML='';
$('campUtmEmpty').hidden = false;
diff --git a/server.js b/server.js
index 6483a3f..fafd11e 100644
--- a/server.js
+++ b/server.js
@@ -55,16 +55,25 @@ function classifyChannel(referrer, sourceName) {
async function fetchAcquisition() {
if (!ORDERS_TOKEN) return { present: false, reason: 'SHOPIFY_ORDERS_TOKEN not set on this host', source: 'shopify-orders' };
if (_acqCache.data && Date.now() - _acqCache.at < 10 * 60 * 1000) return _acqCache.data;
- const url = `https://${SHOP}/admin/api/2024-10/orders.json?status=any&limit=250&fields=id,created_at,source_name,referring_site,landing_site,total_price`;
+ const WINDOW_DAYS = 30;
+ const sinceISO = new Date(Date.now() - WINDOW_DAYS * 864e5).toISOString();
+ const url = `https://${SHOP}/admin/api/2024-10/orders.json?status=any&limit=250&order=created_at+desc&created_at_min=${encodeURIComponent(sinceISO)}&fields=id,created_at,cancelled_at,financial_status,source_name,referring_site,landing_site,total_price`;
const r = await fetch(url, { headers: { 'X-Shopify-Access-Token': ORDERS_TOKEN } });
if (!r.ok) return { present: false, reason: `shopify ${r.status}`, source: 'shopify-orders' };
const j = await r.json();
- const orders = j.orders || [];
+ const raw = j.orders || [];
+ // exclude cancelled — not a real acquisition; count refunds separately, net them from revenue
+ const orders = raw.filter(o => !o.cancelled_at);
+ const REFUNDED = new Set(['refunded', 'partially_refunded', 'voided']);
const bySource = {}, byChannel = {}, byCampaign = {};
- let revenue = 0, utmTagged = 0;
+ let revenue = 0, utmTagged = 0, refundedCount = 0, capped = raw.length >= 250;
const round2 = (n) => Math.round(n * 100) / 100;
+ const dates = [];
for (const o of orders) {
- const rev = Number(o.total_price || 0);
+ if (o.created_at) dates.push(o.created_at);
+ const isRefund = REFUNDED.has(o.financial_status);
+ if (isRefund) refundedCount++;
+ const rev = isRefund ? 0 : Number(o.total_price || 0); // net of refunds/voids
const ch = classifyChannel(o.referring_site, o.source_name);
byChannel[ch] = byChannel[ch] || { orders: 0, revenue: 0 };
byChannel[ch].orders++; byChannel[ch].revenue += rev;
@@ -72,16 +81,20 @@ async function fetchAcquisition() {
bySource[s] = (bySource[s] || 0) + 1;
revenue += rev;
// UTM attribution from the landing_site query string
+ let camp = null, src = '?', med = '?';
try {
const q = new URL(o.landing_site || '', `https://${SHOP}`).searchParams;
- const camp = q.get('utm_campaign');
- if (camp) {
- utmTagged++;
- const key = camp + ' · ' + (q.get('utm_source') || '?');
- byCampaign[key] = byCampaign[key] || { campaign: camp, source: q.get('utm_source') || '?', medium: q.get('utm_medium') || '?', orders: 0, revenue: 0 };
- byCampaign[key].orders++; byCampaign[key].revenue += rev;
- }
- } catch { /* malformed landing_site — skip */ }
+ camp = q.get('utm_campaign'); src = q.get('utm_source') || '?'; med = q.get('utm_medium') || '?';
+ } catch { /* malformed landing_site */ }
+ if (camp) {
+ utmTagged++;
+ const key = camp + ' · ' + src;
+ byCampaign[key] = byCampaign[key] || { campaign: camp, source: src, medium: med, automated: /product_sync|shop/i.test(med), orders: 0, revenue: 0 };
+ byCampaign[key].orders++; byCampaign[key].revenue += rev;
+ } else {
+ const u = byCampaign['(unattributed)'] = byCampaign['(unattributed)'] || { campaign: '(unattributed — no utm_campaign)', source: '—', medium: '—', automated: false, orders: 0, revenue: 0 };
+ u.orders++; u.revenue += rev;
+ }
}
const channels = Object.entries(byChannel)
.map(([channel, v]) => ({ channel, orders: v.orders, revenue: round2(v.revenue) }))
@@ -89,11 +102,13 @@ async function fetchAcquisition() {
const campaigns = Object.values(byCampaign)
.map(c => ({ ...c, revenue: round2(c.revenue) }))
.sort((a, b) => b.orders - a.orders);
+ const from = dates.length ? dates.reduce((a, b) => a < b ? a : b).slice(0, 10) : null;
+ const to = dates.length ? dates.reduce((a, b) => a > b ? a : b).slice(0, 10) : null;
const data = {
- present: true, source: 'shopify-orders', shop: SHOP,
+ present: true, source: 'shopify-orders (net of refunds, excl. cancelled)', shop: SHOP,
generated_at: new Date().toISOString(),
- window: `last ${orders.length} orders`,
- totals: { orders: orders.length, revenue: round2(revenue), utm_tagged: utmTagged },
+ window: capped ? `${from} → ${to} (capped at 250 orders — window may be shorter than ${WINDOW_DAYS}d)` : `${from} → ${to} (last ${WINDOW_DAYS}d)`,
+ totals: { orders: orders.length, revenue: round2(revenue), utm_tagged: utmTagged, refunded_netted: refundedCount },
channels, campaigns, by_source: bySource,
};
_acqCache = { at: Date.now(), data };
← ceae088 Cycle 4: real UTM campaign attribution in /api/acquisition +
·
back to Ads Dashboard
·
Cycle 5: daily orders/revenue trend (30d) in /api/acquisitio f15240c →