In this series, I’ve summed up a few basic Tealeaf pain points by harnessing the power of SQL queries. However, one final, common question remains unanswered. Capturing the information is one thing but once you want to make the jump to actually analyzing the data that the Tealeaf application captures, that’s where you will run into some difficulty. In this series finale, I answer the last, and slightly more demanding, Tealeaf request:
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?
All of my queries thus far were done on the TL_SYSTEM database, which is quite straightforward in the way it is set up (though not publicly documented by Tealeaf, as they aren’t going to make it easier for you to understand what’s in it!).
The reporting data is stored in TL_REPORTS, which has tables that are produced by the portal dynamically. It is no easy task to query against those tables directly. There’s a chance that if you had an insane amount of free time, you could probably figure out how to query on this database, but as the portal adds and removes tables your queries would rapidly become obsolete (and furthermore, Tealeaf would strongly warn against you attempting to manually access those tables).
Also, the TL_REPORTS database is just storing data generated from existing events. For instance, if you wanted to determine how many hits occurred on a URL over the past six months but an event was never created to track the activity, unfortunately, there would be no data within TL_REPORTS in response to your query.
If you really want to be able to query and report on data that Tealeaf captures, cxConnect is the way to go.
The cxConnect Solution
Considering how incredibly detailed the Tealeaf data is (every page, click, form fill, IP address, browser version, etc.) it makes a lot of sense to move that data into a database that can queried at will (or warehoused and integrated with other company data sources). With that, you can then answer bigger questions, such as:
Our website was down last Monday between 8:20am and 10:40am. How many users weren’t able to access it that normally would? How much revenue was potentially lost due to that downtime?
With cxConnect, you could have all the Tealeaf data sent to a SQL database on a daily basis. You’d be able to see how many customers on average visited the site on the last 4 (or more) Mondays, right down to the hour, and for each URL. You’d also be able to query to find out how much they spent at a checkout page during those time frames, so you could make an estimation on what *would* have happened if the site wasn’t down.
cxConnect extracts the session data into flat files that can be consumed by your relational database of choice. It provides a much greater level of flexibility with Tealeaf data than the portal reporting tools, but it’s not a turnkey solution. Quite frankly, you have to apply some data science and database knowledge against these flat files to structure them accordingly within the database of choice. At Stratigent, we have experience with doing exactly this and we can help you operationalize this valuable data for your business.
That’s a wrap!
By utilizing the strategies outlined in this series (Part 1
, Part 2
– in case you missed it!), you should now feel a bit more confident in your admin role. No matter how small or large the request, managing your Tealeaf environment is possible using SQL. You’ll soon be on your way to becoming a lean, mean, efficient data-crunching admin.
Have other SQL tips? Comment below!