Excel Tutorial Series (Part 1): Manipulating Tracking Codes

Jan 29 By ThriveTracker

Update: Since POF added new reporting formats, you must be sure to use the old reporting format, which we use in the tutorial. After going to the “Run Report” page, you must click on “Click here to see previous reporting formats.” Then, run the “Daily Creative Delivery” report to get the same report we use in the video.

Excel is an extremely powerful tool to quickly analyze performance data.  Anything that you can export as a report can be analyzed using Excel.  While it’s not able to handle massive amounts of data (hundreds of thousands of entries), it can quickly generate an analysis report using several basic functions.  You do not have to learn any programming.

I am going to post a 3-part Excel tutorial video series, starting with Part 1 today.  Over the next couple of days the other parts will be posted.  The tutorials will go over the basic functions you need to build a model that analyzes your ads.  At the end of the tutorial, you would be left with a primitive version of iPyxel’s Campaign Viewer.  You should have already received this tool when you subscribed to our mailing list, so the purpose of the tutorials isn’t to create something similar for your use but rather for you to learn the basic Excel functions that you can apply to any data analysis you want to do.

To accompany the tutorials, I have also prepared for you the workbook that I used in the video so you can follow along.  The first two tabs in blue are example raw reports that you can practice with.  The tabs in green are the completed tabs that you can refer to should you get stuck.  Please don’t hesitate to ask any Excel question you may have in the comments section.

Download the Follow Along Workbook Now | 87.9 KB

Mastered Part 1? Go to Part 2 now!

Never miss a feature, product launch, or exclusive offer



    22 Comments

  • Hey Tom,

    that’s pretty amazing how you can manipulate the data that fast. Now I am using a german version of Excel and obviously, the function isn’t named as in the US version. Do you know if there is some kind of “function translator” so I find out what “mid” is called in my german version?

    Can’t wait for part II + III 🙂

  • Hey, Great stuff. Thanks for the tutorials.

    I have one problem – When I download the daily creative delivery it is downloaded as a csw file and when I open it in excel it’s all messed up. How can I change the csw to an excel file?

  • Hey Tom, finally watched these tutorials and they’re awesome!  I was noticing you use a lot of keyboard shortcuts for autofilling, coloring, etc… Would you mind sharing what those are?  I’m using the buttons along the top and it’s pretty slow!  Thanks

  • This may have something to do with your Excel being in international mode where the commas are treated differently.  You may need to change this to suit the standards to recognize .csv’s.  May need to do Google a bit on that.

  • Are you referring to regular shortcuts?  To initiate shortcuts, hit Alt.  Then you can see in the ribbon area what each key initiates which function.  For example, Alt + H + O + I will fit the column width to the selected contents.

    You can also add your own shortcuts to the toolbar above the button.  If you go into Excel Options > Quick Access Toolbar, you can move commands from the left column to the right column.  Everything in the right column will be available in a toolbar above the ribbon.  And you can access them by using Alt + 1 (or 2, or 3, etc…).

  • Hey Tom. In your video, the daily report you get from POF shows a column for creative ID and for creative name. How do you produce this?

    I named my creatives by their title when uploading them in POF, like you mentioned. However when the creative report is produced the titles of my creatives appear in the creative ID column and there is no creative name column produced at all?

    Cheers

  • I’m not sure why you are having that problem. The report I get from POF has the creative_name column in it. So, if you have entered creative names for your creatives, you should have that column as well. My only guess would be somehow the .csv wasn’t converted properly when opened in Excel. You may want to try another converter like http://www.zamzar.com/

  • Thanks Tom, but do you think it may have something to do with what i’m doing on pof? I say this because when i do the following in POF

    1.select “run a report”

    2. (create custom reports section)

    Data =creatives

    Metrics =impressions, clicks and cost

    Interval =date

    3. pick campaign

    4. Press “go”

    I get in their dashboard the headings:

    day – campaign/ads – creativeID – creative picture – Impression – Click – Cost

    In the “creative id column are the copy titles NOT the campaign IDs and ALSO there is NO campaign name column at all.

    This i presume is why it is not working in excel also.

    any ideas?

    many thanks

  • Ok, you’re pulling the wrong report.

    You have to go to “Click here to see previous reporting formats.”

    Then run the “Daily Creative Delivery” report.

    Export that report.

  • Hey Tom. When you refer to exporting the cpv lab “target performance report” Have you created your own template in cpvlab to upload report files or are you simply pulling a report off “current stats on site”

    cheers
    BB

  • To be honest, we don’t use CPVLab anymore and haven’t for a while, but it wasn’t a custom report. I would look for a report that matches the columns listed in the spreadsheet.

  • I was meaning for the POF basic campaign viewer tool. In the viewer the CPV lab page only has ID in the top left corner and no other columns. Would you dump a report form cpvlab ( created from hard data) into this section?

    Just that clicks don’t seem to be passing through when I do this?

    Cheers
    BB

    • Leave a Reply

    Your email address will not be published.