tag:blogger.com,1999:blog-43777807435610646722024-03-17T02:15:51.943-07:00Free AdWords ScriptsA blog dedicated to AdWords Scripts. Always on. Always Free.Russell Savagehttp://www.blogger.com/profile/03030632304611412622noreply@blogger.comBlogger67125tag:blogger.com,1999:blog-4377780743561064672.post-63254843789370929152018-09-27T17:36:00.000-07:002018-09-27T17:36:07.459-07:00Check Close Variants Your Ads Are Showing For<br />
<br />
Google <a href="https://support.google.com/google-ads/answer/9131274">changed how Close Variants work</a> and now exact match keywords may also show an ad when Google believes the meaning of the search is the same as the keyword. Kind of like a synonym, but potentially even broader.<br />
<br />
So here's a script you can use to see exactly what Google considers to be a 'close variant'. The script pulls all queries that were triggered by close variants. It also pulls the associated keyword. It joins the query and keyword and then puts the data on a spreadsheet.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjPYHOULpys7qVG8iwHzTgzeJ8FaRpuDOLFUZbr0-nxtb8an9IGKFQu7Q3KT21OSbBMBUwod-SSgnWZr3R1FvjjEZCX2f53ndIYq6bcLBypRLb3j7amKL6-cDnfsMMIVVgY9ErTW41ivEA/s1600/close+variant+keyword+analysis.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="863" data-original-width="1600" height="344" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjPYHOULpys7qVG8iwHzTgzeJ8FaRpuDOLFUZbr0-nxtb8an9IGKFQu7Q3KT21OSbBMBUwod-SSgnWZr3R1FvjjEZCX2f53ndIYq6bcLBypRLb3j7amKL6-cDnfsMMIVVgY9ErTW41ivEA/s640/close+variant+keyword+analysis.png" width="640" /></a></div>
<div class="separator" style="clear: both; text-align: left;">
On the spreadsheet, you can see line by line what the keyword is and what the close variant is. If you want, you can compare metrics to decide if a close variant that seems a little odd may actually be outperforming the keyword. </div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
I thought it'd also be interesting to add an automatic way to see how close the query is to the keyword. Whereas going from the keyword [optmyzr] to 'opmyzr' seems like a definite good case of a close variant because it's a simple typo in a brand name, going from [campsites in yosemite] to 'campgrounds yosemite' may be too much of a stretch from the original keyword.</div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
To calculate the similarity of the close variant to the keyword, I found an algorithm for the <a href="https://en.wikipedia.org/wiki/Levenshtein_distance">Levenshtein distance</a> which counts the number of characters that must be deleted, edited or added to go from one string to another string. A big number in the Levenshtein column means the query is more different from the keyword.</div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
Grab the code below to try it in your Google Ads account. I've also included a table showing how match types work after the change from Google and a video that goes through using the script in case anyone is just getting their feet wet with scripts.</div>
<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiVbm6XICy3jyZs5YEj6Lry3VhPThwvjUtMVnOb7g_vJyuhdHDSpd_vqBgqav7kiWBAebrGuk-AQyWAbKVDmN3VK8YeLEPeGm6ZtzmXf5X7Ff-EFZhc5SD3CpQwNdpB9Ur-u2KLlPTrdtY/s1600/matchtype2018.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="599" data-original-width="878" height="435" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiVbm6XICy3jyZs5YEj6Lry3VhPThwvjUtMVnOb7g_vJyuhdHDSpd_vqBgqav7kiWBAebrGuk-AQyWAbKVDmN3VK8YeLEPeGm6ZtzmXf5X7Ff-EFZhc5SD3CpQwNdpB9Ur-u2KLlPTrdtY/s640/matchtype2018.png" width="640" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<iframe allowfullscreen="" class="YOUTUBE-iframe-video" data-thumbnail-src="https://i.ytimg.com/vi/6xD_8ipxlTs/0.jpg" frameborder="0" height="266" src="https://www.youtube.com/embed/6xD_8ipxlTs?feature=player_embedded" width="320"></iframe><br />
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
<script src="https://gist.github.com/siliconvallaeys/356b031b5d6da0857b43c35c1ef1ab5d.js"></script>
</div>
<br />Anonymoushttp://www.blogger.com/profile/12494484453470332653noreply@blogger.comtag:blogger.com,1999:blog-4377780743561064672.post-36923890403354881512018-03-29T10:47:00.001-07:002018-03-29T15:00:31.744-07:00Automatically Refresh Weather Data in a SpreadsheetBidding by weather is a classic example of the power of AdWords Scripts but the <a href="https://developers.google.com/adwords/scripts/docs/solutions/weather-based-campaign-management">code from Google</a> can be daunting to implement for scripting newbies. Recently my company Optmyzr launched the ability to connect external data to a Rule Engine, making it possible to bid by weather without writing any code.<br />
<br />
A solution like this uses a Google Sheet that is automatically updated with current weather conditions. While adding the API calls to fetch the weather is straightforward, getting it to automatically refresh this data is difficult. This post covers a solution for updating external data on a spreadsheet automatically.<br />
<br />
First, grab a <a href="https://docs.google.com/spreadsheets/d/1zN1uWpTNtWfrMVlyE4orMTOJU9ljmcGRwWz_PRTOVLo/copy">copy of this Google Spreadsheet</a> that contains a function courtesy of <a href="https://stackoverflow.com/users/1677912/mogsdad">Mogsdad</a> on <a href="https://stackoverflow.com/questions/33872967/periodically-refresh-importxml-spreadsheet-function/33875957#33875957">StackOverflow</a> that handles updating the weather. It does this by removing and then adding back all functions that deal with importing external data.<br />
<br />
This <a href="https://docs.google.com/spreadsheets/d/1zN1uWpTNtWfrMVlyE4orMTOJU9ljmcGRwWz_PRTOVLo/copy">spreadsheet</a> also contains the API connection strings that fetch the temperature and conditions for a zip code.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgocU4KttEOGmtcwejTP1YWUeOk4LL7YeaUD9vzEU6fyl5d4TYcDuDxMTUZBWRqIfw-79JMH3x12uS81liXfiuLzKYzQ8wqK735MfvAsYoJ_r6667wJxhHY_CBiHhRiPr9FhBklSqWkBNU/s1600/Weather+API+URL.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="245" data-original-width="1600" height="97" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgocU4KttEOGmtcwejTP1YWUeOk4LL7YeaUD9vzEU6fyl5d4TYcDuDxMTUZBWRqIfw-79JMH3x12uS81liXfiuLzKYzQ8wqK735MfvAsYoJ_r6667wJxhHY_CBiHhRiPr9FhBklSqWkBNU/s640/Weather+API+URL.jpg" width="640" /></a></div>
<br />
<br />
To be able to use the weather API, be sure to put in your own API key on the settings sheet. You can request one from <a href="http://openweathermap.org/">OpenWeatherMap.orghttps://openweathermap.org/appid#get</a>.<br />
<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh9x26BFX47AhJp4fdnWRYv0O1-jy66DbAgS-Rlfe8vr7_r2g6rM64cverUDp5IY5HPI8qzixJuuVQ5C0Wtx0ulZj5X7Z8g3uYF-aMloKtfKXiUNasIdurbTTwHCfF0L7rzc3d2C7kEzuM/s1600/Add+API+key+to+get+weather+data.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="452" data-original-width="1600" height="180" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh9x26BFX47AhJp4fdnWRYv0O1-jy66DbAgS-Rlfe8vr7_r2g6rM64cverUDp5IY5HPI8qzixJuuVQ5C0Wtx0ulZj5X7Z8g3uYF-aMloKtfKXiUNasIdurbTTwHCfF0L7rzc3d2C7kEzuM/s640/Add+API+key+to+get+weather+data.jpg" width="640" /></a></div>
<br />
<br />
To automatically update the weather on a schedule, set a time-based trigger for the function <a href="https://stackoverflow.com/questions/33872967/periodically-refresh-importxml-spreadsheet-function/33875957#33875957">RefreshImports</a> by going to the "Tools" menu in the spreadsheet and selecting "Script Editor" and then clicking the fields shown in the screenshot below:<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh3g49CFuZ2m8RnSQbCQQr5UN_OGFEaH7U5FcuvNkgu1SUCtP9LVs4PjQnJ3y1I6BraJi_U6ZMCXly35h0u2xHroKClh63PtKd0f19ygEZ7tLGtYTO9Ufdv0TyukSF7MCZSwsjTaxGYLhQ/s1600/Add+a+time+trigger.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="890" data-original-width="1600" height="354" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh3g49CFuZ2m8RnSQbCQQr5UN_OGFEaH7U5FcuvNkgu1SUCtP9LVs4PjQnJ3y1I6BraJi_U6ZMCXly35h0u2xHroKClh63PtKd0f19ygEZ7tLGtYTO9Ufdv0TyukSF7MCZSwsjTaxGYLhQ/s640/Add+a+time+trigger.jpg" width="640" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
You'll also need to update the code at line 17 to reference the ID of your own copy of the spreadsheet. For example, grab this part of the URL:</div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj6LdAy2SL5aQoW1qm82msVpAGY36RPNNHVXtYiVx-YtOryJQaXv1KfIidoSBZC49maoS7xQ1Z-rsnN6Sgz8zn9i-dra_-BXFyYjkIbWdvBusRPDk4KARtVLqtW3qdjATUwckgq9RKnBBs/s1600/what+is+the+id+of+a+google+spreadsheet.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="814" data-original-width="1600" height="324" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj6LdAy2SL5aQoW1qm82msVpAGY36RPNNHVXtYiVx-YtOryJQaXv1KfIidoSBZC49maoS7xQ1Z-rsnN6Sgz8zn9i-dra_-BXFyYjkIbWdvBusRPDk4KARtVLqtW3qdjATUwckgq9RKnBBs/s640/what+is+the+id+of+a+google+spreadsheet.jpg" width="640" /></a></div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
And put that into line 17 of the script that's associated with the spreadsheet here:</div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiurP1_gW9enwLtwcRFT6tL8coNdCDjVSQsmb5CpjH8DyB2zUHUD78Qmu6rrE8i7zaxupddkKUW3TnDcGY4bfVBVhyphenhyphen-5cmejZu1Dt9FtjHOJ27yYTWOkYaTdjk-1-_h4M1LCtAo0H3v1dM/s1600/where+to+copy+the+ID.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="300" data-original-width="1600" height="118" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiurP1_gW9enwLtwcRFT6tL8coNdCDjVSQsmb5CpjH8DyB2zUHUD78Qmu6rrE8i7zaxupddkKUW3TnDcGY4bfVBVhyphenhyphen-5cmejZu1Dt9FtjHOJ27yYTWOkYaTdjk-1-_h4M1LCtAo0H3v1dM/s640/where+to+copy+the+ID.jpg" width="640" /></a></div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
That's it. Now you have a spreadsheet that automatically refreshes with current weather data and you can use it for other AdWords automations.</div>
<br />
<br />
<br />
<br />Anonymoushttp://www.blogger.com/profile/12494484453470332653noreply@blogger.comtag:blogger.com,1999:blog-4377780743561064672.post-33055507495401953332018-03-22T19:36:00.002-07:002018-03-22T19:38:14.574-07:00Automatically Put Pivoted AdWords Data in a SpreadsheetI've written several free scripts that export AdWords data to a Google spreadsheet but the one I'll share below does something a little more and pivots the reports so that segment data shows up in columns rather than rows.<br />
<br />
<h3>
The Problem With Google's AdWords Reports</h3>
Here's what the typical output from an AdWords report looks like if you use a <a href="http://www.optmyzr.com/blog/a-script-to-put-any-adwords-data-in-a-spreadsheet/">script like this one</a> to add it in a Google sheet.<br />
<table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody>
<tr><td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjzRsDWGoJ-rf8DR6qHjX-aLkkuFUMcqG97-xtiZ6UVF6ss8JZHi4Od-NcSLnrdl3IWrbXeliR7dnl2d0Gc9ii_74oHDYZxifaRtTdt7FyeH0JudWAb0wpdvbbL0g7qCfbxWWzv_UbekKA/s1600/AdWords-Report-With-Segments.jpg" imageanchor="1" style="margin-left: auto; margin-right: auto;"><img border="0" data-original-height="1516" data-original-width="1288" height="400" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjzRsDWGoJ-rf8DR6qHjX-aLkkuFUMcqG97-xtiZ6UVF6ss8JZHi4Od-NcSLnrdl3IWrbXeliR7dnl2d0Gc9ii_74oHDYZxifaRtTdt7FyeH0JudWAb0wpdvbbL0g7qCfbxWWzv_UbekKA/s400/AdWords-Report-With-Segments.jpg" width="338" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;">AdWords report data that was automatically added to a Google spreadsheet. The report contains keyword conversion data segmented by conversion type name and day of week, resulting in multiple rows of data for each keyword.</td></tr>
</tbody></table>
I highlighted the cells for the same keyword ID in green so that you can easily see that there are 7 rows of data for the same keyword. Each row has different values for the segments 'ConversionType' and 'DayOfWeek'.<br />
<br />
Here's why I don't like getting my reports this way... In a typical AdWords optimization, I might evaluate a keyword to see if it meets certain criteria, and when it does, change the bid. For example, for keywords with more than 5 total "Sign Up" conversions, and at least 2 of those conversions on today's day of the week, I might raise the bid.<br />
<br />
This is not easy to do with the spreadsheet above because I can't simply build a formula for each row as that would ignore the data from the other 6 rows about the same entity.<br />
<br />
<h3>
A Better Format for AdWords Reports</h3>
To get the report the way I want, I need to do some vlookups and data aggregation so that all the data for a keyword is moved onto a single row. This is what my spreadsheet should look like to make optimizing easy:<br />
<br />
<table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody>
<tr><td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhGTCMPB-elgpTcQ717EculfKQal4w5owEc3e-RJME9cx2SfDyAQII64X0YYUoDHQ6qHrUBQEMFLU89z5EbNDgBVk0K1XlLe6Jwo-l4lWYjlkbH_ZPowx-BIW0i1ptYPS-Povm6Y5NFOCI/s1600/AdWords-Data-In-Spreadsheet-With-Pivot.jpg" imageanchor="1" style="margin-left: auto; margin-right: auto;"><img border="0" data-original-height="586" data-original-width="1600" height="233" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhGTCMPB-elgpTcQ717EculfKQal4w5owEc3e-RJME9cx2SfDyAQII64X0YYUoDHQ6qHrUBQEMFLU89z5EbNDgBVk0K1XlLe6Jwo-l4lWYjlkbH_ZPowx-BIW0i1ptYPS-Povm6Y5NFOCI/s640/AdWords-Data-In-Spreadsheet-With-Pivot.jpg" width="640" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;">AdWords report data that is aggregated by entities. Here there is one row for every keyword along with one column for every combination of metrics and segments.</td></tr>
</tbody></table>
<br />
Now you can see there is only 1 row for that same keyword ID as before (highlighted in green). Now I can write a spreadsheet formula that has access to all the elements I need on one row.<br />
<br />
<br />
<h3>
How Scripts Solved This Problem</h3>
To get the spreadsheet output in the format I wanted, I wrote a script that finds all possible values for the included segments and then combines this with the different metrics. For example, possible values for the DayOfWeek segment are 'Monday', 'Tuesday', 'Wednesday', etc. Combining this with my metrics, I get Monday.Conversions, Monday.AllConversions, etc. Each of these is then treated as a column in the sheet.<br />
<br />
So now I can more easily do my optimization. I could also feed this sheet into a tool like <a href="https://www.optmyzr.com/">Optmyzr's Rule Engine</a> so that I could start automating account management based on factors like day of week, type of conversion, or any other segment I include on the sheet. Optmyzr is my company and also runs this blog.<br />
<br />
<h3>
How to run this script:</h3>
After installing the script code that's all the way at the bottom of this post into an AdWords account (a child account, not an MCC account), you can edit the following settings to customize it:<br />
<br />
<ol>
<li><b>attributes</b>: these are the AdWords reporting attributes. You will get a single spreadsheet row per unique combination of attributes</li>
<li><b>segments</b>: these are the AdWords reporting segments. The values of each segment will be joined with metrics and create 1 column for each combination</li>
<li><b>metrics</b>: the AdWords reporting metrics to include.</li>
<li><b>sourceReport</b>: the report type from Google. See the link below for more information</li>
<li><b>spreadsheetUrl</b>: the Url of the Google spreadsheet that this script will update.</li>
<li><b>tabName</b>: the name of the sheet (tab) in the spreadsheet that should be updated.</li>
<li><b>reportVersion</b>: the version of the AdWords API reports you’re using. The data available in the Ads API changes periodically so this ensures our script talks to the right version of AdWords.</li>
</ol>
<br />
<br />
<h3>
Where to Find Valid Settings for the Script</h3>
Here is the list of acceptable attributes, segments and metrics for the keywords performance report:<br />
<a href="https://developers.google.com/adwords/api/docs/appendix/reports/keywords-performance-report">https://developers.google.com/adwords/api/docs/appendix/reports/keywords-performance-report</a>. As you can see, Google makes it really easy to see what are attributes vs. segments vs metrics. Just be sure to populate the right settings in the script based on what type of data you're adding.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgkJ7CJEcHEOTuYCB7HqL-7eQrJT553jAWgWvFJgy2lj9POQvv_ealZoHFVx_7owRqEN4vIV3Qjw-Tk564h5W74dVgop2g_nK8A2J_l0TSLyAbEbkT-9jhjaJR-TWpezoj6QCfW6ydMyHQ/s1600/attributes-segments-metrics.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="1183" data-original-width="1600" height="472" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgkJ7CJEcHEOTuYCB7HqL-7eQrJT553jAWgWvFJgy2lj9POQvv_ealZoHFVx_7owRqEN4vIV3Qjw-Tk564h5W74dVgop2g_nK8A2J_l0TSLyAbEbkT-9jhjaJR-TWpezoj6QCfW6ydMyHQ/s640/attributes-segments-metrics.jpg" width="640" /></a></div>
<br />
<br />
So for example, if you wanted to add ApprovalStatus to the report, you'd put add it to the comma separated list of attributes as seen here:<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEglCwEinqOE3oL0vZQGGvhX1VyRyOrp4DvBw7XsgSPiZfAFXCF8xbeXGL6_HergOFGIRiC_ieKKtqlLeyzFUAOse19fyiU0BLibhjsD8EwuZqubrjO2Ykgz3TN-xkkUpeG24hK0nQaWvGg/s1600/attributes.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="382" data-original-width="964" height="157" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEglCwEinqOE3oL0vZQGGvhX1VyRyOrp4DvBw7XsgSPiZfAFXCF8xbeXGL6_HergOFGIRiC_ieKKtqlLeyzFUAOse19fyiU0BLibhjsD8EwuZqubrjO2Ykgz3TN-xkkUpeG24hK0nQaWvGg/s400/attributes.jpg" width="400" /></a></div>
<br />
Here is the <a href="https://developers.google.com/adwords/api/docs/appendix/reports">full list of all AdWords reports</a> with the available attributes, segments, and metrics to help you create correct settings.<br />
<br />
<br />
<h3>
One thing to watch out for:</h3>
Note that in the resulting spreadsheet, you can't total up the numbers across a row because metrics are included multiple times, once per included segment. For example, in a report with the 2 segments DayOfWeek and ConversionType, if a keyword has 10 conversions, then the sum of all the conversion columns for that row will be 20 (10 conversions * 2 segments).<br />
<br />
<h3>
Another Popular Script For Putting AdWords Data in Spreadsheets</h3>
Last June I wrote a script that makes it super easy to do <a href="https://searchengineland.com/script-automates-adding-adwords-data-google-spreadsheet-277724">custom data pulls from AdWords and put the data in a spreadsheet</a> so I was happy to hear people still talking about this script at SMX West in March. You can find it on <a href="https://searchengineland.com/script-automates-adding-adwords-data-google-spreadsheet-277724">Search Engine Land</a> if you're looking for more options to get AdWords data in spreadsheets without paying someone for it.
<br />
<br />
<script src="https://gist.github.com/siliconvallaeys/66b10bfcdc7aab832debb487ffa0a460.js"></script>Anonymoushttp://www.blogger.com/profile/12494484453470332653noreply@blogger.comtag:blogger.com,1999:blog-4377780743561064672.post-15131599776480881932018-01-31T17:14:00.000-08:002018-01-31T17:14:25.067-08:00Working with Experiment Campaigns in AdWords ScriptsIf you've started using Google's Draft and Experiment campaigns, you may have noticed some odd behavior with scripts. We were getting these campaigns back in our selectors, even if they were no longer active experiments. And if we tried to change their status to 'ended', we couldn't do so.<br />
<br />
I'll explain the similarities and differences so that hopefully you can avoid some of the frustration I faced when we started using experiments in AdWords and some scripts started to break.<br />
<br />
Experiment campaigns are like other campaigns in the following ways:<br />
<br />
<ol>
<li>Experiment campaigns are returned in API reports like CAMPAIGN_PERFORMANCE_REPORT</li>
<li>Experiment campaigns are returned by AW Scripts selectors like campaignSelector = AdWordsApp.campaigns()</li>
</ol>
<div>
Experiment campaigns are unlike normal campaigns in some important ways:</div>
<div>
<ol>
<li>Experiment campaigns, once enabled are always enabled. If you end the experiment, the ServingStatus becomes 'ENDED' but the CampaignStatus remains 'ENABLED'</li>
<li>Experiment campaigns cannot have labels</li>
<li>Experiment campaigns cannot have their status or budget changed (as these are tied to the corresponding 'base' campaign)</li>
</ol>
<div>
As you can see, there are situations where some of these oddities can lead to issues. For example, if you have a script that pauses any campaigns that have exceeded a certain budget for the month (like those that Optmyzr provides), the script will recognize that an experiment campaign has spent too much but it will be unable to label it or pause it.</div>
</div>
<div>
<br /></div>
<div>
So I wanted to exclude experiment campaigns from my selectors and found this was possible and <a href="https://developers.google.com/adwords/scripts/docs/reference/adwordsapp/adwordsapp_campaignselector#withCondition_1">documented</a>. You can exclude campaigns of different types by adding the following to a selector:</div>
<div>
<ul>
<li>withCondition("CampaignExperimentType = BASE")</li>
<li>withCondition("CampaignExperimentType = DRAFT")</li>
<li>withCondition("CampaignExperimentType = EXPERIMENT")</li>
</ul>
</div>
<div>
<br /></div>
<div>
</div>
<br />
<div>
But there is also an undocumented feature in AdWords Scripts that can help filter these campaigns:</div>
<div>
<ul>
<li>withCondition("ServingStatus = SERVING")</li>
</ul>
</div>
<div>
The <a href="https://developers.google.com/adwords/api/docs/appendix/reports/campaign-performance-report#servingstatus">other possible condition values</a> are ENDED, PENDING, NONE, SUSPENDED</div>
<div>
<br /></div>
<div>
The cool trick I learned here is that you can use conditions that are not documented in the AdWords Scripts reference. You just refer to the fields in the corresponding API report and try adding them to selectors.</div>
<div>
<br /></div>
<div>
Thanks to Alex from the Google team for pointing this out.</div>
<div>
<br /></div>
Anonymoushttp://www.blogger.com/profile/12494484453470332653noreply@blogger.comtag:blogger.com,1999:blog-4377780743561064672.post-53752843094584216932017-12-18T17:06:00.000-08:002017-12-18T17:06:33.781-08:00Automatically Exclude Placements With a Particular Domain<div>
AdWords lets advertisers show ads on the Display Network (GDN) and while it has ways to target ads to show on specific domains, or to exclude specific domains, they don't have a wildcard placement exclusion feature. </div>
<div>
<br /></div>
<div>
So if you want to show your ads on all relevant placements, <b>except</b> those with a Polish domain extension (.pl) or an extension like .org, you would need to monitor automatic placements and add exclusions every time you saw one with this extension. </div>
<div>
<br /></div>
<div>
The following script makes it possible to automate excluding placements when the domain includes a particular string.<br />
<br />
Run this daily or weekly in your AdWords account to prevent accruing too many clicks from unwanted placements.<br />
<br />
Enjoy!<br />
Frederick Vallaeys<br />
Co-Founder, Optmyzr.com</div>
<div>
<br /></div>
<div>
<br /></div>
<div>
<br /></div>
<script src="https://gist.github.com/siliconvallaeys/fd6affc8d3ddedd016956fdc5481d294.js"></script><br />Anonymoushttp://www.blogger.com/profile/12494484453470332653noreply@blogger.comtag:blogger.com,1999:blog-4377780743561064672.post-43048970549056788742017-12-07T16:59:00.000-08:002017-12-07T16:59:35.733-08:00Get alerted when keywords or product groups spend too muchAutomated Rules in AdWords are great to set up alerts for when things spend too much without converting enough. But unfortunately these Automated Rules can only be run once per day, so they're not very useful if you want to get notified as soon as a keyword or product group exceeds your thresholds.<br />
<br />
So here's a simple script that queries for keywords or product groups that have exceeded a specific amount of cost, and have fewer than a specified number of conversions to show for that cost.<br />
<br />
It then emails the list of alerts to the user. The email can include deep links to AdWords to make it really easy to go and fix issues. If the instructions for the settings currentSetting.customerId, currentSetting.effectiveUserId, and currentSetting.ocid confuse you, check out an earlier post by Russ that explains <a href="http://www.freeadwordsscripts.com/2013/11/building-entity-deep-links-with-adwords.html">deep linking to ad groups in AdWords</a> in more detail.<br />
<br />
Thanks,<br />
Fred Vallaeys<br />
<br />
<script src="https://gist.github.com/siliconvallaeys/8723aa0950584b677157cab97975ddbe.js"></script>
<pre class="brush: js"></pre>
Anonymoushttp://www.blogger.com/profile/12494484453470332653noreply@blogger.comtag:blogger.com,1999:blog-4377780743561064672.post-40733257964371857862017-10-09T13:23:00.001-07:002017-10-09T13:23:30.490-07:00Limit AdWords Overdelivery to Any Amount You Want<br />
Google announced that daily budgets will now be able to overdeliver up to 100% rather than 20% as had been the case since the earliest days of AdWords.<br />
<br />
Overdelivery allows Google to help advertisers meet monthly budget targets by making up for slow traffic days by spending more money on high volume days. They assume that advertisers will divide their monthly budget by 30.4, and set this as the daily budget. Then when there may not be a whole lot of traffic on Saturdays and Sundays, Google can exceed the daily budget on Mondays and Tuesdays when there might be more people looking for what the advertiser sells.<br />
<br />
Here's Google's announcement:<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg7scoE1DxP47mIlSORAzAqMf5JA8OvZzN9ld9njbqcxDkI6Dlaoym2gKnGstiLiWpjwNhPpaWEFpJR3hMYJDRjbru8vF7HeRnAi48eqy5iWKLYkk9e_dgqVP-Vd2owkNbfpt9934wMTT4/s1600/Daily+Budget+Changes.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="760" data-original-width="670" height="320" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg7scoE1DxP47mIlSORAzAqMf5JA8OvZzN9ld9njbqcxDkI6Dlaoym2gKnGstiLiWpjwNhPpaWEFpJR3hMYJDRjbru8vF7HeRnAi48eqy5iWKLYkk9e_dgqVP-Vd2owkNbfpt9934wMTT4/s320/Daily+Budget+Changes.png" width="282" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
To truly control budgets they way you need, you'll probably want to use tools, automations, and AdWords Scripts. Here's a very basic script that lets you enforce a more strict overdelivery for a campaign. The script assumes that your daily budgets are the baseline of what you'd like to deliver. Use the setting 'allowedOverdeliveryPercentage' to control a maximum spend for the day by setting a value between 0% and 100%. The script fetches every active campaign's daily budget and accrued cost for the day. If the cost exceeds the daily budget + the allowed percentage of overdelivery, it will label that campaign and pause it.</div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
Important Notes:</div>
<div class="separator" style="clear: both; text-align: left;">
</div>
<ul>
<li>Remember to use another automation to re-enable all paused campaigns during the first hour of every day. You can look for campaigns that have the label set by the script and re-enable those. </li>
<li>The script can be run once per hour so you may still exceed the total cost until the next time the script runs. </li>
<li>The script doesn't deal with shared budgets.</li>
<li>The script doesn't deal with shopping and video campaigns. Making it work for those is really easy, you just have to update the campaigns call to use the video and <a href="https://developers.google.com/adwords/scripts/docs/reference/adwordsapp/adwordsapp_shoppingcampaignselector">shopping methods</a> for getting campaigns.</li>
</ul>
<div>
If you need more control over budgets, and you don't want to do any coding, consider our prebuilt scripts available as part of an <a href="http://www.optmyzr.com/">Optmyzr</a> subscription (Optmyzr is my company).</div>
<br />
<br />
<br />
<pre class="brush: js">/******************************************
*
* Version 1.0
* Created By: Frederick Vallaeys
* FreeAdWordsScripts.com
******************************************/
</pre>
<pre class="brush: js">function main() {
var allowedOverdeliveryPercentage = 0.2; // set percentage as decimal, i.e. 20% should be set as 0.2
var labelName = "paused by overdelivery checker script";
AdWordsApp.createLabel(labelName, "automatic label needed to reenable campaigns");
var campaigns = AdWordsApp.campaigns()
.withCondition("Status = ENABLED")
.withCondition("Cost > 0")
.forDateRange("TODAY");
var campaignIterator = campaigns.get();
while (campaignIterator.hasNext()) {
var campaign = campaignIterator.next();
var campaignName = campaign.getName();
var budgetAmount = campaign.getBudget().getAmount();
var costToday = campaign.getStatsFor("TODAY").getCost();
if(costToday > budgetAmount * (1 + allowedOverdeliveryPercentage)) {
Logger.log(campaignName + " has spent " + costToday + " which is more than allowed.");
campaign.applyLabel(labelName);
campaign.pause();
} else {
Logger.log(campaignName + " has spent " + costToday + " and can continue to run.");
}
}
}</pre>
Anonymoushttp://www.blogger.com/profile/12494484453470332653noreply@blogger.comtag:blogger.com,1999:blog-4377780743561064672.post-31309463939146374612017-10-01T16:03:00.001-07:002017-10-01T16:03:33.651-07:00How to Keep AdWords Scripts Running When the AdWords API Changes<br /><br />The AdWords API is regularly updated by Google with their latest capabilities. While it's great not to have to wait too long to get access to new capabilities, it comes with a downside too: AdWords Scripts may stop working on the day Scripts switch to using a newer version of the API. <br /><br />The reason is that new API version may rename or remove metrics and attributes. An AdWords Script that is not updated with these latest names will stop working. <div>
<br /></div>
<div>
You can find the <a href="https://developers.google.com/adwords/api/docs/sunset-dates">release dates of new API versions here</a> and the table looks like this:</div>
<div>
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg4HCJvaCXWDk-lse5GrZu3XG8LK3EAcTlNpXYDxRemGLB6vuHK5wp34ztaWfK_xZHvSi3qFDCLdbPAvtnYwzfzlPEgt7aA-2_O8xBXSmjO08KBTp3g3LUIM_pqlJshhAsSb6aSeHX4eH4/s1600/API+release+and+sunset+dates.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="376" data-original-width="1600" height="150" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg4HCJvaCXWDk-lse5GrZu3XG8LK3EAcTlNpXYDxRemGLB6vuHK5wp34ztaWfK_xZHvSi3qFDCLdbPAvtnYwzfzlPEgt7aA-2_O8xBXSmjO08KBTp3g3LUIM_pqlJshhAsSb6aSeHX4eH4/s640/API+release+and+sunset+dates.png" width="640" /></a></div>
<div>
<br /></div>
<div>
The release date is not always the date that AdWords Scripts start to use the newer version. As a result, it is very tricky to ensure that scripts you write continue to work. </div>
<div>
<br /></div>
<div>
Luckily there is a solution and it's as simple as telling your script which API version it should use by including the optional apiVersion argument. </div>
<div>
<br /></div>
<div>
A reporting call without the API version: </div>
<div>
<pre class="prettyprint" style="background: rgb(247, 247, 247); box-sizing: inherit; color: #37474f; font-family: "Roboto Mono", monospace; font-size: 14px; font-stretch: normal; line-height: 20px; margin-bottom: 16px; margin-top: 16px; overflow-x: auto; padding: 8px; position: relative;"><span class="kwd" style="box-sizing: inherit; color: #3b78e7;">var</span><span class="pln" style="box-sizing: inherit;"> report2 </span><span class="pun" style="box-sizing: inherit;">=</span><span class="pln" style="box-sizing: inherit;"> </span><span class="typ" style="box-sizing: inherit; color: #9c27b0;">AdWordsApp</span><span class="pun" style="box-sizing: inherit;">.</span><span class="pln" style="box-sizing: inherit;">report</span><span class="pun" style="box-sizing: inherit;">(</span><span class="pln" style="box-sizing: inherit;">
</span><span class="str" style="box-sizing: inherit; color: #0d904f;">'SELECT AdGroupId, Id, KeywordText, Impressions, Clicks '</span><span class="pln" style="box-sizing: inherit;"> </span><span class="pun" style="box-sizing: inherit;">+</span><span class="pln" style="box-sizing: inherit;">
</span><span class="str" style="box-sizing: inherit; color: #0d904f;">'FROM KEYWORDS_PERFORMANCE_REPORT '</span><span class="pln" style="box-sizing: inherit;"> </span><span class="pun" style="box-sizing: inherit;">+</span><span class="pln" style="box-sizing: inherit;">
</span><span class="str" style="box-sizing: inherit; color: #0d904f;">'DURING 20130101,20130301'</span><span class="pun" style="box-sizing: inherit;">);</span></pre>
</div>
<div>
And that same call with the API version:</div>
<div>
<pre class="prettyprint" style="background: rgb(247, 247, 247); box-sizing: inherit; color: #37474f; font-family: "Roboto Mono", monospace; font-size: 14px; font-stretch: normal; line-height: 20px; margin-bottom: 16px; margin-top: 16px; overflow-x: auto; padding: 8px; position: relative;"><span class="kwd" style="box-sizing: inherit; color: #3b78e7;">var</span><span class="pln" style="box-sizing: inherit;"> report2 </span><span class="pun" style="box-sizing: inherit;">=</span><span class="pln" style="box-sizing: inherit;"> </span><span class="typ" style="box-sizing: inherit; color: #9c27b0;">AdWordsApp</span><span class="pun" style="box-sizing: inherit;">.</span><span class="pln" style="box-sizing: inherit;">report</span><span class="pun" style="box-sizing: inherit;">(</span><span class="pln" style="box-sizing: inherit;">
</span><span class="str" style="box-sizing: inherit; color: #0d904f;">'SELECT AdGroupId, Id, KeywordText, Impressions, Clicks '</span><span class="pln" style="box-sizing: inherit;"> </span><span class="pun" style="box-sizing: inherit;">+</span><span class="pln" style="box-sizing: inherit;">
</span><span class="str" style="box-sizing: inherit; color: #0d904f;">'FROM KEYWORDS_PERFORMANCE_REPORT '</span><span class="pln" style="box-sizing: inherit;"> </span><span class="pun" style="box-sizing: inherit;">+</span><span class="pln" style="box-sizing: inherit;">
</span><span class="str" style="box-sizing: inherit; color: #0d904f;">'DURING 20130101,20130301'</span><span class="pun" style="box-sizing: inherit;">,</span><span class="pln" style="box-sizing: inherit;"> </span><span class="pun" style="box-sizing: inherit;">{</span><span class="pln" style="box-sizing: inherit;">
apiVersion</span><span class="pun" style="box-sizing: inherit;">:</span><span class="pln" style="box-sizing: inherit;"> </span><span class="str" style="box-sizing: inherit; color: #0d904f;">'v201605'</span><span class="pln" style="box-sizing: inherit;">
</span><span class="pun" style="box-sizing: inherit;">});</span></pre>
</div>
<div>
<br /></div>
<div>
By telling the script which API version to use, you guarantee that it will continue to work on the day that Google switches the default the a new version because you now control the switch that tells the script when your code has been updated and should start using a new API version.</div>
<div>
<br /></div>
<div>
You'll still need to do the migration at some point, but you'll have several months to do so. The sunset date in the table above indicates the final day that a script can use a particular API version. After that date, the old version will cease to work.</div>
<div>
<br /></div>
<div>
Note that you do NOT have to go through every API version. It's completely acceptable to skip a version if you don't need any of its capabilities. For example, say you were using v201609. Since it doesn't sunset until October 2, 2017, you could have waited for the release of v201708 on August 9, 2017, and skipped the 2 API versions in between.</div>
<div>
<br /></div>
<div>
The scripts in the Optmyzr Enhanced Scripts library handle all of these API transitions automatically for our users so if you'd rather not deal with API versions, it's a great solution to try. (Optmyzr is my employer)</div>
Anonymoushttp://www.blogger.com/profile/12494484453470332653noreply@blogger.comtag:blogger.com,1999:blog-4377780743561064672.post-74123476274879149382017-08-16T16:30:00.002-07:002017-08-17T11:12:23.879-07:00Automatically add AdWords Data to a Google SlideHave you ever had to give a presentation about the performance of an AdWords account and spent a lot of time copy-and-pasting data from AdWords into your slides? If so, now you can automatically push data from AdWords into Google Slides.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi9nf0GzW_hW2vHCOT6u2wyYx_N-MvXqkclQu3hpGMFWbe2ycN65R7HkcrNux31oQ-feFbnvv78pvYW6_wuzX5R71v5f95pkD78h-JtDXgouafobnMzmWYKv0Ab5t4Sqt-KK6MPsEuydiA/s1600/AdWords+Data+In+Slides.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="213" data-original-width="518" height="130" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi9nf0GzW_hW2vHCOT6u2wyYx_N-MvXqkclQu3hpGMFWbe2ycN65R7HkcrNux31oQ-feFbnvv78pvYW6_wuzX5R71v5f95pkD78h-JtDXgouafobnMzmWYKv0Ab5t4Sqt-KK6MPsEuydiA/s320/AdWords+Data+In+Slides.png" width="320" /></a></div>
<br />
<br />
This script leverages the recently announced integration of AdWords Scripts with the <a href="https://developers.google.com/apps-script/advanced/slides">Google Slides API</a>. Because this is one of the advanced APIs, the code is a bit more complicated and you will have to <a href="https://developers.google.com/adwords/scripts/docs/features/advanced-apis#enabling_advanced_apis">enable the Google Slides API</a> from the script through an additional authorization step.<br />
<br />
The code below appends a new slide to your Google Slide deck and adds some basic AdWords metrics. You can modify this code to add exactly the data from AdWords you want.<br />
<br />
<br />
<pre class="brush: js">
/*
// AdWords Script: Add a Slide with AdWords Data
// --------------------------------------------------------------
// Copyright 2017 Optmyzr Inc., All Rights Reserved
//
// This script takes a Google Presentation as input and appends a slide with basic AdWords metrics.
// Use this to automate creating an appendix of AdWords data to existing PPC report slides.
// The AW data we append is basic but can easily be tweaked to your own needs.
//
// For more PPC management tools and reports, visit www.optmyzr.com
//
*/
// Update this line with the presentation you want to edit.
// E.g. this is for presentation https://docs.google.com/presentation/d/1RxIzTJC6Jwwd3H5aaRjA-zj3d5IhcG9uOTuOfwk8PUg/edit#slide=id.optmyzr_slide_a1f911e6-9538-427d-9e2f-12fdc951f752
var PRESENTATION_ID = "1RxIzTJC6Jwwd3H5aaRjA-zj3d5IhcG9uOTuOfwk8PUg"
function main() {
var pageId = createSlide(PRESENTATION_ID);
// Get the page element IDs for a basic TITLE_AND_BODY layout
var baseElementId = readPageElementIds(PRESENTATION_ID, pageId);
var titleId = baseElementId + "_0";
var textId = baseElementId + "_1";
// Edit the following with the text for the slide's title
var titleText = "Automatically Fetched AdWords Data";
updateElement(PRESENTATION_ID, titleId, titleText);
// The next line gets text for the body section
var dataForSlide = getLastMonthData();
updateElement(PRESENTATION_ID, textId, dataForSlide);
Logger.log("Done updating slides at https://docs.google.com/presentation/d/" + PRESENTATION_ID);
}
function getLastMonthData() {
var currentAccount = AdWordsApp.currentAccount();
//Logger.log('Customer ID: ' + currentAccount.getCustomerId() +
// ', Currency Code: ' + currentAccount.getCurrencyCode() +
// ', Timezone: ' + currentAccount.getTimeZone());
var stats = currentAccount.getStatsFor('LAST_MONTH');
var clicks = stats.getClicks();
var impressions = stats.getImpressions();
var text = clicks + " clicks from " + impressions + " impressions.";
return(text);
}
function createSlide(presentationId) {
// You can specify the ID to use for the slide, as long as it's unique.
var pageId = Utilities.getUuid();
var requests = [{
"createSlide": {
"objectId": pageId,
//"insertionIndex": 1,
"slideLayoutReference": {
"predefinedLayout": "TITLE_AND_BODY"
}
}
}];
var slide =
Slides.Presentations.batchUpdate({'requests': requests}, presentationId);
//Logger.log(slide);
//Logger.log("Created Slide with ID: " + slide.replies[0].createSlide.objectId);
return (pageId);
}
function updateElement(presentationId, elementId, textToAdd) {
var requests = [{
"insertText": {
"objectId": elementId,
"text": textToAdd,
}
}];
var result =
Slides.Presentations.batchUpdate({'requests': requests}, presentationId);
//Logger.log(result);
}
function readPageElementIds(presentationId, pageId) {
// You can use a field mask to limit the data the API retrieves
// in a get request, or what fields are updated in an batchUpdate.
var response = Slides.Presentations.Pages.get(
presentationId, pageId, {"fields": "pageElements.objectId"});
//Logger.log(response);
var objectIds = response.pageElements[0].objectId;
var parts = objectIds.split("_");
var objectIdBase = parts[0] + "_" + parts[1];
//Logger.log("objectIdBase: " + objectIdBase);
return(objectIdBase);
}
</pre>
<br />
We maintain the <a href="https://gist.github.com/siliconvallaeys/0738399c51805e94173ed0b1a4d0d45e">most current version of this code on GitHub</a>.
<br />
<br />
For a fully automated way to create PPC reports with interesting visualizations like Quality Score, a word cloud, a cause chart, or a heatmap, take a look at <a href="http://www.optmyzr.com/" rel="nofollow">Optmyzr</a>, my company.
<br /><br />
Thanks,<br />
Fred<br />
<br />Anonymoushttp://www.blogger.com/profile/12494484453470332653noreply@blogger.comtag:blogger.com,1999:blog-4377780743561064672.post-13118313348898541432017-06-08T15:23:00.000-07:002017-06-15T00:30:41.900-07:00Pull Stock Quotes Into AdWords Scripts Using Yahoo! Finance APII was recently asked on Twitter if I had ever seen <a href="https://twitter.com/thegoogleguy/status/622404653783724032">a script that used stock market performance to adjust bids</a>. Honestly I never have, but I have been asked about this ability multiple times. So I thought I'd build something to do just that.<br />
<br />
Finding a reliable and free API for stock data is a little difficult, but everyone seems to point to a somewhat hidden Yahoo! Finance API. Despite the fact that there are multiple libraries built around it, I couldn't find much in the way of documentation other than a <a href="http://stackoverflow.com/questions/10040954/alternative-to-google-finance-api">StackOverflow post</a> that talks about it. So long story short, this API could stop working at anytime, so use at your own risk.<br />
<br />
Here is some sample code to get you started using this. The code below simply looks up a few quotes (one from Bitcoin) and loads them into a Google Spreadsheet of your choosing. Pretty straightforward. The one confusing thing is the "f=" parameter that you need to pass to the API. It is documented a little bit in <a href="http://www.jarloo.com/yahoo_finance/">this blog post</a> but is still pretty confusing. It is a string of one or two character codes that is used to define the columns you want to return. For most people, the symbol, name, and current price should be enough. Feel free to customize it as needed.<br />
<br />
Thanks,<br />
Russ<br />
<pre class="brush: js">/******************************************
* Yahoo Finance API Class Example
* Version 1.0
* Created By: Russ Savage
* FreeAdWordsScripts.com
******************************************/
function main() {
var sheetUrl = 'ENTER A GOOGLE SHEET URL HERE';
var yfa = new YahooFinanceAPI({
symbols: ['^GSPC','VTI','^IXIC','BTCUSD=X'],
f: 'snl1' // or something longer like this 'sl1abb2b3d1t1c1ohgv'
});
for(var key in yfa.results) {
Logger.log(Utilities.formatString('Name: "%s", Symbol: "%s", Last Trade Price: $%s',
yfa.results[key].name,
key,
yfa.results[key].last_trade_price_only));
}
var includeColumnHeaders = true;
var sheetData = yfa.toGoogleSheet(includeColumnHeaders);
var ss = SpreadsheetApp.openByUrl(sheetUrl).getActiveSheet();
for(var i in sheetData) {
ss.appendRow(sheetData[i]);
}
}
</pre>
<br />
Just copy the follow code into the bottom of your AdWords script and you should be good to go.<br />
<pre class="brush: js">/******************************************
* Yahoo Finance API Class
* Use this to pull stock market quotes from Yahoo Finance
* Version 1.0
* Created By: Russ Savage
* FreeAdWordsScripts.com
******************************************/
function YahooFinanceAPI(configVars) {
var QUERY_URL_BASE = 'https://query.yahooapis.com/v1/public/yql';
var FINANCE_URL_BASE = 'http://download.finance.yahoo.com/d/quotes.csv';
this.configVars = configVars;
/*************
* The results are stored here in a
* map where the key is the ticker symbol
* { 'AAPL' : { ... }, 'GOOG' : { ... }
*************/
this.results = {};
/************
* Function used to refresh the results
* from Yahoo! Finance API. Called automatically
* during object reaction.
************/
this.refresh = function() {
var queryUrl = getQueryUrl(this.configVars);
var resp = UrlFetchApp.fetch(queryUrl,{muteHttpExceptions:true});
if(resp.getResponseCode() == 200) {
var jsonResp = JSON.parse(resp.getContentText());
if(jsonResp.query.count == 1) {
var row = jsonResp.query.results.row;
this.results[row.symbol] = row;
} else if(jsonResp.query.count > 1) {
for(var i in jsonResp.query.results.row) {
var row = jsonResp.query.results.row[i];
this.results[row.symbol] = row;
}
}
} else {
throw resp.getContentText();
}
}
/************
* Translates the results into a 2d array
* to make it easier to add into a Google Sheet.
* includeColumnHeaders - true or false if you want
* headers returned in the results.
************/
this.toGoogleSheet = function(includeColumnHeaders) {
if(!this.results) { return [[]]; }
var retVal = [];
var headers = null;
for(var key in this.results) {
if(!headers) {
headers = Object.keys(this.results[key]).sort();
}
var row = [];
for(var i in headers) {
row.push(this.results[key][headers[i]]);
}
retVal.push(row);
}
if(includeColumnHeaders) {
return [headers].concat(retVal);
} else {
return retVal;
}
}
// Perform a refresh on object creation.
this.refresh();
// Private functions
/************
* Builds Yahoo Finance Url
************/
function getFinanceUrl(configVars) {
var financeUrlParams = {
s : configVars.symbols.join(','),
f : configVars.f,
e : '.json'
}
return FINANCE_URL_BASE + serialize(financeUrlParams);
}
/************
* Builds Yahoo Query Url
************/
function getQueryUrl(configVars) {
var financeUrl = getFinanceUrl(configVars);
var cols = fToCols(configVars.f);
var queryTemplate = "select * from csv where url='%s' and columns='%s'";
var query = Utilities.formatString(queryTemplate, financeUrl,cols.join(','));
var params = {
q : query,
format : 'json'
}
var finalRestUrl = QUERY_URL_BASE + serialize(params);
return finalRestUrl;
}
/************
* This function translates the f parameter
* into actual field names to use for columns
************/
function fToCols(f) {
var cols = [];
var chunk = '';
var fBits = f.split('').reverse();
for(var i in fBits) {
chunk = (fBits[i] + chunk);
if(fLookup(chunk)) {
cols.push(fLookup(chunk));
chunk = '';
}
}
return cols.reverse();
}
/************
* Copied from: http://stackoverflow.com/a/18116302
* This function converts a hash into
* a url encoded query string.
************/
function serialize( obj ) {
return '?'+
Object.keys(obj).reduce(
function(a,k) {
a.push(k+'='+encodeURIComponent(obj[k]));
return a
},
[]).join('&');
}
/************
* Adapted from http://www.jarloo.com/yahoo_finance/
* This function maps f codes into
* friendly column names.
************/
function fLookup(f){
return{
a:'ask',b:'bid',b2:'ask realtime',b3:'bid realtime',p:'previous close',o:'open',
y:'dividend yield',d:'dividend per share',r1:'dividend pay date',
q:'ex-dividend date',c1:'change',c:'change & percent change',c6:'change realtime',
k2:'change percent realtime',p2:'change in percent',d1:'last trade date',
d2:'trade date',t1:'last trade time',c8:'after hours change realtime',
c3:'commission',g:'days low',h:'days high',k1:'last trade realtime with time',
l:'last trade with time',l1:'last trade price only',t8:'1 yr target price',
m5:'change from 200 day moving average',m6:'percent change from 200 day moving average',
m7:'change from 50 day moving average',m8:'percent change from 50 day moving average',
m3:'50 day moving average',m4:'200 day moving average',w1:'days value change',
w4:'days value change realtime',p1:'price paid',m:'days range',m2:'days range realtime',
g1:'holdings gain percent',g3:'annualized gain',g4:'holdings gain',
g5:'holdings gain percent realtime',g6:'holdings gain realtime',t7:'ticker trend',
t6:'trade links',i5:'order book realtime',l2:'high limit',l3:'low limit',
v1:'holdings value',v7:'holdings value realtime',s6: 'revenue',k:'52 week high',
j:'52 week low',j5:'change from 52 week low',k4:'change from 52 week high',
j6:'percent change from 52 week low',k5:'percent change from 52 week high',
w:'52 week range',v:'more info',j1:'market capitalization',j3:'market cap realtime',
f6:'float shares',n:'name',n4:'notes',s:'symbol',s1:'shares owned',x:'stock exchange',
j2:'shares outstanding',v:'volume',a5:'ask size',b6:'bid size',k3:'last trade size',
a2:'average daily volume',e:'earnings per share',e7:'eps estimate current year',
e8:'eps estimate next year',e9:'eps estimate next quarter',b4:'book value',j4:'ebitda',
p5:'price sales',p6:'price book',r:'pe ratio',r2:'pe ratio realtime',r5:'peg ratio',
r6:'price eps estimate current year',r7:'price eps estimate next year',s7:'short ratio'
}[f];
}
}
</pre>Russell Savagehttp://www.blogger.com/profile/03030632304611412622noreply@blogger.comtag:blogger.com,1999:blog-4377780743561064672.post-78860017332355449472017-05-11T09:56:00.000-07:002017-06-15T00:32:58.832-07:00executeInParallel on More than 50 Accounts Using LabelsOne of the biggest limitations for people running MCC level scripts is the <a href="https://developers.google.com/adwords/scripts/docs/limits#entity_limits">50 account limit</a> imposed by the executeInParallel function. Until recently, one of the ways to get around this limit was to <a href="http://searchengineland.com/thinking-reusable-code-mcc-scripts-192836">store the processed accounts list on Google Drive</a> and update the file when needed.<br/><br/>
Recently, the AdWords Scripts team enabled the <a href="http://googleadsdeveloper.blogspot.com/2014/10/improvements-to-labels-in-adwords.html">ability to apply labels to each account from the MccApp object</a>. With this, we gain the ability to write a much cleaner version of the script that uses labels to indicate when each account has been processed. Using this method and running this script every hour, you could process up to 1,200 accounts per day.<br/><br/>
The following code is meant to provide a framework for you to substitute your own MccApp code into. You can then schedule this code to run every hour, and it will continue processing the accounts in your MCC until each one of them is finished.<br/><br/>
It will also attempt to notify you when there are accounts that return an error so that you can investigate. The one caveat about this script is that if you run into timeout limits on the Mcc level, namely in the results function, you might not apply the label to each successfully completed account. You can solve this by replacing line 76 with the following and removing line 89:<br /><br/>
applyLabelsToCompletedAccounts([result.getCustomerId()]);<br/><br/>
Anyway, I hope this helps and let me know if you run into any issues in the comments.
<br />
<br />
Thanks,<br />
Russ<br />
<br />
<pre class="brush: js">/******************************************
* MccApp Generic Runner Framework for any number of acounts
* Version 1.1
* Changelog v1.1 - fixed issue with selector in yesterdays label function
* Created By: Russ Savage (@russellsavage)
* FreeAdWordsScripts.com
******************************************/
// The name of the script you are running
// Used in error email subject line and label name
var SCRIPT_NAME = 'Generic MCC App';
// Since timezones are not available at the MCC level
// you need to set it here. You can use the local timezone
// of each account in the function processing each account
var TIMEZONE = 'PST';
// The date for today based on the timezone set above
var TODAY_STR = Utilities.formatDate(new Date(), TIMEZONE, 'yyyy-MM-dd');
// The label prefix which is used to figure out today's label and
// yesterday's label
var LABEL_PREFIX = SCRIPT_NAME + ' - Completed - ';
// This is the label that will be applied to each account
// when it is successfully processed
var FINISHED_LABEL_NAME = LABEL_PREFIX + TODAY_STR;
// This is a list of email addresses to notify when
// one of the accounts returns an error and is not processed.
var NOTIFY = ['your_email@your_domain.com'];
function main() {
// Warning: if running in preview mode, this function will fail
// and the selector that follows will also fail with "cannot read from AdWords"
createLabelIfNeeded();
removeYesterdaysLabel(); // This might not exist, but try to remove it
// Find all the accounts that have not been processed
var accountIter = MccApp.accounts()
.withCondition("LabelNames DOES_NOT_CONTAIN '"+FINISHED_LABEL_NAME+"'")
.withLimit(50)
.get();
// Add them to a list for the executeInParallel later
var accountList = [];
while(accountIter.hasNext()) {
var account = accountIter.next();
accountList.push(account.getCustomerId());
}
// If there are unprocessed accounts, process them
if(accountList.length > 0) {
MccApp.accounts()
.withIds(accountList)
.executeInParallel('doSomethingInEachAccount', 'reportOnResults');
}
}
// This function is called from executeInParallel and contains the
// business logic for each account. Right now, it just has some
// dummy logic to illustrate how this works.
function doSomethingInEachAccount() {
/**************
* Replace this function with what
* you want to do on each account
**************/
Logger.log("In account: "+AdWordsApp.currentAccount().getName()+
" "+AdWordsApp.currentAccount().getCustomerId());
// This function must return a string so we use JSON.stringify() to
// turn almost any object into a string quickly.
return JSON.stringify({something:'else'});
}
// This function will be called as soon as the function above
// has been run on each account. The results object is an array
// of the results returned by the function run in each account.
function reportOnResults(results) {
var completedAccounts = [];
var erroredAccounts = [];
for(var i in results) {
var result = results[i];
// If the account function returns success
if(result.getStatus() == 'OK') {
// Add it to the list to apply the label to
completedAccounts.push(result.getCustomerId());
/**********************
* Fill in the code to process the results from
* each account just below this.
**********************/
var returnedValue = JSON.parse(result.getReturnValue());
} else {
// In case of an error, we should notify someone so they can
// check it out.
erroredAccounts.push({customerId:result.getCustomerId(), error: result.getError()});
}
}
// Finally we apply the labels to each account
applyLabelsToCompletedAccounts(completedAccounts);
// And send an email with any errors
notifyOfAccountsWithErrors(erroredAccounts);
}
/*******************************
* Do not edit code below unless you know
* what you are doing.
*******************************/
// This function creates the required label to apply
// to completed accounts. You can change the label name
// by editing the FINISHED_LABEL_NAME variable at the top
// of this script.
function createLabelIfNeeded() {
try {
var labelIter = MccApp.accountLabels()
.withCondition("LabelNames CONTAINS '"+FINISHED_LABEL_NAME+"'")
.get();
} catch(e) {
MccApp.createAccountLabel(FINISHED_LABEL_NAME);
}
}
// This function applies FINISHED_LABEL_NAME to each completed account
function applyLabelsToCompletedAccounts(completedAccounts) {
var finishedAccountsIter = MccApp.accounts().withIds(completedAccounts).get();
while(finishedAccountsIter.hasNext()) {
var account = finishedAccountsIter.next();
account.applyLabel(FINISHED_LABEL_NAME);
}
}
// This function attempts to remove yesterday's label if it exists.
// If it doesn't exist, it does nothing.
function removeYesterdaysLabel() {
var yesterday = new Date();
yesterday.setDate(yesterday.getDate() - 1);
var yesterdayStr = Utilities.formatDate(yesterday, TIMEZONE, 'yyyy-MM-dd');
var yesterdayLabel = LABEL_PREFIX + yesterdayStr;
Logger.log("Attempting to remove label: "+yesterdayLabel);
try {
var labelIter = MccApp.accountLabels().withCondition("Name CONTAINS '"+yesterdayLabel+"'").get();
while(labelIter.hasNext()) {
labelIter.next().remove();
}
} catch(e) {
// do nothing
}
}
// This function will send an email to each email in the
// NOTIFY list from the top of the script with the specific error
function notifyOfAccountsWithErrors(erroredAccounts) {
if(!erroredAccounts || erroredAccounts.length == 0) { return; }
if(typeof NOTIFY == 'undefined') { throw 'NOTIFY is not defined.'; }
var subject = SCRIPT_NAME+' - Accounts with Errors - '+TODAY_STR;
var htmlBody = 'The following Accounts had errors on the last run.<br / >';
htmlBody += 'Log in to AdWords: http://goo.gl/7mS6A';
var body = htmlBody;
htmlBody += '<br / ><br / >';
htmlBody += '<table border="1" width="95%" style="border-collapse:collapse;">' +
'<tr><td>Account Id</td><td>Error</td></tr>';
for(var i in erroredAccounts) {
htmlBody += '<tr><td>'+ erroredAccounts[i].customerId +
'</td><td>' + erroredAccounts[i].error + '</td></tr>';
}
htmlBody += '</table>';
// Remove this line to get rid of the link back to this site.
htmlBody += '<br / ><br / ><a href = "http://www.freeadwordsscripts.com" >FreeAdWordsScripts.com</a>';
var options = { htmlBody : htmlBody };
for(var i in NOTIFY) {
Logger.log('Sending email to: '+NOTIFY[i]+' with subject: '+subject);
MailApp.sendEmail(NOTIFY[i], subject, body, options);
}
}
</pre>Russell Savagehttp://www.blogger.com/profile/03030632304611412622noreply@blogger.comtag:blogger.com,1999:blog-4377780743561064672.post-92019687228616625732017-04-13T21:33:00.000-07:002017-06-15T00:33:40.877-07:00Use Google Feed API to Convert RSS to JSONHere is a quick tip for anyone sick of working with the xml in RSS feeds. You can use the <a href="https://developers.google.com/feed/v1/">Google Feed API</a> in your scripts to automatically convert the XML in the RSS feed to JSON which makes working with them much easier. For example, there is an <a href="http://www.cpsc.gov/en/Newsroom/CPSC-RSS-Feed/Recalls-RSS/">RSS Feed</a> from the <a href="http://www.cpsc.gov/en/Newsroom/CPSC-RSS-Feed/">Consumer Product Safety Commission</a> and it has all the government recalls in the United States. Using this simple trick, you could easily pull the fresh feed on a regular basis and parse out information about recalls to use in your account. You can also configure Google Alerts to generate RSS feeds based on specific keywords you are interested in. For example, here is an <a href="https://www.google.com/alerts/feeds/14000181979335906394/11299291118581845747">RSS feed for the keyword "smx"</a>.
<br />
<br />
Thanks,<br />
Russ<br />
<br />
<pre class="brush: js">/******************************************
* Use Google Feed API to convert RSS to json
* Version 1.0
* Created By: Russ Savage
* FreeAdWordsScripts.com
******************************************/
// Usage: var jsonData = convertRssToJson('http://www.cpsc.gov/en/Newsroom/CPSC-RSS-Feed/Recalls-RSS/');
function convertRssToJson(rssUrl) {
var FEED_API_URL = "https://ajax.googleapis.com/ajax/services/feed/load?v=1.0&q="
var url = FEED_API_URL+encodeURIComponent(rssUrl);
var resp = UrlFetchApp.fetch(url);
if(resp.getResponseCode() == 200) {
return JSON.parse(resp.getContentText());
} else {
throw "An error occured while trying to parse: "+rssUrl;
}
}
</pre>Russell Savagehttp://www.blogger.com/profile/03030632304611412622noreply@blogger.comtag:blogger.com,1999:blog-4377780743561064672.post-40612207588017014682017-03-09T17:15:00.000-08:002017-06-15T00:34:47.028-07:00Pull Salesforce Data into AdWords Using ScriptsAfter my post about <a href="http://www.freeadwordsscripts.com/2014/05/connect-zoho-crm-data-with-adwords.html">importing Zoho CRM data into AdWords</a>, I received a lot of comments about doing something similar for Salesforce. I finally had some time to build a simple class that allows you to query data and objects from your Salesforce instance and use it in your scripts.<br />
<br />
First, we will need to set you up with some OAuth credentials, and for that, you need to set up a new connected app in Salesforce. It is in slightly different places in each version, but using the developer version, I was able to find it under Setup > Build > Create > Apps. From there, all the way at the bottom, you can see a section for Connected Apps.<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgZrneilZxe6v4xwbY_Rui0YIgwEXKsRPPwcChwas-y-f8ng-Dr05EwP5HewzIrT5X-zwH0QpY7y-__5eh3nF3qWeICFSNGL9l95V5K1T1s93VEBblzQSdWBmDkEePL0Db9DBB4B3IlRb7h/s1600/Apps___salesforce_com_-_Developer_Edition.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Creating a new connected app" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgZrneilZxe6v4xwbY_Rui0YIgwEXKsRPPwcChwas-y-f8ng-Dr05EwP5HewzIrT5X-zwH0QpY7y-__5eh3nF3qWeICFSNGL9l95V5K1T1s93VEBblzQSdWBmDkEePL0Db9DBB4B3IlRb7h/s400/Apps___salesforce_com_-_Developer_Edition.png" title="" /></a></div>
<br />
If for some reason you can't find it in your Salesforce instance, your admin may not have given you access to it. Hopefully, they can help you.
<br />
<br />
After clicking the new button, you will need to fill out a few required fields and then select the option to "Enable Oauth Settings." You will need to enter a callback url but we won't be using it so you can enter any url that starts with https. For "Scopes", I just said "Full Access" but you may have to talk to your Salesforce Admin about that one. We will only be reading from Salesforce so it shouldn't be an issue.<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh-W-ZgVcEknjiepQ4PIKPUytnklkePjBVm37WNa_DtEtKcRn7_G0G3Oq3Rsdg3hFvMuaA3D_IuemTsSNbjx5FrUrBAYizuw-uHLSv-tZWk7e7QCzE7MRAnPDGeZsfbNES6LvtRhadpw0ud/s1600/Connected_App___salesforce_com_-_Developer_Edition.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Enabling the OAuth Settings" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh-W-ZgVcEknjiepQ4PIKPUytnklkePjBVm37WNa_DtEtKcRn7_G0G3Oq3Rsdg3hFvMuaA3D_IuemTsSNbjx5FrUrBAYizuw-uHLSv-tZWk7e7QCzE7MRAnPDGeZsfbNES6LvtRhadpw0ud/s400/Connected_App___salesforce_com_-_Developer_Edition.png" title="" /></a></div>
<br />
That's all you need to fill out and you should have a new app created. The important thing here is the "Consumer Key" and the "Consumer Secret" that you will need for the script to connect to your Salesforce instance.
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEitGQn1slFTGnOs67EkCI9Yc0ez8CRHFoUv2tZBzecPJIY5bGN9qrPBHGKCaIauuCWlijfZDTK5tZ-6LyuMh3PWCDEdMray1cw5IQdlRmI_zuKfy9pX3X-CH5xLZyMvEQSH49ftgiFL2yoM/s1600/Connected_App__My_New_App___salesforce_com_-_Developer_Edition.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Salesforce Consumer and Secret Keys" border="0" height="119" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEitGQn1slFTGnOs67EkCI9Yc0ez8CRHFoUv2tZBzecPJIY5bGN9qrPBHGKCaIauuCWlijfZDTK5tZ-6LyuMh3PWCDEdMray1cw5IQdlRmI_zuKfy9pX3X-CH5xLZyMvEQSH49ftgiFL2yoM/s1600/Connected_App__My_New_App___salesforce_com_-_Developer_Edition.png" title="" width="320" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
The last thing you will need from your Salesforce instance is a security token. You may already have one in which case, you can skip this. But if not, you can reset it under My Settings > Personal > Reset my Security Token. It will email you a new token.<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg0ovutZ4Rt9Lfx0uih6uzZYjJ4wR5fW_FmMPTxwXoQ_2EBFFEpnPfRecg3NYdx3J8IQuKqzG6u2R7XTVC6eBqxPuj5lKD1hq9Tc1EX0AVqJxHH9jnzSxMNyLZvnewOJfRrY-3EShmHlOeb/s1600/Screen+Shot+2014-09-29+at+7.35.48+PM.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Reset your Security Token" border="0" height="199" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg0ovutZ4Rt9Lfx0uih6uzZYjJ4wR5fW_FmMPTxwXoQ_2EBFFEpnPfRecg3NYdx3J8IQuKqzG6u2R7XTVC6eBqxPuj5lKD1hq9Tc1EX0AVqJxHH9jnzSxMNyLZvnewOJfRrY-3EShmHlOeb/s1600/Screen+Shot+2014-09-29+at+7.35.48+PM.png" title="" width="320" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
Ok, now we are finally ready to get to the AdWords Scripts code. The following code will set up a new SalesforceAPI object and query the most recent Opportunities that were Close Won so that you can use that revenue in your AdWords account.</div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<script src="https://gist.github.com/russorat/5734bd182813a842153b.js"></script>
<br />
It's that simple. If you want to get all the information about a particular object after you query for it, you can use the function getObjectByUrl() and send it the url from the query results. To learn more about the query syntax, check out the <a href="http://www.salesforce.com/us/developer/docs/officetoolkit/index_Left.htm#StartTopic=Content%2Fsforce_api_calls_soql.htm|SkinName=webhelp">Salesforce SOQL documentation</a>.
<br />
<br />
There are a few caveats for this code. Every Salesforce installation is unique so there really is no way for me to really troubleshoot issues with your specific install. This code was tested on a fresh Salesforce for Developers account so your results may vary. You will probably have more luck contacting your Salesforce Admin than leaving a comment here. Also, you may notice that the code it using the <a href="https://www.salesforce.com/us/developer/docs/api_rest/Content/intro_understanding_username_password_oauth_flow.htm">least secure option</a> to log into Salesforce. This code with your username and password will be accessible to all users of your AdWords account, so be careful. It might be better to create a special Salesforce user with very limited permissions for something like this.<br />
<br />
If you think this is useful, come let me know at <a href="http://searchmarketingexpo.com/east/2014/full_agenda3#1504">SMX East</a> this week.
<br />
<br />
Thanks,<br />
Russ<br />
<br />
<script src="https://gist.github.com/russorat/80eb61a7b5218eb642f1.js"></script>Russell Savagehttp://www.blogger.com/profile/03030632304611412622noreply@blogger.comtag:blogger.com,1999:blog-4377780743561064672.post-19656792964920611722014-07-28T09:51:00.001-07:002014-07-28T10:11:35.219-07:00Save a File or Spreadsheet in a Specific Folder of GDriveHere is a quick code snippet to help you understand how to save a spreadsheet into a specific folder on Google drive.<br/><br/>
In order to create a file in a folder, you will need to find the parent folder and pass it to the DriveApp when you create the file. Normally, file paths are in the form of "/path/to/file/file.js", so it makes sense to keep that same construct when saving files to GDrive. The following code accepts the full file path and creates a new file in that folder.<br />
<br />
The next obvious question is how to create a spreadsheet. Unfortunately, you can't create it using the DriveApp, you need to use the SpreadsheetApp. Also, since the spreadsheet app cannot create files in a folder, you will need to create the spreadsheet, then move it to the correct folder. I usually create a separate function with similar logic to find the correct folder, then update the create logic to use SpreadsheetApp instead of DriveApp. See the second function for an example.
<br />
<br />
Thanks,<br />
Russ<br />
<br />
<pre class="brush: js">/******************************************
* Create File in a Specific Folder Path
* Version 1.0
* Created By: Russ Savage
* FreeAdWordsScripts.com
******************************************/
function createFile(filePath) {
var pathArray = filePath.trim().split('/');
var folder;
for(var i in pathArray) {
var parentName = pathArray[i];
if(!parentName || parentName === '') { continue; } // in the root folder
// source: http://goo.gl/P0LQ86
if(filePath.indexOf(parentName, filePath.length - parentName.length) !== -1) {
// This is creating the actual file
if(folder) {
if(folder.getFilesByName(parentName).hasNext()) {
Logger.log('Using existing file: '+parentName);
return folder.getFilesByName(parentName).next();
}
return folder.createFile(parentName,'');
} else {
if(DriveApp.getRootFolder().getFilesByName(parentName).hasNext()) {
Logger.log('Using existing file: '+parentName);
return DriveApp.getRootFolder().getFilesByName(parentName).next();
}
return DriveApp.getRootFolder().createFile(parentName,'');
}
}
if(!folder && !DriveApp.getRootFolder().getFoldersByName(parentName).hasNext()) {
Logger.log('Creating folder: '+parentName);
folder = DriveApp.getRootFolder().createFolder(parentName);
} else if(folder && !folder.getFoldersByName(parentName).hasNext()) {
Logger.log('Creating folder: '+parentName);
folder = folder.createFolder(parentName);
} else {
Logger.log('Using existing folder: '+parentName);
folder = (!folder) ? DriveApp.getRootFolder().getFoldersByName(parentName).next()
: folder.getFoldersByName(parentName).next();
}
}
// Should never get here
throw "Invalid file path: "+filePath;
}
</pre>
And to create a spreadsheet:
<pre class="brush: js">/******************************************
* Create Spreadsheet in a Specific Folder Path
* Version 1.0
* Created By: Russ Savage
* FreeAdWordsScripts.com
******************************************/
function createSpreadsheet(filePath) {
var pathArray = filePath.trim().split('/');
var folder;
for(var i in pathArray) {
var parentName = pathArray[i];
if(!parentName || parentName === '') { continue; } // in the root folder
// source: http://goo.gl/P0LQ86
if(filePath.indexOf(parentName, filePath.length - parentName.length) !== -1) {
// This is creating the actual file
if(folder) {
if(folder.getFilesByName(parentName).hasNext()) {
Logger.log('Using existing file: '+parentName);
var file = folder.getFilesByName(parentName).next();
return SpreadsheetApp.openById(file.getId());
}
// creates in folder
var spreadsheet = SpreadsheetApp.create(parentName);
var file = DriveApp.getRootFolder().getFilesByName(parentName).next();
folder.addFile(file);
DriveApp.getRootFolder().removeFile(file);
return spreadsheet;
} else {
if(DriveApp.getRootFolder().getFilesByName(parentName).hasNext()) {
Logger.log('Using existing file: '+parentName);
var file = DriveApp.getRootFolder().getFilesByName(parentName).next();
return SpreadsheetApp.openById(file.getId());
}
// creates in root folder
return SpreadsheetApp.create(parentName);
}
}
if(!folder && !DriveApp.getRootFolder().getFoldersByName(parentName).hasNext()) {
Logger.log('Creating folder: '+parentName);
folder = DriveApp.getRootFolder().createFolder(parentName);
} else if(folder && !folder.getFoldersByName(parentName).hasNext()) {
Logger.log('Creating folder: '+parentName);
folder = folder.createFolder(parentName);
} else {
Logger.log('Using existing folder: '+parentName);
folder = (!folder) ? DriveApp.getRootFolder().getFoldersByName(parentName).next()
: folder.getFoldersByName(parentName).next();
}
}
// Should never get here
throw "Invalid file path: "+filePath;
}
</pre>Russell Savagehttp://www.blogger.com/profile/03030632304611412622noreply@blogger.comtag:blogger.com,1999:blog-4377780743561064672.post-66953211879459146392014-07-27T11:59:00.000-07:002014-07-29T09:42:08.057-07:00Exception Invalid reporting query INVALID_PREDICATE_ENUM_VALUE ACTIVEUPDATE: Here is a <a href="https://developers.google.com/adwords/scripts/community/?place=msg%2Fadwords-scripts%2FSxszEwM08Ss%2FchQmgBlA4YUJ">note from the AdWords Scripts team on the topic</a>.<br /><br />
A few days ago, I started getting a bunch of errors from my scripts that looked like this:<br />
<pre class="brush: js">Exception: Invalid reporting query: INVALID_PREDICATE_ENUM_VALUE: ACTIVE</pre><br />
If anyone else is seeing this issue, it has to do with an update to the reporting API. Previously, ACTIVE used to be a valid enum value for CampaignStatus, which was always sort of an anomaly since everything else used ENABLED. It has been updated to match the other Status values but in the process, broke any script that was using the ACTIVE status. Here is an example:
<br />
<pre class="brush: js">
...
var query = ['select',cols.join(','),'from',report,
'where CampaignStatus = ACTIVE',
'and AdGroupStatus = ENABLED',
'and Status = ACTIVE',
'during','TODAY'].join(' ');
...
</pre>
This should be changed to:
<pre class="brush: js">
...
var query = ['select',cols.join(','),'from',report,
'where CampaignStatus = ENABLED',
'and AdGroupStatus = ENABLED',
'and Status = ENABLED',
'during','TODAY'].join(' ');
...
</pre>
<br />
I am trying to go back and update my previous scripts but it might take some time.
<br />
Thanks,<br />
RussRussell Savagehttp://www.blogger.com/profile/03030632304611412622noreply@blogger.comtag:blogger.com,1999:blog-4377780743561064672.post-14072884902895252902014-06-19T20:33:00.001-07:002014-06-19T20:33:20.449-07:00Calling All AdWords Scripts DevelopersI was thinking of putting together a directory for any developers or companies out there that are currently writing Adwords Scripts for clients and would be interested in being contacted. I figured a picture or logo, name, short bio, location, and website would be a good start for this. If you are interested in being listed, please <a href="http://www.savageautomation.com/developer-directory">fill out this form</a> and I will add you to the directory that is linked in the sidebar.<br/>
<br/>
Thanks,<br/>
RussRussell Savagehttp://www.blogger.com/profile/03030632304611412622noreply@blogger.comtag:blogger.com,1999:blog-4377780743561064672.post-5814093604452170372014-05-05T19:20:00.001-07:002016-03-04T08:55:01.747-08:00Connect Zoho CRM Data with AdWords Using ScriptsFor anyone working in B2B Pay Per Click, one of the biggest headaches is trying to report on the entire sales flow within a single report. The biggest issue is that marketing PPC data lives in AdWords (clicks, impressions, MQLs) and the sales data lives in your CRM (Leads, Contacts, Opportunities, Etc.). So I started looking at ways to connect the two sources of data. I don't use a CRM, so I signed up for a free trial of <a href="https://www.zoho.com/">Zoho CRM</a> and started fiddling with their API. It turns out, they have a REST(ish) API that returns JSON objects, which is perfect for AdWords scripts.<br />
<br />
I built the Class below to pull data out of Zoho. It has the ability to pull <a href="https://www.zoho.com/crm/help/api/modules-fields.html#Leads">Leads</a>, <a href="https://www.zoho.com/crm/help/api/modules-fields.html#Contacts">Contacts</a>, <a href="https://www.zoho.com/crm/help/api/modules-fields.html#Potentials">Potentials</a> and just about any other <a href="https://www.zoho.com/crm/help/api/modules-fields.html">Zoho object</a> you can think of directly from your CRM. I stopped at just being able to get data out since updating or deleting records seemed less useful for AdWords scripts.<br />
<br />
Here is a quick reference guide to the Class. Define a new object with var zoho = new ZohoApi(YOUR_API_TOKEN); If you need help setting up your account for API access or generating a token, check out the <a href="https://www.zoho.com/crm/help/api/">Zoho Developer Docs</a>. I generated some simple getters for each Zoho object. ZohoApi.get[Objects]() will pull in all of the particular object. So you can say zoho.getLeads() or zoho.getPotentials(). You can also get only those objects belonging to you with zoho.getMyLeads().<br />
<br />
If you have any additional url parameters you want to send over with the request, you can add them as a parameter to the function. For example, if you wanted to return the first 100 records (instead of the default 20), you would say zoho.getLeads({fromIndex:1,toIndex:100});<br />
<br />
You can also search for records using zoho.search[Objects](). So to search for Potentials that have been won, you would say zoho.searchPotentials({'searchCondition':'(Stage|=|Closed Won)'}); You can read more about Zoho's <a href="https://www.zoho.com/crm/help/api/getsearchrecords.html#Examples">searchCondition syntax in their API Docs</a>. As part of that, you can put the columns you want to see or if you don't put anything in there, I pull the full set of columns to display for you using the get[Objects]Fields() method.
<br />
<br />
As for the response from the class, you will get an array of objects. Each key in the object has been lowercased with spaces replaced by underscores. For example, retVal[0].first_name or retVal[0].annual_revenue.
<br />
<br />
So give it a shot and let me know what you think in the comments. I put together a simple example script at the very bottom of this post to store Impressions, Clicks, Conversions, and Closed Won Potentials in a Google Doc on a daily basis to give you an idea of what you can do. Let me know what you would like to see next.
<br />
<br />
Thanks,<br />
Russ<br />
<br />
<script src="https://gist.github.com/russorat/034d68735930693468a8.js"></script>
<br />
<br />
And here is a really simple example of how you could combine conversion data from multiple sources into a single Google Spreadsheet report.
<script src="https://gist.github.com/russorat/4a42eaac29b5a521b417.js"></script>Russell Savagehttp://www.blogger.com/profile/03030632304611412622noreply@blogger.comtag:blogger.com,1999:blog-4377780743561064672.post-20972604640842496042014-04-10T00:47:00.001-07:002017-07-05T11:50:56.172-07:00Monitor Broken Links Using MCC Level ScriptsNote: I recommend you take a look at the <a href="https://developers.google.com/adwords/scripts/docs/solutions/link-checker#mccapp">official solution from the AdWords Scripts team</a> before implementing this solution.<br />
<br />
For anyone who didn't know, they are finally here in Beta form. AdWords scripts are now <a href="https://developers.google.com/adwords/scripts/community/?place=topic%2Fadwords-scripts%2FmKdF-p8w66s%2Fdiscussion" target="_blank">available at the MCC level</a>. If you want access to these beta features, all you need to do is <a href="https://services.google.com/fb/forms/mccscripts/" target="_blank">apply here</a> and wait for the team at Google to give you access.<br />
<br />
So what's new with MCC level scripting? The full details are at the <a href="https://developers.google.com/adwords/scripts/docs/features/mcc">Google Developers page</a>, but here is a summary. You can now kick off selectors on Accounts by using the MccApp object. Filtering by stats is the same as other selectors only now you run it at the account level. As you work through each Account, once you set the account you want to work on using MccApp.select(), everything works just like it used to.<br />
<br />
The one new function you will probably want to take advantage of is executeInParallel() which allows you to execute the same code across up to 50 accounts at the same time. So you can kick off a reporting script to run across all of your accounts, then collect the results and send a single email or store it to a single spreadsheet. Also, scripts can now run up to 60 minutes using this method since you get 30 minutes of execution time to run the code on each account and 30 minutes to collect the results from the callback function.<br />
<br />
To get you started with the new MccApp object, I thought I would take one of my most popular posts and rewrite it to run at the MCC level. <a href="http://www.freeadwordsscripts.com/2013/04/report-on-broken-urls-in-your-account.html" target="">Finding Broken Urls in your Account</a> is a great example of how you can leverage the new executeInParallel() function to improve the monitoring of your MCC.<br />
<br />
This script works very similarly to the previous script but has a few added features. This script will check all your Keyword and Ad urls once per day. When you install this script, you should schedule it to run hourly in case there is a large account which can't be processed in the allotted timeframe, it can pick up from where it left off and continue processing. It controls this internally using labels.<br />
<br />
Also, the results of this script are stored in a new spreadsheet for each run. I had issues with the last one where the script would overwrite the values in the spreadsheet before I had a chance to look at them. This eliminates that issue. The spreadsheet is accessed from a summary email that looks like this, with each row containing a link to the spreadsheet tab with that account's results.<br />
<table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody>
<tr><td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEilJuo-c4R8fp9vObSMox4ghPpbv5gb_mJGsBvGzlSrVPqc_02DNNgaI8DvFNs_qSnCPvkVlQf0y1yLYZwcRv3KnZQQHwbv8i5-XaswR3QQ3hXwkIU-AGBb9ApGWKYZY5YUGucqzwJoNwJw/s1600/Gmail_-_Broken_Urls_Checker_Summary_Results.png" imageanchor="1" style="margin-left: auto; margin-right: auto;"><img alt="" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEilJuo-c4R8fp9vObSMox4ghPpbv5gb_mJGsBvGzlSrVPqc_02DNNgaI8DvFNs_qSnCPvkVlQf0y1yLYZwcRv3KnZQQHwbv8i5-XaswR3QQ3hXwkIU-AGBb9ApGWKYZY5YUGucqzwJoNwJw/s1600/Gmail_-_Broken_Urls_Checker_Summary_Results.png" height="161" title="Broken Urls Checker Summary Email" width="400" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;">An Example Email from the Script</td></tr>
</tbody></table>
There are a few other features such as the ability to notify you when errors occur, report on redirects, and setting any number of response codes you are looking for. Take it for a test drive and let me know what you think in the comments.<div>
<br /></div>
<div>
Also, just a quick note that I am getting back in the swing of things after getting married at the end of March so look for a much more frequent posting schedule. I am speaking at the <a href="http://www.marketingfestival.cz/en" target="_blank">Marketing Festival in Brno, Czech Republic</a> at the end of October so I look forward to meeting anyone who can make it.<br /><br />
Thanks,<br />
Russ<br />
<br />
<pre class="brush: js">/******************************************
* Monitor Broken Links Using MCC Level Scripts
* Version 1.5
* Changelog v1.5
* - Additional fixes from copy and paste errors
* Changelog v1.4
* - Fixed INVALID_QUERY error
* Changelog v1.3
* - Added previous version of report api to script until
* I update my urls.
* Changelog v1.2
* - Fixing INVALID_PREDICATE_ENUM_VALUE
* Changelog v1.1
* - Stopped timeouts
* Created By: Russ Savage
* FreeAdWordsScripts.com
******************************************/
var SCRIPT_NAME = 'Broken Url Checker';
var LOG_LEVEL = 'error'; //change this to debug if you want more logging
var NOTIFY = ['<YOUR-EMAIL@example.com>'];
var SPREADSHEET_PREFIX = 'Broken Url Details'; // A timestamp is appended
var NOTIFY_ON_ERROR = ['<YOUR-EMAIL@example.com>'];
var STRIP_QUERY_STRING = true; //Drop everything after the ? in the url to speed things up
var REPORT_ON_REDIRECTS = true; //If you want to be able to track 301s and 302, turn this on
var VALID_RESPONSE_CODES = [200,301,302];
var URLS_CHECKED_FILE_NAME = 'UrlsAlreadyChecked-'+AdWordsApp.currentAccount().getCustomerId()+'.json';
var DONE_LABEL_PREFIX = 'All Urls Checked - ';
function main() {
MccApp.accounts().withLimit(50).executeInParallel('checkUrls', 'reportResults');
}
function checkUrls() {
try {
debug('Processing account: '+AdWordsApp.currentAccount().getName());
debug('Checking to see if we finished processing for today.');
var dateStr = Utilities.formatDate(new Date(), AdWordsApp.currentAccount().getTimeZone(), 'yyyy-MM-dd');
var finishedLabelName = DONE_LABEL_PREFIX+dateStr;
var alreadyDone = AdWordsApp.labels().withCondition("Name = '"+finishedLabelName+"'").get().hasNext();
if(alreadyDone) {
info('All urls have been checked for today.');
return '';
}
var labelIter = AdWordsApp.labels().withCondition("Name STARTS_WITH '"+DONE_LABEL_PREFIX+"'").get();
while(labelIter.hasNext()) { labelIter.next().remove(); }
debug('Checking for previous urls.');
var urlsAlreadyChecked = readValidUrlsFromJSON();
info('Found '+Object.keys(urlsAlreadyChecked).length+' urls already checked.');
var toReportKeywords = [];
var toReportAds = [];
var didExitEarly = false;
var keywordUrls = getKeywordUrls();
for(var key in keywordUrls) {
var kwRow = keywordUrls[key];
var final_urls = kwRow.FinalUrls.split(';');
for(var i in final_urls) {
var url = cleanUrl(final_urls[i]);
verifyUrl(kwRow,url,urlsAlreadyChecked,toReportKeywords);
if(shouldExitEarly()) { didExitEarly = true; break; }
}
}
if(!didExitEarly) {
var adUrls = getAdUrls();
for(var i in adUrls) {
var adRow = adUrls[i];
if(adRow.CreativeFinalUrls) {
var final_urls = adRow.CreativeFinalUrls.split(';');
for(var x in final_urls) {
var url = cleanUrl(final_urls[x]);
verifyUrl(adRow,url,urlsAlreadyChecked,toReportAds);
}
}
if(shouldExitEarly()) { didExitEarly = true; break; }
}
}
var returnData = {
accountId : AdWordsApp.currentAccount().getCustomerId(),
accountName : AdWordsApp.currentAccount().getName(),
uniqueUrlsChecked : Object.keys(urlsAlreadyChecked).length,
brokenKeywords : toReportKeywords,
brokenAds : toReportAds,
didExitEarly : didExitEarly
};
if(didExitEarly) {
writeValidUrlsToJSON(urlsAlreadyChecked);
} else {
AdWordsApp.createLabel(finishedLabelName, 'Label created by '+SCRIPT_NAME, '#C0C0C0');
writeValidUrlsToJSON({});
}
return JSON.stringify(returnData);
} catch(e) {
// This error handling helps notify you when things don't work out well.
error(e);
if(MailApp.getRemainingDailyQuota() >= NOTIFY_ON_ERROR.length) {
var acctName = AdWordsApp.currentAccount().getName();
var acctId = AdWordsApp.currentAccount().getCustomerId();
for(var i in NOTIFY_ON_ERROR) {
info('Sending mail to: '+NOTIFY_ON_ERROR[i]);
MailApp.sendEmail(NOTIFY_ON_ERROR[i], 'ERROR: '+SCRIPT_NAME+' - '+acctName+' - ('+acctId+')', e);
}
} else {
error('Out of email quota for the day. Sending a carrier pigeon.');
}
return '';
}
function shouldExitEarly() {
return (AdWordsApp.getExecutionInfo().getRemainingTime() < 60);
}
function verifyUrl(row,url,urlsAlreadyChecked,toReport) {
if(!urlsAlreadyChecked[url]) {
info('Checking url: ' + url);
var urlCheckResults = checkUrl(url);
if(!urlCheckResults.isValid) {
row['cleanUrl'] = url;
row['responseCode'] = urlCheckResults.responseCode;
toReport.push(row);
}
urlsAlreadyChecked[url] = urlCheckResults;
} else {
if(!urlsAlreadyChecked[url].isValid) {
row['cleanUrl'] = url;
row['responseCode'] = urlsAlreadyChecked[url].responseCode;
toReport.push(row);
}
}
}
function checkUrl(url) {
var retVal = { responseCode : -1, isValid: false };
var httpOptions = {
muteHttpExceptions:true,
followRedirects:(!REPORT_ON_REDIRECTS)
};
try {
retVal.responseCode = UrlFetchApp.fetch(url, httpOptions).getResponseCode();
retVal.isValid = isValidResponseCode(retVal.responseCode);
} catch(e) {
warn(e.message);
//Something is wrong here, we should know about it.
retVal.isValid = false;
}
return retVal;
}
function isValidResponseCode(resp) {
return (VALID_RESPONSE_CODES.indexOf(resp) >= 0);
}
//Clean the url of query strings and valuetrack params
function cleanUrl(url) {
if(STRIP_QUERY_STRING) {
if(url.indexOf('?')>=0) {
url = url.split('?')[0];
}
}
if(url.indexOf('{') >= 0) {
//Let's remove the value track parameters
url = url.replace(/\{[^\}]*\}/g,'');
}
return url;
}
//Use the reporting API to pull this information because it is super fast.
//The documentation for this is here: http://goo.gl/IfMb31
function getKeywordUrls() {
var OPTIONS = { includeZeroImpressions : true };
var cols = ['CampaignId','CampaignName',
'AdGroupId','AdGroupName',
'Id','Criteria','KeywordMatchType',
'IsNegative','FinalUrls','Impressions'];
var report = 'KEYWORDS_PERFORMANCE_REPORT';
var query = ['select',cols.join(','),'from',report,
'where CampaignStatus = ENABLED',
'and AdGroupStatus = ENABLED',
'and Status = ENABLED',
'during','LAST_7_DAYS'].join(' ');
var results = {};
var reportIter = AdWordsApp.report(query, OPTIONS).rows();
while(reportIter.hasNext()) {
var row = reportIter.next();
if(row.IsNegative === 'true') { continue; }
if(!row.FinalUrls) { continue; }
if(row.KeywordMatchType === 'Exact') {
row.Criteria = ['[',row.Criteria,']'].join('');
} else if(row.Criteria === 'Phrase') {
row.Criteria = ['"',row.Criteria,'"'].join('');
}
var rowKey = [row.CampaignId,row.AdGroupId,row.Id].join('-');
results[rowKey] = row;
}
return results;
}
//Use the reporting API to pull this information because it is super fast.
//The documentation for this is here: http://goo.gl/8RHTBj
function getAdUrls() {
var OPTIONS = { includeZeroImpressions : true };
var cols = ['CampaignId','CampaignName',
'AdGroupId','AdGroupName',
'AdType',
'Id','Headline','Description1','Description2','DisplayUrl',
'CreativeFinalUrls','Impressions'];
var report = 'AD_PERFORMANCE_REPORT';
var query = ['select',cols.join(','),'from',report,
'where CampaignStatus = ENABLED',
'and AdGroupStatus = ENABLED',
'and Status = ENABLED',
'during','TODAY'].join(' ');
var results = {};
var reportIter = AdWordsApp.report(query, OPTIONS).rows();
while(reportIter.hasNext()) {
var row = reportIter.next();
if(!row.CreativeFinalUrls) { continue; }
var rowKey = [row.CampaignId,row.AdGroupId,row.Id].join('-');
results[rowKey] = row;
}
return results;
}
//This function quickly writes the url data to a file
//that can be loaded again for the next run
function writeValidUrlsToJSON(toWrite) {
var file = getFile(URLS_CHECKED_FILE_NAME,false);
file.setContent(JSON.stringify(toWrite));
}
//And this loads that stored file and converts it to an object
function readValidUrlsFromJSON() {
var file = getFile(URLS_CHECKED_FILE_NAME,false);
var fileData = file.getBlob().getDataAsString();
if(fileData) {
return JSON.parse(fileData);
} else {
return {};
}
}
}
//This is the callback function that collects all the data from the scripts
//that were run in parallel on each account. More details can be found here:
// http://goo.gl/BvOPZo
function reportResults(responses) {
var summaryEmailData = [];
var dateTimeStr = Utilities.formatDate(new Date(), AdWordsApp.currentAccount().getTimeZone(), 'yyyy-MM-dd HH:m:s');
var spreadsheetName = SPREADSHEET_PREFIX+' - '+dateTimeStr;
for(var i in responses) {
if(!responses[i].getReturnValue()) { continue; }
var res = JSON.parse(responses[i].getReturnValue());
var sheetUrl = writeResultsToSpreadsheet(res,spreadsheetName);
summaryEmailData.push({accountId:res.accountId,
accountName:res.accountName,
didExitEarly:res.didExitEarly,
uniqueUrlsChecked:res.uniqueUrlsChecked,
numBrokenKeywords:res.brokenKeywords.length,
numBrokenAds:res.brokenAds.length,
sheetUrl: sheetUrl});
}
if(summaryEmailData.length > 0) {
sendSummaryEmail(summaryEmailData);
}
function writeResultsToSpreadsheet(res,name) {
var file = getFile(name,true);
var spreadsheet;
var maxRetries = 0;
while(maxRetries < 3) {
try {
spreadsheet = SpreadsheetApp.openById(file.getId());
break;
} catch(e) {
maxRetries++;
Utilities.sleep(1000);
}
}
if(!spreadsheet) { throw 'Could not open file: '+name; }
if(spreadsheet.getSheetByName('Sheet1')) {
spreadsheet.getSheetByName('Sheet1').setName(res.accountId);
}
var sheet = spreadsheet.getSheetByName(res.accountId);
if(!sheet) {
sheet = spreadsheet.insertSheet(res.accountId, spreadsheet.getSheets().length);
}
var toWrite = [['Type','Clean Url','Response Code','Campaign Name','AdGroup Name','Text','Full Url']];
for(var i in res.brokenKeywords) {
var row = res.brokenKeywords[i];
toWrite.push(['Keyword',
row.cleanUrl,
row.responseCode,
row.CampaignName,
row.AdGroupName,
row.Criteria,
row.FinalUrls]);
}
for(var i in res.brokenAds) {
var row = res.brokenAds[i];
toWrite.push([row.AdType,
row.cleanUrl,
row.responseCode,
row.CampaignName,
row.AdGroupName,
(row.Headline) ? [row.Headline,row.Description1,row.Description2,row.DisplayUrl].join('|') : '',
row.CreativeFinalUrls]);
}
var lastRow = sheet.getLastRow();
var numRows = sheet.getMaxRows();
if((numRows-lastRow) < toWrite.length) {
sheet.insertRowsAfter(lastRow,toWrite.length-numRows+lastRow);
}
var range = sheet.getRange(lastRow+1,1,toWrite.length,toWrite[0].length);
range.setValues(toWrite);
if((sheet.getMaxColumns() - sheet.getLastColumn()) > 0) {
sheet.deleteColumns(sheet.getLastColumn()+1, sheet.getMaxColumns() - sheet.getLastColumn());
}
file = DriveApp.getFileById(spreadsheet.getId());
try {
file.setSharing(DriveApp.Access.ANYONE_WITH_LINK, DriveApp.Permission.VIEW);
} catch(e) {
file.setSharing(DriveApp.Access.DOMAIN_WITH_LINK, DriveApp.Permission.VIEW);
}
//This gives you a link directly to the spreadsheet sheet.
return (spreadsheet.getUrl() + '#gid=' + sheet.getSheetId());
}
//This function builds the summary email and sends it to the people in
//the NOTIFY list
//This function builds the summary email and sends it to the people in
//the NOTIFY list
function sendSummaryEmail(summaryEmailData) {
var subject = SCRIPT_NAME+' Summary Results';
var body = subject;
var htmlBody = '<html><body>'+subject;
htmlBody += '<br/ >Should strip query strings: '+STRIP_QUERY_STRING;
htmlBody += '<br/ >Report on redirects: '+REPORT_ON_REDIRECTS;
htmlBody += '<br/ >Valid response codes: '+VALID_RESPONSE_CODES;
htmlBody += '<br/ ><br/ >';
htmlBody += '<table border="1" width="95%" style="border-collapse:collapse;">';
htmlBody += '<tr>';
htmlBody += '<td align="left"><b>Acct Id</b></td>';
htmlBody += '<td align="left"><b>Acct Name</b></td>';
htmlBody += '<td align="left"><b>Exited Early</b></td>';
htmlBody += '<td align="center"><b>Unique Urls Checked</b></td>';
htmlBody += '<td align="center"><b># Broken Keyword Urls</b></td>';
htmlBody += '<td align="center"><b># Broken Ad Urls</b></td>';
htmlBody += '<td align="center"><b>Full Report</b></td>';
htmlBody += '</tr>';
for(var i in summaryEmailData) {
var row = summaryEmailData[i];
htmlBody += '<tr><td align="left">'+ row.accountId +
'</td><td align="left">' + row.accountName +
'</td><td align="left">' + row.didExitEarly +
'</td><td align="center">' + row.uniqueUrlsChecked +
'</td><td align="center">' + row.numBrokenKeywords +
'</td><td align="center">' + row.numBrokenAds +
'</td><td align="left"><a href="'+row.sheetUrl+'">' + 'Show Details' +
'</a></td></tr>';
}
htmlBody += '</table>';
htmlBody += '<br/ >';
htmlBody += Utilities.formatDate(new Date(),AdWordsApp.currentAccount().getTimeZone(),'MMMM dd, yyyy @ hh:mma z');
htmlBody += '. Completed. '+Object.keys(summaryEmailData).length+' Accounts checked.';
htmlBody += '</body></html>';
var options = { htmlBody : htmlBody };
for(var i in NOTIFY) {
MailApp.sendEmail(NOTIFY[i], subject, body, options);
}
}
}
//This function finds a given file on Google Drive
//If it does not exist, it creates a new file
//if isSpreadsheet is set, it will create a new spreadsheet
//otherwise, it creates a text file.
function getFile(fileName,isSpreadsheet) {
var maxRetries = 0;
var errors = [];
while(maxRetries < 3) {
try {
var fileIter = DriveApp.getFilesByName(fileName);
if(!fileIter.hasNext()) {
info('Could not find file: '+fileName+' on Google Drive. Creating new file.');
if(isSpreadsheet) {
return SpreadsheetApp.create(fileName);
} else {
return DriveApp.createFile(fileName,'');
}
} else {
return fileIter.next();
}
} catch(e) {
errors.push(e);
maxRetries++;
Utilities.sleep(1000);
}
}
if(maxRetries == 3) {
throw errors.join('. ');
}
}
//Some functions to help with logging
var LOG_LEVELS = { 'error':1, 'warn':2, 'info':3, 'debug':4 };
function error(msg) { if(LOG_LEVELS['error'] <= LOG_LEVELS[LOG_LEVEL]) { log('ERROR',msg); } }
function warn(msg) { if(LOG_LEVELS['warn'] <= LOG_LEVELS[LOG_LEVEL]) { log('WARN' ,msg); } }
function info(msg) { if(LOG_LEVELS['info'] <= LOG_LEVELS[LOG_LEVEL]) { log('INFO' ,msg); } }
function debug(msg) { if(LOG_LEVELS['debug'] <= LOG_LEVELS[LOG_LEVEL]) { log('DEBUG',msg); } }
function log(type,msg) { Logger.log(type + ' - ' + msg); }
</pre>
</div>Russell Savagehttp://www.blogger.com/profile/03030632304611412622noreply@blogger.comtag:blogger.com,1999:blog-4377780743561064672.post-14570012334698454112014-02-18T22:12:00.000-08:002014-02-18T22:19:06.200-08:00Beginner's Guide to Javascript You Should Know For AdWords ScriptsI've heard from a few readers that the posts on this blog have inspired them to learn to code. That's awesome! But I've also heard from a few that say they have run into trouble getting through many of the Javascript tutorials out there since they deal mostly with Javascript for web design or Node.js.<br />
<br />
So this post is going to attempt to get someone up to speed with Javascript enough that they can at least walk through most of the code I post here and make changes when needed. Also, I aim to at least help you know what to Google when you get stuck.<br />
<br />
A few caveats. Is this meant to be a replacement for a full coding class? No. Will I be making generalizations and over-simplifying some extremely complex topics? Yes. Are there mistakes in this? Probably. If you find one, let me know.<br />
<br />
<pre class="brush: js">/*********************************
* Intro to Javascript For AdWords Scripts
* Version 1.0
* Created By: Russ Savage
* FreeAdWordsScripts.com
*********************************/
function main() {
// This is a comment. AdWords Scripts ignores this
/* Here is another way to comment
that can be used when you need
to comment multiple lines */
// The main function tells AdWords where to start. You always need
// at least a main function in your script.
// Let's start with some variables (or primatives)
// More info on Javascript variables can be found:
// http://www.tutorialspoint.com/javascript/javascript_variables.htm
var clubName = 'Fight Club'; // declared with single quotes
var rule1 = "Don't talk about fight club."; // or double quotes if needed
var members = 12; // a number, no quotes
var dues = 3.50; // also a number
var isAcceptingNewMembers = true; // a boolean, for yes or no answers
// When you need to store multiple values, consider an Array
// More detailed intro to Arrays can be found here:
// http://www.w3schools.com/js/js_obj_array.asp
var memberNames = ['brad','edward','robert'];
// Which you can access the values with an index
var coolestMember = memberNames[0]; // pronounced member names sub zero
// 0 is the index of the first element of the array, 1 for the second, etc.
// We can use the length property of an array to find out how big it is.
var numberOfMembers = memberNames.length; // this will be 3
var dailyFights = numberOfMembers*2; // star ( * ) is an operator for multiply
// so the total number of fights is 6.
// More on operators can be found here:
// http://web.eecs.umich.edu/~bartlett/jsops.html
// If you want to group multiple variables together, you can using an Object.
// An Object is simply a grouping of common variables (and other stuff we'll see later)
var FightClub = { // The curly brace says group these things together. there is another one at the end.
clubName : 'The Fight Club', // a string variable. In an Object, we use : instead of = for assignment
rules : ["Don't talk about fight club.", // each variable is separated by a comma, instead of a semi-colon
'Do not talk about fight club.'],
memberNames : ['brad','eddy','robert','phil','dave'],
dues : 3.50,
foundedYear : 1999
};
// Now to access the variables inside the object, we use the dot
Logger.log(FightClub.clubName); // prints The Fight Club
Logger.log(FightClub.memberNames[0]); // prints brad
// Objects are one of the most important concepts of Javascript and they will come back
// again and again a little later. More details can be found here:
// https://developer.mozilla.org/en-US/docs/Web/JavaScript/Guide/Working_with_Objects
// Sidebar: Why do I use camelCase for variable names? Technically
// I could
var UsEWhaTevERIwanteD = 'but camelCase is easier to read';
// and conforms to the style guide that Google recommends:
// https://google-styleguide.googlecode.com/svn/trunk/javascriptguide.xml#Naming
// Follow the style guide. It helps others read your code.
// If statements (or control statements) allow you to split your code path if needed
if(numberOfMembers > 10) { // if we have more than 10 members
dues += 1.00; // increase the dues,
// plus equals (+=) says "add the value on the right to the value on the left"
} else { // otherwise
dues -= 1.00; // decrease the dues
// there are also -=, *= (multiply), /= (divide by), and %= (modulo equals)
}
// Comparison operators like >, <, ==, ===, <=, >= allow you to compare values
// They return true or false, always
// Notice the double and triple equal signs. That's not a typo. More info can be found at:
// http://www.impressivewebs.com/why-use-triple-equals-javascipt/
// You can also have multiple if statements and multiple things to test
if(dues > 5) { // if dues are over $5
dailyFights++; // increase the fights
} else if(dues > 2 && dues <= 5) { // if dues are greater than $2, but less than $5
dailyFights--; // decrease the fights
} else { // otherwise
dailyFights = numberOfMembers*2; // reset the fights
}
// You'll probably notice none of this makes sense. it is only for example.
// Double Ampersand && just means AND, || means OR. So in the statement above,
// both statements with operators must be true in order for the fights to be decreased.
// Oh, and ++, -- is shortcut for +=1 and -=1 respectively.
// Ok, now lets talk about loops.
// Here are a few different ways to loop through the members
// This is called a While Loop and while it might be easy to understand,
// You won't use it nearly as often as the other two.
var i = 0; // the variable i is what we will use for each indice
while(i < memberNames.length) { // while i is less than the length of names
Logger.log(memberNames[i]); // print out the name
i++; // and increment the index by 1
}
// i is a variable that controls the loop. A common issue with While loops
// is that you will forget to increment the loop control and you get an infinate loop
// This is the classic For loop
// The declaration, checking, and incrementing are all done
// in the first line so it is harder to miss them
for(var index = 0; index < memberNames.length; index++) {
Logger.log(memberNames[index]);
}
// And finally, the easiest loop but hardest to explain, the ForEach loop
// This is just a variation of the For loop that handles incrementing index
// behind the scenes so you don't have to.
for(var index in memberNames) { // declare index, which will be assigned each indice
Logger.log(memberNames[index]); // Use the indice to print each name
}
// You can jump out of a loop before it reaches the end by combining the if statement
for(var index in memberNames) {
if(memberNames[index] === 'edward') {
break; // break is a keyword you can use to break out of the loop.
}
Logger.log(memberNames[index]);
}
// In this case, only the first name is printed because we broke out once we had the
// second name. More on break and its partner, continue, check out:
// http://www.tutorialspoint.com/javascript/javascript_loop_control.htm
// Now let's talk about functions. We have already seen a function in action: main()
// Functions are groupings of useful code that you can call over and over again easily
function fight(player1, player2) {
if(Math.random() < .5) {
return player1;
} else {
return player2; // return means we are going to send player2 back
// to the code that called the function
}
}
// This code can be called over and over again using a loop
for(var player1 in memberNames) { // Loop through each member
for(var player2 in memberNames) { // Then loop through again
if(player1 !== player2) { // Players can't fight themselves so check for that
Logger.log(fight(player1,player2)); // Then call the function we defined earlier
}
}
}
// This code calls fight() for:
// brad vs. edward, brad vs. robert
// edward vs. brad, edward vs. robert
// robert vs. brad, robert vs. edward
// Some other functions we have been calling are Logger.log() and Math.random()
// The cool thing is that as callers of the function, we only need to know how
// to call the function, we don't need to know how it works behind the scenes
// For example:
// var answer = LargeHadronColider.simulateEleventhDimensionalQuantumThingy(47);
// Who knows how this works. All we need to know is to send it a number and expect a
// number back.
// I hope you've been noticing all of the Objects we have been using here. Logger is one,
// Math is another one (and LargeHadronColider is a fake one). Along with variables, we
// can also put functions in there as well:
var FightClub = {
// ... all that other stuff
chant : function() {
Logger.log('His name is Robert Paulson.');
},
totalMembers : 5
};
// Whoa trippy. So what happens when I call
FightClub.chant();
// It's going to print His name is Robert Paulson
// The thing that makes Google AdWords Scripts different from writing just regular Javascript
// is all of the pre-defined Objects that use functions to interact with AdWords.
AdWordsApp.currentAccount();
Utilities.jsonParse('{}');
AdWordsApp.keywords().withLimit(10).get();
// How does the above statement work?
AdWordsApp // this is a predefined object in AdWords Scripts
.keywords() // which has a function called keywords() that returns a KeywordSelector object
.withLimit(10) // which has a function withLimit() that returns the same KeywordSelector object
.get(); // which has a function get() that returns a KeywordIterator object.
// Check out the AdWords Scripts documentation to find the objects and classes that make up these calls
// https://developers.google.com/adwords/scripts/docs/reference/adwordsapp/adwordsapp
// https://developers.google.com/adwords/scripts/docs/reference/adwordsapp/adwordsapp#keywords_0
// https://developers.google.com/adwords/scripts/docs/reference/adwordsapp/adwordsapp_keywordselector
// https://developers.google.com/adwords/scripts/docs/reference/adwordsapp/adwordsapp_keywordselector#withLimit_1
// https://developers.google.com/adwords/scripts/docs/reference/adwordsapp/adwordsapp_keywordselector#get_0
// So I think that just about does it for this tutorial. If you made it this far, awesome! Post a comment to ask
// any questions you might have.
// Thanks,
// Russ
}
</pre>Russell Savagehttp://www.blogger.com/profile/03030632304611412622noreply@blogger.comtag:blogger.com,1999:blog-4377780743561064672.post-769880835239409722014-01-09T00:23:00.001-08:002014-01-09T00:25:56.334-08:00Make Calls And Send Text Messages To Your Phone From AdWords ScriptsMany of the scripts that I write are about alerting SEM managers when there are issues with an account they manage. It is pretty easy to notify yourself and others through email using the MailApp, but what if you can't access your email for some reason?<br />
<br />
To solve this, I have put together a very simple Javascript object that allows you to send SMS messages and make phone calls using <a href="http://www.twilio.com">Twilio</a>. If you are unfamiliar with Twilio, they are a company that provides developers with an easy way to work telephony into their applications. Their API is extremely easy to use and their documentation is excellent.<br />
<br />
In order to use this script, there are a few things you need to do. First, <a href="https://www.twilio.com/try-twilio">Sign Up for a Twilio Account</a>. Once you do so, you should have a phone number that you can use to play around with as well as an <a href="https://www.twilio.com/user/account">Account SID and an Auth Token</a>. Twilio might not <a href="https://www.twilio.com/help/faq/phone-numbers/which-countries-does-twilio-have-phone-numbers-in-and-what-are-their-capabilities">be available in your country yet</a> but hopefully they will be there soon. Make sure you are using the full phone numbers (including counry codes) when making your requests. If you are just using the free version, there may be usage limits but I'm not sure.<br />
<br />
The code is set up so that it can be copied into any script where you need to send notifications. Once you've copied the Twilio object into your script, whenever you want the notification to be sent, you should add the following code:<br />
<br />
<pre class="brush: js">
...
var sid = 'YOUR ACCOUNT SID GOES HERE';
var auth = 'YOUR AUTH TOKEN GOES HERE';
//First, create a new Twilio client
var client = new Twilio(sid,auth);
//Here is how you send a text message
// First number is the receiver (most likely, your cell phone)
// Second number is where is it coming from, which is the free number you got when
// you registered in Twilio
// The third parameter is what you want the text or voice message to say
client.sendMessage('+17245551234','+14155554321','WARNING: Your AdWords Account Is Not Serving Ads.');
client.makeCall('+17245551234','+14155554321',
'This is an automated call to warn you that your AdWords account is no longer serving ads.');
...
</pre><br />
<br />
Of course, the sid, auth, and client can be global variables which would allow you to have a single line in your code to make phone calls or send messages. You could also set up some sort of escalation chain in case people miss the call or text.<br />
<br />
This is just a simple example of starting to use UrlFetchApp to integrate AdWords scripts with 3rd party apps. If you have 3rd party apps you'd like me to try out, leave a note in the comments.<br /><br />
Thanks,<br />
Russ<br />
<br />
<pre class="brush: js">/*********************************
* Twilio Client Library
* Based on the Twilio REST API: https://www.twilio.com/docs/api/rest
* Version 1.0
* Created By: Russ Savage
* FreeAdWordsScripts.com
*********************************/
function Twilio(accountSid, authToken) {
this.ACCOUNT_SID = accountSid;
this.AUTH_TOKEN = authToken;
this.MESSAGES_ENDPOINT = 'https://api.twilio.com/2010-04-01/Accounts/'+this.ACCOUNT_SID+'/Messages.json';
this.CALLS_ENDPOINT = 'https://api.twilio.com/2010-04-01/Accounts/'+this.ACCOUNT_SID+'/Calls.json';
this.sendMessage = function(to,from,body) {
var httpOptions = {
method : 'POST',
payload : {
To: to,
From: from,
Body: body
},
headers : getBasicAuth(this)
};
var resp = UrlFetchApp.fetch(this.MESSAGES_ENDPOINT, httpOptions).getContentText();
return JSON.parse(resp)['sid'];
}
this.makeCall = function(to,from,whatToSay) {
var url = 'http://proj.rjsavage.com/savageautomation/twilio_script/dynamicSay.php?alert='+encodeURIComponent(whatToSay);
var httpOptions = {
method : 'POST',
payload : {
To: to,
From: from,
Url: url
},
headers : getBasicAuth(this)
};
var resp = UrlFetchApp.fetch(this.CALLS_ENDPOINT, httpOptions).getContentText();
return JSON.parse(resp)['sid'];
}
function getBasicAuth(context) {
return {
'Authorization': 'Basic ' + Utilities.base64Encode(context.ACCOUNT_SID+':'+context.AUTH_TOKEN)
};
}
}
</pre>Russell Savagehttp://www.blogger.com/profile/03030632304611412622noreply@blogger.comtag:blogger.com,1999:blog-4377780743561064672.post-76687583786770764742013-12-07T23:08:00.001-08:002014-09-29T09:46:39.762-07:00Automated Creative Testing With Statistical SignificanceIn the spirit of the Holidays, I've got a little gift for everyone. This script will monitor your AdGroups for creative tests that have hit statistical significance and notify you with an email so that you can take action. The script keeps track of changes to any AdGroups so that it always knows when a new test has started without you having to keep track. It applies labels to your Ads and then notifies you via email when the tests have completed.<br /><center><div class="separator" style="text-align: center;display:inline;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiD6yr_yYqw7LZ0wrTKA9qLBpuc33YlUpfq742TQo-TKWXyEopn5epG4SwthT3RlBAduGcv_3IPACABrbNlKPt7kPDXDPpR_cTSlyIT5L_wpM_l_-x3FORUwxRcej_LJXYYZbVD6pEp6nRB/s1600/Screen+Shot+2013-12-07+at+4.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiD6yr_yYqw7LZ0wrTKA9qLBpuc33YlUpfq742TQo-TKWXyEopn5epG4SwthT3RlBAduGcv_3IPACABrbNlKPt7kPDXDPpR_cTSlyIT5L_wpM_l_-x3FORUwxRcej_LJXYYZbVD6pEp6nRB/s320/Screen+Shot+2013-12-07+at+4.png" /></a><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgOTfdCh588kwJuGGB6FMU55AsnVYPERdZzwO_JEvXiD458tmHykASAZ2o1g3s93Hf2YB_GGOTrwEyL0uHrIAl61Bw1z1qlO9YDuCFNB-ATs_qRE5M1AEf75YVYqYw9Bt6r0Af9VBRrEdGZ/s1600/Screen+Shot+2013-12-07+Labels.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgOTfdCh588kwJuGGB6FMU55AsnVYPERdZzwO_JEvXiD458tmHykASAZ2o1g3s93Hf2YB_GGOTrwEyL0uHrIAl61Bw1z1qlO9YDuCFNB-ATs_qRE5M1AEf75YVYqYw9Bt6r0Af9VBRrEdGZ/s320/Screen+Shot+2013-12-07+Labels.png" /></a></div></center>
<br />
This script is based on my previous <a href="http://www.freeadwordsscripts.com/2013/06/ad-creative-test-automation-script.html">Ad Testing Script</a> but has some additional features. Namely, instead of pausing stats based purely on a metric, this script calculates the statistical significance of the test before making a decision. The calculations are based on a <a href="http://visualwebsiteoptimizer.com/split-testing-blog/ab-testing-significance-calculator-spreadsheet-in-excel/">blog post and spreadsheet from VisualWebsiteOptimizer</a> that I modified to work with AdWords Ads.<br />
<br />
I also incorporated the ability to link directly to the Campaign or AdGroup from the notification email which I <a href="http://www.freeadwordsscripts.com/2013/11/building-entity-deep-links-with-adwords.html">posted about before</a>. All you need to do is fill in the __c and __u parameters to enable that feature.<br />
<br />
And finally, since this is probably the largest and most complicated script I've shared here, I used the logic from my post about Google Analytics reporting to fire a beacon whenever the script is ran. You are more than welcome to disable it (remove beacon(); from the second to the last line of the main function), but I would love to get an idea of just how many people are using this script.<br />
<br />
Since this script is pretty big, and the formatting here can be a little iffy sometimes, you can also <a href="https://gist.github.com/russorat/7862488/raw/405656b2f120af88caa01266b6ce8e9fd6557151/AdWords_Scripts_Ad_Testing.js">download it from GitHub</a>.<br />
<br />
Thanks as always for reading and have a fun and safe holiday.
<br />
<br />
Thanks,<br />
Russ<br />
<br />
<pre class="brush: js">/*********************************************
* Automated Creative Testing With Statistical Significance
* Version 2.1
* Changelog v2.1
* - Fixed INVALID_PREDICATE_ENUM_VALUE
* Changelog v2.0
* - Fixed bug in setting the correct date
* - Script now uses a minimum visitors threshold
* per Ad instead of AdGroup
* - Added the ability to add the start date as a label to AdGroups
* - Added ability to check mobile and desktop ads separately
* Changelog v1.1.1 - Fixed bug with getDisplayUrl
* Changelog v1.1
* - Added ability to only run on some campaigns
* - Fixed bug in info logging
* Russ Savage
* FreeAdWordsScripts.com
**********************************************/
var EXTRA_LOGS = true;
var TO = ['user@email.com'];
var CONFIDENCE_LEVEL = 95; // 90%, 95%, or 99% are most common
//If you only want to run on some campaigns, apply a label to them
//and put the name of the label here. Leave blank to run on all campaigns.
var CAMPAIGN_LABEL = '';
//These two metrics are the components that make up the metric you
//want to compare. For example, this measures CTR = Clicks/Impressions
//Other examples might be:
// Cost Per Conv = Cost/Conversions
// Conversion Rate = Conversions/Clicks
// Cost Per Click = Cost/Clicks
var VISITORS_METRIC = 'Impressions';
var CONVERSIONS_METRIC = 'Clicks';
//This is the number of impressions the Ad needs to have in order
//to start measuring the results of a test.
var VISITORS_THRESHOLD = 100;
//Setting this to true to enable the script to check mobile ads
//against other mobile ads only. Enabling this will start new tests
//in all your AdGroups so only enable this after you have completed
//a testing cycle.
var ENABLE_MOBILE_AD_TESTING = false;
//Set this on the first run which should be the approximate last time
//you started a new creative test. After the first run, this setting
//will be ignored.
var OVERRIDE_LAST_TOUCHED_DATE = 'Jan 1, 2014';
var LOSER_LABEL = 'Loser '+CONFIDENCE_LEVEL+'% Confidence';
var CHAMPION_LABEL = 'Current Champion';
// Set this to true and the script will apply a label to
// each AdGroup to let you know the date the test started
// This helps you validate the results of the script.
var APPLY_TEST_START_DATE_LABELS = true;
//These come from the url when you are logged into AdWords
//Set these if you want your emails to link directly to the AdGroup
var __c = '';
var __u = '';
function main() {
createLabelIfNeeded(LOSER_LABEL,"#FF00FF"); //Set the colors of the labels here
createLabelIfNeeded(CHAMPION_LABEL,"#0000FF"); //Set the colors of the labels here
//Let's find all the AdGroups that have new tests starting
var currentAdMap = getCurrentAdsSnapshot();
var previousAdMap = getPreviousAdsSnapshot();
if(previousAdMap) {
currentAdMap = updateCurrentAdMap(currentAdMap,previousAdMap);
}
storeAdsSnapshot(currentAdMap);
previousAdMap = null;
//Now run through the AdGroups to find tests
var agSelector = AdWordsApp.adGroups()
.withCondition('CampaignStatus = ENABLED')
.withCondition('AdGroupStatus = ENABLED')
.withCondition('Status = ENABLED');
if(CAMPAIGN_LABEL !== '') {
var campNames = getCampaignNames();
agSelector = agSelector.withCondition("CampaignName IN ['"+campNames.join("','")+"']");
}
var agIter = agSelector.get();
var todayDate = getDateString(new Date(),'yyyyMMdd');
var touchedAdGroups = [];
var finishedEarly = false;
while(agIter.hasNext()) {
var ag = agIter.next();
var numLoops = (ENABLE_MOBILE_AD_TESTING) ? 2 : 1;
for(var loopNum = 0; loopNum < numLoops; loopNum++) {
var isMobile = (loopNum == 1);
var rowKey;
if(isMobile) {
info('Checking Mobile Ads in AdGroup: "'+ag.getName()+'"');
rowKey = [ag.getCampaign().getId(),ag.getId(),'Mobile'].join('-');
} else {
info('Checking Ads in AdGroup: "'+ag.getName()+'"');
rowKey = [ag.getCampaign().getId(),ag.getId()].join('-');
}
if(!currentAdMap[rowKey]) { //This shouldn't happen
warn('Could not find AdGroup: '+ag.getName()+' in current ad map.');
continue;
}
if(APPLY_TEST_START_DATE_LABELS) {
var dateLabel;
if(isMobile) {
dateLabel = 'Mobile Tests Started: '+getDateString(currentAdMap[rowKey].lastTouched,'yyyy-MM-dd');
} else {
dateLabel = 'Tests Started: '+getDateString(currentAdMap[rowKey].lastTouched,'yyyy-MM-dd');
}
createLabelIfNeeded(dateLabel,"#8A2BE2");
//remove old start date
var labelIter = ag.labels().withCondition("Name STARTS_WITH '"+dateLabel.split(':')[0]+"'")
.withCondition("Name != '"+dateLabel+"'").get();
while(labelIter.hasNext()) {
var label = labelIter.next();
ag.removeLabel(label.getName());
if(!label.adGroups().get().hasNext()) {
//if there are no more entities with that label, delete it.
label.remove();
}
}
applyLabel(ag,dateLabel);
}
//Here is the date range for the test metrics
var lastTouchedDate = getDateString(currentAdMap[rowKey].lastTouched,'yyyyMMdd');
info('Last Touched Date: '+lastTouchedDate+' Todays Date: '+ todayDate);
if(lastTouchedDate === todayDate) {
//Special case where the AdGroup was updated today which means a new test has started.
//Remove the old labels, but keep the champion as the control for the next test
info('New test is starting in AdGroup: '+ag.getName());
removeLoserLabelsFromAds(ag,isMobile);
continue;
}
//Is there a previous winner? if so we should use it as the control.
var controlAd = checkForPreviousWinner(ag,isMobile);
//Here we order by the Visitors metric and use that as a control if we don't have one
var adSelector = ag.ads().withCondition('Status = ENABLED').withCondition('AdType = TEXT_AD');
if(!AdWordsApp.getExecutionInfo().isPreview()) {
adSelector = adSelector.withCondition("LabelNames CONTAINS_NONE ['"+[LOSER_LABEL,CHAMPION_LABEL].join("','")+"']");
}
var adIter = adSelector.forDateRange(lastTouchedDate, todayDate)
.orderBy(VISITORS_METRIC+" DESC")
.get();
if( (controlAd == null && adIter.totalNumEntities() < 2) ||
(controlAd != null && adIter.totalNumEntities() < 1) )
{
info('AdGroup did not have enough eligible Ads. Had: '+adIter.totalNumEntities()+', Needed at least 2');
continue;
}
if(!controlAd) {
info('No control set for AdGroup. Setting one.');
while(adIter.hasNext()) {
var ad = adIter.next();
if(shouldSkip(isMobile,ad)) { continue; }
controlAd = ad;
break;
}
if(!controlAd) {
continue;
}
applyLabel(controlAd,CHAMPION_LABEL);
}
while(adIter.hasNext()) {
var testAd = adIter.next();
if(shouldSkip(isMobile,testAd)) { continue; }
//The Test object does all the heavy lifting for us.
var test = new Test(controlAd,testAd,
CONFIDENCE_LEVEL,
lastTouchedDate,todayDate,
VISITORS_METRIC,CONVERSIONS_METRIC);
info('Control - Visitors: '+test.getControlVisitors()+' Conversions: '+test.getControlConversions());
info('Test - Visitors: '+test.getTestVisitors()+' Conversions: '+test.getTestConversions());
info('P-Value: '+test.getPValue());
if(test.getControlVisitors() < VISITORS_THRESHOLD ||
test.getTestVisitors() < VISITORS_THRESHOLD)
{
info('Not enough visitors in the control or test ad. Skipping.');
continue;
}
//Check for significance
if(test.isSignificant()) {
var loser = test.getLoser();
removeLabel(loser,CHAMPION_LABEL); //Champion has been dethroned
applyLabel(loser,LOSER_LABEL);
//The winner is the new control. Could be the same as the old one.
controlAd = test.getWinner();
applyLabel(controlAd,CHAMPION_LABEL);
//We store some metrics for a nice email later
if(!ag['touchCount']) {
ag['touchCount'] = 0;
touchedAdGroups.push(ag);
}
ag['touchCount']++;
}
}
//Let's bail if we run out of time so we can send the emails.
if((!AdWordsApp.getExecutionInfo().isPreview() && AdWordsApp.getExecutionInfo().getRemainingTime() < 60) ||
( AdWordsApp.getExecutionInfo().isPreview() && AdWordsApp.getExecutionInfo().getRemainingTime() < 10) )
{
finishedEarly = true;
break;
}
}
}
if(touchedAdGroups.length > 0) {
sendMailForTouchedAdGroups(touchedAdGroups,finishedEarly);
}
beacon();
}
// A helper function to return the list of campaign ids with a label for filtering
function getCampaignNames() {
var campNames = [];
var labelIter = AdWordsApp.labels().withCondition("Name = '"+CAMPAIGN_LABEL+"'").get();
if(labelIter.hasNext()) {
var label = labelIter.next();
var campIter = label.campaigns().get();
while(campIter.hasNext()) {
campNames.push(campIter.next().getName());
}
}
return campNames;
}
function applyLabel(entity,label) {
if(!AdWordsApp.getExecutionInfo().isPreview()) {
entity.applyLabel(label);
} else {
var adText = (entity.getEntityType() === 'Ad') ? [entity.getHeadline(),entity.getDescription1(),
entity.getDescription2(),entity.getDisplayUrl()].join(' ')
: entity.getName();
Logger.log('PREVIEW: Would have applied label: '+label+' to Entity: '+ adText);
}
}
function removeLabel(ad,label) {
if(!AdWordsApp.getExecutionInfo().isPreview()) {
ad.removeLabel(label);
} else {
var adText = [ad.getHeadline(),ad.getDescription1(),ad.getDescription2(),ad.getDisplayUrl()].join(' ');
Logger.log('PREVIEW: Would have removed label: '+label+' from Ad: '+ adText);
}
}
// This function checks if the AdGroup has an Ad with a Champion Label
// If so, the new test should use that as the control.
function checkForPreviousWinner(ag,isMobile) {
var adSelector = ag.ads().withCondition('Status = ENABLED')
.withCondition('AdType = TEXT_AD');
if(!AdWordsApp.getExecutionInfo().isPreview()) {
adSelector = adSelector.withCondition("LabelNames CONTAINS_ANY ['"+CHAMPION_LABEL+"']");
}
var adIter = adSelector.get();
while(adIter.hasNext()) {
var ad = adIter.next();
if(shouldSkip(isMobile,ad)) { continue; }
info('Found a previous winner. Using it as the control.');
return ad;
}
return null;
}
function shouldSkip(isMobile,ad) {
if(isMobile) {
if(!ad.isMobilePreferred()) {
return true;
}
} else {
if(ad.isMobilePreferred()) {
return true;
}
}
return false;
}
// This function sends the email to the people in the TO array.
// If the script finishes early, it adds a notice to the email.
function sendMailForTouchedAdGroups(ags,finishedEarly) {
var htmlBody = '<html><head></head><body>';
if(finishedEarly) {
htmlBody += 'The script was not able to check all AdGroups. ' +
'It will check additional AdGroups on the next run.<br / >' ;
}
htmlBody += 'The following AdGroups have one or more creative tests that have finished.' ;
htmlBody += buildHtmlTable(ags);
htmlBody += '<p><small>Generated by <a href="http://www.freeadwordsscripts.com">FreeAdWordsScripts.com</a></small></p>' ;
htmlBody += '</body></html>';
var options = {
htmlBody : htmlBody,
};
var subject = ags.length + ' Creative Test(s) Completed - ' +
Utilities.formatDate(new Date(), AdWordsApp.currentAccount().getTimeZone(), 'yyyy-MM-dd');
for(var i in TO) {
MailApp.sendEmail(TO[i], subject, ags.length+' AdGroup(s) have creative tests that have finished.', options);
}
}
// This function uses my HTMLTable object to build the styled html table for the email.
function buildHtmlTable(ags) {
var table = new HTMLTable();
//CSS from: http://coding.smashingmagazine.com/2008/08/13/top-10-css-table-designs/
//Inlined using: http://inlinestyler.torchboxapps.com/
table.setTableStyle(['font-family: "Lucida Sans Unicode","Lucida Grande",Sans-Serif;',
'font-size: 12px;',
'background: #fff;',
'margin: 45px;',
'width: 480px;',
'border-collapse: collapse;',
'text-align: left'].join(''));
table.setHeaderStyle(['font-size: 14px;',
'font-weight: normal;',
'color: #039;',
'padding: 10px 8px;',
'border-bottom: 2px solid #6678b1'].join(''));
table.setCellStyle(['border-bottom: 1px solid #ccc;',
'padding: 4px 6px'].join(''));
table.addHeaderColumn('#');
table.addHeaderColumn('Campaign Name');
table.addHeaderColumn('AdGroup Name');
table.addHeaderColumn('Tests Completed');
for(var i in ags) {
table.newRow();
table.addCell(table.getRowCount());
var campName = ags[i].getCampaign().getName();
var name = ags[i].getName();
var touchCount = ags[i]['touchCount'];
var campLink, agLink;
if(__c !== '' && __u !== '') { // You should really set these.
campLink = getUrl(ags[i].getCampaign(),'Ad groups');
agLink = getUrl(ags[i],'Ads');
table.addCell(a(campLink,campName));
table.addCell(a(agLink,name));
} else {
table.addCell(campName);
table.addCell(name);
}
table.addCell(touchCount,'text-align: right');
}
return table.toString();
}
// Just a helper to build the html for a link.
function a(link,val) {
return '<a href="'+link+'">'+val+'</a>';
}
// This function finds all the previous losers and removes their label.
// It is used when the script detects a change in the AdGroup and needs to
// start a new test.
function removeLoserLabelsFromAds(ag,isMobile) {
var adSelector = ag.ads().withCondition('Status = ENABLED');
if(!AdWordsApp.getExecutionInfo().isPreview()) {
adSelector = adSelector.withCondition("LabelNames CONTAINS_ANY ['"+LOSER_LABEL+"']");
}
var adIter = adSelector.get();
while(adIter.hasNext()) {
var ad = adIter.next();
if(shouldSkip(isMobile,ad)) { continue; }
removeLabel(ad,LOSER_LABEL);
}
}
// A helper function to create a new label if it doesn't exist in the account.
function createLabelIfNeeded(name,color) {
if(!AdWordsApp.labels().withCondition("Name = '"+name+"'").get().hasNext()) {
info('Creating label: "'+name+'"');
AdWordsApp.createLabel(name,"",color);
} else {
info('Label: "'+name+'" already exists.');
}
}
// This function compares the previous and current Ad maps and
// updates the current map with the date that the AdGroup was last touched.
// If OVERRIDE_LAST_TOUCHED_DATE is set and there is no previous data for the
// AdGroup, it uses that as the last touched date.
function updateCurrentAdMap(current,previous) {
info('Updating the current Ads map using historical snapshot.');
for(var rowKey in current) {
var currentAds = current[rowKey].adIds;
var previousAds = (previous[rowKey]) ? previous[rowKey].adIds : [];
if(currentAds.join('-') === previousAds.join('-')) {
current[rowKey].lastTouched = previous[rowKey].lastTouched;
}
if(previousAds.length === 0 && OVERRIDE_LAST_TOUCHED_DATE !== '') {
current[rowKey].lastTouched = new Date(OVERRIDE_LAST_TOUCHED_DATE);
}
//if we make it here without going into the above if statements
//then the adgroup has changed and we should keep the new date
}
info('Finished updating the current Ad map.');
return current;
}
// This stores the Ad map snapshot to a file so it can be used for the next run.
// The data is stored as a JSON string for easy reading later.
function storeAdsSnapshot(data) {
info('Storing the Ads snapshot to Google Drive.');
var fileName = getSnapshotFilename();
var file = DriveApp.getFilesByName(fileName).next();
file.setContent(Utilities.jsonStringify(data));
info('Finished.');
}
// This reads the JSON formatted previous snapshot from a file on GDrive
// If the file doesn't exist, it creates a new one and returns an empty map.
function getPreviousAdsSnapshot() {
info('Loading the previous Ads snapshot from Google Drive.');
var fileName = getSnapshotFilename();
var fileIter = DriveApp.getFilesByName(fileName);
if(fileIter.hasNext()) {
return Utilities.jsonParse(fileIter.next().getBlob().getDataAsString());
} else {
DriveApp.createFile(fileName, '');
return {};
}
}
// A helper function to build the filename for the snapshot.
function getSnapshotFilename() {
var accountId = AdWordsApp.currentAccount().getCustomerId();
return (accountId + ' Ad Testing Script Snapshot.json');
}
// This function pulls the Ad Performance Report which is the fastest
// way to build a snapshot of the current ads in the account.
// This only pulls in active text ads.
function getCurrentAdsSnapshot() {
info('Running Ad Performance Report to get current Ads snapshot.');
var OPTIONS = { includeZeroImpressions : true };
var cols = ['CampaignId','AdGroupId','Id','DevicePreference','Impressions'];
var report = 'AD_PERFORMANCE_REPORT';
var query = ['select',cols.join(','),'from',report,
'where AdType = TEXT_AD',
'and AdNetworkType1 = SEARCH',
'and CampaignStatus = ENABLED',
'and AdGroupStatus = ENABLED',
'and Status = ENABLED',
'during','TODAY'].join(' ');
var results = {}; // { campId-agId : row, ... }
var reportIter = AdWordsApp.report(query, OPTIONS).rows();
while(reportIter.hasNext()) {
var row = reportIter.next();
var rowKey = [row.CampaignId,row.AdGroupId].join('-');
if(ENABLE_MOBILE_AD_TESTING && row.DevicePreference == 30001) {
rowKey += '-Mobile';
}
if(!results[rowKey]) {
results[rowKey] = { adIds : [], lastTouched : new Date() };
}
results[rowKey].adIds.push(row.Id);
}
for(var i in results) {
results[i].adIds.sort();
}
info('Finished building the current Ad map.');
return results;
}
//Helper function to format the date
function getDateString(date,format) {
return Utilities.formatDate(new Date(date),AdWordsApp.currentAccount().getTimeZone(),format);
}
// Function to build out the urls for deeplinking into the AdWords account.
// For this to work, you need to have __c and __u filled in.
// Taken from: http://www.freeadwordsscripts.com/2013/11/building-entity-deep-links-with-adwords.html
function getUrl(entity,tab) {
var customerId = __c;
var effectiveUserId = __u;
var decodedTab = getTab(tab);
var base = 'https://adwords.google.com/cm/CampaignMgmt?';
var url = base+'__c='+customerId+'&__u='+effectiveUserId+'#';
if(typeof entity['getEntityType'] === 'undefined') {
return url+'r.ONLINE.di&app=cm';
}
var type = entity.getEntityType()
if(type === 'Campaign') {
return url+'c.'+entity.getId()+'.'+decodedTab+'&app=cm';
}
if(type === 'AdGroup') {
return url+'a.'+entity.getId()+'_'+entity.getCampaign().getId()+'.'+decodedTab+'&app=cm';
}
if(type === 'Keyword') {
return url+'a.'+entity.getAdGroup().getId()+'_'+entity.getCampaign().getId()+'.key&app=cm';
}
if(type === 'Ad') {
return url+'a.'+entity.getAdGroup().getId()+'_'+entity.getCampaign().getId()+'.create&app=cm';
}
return url+'r.ONLINE.di&app=cm';
function getTab(tab) {
var mapping = {
'Ad groups':'ag','Settings:All settings':'st_sum',
'Settings:Locations':'st_loc','Settings:Ad schedule':'st_as',
'Settings:Devices':'st_p','Ads':'create',
'Keywords':'key','Audiences':'au','Ad extensions':'ae',
'Auto targets':'at','Dimensions' : 'di'
};
if(mapping[tab]) { return mapping[tab]; }
return 'key'; //default to keyword tab
}
}
// Helper function to print info logs
function info(msg) {
if(EXTRA_LOGS) {
Logger.log('INFO: '+msg);
}
}
// Helper function to print more serious warnings
function warn(msg) {
Logger.log('WARNING: '+msg);
}
/********************************
* Track Script Runs in Google Analytics
* Created By: Russ Savage
* FreeAdWordsScripts.com
********************************/
function beacon() {
var TAG_ID = 'UA-40187672-2';
var CAMPAIGN_SOURCE = 'adwords';
var CAMPAIGN_MEDIUM = 'scripts';
var CAMPAIGN_NAME = 'AdTestingScriptV2_1';
var HOSTNAME = 'www.freeadwordsscripts.com';
var PAGE = '/Ad_Testing_Script_v2_1';
if(AdWordsApp.getExecutionInfo().isPreview()) {
PAGE += '/preview';
}
var DOMAIN_LINK = 'http://'+HOSTNAME+PAGE;
//Pulled from: http://stackoverflow.com/questions/105034/how-to-create-a-guid-uuid-in-javascript
var uuid = 'xxxxxxxx-xxxx-4xxx-yxxx-xxxxxxxxxxxx'.replace(/[xy]/g,
function(c) {var r = Math.random()*16|0,v=c=='x'?r:r&0x3|0x8;return v.toString(16);});
var url = 'http://www.google-analytics.com/collect?';
var payload = {
'v':1,'tid':TAG_ID,'cid':uuid,
't':'pageview','cs':CAMPAIGN_SOURCE,'cm':CAMPAIGN_MEDIUM,'cn':CAMPAIGN_NAME,
'dl':DOMAIN_LINK
};
var qs = '';
for(var key in payload) {
qs += key + '=' + encodeURIComponent(payload[key]) + '&';
}
url += qs.substring(0,qs.length-1);
UrlFetchApp.fetch(url);
}
/*********************************************
* Test: A class for runnning A/B Tests for Ads
* Version 1.0
* Based on VisualWebsiteOptimizer logic: http://goo.gl/jiImn
* Russ Savage
* FreeAdWordsScripts.com
**********************************************/
// A description of the parameters:
// control - the control Ad, test - the test Ad
// startDate, endDate - the start and end dates for the test
// visitorMetric, conversionMetric - the components of the metric to use for the test
function Test(control,test,desiredConf,startDate,endDate,visitorMetric,conversionMetric) {
this.desiredConfidence = desiredConf/100;
this.verMetric = visitorMetric;
this.conMetric = conversionMetric;
this.startDate = startDate;
this.endDate = endDate;
this.winner;
this.controlAd = control;
this.controlStats = (this.controlAd['stats']) ? this.controlAd['stats'] : this.controlAd.getStatsFor(this.startDate, this.endDate);
this.controlAd['stats'] = this.controlStats;
this.controlVisitors = this.controlStats['get'+this.verMetric]();
this.controlConversions = this.controlStats['get'+this.conMetric]();
this.controlCR = getConversionRate(this.controlVisitors,this.controlConversions);
this.testAd = test;
this.testStats = (this.testAd['stats']) ? this.testAd['stats'] : this.testAd.getStatsFor(this.startDate, this.endDate);
this.testAd['stats'] = this.testStats;
this.testVisitors = this.testStats['get'+this.verMetric]();
this.testConversions = this.testStats['get'+this.conMetric]();
this.testCR = getConversionRate(this.testVisitors,this.testConversions);
this.pValue;
this.getControlVisitors = function() { return this.controlVisitors; }
this.getControlConversions = function() { return this.controlConversions; }
this.getTestVisitors = function() { return this.testVisitors; }
this.getTestConversions = function() { return this.testConversions; }
// Returns the P-Value for the two Ads
this.getPValue = function() {
if(!this.pValue) {
this.pValue = calculatePValue(this);
}
return this.pValue;
};
// Determines if the test has hit significance
this.isSignificant = function() {
var pValue = this.getPValue();
if(pValue && pValue !== 'N/A' && (pValue >= this.desiredConfidence || pValue <= (1 - this.desiredConfidence))) {
return true;
}
return false;
}
// Returns the winning Ad
this.getWinner = function() {
if(this.decideWinner() === 'control') {
return this.controlAd;
}
if(this.decideWinner() === 'challenger') {
return this.testAd;
}
return null;
};
// Returns the losing Ad
this.getLoser = function() {
if(this.decideWinner() === 'control') {
return this.testAd;
}
if(this.decideWinner() === 'challenger') {
return this.controlAd;
}
return null;
};
// Determines if the control or the challenger won
this.decideWinner = function () {
if(this.winner) {
return this.winner;
}
if(this.isSignificant()) {
if(this.controlCR >= this.testCR) {
this.winner = 'control';
} else {
this.winner = 'challenger';
}
} else {
this.winner = 'no winner';
}
return this.winner;
}
// This function returns the confidence level for the test
function calculatePValue(instance) {
var control = {
visitors: instance.controlVisitors,
conversions: instance.controlConversions,
cr: instance.controlCR
};
var challenger = {
visitors: instance.testVisitors,
conversions: instance.testConversions,
cr: instance.testCR
};
var z = getZScore(control,challenger);
if(z == -1) { return 'N/A'; }
var norm = normSDist(z);
return norm;
}
// A helper function to make rounding a little easier
function round(value) {
var decimals = Math.pow(10,5);
return Math.round(value*decimals)/decimals;
}
// Return the conversion rate for the test
function getConversionRate(visitors,conversions) {
if(visitors == 0) {
return -1;
}
return conversions/visitors;
}
function getStandardError(cr,visitors) {
if(visitors == 0) {
throw 'Visitors cannot be 0.';
}
return Math.sqrt((cr*(1-cr)/visitors));
}
function getZScore(c,t) {
try {
if(!c['se']) { c['se'] = getStandardError(c.cr,c.visitors); }
if(!t['se']) { t['se'] = getStandardError(t.cr,t.visitors); }
} catch(e) {
Logger.log(e);
return -1;
}
if((Math.sqrt(Math.pow(c.se,2)+Math.pow(t.se,2))) == 0) {
Logger.log('WARNING: Somehow the denominator in the Z-Score calulator was 0.');
return -1;
}
return ((c.cr-t.cr)/Math.sqrt(Math.pow(c.se,2)+Math.pow(t.se,2)));
}
//From: http://www.codeproject.com/Articles/408214/Excel-Function-NORMSDIST-z
function normSDist(z) {
var sign = 1.0;
if (z < 0) { sign = -1; }
return round(0.5 * (1.0 + sign * erf(Math.abs(z)/Math.sqrt(2))));
}
// From: http://picomath.org/javascript/erf.js.html
function erf(x) {
// constants
var a1 = 0.254829592;
var a2 = -0.284496736;
var a3 = 1.421413741;
var a4 = -1.453152027;
var a5 = 1.061405429;
var p = 0.3275911;
// Save the sign of x
var sign = 1;
if (x < 0) {
sign = -1;
}
x = Math.abs(x);
// A&S formula 7.1.26
var t = 1.0/(1.0 + p*x);
var y = 1.0 - (((((a5*t + a4)*t) + a3)*t + a2)*t + a1)*t*Math.exp(-x*x);
return sign*y;
}
}
/*********************************************
* HTMLTable: A class for building HTML Tables
* Version 1.0
* Russ Savage
* FreeAdWordsScripts.com
**********************************************/
function HTMLTable() {
this.headers = [];
this.columnStyle = {};
this.body = [];
this.currentRow = 0;
this.tableStyle;
this.headerStyle;
this.cellStyle;
this.addHeaderColumn = function(text) {
this.headers.push(text);
};
this.addCell = function(text,style) {
if(!this.body[this.currentRow]) {
this.body[this.currentRow] = [];
}
this.body[this.currentRow].push({ val:text, style:(style) ? style : '' });
};
this.newRow = function() {
if(this.body != []) {
this.currentRow++;
}
};
this.getRowCount = function() {
return this.currentRow;
};
this.setTableStyle = function(css) {
this.tableStyle = css;
};
this.setHeaderStyle = function(css) {
this.headerStyle = css;
};
this.setCellStyle = function(css) {
this.cellStyle = css;
if(css[css.length-1] !== ';') {
this.cellStyle += ';';
}
};
this.toString = function() {
var retVal = '<table ';
if(this.tableStyle) {
retVal += 'style="'+this.tableStyle+'"';
}
retVal += '>'+_getTableHead(this)+_getTableBody(this)+'</table>';
return retVal;
};
function _getTableHead(instance) {
var headerRow = '';
for(var i in instance.headers) {
headerRow += _th(instance,instance.headers[i]);
}
return '<thead><tr>'+headerRow+'</tr></thead>';
};
function _getTableBody(instance) {
var retVal = '<tbody>';
for(var r in instance.body) {
var rowHtml = '<tr>';
for(var c in instance.body[r]) {
rowHtml += _td(instance,instance.body[r][c]);
}
rowHtml += '</tr>';
retVal += rowHtml;
}
retVal += '</tbody>';
return retVal;
};
function _th(instance,val) {
var retVal = '<th scope="col" ';
if(instance.headerStyle) {
retVal += 'style="'+instance.headerStyle+'"';
}
retVal += '>'+val+'</th>';
return retVal;
};
function _td(instance,cell) {
var retVal = '<td ';
if(instance.cellStyle || cell.style) {
retVal += 'style="';
if(instance.cellStyle) {
retVal += instance.cellStyle;
}
if(cell.style) {
retVal += cell.style;
}
retVal += '"';
}
retVal += '>'+cell.val+'</td>';
return retVal;
};
}
</pre>Russell Savagehttp://www.blogger.com/profile/03030632304611412622noreply@blogger.comtag:blogger.com,1999:blog-4377780743561064672.post-73464605794133667502013-12-03T21:22:00.004-08:002013-12-03T21:22:56.045-08:00Apologies for Missing Some Comments Lately<br />
Hi Readers, Just a quick note that I seem to have been missing some comments lately due to some messed up notification settings in Google+. I think I've fixed it (feel free to comment here to make sure ;). I also went through the last few posts and tried to reply where I could but if I missed any, just give me a nudge and I will do my best to get to them.<br />
<br />
Thanks as always for reading,<br />
RussRussell Savagehttp://www.blogger.com/profile/03030632304611412622noreply@blogger.comtag:blogger.com,1999:blog-4377780743561064672.post-50414849885122018302013-11-30T23:01:00.003-08:002013-12-03T20:36:20.079-08:00AdWords Account Audit Checklist Using AdWords ScriptsI was browsing through some old <a href="http://www.wordstream.com/blog">WordStream blog posts</a> and noticed this one from Phil Kowalski about an <a href="http://www.wordstream.com/blog/ws/2013/07/02/adwords-account-audit-checklist">AdWords Account Audit Checklist</a>. It seemed like most of that work could be automated using AdWords scripts so I figured I'd give it a shot.<br />
<br />
The following script runs through as many of the checks as possible and tries to report areas to check out first. This would be useful for making sure that an account you take over has at least the basics going for it.<br />
<br />
To see the results, run the script in your account, then click on the "View details" link and hit the "Logs" button. You should see all the warnings that the script found. Try it out and let me know what additional checks you might add.
<br />
<br />
Thanks,<br />
Russ<br />
<br />
<pre class="brush: js">/************************************
* AdWords Account Audit Checklist
* Version 1.1
* ChangeLog v1.1 - Fixed issue with extension selector.
* Based on the blog post by Phil Kowalski
* http://www.wordstream.com/blog/ws/2013/07/02/adwords-account-audit-checklist
* Created By: Russ Savage
* FreeAdWordsScripts.com
************************************/
function main() {
//1. Campaigns
// a. Target the right locations
var includedLocList = ['United States','Canada']; // <-- the list of places your campaigns should be targeting
verifyTargetedLocations(includedLocList);
var excludedLocList = ['Europe']; // <-- the list of places your campaigns should be excluding
verifyExcludedLocations(excludedLocList);
// b. Language - Can't be done using scripts yet :(
// c. Search vs Display
verifySearchAndDisplay();
// d. Check Mobile Strategy
verifyMobileModifiers();
//2. AdGroups
// a. Check for AdGroups with more than 20-30 keywords
var ADGROUP_SIZE = 25; // <-- this is the max number of keywords you want in an AdGroup
verifyAdGroupSize(ADGROUP_SIZE);
// b. Check for topic. Difficult to do with scripts
// c. Check for ads
var NUMBER_OF_ADS = 3; // <-- this is the minimum number of ads in an AdGroup
verifyAdGroupNumberOfAds(NUMBER_OF_ADS);
//3. Keywords
// a. Check for MatchTypes
printMatchTypes();
//4. Search Queries
// This analysis is probably worth it's own script
//5. Other
// a. Conversion Tracking
verifyConversionTracking();
// b. AdExtensions
verifyAdExtensions();
}
function verifyConversionTracking() {
//Assume that if the account has not had a conversion in 7 days, something is wrong.
var campsWithConversions = AdWordsApp.campaigns()
.withCondition('Status = ENABLED')
.forDateRange('LAST_7_DAYS')
.withCondition('Conversions > 0')
.get().totalNumEntities();
if(campsWithConversions == 0) {
warn('Account is probably missing conversion tracking.');
}
}
function verifyAdExtensions() {
var campIter = AdWordsApp.campaigns().withCondition('Status = ENABLED').get();
while(campIter.hasNext()) {
var camp = campIter.next();
var phoneNumExtCount = camp.extensions().phoneNumbers().get().totalNumEntities();
if(phoneNumExtCount == 0) {
warn('Campaign: "'+camp.getName()+'" is missing phone number extensions.');
}
var siteLinksExtCount = camp.extensions().sitelinks().get().totalNumEntities();
if(siteLinksExtCount < 6) {
warn('Campaign: "'+camp.getName()+'" could use more site links. Currently has: '+siteLinksExtCount);
}
var mobileAppsExtCount = camp.extensions().mobileApps().get().totalNumEntities();
if(mobileAppsExtCount == 0) {
warn('Campaign: "'+camp.getName()+'" is missing mobile apps extension.');
}
}
}
function printMatchTypes() {
var numBroad = AdWordsApp.keywords()
.withCondition('Status = ENABLED')
.withCondition('AdGroupStatus = ENABLED')
.withCondition('CampaignStatus = ENABLED')
.withCondition('KeywordMatchType = BROAD')
.get().totalNumEntities();
var numPhrase = AdWordsApp.keywords()
.withCondition('Status = ENABLED')
.withCondition('AdGroupStatus = ENABLED')
.withCondition('CampaignStatus = ENABLED')
.withCondition('KeywordMatchType = PHRASE')
.get().totalNumEntities();
var numExact = AdWordsApp.keywords()
.withCondition('Status = ENABLED')
.withCondition('AdGroupStatus = ENABLED')
.withCondition('CampaignStatus = ENABLED')
.withCondition('KeywordMatchType = EXACT')
.get().totalNumEntities();
var total = numBroad+numPhrase+numExact;
var percBroad = Math.round(numBroad/total*100);
var percPhrase = Math.round(numPhrase/total*100);
var percExact = Math.round(numExact/total*100);
info('Out of a total of: '+total+' active keywords in your account:');
info('\tBroad: '+numBroad+' or '+percBroad+'%');
info('\tPhrase: '+numPhrase+' or '+percPhrase+'%');
info('\tExact: '+numExact+' or '+percExact+'%');
}
function verifyAdGroupNumberOfAds(requiredNumberOfAds) {
var agIter = AdWordsApp.adGroups()
.withCondition('Status = ENABLED')
.withCondition('CampaignStatus = ENABLED')
.get();
while(agIter.hasNext()) {
var ag = agIter.next();
var adCount = ag.ads().withCondition('Status = ENABLED').get().totalNumEntities();
if(adCount < requiredNumberOfAds) {
warn('Campaign: "'+ag.getCampaign().getName()+'" AdGroup: "'+ag.getName()+'" does not have enough ads: '+adCount);
}
if(adCount > (requiredNumberOfAds+2)) {
warn('Campaign: "'+ag.getCampaign().getName()+'" AdGroup: "'+ag.getName()+'" has too many ads: '+adCount);
}
}
}
function verifyAdGroupSize(size) {
var agIter = AdWordsApp.adGroups()
.withCondition('Status = ENABLED')
.withCondition('CampaignStatus = ENABLED')
.get();
while(agIter.hasNext()) {
var ag = agIter.next();
var kwSize = ag.keywords().withCondition('Status = ENABLED').get().totalNumEntities();
if(kwSize >= size) {
warn('Campaign: "'+ag.getCampaign().getName()+'" AdGroup: "'+ag.getName()+'" has too many keywords: '+kwSize);
}
}
}
function verifyMobileModifiers() {
var campIter = AdWordsApp.campaigns().withCondition('Status = ENABLED').get();
while(campIter.hasNext()) {
var camp = campIter.next();
var desktop = camp.targeting().platforms().desktop().get().next();
//var tablet = camp.targeting().platforms().tablet().get().next();
var mobile = camp.targeting().platforms().mobile().get().next();
//check for mobile modifiers
if(desktop.getBidModifier() == 1 && mobile.getBidModifier() == 1) {
warn('Campaign: "'+camp.getName()+'" has no mobile modifier set.');
}
}
}
function verifyTargetedLocations(locList) {
var campIter = AdWordsApp.campaigns().withCondition('Status = ENABLED').get();
while(campIter.hasNext()) {
var camp = campIter.next();
var locIter = camp.targeting().targetedLocations().get();
reportOnLocations(camp,locIter,locList);
}
}
function verifyExcludedLocations(locList) {
var campIter = AdWordsApp.campaigns().withCondition('Status = ENABLED').get();
while(campIter.hasNext()) {
var camp = campIter.next();
var locIter = camp.targeting().excludedLocations().get();
reportOnLocations(camp,locIter,locList);
}
}
function reportOnLocations(camp,locIter,locList) {
var campLocList = [];
while(locIter.hasNext()) {
var loc = locIter.next();
campLocList.push(loc.getName());
if(!locList) {
warn('Campaign: "'+camp.getName()+'" targeting: "'+loc.getName()+'"');
}
}
if(locList && campLocList.sort() != locList.sort()) {
for(var i in campLocList) {
if(locList.indexOf(campLocList[i]) == -1) {
warn('Campaign: "'+camp.getName()+'" incorrectly targeting: "'+campLocList[i]+'"');
}
}
for(var i in locList) {
if(campLocList.indexOf(locList[i]) == -1) {
warn('Campaign: "'+camp.getName()+'" not targeting: "'+locList[i]+'"');
}
}
}
}
function verifySearchAndDisplay() {
var API_VERSION = { includeZeroImpressions : false };
var cols = ['CampaignId','CampaignName','AdNetworkType1','Impressions'];
var report = 'CAMPAIGN_PERFORMANCE_REPORT';
var query = ['select',cols.join(','),'from',report,'during','LAST_30_DAYS'].join(' ');
var results = {}; // { campId : { agId : [ row, ... ], ... }, ... }
var reportIter = AdWordsApp.report(query, API_VERSION).rows();
while(reportIter.hasNext()) {
var row = reportIter.next();
if(results[row.CampaignId]) {
warn('Campaign: "'+row.CampaignName+'" is targeting the Display and Search networks.');
} else {
results[row.CampaignId] = row;
}
}
return results;
}
function warn(msg) {
Logger.log('WARNING: '+msg);
}
function info(msg) {
Logger.log(msg);
}
</pre>Russell Savagehttp://www.blogger.com/profile/03030632304611412622noreply@blogger.comtag:blogger.com,1999:blog-4377780743561064672.post-71226994944542269892013-11-17T17:34:00.001-08:002013-11-17T17:34:26.974-08:00Track Adwords Script Runs with Google AnalyticsThe other day, I was looking into how I could report on how many times my AdWords scripts were running. I figured that since I use Google Analytics reporting for everything else, maybe I should try to use it for reporting on my scripts as well.<br />
<br />
So using some information gathered around the web, I put together this simple script to push data into Google Analytics. This will register each script run as a pageview. If you want to keep track of account ids where the script is running, you will need to add AdWordsApp.currentAccount().getCustomerId() somewhere in the CAMPAIGN or PAGE values. Once you have this code in your script, just make a call to beacon(); at the beginning of your code. And since many tracking systems mimic the Google Analytics tracking format, I'm sure this can be easily adapted to other systems.<br /><br />
Thanks,<br />
Russ<br />
<br />
<pre class="brush: js">/********************************
* Track Script Runs in Google Analytics
* Created By: Russ Savage
* FreeAdWordsScripts.com
********************************/
function beacon() {
var TAG_ID = 'UA-XXXXXXXX-X';
var CAMPAIGN_SOURCE = 'adwords';
var CAMPAIGN_MEDIUM = 'scripts';
var CAMPAIGN_NAME = 'Your Script Name And Version';
var HOSTNAME = 'www.freeadwordsscripts.com';
var PAGE = '/Some/Virtual/Page/Similar/To/Campaign/Name/Probably';
var DOMAIN_LINK = 'http://'+HOSTNAME+PAGE;
//Pulled from: http://stackoverflow.com/questions/105034/how-to-create-a-guid-uuid-in-javascript
var uuid = 'xxxxxxxx-xxxx-4xxx-yxxx-xxxxxxxxxxxx'.replace(/[xy]/g,
function(c) {var r = Math.random()*16|0,v=c=='x'?r:r&0x3|0x8;return v.toString(16);});
var url = 'http://www.google-analytics.com/collect?';
var payload = {
'v':1,'tid':TAG_ID,'cid':uuid,
't':'pageview','cs':CAMPAIGN_SOURCE,'cm':CAMPAIGN_MEDIUM,'cn':CAMPAIGN_NAME,
'dl':DOMAIN_LINK
};
var qs = '';
for(var key in payload) {
qs += key + '=' + encodeURIComponent(payload[key]) + '&';
}
url += qs.substring(0,qs.length-1);
UrlFetchApp.fetch(url);
}
</pre>Russell Savagehttp://www.blogger.com/profile/03030632304611412622noreply@blogger.comtag:blogger.com,1999:blog-4377780743561064672.post-33237562963022416572013-11-11T01:02:00.000-08:002013-11-11T01:10:58.448-08:00Building Entity Deep Links with AdWords ScriptsI was trying to build a report on problems in an account and I was thinking to myself, "Man, it sure would be nice to deep link directly to the entity that was having issues." Then I realized that is the script change logs could do it, I probably could too.<br/>
<br/>
It turns out, there are two magic numbers that you need in order to get this to work. When you login to your account, in the url, you will see __u=<some number> and __c=<some number>. According to this <a href="http://blog-en.openalfa.com/how-to-download-a-report-from-adwords-using-a-perl-script/">blog post</a>, these values are the 'effectiveUserId' and 'customerId' respectively. Unfortunately, there isn't a way to access these values when using scripts, so you will have to manually copy them into the script below.<br />
<br />
After that, you can include the function in all your scripts and deep link to your heart's content. It isn't the prettiest thing in the world but it is self contained so it should be easy to copy into the bottom of your scripts.<br />
<br />
Thanks,<br />
Russ<br/>
<br/>
<pre class="brush: js"> // Link to the Keyword Tab of the AdGroup
Logger.log(getUrl(someAdGroupEntity,'Keywords'));
// Link to the Ads Tab of the AdGroup
Logger.log(getUrl(someAdGroupEntity,'Ads'));
// Link to Location Settings Tab of the Campaign
Logger.log(getUrl(comeCampaignEntity,'Settings:Locations'));
</pre>
<br />
<br />
<pre class="brush: js">/***********************************
* Build Deep Link Urls for Entities
* Version 1.0
* Created By: Russ Savage
* FreeAdWordsScripts.com
***********************************/
function getUrl(entity,tab) {
var customerId = '__c from the url';
var effectiveUserId = '__u from the url';
var decodedTab = getTab(tab);
var base = 'https://adwords.google.com/cm/CampaignMgmt?';
var url = base+'__c='+customerId+'&__u='+effectiveUserId+'#';
if(typeof entity['getBudget'] !== 'undefined') {
//A Campaign
return url+'c.'+entity.getId()+'.'+decodedTab+'&app=cm';
}
if(typeof entity['createKeyword'] !== 'undefined') {
//An AdGroup
return url+'a.'+entity.getId()+'_'+entity.getCampaign().getId()+'.'+decodedTab+'&app=cm';
}
if(typeof entity['getMatchType'] !== 'undefined') {
//A Keyword
return url+'a.'+entity.getAdGroup().getId()+'_'+entity.getCampaign().getId()+'.key&app=cm';
}
if(typeof entity['getHeadline'] !== 'undefined') {
//An Ad
return url+'a.'+entity.getAdGroup().getId()+'_'+entity.getCampaign().getId()+'.create&app=cm';
}
return url+'r.ONLINE.di&app=cm';
function getTab(tab) {
var mapping = {
'Ad groups':'ag','Settings:All settings':'st_sum',
'Settings:Locations':'st_loc','Settings:Ad schedule':'st_as',
'Settings:Devices':'st_p','Ads':'create',
'Keywords':'key','Audiences':'au','Ad extensions':'ae',
'Auto targets':'at','Dimensions' : 'di'
};
if(mapping[tab]) { return mapping[tab]; }
return 'key'; //default to keyword tab
}
}
</pre>Russell Savagehttp://www.blogger.com/profile/03030632304611412622noreply@blogger.com