Analyse und Reporting
für Web | Netzwerk | Sicherheit
Zugriffs- und Datenanalyse von Server-Logs (Proxy, Mailserver, Firewall, Webserver) und Überwachung der Sicherheit & Performance, Schwachstellenanalyse.
Sawmill Analytics 8 | Loganalyse
Sawmill-Tutorial
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.
Professionelle Dienstleistungen
Sollten Sie die Anpassung von Sawmill Analytics nicht selbst vornehmen wollen, können wir Ihnen dies als Dienstleisung anbieten. Unsere Experten setzen sich gerne mit Ihnen in Verbindung, um die Reports oder sonstige Aspekte von Sawmill Analytics an Ihre Gegebenheiten und Wünsche anzupassen. KontaktZur Tutorial-Übersicht