Tips, tricks, and pokes, just WebTrends Analytics

This question just came up on the Webtrends forum and I thought I’d share a quick way to look at individual visits, using Excel plus SDC logs.

Note that I said “using logs.”  This is the raw data that Webtrends processes when it does the aggregation that it was designed for.  If you use OnDemand, you have to ask Webtrends for the SDC logs.  There’s a fee.  If you use OnPremises, you have server logs (IIS or Apache logs) or you have SDC logs.  Somewhere.

These instructions work for SDC logs.  These instructions also assume the logs contain no more than one day of data (if more, you’ll have to change the sorting to keep the days from mingling).  And I’m also assuming your logs aren’t too huge.  Excel can choke.

So … here are instructions for a quick and dirty sorting into visits.  If anybody finds it useful, let me know and I’ll come back and add some screen shots.

It seems endless, but that’s because I’m listing every little step.  It really will take only about a minute of actual work, not counting waiting time while Excel works or the time spent reading the instructions.

1. Open Excel
2. File >> Open
3. Browse to your log file and select it
4. The Text Import Wizard will open:
   1. Choose Delimited, then Next
   2. Choose Space; leave everything else as is, then Next, then Finish
   3. Wait while Excel reads and opens the log.  If your log is too big for Excel, it will tell you so (but will load the log partially)
   4. I strongly recommend that you keep only the first few hundred thousand rows or so and delete the rest.   You’ll reduce the chances of crashing Excel.
5. Boom!  About 20 columns of stuff will appear when Excel is done.
6. Delete the first four rows; they will be #Remark, #Software, #Version, and #Date.  Keep the #Fields row.
7. Delete the cell that says “#Fields:” and shift the rest of the row to the left.  Right click on A1, choose Delete, then Shift Cells Left.  This will put the right headings over the right columns.
8. Make a new column that contains, for each row, both the IP address and the User Agent string, as follows:
   1. Go to row 1 in column S (the first empty column) and enter this formula:  “=concatenate(C1,L1)” and hit Enter.
   2. The cell should now contain this:  “c-ipcs(User-Agent)”
   3. Click on that cell to highlight it, then hover over the lower right corner of the highlighted cell where there is a very small black square.  The cursor will change to a +.  Double-click.
   4. Column S should now auto-fill downwards with a concatenation of the IP and user agent string.  Don’t worry about reading and understanding these.  The point is that these are probably (but not 100% of the time) each unique to an individual visitor, which is what you are after.
9. Highlight all columns (A-S), then open the Data tab and choose Sort.  The Sort dialog box will open.
   1. Make sure the box at the upper right is checked (“My data has headers)
   2. Click on the “Sort by” dropdown and choose the very last item, which will be “c-ipcs(User-Agent)”
   3. Click on Add Level to get another sort config line
   4. Click on the “Then by” dropdown and choose “Time”
   5. Click on OK and wait for the sorting.
10. You now have your visits, with all the hits in each visit sorted by time from first to last.
11. You’ll want to make it a little more intelligible by putting a divider line between individual visits, as follows:
    1. Highlight your block.  I do it by clicking on A1, then Ctrl-Shift-DownArrow, then Ctrl-Shift-RightArrow.
    2. Assuming you are still in the Data tab, select Subtotal
    3. The Subtotal dialog box will open.  Fill it in as follows:
       1. “At each change in:” open the dropdown and choose the very last item, which will be “c-ipcs(User-Agent)”
       2. “Use function:”  Count
       3. “Add subtotal to:” check the box for c-ipcs(User-Agent) which, again, will be at the very end
       4. Leave Replace current subtotals and Summary below data checked
       5. Click on OK and wait.  There’s a progress bar in the status line at the bottom if you feel panicky.
    4. Excel has inserted a new row between “visits” and also added some controls over in the left margin, labeled 1,2,3.  Click on 2.
    5. Excel is now showing you only the inserted rows.  The block should still be highlighted.  Don’t worry, the rest of the data is still there.  Look at the row numbers over on the left if you’re nervous; you’ll see lots of missing numbers.
    6. Click Alt-semicolon (Alt-;) and the screen will change subtly.  You’ve just selected only the visible cells.
    7. Click on the Paint Can to color all these rows, for example black
    8. Go back to the controls “1,2,3″ on the left and click on 3.
    9. Excel is now showing you all rows, with a colored divider row between “visits.”
12. At this point, I usually move everything to a new sheet to get rid of the underlying mechanics, as follows:
    1. Highlight the block
    2. Copy to Clipboard (I always just use Ctrl-C)
    3. Open a new sheet, select A1, then paste (Ctrl-V)
    4. Delete column S if you like
    5. Go back and delete the sheet you were just on

Now you’re ready to look at individual visits line by line, in the order in which they occurred in the visit.  You’ll probably want to concentrate on columns G and H, which are the URL stem and the URL query parameters for each hit.  Have fun, or good luck … whichever applies.

A few notes:

• If the same “person” had two visits, they will be jammed together in chronological order.  Keep an eye on the Time column.  If there’s more than 30 minutes between one hit and the next, Webtrends is probably seeing this activity as two visits.
• You’ll see a lot of visits with nothing in the Cookie field on the first hit.  These are first-time visits.  Don’t worry, Webtrends sorts it out just fine when it analyzes.  Using the IP-User Agent method is a quick-dirty way to get around the complexity of cookies (which are, in fact, in the Query Parameters and not in the so-called cs(Cookie) field of the logs.
• Sometimes SDC hits are virtually simultaneous, i.e. two hits happen in the same second of time.  It’s possible that Excel will sort those two hits into the wrong order (it doesn’t know any better; hh:mm:ss is all it has to work with).
• If you want to get fancy with logs, download Microsoft Log Parser and learn how to use it.

Tags

Posts that WordPress seems to think are related :)

If you’re concerned about part of your page being below the fold, why not check on your suspicions using your site data.

In other words, look for a correlation between your KPIs and the height of the window in which your site is being viewed.

This post is about how to create a custom dimension for the height of the viewing window being used by your visitor.  Note: we’re not talking about the full screen height — we’re speaking of the much more useful height of the viewable window area.

The Webtrends SDC tag collects this information in the parameter WT.bs (bs stands for something like Browserwindow Size, I suppose).  There is a small problem with WT.bs — values are recorded as a combination of browser width and height, for example “1200x700.”

If we’re interested in only the height, we need just the second part, after the “x“.  We can pull out just the height using one of the advanced features of dimension configuration that allows us to extract the part after the x.

Here’s how.

Create a new custom dimension definition.

In the General tab:

• Give it a name (Browser Window Height?  Viewport Height?)
• Give the report column a name (Height?  Browser Window Height?  Height in pixels?)

In the Based On tab:

• Value to Base On is “Query Parameter”
• Parameter Name is “WT.bs”
• Click on the “Advanced” button
• Select the Regular Expression radio button and enter this in the regex field.  This is how the part after the “x” is pulled out and separated:
  ([0-9]+)$

In the “When to Collect Data” tab:

• Choose either “first Occurrence in Visit” or “Last Occurrence in Visit” depending on your instincts.  (I use First Occurence)

Save.

There’s your custom dimension.  Use it in a report that has your favorite measures – conversions, or perhaps a measure of success in those items that you think are usually below the fold.

For best results in this kind of report, it’s probably a good idea to filter it to remove mobile devices.  Or to include only mobile devices, if that’s your interest.  Mobile users seem have different expectations about the fold and seem to be a lot more likely to scroll down.  At least, that’s what my instance of this report seems to tell me.  Removing mobile users gave me a clearer pattern.

Tags

Posts that WordPress seems to think are related :)

For Webtrends OnDemand users, this is maybe the best thing since Dashboards.

For Webtrends OnPremises users, this might remove one of your reasons for not switching to OnDemand.

So …

About a month ago, Webtrends quietly added the ability to kick off short-term rollbacks of your profiles in the UI.  If you add a new custom report or content group, or correct a filter, you get to re-analyze a bit of your data with the new settings.

And, the short-term rollbacks (up to three days) are FREE and UNLIMITED.  I am so amazed at that.  Thank you to whoever at Webtrends was smart enough to realize how very attractive that is.

This capability is called “Ad Hoc Analysis” and seemed to just suddenly appear one day as a new choice in the profile-level dropdown menus.  Personally, I would have called it “Ad Hoc Re-Analysis,” but I’m not complaining.

Here’s how it appears in the profile dropdown:

If you click on it, you’ll see up to five possible time-period rollback choices:

• Those above the red line (red line added by me) are FREE:  today (which means, yesterday’s data that would have been analyzed in the wee hours of today), yesterday (yesterday’s analysis, which covers the day before), and yesterday-plus-the-day-before.  (note the time specs:  they are UTC and you have to do a little mental time-shifting.)
• Those below the red line cost “credits”.  Every OnDemand account is given a few free credits for the year, and others can be bought.  Generally, a month of rollback costs one credit.  Technically, you can roll back to month-before-last, but for that period you have to go through technical support.
• Rollback and re-analyses start as soon as you click the “Analyze” button.  You can cancel a re-analysis any time up until it’s finished.
• After your re-analysis finishes, you can kick off another one for the same profile, if you want.
• If you enter an email address, the program will send you an email when it’s done.
• The program gives you a pretty good estimate of how long it will take.  We’ve had some go considerably faster than the estimate, and only a few have taken longer.
• Note:  While a re-analysis is happening, you can’t change anything in the configuration of the profile.

There are still a couple of, um, unpredictabilities in this very young feature.  The time period choices aren’t always consistent and they especially get a little wonky at the end of a calendar month.  The interaction with the normal analysis cycle of your profile is not predictable, or at least not understood by us Outsiders.  Once in a while one of the day-rollbacks will want to charge you a credit for the privilege, but that corrects itself eventually and it goes back to being free.  And, sometimes there’s a choice of killing all previous data, and sometimes you are not shown that choice.  (Hint – even if it’s offered, you probably don’t want to do it!)

… Why is this wonderful?

Two things:  1) testing new configurations quickly, 2) fixing screwups, especially the kinds of screwups you notice after somebody else has changed a campaign or site page and didn’t bother to tell you right away.

Regarding testing, if you are an OnDemand user you probably already know that if you add a new custom table, change a filter, add a content group, and so on, you have to wait until the next morning to see if it worked as expected.  And if it didn’t, you get another 24 hours to change it … meanwhile you have hours or days of missed analysis.  No longer.  With Ad Hoc Analysis, you can see if something worked in just an hour or two.  You’ll look more agile and smarter to your end users.

Regarding screwups, if you learn about something within a couple of days of it happening, you can correct the configuration or add the new campaign filter or whatever it is you need, and then re-analyze everything back to and before the screwup.  Or, if something spiked and you want a closer look at the spike with an extremely specific new table, you have a couple of days to get that together.  Again, you’ll look smarter to your end users, or at least you’ll avoid a few episodes of looking dumb.

Tags

Posts that WordPress seems to think are related :)

When several different people have editing privileges for configurations in an analytics tool, disaster awaits if there aren’t ground rules.

Here are some tried and true ground rules we have published among ourselves to avoid being shorthanded due to one of the analysts being in jail on grounds of assault on another analyst.

Analyst Ground Rules

1. Create a sandbox profile for each analytics team member.  Each analyst can use theirs (and ONLY theirs) to create and test new or modified configurations.
2. Team members should put their initials somewhere in the “Name” field of all custom items they create.  That means custom reports, dimensions, filters, measures, content groups, path analysis definitions, URL search & replace rules, URL rebuilding rules, templates, dashboards, or profiles.   We Outsiders happen to like having the initials at the beginning of the name for alphabetization purposes, but there are other approaches.
3. In addition, it might be a good idea to create a Category for each analyst.  Finding one’s own items then becomes easy when looking at long lists of configs – just sort the list by Category.
4. Nobody is allowed to alter anything that has another analyst’s initials, unless there is a damn good reason, some communication happens, and the reason is documented somewhere.
5. The Change Comment box that pops up whenever something is saved is not optional.  Comments should be done every time, and should be complete and clear enough to be understandable six months from now.  This is part of the ground rules among analysts, but it’s also an important practice even if you are the only analyst around.
6. Anybody on the team can, of course, freely re-use something with another analyst’s initials on it, by applying those items as-is (no changes!) to one’s own reports or profiles.  However, everybody should remember that the owner of a configuration might change it, so be prepared for that.
7. There are many out-of-the-box reports that don’t have any initials.  They appear in config lists as plain black type, no underlines or hyperlinks.  Luckily, they cannot be edited.  So, if someone wants to modify an out-of-the-box report, they’ll have to copy it, add their initials, then do their modifications.
8. There are two, exactly two, out-of-the-box items that CAN be edited.  Be ultra-careful with these:   Paths from Entry and Content Paths from Entry.  It’s possible to edit the number of steps in these.  There is only one of each allowed in all of your Webtrends installation; they cannot be cloned.   So, be aware that if somebody changes the number of steps in the one-and-only master configuration, they will be erasing all already-analyzed data in all instances of those from-entry reports in all profiles.  Get together, decide as a group on the number of steps you want in all future uses of these reports, and keep the above in mind.
9. Finally, remember the license constraints if you are using OnDemand.  Your Webtrends OnDemand license allows you a certain number of profiles and a certain number of “custom report tokens,” which are the sum of all custom reports that are assigned to the sum of all profiles.  Get to know the rules of how they are tallied and manage accordingly.

Let us know if you have improvements or alternatives.   And if you want to wing it without implementing ground rules like the above, don’t contact us to bail you out of jail!

Tags

Posts that WordPress seems to think are related :)

The Android operating system has versions.   If you’re expecting your site to look good on Android devices using browsers, you want to know how many visitors are using different versions.  But Webtrends 9 (and some other analytics products we could mention) doesn’t have an out-of-the-box report on it.

Not a problem; just make a custom dimension then use it in a custom table.  This post is a good example of a slightly advanced custom dimension that consists of a subpart of a longer string, which is a capability a lot of people don’t know about.  It’ll take about five minutes to do, since we’ve done the research for you.

The gist of it is this:  the Android Version custom dimension needs to extract the version number out of the User Agent portion of the SDC (or regular) logs.

To prep for this, we did a little homework since we needed to know what Webtrends has to work with, i.e. how Android appears in the User Agent field in logs.  It was pretty easy.  We just opened a log we had lying around and looked for mentions of “Android.”  It was obvious that the typical Android-using browser inserts something like this in the User Agent field of the SDC logs, as in the following two examples:

From these long strings, all we really care about is “Android+2.3.4″ or, to be precise, the “2.3.4″ and “4.0.3″  parts that appear after the word “Android”.

Reporting on just a portion of that big Agent string is called an “extraction” or, in Webtrends-lingo, “picking out.”  Webtrends has a couple different ways to do an extraction and they’re right there in the Custom Dimension creation window when you click the “Advanced” button.  When you click on “Advanced” you’ll see three new, kinda wordy radio buttons.

“Use Full String” is the default (it will give you the entire string above that we showed in italics).

The third radio button, using Regular Expressions to extract a substring, is powerful if you know how to write Regular Expressions.  In Regular Expression, parentheses “(  )”  are the operators for extracting.  If you don’t know Regular Expressions, find somebody who does — they tend to like puzzles and this happens to be a really easy one where they can look brilliant without spending a lot of effort.

Here are the individual steps to make the custom dimension:

1.  Go to Administration > Report Configuration > Custom Reports > Dimensions and click on “New”

2. Give it a name, click on Next and choose “Agent” from the dropdown for the “Value to Base On” window.  Then click the “Advanced” button (arrow).

3.  The “Advanced” button will refresh the screen and show three radio button choices:  “Use Full String,” “Fixed Pattern”, and “Regular Expression.”  Select “Regular Expression” and enter:

 Android ([0-9]+\.[0-9]+\.[0-9]+)

4.  Save and close.

5.  Apply it to a new Custom Report with your favorite measures, and you’ll get something like this.  (Don’t forget to add it to a Template so your users can find it easily).

Tags

Posts that WordPress seems to think are related :)