I use no-nonsense and established methodologies that I have tested for YEARS to improve the bottom line of my clients
Make no mistake about it, Excel is a powerful Google Ads manager. In this in-depth article, you’ll learn how to use it to optimize your campaigns and get better results.
The truth is, the effective use of time and a well-defined purpose are two factors that can make or break the success of any PPC manager.
Being proficient with Microsoft Excel, and knowing the easiest way to analyze data, find insights, plan a campaign, and create new PPC campaigns can help you improve your campaign performance and minimize stress.
Let’s dive in.
A pivot table is one of the most powerful features in Excel. A pivot table is used to process and analyze data. It counts, sorts, and summarizes data.
You can use a pivot table to analyze PPC campaigns.
In this example, we will use “hour of day” segmentation. Simply download the CSV report from the dimension tab -> Time -> Hour of day.
The first step is to delete the “Total” row and the first row with the date range. Now, let’s create a pivot table:
Now you can add more rows, columns, and values to the table, such as clicks, impressions, CTR, cost, conversion rate, and others (new dialog on the right).
Select the field and drag it to report filter, column labels, row labels, or values. Move hour of day to rows, day of the week to columns, and sum of clicks to values.
This is how your data will be represented.
Use conditional formatting to highlight the best hour and day in terms of clicks.
This is just one example of how to use pivot tables for analyzing clicks. Potentially, there are unlimited ways a pivot table can help you manage your Google Ads campaigns more efficiently.
The NETWORKDAYS function in Excel is used to return the number of work days between any 2 dates. It excludes weekends and all holidays.
This function is extremely useful for advertisers. You can control the budget of a PPC campaign with the NETWORKDAYS function.
It helps you to quickly calculate the number of weekdays in any month, and can allocate PPC budget for those days only. It’s a simple formula that will help you automate budget allocation.
This is extremely valuable for those situations in which you want to run a campaign on weekdays only because the pivot table indicated that your campaign received fewer clicks on weekends.
The NETWORKDAYS will help you determine the number of weekdays in a month, thus helping you allocate budget to the exact number of days.
This actually helps you manage your budget, estimate cost, and determine your campaign duration as well as the estimated number of clicks.
Once you know the actual number of days and budget per day for a given month, the rest can be done easily in the Google Ads ad manager.
The LEN function is used to find the length of a string. It simply counts the length of any string.
It is used to count the characters in your PPC ad heading and description. Yes, writing ads in Excel is not a bad idea after all – this function makes it easier.
If you’re dealing with only few copies, it doesn’t make much sense to use the LEN function. However, imagine how it can help if you are running multiple Google Ads campaigns for different clients, and if each campaign has several ad copies.
When you’re dealing with a handful of ad copies, LEN function is a life saver.
Insert the LEN function to count the characters. You can use conditional formatting to highlight the cells that cross the character limit.
If you use dynamic keyword insertion, use the following formula to prevent Excel from counting the characters of DKI.
=LEN(A2)-10*COUNTIF(A2,”*{KeyWord:*}*”)
This is a simple technique to generate modified broad match keywords for your campaign. Google Ads is not particularly good at this. You need to use the SUBSTITUTE function in Excel.
First, let’s see what a modified broad match keyword is.
A + sign is used with a keyword in order to trigger ads when the same word or its variant is used in the search query. For instance, cheap +laptop will trigger ads only when “laptop” or its variant (laptops, laptopp, etc.) appears in the query.
Google Ads allows modified broad match keywords.
The SUBSTITUTE function helps you create modified broad match keywords in Excel.
The SUBSTITUTE function is used to replace text based on the content. The syntax used for this function is as follows:
=SUBSTITUTE (text, old_text, new_text, [instance])
“Text” is used to refer to the cell where the old text is placed. The old text will be replaced with the new text. “Instance” is optional, and is used to specify the occurrence of any old text you plan to replace.
Here is how to do it:
Add all the keywords in a column and use the following formula in the next column.
=”+”&SUBSTITUTE (A2,“ “,” +”)
This will add + to all the keywords. You can then copy all the modified keywords and paste them in Google Ads.
Imagine doing this in Google Ads for the 440 keywords that you have for one campaign. Imagine having more than 10 campaigns. Seems a lot of work, right?
This simple formula can save you time.
The TRIM and PROPER functions in Excel help clean data. They remove extra spaces, capitalization issues, and remove all ambiguous characters.
TRIM removes extra spaces, while PROPER converts the first letter of every word to uppercase. The function CLEAN helps remove all non-printable characters.
TRIM and PROPER are handy functions that help with cleaning the ad copy. When you write ad copy, you know how important space is.
Every character matters. Every letter counts. There’s no tool to detect extra spaces. At the same time, converting the first letter of every word into a capital is tedious if you have to do it manually.
Instead of manually proofreading your ad copy, why not use a simple function that automates the entire process?
Once you have the function entered in a sheet, it will clean all the ad copy that you pasted there.
Simply add the TRIM function to an empty column to remove all extra spaces.
PROPER and TRIM can be used together in a single column to capitalize the first letter of each word and to remove extra spaces.
Use the following formula in an empty column.
=PROPER(TRIM(A2))
Now you have all the headlines and ad copy fully formatted and ready to use. Copy the ad and paste it in Google Ads.
These two functions work extremely well for marketers and PPC managers who have to deal with great amounts of ad copy.
Most of you have been using the SUM function to add in data from multiple cells. For instance, if you have to calculate the total number of clicks, impressions, or conversions in Excel for an Google Ads campaign, you use the SUM function, right?
Not right!
There’s a potential drawback in using the SUM function that most advertisers don’t know.
The SUM function adds all the numbers in a given range of cells. This works fine as long as you don’t apply filters.
When you apply filters, the SUM function will show the sum of all the cells – not just the filtered cells. This is where things get confusing.
When you’re managing a PPC campaign in Excel, you’ve got to use filters. For example, you need to apply filters to see a specific keyword performance.
When you apply filters, the SUM function will show the sum of all the cells, not just the filtered cells. To avoid this particular issue, use the SUBTOTAL function.
The SUBTOTAL function is used to total the numbers in cells when you apply a filter. It adds the cells that are visible, thus giving you more accurate results.
As a marketer and Google Ads user, you simply can’t avoid using filters. Therefore, always use the SUBTOTAL function to sum a specific number of cells. It will always give you the correct figure.
A good idea is to add the SUBTOTAL function in the top-most row of all the columns. It will be visible all the time and will give you an exact total.
Did you know that you can manage your Google Ads account via Google Spreadsheets?
I bet you didn’t.
A simple script will send all your Google Ads account metrics and campaign details to your drive or computer. Any changes you make in the spreadsheet will be synced to your campaign.
This is how the sheet looks:
Any changes you make in the sheet will be synced with your campaign. There is no longer a need to sign into Google Ads accounts.
You can send this sheet to your client and let them edit the campaign. Anyone can edit the sheet. Your clients will love you for this.
In addition, the sheet contains all the metrics such as clicks, impressions, conversions, etc. Moreover, you and your clients will also get regular updates. Everything is editable.
To get started, you have to use this script.
Once the script is inserted, you can edit headlines, descriptions, status, device preferences, and URLs from the spreadsheet. When you save the sheet, the changes will be implemented when the script runs again.
This is one of the best uses of Excel that I know. Unfortunately, only a few marketers know this, to say nothing of using the script.
Take a leap forward and start using it today. I know you’ll love it.
So, you’ve been running campaigns in Google Ads for a few weeks, and now you’re interested in creating charts to share with your team and/or clients. Since most of the people don’t understand reports and digits, it’s a good idea to convert data into charts.
Charts are easier to understand. They make sense.
Excel doesn’t let you create charts from the Google Ads data. However, the sort feature is very helpful. It lets you sort the data before creating charts, thus making the charts easier to understand.
The chart below was created without sorting the data.
It definitely isn’t user-friendly. The same chart looks more meaningful when the data has been sorted.
When you’re dealing with several charts for your Google Ads campaign, you must sort data either in descending or ascending order so that all the charts follow the same pattern and can be skimmed easily.
A few clicks will sort all of the data, and will create charts that will appeal to your audience.
A bubble chart in Excel is a variation that uses graphic bubbles to indicate the data. The extent of the data is represented by the size of the bubble.
Bubble charts are useful in comparing categorical data with quantitative data. For instance, campaigns and ad groups are represented by the number of bubbles, while cost and CTR are represented by the size of the bubbles.
Bubble charts, like other charts, are helpful in describing your campaigns and progress. Information that is otherwise hard to deliver can be easily communicated using these charts.
For example, your client, who has never run an Google Ads campaign, can easily understand the bubble charts (image above), while it would be really hard for him to understand the same data using an Excel spreadsheet.
Because creating Bubble charts in Excel is a bit tricky, advertisers and PPC managers usually don’t use them.
“Day Parting” is a common practice that is defined as optimizing the Google Ads schedule and spending based on certain variables.
Using Day Parting, you can download segmented reports from Google Ads to optimize your campaigns in Excel. The process is rather simple.
First, download segmented reports for a specific time period and for specific days. Then, open the reports in Excel and sort and analyze them to determine the best time of day to maximize clicks and conversions, and to minimize CPC. You can also see which days of the week perform better and which ones perform poorly.
Based on the analysis, you can increase budget, pause certain days for maximum performance, adjust bids, etc.
You can also use these reports to create charts that can then be shared with clients and team members. Potentially, there are unlimited uses for Day Parting in running Google Ads.
Match Type is an Excel function that finds the position of an item in a range, and then returns the position of the item.
This is a useful function for PPC managers that can be used to manage keywords in Excel. Match Type can be used in the pivot table to sort the keywords into groups and segments. It makes them identifiable.
Match Type can also be used to find and highlight the cells containing maximum or minimum values. For instance, it can help when you’re dealing with a large Google Ads campaign file, and you’re interested in finding all the instances where conversions were more than 10%. This can be done using Match Type.
Match Type can also be used to find other items throughout the sheet.
It highlights the cells to make all such items prominent.
Did you know that you can find the conversion rate (CVR) for each keyword in Excel?
Of course you can find this in Google Ads too, but if you’re managing your campaigns in Excel, it’s a good idea to calculate and find your CVR in Excel as well.
Download the report and open it in Excel to get started.
Step #1: Create a pivot table with the following settings as shown in the image below:
Step #2: Convert the settings of the cost and conversions into a percentage. This will make things easier since conversion rate is calculated and reported as a percentage.
Step #3: Create a new field for CVR. This will calculate the conversion rate for each individual keyword.
The final report will look something like this:
What’s next?
You can use this sheet to create charts and adjust bidding. More importantly, you can improve the optimization of your campaigns.
Filters in Excel are tricky. If you don’t know how they work, you might end up deleting some of your data.
For instance, if filters are applied and you delete rows, you’ll end up deleting a lot of rows that were hidden by the filter.
There’s a simple rule to avoid such accidents.
But first, why use filters and how do you apply them?
When you’re dealing with hundreds of rows and columns of Google Ads data, the filter makes it easier to find the data you need.
Here’s how to apply filters at the top of a column:
Select the column and click “filter” in the Data tab.
Now you can filter data easily. You can select a campaign, keywords, day, etc.
If you have to delete a campaign when filters are on, or if you want to delete a specific row for any reason, follow these steps:
2. Select “visible cells only” and click OK.
3. Now all the visible rows are selected. You can delete them.
This won’t delete any hidden rows. You’ll never lose data.
Always use this technique to remove columns and rows when you’ve applied filters.
You can analyze ad groups in detail in Excel. To get started, download the most relevant report from Google Ads and open it in Excel. It is recommended that you download the hour of day report.
Create a pivot table. Don’t forget to delete the totals.
Use the following settings for the pivot table.
Add any metrics in the Values such as CTR, clicks, impressions, etc.
You can analyze and create charts based on ad groups. This is the best approach to analyzing ad groups in detail.
Most PPC managers don’t focus much on ad group analytics. A few clicks can reveal information that might help you save hundreds of dollars for your clients. You may also be able to improve CTR and quality score by tweaking ad groups.
Who knows? You now know the potential.
Excel is an advertiser’s best friend. Stop underestimating and underutilizing it. Excel has a lot more to offer than you can imagine.
Start using Excel to optimize your Google Ads campaigns, and impress your clients and peers.
