You can use Business Intelligence (BI) applications such as Microsoft Excel to access and analyze the data.
In our case we are using Microsoft Excel Professional 2013 for analysis of the data.
- • First we will import the Hortonworks Sandbox data into Microsoft Excel.
In Windows, open a new Excel workbook, then select Data -> From Other Sources -> From Microsoft Query.
- • On the Choose Data Source pop-up, select the Hortonworks ODBC data source you installed previously, then click OK.
- • The Hortonworks ODBC driver enables you to access Hortonworks data with Excel and other Business Intelligence (BI) applications that support ODBC.
- • After the connection to the sandbox is established, the Query Wizard appears. Select the webloganalytics table in the Available tables and columns box, then click the right arrow button to add the entire webloganalytics table to the query. Click Next to continue.
Click Finish on the Query Wizard Finish screen to retrieve the query data from the sandbox and import it into Excel.
- • On the Import Data dialog box, click OK to accept the default settings and import the data as a table.
The imported query data appears in the Excel workbook.
Now that we are done with loading data in Excel. Lets use Excel Power view feature.
Visualize the Website Click Stream Data Using Excel Power View
Data visualization can help you optimize your website and convert more visits into sales and revenue. In this section we will:
Identify a few web pages with the highest bounce rates
Click on Insert->PowerView to open report
The Power View Fields area appears on the right side of the window, with the data table displayed on the left.
Now a page would be considered to have a high bounce rate if it is the last page a user visited before leaving the website.
In the Power View Fields area, select url. All of the other fields should be unselected.
In the Power View Fields area, move the pointer over url, click the down-arrow, and then select Add to Table as Count.
The chart shows that we should focus on optimizing four of our website pages for the market segment of men between the ages of 22 and 30. Now we can redesign these four pages and test the new designs based on our target demographic,
thereby reducing the bounce rate and increasing customer retention and sales.