Custom Omniture Dashboards: Part 2 – Storing the Data

Your assignment. Create a custom dashboard to display your site’s top 5 pages. The dashboard will be delivered to executives daily and will trend the page views for each page against the previous day as well as the previous same day of the week.

The script for Part 2, you will need to update with your specific connection information, can be downloaded here.

Part 1: Getting the Data
Part 2: Storing the Data
Part 3: Building the Dashboard

By the end of Part 3 of this series, you will have all the information you need to complete your assignment.

Part 2: Storing the Data

In Part 1, we reviewed how to extract data from your Omniture SiteCatalyst Report Suite using the SiteCatalyst Reporting API. In Part 2 of the series, we will learn how to store the extracted data in our own database. In part 3, we will pull the data from our database so we can format and deliver the custom dashboard.

Omniture Custom Dashboard Data Flow

What you will need:

→Understanding of Omniture’s REST API
→Access to a SiteCatalyst report suite with data
→A SiteCatalyst user with Web Service Access rights
→A server running PHP
→A working knowledge of PHP
→SimpleRestClient.class.php
→A server running MySQL
→A server running phpMyAdmin {optional}

Let’s get on with it…

Create a new MySQL database

The first step is to create a new custom database so that we have a location where we can store the data we extracted from Omniture in Part 1.  In this tutorial we are using MySQL but if you have access to a different database, then by all means go ahead and use it. We will also be making use of phpMyAdmin to administer our new database.

To create a new database using a browser-based control panel, like phpMyAdmin, please consult the documentation or ask your web host service provider as you will need root user privileges.

Create a table to hold the data

Create a table that will store the elements for our custom dashboard.  For this example, my table looks like the following:

I have created fields that will contain the reporting date, the page name, and page views for each page.

Insert API data into database table

Now we are ready to update the script that we coded in Part 1 of this series to insert the data we retrieved using the API into our newly created database table.

First, let’s create a connection to our database. I’m going to add this code just above the line “echo “Page – PageViews”

//Connect To mySQL
$hostname='{your_hostname}';
$username='{your_db_username}';
$password='{your_db_password}';
$dbname='{your_db_name}';

mysql_connect($hostname,$username, $password) OR DIE ('Unable to connect to database! Please try again later.');
mysql_select_db($dbname);

This will create a connection to the database we just created.

Now let’s write the code to insert new records into our table:

foreach ($json->report->data as $el) {
    $query = 'INSERT INTO dashboard (omtr_date, omtr_page, omtr_page_view) VALUES ("'.$reportDate.'", "'.$el->name.'", "'.$el->counts[0].'")';
    $result = mysql_query($query);
}

Automate the script
Using a Cron Job or some other solution, automate the execution of your script so that your database is populated once per day. I’ve setup a Cron Job to execute my script every morning at 3AM. The script will retrieve yesterday’s data and populate it into the database. By choosing a time like 3AM, you are ensuring that the previous days data has been fully processed. Storing all the historical data in our own database will allow us to compute more complex calculations and trends that are not available in the out-of-the-box Omniture reports.

Test the script

If everything works out, when you query your database (SELECT * FROM dashboard) you should see something like the following (with your page names and page views of course)

In Part 3 we will talk about how to take the output from the database and create a visually appealing custom dashboard that can be distributed to your teams.