No, if you have Mac. Unfortunately, PQ is not supported on Mac yet. More information here. You can still use virtual Windows machine but it is quite cumbersome.
So how about old school Windows users? Hell yes, you should! In this article, I will cover why I think PQ is good (maybe the best) tool for common PPC operations. Of course, I am probably biased so read it as my personal opinion.
***Tip: Have a question? Ask in the comment section below or in this Facebook group. You can also sign up for our free newsletter.***
***Do you feel like you need more help? We can help you with PPC Automation and/or with your reporting under our new project MythicalReports.com here.***
What is Power Query?
Power Query (aka “PQ”) is originally a powerful business intelligence tool for processing flat tables. For extracting, cleaning, transforming, merging data. Yes, it does not sound that exciting. Where is the link to PPC? Let’s face it, we PPC people think we are marketers but we are not. We are mainly analysts always looking at data. Always looking at tables. Always processing tables. We are just analysts with little bit of creative edge. What does a good analyst need to do a good job? Good tool for processing tables! So here comes PQ.
Here are the 3 main areas where you can make great use of Power Query in PPC industry:
What are the main advantages of using Power Query?
- All the steps are recorded. You basically build tools as you work in PQ. You can refresh your tool any time you want as long as the structure on input data is unchanged.
- You are not changing underlying data. All the steps are done in preview mode.
- The result of previous step is input for the next step. Imagine you get an export from AdWords, you need to filter out some campaigns, then you need to be looking only at specific ad groups, then you need to e.g. replace characters in Final URL in these ad groups. In PQ, you would filter out the campaigns in the first step, then filter for the groups in the second step, then run Find & Replace as the third step only over the pre-filtered data. Once you do this once, you can run this sequence of steps over and over. Anytime you want.
- Most of the steps can be easily edited retroactively.
- UI is user friendly. There are buttons for 90% of operations.
- No copy’n’pasting involved.
- It does not matter how many rows you are processing. All the operations are performed over entire column. Automatically.
- You don’t need to be coder. Imagine like you are preparing Excel macros (aka VBA) inside a user friendly UI with buttons.
- PQ is way more powerful than standard Excel despite the fact it resides inside Excel. Just fot 1 GB export from AdWords? Not big deal for PQ, the file can be easily loaded into PQ… I have been doing look ups from a 3GB CSV file into 5GB CSV file without any issues.
Power Query can work with these data sources:
- Excel tables inside the same doc
- Excel files
- CSV documents
- TXT documents
- Google Sheet files living online
- Dropbox files living online
- Internal databases
- Internal data cubes
- Tables on webpages
- Multiples files from 1 folder (e.g. joined 50 Excel files or 50 CSV files in a specific).
Reports, numbers, tables, joins and all that stuff is the original target market for Power Query. You can build easily refreshable reports based on multiple data sources built step by step. During every refresh, the steps are always repeated in the same order, with same parameters, with same rules. Typically, you need to build a report based on GA data, AdWords data and less standard CRM data (Czech guys will be also appending Sklik data for sure). In standard Excel, you will be suffering every week while preparing the consolidate report. In PQ, you just prepare the report once and then keep refreshing it. There are buttons for most of the operations. Even the for dreaded equivalent of VLOOKUP function. And yes, IF function has its own button as well! There are even buttons for pulling last X characters from start, middle, or from end of a text string. You will not find buttons for these operations in standard Excel.
Bulk edits & automation of PPC was not definitely the primary target of PQ development team. But there are some great features every PPC specialist can make use of. Imagine you need to classify KWs into ad groups automatically on regular basis. You can easily build a tool for that. Imagine you need to pre-qualify search terms which can be added as potential KWs to your account. You can build a tool for that. Imagine you need to evaluate the best ad in every ad group. You can build a tool that. There are no limits. Just your imagination, needs and time. The downside of using PQ for automated tasks is that the result rows will always end in your Excel (or in Power BI), then someone needs to take these rows, check them and upload them to AdWords/Bing/Sklik etc. So it is not really 100% automation. The question is whether you really want to have running 100% automated tasks and have little to no control over them. This leads to AdWords Scripts, which can be considered as holy grail of automation. The problem is that not everyone can learn (or have time) to write AdWords Scripts. On the other hand, if you are using Excel as regular user, you can be easily using PQ. In fact, some operations are way easier in PQ than in standard Excel. But back to AdWords Scripts. Scripts give you full flexibility, you don’t have to upload anything anywhere, because everything “just happens” in AdWords. Time needed for writing a complex script can be measured in days, then you need to add some testing time as well. Generally speaking, building fairly complex tools in Power Query will take you 1, maybe 2 days max after few weeks of practice. By fairly complex tools, I mean e. g. full restructure of X accounts in X countries in X languages based on various rules for grouping into ad group, for assigning relevant ads etc.
Campaign creation is also a big one for PQ. There are quite easy tricks to create KW combinations, create highly relevant ads with changing variables, then pairing the ads with ad groups, assign bids based on rules or based on reference tables and so on. The main advantage of using PQ is that you completely skip all copy’n’paste operations. Actually, you cannot even copy’n’paste inside PQ UI! How cool is that? As long as your inputs are organized in tables, then you are good to go in PQ. You can create “template” tools in case you are an agency which can reduce onboarding time of new clients to hours instead of days.
In my case, I completely abandoned standard Excel for campaign edits or creations. I also completely abandoned AdWords Editor for similar reasons. I don’t like to copy’n’paste. I like to have ALL my steps recorded so I can get back to them later and see what the result was of any step in the process. The more you start using PQ, the more you lazier you become to use standard Excel or AdWords Editor for doing major changes. Once you master PQ, you will never come back to Excel or to AdWords Editor. Yes, you will still use them for very minor changes but never for anything big.
Ok, so this is all positive (as you would expect, right) but the question is how long does it take to learn PQ?
- For basic operations for beginners, we are talking about hours (yes, hours, not days). Just watch few videos on this website, and try to play with PQ in your Excel. I have trained over 50 people in a 4 hour “beginner course” and I am pretty sure most of them were able to make use of PQ for their repeated tasks after this 4 hour training.
- For becoming intermediate user, we are talking about few days, 2 weeks max. The only requirement is just to use PQ on daily basis. At the end, there are buttons for most of the operations so it is not really a rocket science.
- For becoming PQ power user, we are talking about 1 month or 2 months max. Power user can be defined as someone who is able to build very complex custom functions (e.g. function for KW classification based on very complex rules).
Even if you become just intermediate or stay on beginner forever, you will save hundreds of hours of work every year.
So to sum up, the key strength of PQ is that it offers great balance between having great features for PPC work, effort+skills+time needed and “user friendliness”.
The key weaknesses are:
- The result always ends in Excel. You cannot really send e. g. the new KWs anywhere. You have to upload them manually.
- Your input data needs to be organized in some sort of a table. It does not have to be a perfect table – PQ can help with cleaning or with extracting.
- You cannot really change single cells once in PQ UI since all the operations are performed on column level.
Thoughts? Drop your comments below!
DZ
***Tip: Have a question? Ask in the comment section below or in this Facebook group. You can also sign up for our free newsletter.***
***Do you feel like you need more help? We can help you with PPC Automation and/or with your reporting under our new project MythicalReports.com here.***