Thursday, March 22, 2018

Automatically Put Pivoted AdWords Data in a Spreadsheet

I'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.

The Problem With Google's AdWords Reports

Here's what the typical output from an AdWords report looks like if you use a script like this one to add it in a Google sheet.
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.
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'.

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.

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.

A Better Format for AdWords Reports

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:

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.

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.

How Scripts Solved This Problem

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.

So now I can more easily do my optimization. I could also feed this sheet into a tool like Optmyzr's Rule Engine 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.

How to run this script:

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:

  1. attributes: these are the AdWords reporting attributes. You will get a single spreadsheet row per unique combination of attributes
  2. segments: these are the AdWords reporting segments. The values of each segment will be joined with metrics and create 1 column for each combination
  3. metrics: the AdWords reporting metrics to include.
  4. sourceReport: the report type from Google. See the link below for more information
  5. spreadsheetUrl: the Url of the Google spreadsheet that this script will update.
  6. tabName: the name of the sheet (tab) in the spreadsheet that should be updated.
  7. reportVersion: 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.

Where to Find Valid Settings for the Script

Here is the list of acceptable attributes, segments and metrics for the keywords performance report: 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.

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:

Here is the full list of all AdWords reports with the available attributes, segments, and metrics to help you create correct settings.

One thing to watch out for:

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).

Another Popular Script For Putting AdWords Data in Spreadsheets

Last June I wrote a script that makes it super easy to do custom data pulls from AdWords and put the data in a spreadsheet so I was happy to hear people still talking about this script at SMX West in March. You can find it on Search Engine Land if you're looking for more options to get AdWords data in spreadsheets without paying someone for it.


  1. The thought behind this is Google's AI can comprehend an assortment of inquiries on different subjects and give legitimate reactions. artificial intelligence training in pune

  2. I appreciate the effort of the author. I found it is an informative post on bootstrap technology. I hope you shared this kind of blog in future to help both the developer and business people.
    Hire Dedicated Opencart Developers
    Smarty Developers
    Hire Dedicated Developers
    Dedicated Magento Developer
    Hire Cake Php Programmer

  3. International Watch Company, or IWC, was first introduced to the world in 1868 when an American Engineer,replica watches sale Florentine Ariosto Jones, decided to open his own factory in Switzerland. replica IWC watches has earned its reputation as one of the world's finest manufacturers of Swiss luxury watches. IWC only manufactures automatic and manual timepieces which include features such as the chronograph and GMT dual time zone. IWC has definitely made its mark in the world of watches with its dedication to excellence in form and function. Using a range of supplies a large selection of IWC watches at the best sale prices online so buy yours today!

  4. An interesting discussion is worth comment. I think that you need to write more about this topic, it might not be a taboo matter but usually people do not discuss such subjects. To the next! Kind regards!! onsite mobile repair bangalore Hey there! I just want to offer you a big thumbs up for the great information you have got here on this post. I am coming back to your website for more soon. asus display repair bangalore When I originally commented I seem to have clicked the -Notify me when new comments are added- checkbox and from now on each time a comment is added I recieve 4 emails with the exact same comment. Perhaps there is a means you can remove me from that service? Appreciate it! huawei display repair bangalore

  5. Next time I read a blog, I hope that it doesn't disappoint me as much as this one. I mean, Yes, it was my choice to read, but I genuinely thought you would have something interesting to talk about. All I hear is a bunch of crying about something that you could fix if you were not too busy looking for attention. online laptop repair center bangalore Spot on with this write-up, I really feel this site needs far more attention. I’ll probably be back again to see more, thanks for the information! dell repair center bangalore

  6. You're so cool! I don't suppose I have read anything like that before. So good to discover somebody with a few original thoughts on this topic. Seriously.. many thanks for starting this up. This website is one thing that is required on the web, someone with a little originality! macbook repair center bangalore I really like reading a post that will make men and women think. Also, thanks for allowing for me to comment! acer repair center bangalore

  7. The promotion around Big Data is only that, BIG. Despite the fact that Big Data is anticipated to be the future grain for all investigation. Data Analytics Course

  8. Great Article! thanks for sharing this informative with us. Best Cancer Hospital in India

  9. ShipShop.Pk is the fastest growing online shopping store in Pakistan, where we offer a wide range of imported & local products from all categories at the most competitive prices to facilitate our valuable customers.

  10. This is also a very good post which I really enjoyed reading. It is not every day that I have the possibility to see something like this.. ONMA ist eine Google AdWords Agentur

  11. This is also a very good post which I really enjoyed reading. It is not every day that I have the possibility to see something like this.. Agentur für Google Adwords

  12. I was surfing the Internet for information and came across your blog. I am impressed by the information you have on this blog. It shows how well you understand this subject. worldfree4u

  13. Final approach to compose and distribute for different sites while connecting back to your very own is through a corresponding connecting comments service in 1$

  14. I think that thanks for the valuabe information and insights you have so provided here. Apnetv

  15. Wow! Such an amazing and helpful post this is. I really really love it. It's so good and so awesome. I am just amazed. I hope that you continue to do your work like this in the future also big data

  16. Offering your substance to different sites can not exclusively be useful for your Bottom line temporarily, it can likewise be beneficial for you over the long haul since it can expand your perceivability on the web, driving more clients toward your site. SEO PACKAGE

  17. this is really trusted website to download Bollywood and hollywood movies in hindi dubbed and south indian movies in hindi at khatrimaza

  18. At that point you can arrange the connection and area regarding the two locales. As quickly as time permits, Backlinks service

  19. Just admiring your work and wondering how you managed this blog so well. It’s so remarkable that I can't afford to not go through this valuable information whenever I surf the internet! Web automation

  20. This is such a great resource that you are providing and you give it away for free. I love seeing blog that understand the value of providing a quality resource for free. free movies websites

  21. Thanks for the information

  22. I am impressed by the information that you have on this blog. It shows how well you understand this subject.
    data analytics course

  23. I often use online converters to convert files (e.g. doc to pdf, tiff to jpg, etc.). The best in my ranking -

  24. Presently with Mac Flip Video Converter, every one of these issues won't make any issues.FLV to MP4 video converter


  25. Very nice job... Thanks for sharing this amazing and educative blog post! ExcelR Data Analytics Courses

  26. wonderful article. Very interesting to read this article.I would like to thank you for the efforts you had made for writing this awesome article. This article resolved my all queries. keep it up.
    data analytics course in Bangalore

  27. I will really appreciate the writer's choice for choosing this excellent article appropriate to my matter.Here is deep description about the article matter which helped me more.
    Know more about Data Analytics

  28. Aap Ager Best Photo Banane Wala Apps Ki Talash Kar Rhe Hai Tho Ye Post Read Kar Shkte Hai Saath Mai Aap Ager Jana Chate Hai Youtube Se Video Download Kaise Kare Tho Ye Post Apki Madad Kar Shkti Hai Saath Mai Apko Ye Bhi Btane Wala Hu Youtube Se Paise Kaise Kamaye

  29. wonderful article. Very interesting to read this article.I would like to thank you for the efforts you had made for writing this awesome article. This article resolved my all queries.
    Data Science Course