From zero to Website analytics

You'll learn how to create a database and create analytics with an Website log dataset using the Data Loading & Transformation Module and the Data Exploration & Visualization Module.

In this page

Introduction

This tutorial will introduce you on how to use SlicingDice for storing, making queries on web logs and visualize it using the built-in Data Exploration & Visualization Module.
For this tutorial, we'll purpose a dataset where you collect data from a website and insert it on SlicingDice. We'll also use the Data Loading & Sync Module to insertion process.


Creating a database

To create a new database using the Control Panel, go to the header and click on Create New Database. A 3-step wizard will appear.

The Step 1 is where you'll need to provide basic database information, like name, type and pricing model.

Screenshot of Step 1 of Creating a database

These parameters in the first step are the basic ones that you need to create a database.
The following image shows the input parameters that will be used in this tutorial.

Screenshot of Step 1 of Creating a database with parameters

This way you can ignore the steps 2, 3 and 4 if you want by clicking on Save & Continue until completion.


Loading the data

In this tutorial, it'll be used a ready-to-use dataset. You can take a look at the dataset here or going to the page at https://download.slicingdice.com/web-analytics.csv.

To insert this dataset in SlicingDice, you need to create a Loading Job. Before creating a Loading Job, you'll need to set a Data Source, that will save all the connection information needed for access the dataset. Each of these steps are described below.


Creating a Data Source

In the Control Panel, go to the Data Sources page and click on Create New Data Source to start adding your data source.

The first step is to configure your data source identification as you can see below.

Screen to define
        the data source identification

Here you'll define your data source name, description and possible labels. When ready, click on Save & Continue.

Now you'll define the appropriate data source credentials. In this case, only the address of the dataset is enough. For this tutorial, the HTTP address is https://download.slicingdice.com/web-analytics.csv

Below is an example of how the data source configuration will look like.

Screenshot of Step 2 of Creating a data source with parameters

You can test the connection by clicking on Test Connection. If everything goes ok, you'll see a Connection Success message and then you can click on Save & Continue.

Finally just check your data source configuration and click on Submit. Now you'll be able to find your created data source at the data sources list. That's it!, now you can use this data source to go to the next section of this tutorial.


Creating a Loading Job

In the Control Panel, go to the Loading Jobs page and click on Create New Loading Job to start creating your job.

The first step is to configure your loading job identification as you can see below.

Screen to define the loading
         job identification

Here you'll define your loading job name, description, the registered data source to connect and the job type (One-time manual load in this case). When ready, click on Save & Continue.

Now you'll define in which of your databases and dimensions the data will be loaded. In this case, the database is Web-Analytics and the dimension is default.

Screen to define the loading
         job data destination

When everything is set, click on Save & Continue.

After this you'll map your source columns and match them with the SlicingDice columns and their supported data types.

For it, the first step is to detect your source schema, selecting the data you want to retrieve. You can do it choosing the Access mode - query or table - where you can define a SQL query or just inform the name of the table to retrieve data.

Map columns from source to SlicingDice columns

After detecting your schema, the selected source columns will appear at the Attribute and Event Columns mapping section. Map the source's columns to SlicingDice's existing columns or create new ones if needed. In this tutorial, the data will be modeled in the following way.

Data Source Column SlicingDice Column
ID (varchar) entity-id (unique-id)
IP (varchar) ip (String)
UserAgent (varchar) user-agent (String)
TimeSpentMin (float) timespentmin (decimal)
Languages (varchar) languages (String)
Country (varchar) country (String)
Timestamp (timestamp) timestamp (Datetime)
Interests (varchar) interests (String)
After the modelling, click on Save & Continue.

Finally just check your loading job configuration and click on Submit. Now you'll be able to find your created loading job at the loading jobs list.

As the Loading Job is set as One-Time Manual Job, you can start it by clicking on the Play button at the Actions section on your loading jobs list.

You can also see the Status of your Loading Job changing to Running. When the status changes to Completed, you can now start your queries.


Querying the data

It's possible to do multiple queries using the API and you can also make queries using the SDK Clients or any SQL workbench you prefer. For this tutorial purpose, we'll make two different data analysis as you'll see below.

  • 1st analysis
  • On this first analysis, we'll answer the following question:
    What is the average time spent on the site?

    To answer the question, you'll make an aggregation query that is a powerful way to cross data in order to find insights and unexpected patterns.

    The query below will ask to SlicingDice to retrieve the Average of the TimeSpentMin.

    curl -X POST https://api.slicingdice.com/v1/sql \
    	    -H 'Authorization: PASTE_YOUR_PERSONAL_DEMO_API_KEY_HERE' \
    	    -H 'Content-Type: application/sql' \
    	    -d 'SELECT AVG(timespentmin) FROM default;'
    curl -X POST https://api.slicingdice.com/v1/query/count/entity \
        -H 'Authorization: PASTE_YOUR_PERSONAL_DEMO_API_KEY_HERE' \
        -H 'Content-Type: application/json' \
        -d '{
       "query":[
          {
             "languages":{
                "equals":"chinese"
             }
          }
       ]
    }'
    from pyslicer import SlicingDice
    slicingdice = SlicingDice(master_key='MASTER_API_KEY')
    
    query_data = {
        "query": [
            {
             "languages":{
                "equals":"chinese"
             }
          }
        ]
    }
    
    print(slicingdice.count_entity(query_data))
    import com.slicingdice.jslicer.SlicingDice;
    import java.io.IOException;
    import org.json.JSONObject;
    import org.json.JSONArray;
    
    public class Example {
        public static void main(String[] args) throws IOException {
            SlicingDice slicingdice = new SlicingDice("MASTER_API_KEY");
    
            JSONObject queryData = new JSONObject()
            .put("query", new JSONArray()
                .put(new JSONObject()
                    .put("languages", new JSON Object()
                        .put("equals", "chinese")))));
            JSONObject result = slicingdice.countEntity(queryData);
            System.out.println(result.toString());
        }
    }
    require 'rbslicer'
    slicingdice = SlicingDice.new(master_key: 'MASTER_API_KEY')
    
    query_data = {
        "query" => [
            {
             "languages":{
                "equals":"chinese"
             }
          }
        ]
    }
    
    puts slicingdice.count_entity(query_data)
    const SlicingDice = require('slicerjs');
    
    const slicingdice = new SlicingDice({masterKey: 'MASTER_API_KEY'});
    
    const queryData = {
        "query": [
            {
             "languages":{
                "equals":"chinese"
             }
          }
        ]
    };
    
    slicingdice.countEntity(queryData).then((resp) => {
        console.log(resp);
    }, (err) => {
        console.error(err);
    });
    <?php
    use Slicer\SlicingDice;
    $slicingdice = new SlicingDice(array("masterKey" => "MASTER_API_KEY"));
    
    $queryData = array(
        "query" => array(
            array(
                "languages" => array(
                    "equals" => "chinese"
                )
            )
        )
    );
    
    print_r($slicingdice->countEntity($queryData));
    ?>
    using System.Collections.Generic;
    using Slicer;
    using Newtonsoft.Json;
    
    namespace SlicerTester.Console
    {
        class Program
        {
            static void Main(string[] args)
            {
                var slicingdice = new SlicingDice(masterKey: "MASTER_API_KEY");
    
                var queryData = new Dictionary<string, dynamic>{
                    {"query", new List<dynamic>{
                        new Dictionary<string, dynamic>{
                            {"languages", new List<dynamic>{
                                {"equals", "chinese"}
                        }
    
                    }
                },
            };
    
                var result = slicingdice.CountEntity(queryData);
                System.Console.WriteLine(JsonConvert.SerializeObject(result).ToString());
            }
        }
    }
    package main
    import (
        "fmt"
        "github.com/SlicingDice/slicingdice-go/slicingdice"
    )
    
    "query": [
            {
             "languages":{
                "equals":"chinese"
             }
          }
        ]
    
    func main() {
        keys := new(slicingdice.APIKey)
        keys.MasterKey = "MASTER_API_KEY"
        slicingdice := slicingdice.New(keys, 60)
    
        queryData := map[string]interface{}{
            "query": []interface{}{
                map[string]interface{}{
                    "languages": []interface{}{
                        "equals": "chinese",
                    },
                },
            },
        }
    
        fmt.Println(slicingdice.CountEntity(queryData));
    }

    After running this query, you should be able to see the following result.

    {
      "status": "success",
      "result": {
        "query": 36
      },
      "warning": "Your column entity-id still have 7 days remaining",
      "took": 0.112
    }

    As you can see, the results are basically showing how many IPs has the language as "chinese".

  • 2nd analysis
  • Now another question we'll make is the following one:
    How many people has the language set as chinese?

    We can answer this question using the Count Entities query that are useful to know how many unique entities (in this case, servers) you have that satisfies some set of conditions.

    The query below will ask to SlicingDice to filter the result of languages column that have the value as chinese, then SlicingDice will COUNT the languages column values that have languages value as chinese.

    curl -X POST https://api.slicingdice.com/v1/sql \
    	    -H 'Authorization: PASTE_YOUR_PERSONAL_DEMO_API_KEY_HERE' \
    	    -H 'Content-Type: application/sql' \
    	    -d 'SELECT COUNT(*) FROM default WHERE languages = 'chinese';'
    curl -X POST https://api.slicingdice.com/v1/query/count/entity \
        -H 'Authorization: PASTE_YOUR_PERSONAL_DEMO_API_KEY_HERE' \
        -H 'Content-Type: application/json' \
        -d '{
       "query":[
          {
             "languages":{
                "equals":"chinese"
             }
          }
       ]
    }'
    from pyslicer import SlicingDice
    slicingdice = SlicingDice(master_key='MASTER_API_KEY')
    
    query_data = {
        "query": [
            {
             "languages":{
                "equals":"chinese"
             }
          }
        ]
    }
    
    print(slicingdice.count_entity(query_data))
    import com.slicingdice.jslicer.SlicingDice;
    import java.io.IOException;
    import org.json.JSONObject;
    import org.json.JSONArray;
    
    public class Example {
        public static void main(String[] args) throws IOException {
            SlicingDice slicingdice = new SlicingDice("MASTER_API_KEY");
    
            JSONObject queryData = new JSONObject()
            .put("query", new JSONArray()
                .put(new JSONObject()
                    .put("languages", new JSON Object()
                        .put("equals", "chinese")))));
            JSONObject result = slicingdice.countEntity(queryData);
            System.out.println(result.toString());
        }
    }
    require 'rbslicer'
    slicingdice = SlicingDice.new(master_key: 'MASTER_API_KEY')
    
    query_data = {
        "query" => [
            {
             "languages":{
                "equals":"chinese"
             }
          }
        ]
    }
    
    puts slicingdice.count_entity(query_data)
    const SlicingDice = require('slicerjs');
    
    const slicingdice = new SlicingDice({masterKey: 'MASTER_API_KEY'});
    
    const queryData = {
        "query": [
            {
             "languages":{
                "equals":"chinese"
             }
          }
        ]
    };
    
    slicingdice.countEntity(queryData).then((resp) => {
        console.log(resp);
    }, (err) => {
        console.error(err);
    });
    <?php
    use Slicer\SlicingDice;
    $slicingdice = new SlicingDice(array("masterKey" => "MASTER_API_KEY"));
    
    $queryData = array(
        "query" => array(
            array(
                "languages" => array(
                    "equals" => "chinese"
                )
            )
        )
    );
    
    print_r($slicingdice->countEntity($queryData));
    ?>
    using System.Collections.Generic;
    using Slicer;
    using Newtonsoft.Json;
    
    namespace SlicerTester.Console
    {
        class Program
        {
            static void Main(string[] args)
            {
                var slicingdice = new SlicingDice(masterKey: "MASTER_API_KEY");
    
                var queryData = new Dictionary<string, dynamic>{
                    {"query", new List<dynamic>{
                        new Dictionary<string, dynamic>{
                            {"languages", new List<dynamic>{
                                {"equals", "chinese"}
                        }
    
                    }
                },
            };
    
                var result = slicingdice.CountEntity(queryData);
                System.Console.WriteLine(JsonConvert.SerializeObject(result).ToString());
            }
        }
    }
    package main
    import (
        "fmt"
        "github.com/SlicingDice/slicingdice-go/slicingdice"
    )
    
    "query": [
            {
             "languages":{
                "equals":"chinese"
             }
          }
        ]
    
    func main() {
        keys := new(slicingdice.APIKey)
        keys.MasterKey = "MASTER_API_KEY"
        slicingdice := slicingdice.New(keys, 60)
    
        queryData := map[string]interface{}{
            "query": []interface{}{
                map[string]interface{}{
                    "languages": []interface{}{
                        "equals": "chinese",
                    },
                },
            },
        }
    
        fmt.Println(slicingdice.CountEntity(queryData));
    }

    After run this query, you'll see the following result.

    {
      "status": "success",
      "result": {
        "query": 36
      },
      "took": 0.112
    }

    As you can see above, the results shows that 2 servers presented some instability in the last 24 hours. But which of them? Check the next analysis to find out.


Visualizing your data

You can use the Reporting & Dashboards tool to create charts to visualize your data. On this section, you'll learn how to create one to visualize the answer for the following question:

What are the usage of fuel by Airplane 01 each registered day? And what is the trend?

The answer for this question would look like the following chart. Below you'll learn how to build it.


Creating the dataset for visualization

Before creating a chart to visualize your data, you need to create a dataset. Watch the following quick video or read the written instructions below.

Dataset
            IoT

Go to the Data Visualization Tool, find the database name and click on Browse datasets. Click on Write SQL query and insert the following code to build a visualization dataset with all data you have stored.

SELECT * FROM default;

After that, click on Run. You'll see that the module returned the fields and data you have. Click on Done, name your dataset, click on Rename, then click on Done again to save your dataset.


Creating the chart

After the dataset is created, you can now create your chart. Watch the following time-lapse video to see how the chart at the start of this section was made or if you prefer read how to create your own.

Dataset
            IoT

To create a chart, in the Dataset Browser, click on the dataset you just created and then select the option Create saved chart. The chart designer will open. You should use the following parameters:

  • In the Chart Type, select Time series
  • In the Value axis, select route-consumed-fuel value and select the Sum measure.
  • Still in the Value Axis, click on Add breakdown dimension and select entity-id.
  • In the Filters section, select the entity-id, select the airplane-01 then click on Apply.
  • In the Format menu, in the Chart section, go to Markers, switch on the visible button, and select the size as 10px.
  • Go back to the Chart section, now go to the Abbreviate thousands and millions section. Deactivate it.
  • Go back to the Chart section, switch to Elements section.
  • Click on the Title, and replace the text to Fuel consumed by Airplane 01.
  • Go back to the Elements section, then click on Labels. Enable it.
  • Go back to the Elements section, then click on Trendline. Click on Linear option.
  • Go back to the Elements section, then click on Series. Click on the airplane-01 option. Replace the text to Airplane 01.
  • Click on the Series color option and select a color. In this example, its orange.
  • Click on the Done button.
  • Click on the floppy disk icon, insert a name for the saved chart and click OK.