Reports That Contain Only Natural Search Traffic

Make custom reports that show only natural search traffic, using a couple of basic custom filters.

On Twitter and elsewhere this week, we saw a couple people getting all excited about being able to isolate natural search traffic in their reports.  So we thought we’d review the mechanics of one kind of basic but cool custom WebTrends filter setup that shows you your natural search traffic.  With, of course, any dimension you want … such as the content visited by natural search visitors.

Okay advanced WebTrends people, stop yawning.  You’re excused from the room.  This just happens to be an eye-opener for some very important people – those who are learning WebTrends.

Basically, it’s this.  If you are able to isolate your paid search traffic, you can also isolate your natural search traffic. It’s that simple.  Make a report that contains only search traffic.  Apply a filter that removes paid search traffic.  There you are.

This all works because WebTrends has its own “filter” for search traffic.  The built-in filter makes use of an extensive, continuously updated list of search engines’ domain names.  The filter also has ways of checking that each domain was actually being used for search at the time of the click.  For example, rr.com has a home page and a search function.  WebTrends’ internal checks make sure that only rr.com’s search traffic gets into search reports, and visits coming from links on their content pages don’t.

In other words, you can filter in all search engine traffic with just a couple clicks, by choosing from a drop-down menu when creating the filter.  That’s the first filter we describe below, and you can create it yourself very quickly.

Being able to report on just natural search traffic is nice for reports like:

  • Search terms
  • First time versus return visitors
  • Size of a visit
  • Presence of a key page
  • Content Groups
  • Pages
  • Referrers

Of course, this all assumes you do have a way to clearly and definitely know that a given visit is from paid search.  You should be taking care of this already if you have a paid search program. All your paid search visits should arrive with a marker in the URL that says “Hey, I’m a paid search visit.” The scheme by which you do this can range from the ultra simple (WebTrends recommends adding a parameter “WT.srch=1” as a basic starting point) to the fairly complicated, collecting not only the fact that the visit is a paid search visit, but also collecting information about which paid search campaign, adgroup, match type, search network (in Google’s case), and search engine applied to that particular search.  Plus, of course, you’d collect the keyword that was being paid for.  (Note:  with anything other than exact match, the keyword that the person actually typed in could be different from the one you paid for, and we wrote a post about this, too.)

We have an older post that touched on marking visits as coming from paid search, here.

Here’s how to make the two filters you’ll need

1.    A filter that isolates all search traffic.

  1. Go to Administration >> Web Analysis >> Report Configuration >> Custom Reports >> Filters >> New Filter
  2. Give the filter a name (and a category, optional) and select the Type of Filter to be “Visit”.  (The “ALL criteria” vs “one or more criteria” choice doesn’t matter here)
  3. Click on the “New” button that’s above the Match Criteria sectionSEfilter1
  4. You’ll now be on the “Visit Properties” screen, which is where you set your match criteria
  5. Drop down the “Filter On:” menu and choose “Search Engine” (while you’re there, take a look at all the choices you have!)

    SEfilter2
  6. The screen will refresh and show you three radio button choices:  “any search engine,” “not a search engine,” and “specific search engines.”  Choose the first one, “Visits resulting from any search engine.”
  7. Click on DoneSEfilter3
  8. Note that you didn’t specify whether this filter is going to include or exclude search traffic.  You’ll make that distinction when you apply the filter to the report — when you make the report, be sure to apply it as “Include.”

2.  A filter that excludes paid search traffic

  1. Identify how you are marking your paid search traffic.   Ask yourself, what query parameter do all your search visits have in common?  Maybe you are using the WebTrends parameter “WT.srch” that’s suggested by WebTrends as a starting point.  Or maybe you have implemented your own parameters, such as “source=paidsearch,” “source=adwords,”  “campaign_type=paidsearch,” or “searchengine=yahoo.”
  2. Go to Administration >> Web Analysis >> Report Configuration >> Custom Reports >> Filters >> New Filter
  3. Give the filter a name (and a category, optional) and select Type of Filter to be “Visit”.  (The “ALL criteria” vs “one or more criteria” choice doesn’t matter here)
  4. Click on the “New” button that’s above the Match Criteria section
  5. You’ll now be on the “Visit Properties” screen, which is where you set your match criteria
  6. Drop down the Filter On: menu and choose “Entry Page”

    SEfilter4
  7. The screen will refresh and give you a place to enter the Page Expression.  Enter “*” because it can be any page.

    SEfilter5
  8. Click on the “New URL Parameter” button
  9. In the URL Parameter screen, enter the name of the parameter that will always be present for paid search traffic and never present for non-paid search traffic, which you identified in the first step.  In the screen shot below, we used WT.srch but yours might be “source,” “campaign_type,” “searchengine” and so on.
  10. For Parameter Value, decide whether just the presence of the parameter is all that matters, regardless of value.  If yes, enter “*”.  If, on the other hand, the value is really important, as in “campaign_type=paidsearch,” enter “paidsearch” (or whatever) in the Parameter Value box.  Notice that you can use Text, Numeric or Regular Expression matching.   Make the appropriate choice.  For “*” use Text, of course.
  11. Click on Done

    SEfilter6
  12. Note that you didn’t specify whether this filter is going to include or exclude search traffic.  You’ll make that distinction when you apply the filter to the report — when you make the report, be sure to apply it as “Exclude.”

That’s it.  Now make a Custom Report. Choose the dimension you’re interested in (search terms, content groups, etc) and apply both of these filters.  The Search Traffic filter should be applied as Include and the Paid Search Traffic filter should be applied as Exclude. The screen shot below shows what the include/exclude choice will look like when you are creating/editing your Custom Report.

SEfilter7

Postscript

What if a visit starts with a click from natural search, but during the visit switches back to a search engine screen and re-enters the site from a paid search link?  Is it a natural or a paid search visit, or both?  Got an answer?

Pages Sorted by Directory, in One Report

Create one report that divides your viewed pages according to what directory they are in.

A question came up on the WT User Forum (http://forums.webtrends.com) from someone who has many subdirectories, each containing content for different countries.  He wanted a single report that would show what pages were visited, divided by country.  

His URLs looked approximately like this:

site.com/regions/Afghanistan/page1.asp
site.com/regions/Afghanistan/subtopic/page9.asp
site.com/regions/Canada/page1.asp

This is obviously going to need a nested (2-dimension) report, where the list of pages viewed is the secondary dimension and the country(which is a subdirectory under the /regions/directory) is the primary dimension. 

We can think of three ways to get that primary dimension, and will explain the one we like most in this post.  (The other two are mentioned in this post’s Postscript.)

  • Create a custom dimension that extracts the country name from the URL.  

This custom dimension takes advantage of WebTrends’ ability to isolate portions of URLs, using regular expressions.  We’re providing a regular expression that you can easily adapt to work in other situations.

Here’s how to do that custom dimension and the resulting custom report.

Create the  dimension 

  1. Go to the create-dimension screen (Administration >> Web Analysis >> Custom Reports >> Dimensions >> New)
  2. Give the new dimension  a name and column name.  Note that in the screen shot below we have also optionally chosen to put it in our Pages category of dimensions.  This will make it easier to find in dropdown lists in the future.dir-dimension1
  3. Go to the next screen and base the new dimension on URL (i.e. choose URL from the dropdown list).  Then click on the Advanced button.dir-dimension2
  4. The Advanced button causes the screen to expand, showing some new choices as shown below.  Choose “Regular Expression” and enter this regex:

    .*/regions/([^/]*)/.*dir-dimension3

  5. Set your measures
  6. Save and close.

Use the new dimension in a Custom Report 

  1. Get to the create-report screen (Administration >> Web Analysis >> Custom Reports >> Reports >> New)
  2. Give it a name.  Note that we’ve optionally chosen to put it in our Pages report category.dir-dimension4
  3. In the next screen, the dimensions screen, pull down the dropdown list for Primary Dimension and locate the newly created custom dimension.  (It was listed here automatically as soon as you saved it.  If it’s not there, you forgot to save it.)  Note that because we originally tagged the new dimension with the “Pages” category, it shows up conveniently in the Pages section of the dropdown list.dir-dimension5
  4. On the same screen, pull down the dropdown list for Secondary Dimension and select Pages.
  5. Check the boxes for “Exclude Activity without Dimension Data” for the Primary Dimension.dir-dimension6
  6. Save and close.

Postscript

The other two methods we thought of are:

  • Define content groups, one for each directory, and use the out-of-the-box Content Groups dimension as the primary.  Drawback:  If you already have some content groups defined in the profile you want to use, the report will have extra divisions in it unless you use a hit filter that allows only “/regions/” hits.
  • Use a primary dimension of Directories (an out of the box dimension).  Drawback:  By default, the WebTrends Directories dimension uses only the top level of directories, which will give you what you want only if your country directory happens to be at the very top level of the site.  If on the other hand your country dimension is on a lower level than that, say, level 2, then you have to change the default so that the top two (or whatever) levels of directories are reported.  In WT software, it’s easily done with a change to the profile *.wlp file.  In On Demand, it’s easily done with a tech support request.  And, to do this, you should have a custom filter on the report that allows only hits that contain “/regions/”.

Second Postscript

A high-level explanation of the regular expression:  it’s being told to look for the string “/regions/” then extract everything between “/regions/”  and the very next instance of “/”.

 

EU Directives FAQ from WebTrends

WebTrends’ FAQ on the EU Data Protection and Privacy Directives

WebTrends just released a long FAQ, updated this morning, March 16 2011, on the EU Data Protection and Privacy Directives and how they affect WebTrends data collection and reporting.  We learned a couple things reading it and suggest you read it, too.

It’s here:

FAQs on EU Data Protection and Privacy Directives

Get a Divorce from IP Addresses

Keep IP addresses from being recorded with a few changes to your SDC tags. Here’s how, and here are the tradeoffs.

WebTrends has a definitive way to keep IP addresses out of your data.

The issue is probably partly driven by the death struggle going on right now between Google Analytics and German authorities, but it’s also an answer to some customers’ desire right here in the U.S. of A. to not have potentially personally identifiable information in analytics data at all.

Specific instructions are in the WebTrends Knowledge Base, here.

Basically, WebTrends confronts the challenge head-on with some tag changes you can make yourself, that will keep IP addresses out of SDC logs and also out of the SDC cookies.

The changes will result in

  1. using a randomly generated character string instead of IP address in the cookie (and, therefore, in the visitor identifier)
  2. recording the same IP address for everybody

There are four edits you have to make to the SDC tag if you want to stop SDC from collecting IP addresses.  Three of them are in the main tag and one is in the “no script” part of the tag. If you use the WebTrends SDK to track mobile apps, there are other changes you can make, explained in the same Knowledge Base article.

There are of course some tradeoffs.  If you implement this, realize that:

  • You will not be able to get geography reports, since those reports depend on IP address.
  • You will not get domain information about your visitors, because those reports use IP address.
  • Your visitor and visit numbers will go down slightly, to the extent that you have visitors that do not accept cookies, because WebTrends normally uses IP address as a piece of its backup method to sessionize/visitorize if a cookie isn’t available.
  • Cross-domain tracking will go away, since this relies on a third party cookie which gets disabled as part of the process.  (Update 3/11 – WebTrends is considering (via internal feature request) a fundamental change to the format of the cookies which would  allow cross-domain tracking to continue working.)
  • You will not be able to filter your own traffic from reports (since IP address is needed for that).

If you implement it as part of an existing installation, you’re still going to have old WebTrends cookies around that contain IP addresses.  Even so, WebTrends will continue to analyze things just fine (except for the reports mentioned above).  But to make an absolutely clean break of it with no IPs in all your history, you’d have to consider getting a different DCSID.

Postscript

One of the things I like about this fix is that WebTrends already had in place the basic raw materials for this fix, in the tag code, the SDK and in its own log field substitution parameters (specifically, DCS.dcsip which is tailor-made for recording whatever you want in the SDC log’s c-IP field).  As a result, the edits you have to make are really quite simple and understandable.

Speaking of which, the several available DCS.xxx meta-tags are worth highlighting for their own sake, so we’ll talk about them in our next post.

View All Your SQL (ODBC) Queries At Once

Use this Excel macro to dump a workbook’s ODBC information into a spreadsheet, where you can edit queries and connection strings.

If you use the ODBC driver to get data from WebTrends into Excel, then you’re using a SQL query.

If you have a lot of SQL queries in one workbook, you can use this Excel macro (“ReadSQL( )”) to dump your ODBC information into its own Excel sheet.  It will have one row per query table and the following  four columns:

  1. The name of the sheet
  2. The number of the query table in the sheet (it’s “1” unless you have more than one query table in that sheet)
  3. The connection string for the query table (if you are connecting to more than one WebTrends profile, it helps to see the profile’s GUID)
  4. The SQL query itself

Like this:

readsql3

The beauty of all this is that there’s also a “WriteSQL( )” version of the macro.   This means you can make changes to the SQL queries or the connection strings right on the worksheet, then run the “WriteSQL( )” macro.  All the queries/strings shown on the sheet will update (inside Excel) to be exactly as you edited them.  Woot!

This is great for:

  • changing SQL queries (‘cuz you’re  using a simple text-based Excel sheet)
  • quality-checking your SQL queries (‘cuz all your queries are showing on one sheet)
  • changing the time periods for all your queries at once ( just use Excel Find & Replace)
  • replacing one profile with another (i.e. swapping a lot of profile GUIDs at once; use Excel Find & Replace)

Here’s the Excel macro code for “ReadSQL( )” – this version works in Excel 2003 (or if you’re in Excel 2007 and using cloned sheets and modifying queries that were originally inserted in Excel 2003).  We’re working on a pure 2007 version (thanks to Mike w).


Sub ReadSQL()
     Cells.Select
     Selection.Delete Shift:=xlUp
     x = 1
     For Each WKS In Worksheets
          y = 1
          For Each Qtable In WKS.QueryTables
               Cells(x, 1).Value = WKS.Name
               Cells(x, 2).Value = y
               Cells(x, 3).Value = Qtable.Connection
               Cells(x, 4).Value = Qtable.CommandText
               x = x + 1
               y = y + 1
          Next
     Next
     Columns(1).ColumnWidth = 20
     Columns(2).ColumnWidth = 3
     Columns(3).ColumnWidth = 50
     Columns(4).ColumnWidth = 50
     Range("A:C").WrapText = True
End Sub

and here is its counterpart “WriteSQL( )”:

Sub WriteSQL()
     x = 1
     Do While Not Cells(x, 1) = ""
          Worksheets(Cells(x, 1).Value).QueryTables(Cells(x, 2).Value).Connection = Cells(x, 3).Value
          Worksheets(Cells(x, 1).Value).QueryTables(Cells(x, 2).Value).CommandText = Cells(x, 4).Value
          x = x + 1
     Loop
End Sub

CAVEATS (Warnings):

  • I’m assuming you already know how to add a macro to your Excel.  Either put it in your ODBC-based workbook, or alternatively you can put it in your Personal.xls workbook and it will be available all the time.
  • You should always run the ReadSQL( ) macro immediately before making any edits, so that you are working with a perfectly fresh and accurate set of information.  Don’t rely on an old sheet that you may have filled (using ReadSQL) some time ago.  You need to be sure that what  you’re modifying actually matches the current state of your queries and connection strings.
  • Run the ReadSQL( ) macro when you are on the sheet where you want the information to magically appear.  Similarly, have that sheet be the active sheet when you run WriteSQL( ).
  • It’s probably best to not use it in a workbook that also has REST URLs, though we think it’ll work fine.
  • And of course … there’s no warranty express or implied.

Postscript:

These macros come courtesy of the fabulous Mister Peabody, who acknowledges that “the code is ugly.”  Mister Peabody has been meaning to go back and make some improvements, but it’s been working fine, so he hasn’t.