/
v28
143 lines (119 loc) · 7.36 KB
/
v28
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
// want the paid version? head to www.mikerhodes.com.au
// or buy it here: https://mikerhodes.circle.so/checkout/latest-script
// it does everything the free script does, plus much more
// and includes free updates, support, some training & more
// v28 (free - does not include support)
// make a copy of this template sheet first & copy your URL into the ss variable on line 11. template: https://docs.google.com/spreadsheets/d/1aGOBOLNUjEIwwlYuS0D3fcIimRnzGdLSJxVd9rHOQ0E/copy
function main() {
let ss = SpreadsheetApp.openByUrl('');
let zombieDays = 366 // how many days data do you want to use to identify zombie (0 click) products?
let prodDays = 181 // how many days data do you want to use to identify 'long range' products?
// don’t change any code below this line ——————————————————————————————————————————————————————————————————————————————
// define query elements. wrap with spaces for safety
let impr = ' metrics.impressions ';
let clicks = ' metrics.clicks ';
let cost = ' metrics.cost_micros ';
let conv = ' metrics.conversions ';
let value = ' metrics.conversions_value ';
let allConv = ' metrics.all_conversions ';
let allValue = ' metrics.all_conversions_value ';
let views = ' metrics.video_views ';
let cpv = ' metrics.average_cpv ';
let segDate = ' segments.date ';
let prodTitle = ' segments.product_title ';
let prodID = ' segments.product_item_id ';
let prodC0 = ' segments.product_custom_attribute0 ';
let prodC1 = ' segments.product_custom_attribute1 ';
let prodC2 = ' segments.product_custom_attribute2 ';
let prodC3 = ' segments.product_custom_attribute3 ';
let prodC4 = ' segments.product_custom_attribute4 ';
let campName = ' campaign.name ';
let chType = ' campaign.advertising_channel_type ';
let adgName = ' ad_group.name ';
let adStatus = ' ad_group_ad.status ';
let adPerf = ' ad_group_ad_asset_view.performance_label ';
let adType = ' ad_group_ad_asset_view.field_type ';
let aIdAsset = ' asset.resource_name ';
let aId = ' asset.id ';
let assetType = ' asset.type ';
let aFinalUrl = ' asset.final_urls ';
let assetName = ' asset.name ';
let assetText = ' asset.text_asset.text ';
let assetSource = ' asset.source ' ;
let adUrl = ' asset.image_asset.full_size.url ';
let ytTitle = ' asset.youtube_video_asset.youtube_video_title ';
let ytId = ' asset.youtube_video_asset.youtube_video_id ';
let agId = ' asset_group.id ';
let assetFtype = ' asset_group_asset.field_type ';
let adPmaxPerf = ' asset_group_asset.performance_label ';
let agStrength = ' asset_group.ad_strength ';
let agStatus = ' asset_group.status ';
let asgName = ' asset_group.name ';
let lgType = ' asset_group_listing_group_filter.type ';
let aIdCamp = ' segments.asset_interaction_target.asset ';
let interAsset = ' segments.asset_interaction_target.interaction_on_this_asset ';
let pMaxOnly = ' AND campaign.advertising_channel_type = "PERFORMANCE_MAX" ';
let searchOnly = ' AND campaign.advertising_channel_type = "SEARCH" ';
let agFilter = ' AND asset_group_listing_group_filter.type != "SUBDIVISION" ';
let adgEnabled = ' AND ad_group.status = "ENABLED" AND campaign.status = "ENABLED" AND ad_group_ad.status = "ENABLED" ';
let asgEnabled = ' asset_group.status = "ENABLED" AND campaign.status = "ENABLED" ';
let notInter = ' AND segments.asset_interaction_target.interaction_on_this_asset != "TRUE" ';
let inter = ' AND segments.asset_interaction_target.interaction_on_this_asset = "TRUE" ';
let date07 = ' segments.date DURING LAST_7_DAYS ';
let date30 = ' segments.date DURING LAST_30_DAYS ';
let order = ' ORDER BY campaign.name ';
let orderImpr = ' ORDER BY metrics.impressions DESC ';
// Date stuff
let MILLIS_PER_DAY = 1000 * 60 * 60 * 24;
let now = new Date();
let from = new Date(now.getTime() - zombieDays * MILLIS_PER_DAY); // xx days in the past - default 366
let prod180 = new Date(now.getTime() - prodDays * MILLIS_PER_DAY); // xx days in the past - default 181
let to = new Date(now.getTime() - 1 * MILLIS_PER_DAY); // yesterday
let timeZone = AdsApp.currentAccount().getTimeZone();
let zombieRange = ' segments.date BETWEEN "' + Utilities.formatDate(from, timeZone, 'yyyy-MM-dd') + '" AND "' + Utilities.formatDate(to, timeZone, 'yyyy-MM-dd') + '"'
let prodDate = ' segments.date BETWEEN "' + Utilities.formatDate(prod180, timeZone, 'yyyy-MM-dd') + '" AND "' + Utilities.formatDate(to, timeZone, 'yyyy-MM-dd') + '"'
// build queries
let cd = [segDate, campName, cost, conv, value, views, cpv, impr, clicks, chType] // campaign by day
let campQuery = 'SELECT ' + cd.join(',') +
' FROM campaign ' +
' WHERE ' + date30 + pMaxOnly + order ;
let dv = [segDate, campName, aIdCamp, cost, conv, value, views, cpv, impr, chType, interAsset] // inter by day
let dvQuery = 'SELECT ' + dv.join(',') +
' FROM campaign ' +
' WHERE ' + date30 + pMaxOnly + notInter + order ;
let p = [campName, prodTitle, cost, conv, value, impr, chType,prodID,prodC0,prodC1,prodC2,prodC3,prodC4] // product totals
let pQuery = 'SELECT ' + p.join(',') +
' FROM shopping_performance_view ' +
' WHERE ' + date30 + pMaxOnly + order ;
let p180Query = 'SELECT ' + p.join(',') +
' FROM shopping_performance_view ' +
' WHERE ' + prodDate + pMaxOnly + order ;
let ag = [segDate, campName, asgName, agStrength, agStatus, lgType, impr, clicks, cost, conv, value] // asset group by day
let agQuery = 'SELECT ' + ag.join(',') +
' FROM asset_group_product_group_view ' +
' WHERE ' + date30 + agFilter ;
let assets = [aId, aFinalUrl, assetSource, assetType, ytTitle, ytId, assetText, aIdAsset, assetName] // names, IDs, URLs for all ad assets in account
let assetQuery = 'SELECT ' + assets.join(',') +
' FROM asset ' ;
let ads = [campName, asgName, agId, aIdAsset, assetFtype, adPmaxPerf, agStrength, agStatus, assetSource] // pMax ads
let adsQuery = 'SELECT ' + ads.join(',') +
' FROM asset_group_asset ' ;
let zombies = [prodID, clicks, impr, prodTitle] // zombie (0click) products - last xx days, set xx days at top of script
let zQuery = 'SELECT ' + zombies.join(',') +
' FROM shopping_performance_view ' +
' WHERE metrics.clicks < 1 AND ' + zombieRange + orderImpr ;
// call report function to pull data & push it to the named tabs in the sheet
runReport(campQuery, ss.getSheetByName('r_camp'));
runReport(dvQuery, ss.getSheetByName('r_dv'));
runReport(pQuery, ss.getSheetByName('r_prod_t'));
runReport(p180Query, ss.getSheetByName('r_prod_t_180'));
runReport(agQuery, ss.getSheetByName('r_ag'));
runReport(assetQuery, ss.getSheetByName('r_allads'));
runReport(adsQuery, ss.getSheetByName('r_ads'));
runReport(zQuery, ss.getSheetByName('zombies'));
} // end main
// query & export report data to named sheet
function runReport(q,sh) {
const report = AdsApp.report(q);
report.exportToSheet(sh);
}