Newsletters
 |
Sawmill Newsletter April 15, 2007 |
Welcome to the Sawmill Newsletter!
You're receiving this newsletter because during the downloading or purchase of Sawmill, you checked the box to join our mailing list. If you wish to be removed from this list, please send an email, with the subject line of "UNSUBSCRIBE" to newsletter@sawmill.net .
News
This issue of the Sawmill Newsletter explores using Sawmill to report the click-through ratio (CTR) in standard Sawmill reports using a relatively new feature in Sawmill, calculated report columns. The CTR is used industry-wide to calculate the relative effectiveness of an advertising campaign, including pay-per-click campaigns. In the Tips & Techniques section below, you'll find a detailed description of how to add a CTR column to a Sawmill report.
We are currently shipping Sawmill 7.2.9. You can get it from http://sawmill.net/download.html .
Get the Most out of Sawmill with Professional Services
Looking to get more out of your statistics from Sawmill? Running short on time, but need the information now to make critical business decisions? Our Professional Service Experts are available for just this situation and many others. We will assist in the initial installation of Sawmill using best practices; work with you to integrate and configure Sawmill to generate reports in the shortest possible time. We will tailor Sawmill to your environment, create a customized solution, be sensitive to your requirements and stay focused on what your business needs are. We will show you areas of Sawmill you may not even be aware of, demonstrating these methods will provide you with many streamlined methods to get you the information more quickly. Often you'll find that Sawmill's deep analysis can even provide you with information you've been after but never knew how to reach, or possibly never realized was readily available in reports. Sawmill is an extremely powerful tool for your business, and most users only exercise a fraction of this power. That's where our experts really can make the difference. Our Sawmill experts have many years of experience with Sawmill and with a large cross section of devices and business sectors. Our promise is to very quickly come up with a cost effective solution that fits your business, and greatly expand your ROI with only a few hours of fee based Sawmill Professional Services. For more information, a quote, or to speak directly with a Professional services expert contact consulting@flowerfire.com.
Tips & Techniques: Adding Calculated Columns to a Report
Important: Calculated Columns are available only in Sawmill 7.2.6 and later.
Suppose we have a report like this:
This report shows, for each advertisement:
1. The number of impressions for the advertisement (the number of times the ad was displayed). This can be calculated using a Log Filter, from the number of hits on an ad image, assuming you're serving the image yourself.
2. The number of clicks on the advertisement. This can be calculated using a Log Filter, by counting the number of hits on the target page of the ad.
3. The cost of the ad. This can be calculated using a Log Filter, looking up the cost per impression, or per click, in a CFG map.
4. The number of unique users seeing the ad.
All this can be done with Sawmill, using custom numerical fields and CFG maps. We previously described custom fields in the February 15, 2007 newsletter, and CFG maps in the December 15, 2006 newsletter.
But now, suppose we want to know the click-through ratio (CTR) for each campaign. We could do this by exporting this table to a spreadsheet, and adding an extra column with an equation like: 100 * Clicks / Impressions. But that would require a separate step, so we'll add this column, and do this calculation, in Sawmill. What we'd really like is to have this column in the Sawmill report, like this:
The Click-through Ratio (CTR) can be computed by dividing the number of clicks by the number of impressions, and multiplying by 100. However, this cannot be done with a custom database field. Custom database fields are useful when the value of the field can be computed from other fields in the same log entry. However, the click-through ratio must be computed from the total number of clicks and the total number of impressions, which are not available until the entire log has been analyzed and aggregated, so the CTR cannot be computed using log filters, which are executed during log processing.
For this type of calculation, Sawmill provides a Calculated Column feature, where a column can be calculated at report generation time, from other columns and rows of the table it is in. This is analogous to the way a spreadsheet program can compute a cell from other cells in the spreadsheet. In Sawmill, this is done by adding a new database field, with an "expression" parameter that specifies a Sawmill Language (Salang) expression, which calculates the value of the cell; then that database field is added to the report table.
Creating a New Database Field with an Expression
First, using a text editor, edit the profile CFG file, which is in LogAnalysisInfo/profiles. Search for "database = {", then search for "fields = {", to find the database fields group. Then, add this field, as the last field in the group:
ctr = { label = "CTR" type = "string" log_field = "ctr" display_format_type = "string" expression = `((1.0 * cell_by_name(row_number, 'Clicks')) / cell_by_name(row_number, 'Impressions')) * 100.0` } # ctr |
This creates a field whose value is computed from the table that contains it. This is done by adding an "expression" parameter whose value is a Salang expression:
((1.0 * cell_by_name(row_number, 'Clicks')) / cell_by_name(row_number, 'Impressions')) * 100.0
This expression is somewhat complex, so let's break it down a bit:
row_number : This is a special variable which means "the current row number." So when computing a cell value in row 5, this will be 5.
cell_by_name(row_number, 'Clicks') : This gets the value of the Clicks field, in the row row_number (i.e., in the current row).
cell_by_name(row_number, 'Impressions') : This gets the value of the Impressions field, in the row row_number (i.e., in the current row).
The use of 1.0 and 100.0 force the number to be a floating point number, so it includes the fractional part (otherwise, it will be an integer).
These functions are documented in detail in The Configuration Language, in the Technical Manual.
Adding the Column to the Report
Now that we've defined the field, we need to add it to the report. To do this, search from the top of the CFG file for "statistics = {", then for "reports = {", then find the report element you want to add the column to. Within that report element, add a new CTR column to the "columns" group:
ctr = { data_type = "unique" display_format_type = "%0.2f%%" field_name = "ctr" header_label = "CTR" show_bar_column = "false" show_graph = "true" show_number_column = "true" show_percent_column = "false" type = "number" visible = "true" } # ctr |
Generating the Report
Finally, rebuild the database, and the Campaigns report will now show the CTR column.
Questions or suggestions? Contact support@sawmill.net . If would you like a Sawmill Professional Services expert to implement this, or another customization, contact consulting@sawmill.net .
[Article revision v2.2]
[ClientID: ]