Map throws an exception after Google Analytics import

[tssge]

Past Issues Searched

• [x] I have searched open and closed issues to make sure that the bug has not yet been reported

Issue is a Bug Report

• [X] This is a bug report and not a feature request, nor asking for self-hosted support

Using official Plausible Cloud hosting or self-hosting?

Self-hosting

Describe the bug

The map fails to load after Google Analytics data is imported for the site. An exception is raised by Plausible:

10:23:28.395 [error] #PID<0.5408.0> running PlausibleWeb.Endpoint (connection #PID<0.5405.0>, stream id 1) terminated
Server: localhost:8000 (http)
Request: GET /api/stats/bojoko.com/countries?period=30d&date=2022-06-29&filters=%7B%7D&with_imported=true&limit=300
** (exit) an exception was raised:
    ** (Clickhousex.Error) Code: 53, e.displayText() = DB::Exception: Type mismatch of columns to JOIN by: country FixedString(2) at left, s1.country String at right (version 21.3.2.5 (official build))

        (ecto_sql 3.8.0) lib/ecto/adapters/sql.ex:928: Ecto.Adapters.SQL.raise_sql_call_error/1
        (ecto_sql 3.8.0) lib/ecto/adapters/sql.ex:843: Ecto.Adapters.SQL.execute/6
        (ecto 3.8.1) lib/ecto/repo/queryable.ex:221: Ecto.Repo.Queryable.execute/4
        (ecto 3.8.1) lib/ecto/repo/queryable.ex:19: Ecto.Repo.Queryable.all/3
        (plausible 0.0.1) lib/plausible/stats/breakdown.ex:202: Plausible.Stats.Breakdown.breakdown_sessions/5
        (plausible 0.0.1) lib/plausible_web/controllers/api/stats_controller.ex:579: PlausibleWeb.Api.StatsController.countries/2
        (plausible 0.0.1) lib/plausible_web/controllers/api/stats_controller.ex:1: PlausibleWeb.Api.StatsController.action/2
        (plausible 0.0.1) lib/plausible_web/controllers/api/stats_controller.ex:1: PlausibleWeb.Api.StatsController.phoenix_controller_pipeline/2

This is due to mismatch between data types of Clickhouse table events column country_code which is being joined with imported_locations column country. country_code is of type LowCardinality(FixedString(2)) but country is of type String. Also in events table city_geoname_id is of type UInt32 while in imported_locations table city is of type UInt64.

This can be probably fixed by migration, but I don't have the skills to create a proper one with Ecto/Elixir.

I did:

CREATE TABLE plausible_events_db.imported_locations2(
  `site_id` UInt64, 
  `date` Date, 
  `country` LowCardinality(
    FixedString(2)
  ), 
  `region` String, 
  `city` UInt32, 
  `visitors` UInt64, 
  `visits` UInt64, 
  `visit_duration` UInt64, 
  `bounces` UInt32
) ENGINE = MergeTree 
ORDER BY 
  (
    site_id, date, country, region, city
  ) SETTINGS index_granularity = 8192;

INSERT INTO imported_locations2 SELECT * FROM imported_locations;

RENAME TABLE imported_locations TO imported_locations_old;

RENAME TABLE imported_locations2 TO imported_locations;

This fixes the issue, but of course is not a proper fix.

Expected behavior

The map should load as expected without Error 500.

Screenshots

Environment

- OS: Ubuntu 22.04 LTS
- Browser: Chrome
- Browser Version: 102.0.5005.115

[metmarkosaric]

Thanks for the feedback @tssge! GA import is not supported in the latest version of our self-hosted release. It was never supported by self-hosted in fact so you must be using some unofficial package which explains the errors. The latest and the only supported version of self-hosted is 1.4.4: https://github.com/plausible/analytics/releases/tag/v1.4.4. We plan to introduce GA import into the self-hosted release at some point later this year. Thanks!

[DarthMikke]

Hi @tssge ! Thank you for this snippet -- I experienced the same bug, it was awesome to finally find a way to fix it. I made a quick guide on how to implement it in a self hosted Plausible installation, and put it in a repository in case anyone else was wondering.