If you’re just join­ing us now, be sure to read through my first post on set­ting up a sim­ple track­ing solu­tion for dis­play adver­tis­ing. We’ll wait.

Mov­ing on. Assum­ing you’ve worked your way through Part 1, we now need to clas­sify all these track­ing codes that are show­ing up in your report­ing because the cam­paign has been so successful.

Setting up classifications for future SAINT uploadsIn the pre­vi­ous post, we focused on three macros/tokens – site, place­ment and cre­ative. We now need to set up Con­ver­sion Clas­si­fi­ca­tions for each within the Site­Cat­a­lyst Admin. The fol­low­ing screen­shot shows the clas­si­fi­ca­tions for Cam­paigns, but the same idea holds true if you hap­pen to be using Mar­ket­ing Chan­nels Detail report­ing instead. Now that you have your clas­si­fi­ca­tions set up, go ahead and down­load your cam­paign data set via SAINT.

The next step may require a bit of cajol­ing on your part. You’ll need either access to your company’s or agency’s ad serv­ing plat­form (DFA, Medi­a­Mind, etc.). Alter­na­tively, you may ask them to send you an ad serv­ing report every so often. Offers of cof­fee and donuts have been known to help with these sorts of requests.

Since our exam­ple is using DFA, we’ll want to pull the fol­low­ing out of DFA and into a CSV file: Site, Place­ment and Cre­ative (these are our friendly names) and SiteID, Place­men­tID, Cre­ativeID (these are our numer­i­cal macro val­ues). Notice how each macro value has a cor­re­spond­ing friendly name.

Raw DFA dataThere are likely count­less ways to match the track­ing codes found in your SAINT file with the data set present above, but what I’ve always found the eas­i­est and quick­est way to get the job done is to use a com­bi­na­tion Excel’s CONCATENATE func­tion and the always use­ful VLOOKUP function.

  1. First using CONCATENATE, con­struct the track­ing code on the DFA sheet.
    • =CONCATENATE(“DFA:”,D10,”:”,E11,”:”,F10)
    • Based on the screen­shot above, we should get DFA:935117:75541764:45376751 as a result (look familiar?)
    • Copy these results over into Col­umn A. We’ll need it there for the next step.
  2. Now pull up your SAINT file and use the VLOOKUP func­tion to match what’s found in your Key (Col­umn A) with the data table you cre­ated in step one.
    • For exam­ple =VLOOKUP(A10,B10:E12,2,FALSE) – of course your exact for­mula will depend on where your lookup table is located and which col­umn you wish to ref­er­ence. Always VLOOKUP responsibly.
  3. Save & Upload to SAINT at Admin > SAINT Clas­si­fi­ca­tions > Import File.

When all is said and done, you should be able to break down your dis­play cam­paigns by Site, Place­ment and Creative.

Post SAINT Classification of DART data setBut wait, there’s more! You now have a com­mon key to use between Site­Cat­a­lyst and DFA data sets. A lit­tle Excel wiz­ardry will let you merge ad serv­ing data (impres­sions, clicks, view-throughs, etc.) with all your Site­Cat­a­lyst data. Maybe that’s a topic for another post.

Let the report­ing and analy­sis begin and good luck! Leave you favorite tips and tricks for work­ing with ad serv­ing files and macros/tokens in the comments.

Tip — When set­ting up clas­si­fi­ca­tions for some­thing spe­cific, like dis­play ads, use “DFA” within the clas­si­fi­ca­tion name so that there’s no con­fu­sion about what this is for in the future.