Introduction

As Python's premier web framework, Django provides the foundation upon which many web applications have been built, facilitating rapid development without sacrificing stability. Amongst other versatile functionalities, Django integrates seamlessly with SQL databases, so today, we'll be using the framework to implement basic site analytics data recording. Then, we'll use this data to create gorgeous visualizations in an Arctype dashboard, which will keep track of the following:

• Registrations, logins, and logouts
• User activity by day
• Breakdown of user activity on a selected day

To achieve this, we will be utilizing Django's ORM and Arctype (of course!)

Why Use Arctype for Site Analytics?

For this tutorial, we will be using Arctype to query our SQL data. Running queries has become easier than ever with the assistance of Arctype's autocomplete and save functionalities. You no longer have to remember every SQL command word-for-word—Arctype will complete them for you and allow you to save your commonly used queries for later.

We will also be building our analytics dashboard using the dashboard functionality. Arctype dashboards allow you to seamlessly interact with your data, creating eye-catching visualizations with ease. For more on Actype's functionalities, you can visit their official documentation.

Prerequisites for This Tutorial

To fully understand this tutorial, you should have the following:

• Python 3.7 or newer
• Arctype
• A basic understanding of SQL
• A basic understanding of Django
• A text editor

With those prerequisites out of the way, we are ready to begin.

Getting Started with Django

Because simply writing a Django app to record website analytics could take up an entire post, in this tutorial, we will be cloning the Django application I have already built and subsequently connecting the application to an SQL server by editing the settings.py file.

Using Git to Clone the Django App

Run the code below in your command line to clone the repository containing the Django app on Github:

git clone https://github.com/Chukslord1/ARCTYPE_DJANGO_DASHBOARD.git

Editing Settings.py to Connect to Your SQL Server

In your settings.py file, edit the code below and replace it with the details required to connect to your SQL server:

DATABASES = {
    'default': {
        'ENGINE': 
'django.db.backends.mysql',
        'NAME': 'database_name',
        'HOST': '127.0.0.1',
        'PORT': '3306',
        'USER': 'username',
        'PASSWORD': 'password',
    }
}

Connecting Arctype to Your Database

While you've got your database information handy, let's go ahead and connect Arctype to your database. After downloading Arctype and creating an account, you should be directed to an initial database setup page. Assuming you already have a database, simply click "I have an existing database" and then select your database type:

On the next screens, you'll need to enter your database credentials. These should match the connection details in your settings.py. If your database is running on localhost, you can simply use the values below:

Finally, you have to create and name your workspace:

With Arctype all ready to go, let's get our Django app set up to start filling this database with some user data!

Creating Django Models for Analytics Data

In your models.py file below, you will create seven models; Analytics, Event, RegisterEvent, LoginEvent, ViewPageEvent, EditProfileEvent, and LogoutEvent. Models are classes that define the way Django creates a table. The class name refers to a table name, and the class attributes refer to each table’s fields.

from django.db import models
import datetime

# Create your models here.
class Analytics(models.Model):
    name=name = models.TextField()
    stats= models.IntegerField(default=0)
    date= models.DateTimeField(default=datetime.datetime.now())

class Event(models.Model):
    name = models.TextField()
    username = models.TextField()
    ip = models.CharField(max_length=40)
    session = models.CharField(max_length=40)
    created = models.DateTimeField(default=datetime.datetime.now())

class RegisterEvent(models.Model):
    username = models.TextField()
    created = models.DateTimeField(default=datetime.datetime.now())

class LoginEvent(models.Model):
    username = models.TextField()
    created = models.DateTimeField(default=datetime.datetime.now())

class ViewPageEvent(models.Model):
    ip = models.CharField(max_length=40)
    session = models.CharField(max_length=40)
    created = models.DateTimeField(default=datetime.datetime.now())

class EditProfileEvent(models.Model):
    username = models.TextField()
    created = models.DateTimeField(default=datetime.datetime.now())

class LogoutEvent(models.Model):
    username = models.TextField()
    created = models.DateTimeField(default=datetime.datetime.now())

Here, the Event class defines the Event table to save the user’s actions on the site. The Analytics class defines the Analytics table to record the site’s analytics or numbers from the various events. As for the other model classes, we will utilize them later on in this tutorial to classify the events saved.

Building Dashboard Functionality with Django Views

The views.py file is where we will implement all the functionalities for your dashboard. Before diving into the fun part of the project, we're first going to go through each application view.

Importing the Required Django Libraries

At the very top, we need to initially import all of the Django libraries needed to create this application. If you're trying to create your own analytics application from scratch, you should still start with these libraries.

from django.shortcuts import render,redirect
from django.contrib.auth.models import User, auth
from django.contrib import messages
from . models import Event,RegisterEvent,LoginEvent,ViewPageEvent,EditProfileEvent,LogoutEvent,Analytics
from django.contrib.sessions.models import Session
import datetime

Recording Registrations in the Register View

Now, let's tell our application how to both handle user registrations and record them:

def register(request):
    if request.method=="POST":
        username=request.POST.get("username")
        email=request.POST.get("email")
        password=request.POST.get("pwd")
        if User.objects.filter(email=email).exists() or User.objects.filter(username=username).exists():
            messages.add_message(request, messages.INFO,"user with email already exists", "danger")
            return render(request,"register.html")
        else:
            user = User.objects.create(username=username, password=password, email=email)
            user.set_password(user.password)
            user.save()
            register_user=Event(name="Registered",username=username,created=datetime.datetime.now())
            register_user.save()
            messages.add_message(request, messages.INFO,"Registration Succesful", "danger")
    return render(request,"register.html")

In the Python code above, we are collecting the email, username, and password passed in the POST request. We will then use the username to check if the user’s data already exists in the database. If the data exists, we will display an error message. Conversely, if the inputted username does not match any entry currently in the database, we will save the new user’s data, create an event for the registration, and pass a success message back to the user. The register page should resemble the image below:

Handling Logins in a Django View

Now, as seen in the code below, we can implement the logic for the login system on this application:

def login(request):
    if request.method=="POST":
        email=request.POST.get("email")
        password=request.POST.get("pwd")
        username=User.objects.get(email=email).username
        user = auth.authenticate(username=username, password=password)
        if user is not None:
            auth.login(request, user)
            request.session["user"] = username
            login_user=Event(name="Logged In",username=request.session["user"],created=datetime.datetime.now())
            login_user.save()
            return redirect("App:index")
        else:
            messages.add_message(request, messages.INFO,"You have supplied invalid login credentials, please try again!", "danger")
            return render(request, 'login.html')
    return render(request,"login.html")

Here, we are collecting the email, username, and password passed in the POST request. We then use the username and password to authenticate the user. If the user’s data exists in the database, we redirect the user to the index page and record a login event. Otherwise, we show an error message. Your login page should be like the one in the image below:

Handling and Recording Logout Events with a Django View

We can now implement the logout system for our application using code that looks something like this:

def logout(request):
    logout_user=Event(name="Logged Out",username=request.session["user"],created=datetime.datetime.now())
    auth.logout(request)
    logout_user.save()
    messages.add_message(request, messages.INFO," you have been logged out", "danger")
    return redirect("App:login")

On a request to the logout view, we log the user out and display a logout message. Then, we redirect the user to the login page, while also creating an event for the logout action.

Creating the User Index View in Django

In this view, we are recording the page views for users’ pages (for each session) and allowing users to update their profile information. This is roughly what a rudimentary index view should look like:

def index(request):
    profile=request.user
    if not Event.objects.filter(session=request.session.session_key):
        view = Event(name="Viewed Page",ip=request.META['REMOTE_ADDR'],created=datetime.datetime.now(),session=request.session.session_key)
        view.save()
    if request.method=="POST":
        email=request.POST.get("email")
        username=request.POST.get("username")
        first_name=request.POST.get("first_name")
        last_name=request.POST.get("last_name")
        if email:
            profile.email=email
        if username:
            profile.username=username
        profile.first_name=first_name
        profile.last_name=last_name
        profile.save()
        edit=Event(name="Edited Profile",username=request.user,created=datetime.datetime.now())
        edit.save()
    context={"profile":profile}
    return render(request,"index.html",context)

In the Python code above, we are first checking if any data exists in the Event model that contains the same session key. If the query returns true, we will create an instance of the Event object with the session key and the current time.

Next, we are collecting the data needed to update the user’s profile—sent in a POST request—and then saving the data in the user’s profile whilst simultaneously recording an event for this edit action.

Finally, we are passing the user’s information to be rendered on the profile page in the context dictionary variable. The data points to be rendered are: the number of users, the number of page views, the number of active sessions, the number of inactive sessions, and the logout register.

Your index dashboard should look like the one in the image below:

Using Arctype to Query for User Events

Now that we have begun to populate our new database tables, We can easily find data in your events table using the event’s name. To do this, simply click "Create a new query" and run the SQL SELECT statement below:

SELECT * FROM app_event WHERE name='Edited Profile';

This query should return results that look something like this:

How to Group User Events in Django

As more users register, login, and explore your site, your events table will ultimately contain thousands of rows—recording different types of events. In order to more effectively run analytics on our data, we will need to group these events into separate tables.

#query to classify events into seperate tables
for i in Event.objects.all():
    if i.name == "Registered":
        if RegisterEvent.objects.filter(username=i.username,created=i.created):
            pass
        else:
            register=RegisterEvent(username=i.username,created=i.created)
            register.save()
    if i.name == "Logged In":
        if LoginEvent.objects.filter(username=i.username,created=i.created):
            pass
        else:
            login=LoginEvent(username=i.username,created=i.created)
            login.save()
    if i.name == "Viewed Page":
        if ViewPageEvent.objects.filter(ip=i.ip,session=i.session,created=i.created):
            pass
        else:
            page=ViewPageEvent(ip=i.ip,session=i.session,created=i.created)
            page.save()
    if i.name == "Edited Profile":
        if EditProfileEvent.objects.filter(username=i.username,created=i.created):
            pass
        else:
            edit=EditProfileEvent(username=i.username,created=i.created)
            edit.save()
    if i.name == "Logged Out":
        if LogoutEvent.objects.filter(username=i.username,created=i.created):
            pass
        else:
            logout=LogoutEvent(username=i.username,created=i.created)
            logout.save()

Here, we are running a loop through the events table and checking for the various event types before saving them into their respective model tables. However, before saving the data, we will check if the data already exists in the table. This loop will run through all the rows in the events table.

Setting Up Analytics Using an Arctype Dashboard

Of course, Django is a versatile framework through which you could save some amount of useful analytical data into a separate table. However, we can easily create more holistic and responsive analytics using an Arctype dashboard and a handful of queries. As a note, for the purpose of this example dashboard, I have populated each of my analytics tables with a few thousand rows of sample (conforming) data.

Creating and Saving Analytics Queries

Creating and saving queries in Arctype is as simple as clicking "Create a new query", entering your SQL code, naming the query, and clicking save. Here's an example:

In order to streamline dashboard creation for this guide, I will give you a few queries that are ready-to-use for your example dashboard. You can always create your own (cooler) queries later, but for now, you should save these four queries:

Total Stats

--Return four columns with the total events from each user event table
SELECT 
(SELECT COUNT(*) FROM app_loginevent) AS 'logins',
(SELECT COUNT(*) FROM app_logoutevent) AS 'logouts',
(SELECT COUNT(*) FROM app_registerevent) AS 'registrations',
(SELECT COUNT(*) FROM app_viewpageevent) AS 'pageviews';

Pageviews by Day

--Return the total profile page views from every day with user activity, output formatted dates
SELECT
  DATE_FORMAT(created, '%M %e, %Y') as 'Date',
  COUNT(*) AS 'Pageviews'
FROM app_viewpageevent
GROUP BY Date;

User Events by Day

--Return total user events of each type for days when any event is recorded.
SELECT 
  DATE_FORMAT(dates.uf_date, '%M %e, %Y') as 'Date', 
  (SELECT
    COUNT(*) FROM app_registerevent
    WHERE DATE(app_registerevent.created) = dates.uf_date
   ) AS 'Registrations', 
  (SELECT
    COUNT(*) FROM app_loginevent
    WHERE DATE(app_loginevent.created) = dates.uf_date
   ) AS 'Logins', 
  (SELECT
    COUNT(*) FROM app_logoutevent
    WHERE DATE(app_logoutevent.created) = dates.uf_date
   ) AS 'Logouts'
FROM
  ((SELECT DATE(created) AS uf_date FROM app_registerevent)
  UNION
  (SELECT DATE(created) AS uf_date FROM app_loginevent)
  UNION
  (SELECT DATE(created) AS uf_date FROM app_logoutevent)) as dates
ORDER BY dates.uf_date;

Event Breakdown on Day

--Return total events of all types on dynamically linked date
SELECT
  'Registrations' AS 'Event',
  COUNT(*) AS 'Count'
  FROM app_registerevent
  WHERE DATE_FORMAT(app_registerevent.created, '%M %e, %Y') = {{input_date}}
UNION ALL
SELECT
  'Logins' AS 'Event',
  COUNT(*) AS 'Count' 
  FROM app_loginevent
  WHERE DATE_FORMAT(app_loginevent.created, '%M %e, %Y') = {{input_date}}
UNION ALL
SELECT
  'Logouts' AS 'Event',
  COUNT(*) AS 'Count' 
  FROM app_logoutevent
  WHERE DATE_FORMAT(app_logoutevent.created, '%M %e, %Y') = {{input_date}};

Now that we have our queries ready to go, let's start building our dashboard!

Creating a Dashboard for Your Database

To create an Arctype dashboard, simply click "Create a new dashboard". You will be presented with a blank canvas—this is where the fun begins.

We'll start by creating scorecard-style totals for each type of user event. This can be accomplished by simply dragging the "chart" box from the sidebar out into the dashboard canvas. After the chart has been created, point it towards our Total Stats query, select "Scorecard" from "Chart type" and one of the four user event columns into the "Display Text" box like this:

For this dashboard, we'll make four of these scorecards to display each user event type. After making all the scorecards, we can spruce things up by adding some header text:

Adding Charts to an Arctype Dashboard

Next, let's create an area chart that displays user profile page views over time. To do this, drag out another chart, select our Pageviews by Day query, select the area chart option from "Chart Type", and drag one column to each axis. You'll want the date to display on the x-axis for this chart:

Let's do one more chart to display all user events over time using either a bar chart or stacked bar chart:

The stacked bar chart also achieves an incredible effect:

Creating a Chart with Dynamically-Linked Values in a Dashboard

Finally, let's use dynamically-linked values to create a doughnut chart that changes based upon a table selection. To do this, we'll first create a table using the same query as our bar chart (User Events by Day) :

Because we don't need every column of this table to display, we can also clean things up using options in the "Advanced" tab on the right sidebar.

As you can see, each row in the table is selectable. This is where the {{input_date}} dynamic value from our query will come in handy. First, we will create a new chart element getting data from Event Breakdown on Day. Then, in the "input_date" field, we can select a link to selectedRow.date, which will automatically update the date queried for our doughnut chart results whenever a new date in the table is selected.

After a bit of sprucing up, your dashboard will look something like this:

Conclusion

Django's versatility makes it the ideal framework for developers endeavoring to collect website analytics, which can then be visualized and analyzed using Arctype's dashboard functionality—truly a perfect synergy.