If you're new here, you may want to subscribe to my RSS feed. Thanks for visiting!

Note: This article is a bit more advanced than some I have written. However, the technique is very easy to take advantage of, provided that you have knowledge of desktop database applications and/or SQL.

If you’ve been promoting ClickBank products for any length of time, you have most likely had some frustration with their stat reports. They don’t offer much customization, and if you are promoting more than one product, it becomes nearly impossible to get the data you are looking for.

There are programs that attempt to solve this problem (CBAccountant or CBStatz to name a few), but they also have disadvantages. First, they are pretty expensive for the average user. CBAccountant is $97, but can be bought for less with ClickBank discounts, while CBStatz is a whopping $147. Second, the developers don’t know exactly what kind of reports you need, and don’t allow for completely custom reports. Also worth mentioning is that, when I tried them out, they did not feel like high-quality, professional software.

The Scenario

I would assume that the type of reports I create are common among many other ClickBank affiliates. I like to generate total daily revenue reports by markets so that I can compare this to my PPC costs and see my total daily profits for a particular niche. ClickBank’s interface does not aggregate daily sales (unless you just want total daily sales across all products for the previous 15 days) and the previously mentioned programs only let you see daily sales for one product.

Lets say I have built a niche website about “learn piano” products. On that website, there are 5 different ClickBank ebooks about learning piano, giving reviews and recommendations for each of them. After making a handful of sales, I want to see how much revenue was generated each day so that it can be matched up against the PPC spending. Here is how you can do this for free (and create any other type of report you could possibly imagine).

Step 1: Download Account Data (CSV) From ClickBank

The first thing you need to do is download your account data from ClickBank. Packaged stat programs do this for you, but it actually isn’t that difficult. What I like to do is, rather than download all the account data, just get the data for the date period I am analyzing.

You do this by logging into your ClickBank account, clicking the “reporting” tab, and then clicking the “transactions” link. Simply fill in a date range, leaving everything else as is, and click the “Go” button. After the page reloads and your data is displayed, scroll down the the bottom, click the “Export Options: CSV” link, and save the file to your hard drive.

This CSV (comma separated values) file is a standard format that can be read by many different programs, including spreadsheet and database applications. We are now going to import our data into any desktop database software such as Microsoft Access or OpenOffice Base.

Step 2: Import Account Data (CSV) Into A Database

Now that you have your account data downloaded and in CSV format, you will need to import it into a database. It would take a long time for me to explain this process step by step, but both Microsoft Access and OpenOffice Base make this pretty simple, so I will just leave it up to you

Here are some “hints” to help you along the way:

1. You are importing a text CSV file
2. The data is delimited (comma separated)
3. The first row contains the field names
4. “Date” field might need to be set as a “text” field
5. “Txn Type” field should be renamed to remove spaces
6. Let the database application automatically add a primary key

After you have done this process once, you will be able to do it again in your sleep. Access 2007 also allows you to save the import steps so that they can be repeated automatically for similar CSV files in the future.

Step 3: Creating Custom Queries

This is the fun part. With your account data loaded into a table, you can begin to create custom queries and reports of the data. You can try to use the query wizards, but it is much easier if you just write the SQL on your own (assuming you know SQL).

Here is the query I use to get a list of daily totals for a group of products:

SELECT Date, SUM(Amount) AS Total
FROM TableName
WHERE (Publisher='PRODUCT1' OR Publisher='PRODUCT2' OR Publisher='PRODUCT3') AND (TxnType='Sale')
GROUP BY Date ORDER BY Date

That is just one example of a query that I use, but there is an unlimited amount of others that can be created. You can set up your database so that it will automatically import new data, and you can save all of your custom queries for repeated use. Over time, you will have a robust system of queries and reports that are more powerful, more useful, and more versatile than any premade application on the market today.

Conclusion

This post was not meant to teach you how to use Microsoft Access or how to write SQL queries (those topics take up entire books on their own). It was meant to show you that you don’t necessarily need to buy a piece of software to track your ClickBank stats. Once you get this process down, you will probably prefer it to those dedicated programs.

You don’t have to limit yourself to ClickBank either. Because CSV is a standard file format that can be downloaded from pretty much any online account (affiliate networks, bank accounts, ppc accounts, etc), you can use this technique to create custom reports for nearly anything. It doesn’t make sense to do this for an interface that already provides sufficient customization, but can be a huge time-saver if you find yourself in a situation where you can’t get the data you need (like with ClickBank).

I use this method because the premade CB stats applications are too cumbersome for me and don’t give me the exact reports that I need. If you are currently using one of those programs with success, good for you, but if you are stuck manually calculating your ClickBank stats, try making your own system with a database and a few SQL queries