You cannot select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
analytics/lib/plausible/exports.ex

372 lines
10 KiB
Elixir

defmodule Plausible.Exports do
@moduledoc """
Contains functions to export data for events and sessions as Zip archives.
"""
require Plausible
import Ecto.Query
@doc """
Renders filename for the Zip archive containing the exported CSV files.
Examples:
iex> archive_filename("plausible.io", ~D[2021-01-01], ~D[2024-12-31])
"plausible_io_20210101_20241231.zip"
iex> archive_filename("Bücher.example", ~D[2021-01-01], ~D[2024-12-31])
"Bücher_example_20210101_20241231.zip"
"""
def archive_filename(domain, min_date, max_date) do
name =
Enum.join(
[
String.replace(domain, ".", "_"),
Calendar.strftime(min_date, "%Y%m%d"),
Calendar.strftime(max_date, "%Y%m%d")
],
"_"
)
name <> ".zip"
end
@doc """
Builds Ecto queries to export data from `events_v2` and `sessions_v2`
tables into the format of `imported_*` tables for a website.
"""
@spec export_queries(pos_integer, extname: String.t(), date_range: Date.Range.t()) ::
%{String.t() => Ecto.Query.t()}
def export_queries(site_id, opts \\ []) do
extname = opts[:extname] || ".csv"
date_range = opts[:date_range]
filename = fn table ->
name =
if date_range do
first_date = Timex.format!(date_range.first, "{YYYY}{0M}{0D}")
last_date = Timex.format!(date_range.last, "{YYYY}{0M}{0D}")
"#{table}_#{first_date}_#{last_date}"
else
table
end
name <> extname
end
%{
filename.("imported_visitors") => export_visitors_q(site_id),
filename.("imported_sources") => export_sources_q(site_id),
# NOTE: this query can result in `MEMORY_LIMIT_EXCEEDED` error
filename.("imported_pages") => export_pages_q(site_id),
filename.("imported_entry_pages") => export_entry_pages_q(site_id),
filename.("imported_exit_pages") => export_exit_pages_q(site_id),
filename.("imported_locations") => export_locations_q(site_id),
filename.("imported_devices") => export_devices_q(site_id),
filename.("imported_browsers") => export_browsers_q(site_id),
filename.("imported_operating_systems") => export_operating_systems_q(site_id)
}
end
Plausible.on_full_build do
defp sampled(table) do
Plausible.Stats.Sampling.add_query_hint(from(table))
end
else
defp sampled(table) do
table
end
end
defmacrop date(timestamp) do
quote do
selected_as(fragment("toDate(?)", unquote(timestamp)), :date)
end
end
defmacrop visit_duration(t) do
quote do
selected_as(
fragment("greatest(sum(?*?),0)", unquote(t).sign, unquote(t).duration),
:visit_duration
)
end
end
defmacrop visitors(t) do
quote do
selected_as(
fragment("toUInt64(round(uniq(?)*any(_sample_factor)))", unquote(t).user_id),
:visitors
)
end
end
defmacrop visits(t) do
quote do
selected_as(sum(unquote(t).sign), :visits)
end
end
defmacrop bounces(t) do
quote do
selected_as(
fragment("greatest(sum(?*?),0)", unquote(t).sign, unquote(t).is_bounce),
:bounces
)
end
end
defmacrop pageviews(t) do
quote do
selected_as(
fragment("greatest(sum(?*?),0)", unquote(t).sign, unquote(t).pageviews),
:pageviews
)
end
end
@spec export_visitors_q(pos_integer) :: Ecto.Query.t()
def export_visitors_q(site_id) do
from s in sampled("sessions_v2"),
where: s.site_id == ^site_id,
group_by: selected_as(:date),
select: [
date(s.start),
visitors(s),
pageviews(s),
bounces(s),
visits(s),
visit_duration(s)
]
end
@spec export_sources_q(pos_integer) :: Ecto.Query.t()
def export_sources_q(site_id) do
from s in sampled("sessions_v2"),
where: s.site_id == ^site_id,
group_by: [
selected_as(:date),
selected_as(:source),
s.referrer,
s.utm_source,
s.utm_medium,
s.utm_campaign,
s.utm_content,
s.utm_term
],
order_by: selected_as(:date),
select: [
date(s.start),
selected_as(s.referrer_source, :source),
s.referrer,
s.utm_source,
s.utm_medium,
s.utm_campaign,
s.utm_content,
s.utm_term,
pageviews(s),
visitors(s),
visits(s),
visit_duration(s),
bounces(s)
]
end
@spec export_pages_q(pos_integer) :: Ecto.Query.t()
def export_pages_q(site_id) do
window_q =
from e in sampled("events_v2"),
where: e.site_id == ^site_id,
select: %{
timestamp: e.timestamp,
next_timestamp:
over(fragment("leadInFrame(?)", e.timestamp),
partition_by: e.session_id,
order_by: e.timestamp,
frame: fragment("ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING")
),
pathname: e.pathname,
hostname: e.hostname,
name: e.name,
user_id: e.user_id,
session_id: e.session_id,
_sample_factor: fragment("_sample_factor")
}
from e in subquery(window_q),
group_by: [selected_as(:date), e.pathname],
order_by: selected_as(:date),
select: [
date(e.timestamp),
selected_as(fragment("any(?)", e.hostname), :hostname),
selected_as(e.pathname, :page),
selected_as(
fragment("toUInt64(round(uniq(?)*any(_sample_factor)))", e.session_id),
:visits
),
visitors(e),
selected_as(
fragment("toUInt64(round(countIf(?='pageview')*any(_sample_factor)))", e.name),
:pageviews
),
# NOTE: are exits pageviews or any events?
selected_as(
fragment("toUInt64(round(countIf(?=0)*any(_sample_factor)))", e.next_timestamp),
:exits
),
selected_as(
fragment("sum(greatest(?,0))", e.next_timestamp - e.timestamp),
:time_on_page
)
]
end
@spec export_entry_pages_q(pos_integer) :: Ecto.Query.t()
def export_entry_pages_q(site_id) do
from s in sampled("sessions_v2"),
where: s.site_id == ^site_id,
group_by: [selected_as(:date), s.entry_page],
order_by: selected_as(:date),
select: [
date(s.start),
s.entry_page,
visitors(s),
selected_as(
fragment("toUInt64(round(sum(?)*any(_sample_factor)))", s.sign),
:entrances
),
visit_duration(s),
bounces(s),
pageviews(s)
]
end
@spec export_exit_pages_q(pos_integer) :: Ecto.Query.t()
def export_exit_pages_q(site_id) do
from s in sampled("sessions_v2"),
where: s.site_id == ^site_id,
group_by: [selected_as(:date), s.exit_page],
order_by: selected_as(:date),
select: [
date(s.start),
s.exit_page,
visitors(s),
visit_duration(s),
selected_as(
fragment("toUInt64(round(sum(?)*any(_sample_factor)))", s.sign),
:exits
),
bounces(s),
pageviews(s)
]
end
@spec export_locations_q(pos_integer) :: Ecto.Query.t()
def export_locations_q(site_id) do
from s in sampled("sessions_v2"),
where: s.site_id == ^site_id,
where: s.city_geoname_id != 0 and s.country_code != "\0\0" and s.country_code != "ZZ",
group_by: [selected_as(:date), s.country_code, selected_as(:region), s.city_geoname_id],
order_by: selected_as(:date),
select: [
date(s.start),
selected_as(s.country_code, :country),
selected_as(s.subdivision1_code, :region),
selected_as(s.city_geoname_id, :city),
visitors(s),
visits(s),
visit_duration(s),
bounces(s),
pageviews(s)
]
end
@spec export_devices_q(pos_integer) :: Ecto.Query.t()
def export_devices_q(site_id) do
from s in sampled("sessions_v2"),
where: s.site_id == ^site_id,
group_by: [selected_as(:date), s.screen_size],
order_by: selected_as(:date),
select: [
date(s.start),
selected_as(s.screen_size, :device),
visitors(s),
visits(s),
visit_duration(s),
bounces(s),
pageviews(s)
]
end
@spec export_browsers_q(pos_integer) :: Ecto.Query.t()
def export_browsers_q(site_id) do
from s in sampled("sessions_v2"),
where: s.site_id == ^site_id,
group_by: [selected_as(:date), s.browser, s.browser_version],
order_by: selected_as(:date),
select: [
date(s.start),
s.browser,
s.browser_version,
visitors(s),
visits(s),
visit_duration(s),
bounces(s),
pageviews(s)
]
end
@spec export_operating_systems_q(pos_integer) :: Ecto.Query.t()
def export_operating_systems_q(site_id) do
from s in sampled("sessions_v2"),
where: s.site_id == ^site_id,
group_by: [selected_as(:date), s.operating_system, s.operating_system_version],
order_by: selected_as(:date),
select: [
date(s.start),
s.operating_system,
s.operating_system_version,
visitors(s),
visits(s),
visit_duration(s),
bounces(s),
pageviews(s)
]
end
@doc """
Creates a streamable Zip archive from the provided (named) Ecto queries.
Example usage:
{:ok, pool} = Ch.start_link(pool_size: 1)
DBConnection.run(pool, fn conn ->
conn
|> stream_archive(export_queries(_site_id = 1), format: "CSVWithNames")
|> Stream.into(File.stream!("export.zip"))
|> Stream.run()
end)
"""
@spec stream_archive(DBConnection.t(), %{String.t() => Ecto.Query.t()}, [Ch.query_option()]) ::
Enumerable.t()
def stream_archive(conn, named_queries, opts \\ []) do
entries =
Enum.map(named_queries, fn {name, query} ->
{sql, params} = Plausible.ClickhouseRepo.to_sql(:all, query)
datastream =
conn
|> Ch.stream(sql, params, opts)
|> Stream.map(fn %Ch.Result{data: data} -> data end)
Zstream.entry(name, datastream, coder: Zstream.Coder.Stored)
end)
Zstream.zip(entries)
end
end