Collapse thousands of report rows into a few buckets with the lookup table capability.
Sometimes there are too many rows in a dimension and you wish you could collapse them into groups. As an example, the Cool Report in our post on Browser Window Height produces as many as 20,000 rows. That’s one row for each possible browser window pixel height. The only way to make sense of it is to dump it into Excel and subtotal.
But — you can define your own groupings for a dimension and have Webtrends do the subtotaling.
It can make the difference between this loooong list:
and this short summarized one:
All you have to do is take the list of possible values and create a lookup file that Webtrends will use to dump each original value into a bucket. Each bucket can contain as many of the original values as you want (well, there are limits, but they are big ones, see below).
The point is that the bucket names, not the original values, will appear in the report.
The lookup table can be a text file or an Excel csv. In either one, each original value gets one line. In the examples below, the original values are 1, 2, 478, and 479. The bucket names are 1-100 and 401-500.
For a text file, put a comma between the original value and what you want the bucket name to be, like this:
In the above, values 1 and 2 get put into the bucket called “1-100” and values 478 and 479 will appear in the row labeled “401-500.”
For an Excel csv file, put the original value in column A and the bucket name in column B, like this:
A few important details:
- Note that although our values 1, 2, 478 and 479 are technically numeric, they are being treated like text. And, of course, this is a dumb little example. If you were really trying to get 20,000 different browser height values into buckets, you’d have to produce a file of 20,000 lines. Yeah, I know, inconvenience. That’s why they call it “W-O-R-K”!
- Be careful about commas — the divider comma should be the only comma in the line
- Use a header if you want so later you’ll know what you did. Format it exactly the same way as individual lines. In our example, the header could be “Browser window height, Browser height bucket”
- Characters must be ASCII … not extended ASCII, but regular strict ASCII
- It’s a good idea to have a blank line at the end of text translation files. This isn’t necessary with an Excel csv file
- Wildcards and regular expressions don’t work. Sadly.
- Express Analysis and RealTime will not execute these translations.
- The saved text file should be ASCII, i.e. “UTF-8 without BOM (Byte Order Mark)”. Notepad and Textpad work great for making these files, and you can leverage Excel to create the text files if you copy from Excel and paste into Notepad or Textpad. You can also save Word files as ASCII, but I strongly suggest you peek at the saved file with Notepad or Textpad before deciding you’re done.
- Although our example is a simple two-column one, you can have up to twenty-six columns in all. When you set up a custom dimension, you get to tell Webtrends which of the 25 “translated” columns you want to use in that report. There is no limit to the number of rows, as long as your file is not larger than two gigabytes. That’s a lot of rows.
- Once your file is live, you can use WinSCP or other FTP-related programs to change the content of the files, i.e. add new rows or even replace all the contents.
Once you have your file created, put it where Webtrends can find it
- For On Premises, you’ll have access to the server so just create a location for it. I have a folder called “translation files” in the /datfiles folder. Since your installation is yours and mine is mine, check your own installation. A good starter URL common to many Webtrends installations is <drive:>\Program Files\Webtrends\storage\config\wtm_wtx\datfiles\<filename>
- For On Demand, use WinSCP to put it in an SFTP folder. (Get an SFTP login from Webtrends tech support.) The File protocol is SFTP, the Host Name is sftp.webtrends.com, Port number is 22. You should see, or can create, a “Lookup Tables” folder once you have signed on. Put the file there.
Next, tell Webtrends where this file is.
- In the Admin Console, go to Web Analysis > Report Configuration > Custom Reports > Lookup Tables.
- Create a new entry in the Lookup Tables list.
- For On Premises, give it a name, use type “file” and enter the address of the file (for example, .\wtm_wtx\translation files\filename.ext
- For On Demand, give it a name, specify the delimiter (“comma” in our simple example), and Save.
- The next step for On Demand is: contact tech support (phone at 503 223 3023, or enter a support ticket in your support portal). Tech support has to be involved to get the final connection to happen. You’ll need to give them the exact name and location of the file. They’ll put in a request to the back room people and within a few hours (we hope) the connection will be final. In addition to getting an email about it, you’ll know by looking at the “Source” column in the Lookup Tables list, which will no longer be empty.
Next, turn the lookup into a custom dimension.
- In the Admin Console, go to Web Analysis > Report Configuration > Custom Reports > Dimensions and create a new dimension, giving it a name and so on. When I have a translated dimension, I make sure the new dimension’s name says so.
- In the “Based On” screen, specify the original dimension, before translation, as if you were going to produce an unbucketed report.
- Click on the Advanced button
- At the bottom of the Advanced section, there’s a place for “Translate substring retrieved above.” It’s not the clearest language in the world, but that’s where you should be. Check the box, then choose your table from the dropdown list. The Key Column should be A and the Value Column should be B in our simple 2-column example. (If your table doesn’t show up in the dropdown, go back to the previous step “Tell Webtrends where the file is.”)
Finally, use the custom dimension in a report.
If you run into snags, there are several WebTrends Knowledge Base articles that can help you. Go to the Knowledge Base (http://webtrends.com/support/knowledgebase) and enter “translation file” in the search box.
Here’s a sneaky way to stay ahead of the curve so you can get these to happen immediately when you need them. Create a few dummy lookup tables and go through the process of activating them. Don’t use them until you need them. When you want lookup capability in a hurry, without waiting for Tech Support to do the activation thing, just edit one of the dummy files and replace the dummy content. I don’t know if you can rename the file – never tried it.