ComboStrap Analytics - How to query the analytics database

Undraw My Documents

How-to

This page will show you how to query the database online.

Steps

Go to the admin page

Click on the Admin link to go to the Admin Window. ie https://yourdomain.com/?do=admin

Go to the Sqlite Interface

The SQLite interface is a webpage that permits to send statement to the SQLite database.

Sqlite Interface

To access it directly, the URL is https://yourdomain.com/?do=admin&page=sqlite

Choose the database

Choose the Combo database.

Database Combo Sqlite Interface

If you were users:

  • of the 404Manager, the database may be named 404manager
  • of the webcomponent plugin, the database may be named webcomponent

The URL to access your database will look like the following URL

https://yourdomain.com/?do=admin&page=sqlite&db=combo&version=sqlite3

Enter your query and submit

Enter the analytical query and submit.

Sqlite Interface Query

You can try the below query that returns statistics over words.

select
	count(*) as page_count,
	round(word_count) as word_avg,
	sum(word_count) as word_count,
	max(word_count) as word_max,
	min(word_count) as word_min
from
	(
	select
		id,
		word_count
	from
		pages
	)

Advanced: Json Support

If you want to query, the analytics json data, you need to have sqlite with json support.

This steps shows you how to validate that your SQLite database can query JSON.

  • The sqlite database version should then be greater or equal to SQLite 3.9.0 (2015-10-14).
select sqlite_version();
^ sqlite_version ^
| 3.28.0 |

  • The json module should be present when executing the below SQL.
PRAGMA compile_options; 
-- or
select * from pragma_compile_options() where compile_options = 'ENABLE_JSON1';
^ compile_options ^
| ENABLE_JSON1 |

If you have Json enable, you can extract all information in the analytics file.

For instance, the above query can be translated to this query with the json analytics column

select
	count(*) as page_count,
	round(word_count) as word_avg,
	sum(word_count) as word_count,
	max(word_count) as word_max,
	min(word_count) as word_min
from
	(
	select
		id,
		json_extract(analytics , '$.statistics.words') as word_count
	from
		pages
	)

If you don't have the JSON extension or can't install it, you can always download the database file and query it locally for instance with dbeaver




Showcase yourself and your brand

Get free news, tips, and tricks
to create a remarkable experience for your readers.




Recommended Pages
Undraw My Documents
A broken link is a link that points to a non-existing page or media

This page will show you how to monitor the list of broken links for your ComboStrap website
Undraw My Documents
ComboStrap - Analytics

analytics is a module that gives the possibility to query analytical data against a database in order to get insight on your website: quality statistics metadata and redirections There is a lot...
ComboStrap Analytics - Data (JSON)

ComboStrap performs regularly analytical calculation on pages. The analytical data are composed of three parts: metadata: the page metadata statistics: the page statistics such as late publication...
Undraw My Documents
ComboStrap Analytics - Page Statistics

The statistics feature allows you to query your page statistics such as word count, internal, backlinks and get insight on your whole website at once.
Undraw My Documents
ComboStrap Analytics - Database

All ComboStrap data are stored in a Sqlite Database that you can query online The database schema has the following tables: pages - data on pages: page_id: the page id canonical: the canonical...
Undraw Through The Park
ComboStrap Routing - Log redirections History

ComboStrap Routing - Log redirections History This page shows you how you can query the redirection history table stored in the secondary database All redirections from the last 5 minutes: ...
Undraw Through The Park
ComboStrap URL - Page Alias

URL A page alias is an extra path value for a page. If a page has an alias that corresponds to the path of a missing page, a redirection is performed. You can see them as symlink or shortcut for a...
Undraw My Documents
How ComboStrap analyzes and replicates data ?

The analysis and replication of a webpage are the cornerstones of the quality and performance of a page. This page explains how this is done.
How to define your data set with Sql

This page defines the sql that can be used to retrieve data from pages
Undraw Through The Park
What are permanent redirects ?

This articles explains you what a permanent redirect is and when they are executed



Task Runner