Skip to content

Database options and maintenance

4Analytics may store a lot of data if your site has a lot of traffic, or after you have used it for a long time. It is designed to do just that, and this design includes getting read of unused data as needed.

Use an external database

For the really, really large and busy sites, it may be more convenient to offload analytics data to another database, or even another server entirely. 4Analytics can also do that, please see Use an external database below.

You can adapt 4Analytics default behavior to your requirements using the settings and tools on the Configuration | Database page.

Retention and privacy

Most privacy legal framework, such as the GDPR, state that you should keep data only as long as you need it. This is however for personally identifiable data, stored with user consent.

There should not be any restriction on how long you can keep 4Analytics data, at least from a legal point of view. But as usual, we're not lawyer and you should make sure your choice of how long you keeop data complies with your own specific legal requirements.

2 types of data and how they differ

4Analytics stores 2 types of data:

  • Raw data: the individual data records of each single page viewed by a visitor on your site

  • Aggregate data: based on raw data, 4Analytics regularly computes aggregate data. For instance, it groups togeter all pages viewed on a given day into one record per day.

    Then it does the same for each week, each month and lastly for each year. Aggregate data is what you see on your screen when looking at 4Analytics reports.

The following should be kept in mind:

  • Aggregate data is "just" computed based on raw data. Therefore, we can delete all aggregate data at any time, and as long as we still have the raw data, the individual page view records, 4Analytics can always rebuild the aggregate data.
  • If deleted, raw data cannot be rebuilt. Raw data are the basic bricks, if we delete the record that says that this visitor viewed this page on this date, at this time, with this browser,... then that information is lost.
  • Raw data uses up a lot more space than aggregate data. There is only one record per day for aggregate data, while if you had 1000 page-views that day, there will be 1000 raw data records in the database
  • Aggregate data is what you are interested in, and likely want to keep longer

This is why 4Analytics has a retention policy that decides how long each type of data should be kept.

Database retention policy

Database data retention policy configuration

These settings define how long 4Analytics will keep each type of data. Deletion happens in the background, on a regular basis.

Keep data forever

Contrary to common analytics service, or solutions where Personally Identifiable Information is stored, you can keep analytics data for as long as you want, allowing long term comparisons.

For reference, Google Analytics retention period for user-level information is up to 14 months only.

You do not need to do anything about data deletion, this is all handled by 4Analytics based on the settings you chose.

In fact, we strongly advise that you do not try to directly alter the 4Analytics database tables, or else you risk rendering it inconsistent, providing incorrect reports.

Database maintenance

Database maintenance operations

In a few exceptional cases, you may have to perform database maintenance operations. Two of them are possible:

Rebuild aggregate data

Rebuilding aggregate data from raw data may be needed on rare occasions, but the most common one is if you purposedly delete all raw data.

Rebuilding aggregate data after deleting raw data resets everything to zero. You may want this for instance after completing a website development and testing is over. By deleting raw data and aggregate data, you ready the site for actual live operation

Another common use case, without deleting raw data this time, is if you change some settings related to how reports are calculated, in which case you need to rebuild aggregate data so that these new settings are taken into account.

Delete all raw data

Deleting all raw data should likely only happen when testing or developing the site. Once the site is live, in production, raw data will be managed through the Retention policy set above and manual deletion should not be needed.

Password confirmation

As these operations can lead to data loss if used by mistake, both operations will first prompt your for a confirmation, and then ask for Joomla user password to finally trigger the operation:

Password confirmation dialog for dangerous operations

Use an external database

If you run a very large site, with a lot of traffic, your database server may already be nearing its capacity, either in terms of volume of data stored, or performance.

For these extreme cases, 4Analytics can store its data tables (raw data and aggregate data) onto another server.

Be cautious about this

You can switch to an external database, and come back to the previous configuration, simply and at any time, but you have to be aware of where your data is located.

If you start with the default and store analytics data into Joomla database, then later switch to an external database, the new server will have no data. It will be as if you started from scratch.

What happens to existing data?

  1. When switching to an external database, all data on the previously used server is safely left untouched
  2. If switching back to the original server, all data on the external server is also safely left untouched
  3. BUT 4Analytics will not synchronize or copy data across, this is your responsibility (see below)

How to switch

Start by filling-in the connection details for the external database to use. They are the same settings you already had to provide for your main site database server (host, database name, etc), but obviously should be for another database:

  • the external database can be on the same server, or another one
  • if another database server, your web server (the one where the Joomla PHP code runs) must be able to connect this server

Once parameters are there, toggle the Select database to use selector from Now using the website normal database to Now using an external database.

This is an important operation, so you will be prompted first for a confirmation and then to finally validate the change with your Joomla user password.

After you finally confirm, 4Analytics will:

  • try to connect to the server using the provided credentials
  • verify the required database tables exists on the selected server and create them if not
  • start directing all storage, aggregation and reporting operations to that server

Copying database tables to preserve historical information

If you intend to switch to an external database but have already used 4Analytics for a while, you certainly want to preserve your historical data.

This cannot be done within 4Analytics or Joomla, and you will have to copy the following database tables manually from the current server to the new one.

Tables to copy
#####_foranalytics_agg_daily
#####_foranalytics_agg_monthly
#####_foranalytics_agg_weekly
#####_foranalytics_agg_yearly
#####_foranalytics_live
#####_foranalytics_views
#####_foranalytics_visits

Use your own prefix instead of #####

In the above table, the string ##### represents what Joomla calls the Database Tables Prefix. It is unique to your website.

While you can change the prefix when copying tables to the external server, we suggest you keep it the same as the one you already use. You can find it in your Joomla website Global configuration, under the Server tab.

 

Only copy these tables

4Analytics only uses the external database when working with the tables listed above.

For all other tables, the main Joomla website database server is always used. For instance, the 4Analytics configuration table (#####_foranalytics_config) always stays on the main server, whether you are using an external database or not.