"I want my Sitecore Analytics data in Excel"

Why should I care about Sitecore in Excel?

No matter what you've spent on reporting systems (and business analysts to run those reporting systems), inevitably there are still spreadsheets all over your organization.  I can't think of a company I've been at or worked with that doesn't have at least one Excel Workbook that is the voice of truth.  The person that owns it has confidence in it--the data has overcome the skeptics, and the Excel interface is common to all and within everyone's grasp.

Sitecore data can now join in.  Let's forget about the "but I should have dashboards that tell me what to look at" crowd.  You know that if you get all this great data in Excel, you'll be in control and will start to highlight the insights and outcomes that is important to your business and your business only.  You'll pivot, shoot...score.

Familiar Pivot Tables and Charts

With Sitecore data in our OLAP cube format (more below), within about 15 minutes I created the Pivot table and chart below.  Our Sales Engineering group will certainly be looking at more insightful dashboards as we go, but these simple views can get us to the next stage of our discussion:  "How can we put together a customer journey that will promote the best possible software evaluation experience, leading to Sitecore being selected as the best choice in digital platforms?".

Sitecore Data in MS Excel Pivot Tables and Charts

Sitecore's Engagement Intelligence Datamart 

To get to this, we are using Sitcore's Engagement Intelligence Datamart.  This product provides the tools necessary to create a standardized OLAP cube from the Sitecore Analytics database.  Once in an OLAP format, virtually any reporting environment will "understand" what the data is all about.  OLAP will define Dimensions (the facets, categories, rows, columns) and Measures (the values, numbers, calculations) that the data is now available in, and we can immediately use the standard interfaces of Tableau, MS Excel, SQL Reporting Services, Sitecore's implementation of the Targit BI Suite or any business intelligence strategy you currently have in place.  We can start thinking about easily tying to other data sources, tying authenticated users to CRM records....sky's the limit here.

So, after following the Engagement Intelligence Datamart documentation, we have the OLAP cube in our SQL Server instance:

The Sitecore OLAP Cube Database in SQL Analysis Services

Right clicking on Cubes/Analytics node gives us the option to "Browse" the Cube.  This provides a design interface very similar to MS Excel, where we can drag the Dimensions and Measures from the Cube to a canvas where we can analyze all the prebuilt calculation goodness.  Below I simply added our Pattern concept in Sitecore and used the single measure of Page Views:

Exporting to Excel

From here, there's a nice little export feature to Excel at the top of the window:

You can also start in Excel and use the Data menu to create the connection string to your OLAP cube database on SQL Server.  The above export feature will create the connection for you automatically.

Now in Excel, we can take advantage of our familiarity with this application--Pivot Tables and Charts, Slicers and all of the easy-to-use data analysis tools that Microsoft has provided.  Below is the Pivot Table fields that will be immediately available to you.  Within the list, you'll be able to find common web analytics elements such as:

• Page Views
• Unique Visitors
• Time Spent on Page
• Engagement Value (unique to Sitecore)

...and much more.  Happy Pivoting.

Pivot Table fields available from Sitecore's DataMart / OLAP Cube

The power of session-level analytics reporting in Sitecore Analytics

Some background on session-level analytics in Sitecore:

The conversation often comes up when discussing Sitecore Analytics: "when should I use Sitecore Analytics and when should I use xyz Analytics?".  The use of multiple analytics tools, the coordination between them, and/or the decision to standardize on one analytics tool is a significant topic, but from my perspective it's simply important to consider what the landscape of Sitecore Analytics provides.  On the surface:

1. Sitecore has incredible analytics drill-down capabilities--from a list of recent visits, to all visits from a particular company or source, to an individual visitor, to an individual visit.
2. Sitecore offers standardized OLAP cube data, and a set of sophisticated business intelligence dashboards
3. Probably most importantly, Sitecore provides ACTIONABLE analytics.  Your reports are right there in your power tool of content authoring and publishing.  Now you can DO something with that analysis right away.
4. Sitecore provides incredibly compelling segmentation possibilities.

Together these capabilities span the spectrum of the ultra-specific (an almost Tealeaf style capability of watching an individual session which could be interesting for many situations such as a high-price ecommerce transaction, evaluation of a sample session for usability, etc.) to the ultra-summary (the rolling up and tearing back down based on my own business’ dimensions or facets).

So for today, let’s get ultra-specific.  For those of you that have read other articles about Launch Sitecore, you’ll know that our purpose is pretty specific—create an evaluation package that’s best-in-class so that our prospects can quickly learn that Sitecore is the best platform choice on the planet.  For those of you that understand our Engagement Plans, you’ll know that this process looks like this:

1. Through demonstrations and technical deep dives, our Sales Engineering team builds interest in having a prospect visit www.launchsitecore.net.
2. While enjoying some articles about Sitecore, our visitor is really urged to login or register to get to the good stuff (the download of the package itself).
3. After registering, the visitor wants to download the package (the site itself that they can apply to their local test installation of Sitecore).
4. After a glorious evaluation of the product, the visitor (now our customer) tells us how great the experience was in an incredibly over-simplified feedback form.

So, since this is only intended to be marginally real-world, let’s go through this is a visitor, all in one visit and see what we get on the other end from the reporting side.

The Simulation:

First, visit www.launchsitecore.net.

Since we’re simulating a brand new visitor, we’ll go ahead and register:

We are so excited about Launch Sitecore, we want to get our hands on the package right away, and we choose to download the version for Sitecore 7.x:

As we read a Digital Marketing System focused article, we check out the slide out at the top right of the screen, used to show the type of information Sitecore is collecting about our visit.  We notice that we've been categorized in the Digital Marketer Audience Segment (based on a number of articles we've read, including the "Create a Goal" article.  We also notice that it seems we are investigating the site to a level that shows we have a "Detailed" interest in the content.  We're not just reading the home page--we've investigated some detailed articles about the Sitecore Digital Marketing System.

The Reporting:

In the Sitecore Marketing Center, we can find the Latest Visit report.  Lots more to talk about around this reporting interface itself (including filters, date ranges, etc.), but for today let’s get to the info about our simulation.  First, since I’m in my home office on a snowy New England day, I’m behind my Comcast ISP account.  Beyond the scope of this article is my ability to tag my visitor sessions with additional information (from forms filled in, from authentication events, etc.) that could further identify my visitors.  In addition, I chose to “categorize” all Comcast Cable visitors as “My Company”.  (I could have categorized this as “ISP” or anything else I’d want to use to describe visitors from the Comcast DNS resolution).

I could drill down to all Visits from Comcast Cable by clicking on that link.  That would then show me more specific visitor sessions (possibly with specifically identifying tags).  Since I just ran through the simulation, I know this session is mine and I’ll bypass that by clicking on the link under the Date & Time.



Now at the Visit (Session) Detail report, I get a nice snapshot of my overall visit.  Some things to notice:

• We were able to tag the session with “MikeBlogTest” (the name I used to Register with the site)
• We see that I visited by clicking on a link within my Blog
• We see the total value of the visit was 85 Engagement Value points (with the breakdown of goal achievement following)
• We get a nice breakdown of the Pattern Card shape that is emerging from my visit—the specific content profile key attributes that are accumulating based on my content consumption.

Further down in the report, we start to get even more detail about the visit, including:

• The fact that my Campaign was triggered
• Fields that were filled out in our registration form
• Pages visited and specific duration for those page views
• Goals achieved (Register)

Another page, and more detail to round out the entire session.  More goals and fields filled in.  One interesting thing I want to point out here is that we actually captured an “error” on the site (line 17).  Error is in quotes here since, depending on how we handled it in the application, this could have been completely invisible to the visitor.  While this isn’t in any way a sophisticated error handling technique (it’s not meant to be), it could provide incredibly valuable information to the reviewer of the report in the context of the site visit.  What this tells the reviewer is that when a visitor clicked on a Search Result, there was no presentation assigned to the item clicked on.  A nice bit of usability testing after the fact (hey, we can’t catch everything in QA).

The Summary:

While not every analysis requirement will focus on an individual session or individual visitor, it is important to know that we can drill down to this level.  This gives us an incredible foundation to consider ties to transactional systems (Ecommerce), CRMs and other data sets to develop a full picture of our customer experience and value.  This is the ultimate leaf of the analytics tree, while keeping the ability to aggregate and roll back up to the branches.  By rolling back up, we will be able to get this same insight at the company level (all visits from one of our prospects), at the Pattern level (with Sitecore's ability to model the audience segments, now I can use those segments to hold aggregate analytics), and much more....

Using Sitecore Engagement Plans as an Audience Segmentation Tool

Engagement Plan as Audience Segmenter

I've been kicking around the idea of, instead of strictly mapping out a customer journey with Sitecore Engagement Plans, to instead use them as a visual audience segmentation tool.  With the great analytics that Sitecore is collecting, this seems like a natural extension to be able to divide the visitors within that analytics data into well-defined categories of patterns.  I'll end this post with an idea that could be added to the current Sitecore concept of Pattern Cards (with your great development addition, of course).

An example audience segmentation Engagement Plan

The idea is this: use the States and Conditions of Sitecore’s Engagement Plan to get a visual indication of the distribution of your audience based on any segmentation criteria.  Since the Rules Engine is so nicely involved in the Engagement Plan Condition checks, this to me is an incredibly natural use of this tool.

The Idea in Launch Sitecore

Here’s how it falls out for Launch Sitecore.  Our audience segments are clearly defined by the groups we envision might visit us at www.launchsitecore.net.  However, this definition is strictly based on content consumption—using Sitecore’s Pattern Card matching, we simply watch for the thresholds between these content consumption patterns.  If someone is reading a lot of articles around Sitecore’s Digital Marketing System, we might conclude he appears to be a marketer.  While this is really cool and easy to do, I have always thought that these patterns need to be a bit more descriptive, inclusive of content consumption patterns and potentially a lot more.  In addition, the definition of this pattern should be flexible and open to change on an ongoing basis—without development.

Talking to a lot of smart partners and clients lately has verified my assumptions.  As we get into the real-world use cases, we talk about all the attributes that should come together to fully define a marketing persona—everything from campaigns to keyword use in search to “known” profile attributes in CRMs to landing pages to referral sources.

Enter the Rules Engine

Enter the Sitecore Rules Engine and the perfect modeling environment for this situation.  Back to Launch Sitecore and our example.  As a simple start, all I want to do is segment my visitor traffic between two opposing possibilities—the visitor appears to be a Marketer or the visitor appears to be a Technologist.  We know that a couple of our Patterns nibble at this persona, and through content consumption our visitor might look like Mark the Marketer, or Devon the Developer.  But what we’re shooting for here is a much richer definition of the age-old split between marketer and technologist.  (By the way, if you consider yourself the perfect marriage of both, give me a shout).

First, we begin with a State that everyone is going to start their journey in:

The initial Engagement Plan state that all our visitors start in

We also get to decide which visitors we want in this plan.  For example, we might only want to evaluate visitors from a certain campaign.  Maybe I'm writing lots of articles on digital marketing subjects, and I want to test if the readers of those articles exhibit what I've defined to be "marketer" behavior on the site.

For the visitors we decide to track in this Engagement Plan, the first assumption is that we don’t yet have enough information to define them as Marketer or Technologist.  But we can immediately rectify that by invoking the Rules Engine in our first condition check.  We could check for either, but we chose to to first see if we should deem this visitor as technical.

A set of conditions we can check for to see if some one is "a technologist"

Our Condition invokes the Rules Engine where we can build a very sophisticated grouping of attributes that, for us at Launch Sitecore, peg someone with the Technical label.  We’re going to use the Pattern Card matches as a possibility (now grouping a couple of them together that represent our two technical Patterns), as well as including an additional possibility that someone got to Launch Sitecore using the word “development” in their search phrase (i.e., “Sitecore development”).

While this is a simple example, hopefully you’re seeing the possibilities here.  Never mind how rich the out of the box condition set is in Sitecore (geography, site, where you are in the content hierarchy itself, etc., etc.), we also know that this condition could be absolutely anything you dream up that returns a true/false.

If the condition returns true, we’re done and place the visitor in the Technical bucket:

A state for those we've deemed to be Technologists

If the overall condition returns false, we drop down to the check for the Marketer:

A set of conditions we can check for to see if some one is "a marketer"

As before, we are able to reuse our initial logic around Pattern Cards, with Connie and Mark combining forces as a possible reason to label a visitor as a Marketer.  In addition, we look for a search keyword containing DMS (Digital Marketing System) as well as a check for my MarchBlogBlitz campaign.  Since many of my articles in this time period are covering Analytics, I’m making the assumption that we have some Marketers on our hands.  Keep in mind the ability to replace the “or”s for “and”s, giving us possibly more granular condition definition.  As above for the Technical case, if this multi-clause condition returns true, we dump the visitor in the Marketing bucket.

Nice little trick if both of these checks return false.  We simply put the visitor back into the Undecided state and let other things trigger another check (maybe the next check is done on a follow up Goal, Page Visit, Login Event or anything else we might wait for our visitor to accomplish).  We do this by one last Conditional check, which always returns True:

Send the visitor back until we're sure

Here’s another look at the overall plan:

The full audience segmentation Engagement Plan

Pretty simple, but sky’s the limit.  One really cool idea that fits nicely into this is a very simple geography breakdown.  I could have, for instance, three states representing West, Central, and Eastern United States and immediately drop visitors into those states as they enter the site based on our GEOIP resolution.  (Since I am in the world of ideas only here, be sure to appreciate performance implications of making such wholesale checks).

Manually test how a visitor maps to the plan

One last really cool thing.  Since I have lots of visitor information in our Launch Sitecore site, I can even run a visitor through my new Engagement Plan.  Using the Supervise features of Engagement Plans, I grabbed my user (the Visitor associated with my Facebook authentication).  I moved that User to my new plan and the Undecided State.  In Supervise, I can manually press the trigger:



Since my Conditions are checked at Session End (among other events), I chose to run the Session End Trigger.

Sitecore goes through the entire logic of my plan and determines that my user account should be placed in the Marketing bucket.

The results of manually sending someone through the plan

Way cool.

NOW GO DOWNLOAD LAUNCH SITECORE.  (Need to get that great data for my reporting experiment).  I’ll let you know how our audience trends between this new Marketing and Technologist segmentation.

Extra Credit

For the interested student and the great developer….

Where I think this would be even more interesting is to use this same logic in the Pattern Card feature of Sitecore.  My prototype took all of 2 seconds (since it doesn’t in any way work).  But for an enterprising developer that adds the Rules logic to the Pattern Card check, this seems like a killer feature:

This becomes especially interesting considering Pattern Cards are an automatic dimension of Sitecore’s Analytics reporting.

Launch Sitecore and Google Analytics: Overview Reports

In my investigation of Google Analytics (and I’m new to it), I wanted to understand what you get for table stakes reports.  Logging into to GA gives you a really nice overview:

Hard to see such a rich report in a blog article, but some of the things I’d expect from an analytics are there—visits, unique visitors, page/visit, etc.  In addition, a very intuitive interface let’s me see more specific table-based reports that filter my viewpoint by browser, visitor location, operating system, and more.

Sitecore’s Engagement Intelligence Analyzer (and the full business intelligence suite it enjoys) employs a robust interface strategy, where dozens of prebuilt dashboards are available, each additionally customizable with filter criteria.  The example report below allowed me to easily define date ranges and looks at visitor locale (by country), to see a nice bar/line graph plotting both average visit duration and pages per visit, see a quick list of referring sites comparing the last 30 days against the previous 30 days and more:

There are plenty of additional dashboards that reflect all of the data shown on GA’s opening dashboard (stats covering visit duration, bounce rate, operating systems, browsers, etc.).

In addition, each of the individual graphs above gives me the option to expand / contract levels, add additional criteria, toggle between a chart and the data table behind it and more.

Here’s the same dashboard with the table data toggle on the top right graph:

The available dashboards make readily available all the same information on the Google default dashboard.  Of course, with a full-featured BI environment, the possibilities for customization are much greater here.  The Operating System report, for instance, gives me “live” graphs showing visit, conversion, duration and page per visit information.  As I click on a particular operating system, those graphs are refreshed and updated with the relevant data to that operating system only.  A full “drillpad” gives me even more power to fine tune and specify my criteria:

OK, so that’s just the surface, but I’m now confident that Sitecore Analytics provide me easy access to all the “default” analytics data in Google.  Since I know a lot about Sitecore Analytics, now I’m off to learn about segmented targeting, goal and campaign identification in GA so that I can fully understand the level effort to model these (I already have these marketing assets modeled in Sitecore and our Launch Sitecore site).

Off to consider both systems as I try to leverage analytics to answer the important questions for our Launch Sitecore site—why does our site exist, what do we want our visitors to accomplish, how are they getting to the site and which full path leads to optimal results?

Next up, setting Goals in GA.

Launch Sitecore and Google Analytics

Yes, Google Analytics.  I decided it was time to step back and figure out how the leader of the pack does things so that I can truly understand Sitecore’s offering in this space.  As I’ve been diving more and more into the analytics discussion, my main focus has been on understanding Sitecore’s quickly growing and rich analytics offering and to describe the benefits to partners and prospects.  Invariably, though, the challenging discussion comes up:  “do I use this instead of Google Analytics?”, and: “I have this set of reports from GA….are you saying Sitecore’s reports are different, better?”.

So, now I’m setting off on a journey to understand this a lot better.  The good thing—we have a public Launch Sitecore site that has been up and running now for some time with both Google Analytics and Sitecore Analytics gathering some great data.  Not a huge site, but very effective in considering things like Goals/Conversions, Segmentation, Referral Sources / Campaigns, and more.

My goal with this exercise is to truly define the best of both worlds.  Google Analytics is huge, rich, entrenched, effective.  Sitecore Analytics is laser focused on the the best Customer Engagement Platform on the planet.  Let’s try to answer the questions….where do these tools differ, overlap, complement?  How are they used together and what does each uniquely bring to the table?

For this simple start, one question that often comes up:  how do I integrate Sitecore with Google Analytics.  While this discussion could take off on a few different directions, let’s keep this ridiculously simple for now and see where we need to expand the answer.  For now, because of the great Sitecore presentation strategy, it’s as easy as this:

1. Sign up for a Google Analytics account if you don’t already have one
2. Copy the javascript GA gives you to insert into your pages for tracking
3. In Launch Sitecore, since we have one Layout that governs our entire browser-based design, all we have to do is insert that javascript right before </body>.  This will automatically track every Sitecore item / page that uses this layout (in the case of Launch Sitecore, this means all pages).
4. Sit back and let Google start collecting
5. (oh yeah), Sitecore DMS is already working its collection magic without the javascript.

Next we’ll start to drill into the data a bit and start to understand if our Launch Sitecore site is meeting up to our goals.

An Analytics Session for Launch Sitecore

Now that we’ve talked about our team’s Engagement Plan, Goals and Personas for Launch Sitecore, let’s take a quick look at the data Sitecore Analytics can provide us for a visitor to our site.  I will be having much more detailed posts around our upcoming Engagement Intelligence release, but for now we’ll take a look at things that are readily available in Sitecore 6.5.
The data we will see in this report will be based on our ideal visitor to Launch Sitecore:

1. A prospect that has been engaged by our Sales Engineering team (had a tech deep dive with the product, wanted to learn more by downloading and installing Sitecore)
2. The prospect registers at Launch Sitecore
3. The prospect downloads the Launch Sitecore package (and reads some additional articles along the way)
4. The prospect spends time with the evaluation (and hopefully has some more conversations and communication with the Sales Engineering team)
5. After a successful evaluation, the prospect comes back to Launch Sitecore to give the team some feedback.

Incidentally, this prospect will go through all phases of our Engagement Plan:

 

In the Engagement Analytics Reports, we can find the Recent Activit—>Latest Visits reports:

I’m at home behind my Comcast Cable ISP service, so that’s how my DNS record resolved.  These reports do have the ability to more clearly classify session records if the DNS only resolves to the ISP.
Clicking on Comcast Cable gives us insight into all recent activity from this “company”…which will be really meaningful in your case when your visitors sit behind a company DNS record:

We can now see Total Engagement Value generated from this company (over a selectable time frame).  We can see the total number of unique visitors from this company, the last time they visited and a Google Map to show the geographies they came from.
In the Top 20 Visitors column, we can see individual visitors and any Tags we were able to apply to this session.  In the case of Launch Sitecore, we are able to easily use the Web Forms for Marketers feature to Tag any field of an input form, so when I went to Register just now, I included mikecaseynow as my Name.  The Name field was Tagged and now shows up to identify the Visitor in this report.

 

Further down the report we get a nice summary of conversions for this company.  Notice all the Goals we discussed in this series.
Clicking “mikecaseynow” in the above report will now drill down to the specific visitor (Me) and describe the activity from that single visitor (across multiple visits)

 

I can see that I’m always showing up from NH, that I’ve accomplished the range of Goals at Launch Sitecore.  At the bottom of this drill-down, now I see the specific visits I’ve made to Launch Sitecore recently.  Clicking on my 9th visit from 11/4/2012, we get the most granular report in the drill-down chain:  the specifics of a single visit:

 

At the top of this report we get an interesting summary of the activity in this single visit.  We made a decision at Launch Sitecore to only calculate Visit Value for downloads of Launch Sitecore the first time it happens, and since I’ve tested this site a lot, I had already downloaded the package.
We get a nice list of this visit’s conversions, and a great readout of the probable Persona the visitor was trending to in this visit.  Since I read some infrastructure-focused articles on this visit, I was scoring heavily in the Technology Profile Key (and because of this, I would be looking like Ivan the IT Pro in the Launch Sitecore Personas).
Further down, we get a detailed Session Report:

 

Here we see specific time spent on pages and the associated Goal accomplishments, Campaign triggers and Tags.  Since I clicked on one of my Blog articles to start this visit and the Blog link included the Campaign ID, my Campaign was triggered on my entry to the home page of Launch Sitecore.
This report is truncated at the 5th page, but it details all pages visited with any associated detail.  Since in this session I also accomplished the Feedback Goal, I can actually see the Feedback directly in the report:

 

….and our Engagement Plan is able to email out the Feedback to our Sales Engineering Team (or, in our future, enter this directly to the CRM).

 

Previous Post:  The Engagement Plan for Launch Sitecore