Part 2 of 3: Gathering Hit Attributes and Associated Events using SQL

SQL queries save the day once again…

As you’ll recall from part 1 of 3,Tealeaf Admins are presented with an abundance of data and details about a customer’s web experience. From this information comes an abundance of simple requests/questions which unfortunately, aren’t so simply answered.
 
In the previous post, I outlined a way in which to answer an extremely common and important request.
 
With a few simple SQL queries we were able to get a list of Tealeaf users, when they last logged in, as well as a list of ALL tealeaf events.
 
In part 2 of 3, I address an even more specific, but just as common request.
“Show me a list of all Hit Attributes and their associated events”  
Again, by applying some simple SQL tips, you will be able to effectively and efficiently find a solution while significantly lessening your workload and headache. 
 
 
What is a Hit Attribute?
 
A Hit Attribute is a definition of a pattern. It can be something like the text “Thank you for your order”, a specific URL, or a name/value pair such as “TotalOrder=” followed by the dollar amount. When you define a Hit Attribute, you are telling Tealeaf to find that pattern in either the Request or Response buffer, and either track the occurrences of the pattern, or record a value within it. Here are some examples of what Hit Attributes can capture, when paired with Events that use them:
 
  • Total dollar value at Checkout
  • Items added to Shopping Cart
  • Coupon Code
  • Error message received, along with user’s browser type and version
  • Error message for a specific page or activity
  • Page A followed by Page B, then followed by Page C
  • User location information
You would then build an event that looks for the presence of the Hit Attribute. With the event built, you can then search for sessions where it fired, right down to the specific page. Then these events can be added to reports and dashboards, as well as alerts.
 
 
In other words, Hit Attributes are the foundation for the entire eventing system within Tealeaf. As a result, you’ll typically need to reference these along the way and understand which events are associated with them. 
 
REQUEST 1:  Can I get a list of all Hit Attributes?
 
Getting a list of these Hit Attributes is a bit tricky to find, in the TM_DATA_POINT table :
 
/****** This view shows all Tealeaf Hit Attributes  ******/
 
SELECT   [ID]
      ,[CONTAINED_BY_ID]
      ,[MODEL_ID]
      ,[CREATED]
      ,[UPDATED]
      ,[INTERNAL_NAME]
      ,[MM_TYPE]
      ,[DISPLAY_NAME]
      ,[NOTES]
      ,[DATA_TYPE]
      ,[METHOD]
      ,[DELETED]
      ,[VISIBLE]
  FROM [TL_SYSTEM].[dbo].[TM_DATA_POINT]
  where MM_TYPE = 35
 
These fields should be self-explanatory and you now have a spreadsheet-friendly list of all the Hit Attributes.
 
What’s great about this is that if your Tealeaf log files are showing a ton of errors due to something called “P_CATG_IM_COND_410_RESPONSE,” you can leverage this list (and its friendly naming conventions) to see that this is actually the internal name for “New Member Login Failure Message in Response” -now you can troubleshoot the problem instead of troubleshooting the error terminology!
 
 
REQUEST 2: Show me a list of all Hit Attributes and their associated events.
 
When it’s time for that dreaded Event Cleanup exercise (which we recommend you do a couple times per year), it definitely helps to be able to identify what Hit Attributes are in use, and what events use them. You’re not going to find THIS level of detail in Event Editor, unless you want to click “Show Dependencies” on each Hit Attribute and record them all. If you have hundreds of Hit Attributes, you’ll be kept busy for a long time.
 
 As an alternative, you can leverage this SQL query:
 
/****** This view shows all Tealeaf Hit Attributes and their associated events ******/
SELECT a.DISPLAY_NAME
,isnull(b.DISPLAY_NAME,'NONE') EVENT_NAME
,a.CREATED
,a.UPDATED
,CASE WHEN a.DELETED = 1 THEN 'YES' 
         ELSE 'NO'
     END DELETED
,CASE WHEN a.VISIBLE = 1 THEN 'YES' 
         ELSE 'NO'
     END VISIBLE
 
FROM
 
(SELECT  d.ID
      ,d.CREATED
      ,d.UPDATED
      ,d.MM_TYPE
      ,d.DISPLAY_NAME
      ,d.NOTES
      ,DATA_TYPE
      ,METHOD
      ,d.DELETED
      ,VISIBLE
  FROM [TL_SYSTEM].[dbo].[TM_DATA_POINT] d
  WHERE d.MM_TYPE = 35) a
  
  left outer join
  
 ( SELECT d.ID
      ,e.DISPLAY_NAME 
      ,d.CREATED
      ,d.UPDATED
      ,d.DELETED
      ,VISIBLE
FROM [TL_SYSTEM].[dbo].[TM_DATA_POINT_DATA_POINT_MAP] m, TM_DATA_POINT d, TM_EVENT e
WHERE d.MM_TYPE = 35
and m.SOURCE_ID = d.ID
and m.DEPENDENT_ID = e.ID )  b
 
on a.ID = b.ID
 
This returns just five columns:
 
  • DISPLAY_NAME – The Hit Attribute name
  • EVENT_NAME – The Event that utilizes the Hit Attribute
  • CREATED – Date/Time the Hit Attribute was created
  • UPDATED – Date/Time the Hit Attribute was last updated
  • DELETED – Has the Hit Attribute been deleted?
  • VISIBLE – Is the Hit Attribute active or inactive?  
 
For Hit Attributes that are associated with multiple events, there will be multiple rows returned. If a Hit Attribute has no associated events, the EVENT_NAME column will show “NONE”.
 
On a side note, these might be good candidates for removal if your goal is to clean up unnecessary Tealeaf objects. 
 
 
As you can see, there is a lot of useful Tealeaf system data that can be gleaned by some simple SQL queries. I hope these tips will help you save time by avoiding the need to collect this kind of information manually from the portal.   
 
In my last post, part 3 of 3, I will address one final and all-too-frequently-asked question. 
“Is there a way, via SQL Server, to access all of our Tealeaf data and drop it into a data warehouse for use in an OLAP cube for analysis?”
Thus wrapping up the series and leaving you feeling capable and efficient as a Tealeaf admin.
 
Questions, concerns? Feel free to reach out to us at info@stratigent.com
 
 

 

By Customer Experience Team
About the Author:

Written by the Stratigent Customer Experience Team.

Contact Us Now