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 |
