If you’re just joining us now, be sure to read through my first post on setting up a simple tracking solution for display advertising. We’ll wait.
Moving on. Assuming you’ve worked your way through Part 1, we now need to classify all these tracking codes that are showing up in your reporting because the campaign has been so successful.
In the previous post, we focused on three macros/tokens – site, placement and creative. We now need to set up Conversion Classifications for each within the SiteCatalyst Admin. The following screenshot shows the classifications for Campaigns, but the same idea holds true if you happen to be using Marketing Channels Detail reporting instead. Now that you have your classifications set up, go ahead and download your campaign data set via SAINT.
The next step may require a bit of cajoling on your part. You’ll need either access to your company’s or agency’s ad serving platform (DFA, MediaMind, etc.). Alternatively, you may ask them to send you an ad serving report every so often. Offers of coffee and donuts have been known to help with these sorts of requests.
Since our example is using DFA, we’ll want to pull the following out of DFA and into a CSV file: Site, Placement and Creative (these are our friendly names) and SiteID, PlacementID, CreativeID (these are our numerical macro values). Notice how each macro value has a corresponding friendly name.
There are likely countless ways to match the tracking codes found in your SAINT file with the data set present above, but what I’ve always found the easiest and quickest way to get the job done is to use a combination Excel’s CONCATENATE function and the always useful VLOOKUP function.
- First using CONCATENATE, construct the tracking code on the DFA sheet.
- Based on the screenshot above, we should get DFA:935117:75541764:45376751 as a result (look familiar?)
- Copy these results over into Column A. We’ll need it there for the next step.
- Now pull up your SAINT file and use the VLOOKUP function to match what’s found in your Key (Column A) with the data table you created in step one.
- For example =VLOOKUP(A10,B10:E12,2,FALSE) – of course your exact formula will depend on where your lookup table is located and which column you wish to reference. Always VLOOKUP responsibly.
- Save & Upload to SAINT at Admin > SAINT Classifications > Import File.
When all is said and done, you should be able to break down your display campaigns by Site, Placement and Creative.
But wait, there’s more! You now have a common key to use between SiteCatalyst and DFA data sets. A little Excel wizardry will let you merge ad serving data (impressions, clicks, view-throughs, etc.) with all your SiteCatalyst data. Maybe that’s a topic for another post.
Let the reporting and analysis begin and good luck! Leave you favorite tips and tricks for working with ad serving files and macros/tokens in the comments.
Tip – When setting up classifications for something specific, like display ads, use “DFA” within the classification name so that there’s no confusion about what this is for in the future.