Overview
| # | Product | Store | Units | Revenue | Avg Cost | BEROAS |
|---|---|---|---|---|---|---|
| Loading… | ||||||
| # | Product | Store | Units | Revenue | Avg Cost | BEROAS |
|---|---|---|---|---|---|---|
| Loading… | ||||||
| # | Product | Store | Units Sold | Refund Events | Rate |
|---|---|---|---|---|---|
| Loading… | |||||
| Store | Gross Revenue↓ | of which deducted | Net Revenue | Orders | AOV | COG% | Ad Spend | Txn Fees | Profit | Profit % | ROAS | Share | ||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| Cust. Ref. | Alert Ref. | Chgbk. | ||||||||||||
| Loading… | ||||||||||||||
| Period | Orders | Revenue | Refunds | COGS | Tx Fees | Ad Spend | VAs | Payroll | Other Costs | Other Income | Net Profit | Margin % | AOV |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| Loading… | |||||||||||||
read_orders, write_orders, read_customers, write_customers, read_draft_orders, read_fulfillments, read_shipping
read_products, write_products, read_inventory, write_inventory, read_locations, read_publications, write_publications
read_orders, write_orders, read_fulfillments, write_fulfillments, read_assigned_fulfillment_orders, write_assigned_fulfillment_orders, read_merchant_managed_fulfillment_orders, write_merchant_managed_fulfillment_orders, read_inventory, read_shipping
read_all_orders, read_assigned_fulfillment_orders, write_assigned_fulfillment_orders, read_custom_fulfillment_services, read_customers, write_customers, read_draft_orders, read_files, read_fulfillment_constraint_rules, read_fulfillments, write_fulfillments, read_gift_cards, read_inventory, write_inventory, read_inventory_shipments, read_inventory_shipments_received_items, read_inventory_transfers, read_locations, read_markets, read_merchant_managed_fulfillment_orders, write_merchant_managed_fulfillment_orders, read_metaobjects, read_order_edits, read_orders, write_orders, read_payment_mandate, read_payment_notifications, read_payment_terms, read_product_feeds, read_product_listings, read_products, write_products, read_publications, write_publications, read_returns, read_shipping, read_shopify_payments_accounts, read_shopify_payments_bank_accounts, read_shopify_payments_disputes, read_shopify_payments_payouts, read_shopify_payments_provider_accounts_sensitive, read_content, read_third_party_fulfillment_orders
How to generate a Shopify access token (shpat_…)
You need: Client ID, Client Secret, and your myshopify URL — all from the custom app in the Shopify Partner / Admin dashboard.
code=… value from the redirect URL in your address bar.https://admin.shopify.com/store/MYSHOPIFY-URL/oauth/authorize?client_id=CLIENT_ID&redirect_uri=https://example.com
https://MYSHOPIFY-URL.myshopify.com/admin/oauth/access_token
{
"client_id": "CLIENT_ID",
"client_secret": "CLIENT_SECRET",
"code": "PASTE_CODE_FROM_STEP_1"
}
"access_token": "shpat_…". Copy that value into the token field above.⚠ The authorisation code from step 1 expires in 60 seconds — complete both steps in one go.
| Code | Shop Name | myshopify URL | Access Code | Live URL | Country | Lang | Store Currency | Ad Currency | Price Rule | Unit Format | Gender | Date Created | Permissions | Finance | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| Loading… | |||||||||||||||
/opt/ganntz-shared/secrets.env via systemd
EnvironmentFile= — no tokens are stored in application code.
A store shows Connected when both
SHOPIFY_XX_DOMAIN and SHOPIFY_XX_TOKEN are present.
| Store | Meta | Total Spend | Net Revenue | ROAS | |||
|---|---|---|---|---|---|---|---|
| Select a date range and click refresh. | |||||||
Ad spend flows from each store's ad platforms into a central Google Sheet and is read here automatically. The sheet has three tabs — Google, Meta, Pinterest — each in wide format: one row per date, one column per store. Google spend is synced via a script installed in each Google Ads account. Meta spend is synced via a Google Apps Script that calls the Facebook Marketing API — one script per Meta ad account, run daily.
ACCOUNT_OVERRIDE to the exact column header value./**
* GANNTZ — Google Ads Daily Spend Sync (v7)
* Install one copy per Google Ads account. No edits needed —
* account name is auto-detected and matched to the sheet header.
* Re-runs are safe; last 7 days are always re-written (self-healing).
*/
var SHEET_ID = '127SZkm9R6Xsp7he4xdVHKlkjOkQzoevIIjJD7DPoqEI';
var TAB = 'Google';
var LOOKBACK_DAYS = 7;
var ACCOUNT_OVERRIDE = ''; // leave empty — or set to exact column header if name differs
function main() {
var ss = SpreadsheetApp.openById(SHEET_ID);
var sheet = ss.getSheetByName(TAB);
if (!sheet) throw new Error('Tab not found: ' + TAB);
var store = ACCOUNT_OVERRIDE || AdsApp.currentAccount().getName();
var tz = AdsApp.currentAccount().getTimeZone();
var header = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
var col = -1;
for (var c = 1; c < header.length; c++) {
if (String(header[c]).trim() === String(store).trim()) { col = c + 1; break; }
}
if (col === -1) {
throw new Error('Account "' + store + '" has no column in the Google tab. ' +
'Header: ' + header.join(', ') +
'. Rename the Ads account or set ACCOUNT_OVERRIDE.');
}
var lastRow = sheet.getLastRow();
if (lastRow < 2) throw new Error('Google tab has no date rows in column A.');
var dates = sheet.getRange(2, 1, lastRow - 1, 1).getValues();
var rowOf = {};
for (var i = 0; i < dates.length; i++) {
var v = dates[i][0];
if (!v) continue;
var key = (v instanceof Date) ? Utilities.formatDate(v, tz, 'yyyy-MM-dd')
: String(v).trim().substring(0, 10);
rowOf[key] = i + 2;
}
var now = new Date();
var start = new Date(now.getTime() - LOOKBACK_DAYS * 86400000);
var startStr = Utilities.formatDate(start, tz, 'yyyy-MM-dd');
var endStr = Utilities.formatDate(now, tz, 'yyyy-MM-dd');
var totals = {};
var res = AdsApp.search(
'SELECT segments.date, metrics.cost_micros FROM campaign ' +
"WHERE segments.date BETWEEN '" + startStr + "' AND '" + endStr + "'");
while (res.hasNext()) {
var r = res.next();
var d = r.segments.date;
totals[d] = (totals[d] || 0) + (r.metrics.costMicros / 1000000);
}
var written = 0, missing = [];
for (var d in totals) {
var row = rowOf[d];
if (!row) { missing.push(d); continue; }
sheet.getRange(row, col).setValue(totals[d]);
written++;
}
Logger.log('[' + store + '] ' + startStr + ' -> ' + endStr +
': wrote ' + written + ' day(s) into column ' + col);
if (missing.length) Logger.log('[' + store + '] no date row yet for: ' + missing.join(', '));
if (written === 0) Logger.log('[' + store + '] nothing written — no spend in window or date rows missing.');
}
⚠ The Google script must be installed separately in each store's own Google Ads account — it writes only to its own column.
This is a Google Apps Script (not a Facebook tool) — it runs on a schedule in Google and calls the Facebook Marketing API to pull daily spend into the Meta tab of the sheet.
ads_read scope. Then exchange it for a 60-day token via:GET https://graph.facebook.com/oauth/access_token?grant_type=fb_exchange_token&client_id=APP_ID&client_secret=APP_SECRET&fb_exchange_token=SHORT_TOKENact_1234567890).ACCESS_TOKEN, AD_ACCOUNT_ID, and STORE_NAME./**
* GANNTZ — Meta (Facebook) Ads Daily Spend Sync (v1)
* Runs as a Google Apps Script. Fetches daily spend from the
* Facebook Marketing API and writes to the "Meta" tab of the sheet.
* Re-runs are safe — last 7 days are always re-written (self-healing).
*
* Fill in the three variables below, then run once to verify.
*/
var SHEET_ID = '127SZkm9R6Xsp7he4xdVHKlkjOkQzoevIIjJD7DPoqEI';
var TAB = 'Meta';
var LOOKBACK_DAYS = 7;
var ACCESS_TOKEN = ''; // ← long-lived Meta user token (60 days, renew monthly)
var AD_ACCOUNT_ID = ''; // ← e.g. act_1234567890
var STORE_NAME = ''; // ← exact column header in the Meta tab (e.g. Laurentcarter)
function main() {
var ss = SpreadsheetApp.openById(SHEET_ID);
var sheet = ss.getSheetByName(TAB);
if (!sheet) throw new Error('Tab not found: ' + TAB);
// Locate store column
var header = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
var col = -1;
for (var c = 1; c < header.length; c++) {
if (String(header[c]).trim() === String(STORE_NAME).trim()) { col = c + 1; break; }
}
if (col === -1) throw new Error('Column "' + STORE_NAME + '" not found. Header: ' + header.join(', '));
// Map date -> row
var lastRow = sheet.getLastRow();
if (lastRow < 2) throw new Error('Meta tab has no date rows in column A.');
var dates = sheet.getRange(2, 1, lastRow - 1, 1).getValues();
var rowOf = {};
for (var i = 0; i < dates.length; i++) {
var v = dates[i][0];
if (!v) continue;
var key = (v instanceof Date) ? Utilities.formatDate(v, 'UTC', 'yyyy-MM-dd')
: String(v).trim().substring(0, 10);
rowOf[key] = i + 2;
}
// Date range
var now = new Date();
var start = new Date(now.getTime() - LOOKBACK_DAYS * 86400000);
var since = Utilities.formatDate(start, 'UTC', 'yyyy-MM-dd');
var until = Utilities.formatDate(now, 'UTC', 'yyyy-MM-dd');
// Fetch spend from Meta Marketing API
var url = 'https://graph.facebook.com/v19.0/' + AD_ACCOUNT_ID + '/insights' +
'?fields=spend,date_start' +
'&time_increment=1' +
'&time_range={"since":"' + since + '","until":"' + until + '"}' +
'&access_token=' + ACCESS_TOKEN;
var resp = UrlFetchApp.fetch(url, { muteHttpExceptions: true });
if (resp.getResponseCode() !== 200) {
throw new Error('Meta API error: ' + resp.getContentText().substring(0, 300));
}
var data = JSON.parse(resp.getContentText()).data || [];
// Write to sheet
var written = 0, missing = [];
for (var i = 0; i < data.length; i++) {
var d = data[i].date_start;
var spend = parseFloat(data[i].spend || 0);
var row = rowOf[d];
if (!row) { missing.push(d); continue; }
sheet.getRange(row, col).setValue(spend);
written++;
}
Logger.log('[' + STORE_NAME + '] ' + since + ' -> ' + until +
': wrote ' + written + ' day(s) into column ' + col);
if (missing.length) Logger.log('[' + STORE_NAME + '] no date row for: ' + missing.join(', '));
if (written === 0) Logger.log('[' + STORE_NAME + '] nothing written — no spend or date rows missing.');
}
main → Time-driven → Day timer → e.g. 5–6 AM.ACCESS_TOKEN in the script before it expires, or the script will silently stop writing.⚠ The Meta script must be set up separately for each store's ad account — one Apps Script project per store, each writing to its own column in the Meta tab.
| Loading… |
Assign categories to vendors — changes apply to all entries from that vendor. You can still adjust individual entries in the next step.
| Vendor | Entries | Total | AI Category | Set Category | Employee | Relevant |
|---|
| Date | Vendor | Description | Amount | Cur | Category | Store | Employee | Relevant | Confidence |
|---|
| Date ▼ | Vendor ↕ | Description ↕ | Amount ↕ | Cur ↕ | Category ↕ | Store ↕ | Employee ↕ | Source ↕ | Rel. ↕ | ||
|---|---|---|---|---|---|---|---|---|---|---|---|
| Loading… | |||||||||||
Recurring payments detected automatically from your ledger — same vendor, similar amount, regular interval.
| Vendor | Category | Frequency | Avg Amount | Occurrences | First Seen | Last Charge | Next Expected |
|---|
No recurring payment patterns detected yet — needs at least 2 charges from the same vendor at a regular interval.
Recomputes the USD equivalent for every existing entry using date-accurate exchange rates (frankfurter.app). Run this if you notice incorrect USD totals on historical entries.
All categories (base and custom). Click any chip to delete it — entries in that category will be cleared and flagged for re-classification.
Rules match by vendor name (and optional description keyword) and override the AI's category, store, and expense-relevance. Create rules using the 📌 button on any entry or in the upload review table.