In today’s post, which is our third installment of this week’s PPC Love Series, I will be telling a tattered tale of heartbreak that has occurred over trying to use AdWords and Anlytics reporting separately for eCommerce clients. Fortunately this story has a happy ending, one that would keep Cupid himself aflutter. In the words of Al Green, “Times are good or bad, happy or sad…” My aim with this post is to make them more good/happy and to show you that you can use both AdWords and Analytics reports in harmony.
My biggest challenge in managing PPC accounts for eCommerce clients has been not being able to see revenue data in Google AdWords. Conversions and conversion tracking really don’t do me much good because my clients’ products have different values, so I can’t assign specific values to conversions. Of course, my keyword revenue data is available in Analytics, but I can only add one secondary dimension at a time to drill down this information, choosing either match type, campaign OR ad group. This isn’t entirely useful if I want to do bid changes that factor in revenue or ROI, as I need to have a complete data set to upload changes into AdWords.
Because I love you, I have found a work around, and I am happy to share it today with you. It’s fairly easy, and it’s worth the extra few minutes you’ll spend compiling your reports in the long haul. Combining your AdWords metrics with your Analytics data will allow you to analyze your PPC campaign on many new levels. You’ll never look back! I will stop keeping you in suspense about this newly defined, revamped relationship between your AdWords and Analytics reports. Consider them turned around and made up (thanks, Al!).
In this post I’ll be detailing how to pull together a keyword report with revenue data, but you could apply this to any account level (campaign, ad group, keyword). So, without further ado, let the reporting honeymoon begin.
Step 1 – Analytics Custom Report
Build the report you want in Analytics. All your AdWords data (clicks, impressions, cost, etc.) is actually available to you in Analytics (assuming your account is linked to Analytics), you just might not know where/how to access it. The easiest way to get all the information you want in the same place is to create a custom report.
On the custom report tab, click the button at the top of your screen to create a new custom report. You’ll want to make sure that you’re creating an Explorer report, and not a Flat Table report, because you’ll be segmenting your data further once it’s created.
Since I’m only interested in Google data for this report, I made sure to filter this report accordingly:
Essentially, you just want to recreate the metrics available to you in AdWords in Analytics. Not all metrics are available, but the key ones are. Here’s the final structure of my custom report:
If you need more information about creating custom reports in Analytics, check out Google’s Quick Start Guide.
Step 2 – Export Your Data From Analytics
Okay, this is the only part of this process that can be a little labor intensive if you have a lot of keywords in your account. After you’ve created your report, run it and make sure you’ve set your view to show the maximum number of entries on your screen, 500 (you can change this at the bottom of your report in a small drop down menu). Then go to the top of your screen and click Export, then choose CSV as your file type.
Frustratingly enough, Analytics will only export the data that is shown on your screen, so you’ll have to dump your keyword data into your report 500 keywords at a time. I just created one report and copied and pasted all subsequent exports into my original. Keep things easy for yourself and don’t edit your columns until after you’ve dumped in all your keywords.
Step 3 – Reformat Keywords
To prime your report to merge with the data you’ll be pulling out of AdWords, you’ll need to format your keywords like the appear in AdWords with the keyword identifiers for each match type (“phrase”, [exact]). The fastest way to do this is to sort your keywords by match type, then use a blank worksheet to edit. Copy and paste keywords one match type at a time onto your blank worksheet and use the concatenate function in Excel to put parentheses or brackets around your keywords.
Copy your concatenated list and paste back into your original report to overwrite the keywords in your keyword column, but make sure you use Paste Special – Values to only copy in text. Otherwise you’ll paste in your formula, which won’t work since your original data is on another sheet.
Here’s what my Analytics report looked like when I was finished:
Step 3 – AdWords
Head into AdWords, and download a keyword report. Make sure your keyword match type is included in the data you export, and also make sure you’ve run the exact date range you used in your Analytics report. Clean up your report by getting rid of getting rid of data you don’t need. In this example, I got rid of all paused keywords, since I’m looking to make bid changes on active keywords, and then I deleted my status column. When this was all said and done, here’s what my report looked like:
Copy or move this worksheet into the same Excel file that houses your Analytics sheet.
Step 4 – Combine and Be Amazed
Now it’s time to combine your AdWords and Analytics reports to have campaign, ad group AND revenue/ROI data for each keyword! To do this, go back to your Analytics sheet and add in columns for the missing AdWords data you want to add. Make sure that you add the columns in the same order that they appear on your AdWords report. You’ll need the columns to be in the same place because you’ll be referencing data by column number.
A brief note, the CPC shown on your Analytics report is actually your keyword’s avg. CPC, and not your max CPC. I just changed the column title, and then set up my report to pull in the max CPC from my AdWords sheet. So, I added three columns (campaign, ad group, max CPC) where they appear in my AdWords report:
To make the real magic happen, we’ll be using the VLOOKUP function in Excel. After you’ve created your campaign, ad group and max CPC columns, click in the first cell of your campaign column. The VLOOKUP function will match data from another table based on your leftmost column. If you’re not well versed in Excel functions, just use the Formula Builder in Excel to plug in the correct data. To get to the Formula Builder, go to the Insert menu at the top of your screen and choose Function. Double click VLOOKUP and you’ll get this box:
Basically, you’ll just need to tell Excel which cell to try to match (lookup_value), which will be the data in your first column, the range of data to use to match from, the number column of the data you want to display (in this case, campaign, which is column 3). You’ll want to use FALSE as the range_lookup, which will tell Excel to match your keyword value exactly. This is why I had you format your keywords in the same way they’re shown in AdWords.
Don’t worry if you’re not an Excel wizard. I used this help topic from Microsoft when I first started doing this, and found it to be more than adequate to get me started: VLOOKUP.
Copy your formula down to all rows in your Campaign column. To populate your Ad group and Max CPC columns, simply copy the formula in your campaign column, paste into each subsequent colomn, and then change the the column index number for each column (4 and 5 in this example). Congrats! You’ve merged your reports. Just a one more small thing to do…
Step 5 – One Last thing…
To make your new, combined report workable, you’ll just want to copy and paste your report onto a new sheet to get rid of the formulas. This will make tweaking your data possible, like using the report to do bid changes. To do this, just copy all rows, columns, create a new worksheet and paste as values (Paste Special – Values).
Just a brief note, I wouldn’t recommend trying to do this backwards by pulling data via the VLOOKUP function into your AdWords sheet data. Why? You might have keywords in AdWords that have not generated data in Analytics yet.
I hope this post will help you find the love that exists between your Analytics and AdWords reports. If you give it a try, let us know in the comments below.
Check back tomorrow for Kayla’s final post in our series, Hopelessly Devoted To Revenue!
