SQL Query on Matomo Database to Calculate the Number of Visits to Each Page

Useful WebsitesI’ve been using for just short of four years now and have been pretty happy with it.

I generally consume the data it holds via the eweb portal, but I was wanting to extract some data to manipulate recently, in a way that the web portal didn’t allow, so I did a little poking around the database to work out the syntax needed to query the database directly in mySQL.

The below query returns all pages for the specified date range (the first two highlighted variables at the top), sorted in descending order of number of visits; the third parameter is the id of the site for which you want the data:

/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (http://www.azurecurve.co.uk) This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int). */
SET @StartDate = '2023-01-01'; SET @EndDate = '2023-12-31'; SET @SiteID = 1; SELECT name ,COUNT(name) AS COUNT FROM matomo_log_visit LEFT JOIN matomo_log_link_visit_action ON matomo_log_visit.idvisit = matomo_log_link_visit_action.idvisit LEFT JOIN matomo_log_action ON matomo_log_action.idaction = matomo_log_link_visit_action.idaction_name WHERE matomo_log_visit.idsite = @SiteID AND type = 4 AND matomo_log_link_visit_action.server_time >= @StartDate AND matomo_log_link_visit_action.server_time <= @EndDate GROUP BY NAME ORDER BY COUNT(NAME) DESC;

diagrams.net For Flow Chart Creation (Visio Replacement)

Useful WebsitesI’ve been a long time user of Microsoft Visio for creating flow charts and workflow diagrams. Someone recently suggested diagrams.net, an open source alternative to me as a site worth taking a look at.

I’m not really an advanced user of Visio and the site does allow me to create all of the diagrams I want and is flexible enough to allow me to format them as I want.

Diagrams.net home page

According to the about page, diagrams.net/draw.io is an open source technology stack for building diagramming applications, and the world’s most widely used browser-based end-user diagramming software.

Continue reading “diagrams.net For Flow Chart Creation (Visio Replacement)”

Check For Colour Accessibility (A11y)

Useful WebsitesI’ve posted about a few useful websites as a way of remembering and finding them again. Today I am posting another one which is about colour accessibility.

Back in September 2020, I posted about a site called ColorHexa which you can use to generate matching colour palettes. This is good as far as it goes, but for web design best practice is that websites need to be accessible.

This is recommended for ClassicPress developers to make sure their plugins and themes are accessible, but it is also required for designers and content producers.

The ColorHexa website will give you matching palettes, but many times you will want to pick your own colours or change from the ones supplied by a site like ColorHexa. That’s where the Adobe Color site can come in very useful as it has two tools for checking the accessibility of color.

Continue reading “Check For Colour Accessibility (A11y)”

Website Analytics With Matomo: Series Index

Useful WebsitesI’ve used a few different tools for tracking visits to my sites for a while, moving on from one tool when they made it less useful or when it stopped working. More recently I have started using Matomo which bills itself as a Google Analytics alternative that protects your data and your customers’ privacy.

In this series I am going to take a look at what Matomo is and how it is implemented and used. If you’re reading this post directly on azurecurve|Rambings of a Dynamics GP Consultant it will automatically update, otherwise you will need to .

Website Analytics With Matomo
What is Matomo?
Download Software
Prerequisites
Install
First Run
Add a Site
Update

Format XML

Useful WebsitesI posted a link to a website which will format your SQL code a while back and recently found myself looking for a way of formatting XML code to make it more readable.

One of the clients I was working with, had an integration sending data to eConnect to load into Microsoft Dynamics GP, and the XML was coming across as one long string and I needed to make it readable by breaking it over multiple lines. A quick search found me Free Online XML Formatter from freeformatter.com which includes a number of other tools which look useful for formatting/minifying/validating and so on.

Free Online XML Formatter - freeformater.com

Bulk watermark images

Useful WebsitesA couple of days ago, I posted about a website which allowed you to bulk resize images; that site has a sister site which allows images to be watermarked in bulk.

The Batch Watermark; you are able to use an image or text as the watermark and it will be applied to all of the uploaded photos:

Bulk watermark images

I am not a huge fan of watermarking images, but lots of people will use any photo posted online as if it was copyright free, so when I upload photos to my Coppr|Distilleries to Visit and Dark Forge | Tourist Attractions sites, I will apply a watermark to them so any other use of the photo will at least include the original source.

Bulk resize images

Useful WebsitesA few months ago I posted about the Tiny JPG site which can be used to shrink files for posting online; this site redcues the filesize of the image, but not the dimensions of the image.

I am building two sites at the moment (Coppr|Distilleries to Visit and Dark Forge | Tourist Attractions), onto which I will be uploading photographs of the places I have visited. Before uploading the photos, I need to resize the photos dimensions and to do this I need an application or at least I thought I needed an application, but I then found Bulk Resize Photos:

Bulk resize images

This site allows you to resize up to 150 photos at a time and you have control over how it resizes (based on height, width or longest side) while keeping the filesize below a certain size. Early experimentation suggests that this site does exactly what I need and does so without needing to install anything, meaning I can use it from any computer.

Generate matching colour palettes

Useful WebsitesI run a few websites now which means I have had a need to change colours on themes. However, being colourblind, this can be somewhat difficult to mke sure that the colours I am choosing will work together. One site I have found very useful is ColorHexa.com; this is a free color tool providing information about any color and generating matching color palettes for your designs (such as complementary, analogous, triadic, tetradic or monochromatic colors schemes).

The below screenshot shows part of the color palette generated for this sites main colour azure blue (#007FFF):

Color Hexa website

Formatting SQL

Useful WebsitesAt the start of March last year, I posted about a site you can use to format SQL by pasting it in or uploading a file. That site is still present and working, but I’ve found a new website which formats SQL more to my liking (thinking specifically of formatting some of the Microsoft Dynamics GP stored procedures).

The new site I’m using is SQL Formatter from DevArt:

SQL Formatter

The SQL in the above is from my SQL View to Return Budgets by Month post.

Using the options to the right, you can change how the SQL is formatted, so you can alter how the SQL is formatted to make it closer to your required output.

I prefer a web based solution as I sometimes need to format SQL when I’m working on a client system and don’t have access to my own laptop, so one of the tools which can install into SQL Server Management Studio aren’t always usable.

Reduce size of JPEG or PNG images

Useful WebsitesI’ve been doing some work on both my websites and the plugins I’ve developed for ClassicPress in order to make improvements. One of the areas I am aware of is that the images I am using are larger than they could be.

It is possible to compress PNG or JPEG images without any loss of quality. I’ve used a couple of tools to compress the screenshots I’ve already uploaded, without being especially happy with any of them, so I’m not going to recommend anything in that area.

However, I’ve been happier with an online tool called TinyJPG which you can use to compress images:

Tiny Jpg website

As you can see from the image above, a few images I ran through the tool resulted in an overall compression of 57%. This will save me both storage and bandwidth on the website. I am using the free version which allows you to upload up to 20 images up to 5MB in size at a time; they do have a paid service available which does not have these restrictions.