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:
https://developers.google.com/adwords/api/docs/appendix/reports/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.

51 comments:

  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

    ReplyDelete
  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

    ReplyDelete
  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!

    ReplyDelete
  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

    ReplyDelete
  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

    ReplyDelete
  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

    ReplyDelete
  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

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

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

    ReplyDelete
  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

    ReplyDelete
  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

    ReplyDelete
  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

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

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

    ReplyDelete
  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

    ReplyDelete
  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

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

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

    ReplyDelete
  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

    ReplyDelete
  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

    ReplyDelete
  21. Thanks for the information

    https://www.mindsoftglobal.com/

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

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

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

    ReplyDelete

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

    ReplyDelete
  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

    ReplyDelete
  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

    ReplyDelete
  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

    ReplyDelete
  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

    ReplyDelete
  30. Our blog is awesome and very interesting. Every one should go and check it.https://cuteinsides.com/home-improvement-guest-post/

    ReplyDelete
  31. Hi! I could have sworn I’ve visited your blog before but after going through many of the articles I realized it’s new to me. Anyhow, Oppo Service Center near me I’m certainly delighted I found it and I’ll be book-marking it and checking back often! lg mobile Service Center in Chennai

    ReplyDelete
  32. Your style is so unique compared to other folks I have read stuff from. huawei display repair Chennai Thank you for posting when you have the opportunity, Guess I will just book mark this web site. Xiaomi Service Center

    ReplyDelete
  33. Hi, I do believe this is an excellent web site. iPhone Charging port replacement I stumbledupon it ;) I'm going to revisit yet again since i have saved as a favorite it. Best Mobile Service Center Chennai Money and freedom is the best way to change, may you be rich and continue to guide others.Vivo Service Center in tirunelveli

    ReplyDelete
  34. I encourage you to read this text it is fun described ... שאלון אישיות

    ReplyDelete
  35. I am hoping the same best effort from you in the future as well. In fact your creative writing skills has inspired me. backpage alternatives

    ReplyDelete
  36. 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 inspired me to read more. keep it up.
    Correlation vs Covariance
    Simple linear regression

    ReplyDelete
  37. Howdy, I am walker. We are offering help for HP Printer. On the off chance that you have any technical glitches, simply contact us. For more information ,visit 123.hp.com/setup.

    ReplyDelete
  38. You mobile phone acts artificially intelligent at a very low level of it. All the video games you play are managed by some kind of game engine which is a form of artificial intelligence functions on logic. artificial intelligence training in hyderabad

    ReplyDelete
  39. The development of artificial intelligence (AI) has propelled more programming architects, information scientists, and different experts to investigate the plausibility of a vocation in machine learning. Notwithstanding, a few newcomers will in general spotlight a lot on hypothesis and insufficient on commonsense application. machine learning projects for final year In case you will succeed, you have to begin building machine learning projects in the near future.

    Projects assist you with improving your applied ML skills rapidly while allowing you to investigate an intriguing point. Furthermore, you can include projects into your portfolio, making it simpler to get a vocation, discover cool profession openings, and Final Year Project Centers in Chennai even arrange a more significant compensation.


    Data analytics is the study of dissecting crude data so as to make decisions about that data. Data analytics advances and procedures are generally utilized in business ventures to empower associations to settle on progressively Python Training in Chennai educated business choices. In the present worldwide commercial center, it isn't sufficient to assemble data and do the math; you should realize how to apply that data to genuine situations such that will affect conduct. In the program you will initially gain proficiency with the specialized skills, including R and Python dialects most usually utilized in data analytics programming and usage; Python Training in Chennai at that point center around the commonsense application, in view of genuine business issues in a scope of industry segments, for example, wellbeing, promoting and account.

    ReplyDelete
  40. This is my first visit to your blog! We are a team of volunteers and new
    initiatives in the same niche. Blog gave us useful information to work. You
    have done an amazing job!

    artificial intelligence training in Bangalore

    ReplyDelete
  41. Attend The Data Analyst Course From ExcelR. Practical Data Analyst Course Sessions With Assured Placement Support From Experienced Faculty. ExcelR Offers The Data Analyst Course.
    Data Analyst Course

    ReplyDelete
  42. 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 inspired me to read more. keep it up.
    Correlation vs Covariance
    Simple linear regression
    data science interview questions

    ReplyDelete
  43. This is a wonderful article, Given so much info in it, These type of articles keeps the users interest in the website, and keep on sharing more ... good luck.

    Simple Linear Regression

    Correlation vs Covariance

    ReplyDelete

  44. Really nice and interesting post. I was looking for this kind of information and enjoyed reading this one. Keep posting. Thanks for sharing.
    python course training in guduvanchery

    ReplyDelete

  45. This is an awesome motivating article.I am practically satisfied with your great work.You put truly extremely supportive data. Keep it up. Continue blogging. Hoping to perusing your next post
    360DigiTMG

    ReplyDelete