defmodule Plausible.Stats.Imported do use Plausible.ClickhouseRepo alias Plausible.Stats.Query import Ecto.Query import Plausible.Stats.Fragments @no_ref "Direct / None" def merge_imported_timeseries(native_q, _, %Plausible.Stats.Query{include_imported: false}, _), do: native_q def merge_imported_timeseries( native_q, site, query, metrics ) do import_ids = site.complete_import_ids imported_q = from(v in "imported_visitors", where: v.site_id == ^site.id, where: v.import_id in ^import_ids, where: v.date >= ^query.date_range.first and v.date <= ^query.date_range.last, select: %{} ) |> select_imported_metrics(metrics) |> apply_interval(query, site) from(s in Ecto.Query.subquery(native_q), full_join: i in subquery(imported_q), on: s.date == i.date, select: %{date: fragment("greatest(?, ?)", s.date, i.date)} ) |> select_joined_metrics(metrics) end defp apply_interval(imported_q, %Plausible.Stats.Query{interval: "month"}, _site) do imported_q |> group_by([i], fragment("toStartOfMonth(?)", i.date)) |> select_merge([i], %{date: fragment("toStartOfMonth(?)", i.date)}) end defp apply_interval(imported_q, %Plausible.Stats.Query{interval: "week"} = query, _site) do imported_q |> group_by([i], weekstart_not_before(i.date, ^query.date_range.first)) |> select_merge([i], %{date: weekstart_not_before(i.date, ^query.date_range.first)}) end defp apply_interval(imported_q, _query, _site) do imported_q |> group_by([i], i.date) |> select_merge([i], %{date: i.date}) end def merge_imported(q, _, %Query{include_imported: false}, _, _), do: q def merge_imported(q, _, _, _, [:events | _]), do: q # GA only has 'source' def merge_imported(q, _, _, "utm_source", _), do: q def merge_imported(q, site, query, property, metrics) when property in [ "visit:source", "visit:utm_medium", "visit:utm_campaign", "visit:utm_term", "visit:utm_content", "visit:entry_page", "visit:exit_page", "visit:country", "visit:region", "visit:city", "visit:device", "visit:browser", "visit:os", "event:page" ] do {table, dim} = case property do "visit:country" -> {"imported_locations", :country} "visit:region" -> {"imported_locations", :region} "visit:city" -> {"imported_locations", :city} "visit:utm_medium" -> {"imported_sources", :utm_medium} "visit:utm_campaign" -> {"imported_sources", :utm_campaign} "visit:utm_term" -> {"imported_sources", :utm_term} "visit:utm_content" -> {"imported_sources", :utm_content} "visit:os" -> {"imported_operating_systems", :operating_system} "event:page" -> {"imported_pages", :page} _ -> dim = String.trim_leading(property, "visit:") {"imported_#{dim}s", String.to_existing_atom(dim)} end import_ids = site.complete_import_ids imported_q = from( i in table, group_by: field(i, ^dim), where: i.site_id == ^site.id, where: i.import_id in ^import_ids, where: i.date >= ^query.date_range.first and i.date <= ^query.date_range.last, where: i.visitors > 0, select: %{} ) |> select_imported_metrics(metrics) imported_q = case query.filters[property] do {:is_not, value} -> value = if value == @no_ref, do: "", else: value where(imported_q, [i], field(i, ^dim) != ^value) {:member, list} -> where(imported_q, [i], field(i, ^dim) in ^list) _ -> imported_q end imported_q = case dim do :source -> imported_q |> select_merge([i], %{ source: fragment("if(empty(?), ?, ?)", i.source, @no_ref, i.source) }) :utm_medium -> imported_q |> where([i], fragment("not empty(?)", i.utm_medium)) |> select_merge([i], %{ utm_medium: i.utm_medium }) :utm_campaign -> imported_q |> where([i], fragment("not empty(?)", i.utm_campaign)) |> select_merge([i], %{ utm_campaign: i.utm_campaign }) :utm_term -> imported_q |> where([i], fragment("not empty(?)", i.utm_term)) |> select_merge([i], %{ utm_term: i.utm_term }) :utm_content -> imported_q |> where([i], fragment("not empty(?)", i.utm_content)) |> select_merge([i], %{ utm_content: i.utm_content }) :page -> imported_q |> select_merge([i], %{ page: i.page, time_on_page: sum(i.time_on_page) }) :entry_page -> imported_q |> select_merge([i], %{ entry_page: i.entry_page }) :exit_page -> imported_q |> select_merge([i], %{exit_page: i.exit_page}) :country -> imported_q |> where([i], i.country != "ZZ") |> select_merge([i], %{country: i.country}) :region -> imported_q |> where([i], i.region != "") |> select_merge([i], %{region: i.region}) :city -> imported_q |> where([i], i.city != 0 and not is_nil(i.city)) |> select_merge([i], %{city: i.city}) :device -> imported_q |> select_merge([i], %{device: i.device}) :browser -> imported_q |> select_merge([i], %{browser: i.browser}) :operating_system -> imported_q |> select_merge([i], %{operating_system: i.operating_system}) end q = from(s in Ecto.Query.subquery(q), full_join: i in subquery(imported_q), on: field(s, ^dim) == field(i, ^dim), select: %{} ) |> select_joined_metrics(metrics) |> apply_order_by(metrics) case dim do :source -> q |> select_merge([s, i], %{ source: fragment("if(empty(?), ?, ?)", s.source, i.source, s.source) }) :utm_medium -> q |> select_merge([s, i], %{ utm_medium: fragment("if(empty(?), ?, ?)", s.utm_medium, i.utm_medium, s.utm_medium) }) :utm_source -> q |> select_merge([s, i], %{ utm_source: fragment("if(empty(?), ?, ?)", s.utm_source, i.utm_source, s.utm_source) }) :utm_campaign -> q |> select_merge([s, i], %{ utm_campaign: fragment("if(empty(?), ?, ?)", s.utm_campaign, i.utm_campaign, s.utm_campaign) }) :utm_term -> q |> select_merge([s, i], %{ utm_term: fragment("if(empty(?), ?, ?)", s.utm_term, i.utm_term, s.utm_term) }) :utm_content -> q |> select_merge([s, i], %{ utm_content: fragment("if(empty(?), ?, ?)", s.utm_content, i.utm_content, s.utm_content) }) :page -> q |> select_merge([s, i], %{ page: fragment("if(empty(?), ?, ?)", i.page, s.page, i.page) }) :entry_page -> q |> select_merge([s, i], %{ entry_page: fragment("if(empty(?), ?, ?)", i.entry_page, s.entry_page, i.entry_page) }) :exit_page -> q |> select_merge([s, i], %{ exit_page: fragment("if(empty(?), ?, ?)", i.exit_page, s.exit_page, i.exit_page) }) :country -> q |> select_merge([i, s], %{ country: fragment("if(empty(?), ?, ?)", s.country, i.country, s.country) }) :region -> q |> select_merge([i, s], %{ region: fragment("if(empty(?), ?, ?)", s.region, i.region, s.region) }) :city -> q |> select_merge([s, i], %{ city: fragment("coalesce(nullif(?, 0), ?)", i.city, s.city) }) :device -> q |> select_merge([i, s], %{ device: fragment("if(empty(?), ?, ?)", s.device, i.device, s.device) }) :browser -> q |> select_merge([i, s], %{ browser: fragment("if(empty(?), ?, ?)", s.browser, i.browser, s.browser) }) :operating_system -> q |> select_merge([i, s], %{ operating_system: fragment( "if(empty(?), ?, ?)", s.operating_system, i.operating_system, s.operating_system ) }) end end def merge_imported(q, site, query, :aggregate, metrics) do imported_q = imported_visitors(site, query) |> select_imported_metrics(metrics) from( s in subquery(q), cross_join: i in subquery(imported_q), select: %{} ) |> select_joined_metrics(metrics) end def merge_imported(q, _, _, _, _), do: q def total_imported_visitors(site, query) do imported_visitors(site, query) |> select_merge([i], %{total_visitors: fragment("sum(?)", i.visitors)}) end defp imported_visitors(site, query) do import_ids = site.complete_import_ids from( i in "imported_visitors", where: i.site_id == ^site.id, where: i.import_id in ^import_ids, where: i.date >= ^query.date_range.first and i.date <= ^query.date_range.last, select: %{} ) end defp select_imported_metrics(q, []), do: q defp select_imported_metrics(q, [:visitors | rest]) do q |> select_merge([i], %{visitors: sum(i.visitors)}) |> select_imported_metrics(rest) end defp select_imported_metrics( %Ecto.Query{from: %Ecto.Query.FromExpr{source: {"imported_exit_pages", _}}} = q, [:visits | rest] ) do q |> select_merge([i], %{visits: sum(i.exits)}) |> select_imported_metrics(rest) end defp select_imported_metrics( %Ecto.Query{from: %Ecto.Query.FromExpr{source: {"imported_entry_pages", _}}} = q, [:visits | rest] ) do q |> select_merge([i], %{visits: sum(i.entrances)}) |> select_imported_metrics(rest) end defp select_imported_metrics(q, [:visits | rest]) do q |> select_merge([i], %{visits: sum(i.visits)}) |> select_imported_metrics(rest) end defp select_imported_metrics(q, [:pageviews | rest]) do q |> where([i], i.pageviews > 0) |> select_merge([i], %{pageviews: sum(i.pageviews)}) |> select_imported_metrics(rest) end defp select_imported_metrics( %Ecto.Query{from: %Ecto.Query.FromExpr{source: {"imported_entry_pages", _}}} = q, [:bounce_rate | rest] ) do q |> select_merge([i], %{ bounces: sum(i.bounces), __internal_visits: sum(i.entrances) }) |> select_imported_metrics(rest) end defp select_imported_metrics(q, [:bounce_rate | rest]) do q |> select_merge([i], %{ bounces: sum(i.bounces), __internal_visits: sum(i.visits) }) |> select_imported_metrics(rest) end defp select_imported_metrics( %Ecto.Query{from: %Ecto.Query.FromExpr{source: {"imported_entry_pages", _}}} = q, [:visit_duration | rest] ) do q |> select_merge([i], %{ visit_duration: sum(i.visit_duration), __internal_visits: sum(i.entrances) }) |> select_imported_metrics(rest) end defp select_imported_metrics(q, [:visit_duration | rest]) do q |> select_merge([i], %{ visit_duration: sum(i.visit_duration), __internal_visits: sum(i.visits) }) |> select_imported_metrics(rest) end defp select_imported_metrics(q, [:views_per_visit | rest]) do q |> where([i], i.pageviews > 0) |> select_merge([i], %{ pageviews: sum(i.pageviews), __internal_visits: sum(i.visits) }) |> select_imported_metrics(rest) end defp select_imported_metrics(q, [_ | rest]) do q |> select_imported_metrics(rest) end defp select_joined_metrics(q, []), do: q # TODO: Reverse-engineering the native data bounces and total visit # durations to combine with imported data is inefficient. Instead both # queries should fetch bounces/total_visit_duration and visits and be # used as subqueries to a main query that then find the bounce rate/avg # visit_duration. defp select_joined_metrics(q, [:visits | rest]) do q |> select_merge([s, i], %{ :visits => fragment("? + ?", s.visits, i.visits) }) |> select_joined_metrics(rest) end defp select_joined_metrics(q, [:visitors | rest]) do q |> select_merge([s, i], %{ :visitors => selected_as( fragment("coalesce(?, 0) + coalesce(?, 0)", s.visitors, i.visitors), :visitors ) }) |> select_joined_metrics(rest) end defp select_joined_metrics(q, [:pageviews | rest]) do q |> select_merge([s, i], %{ pageviews: fragment("coalesce(?, 0) + coalesce(?, 0)", s.pageviews, i.pageviews) }) |> select_joined_metrics(rest) end defp select_joined_metrics(q, [:views_per_visit | rest]) do q |> select_merge([s, i], %{ views_per_visit: fragment( """ if( coalesce(?, 0) + coalesce(?, 0) > 0, round((? + ? * coalesce(?, 0)) / (coalesce(?, 0) + coalesce(?, 0)), 2), 0 ) """, s.__internal_visits, i.__internal_visits, i.pageviews, s.views_per_visit, s.__internal_visits, i.__internal_visits, s.__internal_visits ) }) |> select_joined_metrics(rest) end defp select_joined_metrics(q, [:bounce_rate | rest]) do q |> select_merge([s, i], %{ bounce_rate: fragment( """ if( coalesce(?, 0) + coalesce(?, 0) > 0, round(100 * (coalesce(?, 0) + coalesce((? * ? / 100), 0)) / (coalesce(?, 0) + coalesce(?, 0))), 0 ) """, s.__internal_visits, i.__internal_visits, i.bounces, s.bounce_rate, s.__internal_visits, i.__internal_visits, s.__internal_visits ) }) |> select_joined_metrics(rest) end defp select_joined_metrics(q, [:visit_duration | rest]) do q |> select_merge([s, i], %{ visit_duration: fragment( """ if( ? + ? > 0, round((? + ? * ?) / (? + ?), 1), 0 ) """, s.__internal_visits, i.__internal_visits, i.visit_duration, s.visit_duration, s.__internal_visits, s.__internal_visits, i.__internal_visits ) }) |> select_joined_metrics(rest) end defp select_joined_metrics(q, [:sample_percent | rest]) do q |> select_merge([s, i], %{sample_percent: s.sample_percent}) |> select_joined_metrics(rest) end defp select_joined_metrics(q, [_ | rest]) do q |> select_joined_metrics(rest) end defp apply_order_by(q, [:visitors | rest]) do order_by(q, [s, i], desc: fragment("coalesce(?, 0) + coalesce(?, 0)", s.visitors, i.visitors)) |> apply_order_by(rest) end defp apply_order_by(q, _), do: q end