©TsvetanAngelov.com | All Rights Reserved
Създавам и оптимизирам Google Ads, Facebook Ads и сайтове с цел повече запитвания и продажби.
✔ ясна стратегия
✔ измерими резултати
✔ дългосрочно развитие
Въведение: Защо автоматизация на репорти?
Ръчното експортиране на CSV файлове от Google Ads, обработката им в Excel и създаването на PowerPoint отчети отнема часове и води до грешки. С BigQuery + Looker Studio можете да изградите напълно автоматизирана система, която:
- Обновява данните автоматично (дневно, почасово или на поток)
- Визуализира KPI в реално време (с малко закъснение)
- Изпраща имейли или PDF отчети по график
- Намалява човешките грешки до минимум
-- Параметризирана заявка за ROAS dashboard
-- {{ start_date }} и {{ end_date }} се попълват автоматично от контролите в Looker Studio
SELECT
campaign.name AS campaign_name,
segments.date AS date,
SUM(cost_micros) / 1000000 AS cost,
SUM(conversions) AS conversions,
SUM(conversions_value) AS conversion_value,
SUM(impressions) AS impressions,
SUM(clicks) AS clicks
FROM `your_project.your_dataset.p_ads_AdStats_*`
WHERE _PARTITIONTIME BETWEEN PARSE_DATE('%Y%m%d', '{{ start_date }}') AND PARSE_DATE('%Y%m%d', '{{ end_date }}')
GROUP BY campaign_name, date
ORDER BY date DESC;
| Компонент | Описание | Как се настройва в Looker Studio |
|---|---|---|
| Date Range Control | Позволява на потребителя да избира период без редакция на SQL | Add a control → Date range control → свързва се с всички графики |
| Filter Controls | Филтриране по кампания, устройство, държава | Add a control → Drop-down list → изберете поле (campaign.name) |
| Scorecards (KPI карти) | Общ разход, конверсии, ROAS, CTR | Add a chart → Scorecard → изберете метрика + агрегираща функция (SUM, AVG) |
| Time Series Charts | Тенденция на разход и конверсии във времето | Add chart → Time series → dimension: date, metric: cost+conversions |
| Bar / Table Charts | Детайлен списък на кампании или ключови думи | Table chart → rows: campaign_name, metrics: cost, roas |
-- SQL за KPI dashboard (агрегирани метрики за даден период)2.3. Добавяне на "Community Connectors" за допълнителни източници (Google Sheets, GA4, Facebook Ads)
SELECT
SUM(cost_micros) / 1000000 AS total_cost,
SUM(conversions) AS total_conversions,
SUM(conversions_value) AS total_revenue,
ROUND(SAFE_DIVIDE(SUM(conversions_value), (SUM(cost_micros) / 1000000)) * 100, 2) AS roas,
ROUND(SAFE_DIVIDE((SUM(cost_micros) / 1000000), SUM(conversions)), 2) AS cpa,
ROUND(SAFE_DIVIDE(SUM(clicks), SUM(impressions)) * 100, 2) AS ctr,
ROUND((SUM(cost_micros) / 1000000) / SUM(clicks), 2) AS avg_cpc
FROM `your_project.your_dataset.p_ads_AdStats_*`
WHERE _PARTITIONTIME BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY) AND CURRENT_DATE();
-- Тази заявка се изпълнява всеки ден в 5:00 сутринта
-- Записва показателите от предния ден в таблица daily_performance_snapshot
SELECT
PARSE_DATE('%Y%m%d', segments.date) AS date,
campaign.id AS campaign_id,
campaign.name AS campaign_name,
SUM(cost_micros) / 1000000 AS cost,
SUM(conversions) AS conversions,
SUM(conversions_value) AS revenue,
ROUND(SAFE_DIVIDE(SUM(conversions_value), (SUM(cost_micros) / 1000000)) * 100, 2) AS roas_percent,
CURRENT_TIMESTAMP() AS processed_at
FROM `your_project.your_dataset.p_ads_AdStats_*`
WHERE _PARTITIONTIME = DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)
GROUP BY date, campaign_id, campaign_name;
-- Автоматично откриване на кампании с внезапен спад в ROAS
WITH weekly_performance AS (
SELECT
campaign_name,
DATE_TRUNC(PARSE_DATE('%Y%m%d', segments.date), WEEK) AS week,
SUM(cost_micros) / 1000000 AS weekly_cost,
SUM(conversions_value) AS weekly_revenue,
ROUND(SAFE_DIVIDE(SUM(conversions_value), (SUM(cost_micros) / 1000000)) * 100, 2) AS weekly_roas
FROM `your_project.your_dataset.p_ads_AdStats_*`
WHERE _PARTITIONTIME >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
GROUP BY campaign_name, week
),
anomalies AS (
SELECT
campaign_name,
week,
weekly_roas,
AVG(weekly_roas) OVER (PARTITION BY campaign_name) AS avg_roas,
weekly_roas - AVG(weekly_roas) OVER (PARTITION BY campaign_name) AS roas_deviation
FROM weekly_performance
)
SELECT * FROM anomalies
WHERE ABS(roas_deviation) > avg_roas * 0.5 -- 50% спад или ръст
ORDER BY roas_deviation ASC;
| Характеристика | Batch (партидна обработка) | Real-time / Streaming (реално време) |
|---|---|---|
| Латентност (закъснение) | Часове до дни (обикновено 24 часа за Google Ads DTS) | Секунди до минути |
| Цена в BigQuery | По-евтино – зарежда наведнъж големи обеми | По-скъпо – стрийминг insert таксува се допълнително |
| Подходящ за | Исторически анализи, ROAS, CPA, дългосрочни трендове | Аларми, real-time bidding, оперативни dashboards (последните 5 минути) |
| Източник за Google Ads | Data Transfer Service (DTS) – идеален за batch | Streaming API (Google Ads API с push към Pub/Sub + BigQuery) |
| Типична употреба в Looker Studio | Daily или weekly executive dashboards | Live мониторинг на разхода, бюджети |
-- Пример: Таблица с "near real-time" данни (актуализирана на всеки час чрез Scheduled Query)
-- Създайте scheduled query, която се изпълнява на всеки 60 минути
-- и записва резултатите в hourly_performance таблица
SELECT
TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), HOUR) AS hour,
campaign.name,
SUM(cost_micros) / 1000000 AS cost_last_hour,
SUM(clicks) AS clicks_last_hour
FROM `your_project.your_dataset.p_ads_AdStats_*`
WHERE _PARTITIONTIME = CURRENT_DATE()
AND TIMESTAMP_DIFF(CURRENT_TIMESTAMP(), TIMESTAMP(segments.date), HOUR) < 24
GROUP BY campaign.name;
-- Създайте Scheduled Query, която записва резултата в Google Sheets (чрез BigQuery Connector за Sheets)
-- След това Google Apps Script изпраща имейл с прикачен файл
-- Пример за Apps Script код (тригер за изпращане):
function sendDailyReport() {
var sheet = SpreadsheetApp.openById('your_sheet_id');
var range = sheet.getDataRange();
var pdf = range.getAs('application/pdf');
MailApp.sendEmail({
to: 'marketing@company.com',
subject: 'Daily Google Ads Report',
body: 'Attached is the daily performance summary.',
attachments: [pdf]
});
}
| Проблем (Issue) | Причина (Cause) | Решение (Solution) |
|---|---|---|
| Looker Studio отчетът е твърде бавен (зарежда > 30 секунди) | Заявката сканира твърде много данни, няма партициониране | 1. Използвайте _PARTITIONTIME филтър. 2. Създайте агрегирана таблица чрез Scheduled Query. |
| Грешка "Query timeout" или "Resources exceeded" | Твърде сложна заявка с много JOIN или големи обеми | Предварително агрегиране (materialized views) или разделяне на заявката |
| Данните в Looker Studio не се обновяват | Кеширане в Looker Studio или в BigQuery резултата | Настройте Cache Duration → "Disable caching" или по-кратък интервал |
| Спад на ROAS/конверсиите не се вижда веднага | DTS закъснение от 24-48 часа (batch limitation) | Добавете disclaimer в dashboard, или използвайте API за приблизителни real-time стойности |
©TsvetanAngelov.com | All Rights Reserved