This sample spreadsheet lets you see the top ranking Ad Groups by month over one year. When you can see which months and which Ad Groups were performing better, you can imitate the winners and drop the losers. For each Ad Group, in each Month, you get the following data.
- Impressions aka ga:impressinos
- Ad Clicks aka ga:adClicks
- Ad Cost aka ga:adCost
- Cost Per Thousand aka ga:CPM
- Cost Per Click aka ga:CPC
- Click Thru Rate aka ga:CTR
- Cost Per Transaction aka ga:costPerTransaction
- Cost Per Goal Conversion aka ga:costPerGoalConversion
- Cost Per Conversion aka ga:costPerConversion
- Revenue Per Click aka ga:RPC
Querying Google Analytics gets you all the data you need to optimize your Paid Ads
The first thing this sample Excel spreadsheet does is calculate the Top 10 Ad Group Months. It also adds two calculated columns:
- Average fo the Top 10, and
- Average of The Rest (anything not in the top 10).
These two calculated columns are useful for comparison. Here is the table of data showing the Top 10. There are other tables, showing the full data set.
You can build any chart you want from the data in this spreadsheet.
I chose to build FIVE CHARTS, each one giving an interesting view of the data.
How are your Ad Groups performing — which ones are your most successful ones?
The first two charts shows the top performing ad group/month combinations. They are on separate charts because I felt the axis values fit more closely together.
The first chart pairs cost per thousand against Cost Per Click. Depending on my business goals, I’d want to optimize for one over the other. It’s nice to have a view of the Top 10 because it gives me something to aim for in all my future Ad Group planning.
The second chart gives you an impression of how well the ads themselves are doing, by showing their click through rate. Bear in mind, that a small percentage on a large number of impressions is a lot more than a high percentage on a small number of impressions. Your ads might be highly targeted with good click through, or they may be vague but a poor click through, both arriving at the same number of clicks. It depends on your business and the amount of time preparing ads whether one of these is preferred.
Was it worth running your PPC Campaigns?
For the next three charts, we start to consider revenue and achievement of Goals. The third chart shows revenue per click, vis a vis the other metrics. Ideally, you want your costs low, your goals (the positive ones at least) high, and your revenue high. This chart shows you which Ad Groups in which months did better than others. It’s very useful, because you might be able to imitate it in future months, and you can also cull the Ad Groups which are performing poorly. From the look of this sample data, monthly newsletters and do better than Ad Groups which try to appeal by genre.
Can you see the Trees in the Forest?
The following chart gives a high level view in that it includes all Ad Groups all Time. i.e. not the Top 10. If the line is higher than the bars, then those Ad Groups in those Months did pretty well. It’s a “big picture” view, helps you plan where to spend your time, and what things you can do to improve an Ad Group or remove the ones that don’t perform.
Top Performing Ad Groups Life To Date
The final chart is all Ad Groups in all Months, sorted by which ones generated the highest revenue per click. These are your winning Ad Groups of all time. This chart gives you a sense of your “long tail” in Ad Group performance. It gives you a sense of your Top 10 or Top 20, if they are representative of your life to date performance.
Free Download
You can download the spreadsheet here:
Analytics Pondering
Please remember, this is analytics at the Ad Group level. Within an ad group, it is entirely possible that you have wildly fluctuating values which result in an average with a high standard deviation. For example, an Ad Group might have 1 ad that was wildly successful, and 10 others that had zero results. What would an average tell you? When there is a high standard deviation such as this, the use of the Average to make business decisions is a bit risky. You should also be looking within Ad Groups for this kind of situation, before you decide to rely on the aggregate numbers of the Ad Group.
Power Users
If you want to start from scratch, or put it into a dashboard you already own, then past the following text into a worksheet and rename that worksheet to end in _actions e.g. adgroup_actions. From there, you will get the data in worksheets, and you can build your own charts by referencing the data. If you study the script closely, you can see where to change which columns are sorted, or how many rows to include or exclude e.g. change it to Top 20. You can also add/remove metrics and dimensions, but make sure you keep the
prompt,add,[GROUP],AdGroup
prompt,add,[GA_DIMENSIONS],"&dimensions=ga:adGroup,ga:yearMonth"
prompt,add,[GA_METRICS],"&metrics=ga:impressions,ga:adClicks,ga:adCost,ga:CPM,ga:CPC,ga:CTR,ga:costPerTransaction,ga:costPerGoalConversion,ga:costPerConversion,ga:RPC"
prompt,add,[ROLES],"nxtext_f1,nxtext_f2,nxnumeric_Impressions,nxnumeric_Ad Clicks,nxnumeric_Ad Cost,nxnumeric_Cost Per Thousand,nxnumeric_Cost Per Click,nxnumeric_Click Thru Rate,nxnumeric_Cost Per Transaction,nxnumeric_Cost Per Goal Conversion,nxnumeric_Cost Per Conversion,nxnumeric_Revenue Per Click"
Prompt,default,[GA_FILTER],
Prompt,add,[GA_PROFILE_ID],
Prompt,default,[GA_SEGMENT],&segment=gaid::-1
Prompt,default,[GOOGLE_ID],
prompt,default,[PERIOD],Past 1 Years
Prompt,default,[MAX_RESULTS],&max-results=1000000
Prompt,add,[GA_SORT],
Prompt,add,[FILENAME],ga-metrics-[GROUP].csv
GetGoogleAnalyticsData[GOOGLE_ID],,,[GA_PROFILE_ID],[PERIOD],[FILENAME],"[GA_SEGMENT][MAX_RESULTS][GA_DIMENSIONS][GA_METRICS][GA_SORT][GA_FILTER]"
ImportDataFiles,[FILENAME],"[ROLES]",0,1,utf-8,,yyyy-MM-dd,en-US,,""
concatenatetext,," "
newRowLabel,Ad Groups (each month)
prompt,add,[FULL-LIST],All Ad Groups
pageCaption,[FULL-LIST]
;;; find the Top N and write a sheet
sort,first,descending
prompt,add,[NUM_ROWS],10
SelectRange,Row,First,[NUM_ROWS]
Combine,Row,Average,unselected,remove,Keep,Average of The Rest,False
SelectRange,Row,1,[NUM_ROWS]1
Combine,Row,Average,selected,keep,Keep,Average of The Top [NUM_ROWS],False
newRowLabel,Top Ranking [GROUP]s
swap
PageCaption,Top Ranking [GROUP]s for [PERIOD]
prompt,add,[DATA_SHEET_NAME],Top Ranked [GROUP]
prompt,add,[OVERWRITE],overwrite
prompt,add,[TOP_ROW],1
prompt,add,[TOP_LEFT],1
prompt,add,[BOTTOM_ROW],-1
prompt,add,[BOTTOM_RIGHT],-1
SaveInWorksheet,current,[DATA_SHEET_NAME],[OVERWRITE],visible,[TOP_ROW],[TOP_LEFT],unformatted,[BOTTOM_ROW],[BOTTOM_RIGHT]
; return to the original list and write that as a sheet
Select,Page,[FULL-LIST]
Sort,last,Descending
PageCaption,Top Ranking Ad Group (month) by Revenue Per Click
SaveInWorksheet,current,[FULL-LIST] Ranked,[OVERWRITE],notvisible,[TOP_ROW],[TOP_LEFT],unformatted,[BOTTOM_ROW],[BOTTOM_RIGHT]
; return to the original list and write that as a sheet
Select,Page,[FULL-LIST]
PageCaption,Ad Group (monthly)
SaveInWorksheet,current,[FULL-LIST] Monthly,[OVERWRITE],notvisible,[TOP_ROW],[TOP_LEFT],unformatted,[BOTTOM_ROW],[BOTTOM_RIGHT]