defmodule Plausible.Stats.Clickhouse do use Plausible use Plausible.Repo use Plausible.ClickhouseRepo use Plausible.Stats.Fragments import Ecto.Query, only: [from: 2] alias Plausible.Stats.Query alias Plausible.Timezones @spec pageview_start_date_local(Plausible.Site.t()) :: Date.t() | nil def pageview_start_date_local(site) do datetime = ClickhouseRepo.one( from(e in "events_v2", select: fragment("min(?)", e.timestamp), where: e.site_id == ^site.id, where: e.timestamp >= ^site.native_stats_start_at ) ) case datetime do # no stats for this domain yet ~N[1970-01-01 00:00:00] -> nil _ -> Timezones.to_date_in_timezone(datetime, site.timezone) end end def imported_pageview_count(site) do Plausible.ClickhouseRepo.one( from(i in "imported_visitors", where: i.site_id == ^site.id, select: sum(i.pageviews) ) ) end @spec imported_pageview_counts(Plausible.Site.t()) :: %{non_neg_integer() => non_neg_integer()} def imported_pageview_counts(site) do from(i in "imported_visitors", where: i.site_id == ^site.id, group_by: i.import_id, select: {i.import_id, sum(i.pageviews)} ) |> Plausible.ClickhouseRepo.all() |> Map.new() end def usage_breakdown([d | _] = domains, date_range) when is_binary(d) do Enum.chunk_every(domains, 300) |> Enum.reduce({0, 0}, fn domains, {pageviews_total, custom_events_total} -> {chunk_pageviews, chunk_custom_events} = ClickhouseRepo.one( from(e in "events", where: e.domain in ^domains, where: fragment("toDate(?)", e.timestamp) >= ^date_range.first, where: fragment("toDate(?)", e.timestamp) <= ^date_range.last, select: { fragment("countIf(? = 'pageview')", e.name), fragment("countIf(? != 'pageview')", e.name) } ) ) {pageviews_total + chunk_pageviews, custom_events_total + chunk_custom_events} end) end def usage_breakdown([sid | _] = site_ids, date_range) when is_integer(sid) do Enum.chunk_every(site_ids, 300) |> Enum.reduce({0, 0}, fn site_ids, {pageviews_total, custom_events_total} -> {chunk_pageviews, chunk_custom_events} = ClickhouseRepo.one( from(e in "events_v2", where: e.site_id in ^site_ids, where: fragment("toDate(?)", e.timestamp) >= ^date_range.first, where: fragment("toDate(?)", e.timestamp) <= ^date_range.last, select: { fragment("countIf(? = 'pageview')", e.name), fragment("countIf(? != 'pageview')", e.name) } ) ) {pageviews_total + chunk_pageviews, custom_events_total + chunk_custom_events} end) end def usage_breakdown([], _date_range), do: {0, 0} def top_sources_for_spike(site, query, limit, page) do offset = (page - 1) * limit {first_datetime, last_datetime} = utc_boundaries(query, site) referrers = from(s in "sessions_v2", select: %{ name: s.referrer_source, count: uniq(s.user_id) }, where: s.site_id == ^site.id, # Note: This query intentionally uses session end timestamp to get currently active users where: s.timestamp >= ^first_datetime and s.start < ^last_datetime, where: s.referrer_source != "", group_by: s.referrer_source, order_by: [desc: uniq(s.user_id), asc: s.referrer_source], limit: ^limit, offset: ^offset ) on_full_build do referrers = Plausible.Stats.Sampling.add_query_hint(referrers, 10_000_000) end ClickhouseRepo.all(referrers) end def current_visitors(site, query) do Plausible.ClickhouseRepo.one( from(e in base_query(site, query), select: uniq(e.user_id) ) ) end def has_pageviews?(site) do ClickhouseRepo.exists?( from(e in "events_v2", where: e.site_id == ^site.id and e.name == "pageview" and e.timestamp >= ^site.native_stats_start_at ) ) end @spec empty_24h_visitors_hourly_intervals([Plausible.Site.t()], NaiveDateTime.t()) :: map() def empty_24h_visitors_hourly_intervals(sites, now \\ NaiveDateTime.utc_now()) do sites |> Enum.map(fn site -> {site.domain, %{ intervals: empty_24h_intervals(now), visitors: 0, change: 0 }} end) |> Map.new() end @spec last_24h_visitors_hourly_intervals([Plausible.Site.t()], NaiveDateTime.t()) :: map() def last_24h_visitors_hourly_intervals(sites, now \\ NaiveDateTime.utc_now()) def last_24h_visitors_hourly_intervals([], _), do: %{} def last_24h_visitors_hourly_intervals(sites, now) do site_id_to_domain_mapping = for site <- sites, do: {site.id, site.domain}, into: %{} now = now |> NaiveDateTime.truncate(:second) placeholder = empty_24h_visitors_hourly_intervals(sites, now) previous_query = visitors_24h_total(now, -48, -24, site_id_to_domain_mapping) previous_result = previous_query |> ClickhouseRepo.all() |> Enum.reduce(%{}, fn %{total_visitors: total, site_id: site_id}, acc -> Map.put_new(acc, site_id, total) end) total_q = visitors_24h_total(now, -24, 0, site_id_to_domain_mapping) current_q = from( e in "events_v2", join: total_q in subquery(total_q), on: e.site_id == total_q.site_id, where: e.site_id in ^Map.keys(site_id_to_domain_mapping), where: e.timestamp >= ^NaiveDateTime.add(now, -24, :hour), where: e.timestamp <= ^now, select: %{ site_id: e.site_id, interval: fragment("toStartOfHour(timestamp)"), visitors: uniq(e.user_id), total: fragment("any(total_visitors)") }, group_by: [e.site_id, fragment("toStartOfHour(timestamp)")], order_by: [e.site_id, fragment("toStartOfHour(timestamp)")] ) on_full_build do current_q = Plausible.Stats.Sampling.add_query_hint(current_q) end result = current_q |> ClickhouseRepo.all() |> Enum.group_by(& &1.site_id) |> Enum.map(fn {site_id, entries} -> %{total: visitors} = List.first(entries) full_entries = (entries ++ empty_24h_intervals(now)) |> Enum.uniq_by(& &1.interval) |> Enum.sort_by(& &1.interval, NaiveDateTime) change = Plausible.Stats.Compare.percent_change(previous_result[site_id], visitors) || 100 {site_id_to_domain_mapping[site_id], %{intervals: full_entries, visitors: visitors, change: change}} end) |> Map.new() Map.merge(placeholder, result) end defp visitors_24h_total(now, offset1, offset2, site_id_to_domain_mapping) do query = from e in "events_v2", where: e.site_id in ^Map.keys(site_id_to_domain_mapping), where: e.timestamp >= ^NaiveDateTime.add(now, offset1, :hour), where: e.timestamp <= ^NaiveDateTime.add(now, offset2, :hour), select: %{ site_id: e.site_id, total_visitors: fragment("toUInt64(round(uniq(user_id) * any(_sample_factor)))") }, group_by: [e.site_id] on_full_build do query = Plausible.Stats.Sampling.add_query_hint(query) end query end defp empty_24h_intervals(now) do first = NaiveDateTime.add(now, -23, :hour) {:ok, time} = Time.new(first.hour, 0, 0) first = NaiveDateTime.new!(NaiveDateTime.to_date(first), time) for offset <- 0..24 do %{ interval: NaiveDateTime.add(first, offset, :hour), visitors: 0 } end end defp base_query_bare(site, query) do {first_datetime, last_datetime} = utc_boundaries(query, site) q = from(e in "events_v2", where: e.site_id == ^site.id, where: e.timestamp >= ^first_datetime and e.timestamp < ^last_datetime ) on_full_build do q = Plausible.Stats.Sampling.add_query_hint(q, 10_000_000) end q = if query.filters["screen"] do size = query.filters["screen"] from(e in q, where: e.screen_size == ^size) else q end q = if query.filters["browser"] do browser = query.filters["browser"] from(s in q, where: s.browser == ^browser) else q end q = if query.filters["browser_version"] do version = query.filters["browser_version"] from(s in q, where: s.browser_version == ^version) else q end q = if query.filters["os"] do os = query.filters["os"] from(s in q, where: s.operating_system == ^os) else q end q = if query.filters["os_version"] do version = query.filters["os_version"] from(s in q, where: s.operating_system_version == ^version) else q end q = if query.filters["country"] do country = query.filters["country"] from(s in q, where: s.country_code == ^country) else q end q = if query.filters["utm_medium"] do utm_medium = query.filters["utm_medium"] from(e in q, where: e.utm_medium == ^utm_medium) else q end q = if query.filters["utm_source"] do utm_source = query.filters["utm_source"] from(e in q, where: e.utm_source == ^utm_source) else q end q = if query.filters["utm_campaign"] do utm_campaign = query.filters["utm_campaign"] from(e in q, where: e.utm_campaign == ^utm_campaign) else q end q = if query.filters["utm_content"] do utm_content = query.filters["utm_content"] from(e in q, where: e.utm_content == ^utm_content) else q end q = if query.filters["utm_term"] do utm_term = query.filters["utm_term"] from(e in q, where: e.utm_term == ^utm_term) else q end q = if query.filters["referrer"] do ref = query.filters["referrer"] from(e in q, where: e.referrer == ^ref) else q end q = include_path_filter(q, query.filters[:page]) if query.filters["props"] do [{key, val}] = query.filters["props"] |> Enum.into([]) if val == "(none)" do from( e in q, where: not has_key(e, :meta, ^key) ) else from( e in q, where: has_key(e, :meta, ^key) and get_by_key(e, :meta, ^key) == ^val ) end else q end end defp base_query(site, query) do base_query_bare(site, query) |> include_goal_conversions(query) end defp utc_boundaries(%Query{now: now, period: "30m"}, site) do last_datetime = now |> NaiveDateTime.truncate(:second) first_datetime = last_datetime |> Timex.shift(minutes: -30) |> beginning_of_time(site.native_stats_start_at) |> NaiveDateTime.truncate(:second) {first_datetime, last_datetime} end defp utc_boundaries(%Query{now: now, period: "realtime"}, site) do last_datetime = now |> NaiveDateTime.truncate(:second) first_datetime = last_datetime |> Timex.shift(minutes: -5) |> beginning_of_time(site.native_stats_start_at) |> NaiveDateTime.truncate(:second) {first_datetime, last_datetime} end defp utc_boundaries(%Query{date_range: date_range}, site) do {:ok, first} = NaiveDateTime.new(date_range.first, ~T[00:00:00]) first_datetime = first |> Timezones.to_utc_datetime(site.timezone) |> beginning_of_time(site.native_stats_start_at) {:ok, last} = NaiveDateTime.new(date_range.last |> Timex.shift(days: 1), ~T[00:00:00]) last_datetime = Timezones.to_utc_datetime(last, site.timezone) {first_datetime, last_datetime} end defp event_name_for_goal(query) do case query.filters["goal"] do "Visit " <> page -> {"pageview", page} goal when is_binary(goal) -> {goal, nil} _ -> {nil, nil} end end defp include_goal_conversions(db_query, query) do {goal_event, path} = event_name_for_goal(query) q = if goal_event do from(e in db_query, where: e.name == ^goal_event) else from(e in db_query, where: e.name == "pageview") end if path do {contains_regex, path_regex} = convert_path_regex(path) if contains_regex do from(e in q, where: fragment("match(?, ?)", e.pathname, ^path_regex)) else from(e in q, where: e.pathname == ^path) end else q end end defp check_negated_filter(filter) do negated = String.at(filter, 0) == "!" updated_filter = if negated, do: String.slice(filter, 1..-1), else: filter {negated, updated_filter} end defp convert_path_regex(path) do contains_regex = String.match?(path, ~r/\*/) regex = "^#{path}\/?$" |> String.replace(~r/\*\*/, ".*") |> String.replace(~r/(?