Build your own Google Analytics easily using PHP
In this tutorial, you will be learning to create your own Google Analytics like tool using PHP.
Google Analytics is a powerful web analytics service offered by Google that allows website owners and marketers to track and analyze various aspects of their website or app traffic.
We need 4 files :
- get.php => contains SQL queries that will be used by the index.php to show data
- index.php => dashboard page to see realtime stats
- track.js => JavaScript file that is included in the webpage whose analytics is to be tracked
- track.php => PHP script that receives analytics data from webpage and stores in database
MySQL Table for your own Google Analytics
Create a table “visits” with following SQL code
CREATE TABLE `visits` ( `id` int(11) NOT NULL, `path` varchar(255) NOT NULL, `title` varchar(255) NOT NULL, `device` varchar(25) NOT NULL, `source` varchar(255) NOT NULL, `user_session` varchar(100) NOT NULL, `created_at` timestamp NOT NULL DEFAULT current_timestamp(), `country` varchar(255) NOT NULL, `city` varchar(255) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; ALTER TABLE `visits` ADD PRIMARY KEY (`id`); ALTER TABLE `visits` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT; COMMIT;
Follow this video for complete guidance :
Complete Source Code
track.js
(() => {
var postData = {
'path' : window.location.pathname,
'title': document.title,
'device':getDeviceType(),
'source':getTrafficSource(),
'user_session':getUserSession(),
'country':getCountry(),
'city':getCity()
};
postData = JSON.stringify(postData);
xhr = new XMLHttpRequest();
xhr.open('POST', 'http://localhost/ana/track.php', true);
xhr.setRequestHeader('Content-type', 'application/json; charset=UTF-8');
xhr.send(postData);
xhr.onload = function () {
if(xhr.status === 200) {
response = JSON.parse(xhr.response);
console.log(response);
}
}
function getDeviceType() {
const userAgent = navigator.userAgent;
if (/android|iphone|ipad|ipod|mobile|mini|windows\sce|palm/i.test(userAgent)) {
return "Mobile";
}
if (/tablet|ipad|playbook|silk|android(?!.*mobi)/i.test(userAgent)) {
return "Tablet";
}
return "Desktop";
}
function getTrafficSource() {
const referrer = document.referrer;
const searchEngines = ["google", "bing", "yahoo", "duckduckgo", "baidu"];
const socialNetworks = ["facebook", "twitter", "linkedin", "instagram", "pinterest"];
if (!referrer) {
return "Direct";
}
const referrerHost = new URL(referrer).hostname.toLowerCase();
for (const engine of searchEngines) {
if (referrerHost.includes(engine)) {
return "Organic : "+referrerHost;
}
}
for (const network of socialNetworks) {
if (referrerHost.includes(network)) {
return "Social : "+referrerHost;
}
}
return "Referral :" + referrerHost;
}
function getUserSession(){
if(localStorage.getItem('user_session')){
return localStorage.getItem('user_session');
}else{
session_id = generateRandomString(25);
localStorage.setItem('user_session',session_id);
return session_id;
}
}
function generateRandomString(length) {
const characters = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789';
let result = '';
const charactersLength = characters.length;
for (let i = 0; i < length; i++) {
result += characters.charAt(Math.floor(Math.random() * charactersLength));
}
return result;
}
function getCountry(){
return 'Nepal';
}
function getCity(){
return 'Kathmandu';
}
})();
track.php
<?php
$conn = mysqli_connect('HOST','USER','PASSWORD','DATABASE_NAME');
date_default_timezone_set('Asia/Kathmandu');
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
$data = json_decode(file_get_contents('php://input'),true);
$path = $data['path'];
$title = $data['title'];
$device = $data['device'];
$source = $data['source'];
$user_session = $data['user_session'];
$created_at = date('Y-m-d H:i:s');
$country = $data['country'];
$city = $data['city'];
$sql = "insert into visits(path,title,device,source,user_session,created_at,country,city) values('$path','$title','$device','$source','$user_session','$created_at','$country','$city')";
mysqli_query($conn,$sql);
}
index.php
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/bootstrap/5.3.3/css/bootstrap.min.css">
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/leaflet/1.9.4/leaflet.min.css" />
<script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.7.1/jquery.min.js"></script>
<style type="text/css">
.box{
background: beige;
height: 200px;
padding: 20px;
position: relative;
}
#map {
height: 100%;
}
.leaflet-control-attribution{
display: none;
}
</style>
<div class="container-fluid">
<div class="header text-center pt-3">
<h1>Realtime Website Analytics</h1>
</div>
</div>
<hr>
<div class="container">
<div class="row">
<div class="col-md-3">
<div class="box user-count fs-3 d-flex text-center justify-content-center align-items-center">
<div>
<div id="active-users"></div>
<div class="fs-5">Active Users</div>
</div>
</div>
</div>
<div class="col-md-6">
<div class="box pb-0">
<small class="position-absolute" style="padding:0px 10px;background:#444;color:#fff;top:0;left:50%;transform: translate(-50%,0);">Page Views in Last 30 minutes</small>
<canvas id="realtime-chart"></canvas>
</div>
</div>
<div class="col-md-3">
<div class="box p-0">
<div id="map"></div>
</div>
</div>
</div>
<div class="mt-5">
<div class="row">
<div class="col-md-8">
<div class="box">
<h3 class="text-center mb-4">Active Pages</h3>
<table class="table table-bordered" id="active-pages">
<thead>
<tr>
<th width="80%">Top Active Pages</th>
<th>Views</th>
</tr>
</thead>
<tbody>
</tbody>
</table>
</div>
</div>
<div class="col-md-4">
<div class="box">
<h3 class="text-center mb-4">Traffic Source</h3>
<table class="table table-bordered" id="traffic-source">
<thead>
<tr>
<th width="80%">Top Active Pages</th>
<th>Views</th>
</tr>
</thead>
<tbody>
</tbody>
</table>
</div>
</div>
</div>
</div>
</div>
<script src="https://analytics.youthsforum.com/themes/altum/assets/js/libraries/Chart.bundle.min.js"></script>
<script src="https://analytics.youthsforum.com/themes/altum/assets/js/chartjs_defaults.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/leaflet/1.9.4/leaflet.min.js"></script>
<script type="text/javascript">
function refresh(){
$.ajax({
url:'get.php',
type:'get',
success:function(res){
res = JSON.parse(res);
$("#active-users").html(res.active_users);
tr = '';
for(i=0;i<res.active_pages.length;i++){
tr += '<tr>'+
'<td>'+res.active_pages[i].title+'</td>'+
'<td>'+res.active_pages[i].page_views+'</td>'+
'</tr>';
}
$("table#active-pages tbody").html(tr);
initBar(res.active_minutes);
tr = '';
for(i=0;i<res.traffic_sources.length;i++){
tr += '<tr>'+
'<td>'+res.traffic_sources[i].source+'</td>'+
'<td>'+res.traffic_sources[i].users+'</td>'+
'</tr>';
}
$("table#traffic-source tbody").html(tr);
}
});
}
setInterval(refresh,5000);
var map = L.map('map', {
zoomControl: false, // Disable default zoom control
continuousWorld: false
}).setView([27.7172, 85.3240], 1);
function addCountryMarker(countryName, latlng) {
var marker = L.marker(latlng).addTo(map);
marker.bindPopup('<b>' + countryName + '</b>');
}
// Example: Adding a marker for Nepal
var nepalLatLng = [28.3949, 84.1240]; // Example coordinates for Nepal
addCountryMarker('Nepal : 4', nepalLatLng);
L.tileLayer('https://{s}.tile.openstreetmap.org/{z}/{x}/{y}.png', {
attribution: false // Disable default attribution
}).addTo(map);
var ctx = document.getElementById("realtime-chart").getContext('2d');
function initBar(data){
var myChart = new Chart(ctx, {
type: 'bar',
data: {
labels: ["", "", "", "", "", "", "", "", "", "","", "", "", "", "", "", "", "", "", "","", "", "", "", "", "", "", "", "", ""],
datasets: [{
label: '', // Empty string to hide the dataset label
data: data,
fill: false,
borderColor: '#2196f3',
backgroundColor: '#2196f3',
borderWidth: 1,
barPercentage : 1.24
}]
},
options: {
responsive: true,
maintainAspectRatio: false,
plugins: {
legend: {
display: false // Disable the dataset label
},
tooltip: {
enabled: false // Disable tooltips
}
},
scales: {
y: {
beginAtZero:true,
ticks: {
stepSize:1
}
},
x: {
display: false
}
}
}
});
}
refresh();
</script>
get.php
<?php
$conn = mysqli_connect('HOST','USER','PASSWORD','DATABASE_NAME');
$sql = "SELECT count(distinct(user_session)) as active_users FROM visits WHERE created_at >= NOW() - INTERVAL 30 MINUTE";
$result = mysqli_query($conn,$sql);
$result = mysqli_fetch_assoc($result);
$response['active_users'] = $result['active_users'];
$sql = "SELECT *,count(id) as page_views FROM visits WHERE created_at >= NOW() - INTERVAL 30 MINUTE group by path";
$result = mysqli_query($conn,$sql);
$active_pages = array();
while($row = mysqli_fetch_assoc($result)){
$active_pages[] = $row;
}
$response['active_pages'] = $active_pages;
$sql = "WITH RECURSIVE IntervalSequence AS ( SELECT NOW() - INTERVAL 29 MINUTE AS interval_start UNION ALL SELECT interval_start + INTERVAL 1 MINUTE FROM IntervalSequence WHERE interval_start < NOW() ) SELECT DATE_FORMAT(IntervalSequence.interval_start, '%Y-%m-%d %H:%i') AS minute_interval, COUNT(visits.id) AS active_users_count FROM IntervalSequence LEFT JOIN visits ON visits.created_at >= IntervalSequence.interval_start AND visits.created_at < IntervalSequence.interval_start + INTERVAL 1 MINUTE GROUP BY minute_interval ORDER BY minute_interval ASC";
$result = mysqli_query($conn,$sql);
$active_minutes = array();
while($row = mysqli_fetch_assoc($result)){
$active_minutes[] = $row['active_users_count'];
}
$response['active_minutes'] = $active_minutes;
$sql = "select *,count(id) as users from visits WHERE created_at >= NOW() - INTERVAL 30 MINUTE group by source";
$result = mysqli_query($conn,$sql);
$traffic_sources = array();
while($row = mysqli_fetch_assoc($result)){
$traffic_sources[] = $row;
}
$response['traffic_sources'] = $traffic_sources;
echo json_encode($response);
exit;
Place this tracking code in the website you want to track
<script src="LINK_TO_TRACK.JS"></script>
Key Features of Google Analytics
- Traffic Analysis: It provides detailed insights into your website’s traffic sources, showing where visitors come from (referral sources, search engines, direct visits), which pages they visit, and how long they stay on your site.
- Audience Analysis: You can segment your audience based on various factors such as demographics (age, gender), interests, location, and behavior (new vs. returning visitors).
- Behavior Flow: This feature visualizes how users navigate through your site, showing the path they take from one page to another.
- Conversion Tracking: You can set up goals to track specific actions users take on your site, such as completing a purchase, signing up for a newsletter, or downloading a file. This helps in measuring the effectiveness of your marketing campaigns.
- E-commerce Tracking: For online stores, Google Analytics can track transaction data, revenue, and other e-commerce metrics, providing insights into sales performance.
- Real-Time Data: It offers real-time reporting, showing you active users on your site, their geographic locations, and the pages they are currently viewing.
- Customization: Google Analytics allows customization through custom dimensions and metrics, which can be used to track specific user interactions or events on your site.
- Integration: It integrates with other Google products such as Google Ads, Search Console, and Data Studio, allowing for deeper insights and cross-platform analysis.
- Reporting: It provides a range of pre-built reports and dashboards for quick insights, and you can also create custom reports tailored to your specific needs.
