Blog

In Part 1 of Tracking Paid Search in Omniture SiteCatalyst we covered the first 2 steps, setting up Success Events and Data Sources in the Omniture Admin. Let’s jump in

Step 3: Export Data

In Step 3 we’ll export data from your PPC account. This works for MSN, Yahoo! and of coarse, Google AdWords. But you aren’t limited to those. As long as you can export something containing your tracking code, impressions, clicks and total cost, you can import them into Omniture for Paid Search ROI tracking.

For our example, we will use AdWords ppc data as our sample.

1. Login to your AdWords Account and navigate to the reports area
2. Click “Create a New Report»”
3. Select the Ad Performance Radio Button
4. Change the date range to yesterday
5. Click Add or Remove Columns
6. Under Level of Detail, we only need Ad ID
7. Under attributes we need Destination URL
8. Under Performance Statistics we need Impressions, Clicks and Cost
9. Filter your Results and select Ad Status :: Active
10. If you like, save your report as a template in AdWords
11. Once your report is completed, export a .csv and open in Excel

Our exported file should contain 5 columns. Now it’s time to do a little excel magic to get it ready. We’ll start by deleting the first column, Ad ID. Google AdWords requires you export this, but we don’t need it. Click the A over the first column, which will select the whole column. Then click Edit in the main menu and then Delete.

Now, we have 6 extra rows on top so we will select those by clicking the 1-6 on the left. Again choose Edit and Delete from the main menu.

Isolating our tracking code is next. We are going to do this using Excels Text to Columns feature. First step is to move the data from Column A to Column E. Select the A above the first column and select CRTL+C on your keyboard. Next put your cursor in cell E1 and select CTRL+V. In the main menu there is a Data option. Select that then select Text to Columns. A wizard should pop up to walk you thru this process. On step 1 make sure delimited is selected and then click Next>. During Step 2 we will tell Excel what the delimiter is. Yours should be either ? or & depending on how you’ve created your URLs. (To ease the pain of this process, I recommend you make your tracking code variable the first one following your page name. For example: http://www.domainname.com/index.htm?trackingcodename=trackingcode&variable2=222&variable3=333) Check the Other option and enter a ? into the text field. Click Next> and then preview your data. If you see your trackingcodename=trackingcode in one of the columns we have success and click finish.

If you have additional variables, you will need to repeat this step again using & as the delimiter. Once you have a column that contains trackingcodename=trackingcode you are ready for your final Text to Columns operation. This time, use = as your delimiter.

Let’s delete all the columns except your tracking codes, impressions, clicks and cost and organize your columns in the following order: Tracking code :: Impressions :: Clicks :: Cost. Once you have them in order, add a new column in beginning so we can add our date formatted as MM/DD/YYYY

Now your data is ready, let’s add it to the template we downloaded in Step 2. Save and close your template. We will upload this template via FTP to Omniture. Once it is complete, we will need to upload another file to tell Omniture we are done. If your original upload is named datasources.txt, the blank file we’ll upload should be datasources.fin

Omniture does not guarantee the processing time on data source uploads. In my experience, they generally appear in 30 minutes.

Step 4: Calculated Metrics

Let’s face it, Impressions, Clicks and Total Cost are interesting, but what are you going to DO with that information? With SiteCatalyst we can add in Revenue… so that’s adds a little more data. But we want to make some decisions here and we don’t want to look at 15 different reports to do so. In Step 4, I’ll teach you to create Calculated Metrics in Omniture. This is a GREAT feature they offer but I rarely see it used. For this exercise, we will create metrics that can be added to your reports that will be very helpful when making PPC decisions.

• Page Views/Visit
• Average Order Value (AOV) – $
• Revenue/Visit
• Click Thru Rate (CTR) – %
• Profit – $
• Profit per Impression (PPI) – $
• ROAS – %

Let’s go back to Admin :: Report Suites as we did in Step 1. We’ll select our report suite and then select Edit Settings :: General :: Calculated Metrics

Calculated Metrics

It’s time to do some MATH!!!

We’ll need to reference the success events we created for this. In our example we have the following:

Event15 = Clicks
Event16 = Impressions
Event17 = Total Cost

In our formulas Event15 will be Custom15. When setting up the formulas select currency or percent depending on the metric

Here are the formulas:

Page Views/Visit: [Page Views] / [Visits]
Average Order Value (AOV): [Revenue] / [Orders]
Revenue/Visit: [Revenue] / [Visits]
Click Thru Rate (CTR): [Custom15] / [Custom16]
Profit: [Revenue] – [Custom17]
Profit per Impression (PPI): ([Revenue] – [Custom17]) / [Custom16]
ROAS: (([Revenue] – [Custom 17] )/ [Custom 17])+1

Step 5: Run Report

In Step 5 we’ll put it altogether and add the calculate metrics to your PPC report and drill down into a campaign to view performance and get an “at your fingertips” view of how each Ad Group is performing.

Go to SiteCatalyst and select the report suite you have imported Data Sources to. Select Campaigns :: Tracking Codes :: Tracking Codes to view your data. Select your date range and then click Add Metrics.

Add Metrics

A pop up will appear. You can find your new data source metrics, Clicks, Impressions and Total Costs, in the standard Metric type menu. Click and drag each of them to the right had side of the screen. Then select Calculated Metrics from the Metric Type menu and report for your calculated metrics.

Click Save and your report is ready for review.

Happy Optimizing!