Untitled
plain_text
a month ago
6.0 kB
3
Indexable
Never
// copyright Mike Rhodes 2022-2023 (c) // for more training & resources to help you get great results at google ads head to AgencySavvy.com :) // // Yes we also manage accounts - just email the team hello@websavvy.com.au for more info // or connect with me on LinkedIn at https://www.linkedin.com/in/websavvy/ // // // PLEASE NOTE: this script is designed for campaigns with a product feed (ie ecommerce accounts ONLY) // Because all the data is held at the Listing Group level, it’s IMPOSSIBLE to get useful charts & data for Lead Gen accounts // Please yell at Google, not me :) // // // version 20 (added split between Display & Search - thanks Tobias Hink for all your help! Look him up - the guy's a genius https://www.linkedin.com/in/tobias-hink/ // // // // INSTRUCTIONS: ——— PLEASE READ ——— // // COPY this template Google Sheet: // https://docs.google.com/spreadsheets/d/10BXV-O2gsFIy5Ov_mfvHpcuCG49VWddWoyyrSGm1pGw/copy // // now grab the URL of YOUR sheet & enter it below in the line that starts 'let ss =' (put the URL between the single quotes) // // name your script: WebSavvy: pMax charts v20 (updates daily) // name your sheet: {put company name here} - pMax charts v20 (updates daily) // authorise, save & run your script // once it’s all working, set the schedule to run daily // // thanks for reading :) function main() { let ss = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/1HQnGgaQx3sKltxxmCETrms4Hc1bANaL7MpZUgRNr-b4/'); // enter the URL of YOUR sheet over there <— // no need to touch any code below this line —————————————————————————— // define commonly used 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 campName = ' campaign.name '; let adgName = ' ad_group.name '; let chType = ' campaign.advertising_channel_type '; 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 agId = ' asset_group.id '; let aId = ' asset.id '; let adPmaxPerf = ' asset_group_asset.performance_label '; let assetText = ' asset.text_asset.text '; let assetSource = ' asset.source ' ; let adUrl = ' asset.image_asset.full_size.url '; let youtubeTitle = ' asset.youtube_video_asset.youtube_video_title '; let youtubeId = ' asset.youtube_video_asset.youtube_video_id '; let assetType = ' asset_group_asset.field_type '; let agStrength = ' asset_group.ad_strength '; let agStatus = ' asset_group.status '; let aIdCamp = ' segments.asset_interaction_target.asset '; let interAsset = ' segments.asset_interaction_target.interaction_on_this_asset ' let aIdAsset = ' asset.resource_name '; let asgName = ' asset_group.name '; let lgType = ' asset_group_listing_group_filter.type '; 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 date07 = ' segments.date DURING LAST_7_DAYS ' let date30 = ' segments.date DURING LAST_30_DAYS ' let order = ' ORDER BY campaign.name'; // 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] // product totals let pQuery = 'SELECT ' + p.join(',') + ' FROM shopping_performance_view ' + ' WHERE ' + date30 + 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 ads = [campName, asgName, agId, aIdAsset, assetType, adPmaxPerf, agStrength, agStatus, assetSource] // pMax ads let adsQuery = 'SELECT ' + ads.join(',') + ' FROM asset_group_asset ' + ' WHERE ' + asgEnabled; // call report function to pull data & push to 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(agQuery, ss.getSheetByName('r_ag')); runReport(adsQuery, ss.getSheetByName('r_ads')); } // query & export report data to named sheet function runReport(q,sh) { const report = AdsApp.report(q); report.exportToSheet(sh); }