<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport"="width=device-width, initial-scale=1.0">
<title>Google Ads Spend Tracker</title>
<style>
* { margin: 0; padding: 0; box-sizing: border-box; }
body { font-family: -apple-system, BlinkMacSystemFont, 'Segoe UI', sans-serif; background: #f5f5f5; padding: 20px; }
.container { max-width: 1920px; margin: 0 auto; }
h1 { margin-bottom: 20px; color: #333; display: flex; justify-content: space-between; align-items: center; }
.nav-links { display: flex; gap: 15px; }
.nav-links a { background: #007bff; color: white; padding: 10px 20px; border-radius: 6px; text-decoration: none; font-size: 0.9rem; }
.nav-links a:hover { background: #0056b3; }
.filters { background: white; padding: 20px; margin-bottom: 20px; border-radius: 8px; display: flex; gap: 15px; flex-wrap: wrap; align-items: center; box-shadow: 0 2px 4px rgba(0,0,0,0.1); }
.filters label { font-size: 0.9rem; color: #555; }
.filters select, .filters input { padding: 8px 12px; border: 1px solid #ddd; border-radius: 4px; font-size: 0.9rem; }
.filters button { padding: 8px 16px; background: #007bff; color: white; border: none; border-radius: 4px; cursor: pointer; font-size: 0.9rem; }
.filters button:hover { background: #0056b3; }
.search-box { flex: 1; min-width: 200px; }
.search-box input { width: 100%; padding: 8px 12px; border: 1px solid #ddd; border-radius: 4px; }
.stats { display: grid; grid-template-columns: repeat(auto-fit, minmax(180px, 1fr)); gap: 15px; margin-bottom: 20px; }
.stat-card { background: white; padding: 20px; border-radius: 8px; box-shadow: 0 2px 4px rgba(0,0,0,0.1); }
.stat-label { font-size: 0.85rem; color: #666; margin-bottom: 8px; }
.stat-value { font-size: 1.6rem; font-weight: 700; color: #333; }
.stat-value.good { color: #28a745; }
.stat-value.bad { color: #dc3545; }
table { width: 100%; background: white; border-radius: 8px; overflow: hidden; box-shadow: 0 2px 4px rgba(0,0,0,0.1); border-collapse: collapse; }
th { background: #f8f9fa; padding: 12px; text-align: left; font-weight: 600; border-bottom: 2px solid #e9ecef; font-size: 0.85rem; cursor: pointer; user-select: none; position: relative; }
th:hover { background: #e9ecef; }
th.sortable::after { content: ' ⇅'; color: #999; font-size: 0.7rem; }
th.sorted-asc::after { content: ' ▲'; color: #333; }
th.sorted-desc::after { content: ' ▼'; color: #333; }
td { padding: 12px; border-bottom: 1px solid #e9ecef; font-size: 0.9rem; }
tr:hover { background: #f8f9fa; }
tr.roas-excellent { background: #d4edda; }
tr.roas-good { background: #fff3cd; }
tr.roas-poor { background: #f8d7da; }
tr.overspend { border-left: 4px solid #dc3545; }
tr.underspend { border-left: 4px solid #ffc107; }
.loading { text-align: center; padding: 40px; color: #666; }
.budget-edit { display: inline-block; cursor: pointer; padding: 2px 6px; border-radius: 4px; }
.budget-edit:hover { background: #e9ecef; }
.budget-input { width: 80px; padding: 4px; border: 1px solid #007bff; border-radius: 3px; }
.pacing-badge { display: inline-block; padding: 2px 8px; border-radius: 12px; font-size: 0.75rem; font-weight: 600; }
.pacing-on-track { background: #d4edda; color: #155724; }
.pacing-overspend { background: #f8d7da; color: #721c24; }
.pacing-underspend { background: #fff3cd; color: #856404; }
</style>
</head>
<body>
<div class="container">
<h1>
<span>Google Ads Spend Tracker</span>
<div class="nav-links">
<a href="analytics.html">📊 Analytics</a>
</div>
</h1>
<div class="filters">
<label>Date Range:
<select id="date-range">
<option value="mtd">Month to Date</option>
<option value="yesterday">Yesterday</option>
<option value="last7">Last 7 Days</option>
<option value="last30">Last 30 Days</option>
<option value="custom">Custom Range</option>
</select>
</label>
<div id="custom-dates" style="display: none; display: flex; gap: 10px;">
<input type="date" id="start-date">
<input type="date" id="end-date">
</div>
<label>Executive:
<select id="exec-filter">
<option value="">All</option>
<option value="Ben">Ben</option>
<option value="Rachel">Rachel</option>
<option value="Gethin">Gethin</option>
<option value="Larisa">Larisa</option>
</select>
</label>
<div class="search-box">
<input type="text" id="search" placeholder="Search accounts...">
</div>
<button onclick="loadData()">🔄 Refresh</button>
</div>
<div class="stats" id="stats"></div>
<div id="table-container"><div class="loading">Loading...</div></div>
</div>
<script>
const API_URL = 'http://141.227.131.159:5000/api';
let allData = [];
let sortColumn = null;
let sortDirection = 'desc';
// Date range handling
document.getElementById('date-range').addEventListener('change', function() {
const customDates = document.getElementById('custom-dates');
if (this.value === 'custom') {
customDates.style.display = 'flex';
} else {
customDates.style.display = 'none';
loadData();
}
});
document.getElementById('start-date').addEventListener('change', loadData);
document.getElementById('end-date').addEventListener('change', loadData);
document.getElementById('exec-filter').addEventListener('change', loadData);
document.getElementById('search').addEventListener('input', filterTable);
function getDateRange() {
const range = document.getElementById('date-range').value;
const today = new Date();
let startDate, endDate;
switch(range) {
case 'mtd':
startDate = new Date(today.getFullYear(), today.getMonth(), 1);
endDate = today;
break;
case 'yesterday':
const yesterday = new Date(today);
yesterday.setDate(yesterday.getDate() - 1);
startDate = endDate = yesterday;
break;
case 'last7':
startDate = new Date(today);
startDate.setDate(startDate.getDate() - 7);
endDate = today;
break;
case 'last30':
startDate = new Date(today);
startDate.setDate(startDate.getDate() - 30);
endDate = today;
break;
case 'custom':
startDate = document.getElementById('start-date').value;
endDate = document.getElementById('end-date').value;
return { start: startDate, end: endDate };
}
return {
start: startDate.toISOString().split('T')[0],
end: endDate.toISOString().split('T')[0]
};
}
async function loadData() {
const exec = document.getElementById('exec-filter').value;
const dates = getDateRange();
try {
let statsUrl = `${API_URL}/stats?start_date=${dates.start}&end_date=${dates.end}`;
const statsRes = await fetch(statsUrl);
const stats = await statsRes.json();
document.getElementById('stats').innerHTML = `
<div class="stat-card">
<div class="stat-label">Accounts</div>
<div class="stat-value">${stats.accounts}</div>
</div>
<div class="stat-card">
<div class="stat-label">Total Spend</div>
<div class="stat-value">£${stats.total_spend.toLocaleString()}</div>
</div>
<div class="stat-card">
<div class="stat-label">Total Revenue</div>
<div class="stat-value">£${stats.total_revenue.toLocaleString()}</div>
</div>
<div class="stat-card">
<div class="stat-label">Avg ROAS</div>
<div class="stat-value ${stats.avg_roas >= 2 ? 'good' : stats.avg_roas < 1 ? 'bad' : ''}">${stats.avg_roas.toFixed(2)}x</div>
</div>
<div class="stat-card">
<div class="stat-label">Total Budget</div>
<div class="stat-value">£${stats.total_budget.toLocaleString()}</div>
</div>
<div class="stat-card">
<div class="stat-label">Budget Used</div>
<div class="stat-value ${stats.budget_used_pct > 100 ? 'bad' : stats.budget_used_pct > 80 ? '' : 'good'}">${stats.budget_used_pct}%</div>
</div>
`;
let url = `${API_URL}/spend?start_date=${dates.start}&end_date=${dates.end}`;
if (exec) url += `&exec=${exec}`;
const spendRes = await fetch(url);
allData = await spendRes.json();
renderTable(allData);
} catch (err) {
console.error('Error loading data:', err);
document.getElementById('table-container').innerHTML = '<div class="loading">Error loading data. Check console.</div>';
}
}
function renderTable(data) {
const html = `
<table>
<thead>
<tr>
<th class="sortable" onclick="sortTable('account_name')">Account</th>
<th class="sortable" onclick="sortTable('exec')">Exec</th>
<th class="sortable" onclick="sortTable('monthly_budget')">Budget</th>
<th class="sortable" onclick="sortTable('spend')">Spend</th>
<th class="sortable" onclick="sortTable('revenue')">Revenue</th>
<th class="sortable" onclick="sortTable('roas')">ROAS</th>
<th class="sortable" onclick="sortTable('budget_used_pct')">Budget Used</th>
<th class="sortable" onclick="sortTable('pacing')">Pacing</th>
<th class="sortable" onclick="sortTable('conversions')">Conv.</th>
<th class="sortable" onclick="sortTable('cpa')">CPA</th>
</tr>
</thead>
<tbody>
${data.map(row => {
let rowClass = '';
if (row.roas >= 3) rowClass = 'roas-excellent';
else if (row.roas >= 1.5) rowClass = 'roas-good';
else if (row.roas > 0 && row.roas < 1) rowClass = 'roas-poor';
if (row.pacing === 'overspend') rowClass += ' overspend';
else if (row.pacing === 'underspend') rowClass += ' underspend';
return `
<tr class="${rowClass}">
<td><strong>${row.account_name}</strong></td>
<td>${row.exec || '-'}</td>
<td><span class="budget-edit" onclick="editBudget(${row.account_id}, ${row.monthly_budget})">£${row.monthly_budget.toLocaleString()}</span></td>
<td>£${row.spend.toLocaleString()}</td>
<td>£${row.revenue.toLocaleString()}</td>
<td>${row.roas > 0 ? row.roas.toFixed(2) + 'x' : '-'}</td>
<td>${row.budget_used_pct}%</td>
<td><span class="pacing-badge pacing-${row.pacing}">${row.pacing}</span></td>
<td>${row.conversions.toFixed(1)}</td>
<td>${row.cpa > 0 ? '£' + row.cpa.toFixed(2) : '-'}</td>
</tr>
`;
}).join('')}
</tbody>
</table>
`;
document.getElementById('table-container').innerHTML = html;
}
function sortTable(column) {
if (sortColumn === column) {
sortDirection = sortDirection === 'asc' ? 'desc' : 'asc';
} else {
sortColumn = column;
sortDirection = 'desc';
}
const sorted = [...allData].sort((a, b) => {
let aVal = a[column];
let bVal = b[column];
if (aVal === null || aVal === undefined) aVal = 0;
if (bVal === null || bVal === undefined) bVal = 0;
if (typeof aVal === 'string') {
return sortDirection === 'asc'
? aVal.localeCompare(bVal)
: bVal.localeCompare(aVal);
}
return sortDirection === 'asc' ? aVal - bVal : bVal - aVal;
});
// Update sort indicators
document.querySelectorAll('th').forEach(th => {
th.classList.remove('sorted-asc', 'sorted-desc');
});
document.querySelector(`th[onclick="sortTable('${column}')"]`)
.classList.add(`sorted-${sortDirection}`);
renderTable(sorted);
}
function filterTable() {
const search = document.getElementById('search').value.toLowerCase();
const filtered = allData.filter(row =>
row.account_name.toLowerCase().includes(search) ||
(row.exec && row.exec.toLowerCase().includes(search))
);
renderTable(filtered);
}
function editBudget(accountId, currentBudget) {
const newBudget = prompt(`Enter new monthly budget for this account:`, currentBudget);
if (newBudget !== null && !isNaN(newBudget)) {
updateBudget(accountId, parseFloat(newBudget));
}
}
async function updateBudget(accountId, budget) {
try {
const response = await fetch(`${API_URL}/budget/${accountId}`, {
method: 'PUT',
headers: { 'Content-Type': 'application/json' },
body: JSON.stringify({ budget })
});
if (response.ok) {
loadData(); // Reload to show updated budget
} else {
alert('Failed to update budget');
}
} catch (err) {
console.error('Error updating budget:', err);
alert('Error updating budget');
}
}
// Initial load
loadData();
</script>
</body>
</html>