Conversion Lag Report

1/**
2 * Conversion Lag Report
3 *
4 * Schat per conversieactie de conversievertraging (de tijd tussen klik en conversie) door
5 * conversies toegerekend aan de klikdatum (metrics.conversions / all_conversions) te
6 * vergelijken met conversies toegerekend aan de conversiedatum
7 * (metrics.conversions_by_conversion_date / all_conversions_by_conversion_date) per dag.
8 * Voor recente dagen ligt het aantal "op klikdatum" structureel lager zolang er nog
9 * conversies binnenkomen; uit dat verloop wordt afgeleid na hoeveel dagen ~50%, ~80% en
10 * ~95% van de conversies is geregistreerd. Rapport gaat naar een Google Sheet en optioneel
11 * per e-mail. Het script wijzigt niets in het account.
12 *
13 * Versie:     1.2.0
14 * Frequentie: Wekelijks
15 * Niveau:     Account
16 * Licentie:   MIT
17 */
18
19// ===== CONFIGURATIE =====
20
21// Google Sheet voor het rapport.
22var SPREADSHEET_URL = 'https://docs.google.com/spreadsheets/d/JOUW_SHEET_ID/edit';
23var SHEET_NAMES = { summary: 'Conversielag', daily: 'Conversielag per dag' };
24
25// Aantal dagen historie. Minimaal 60 aanbevolen: de oudste 30 dagen dienen als "volledig" referentiekader.
26var DAYS_OF_HISTORY = 90;
27
28// Aantal meest recente dagen dat als "nog niet volledig" wordt beschouwd bij het schatten van de lag.
29var RECENT_WINDOW_DAYS = 30;
30
31// Alleen conversieacties met minimaal dit aantal conversies in de hele periode.
32var MIN_CONVERSIONS = 20;
33
34// Optioneel e-mailoverzicht. Leeg = geen e-mail.
35var EMAIL_RECIPIENTS = '';
36
37// ===== EINDE CONFIGURATIE =====
38
39function main() {
40  try {
41    var account = AdsApp.currentAccount();
42    var timezone = account.getTimeZone();
43    var today = new Date();
44    var endDate = fmt(addDays(today, -1), timezone);
45    var startDate = fmt(addDays(today, -DAYS_OF_HISTORY), timezone);
46    Logger.log('Conversielag-rapport voor ' + account.getName() + ' (' + startDate + ' t/m ' + endDate + ')');
47
48    var actionsMeta = fetchConversionActions();
49    var daily = fetchDailyByAction(startDate, endDate);
50    var summary = estimateLag(daily, actionsMeta, endDate, timezone);
51    Logger.log(summary.length + ' conversieacties geanalyseerd.');
52    summary.forEach(function (s) {
53      Logger.log(s.name + ': ' + s.totalByConvDate.toFixed(1) + ' conv. | gem. lag ~' + (s.avgLagDays === null ? '?' : s.avgLagDays.toFixed(1) + ' dagen') +
54        ' | 80% binnen ' + (s.p80Days === null ? '?' : s.p80Days + ' dagen') + ' | lookback ' + s.lookbackDays + 'd');
55    });
56
57    var ss = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
58    writeSummary(ss, summary, startDate, endDate);
59    writeDaily(ss, daily, actionsMeta);
60    sendEmail(account, summary);
61    Logger.log('Klaar: ' + SPREADSHEET_URL);
62  } catch (e) {
63    Logger.log('FOUT in conversion-lag-report: ' + e + (e.stack ? '\n' + e.stack : ''));
64    throw e;
65  }
66}
67
68/**
69 * Metadata per conversieactie (lookback window, categorie, telt mee in "Conversies"?).
70 */
71function fetchConversionActions() {
72  var query =
73    'SELECT conversion_action.id, conversion_action.name, conversion_action.category, conversion_action.type, ' +
74    'conversion_action.status, conversion_action.include_in_conversions_metric, conversion_action.click_through_lookback_window_days, ' +
75    'conversion_action.counting_type ' +
76    'FROM conversion_action ' +
77    "WHERE conversion_action.status = 'ENABLED'";
78  var meta = {};
79  var rows = AdsApp.report(query).rows();
80  while (rows.hasNext()) {
81    var row = rows.next();
82    meta[row['conversion_action.name']] = {
83      id: row['conversion_action.id'],
84      name: row['conversion_action.name'],
85      category: row['conversion_action.category'],
86      type: row['conversion_action.type'],
87      includeInConversions: String(row['conversion_action.include_in_conversions_metric']) === 'true',
88      lookbackDays: Number(row['conversion_action.click_through_lookback_window_days']),
89      countingType: row['conversion_action.counting_type']
90    };
91  }
92  return meta;
93}
94
95/**
96 * Dagelijkse conversies per conversieactie: op klikdatum en op conversiedatum.
97 * Bij segmentatie op conversion_action_name zijn alleen conversie-metrics toegestaan (geen kosten).
98 */
99function fetchDailyByAction(startDate, endDate) {
100  var query =
101    'SELECT segments.date, segments.conversion_action_name, ' +
102    'metrics.all_conversions, metrics.all_conversions_by_conversion_date, ' +
103    'metrics.conversions, metrics.conversions_by_conversion_date, ' +
104    'metrics.all_conversions_value, metrics.all_conversions_value_by_conversion_date ' +
105    'FROM campaign ' +
106    "WHERE segments.date BETWEEN '" + startDate + "' AND '" + endDate + "'";
107  var daily = {}; // daily[action][date] = {...}
108  var rows = AdsApp.report(query).rows();
109  while (rows.hasNext()) {
110    var row = rows.next();
111    var action = row['segments.conversion_action_name'];
112    var date = row['segments.date'];
113    if (!action) { continue; }
114    if (!daily[action]) { daily[action] = {}; }
115    if (!daily[action][date]) {
116      daily[action][date] = { byClick: 0, byConv: 0, byClickPrimary: 0, byConvPrimary: 0, valueByClick: 0, valueByConv: 0 };
117    }
118    var d = daily[action][date];
119    d.byClick += Number(row['metrics.all_conversions'] || 0);
120    d.byConv += Number(row['metrics.all_conversions_by_conversion_date'] || 0);
121    d.byClickPrimary += Number(row['metrics.conversions'] || 0);
122    d.byConvPrimary += Number(row['metrics.conversions_by_conversion_date'] || 0);
123    d.valueByClick += Number(row['metrics.all_conversions_value'] || 0);
124    d.valueByConv += Number(row['metrics.all_conversions_value_by_conversion_date'] || 0);
125  }
126  return daily;
127}
128
129/**
130 * Schat de conversielag per actie.
131 * Aanpak: voor de oudste (volledige) periode is de verhouding klikdatum/conversiedatum ~1.
132 * Voor elke recente dag d (x dagen geleden) is de "vulgraad" = byClick(d) / verwachte volledige waarde,
133 * waarbij de verwachte volledige waarde het gemiddelde is van byConv over de referentieperiode
134 * (byConv is niet afhankelijk van lag). Uit de vulgraad per aantal dagen geleden volgt de curve.
135 */
136function estimateLag(daily, actionsMeta, endDate, timezone) {
137  var summary = [];
138  var end = parseDate(endDate);
139
140  Object.keys(daily).forEach(function (action) {
141    var days = daily[action];
142    var dates = Object.keys(days).sort();
143    var totalByConv = 0;
144    var totalByClick = 0;
145    dates.forEach(function (d) { totalByConv += days[d].byConv; totalByClick += days[d].byClick; });
146    if (totalByConv < MIN_CONVERSIONS) { return; }
147
148    // Referentie: dagen ouder dan RECENT_WINDOW_DAYS. Gemiddelde dagwaarde op klikdatum (compleet) en op conversiedatum.
149    var refClick = 0;
150    var refConv = 0;
151    var refDays = 0;
152    var fill = []; // {daysAgo, ratio}
153    dates.forEach(function (d) {
154      var daysAgo = Math.round((end.getTime() - parseDate(d).getTime()) / 86400000) + 1;
155      if (daysAgo > RECENT_WINDOW_DAYS) {
156        refClick += days[d].byClick;
157        refConv += days[d].byConv;
158        refDays++;
159      }
160    });
161    var refDailyComplete = refDays > 0 ? refClick / refDays : 0;
162    // Structurele verhouding klik/conv-datum in de volledige periode (compenseert voor volumetrends).
163    var structuralRatio = refConv > 0 ? refClick / refConv : 1;
164
165    if (refDailyComplete > 0) {
166      dates.forEach(function (d) {
167        var daysAgo = Math.round((end.getTime() - parseDate(d).getTime()) / 86400000) + 1;
168        if (daysAgo <= RECENT_WINDOW_DAYS) {
169          // Vulgraad van deze klikdag t.o.v. het verwachte complete niveau. Gebruik het weekdaggemiddelde
170          // benaderd door het referentiegemiddelde; begrens op 1.
171          var ratio = Math.min(days[d].byClick / refDailyComplete, 1.2);
172          fill.push({ daysAgo: daysAgo, ratio: ratio });
173        }
174      });
175    }
176
177    // Maak de curve monotoon (cumulatieve fractie stijgt met daysAgo) door glijdend maximum vanaf dag 1.
178    fill.sort(function (a, b) { return a.daysAgo - b.daysAgo; });
179    var smoothed = [];
180    var running = 0;
181    fill.forEach(function (f) {
182      running = Math.max(running, Math.min(f.ratio, 1));
183      smoothed.push({ daysAgo: f.daysAgo, fraction: running });
184    });
185
186    var p50 = firstDayReaching(smoothed, 0.5);
187    var p80 = firstDayReaching(smoothed, 0.8);
188    var p95 = firstDayReaching(smoothed, 0.95);
189    var avgLag = estimateAverageLag(smoothed);
190
191    var meta = actionsMeta[action] || {};
192    var recentByClick = 0;
193    var recentByConv = 0;
194    dates.forEach(function (d) {
195      var daysAgo = Math.round((end.getTime() - parseDate(d).getTime()) / 86400000) + 1;
196      if (daysAgo <= 7) { recentByClick += days[d].byClick; recentByConv += days[d].byConv; }
197    });
198
199    summary.push({
200      name: action,
201      category: meta.category || '',
202      includeInConversions: meta.includeInConversions === undefined ? '' : (meta.includeInConversions ? 'Ja' : 'Nee'),
203      lookbackDays: meta.lookbackDays || '',
204      countingType: meta.countingType || '',
205      totalByConvDate: totalByConv,
206      totalByClickDate: totalByClick,
207      structuralRatio: structuralRatio,
208      last7ByClick: recentByClick,
209      last7ByConv: recentByConv,
210      last7Fill: recentByConv > 0 ? recentByClick / recentByConv : null,
211      p50Days: p50,
212      p80Days: p80,
213      p95Days: p95,
214      avgLagDays: avgLag,
215      advice: buildAdvice(p80, p95, meta.lookbackDays)
216    });
217  });
218  summary.sort(function (a, b) { return b.totalByConvDate - a.totalByConvDate; });
219  return summary;
220}
221
222function firstDayReaching(curve, target) {
223  for (var i = 0; i < curve.length; i++) {
224    if (curve[i].fraction >= target) { return curve[i].daysAgo; }
225  }
226  return null;
227}
228
229/**
230 * Gemiddelde lag = som over dagen van (1 - cumulatieve fractie), begrensd op het venster.
231 */
232function estimateAverageLag(curve) {
233  if (!curve.length) { return null; }
234  var sum = 0;
235  var lastFraction = 0;
236  for (var day = 1; day <= RECENT_WINDOW_DAYS; day++) {
237    var point = null;
238    for (var i = 0; i < curve.length; i++) { if (curve[i].daysAgo === day) { point = curve[i]; break; } }
239    if (point) { lastFraction = point.fraction; }
240    sum += (1 - lastFraction);
241  }
242  return Math.max(sum, 0);
243}
244
245function buildAdvice(p80, p95, lookbackDays) {
246  if (p80 === null) { return 'Onvoldoende data voor een schatting.'; }
247  var parts = [];
248  if (p80 <= 1) { parts.push('Nauwelijks lag: prestaties van gisteren zijn vrijwel compleet.'); }
249  else if (p80 <= 7) { parts.push('Wacht ~' + p80 + ' dagen voordat je een periode beoordeelt (80% compleet).'); }
250  else { parts.push('Lange lag: wacht minimaal ' + p80 + ' dagen (80%) en ' + (p95 || RECENT_WINDOW_DAYS + '+') + ' dagen (95%) voordat je op deze conversie optimaliseert.'); }
251  if (lookbackDays && p95 !== null && p95 >= lookbackDays * 0.8) {
252    parts.push('Conversievenster (' + lookbackDays + 'd) lijkt krap; overweeg een langer click-through venster.');
253  }
254  if (p80 > 7) { parts.push('Gebruik in Smart Bidding een langere evaluatieperiode en vermijd dagelijkse tROAS/tCPA-aanpassingen.'); }
255  return parts.join(' ');
256}
257
258function writeSummary(ss, summary, startDate, endDate) {
259  var sheet = ss.getSheetByName(SHEET_NAMES.summary) || ss.insertSheet(SHEET_NAMES.summary);
260  sheet.clearContents();
261  var header = ['Conversieactie', 'Categorie', 'Telt in Conversies', 'Teltype', 'Lookback (d)', 'Alle conv. (conversiedatum)', 'Alle conv. (klikdatum)',
262    'Verhouding klik/conv-datum (ref.)', 'Laatste 7d op klikdatum', 'Laatste 7d op conversiedatum', 'Vulgraad laatste 7d', 'Gem. lag (dagen)', '50% binnen (d)', '80% binnen (d)', '95% binnen (d)', 'Advies'];
263  var values = [header].concat(summary.map(function (s) {
264    return [s.name, s.category, s.includeInConversions, s.countingType, s.lookbackDays, round(s.totalByConvDate), round(s.totalByClickDate), round(s.structuralRatio),
265      round(s.last7ByClick), round(s.last7ByConv), s.last7Fill === null ? '' : s.last7Fill, s.avgLagDays === null ? '' : round(s.avgLagDays),
266      s.p50Days === null ? '' : s.p50Days, s.p80Days === null ? '' : s.p80Days, s.p95Days === null ? '' : s.p95Days, s.advice];
267  }));
268  sheet.getRange(1, 1, values.length, header.length).setValues(values);
269  sheet.getRange(1, 1, 1, header.length).setFontWeight('bold').setBackground('#e8eaf6');
270  sheet.setFrozenRows(1);
271  if (values.length > 1) { sheet.getRange(2, 11, values.length - 1, 1).setNumberFormat('0%'); }
272  sheet.getRange(1, header.length + 2).setValue('Periode ' + startDate + ' t/m ' + endDate + ' | bijgewerkt ' + new Date());
273}
274
275function writeDaily(ss, daily, actionsMeta) {
276  var sheet = ss.getSheetByName(SHEET_NAMES.daily) || ss.insertSheet(SHEET_NAMES.daily);
277  sheet.clearContents();
278  var header = ['Datum', 'Conversieactie', 'Alle conv. (klikdatum)', 'Alle conv. (conversiedatum)', 'Conversies (klikdatum)', 'Conversies (conversiedatum)', 'Waarde (klikdatum)', 'Waarde (conversiedatum)'];
279  var values = [header];
280  Object.keys(daily).sort().forEach(function (action) {
281    Object.keys(daily[action]).sort().forEach(function (date) {
282      var d = daily[action][date];
283      if (d.byClick === 0 && d.byConv === 0) { return; }
284      values.push([date, action, round(d.byClick), round(d.byConv), round(d.byClickPrimary), round(d.byConvPrimary), round(d.valueByClick), round(d.valueByConv)]);
285    });
286  });
287  sheet.getRange(1, 1, values.length, header.length).setValues(values);
288  sheet.getRange(1, 1, 1, header.length).setFontWeight('bold').setBackground('#e8eaf6');
289  sheet.setFrozenRows(1);
290}
291
292function sendEmail(account, summary) {
293  if (!EMAIL_RECIPIENTS || !summary.length) { return; }
294  var html = '<div style="font-family:Arial,sans-serif;font-size:13px"><h3>Conversielag ' + escapeHtml(account.getName()) + '</h3>' +
295    '<table cellpadding="5" style="border-collapse:collapse;border:1px solid #ddd"><tr style="background:#f5f5f5"><th align="left">Conversieactie</th><th>Conv.</th><th>Gem. lag</th><th>80% binnen</th><th>95% binnen</th><th align="left">Advies</th></tr>';
296  summary.forEach(function (s) {
297    html += '<tr><td>' + escapeHtml(s.name) + '</td><td align="right">' + s.totalByConvDate.toFixed(0) + '</td><td align="right">' + (s.avgLagDays === null ? '-' : s.avgLagDays.toFixed(1) + 'd') +
298      '</td><td align="right">' + (s.p80Days === null ? '-' : s.p80Days + 'd') + '</td><td align="right">' + (s.p95Days === null ? '-' : s.p95Days + 'd') + '</td><td>' + escapeHtml(s.advice) + '</td></tr>';
299  });
300  html += '</table><p><a href="' + SPREADSHEET_URL + '">Open de Sheet</a></p></div>';
301  try {
302    MailApp.sendEmail({ to: EMAIL_RECIPIENTS, subject: '[Google Ads] Conversielag-rapport - ' + account.getName(), htmlBody: html });
303  } catch (e) {
304    Logger.log('E-mail versturen mislukt: ' + e);
305  }
306}
307
308function parseDate(str) {
309  var p = str.split('-');
310  return new Date(Number(p[0]), Number(p[1]) - 1, Number(p[2]));
311}
312
313function addDays(date, days) {
314  var d = new Date(date.getTime());
315  d.setDate(d.getDate() + days);
316  return d;
317}
318
319function fmt(date, timezone) {
320  return Utilities.formatDate(date, timezone, 'yyyy-MM-dd');
321}
322
323function round(value) {
324  return Math.round(Number(value) * 100) / 100;
325}
326
327function escapeHtml(text) {
328  return String(text).replace(/&/g, '&amp;').replace(/</g, '&lt;').replace(/>/g, '&gt;');
329}
330