How to build a simple blog analytics dashboard using PHP and MySQL

A blog analytics dashboard gives you a practical view of what visitors do on your website. Instead of relying entirely on a third-party platform, you can collect selected events yourself, store them in MySQL, and display useful summaries through a private PHP admin page.

This approach suits a small blog, digital store, affiliate website, or content platform that needs basic reporting without the cost and complexity of a large analytics suite. You can track page views, referral sources, devices, popular articles, and activity by day.

The project is also a useful PHP and MySQL exercise. It covers database design, form handling, AJAX requests, prepared statements, date filtering, and simple dashboard charts. The finished system will not replace enterprise analytics software, but it can answer the questions that matter most to a growing site.

For an Australian audience, local reporting can be especially useful. You might compare visitors from Sydney, Melbourne, Brisbane, and regional areas, monitor traffic during AEST business hours, or see whether a campaign performs better for mobile users on Telstra, Optus, or Vodafone connections.

Decide what the dashboard should measure

Start with a narrow set of metrics. A basic blog reporting tool should tell you which pages receive attention, how many visits occurred during a selected period, where visitors came from, and what devices they used. Recording every possible detail creates unnecessary privacy, storage, and maintenance problems.

A simple event model can capture one row for each page view. Useful fields include the visited URL, page title, referrer, user agent, IP address or a privacy-safe hash, visitor session identifier, and timestamp. If you later add downloads, outbound clicks, or newsletter sign-ups, an event_type column can distinguish those actions.

You should also decide whether a “visit” means every page load or a session-based visit. Counting page views is easier and more transparent. A session can be defined as activity from the same anonymous identifier within a 30-minute period, although this will always be an estimate.

Before collecting data, check your privacy obligations. Australian websites may need to consider the Privacy Act, their privacy policy, cookie disclosures, and any consent requirements that apply to their audience or business model. Avoid storing raw personal information unless the dashboard genuinely needs it.

Create the MySQL data model

Create a database table that is flexible enough for a small blog but not overloaded with unnecessary columns. The following structure stores the core information:

CREATE TABLE analytics_events (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    event_type VARCHAR(30) NOT NULL DEFAULT 'page_view',
    page_url VARCHAR(500) NOT NULL,
    page_title VARCHAR(255) NULL,
    referrer_url VARCHAR(500) NULL,
    visitor_hash CHAR(64) NULL,
    device_type VARCHAR(20) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_created_at (created_at),
    INDEX idx_page_url (page_url(191)),
    INDEX idx_event_type (event_type)
);

The indexes improve date, page, and event queries as the table grows. A MySQL installation using utf8mb4 should be configured at database and connection level so article titles and referral URLs containing international characters are stored correctly.

For basic privacy protection, do not save a full IP address as a long-term identifier. You could hash the IP address with a rotating site-specific salt, although even hashed information can still be treated as personal data in some situations. Another option is to omit it and use a temporary session token.

Keep connection settings outside publicly accessible files. A PHP data access file might use PDO with exceptions and prepared statements:

$pdo = new PDO(
    'mysql:host=localhost;dbname=blog_stats;charset=utf8mb4',
    $_ENV['DB_USER'],
    $_ENV['DB_PASS'],
    [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
    ]
);

Capture page views safely

You can record a page view when a blog page loads, either by calling a small PHP endpoint server-side or by sending a fetch() request from the browser. The browser method is convenient for a WordPress-style blog or a separate frontend because it does not delay the main page response.

A tracking endpoint should accept only expected values, limit field lengths, and use a prepared insert query. Never trust a title, URL, or referrer supplied by the browser. A simplified endpoint could look like this:

$pageUrl = filter_input(INPUT_POST, 'page_url', FILTER_SANITIZE_URL);
$title = trim($_POST['page_title'] ?? '');

if (!$pageUrl || strlen($title) > 255) {
    http_response_code(422);
    exit('Invalid data');
}

$stmt = $pdo->prepare(
    'INSERT INTO analytics_events
     (page_url, page_title, referrer_url, device_type)
     VALUES (:url, :title, :referrer, :device)'
);

$stmt->execute([
    ':url' => $pageUrl,
    ':title' => mb_substr($title, 0, 255),
    ':referrer' => mb_substr($_SERVER['HTTP_REFERER'] ?? '', 0, 500),
    ':device' => detectDevice($_SERVER['HTTP_USER_AGENT'] ?? '')
]);

In production, add CSRF protection where appropriate, rate limiting, bot filtering, and origin checks. A tracking endpoint exposed without controls can be flooded with fake events, making the dashboard unreliable. Exclude your own admin IP or authenticated staff sessions if internal visits should not affect content reports.

A small JavaScript snippet can send the event after the page becomes available:

fetch('/analytics/collect.php', {
  method: 'POST',
  headers: {'Content-Type': 'application/x-www-form-urlencoded'},
  body: new URLSearchParams({
    page_url: location.pathname,
    page_title: document.title
  })
});

Build useful summary queries

The dashboard should turn raw events into clear summaries. To show daily page views for the last 30 days, use a grouped date query:

SELECT DATE(created_at) AS visit_date, COUNT(*) AS views
FROM analytics_events
WHERE event_type = 'page_view'
  AND created_at >= DATE_SUB(CURDATE(), INTERVAL 29 DAY)
GROUP BY DATE(created_at)
ORDER BY visit_date ASC;

For popular content, group by URL and title. Add a date range selected by the administrator rather than placing a fixed period in every query. Always validate the requested dates and bind them as parameters.

SELECT page_url, MAX(page_title) AS title, COUNT(*) AS views
FROM analytics_events
WHERE event_type = 'page_view'
  AND created_at BETWEEN :start_date AND :end_date
GROUP BY page_url
ORDER BY views DESC
LIMIT 20;

A useful dashboard can show four summary cards: total views, unique visitor estimates, top article, and leading referral source. For charts, pass PHP query results to a lightweight JavaScript library such as Chart.js, or render a simple HTML bar graph if you want to avoid another dependency.

Metrics worth tracking first

Do not confuse traffic volume with business value. A tutorial that attracts fewer visitors may generate more email sign-ups or product sales than a widely shared opinion post. Add event tracking for meaningful actions when the basic page-view system is stable.

Add filters and a private admin view

A useful dashboard needs filters for dates, page URLs, event types, and device categories. A simple form can submit start_date and end_date to dashboard.php, where PHP validates the YYYY-MM-DD format before running parameterised queries.

Protect the admin page with authentication. At minimum, use PHP sessions, password hashes created with password_hash(), login throttling, and secure cookies. Do not place database credentials or reporting pages inside a folder that is publicly browsable.

For an Australian content business, timezone handling matters. Store timestamps in UTC, then convert them for display in Australia/Sydney or the relevant local zone. This prevents confusion when a campaign goes live in the afternoon in Sydney but database reports are grouped according to server time.

Your dashboard can also help assess local marketing activity. A post promoted to readers in Melbourne may show a different engagement pattern from one aimed at Brisbane or Perth. If you run email campaigns or paid adverts, add UTM parameters and store the campaign source, medium, and name rather than guessing from referrer URLs.

Improve accuracy without overbuilding

Analytics data is never perfect. Ad blockers can prevent tracking scripts from loading, privacy tools can hide referrers, bots can create artificial page views, and several people may share one network. Describe your numbers as estimates and use consistent definitions across reports.

You can improve data quality by excluding known crawlers, ignoring requests for missing pages, and using a short-lived first-party session cookie. Do not fingerprint browsers or collect unnecessary identifying details. A dashboard that stores less data is easier to secure and explain.

If your site publishes SEO tutorials or digital products, connect analytics with search and conversion reporting. For example, resources about free SEO tools could be measured by article views, tool launches, returning visitors, and sign-ups instead of page views alone.

Practical launch checks

Begin with one tracking event and a few dependable reports. Once the dashboard has been used for a fortnight, review which numbers actually guide publishing, promotion, or product decisions. Then add conversions, campaign tags, geographic summaries, or export tools only when there is a clear operational reason.

A lightweight PHP and MySQL dashboard can become a valuable internal tool for an Australian blog, whether the audience is concentrated around Sydney and Melbourne or spread across regional communities. Create the database, secure the collection endpoint, build the first reports, and use the resulting evidence to improve your next article and marketing campaign.