I’m a big Chartboost fan. Getting to grips with all of the options available to a developer is a bit of a learning curve. The good news is their team are always on hand to help you. Chartboost allows app developers to control what ads get shown in their apps, to improve their app eCPMs. I wanted to find what ads that were showing most in my apps, but which were not getting installs or earning me much money. Not all ads will appeal to the demographic that plays your particular game.

HUGE thanks to Shannon in Chartboost for helping me to figure out how to do this 

To get started, log into Chartboost, browse to campaign>analytics and type in the name of the campaign you want to analyse. Put in dates over the last week, and select ‘group by app’. The system will then generate results which are too large to be displayed in the web brower, and you will be prompted to download a CSV file.

Open this CSV file up in Excel. Now select the whole spreadsheet, and press ‘Ctrl+F’. Find all of the ‘$’ and replace them with a blank. This is a really important step as otherwise your money earned column will not display correctly later on.

Now create a pivot table.

Select all the content in the spreadsheet, and then (in Office 2010 for PC) you can create a pivot table by selecting “insert > pivot table” from the top menu area.

When the grey popup box appears, be sure and select the ‘new worksheet’ option.

Now that you have your pivot table created in a new sheet. You should revise the parameters to show Row labels: “App Name” and Values: “Impressions”, “Click”, “Installs”, and “Money earned”. You can select these in the pivot table on the right of the screen.

Go into the ‘row labels’ column, so drag them as needed to the ‘values’ column.

Then you need to change update the ‘impressions, clicks, installs, money earned’ to the SUM of. You can do this as in this screenshot, for each of the 4 columns.

So select ‘sum’ instead of ‘count’ for these 3 columns.

Highlight the ‘impressions’ column and click on the filter button in the top menu.

Now sort the ‘sum of impressions’ from largest to smallest. Then copy all of the data to a new sheet. This new sheet is where you will calculate the CTR, IR, and eCPM.

CTR= clicks/impressions
IR= installs/clicks
eCPM= (money earned/impressions)*1000

Below is a spreadsheet with these calculations put in. The cells highlighted in red represent ads that have been shown quite a lot in my app, which I’ve earned no money for. These would be examples of ads that could be removed from my ad campaign as they do not seem to be fitting it with my target audience. (Obviously this will be different for every app).

Have fun!

UPDATE: Having played around with campaigns over the past 10 days or so, by deleting non-performing ads I’ve managed to nearly double my eCPM. I also seem to have halved (or thereabouts) my impressions. When you start deleting out ads (I’m guessing which perhaps were global ad campaigns) it look like some countries might be seeing no ads in my apps. So instead of a low ecpm rate in those countries (and low revenue for me) I was getting 0 ecpm.

So I think unless you have someone fulltime or thereabouts looking at all this stuff AND can track it by country to make sure you are displaying the best ads you can everywhere, this filtering can be useful but only on a much smaller scale. E.g. take out ‘Top Girl’ ads in a racing game for guys. So I’m limiting the app filtering to a small handful of apps max for now.