|
|
|
|
defmodule Plausible.Stats.Base do
|
|
|
|
|
use Plausible.ClickhouseRepo
|
|
|
|
|
use Plausible
|
|
|
|
|
use Plausible.Stats.Fragments
|
|
|
|
|
|
|
|
|
|
alias Plausible.Stats.{Query, Filters}
|
|
|
|
|
alias Plausible.Timezones
|
[Continued] Google Analytics import (#1753)
* Add has_imported_stats boolean to Site
* Add Google Analytics import panel to general settings
* Get GA profiles to display in import settings panel
* Add import_from_google method as entrypoint to import data
* Add imported_visitors table
* Remove conflicting code from migration
* Import visitors data into clickhouse database
* Pass another dataset to main graph for rendering in red
This adds another entry to the JSON data returned via the main graph API
called `imported_plot`, which is similar to `plot` in form but will be
completed with previously imported data. Currently it simply returns
the values from `plot` / 2. The data is rendered in the main graph in
red without fill, and without an indicator for the present. Rationale:
imported data will not continue to grow so there is no projection
forward, only backwards.
* Hook imported GA data to dashboard timeseries plot
* Add settings option to forget imported data
* Import sources from google analytics
* Merge imported sources when queried
* Merge imported source data native data when querying sources
* Start converting metrics to atoms so they can be subqueried
This changes "visitors" and in some places "sources" to atoms. This does
not change the behaviour of the functions - the tests all pass unchanged
following this commit. This is necessary as joining subqueries requires
that the keys in `select` statements be atoms and not strings.
* Convery GA (direct) source to empty string
* Import utm campaign and utm medium from GA
* format
* Import all data types from GA into new tables
* Handle large amounts of more data more safely
* Fix some mistakes in tables
* Make GA requests in chunks of 5 queries
* Only display imported timeseries when there is no filter
* Correctly show last 30 minutes timeseries when 'realtime'
* Add with_imported key to Query struct
* Account for injected :is_not filter on sources from dashboard
* Also add tentative imported_utm_sources table
This needs a bit more work on the google import side, as GA do not
report sources and utm sources as distinct things.
* Return imported data to dashboard for rest of Sources panel
This extends the merge_imported function definition for sources to
utm_sources, utm_mediums and utm_campaigns too. This appears to be
working on the DB side but something is incomplete on the client side.
* Clear imported stats from all tables when requested
* Merge entry pages and exit pages from imported data into unfiltered dashboard view
This requires converting the `"visits"` and `"visit_duration"` metrics
to atoms so that they can be used in ecto subqueries.
* Display imported devices, browsers and OSs on dashboard
* Display imported country data on dashboard
* Add more metrics to entries/exits for modals
* make sure data is returned via API with correct keys
* Import regions and cities from GA
* Capitalize device upon import to match native data
* Leave query limits/offsets until after possibly joining with imported data
* Also import timeOnPage and pageviews for pages from GA
* imported_countries -> imported_locations
* Get timeOnPage and pageviews for pages from GA
These are needed for the pages modal, and for calculating exit rates for
exit pages.
* Add indicator to dashboard when imported data is being used
* Don't show imported data as separately line on main graph
* "bounce_rate" -> :bounce_rate, so it works in subqueries
* Drop imported browser and OS versions
These are not needed.
* Toggle displaying imported data by clicking indicator
* Parse referrers with RefInspector
- Use 'ga:fullReferrer' instead of 'ga:source'. This provides the actual
referrer host + path, whereas 'ga:source' includes utm_mediums and
other values when relevant.
- 'ga:fullReferror' does however include search engine names directly,
so they are manually checked for as RefInspector won't pick up on
these.
* Keep imported data indicator on dashboard and strikethrough when hidden
* Add unlink google button to import panel
* Rename some GA browsers and OSes to plausible versions
* Get main top pages and exit pages panels working correctly with imported data
* mix format
* Fetch time_on_pages for imported data when needed
* entry pages need to fetch bounces from GA
* "sample_percent" -> :sample_percent as only atoms can be used in subqueries
* Calculate bounce_rate for joined native and imported data for top pages modal
* Flip some query bindings around to be less misleading
* Fixup entry page modal visit durations
* mix format
* Fetch bounces and visit_duration for sources from GA
* add more source metrics used for data in modals
* Make sources modals display correct values
* imported_visitors: bounce_rate -> bounces, avg_visit_duration -> visit_duration
* Merge imported data into aggregate stats
* Reformat top graph side icons
* Ensure sample_percent is yielded from aggregate data
* filter event_props should be strings
* Hide imported data from frontend when using filter
* Fix existing tests
* fix tests
* Fix imported indicator appearing when filtering
* comma needed, lost when rebasing
* Import utm_terms and utm_content from GA
* Merge imported utm_term and utm_content
* Rename imported Countries data as Locations
* Set imported city schema field to int
* Remove utm_terms and utm_content when clearing imported
* Clean locations import from Google Analytics
- Country and region should be set to "" when GA provides "(not set)"
- City should be set to 0 for "unknown", as we cannot reliably import
city data from GA.
* Display imported region and city in dashboard
* os -> operating_system in some parts of code
The inconsistency of using os in some places and operating_system in
others causes trouble with subqueries and joins for the native and
imported data, which would require additional logic to account for. The
simplest solution is the just use a consistent word for all uses. This
doesn't make any user-facing or database changes.
* to_atom -> to_existing_atom
* format
* "events" metric -> :events
* ignore imported data when "events" in metrics
* update "bounce_rate"
* atomise some more metrics from new city and region api
* atomise some more metrics for email handlers
* "conversion_rate" -> :conversion_rate during csv export
* Move imported data stats code to own module
* Move imported timeseries function to Stats.Imported
* Use Timex.parse to import dates from GA
* has_imported_stats -> imported_source
* "time_on_page" -> :time_on_page
* Convert imported GA data to UTC
* Clean up GA request code a bit
There was some weird logic here with two separate lists that really
ought to be together, so this merges those.
* Fail sooner if GA timezone can't be identified
* Link imported tables to site by id
* imported_utm_content -> imported_utm_contents
* Imported GA from all of time
* Reorganise GA data fetch logic
- Fetch data from the start of time (2005)
- Check whether no data was fetched, and if so, inform user and don't
consider data to be imported.
* Clarify removal of "visits" data when it isn't in metrics
* Apply location filters from API
This makes it consistent with the sources etc which filter out 'Direct /
None' on the API side. These filters are used by both the native and
imported data handling code, which would otherwise both duplicate the
filters in their `where` clauses.
* Do not use changeset for setting site.imported_source
* Add all metrics to all dimensions
* Run GA import in the background
* Send email when GA import completes
* Add handler to insert imported data into tests and imported_browsers_factory
* Add remaining import data test factories
* Add imported location data to test
* Test main graph with imported data
* Add imported data to operating systems tests
* Add imported data to pages tests
* Add imported data to entry pages tests
* Add imported data to exit pages tests
* Add imported data to devices tests
* Add imported data to sources tests
* Add imported data to UTM tests
* Add new test module for the data import step
* Test import of sources GA data
* Test import of utm_mediums GA data
* Test import of utm_campaigns GA data
* Add tests for UTM terms
* Add tests for UTM contents
* Add test for importing pages and entry pages data from GA
* Add test for importing exit page data
* Fix module file name typo
* Add test for importing location data from GA
* Add test for importing devices data from GA
* Add test for importing browsers data from GA
* Add test for importing OS data from GA
* Paginate GA requests to download all data
* Bump clickhouse_ecto version
* Move RefInspector wrapper function into module
* Drop timezone transform on import
* Order imported by side_id then date
* More strings -> atoms
Also changes a conditional to be a bit nicer
* Remove parallelisation of data import
* Split sources and UTM sources from fetched GA data
GA has only a "source" dimension and no "UTM source" dimension. Instead
it returns these combined. The logic herein to tease these apart is:
1. "(direct)" -> it's a direct source
2. if the source is a domain -> it's a source
3. "google" -> it's from adwords; let's make this a UTM source "adwords"
4. else -> just a UTM source
* Keep prop names in queries as strings
* fix typo
* Fix import
* Insert data to clickhouse in batches
* Fix link when removing imported data
* Merge source tables
* Import hostname as well as pathname
* Record start and end time of imported data
* Track import progress
* Fix month interval with imported data
* Do not JOIN when imported date range has no overlap
* Fix time on page using exits
Co-authored-by: mcol <mcol@posteo.net>
5 years ago
|
|
|
import Ecto.Query
|
|
|
|
|
|
|
|
|
|
@no_ref "Direct / None"
|
|
|
|
|
@not_set "(not set)"
|
|
|
|
|
|
|
|
|
|
@uniq_users_expression "toUInt64(round(uniq(?) * any(_sample_factor)))"
|
|
|
|
|
|
|
|
|
|
def base_event_query(site, query) do
|
|
|
|
|
events_q = query_events(site, query)
|
|
|
|
|
|
|
|
|
|
if Enum.any?(Filters.visit_props(), &query.filters["visit:" <> &1]) do
|
|
|
|
|
sessions_q =
|
|
|
|
|
from(
|
|
|
|
|
s in query_sessions(site, query),
|
|
|
|
|
select: %{session_id: s.session_id},
|
|
|
|
|
where: s.sign == 1,
|
|
|
|
|
group_by: s.session_id
|
|
|
|
|
)
|
|
|
|
|
|
|
|
|
|
from(
|
|
|
|
|
e in events_q,
|
|
|
|
|
join: sq in subquery(sessions_q),
|
|
|
|
|
on: e.session_id == sq.session_id
|
|
|
|
|
)
|
|
|
|
|
else
|
|
|
|
|
events_q
|
|
|
|
|
end
|
|
|
|
|
end
|
|
|
|
|
|
|
|
|
|
def query_events(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, query)
|
|
|
|
|
end
|
|
|
|
|
|
|
|
|
|
q = from(e in q, where: ^dynamic_filter_condition(query, "event:page", :pathname))
|
|
|
|
|
|
|
|
|
|
q =
|
|
|
|
|
case query.filters["event:name"] do
|
|
|
|
|
{:is, name} ->
|
|
|
|
|
from(e in q, where: e.name == ^name)
|
|
|
|
|
|
|
|
|
|
{:member, list} ->
|
|
|
|
|
from(e in q, where: e.name in ^list)
|
|
|
|
|
|
|
|
|
|
nil ->
|
|
|
|
|
q
|
|
|
|
|
end
|
|
|
|
|
|
|
|
|
|
q =
|
|
|
|
|
case query.filters["event:goal"] do
|
|
|
|
|
{:is, {:page, path}} ->
|
|
|
|
|
from(e in q, where: e.pathname == ^path and e.name == "pageview")
|
|
|
|
|
|
|
|
|
|
{:matches, {:page, expr}} ->
|
|
|
|
|
regex = page_regex(expr)
|
|
|
|
|
|
|
|
|
|
from(e in q,
|
|
|
|
|
where: fragment("match(?, ?)", e.pathname, ^regex) and e.name == "pageview"
|
|
|
|
|
)
|
|
|
|
|
|
|
|
|
|
{:is, {:event, event}} ->
|
|
|
|
|
from(e in q, where: e.name == ^event)
|
|
|
|
|
|
|
|
|
|
{:member, clauses} ->
|
|
|
|
|
{events, pages} = split_goals(clauses)
|
|
|
|
|
|
|
|
|
|
from(e in q,
|
|
|
|
|
where: (e.pathname in ^pages and e.name == "pageview") or e.name in ^events
|
|
|
|
|
)
|
|
|
|
|
|
|
|
|
|
{:matches_member, clauses} ->
|
|
|
|
|
{events, pages} = split_goals(clauses, &page_regex/1)
|
|
|
|
|
|
|
|
|
|
event_clause =
|
|
|
|
|
if Enum.any?(events) do
|
|
|
|
|
dynamic([x], fragment("multiMatchAny(?, ?)", x.name, ^events))
|
|
|
|
|
else
|
|
|
|
|
dynamic([x], false)
|
|
|
|
|
end
|
|
|
|
|
|
|
|
|
|
page_clause =
|
|
|
|
|
if Enum.any?(pages) do
|
|
|
|
|
dynamic(
|
|
|
|
|
[x],
|
|
|
|
|
fragment("multiMatchAny(?, ?)", x.pathname, ^pages) and x.name == "pageview"
|
|
|
|
|
)
|
|
|
|
|
else
|
|
|
|
|
dynamic([x], false)
|
|
|
|
|
end
|
|
|
|
|
|
|
|
|
|
where_clause = dynamic([], ^event_clause or ^page_clause)
|
|
|
|
|
|
|
|
|
|
from(e in q, where: ^where_clause)
|
|
|
|
|
|
|
|
|
|
nil ->
|
|
|
|
|
q
|
|
|
|
|
end
|
|
|
|
|
|
|
|
|
|
q =
|
|
|
|
|
Enum.reduce(
|
|
|
|
|
Query.get_all_filters_by_prefix(query, "event:props"),
|
|
|
|
|
q,
|
|
|
|
|
&filter_by_custom_prop/2
|
|
|
|
|
)
|
|
|
|
|
|
|
|
|
|
q
|
|
|
|
|
end
|
|
|
|
|
|
|
|
|
|
@api_prop_name_to_db %{
|
|
|
|
|
"source" => "referrer_source",
|
|
|
|
|
"device" => "screen_size",
|
|
|
|
|
"screen" => "screen_size",
|
|
|
|
|
"os" => "operating_system",
|
|
|
|
|
"os_version" => "operating_system_version",
|
|
|
|
|
"country" => "country_code",
|
|
|
|
|
"region" => "subdivision1_code",
|
|
|
|
|
"city" => "city_geoname_id"
|
|
|
|
|
}
|
|
|
|
|
|
|
|
|
|
def query_sessions(site, query) do
|
|
|
|
|
{first_datetime, last_datetime} =
|
|
|
|
|
utc_boundaries(query, site)
|
|
|
|
|
|
|
|
|
|
q = from(s in "sessions_v2", where: s.site_id == ^site.id)
|
|
|
|
|
|
|
|
|
|
sessions_q =
|
|
|
|
|
if FunWithFlags.enabled?(:experimental_session_count, for: site) or
|
|
|
|
|
query.experimental_session_count? do
|
|
|
|
|
from s in q, where: s.timestamp >= ^first_datetime and s.start < ^last_datetime
|
|
|
|
|
else
|
|
|
|
|
from s in q, where: s.start >= ^first_datetime and s.start < ^last_datetime
|
|
|
|
|
end
|
|
|
|
|
|
|
|
|
|
on_full_build do
|
|
|
|
|
sessions_q = Plausible.Stats.Sampling.add_query_hint(sessions_q, query)
|
|
|
|
|
end
|
|
|
|
|
|
|
|
|
|
sessions_q = filter_by_entry_props(sessions_q, query)
|
|
|
|
|
|
|
|
|
|
Enum.reduce(Filters.visit_props(), sessions_q, fn prop_name, sessions_q ->
|
|
|
|
|
filter_key = "visit:" <> prop_name
|
|
|
|
|
|
|
|
|
|
db_field =
|
|
|
|
|
Map.get(@api_prop_name_to_db, prop_name, prop_name)
|
|
|
|
|
|> String.to_existing_atom()
|
|
|
|
|
|
|
|
|
|
from(s in sessions_q,
|
|
|
|
|
where: ^dynamic_filter_condition(query, filter_key, db_field)
|
|
|
|
|
)
|
|
|
|
|
end)
|
|
|
|
|
end
|
|
|
|
|
|
|
|
|
|
def filter_by_entry_props(sessions_q, query) do
|
|
|
|
|
case Query.get_filter_by_prefix(query, "visit:entry_props:") do
|
|
|
|
|
nil ->
|
|
|
|
|
sessions_q
|
|
|
|
|
|
|
|
|
|
{"visit:entry_props:" <> prop_name, filter_value} ->
|
|
|
|
|
apply_entry_prop_filter(sessions_q, prop_name, filter_value)
|
|
|
|
|
end
|
|
|
|
|
end
|
|
|
|
|
|
|
|
|
|
def apply_entry_prop_filter(sessions_q, prop_name, {:is, "(none)"}) do
|
|
|
|
|
from(
|
|
|
|
|
s in sessions_q,
|
|
|
|
|
where: not has_key(s, :entry_meta, ^prop_name)
|
|
|
|
|
)
|
|
|
|
|
end
|
|
|
|
|
|
|
|
|
|
def apply_entry_prop_filter(sessions_q, prop_name, {:is, value}) do
|
|
|
|
|
from(
|
|
|
|
|
s in sessions_q,
|
|
|
|
|
where:
|
|
|
|
|
has_key(s, :entry_meta, ^prop_name) and get_by_key(s, :entry_meta, ^prop_name) == ^value
|
|
|
|
|
)
|
|
|
|
|
end
|
|
|
|
|
|
|
|
|
|
def apply_entry_prop_filter(sessions_q, prop_name, {:is_not, "(none)"}) do
|
|
|
|
|
from(
|
|
|
|
|
s in sessions_q,
|
|
|
|
|
where: has_key(s, :entry_meta, ^prop_name)
|
|
|
|
|
)
|
|
|
|
|
end
|
|
|
|
|
|
|
|
|
|
def apply_entry_prop_filter(sessions_q, prop_name, {:is_not, value}) do
|
|
|
|
|
from(
|
|
|
|
|
s in sessions_q,
|
|
|
|
|
where:
|
|
|
|
|
not has_key(s, :entry_meta, ^prop_name) or
|
|
|
|
|
get_by_key(s, :entry_meta, ^prop_name) != ^value
|
|
|
|
|
)
|
|
|
|
|
end
|
|
|
|
|
|
|
|
|
|
def apply_entry_prop_filter(sessions_q, _, _), do: sessions_q
|
|
|
|
|
|
|
|
|
|
def select_event_metrics(metrics) do
|
|
|
|
|
metrics
|
|
|
|
|
|> Enum.map(&select_event_metric/1)
|
|
|
|
|
|> Enum.reduce(%{}, &Map.merge/2)
|
|
|
|
|
end
|
|
|
|
|
|
|
|
|
|
defp select_event_metric(:pageviews) do
|
|
|
|
|
%{
|
|
|
|
|
pageviews:
|
|
|
|
|
dynamic(
|
|
|
|
|
[e],
|
|
|
|
|
fragment("toUInt64(round(countIf(? = 'pageview') * any(_sample_factor)))", e.name)
|
|
|
|
|
)
|
|
|
|
|
}
|
|
|
|
|
end
|
|
|
|
|
|
|
|
|
|
defp select_event_metric(:events) do
|
|
|
|
|
%{
|
|
|
|
|
events: dynamic([], fragment("toUInt64(round(count(*) * any(_sample_factor)))"))
|
|
|
|
|
}
|
|
|
|
|
end
|
|
|
|
|
|
|
|
|
|
defp select_event_metric(:visitors) do
|
|
|
|
|
%{
|
|
|
|
|
visitors: dynamic([e], selected_as(fragment(@uniq_users_expression, e.user_id), :visitors))
|
|
|
|
|
}
|
|
|
|
|
end
|
|
|
|
|
|
|
|
|
|
on_full_build do
|
|
|
|
|
defp select_event_metric(:total_revenue) do
|
|
|
|
|
%{total_revenue: Plausible.Stats.Goal.Revenue.total_revenue_query()}
|
|
|
|
|
end
|
|
|
|
|
|
|
|
|
|
defp select_event_metric(:average_revenue) do
|
|
|
|
|
%{average_revenue: Plausible.Stats.Goal.Revenue.average_revenue_query()}
|
|
|
|
|
end
|
|
|
|
|
end
|
|
|
|
|
|
|
|
|
|
defp select_event_metric(:sample_percent) do
|
|
|
|
|
%{
|
|
|
|
|
sample_percent:
|
|
|
|
|
dynamic(
|
|
|
|
|
[],
|
|
|
|
|
fragment("if(any(_sample_factor) > 1, round(100 / any(_sample_factor)), 100)")
|
|
|
|
|
)
|
|
|
|
|
}
|
|
|
|
|
end
|
|
|
|
|
|
|
|
|
|
defp select_event_metric(:percentage), do: %{}
|
|
|
|
|
defp select_event_metric(:conversion_rate), do: %{}
|
|
|
|
|
defp select_event_metric(:total_visitors), do: %{}
|
|
|
|
|
|
|
|
|
|
defp select_event_metric(unknown), do: raise("Unknown metric: #{unknown}")
|
|
|
|
|
|
|
|
|
|
def select_session_metrics(metrics, query) do
|
|
|
|
|
metrics
|
|
|
|
|
|> Enum.map(&select_session_metric(&1, query))
|
|
|
|
|
|> Enum.reduce(%{}, &Map.merge/2)
|
|
|
|
|
end
|
|
|
|
|
|
|
|
|
|
defp select_session_metric(:bounce_rate, query) do
|
|
|
|
|
condition = dynamic_filter_condition(query, "event:page", :entry_page)
|
|
|
|
|
|
|
|
|
|
%{
|
|
|
|
|
bounce_rate:
|
|
|
|
|
dynamic(
|
|
|
|
|
[],
|
|
|
|
|
fragment(
|
|
|
|
|
"toUInt32(ifNotFinite(round(sumIf(is_bounce * sign, ?) / sumIf(sign, ?) * 100), 0))",
|
|
|
|
|
^condition,
|
|
|
|
|
^condition
|
|
|
|
|
)
|
|
|
|
|
),
|
|
|
|
|
__internal_visits: dynamic([], fragment("toUInt32(sum(sign))"))
|
|
|
|
|
}
|
|
|
|
|
end
|
|
|
|
|
|
|
|
|
|
defp select_session_metric(:visits, _query) do
|
|
|
|
|
%{
|
|
|
|
|
visits: dynamic([s], fragment("toUInt64(round(sum(?) * any(_sample_factor)))", s.sign))
|
|
|
|
|
}
|
|
|
|
|
end
|
|
|
|
|
|
|
|
|
|
defp select_session_metric(:pageviews, _query) do
|
|
|
|
|
%{
|
|
|
|
|
pageviews:
|
|
|
|
|
dynamic(
|
|
|
|
|
[s],
|
|
|
|
|
fragment("toUInt64(round(sum(? * ?) * any(_sample_factor)))", s.sign, s.pageviews)
|
|
|
|
|
)
|
|
|
|
|
}
|
|
|
|
|
end
|
|
|
|
|
|
|
|
|
|
defp select_session_metric(:events, _query) do
|
|
|
|
|
%{
|
|
|
|
|
events:
|
|
|
|
|
dynamic(
|
|
|
|
|
[s],
|
|
|
|
|
fragment("toUInt64(round(sum(? * ?) * any(_sample_factor)))", s.sign, s.events)
|
|
|
|
|
)
|
|
|
|
|
}
|
|
|
|
|
end
|
|
|
|
|
|
|
|
|
|
defp select_session_metric(:visitors, _query) do
|
|
|
|
|
%{
|
|
|
|
|
visitors:
|
|
|
|
|
dynamic(
|
|
|
|
|
[s],
|
|
|
|
|
selected_as(
|
|
|
|
|
fragment("toUInt64(round(uniq(?) * any(_sample_factor)))", s.user_id),
|
|
|
|
|
:visitors
|
|
|
|
|
)
|
|
|
|
|
)
|
|
|
|
|
}
|
|
|
|
|
end
|
|
|
|
|
|
|
|
|
|
defp select_session_metric(:visit_duration, _query) do
|
|
|
|
|
%{
|
|
|
|
|
visit_duration:
|
|
|
|
|
dynamic([], fragment("toUInt32(ifNotFinite(round(sum(duration * sign) / sum(sign)), 0))")),
|
|
|
|
|
__internal_visits: dynamic([], fragment("toUInt32(sum(sign))"))
|
|
|
|
|
}
|
|
|
|
|
end
|
|
|
|
|
|
|
|
|
|
defp select_session_metric(:views_per_visit, _query) do
|
|
|
|
|
%{
|
|
|
|
|
views_per_visit:
|
|
|
|
|
dynamic(
|
|
|
|
|
[s],
|
|
|
|
|
fragment("ifNotFinite(round(sum(? * ?) / sum(?), 2), 0)", s.sign, s.pageviews, s.sign)
|
|
|
|
|
),
|
|
|
|
|
__internal_visits: dynamic([], fragment("toUInt32(sum(sign))"))
|
|
|
|
|
}
|
|
|
|
|
end
|
|
|
|
|
|
|
|
|
|
defp select_session_metric(:sample_percent, _query) do
|
|
|
|
|
%{
|
|
|
|
|
sample_percent:
|
|
|
|
|
dynamic(
|
|
|
|
|
[],
|
|
|
|
|
fragment("if(any(_sample_factor) > 1, round(100 / any(_sample_factor)), 100)")
|
|
|
|
|
)
|
|
|
|
|
}
|
|
|
|
|
end
|
|
|
|
|
|
|
|
|
|
defp select_session_metric(:percentage, _query), do: %{}
|
|
|
|
|
|
|
|
|
|
def dynamic_filter_condition(query, filter_key, db_field) do
|
|
|
|
|
case query && query.filters && query.filters[filter_key] do
|
|
|
|
|
{:is, value} ->
|
|
|
|
|
value = db_field_val(db_field, value)
|
|
|
|
|
dynamic([x], field(x, ^db_field) == ^value)
|
|
|
|
|
|
|
|
|
|
{:is_not, value} ->
|
|
|
|
|
value = db_field_val(db_field, value)
|
|
|
|
|
dynamic([x], field(x, ^db_field) != ^value)
|
|
|
|
|
|
|
|
|
|
{:matches_member, glob_exprs} ->
|
|
|
|
|
page_regexes = Enum.map(glob_exprs, &page_regex/1)
|
|
|
|
|
dynamic([x], fragment("multiMatchAny(?, ?)", field(x, ^db_field), ^page_regexes))
|
|
|
|
|
|
|
|
|
|
{:not_matches_member, glob_exprs} ->
|
|
|
|
|
page_regexes = Enum.map(glob_exprs, &page_regex/1)
|
|
|
|
|
dynamic([x], fragment("not(multiMatchAny(?, ?))", field(x, ^db_field), ^page_regexes))
|
|
|
|
|
|
|
|
|
|
{:matches, glob_expr} ->
|
|
|
|
|
regex = page_regex(glob_expr)
|
|
|
|
|
dynamic([x], fragment("match(?, ?)", field(x, ^db_field), ^regex))
|
|
|
|
|
|
|
|
|
|
{:does_not_match, glob_expr} ->
|
|
|
|
|
regex = page_regex(glob_expr)
|
|
|
|
|
dynamic([x], fragment("not(match(?, ?))", field(x, ^db_field), ^regex))
|
|
|
|
|
|
|
|
|
|
{:member, list} ->
|
|
|
|
|
list = Enum.map(list, &db_field_val(db_field, &1))
|
|
|
|
|
dynamic([x], field(x, ^db_field) in ^list)
|
|
|
|
|
|
|
|
|
|
{:not_member, list} ->
|
|
|
|
|
list = Enum.map(list, &db_field_val(db_field, &1))
|
|
|
|
|
dynamic([x], field(x, ^db_field) not in ^list)
|
|
|
|
|
|
|
|
|
|
nil ->
|
|
|
|
|
true
|
|
|
|
|
end
|
|
|
|
|
end
|
|
|
|
|
|
|
|
|
|
def filter_converted_sessions(db_query, site, query) do
|
|
|
|
|
if Query.has_event_filters?(query) do
|
|
|
|
|
converted_sessions =
|
|
|
|
|
from(e in query_events(site, query),
|
|
|
|
|
select: %{
|
|
|
|
|
session_id: fragment("DISTINCT ?", e.session_id),
|
|
|
|
|
_sample_factor: fragment("_sample_factor")
|
|
|
|
|
}
|
|
|
|
|
)
|
|
|
|
|
|
|
|
|
|
from(s in db_query,
|
|
|
|
|
join: cs in subquery(converted_sessions),
|
|
|
|
|
on: s.session_id == cs.session_id
|
|
|
|
|
)
|
|
|
|
|
else
|
|
|
|
|
db_query
|
|
|
|
|
end
|
|
|
|
|
end
|
|
|
|
|
|
|
|
|
|
defp db_field_val(:referrer_source, @no_ref), do: ""
|
|
|
|
|
defp db_field_val(:referrer, @no_ref), do: ""
|
|
|
|
|
defp db_field_val(:utm_medium, @no_ref), do: ""
|
|
|
|
|
defp db_field_val(:utm_source, @no_ref), do: ""
|
|
|
|
|
defp db_field_val(:utm_campaign, @no_ref), do: ""
|
|
|
|
|
defp db_field_val(:utm_content, @no_ref), do: ""
|
|
|
|
|
defp db_field_val(:utm_term, @no_ref), do: ""
|
|
|
|
|
defp db_field_val(_, @not_set), do: ""
|
|
|
|
|
defp db_field_val(_, val), do: val
|
|
|
|
|
|
|
|
|
|
defp beginning_of_time(candidate, native_stats_start_at) do
|
|
|
|
|
if Timex.after?(native_stats_start_at, candidate) do
|
|
|
|
|
native_stats_start_at
|
|
|
|
|
else
|
|
|
|
|
candidate
|
|
|
|
|
end
|
|
|
|
|
end
|
|
|
|
|
|
|
|
|
|
def utc_boundaries(%Query{period: "realtime", now: now}, site) do
|
|
|
|
|
last_datetime =
|
|
|
|
|
now
|
|
|
|
|
|> Timex.shift(seconds: 5)
|
|
|
|
|
|> beginning_of_time(site.native_stats_start_at)
|
|
|
|
|
|> NaiveDateTime.truncate(:second)
|
|
|
|
|
|
|
|
|
|
first_datetime =
|
|
|
|
|
now |> Timex.shift(minutes: -5) |> NaiveDateTime.truncate(:second)
|
|
|
|
|
|
|
|
|
|
{first_datetime, last_datetime}
|
|
|
|
|
end
|
|
|
|
|
|
|
|
|
|
def utc_boundaries(%Query{period: "30m", now: now}, site) do
|
|
|
|
|
last_datetime =
|
|
|
|
|
now
|
|
|
|
|
|> Timex.shift(seconds: 5)
|
|
|
|
|
|> beginning_of_time(site.native_stats_start_at)
|
|
|
|
|
|> NaiveDateTime.truncate(:second)
|
|
|
|
|
|
|
|
|
|
first_datetime =
|
|
|
|
|
now |> Timex.shift(minutes: -30) |> NaiveDateTime.truncate(:second)
|
|
|
|
|
|
|
|
|
|
{first_datetime, last_datetime}
|
|
|
|
|
end
|
|
|
|
|
|
|
|
|
|
def 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
|
|
|
|
|
|
|
|
|
|
def page_regex(expr) do
|
|
|
|
|
escaped =
|
|
|
|
|
expr
|
|
|
|
|
|> Regex.escape()
|
|
|
|
|
|> String.replace("\\|", "|")
|
|
|
|
|
|> String.replace("\\*\\*", ".*")
|
|
|
|
|
|> String.replace("\\*", ".*")
|
|
|
|
|
|
|
|
|
|
"^#{escaped}$"
|
|
|
|
|
end
|
|
|
|
|
|
|
|
|
|
defp split_goals(clauses, map_fn \\ &Function.identity/1) do
|
|
|
|
|
groups =
|
|
|
|
|
Enum.group_by(clauses, fn {goal_type, _v} -> goal_type end, fn {_k, val} -> map_fn.(val) end)
|
|
|
|
|
|
|
|
|
|
{
|
|
|
|
|
Map.get(groups, :event, []),
|
|
|
|
|
Map.get(groups, :page, [])
|
|
|
|
|
}
|
|
|
|
|
end
|
|
|
|
|
|
|
|
|
|
defp filter_by_custom_prop({"event:props:" <> prop_name, {:is, "(none)"}}, q) do
|
|
|
|
|
from(
|
|
|
|
|
e in q,
|
|
|
|
|
where: not has_key(e, :meta, ^prop_name)
|
|
|
|
|
)
|
|
|
|
|
end
|
|
|
|
|
|
|
|
|
|
defp filter_by_custom_prop({"event:props:" <> prop_name, {:is, value}}, q) do
|
|
|
|
|
from(
|
|
|
|
|
e in q,
|
|
|
|
|
where: has_key(e, :meta, ^prop_name) and get_by_key(e, :meta, ^prop_name) == ^value
|
|
|
|
|
)
|
|
|
|
|
end
|
|
|
|
|
|
|
|
|
|
defp filter_by_custom_prop({"event:props:" <> prop_name, {:is_not, "(none)"}}, q) do
|
|
|
|
|
from(
|
|
|
|
|
e in q,
|
|
|
|
|
where: has_key(e, :meta, ^prop_name)
|
|
|
|
|
)
|
|
|
|
|
end
|
|
|
|
|
|
|
|
|
|
defp filter_by_custom_prop({"event:props:" <> prop_name, {:is_not, value}}, q) do
|
|
|
|
|
from(
|
|
|
|
|
e in q,
|
|
|
|
|
where: not has_key(e, :meta, ^prop_name) or get_by_key(e, :meta, ^prop_name) != ^value
|
|
|
|
|
)
|
|
|
|
|
end
|
|
|
|
|
|
|
|
|
|
defp filter_by_custom_prop({"event:props:" <> prop_name, {:matches, value}}, q) do
|
|
|
|
|
regex = page_regex(value)
|
|
|
|
|
|
|
|
|
|
from(
|
|
|
|
|
e in q,
|
|
|
|
|
where:
|
|
|
|
|
has_key(e, :meta, ^prop_name) and
|
|
|
|
|
fragment("match(?, ?)", get_by_key(e, :meta, ^prop_name), ^regex)
|
|
|
|
|
)
|
|
|
|
|
end
|
|
|
|
|
|
|
|
|
|
defp filter_by_custom_prop({"event:props:" <> prop_name, {:member, values}}, q) do
|
|
|
|
|
none_value_included = Enum.member?(values, "(none)")
|
|
|
|
|
|
|
|
|
|
from(
|
|
|
|
|
e in q,
|
|
|
|
|
where:
|
|
|
|
|
(has_key(e, :meta, ^prop_name) and get_by_key(e, :meta, ^prop_name) in ^values) or
|
|
|
|
|
(^none_value_included and not has_key(e, :meta, ^prop_name))
|
|
|
|
|
)
|
|
|
|
|
end
|
|
|
|
|
|
|
|
|
|
defp filter_by_custom_prop({"event:props:" <> prop_name, {:not_member, values}}, q) do
|
|
|
|
|
none_value_included = Enum.member?(values, "(none)")
|
|
|
|
|
|
|
|
|
|
from(
|
|
|
|
|
e in q,
|
|
|
|
|
where:
|
|
|
|
|
(has_key(e, :meta, ^prop_name) and
|
|
|
|
|
get_by_key(e, :meta, ^prop_name) not in ^values) or
|
|
|
|
|
(^none_value_included and
|
|
|
|
|
has_key(e, :meta, ^prop_name) and
|
|
|
|
|
get_by_key(e, :meta, ^prop_name) not in ^values) or
|
|
|
|
|
(not (^none_value_included) and not has_key(e, :meta, ^prop_name))
|
|
|
|
|
)
|
|
|
|
|
end
|
|
|
|
|
|
|
|
|
|
defp filter_by_custom_prop({"event:props:" <> prop_name, {:matches_member, clauses}}, q) do
|
|
|
|
|
regexes = Enum.map(clauses, &page_regex/1)
|
|
|
|
|
|
|
|
|
|
from(
|
|
|
|
|
e in q,
|
|
|
|
|
where:
|
|
|
|
|
has_key(e, :meta, ^prop_name) and
|
|
|
|
|
fragment("arrayExists(k -> match(?, k), ?)", get_by_key(e, :meta, ^prop_name), ^regexes)
|
|
|
|
|
)
|
|
|
|
|
end
|
|
|
|
|
|
|
|
|
|
defp total_visitors(site, query) do
|
|
|
|
|
base_event_query(site, query)
|
|
|
|
|
|> select([e], total_visitors: fragment(@uniq_users_expression, e.user_id))
|
|
|
|
|
end
|
|
|
|
|
|
|
|
|
|
defp total_visitors_subquery(site, query, true) do
|
|
|
|
|
dynamic(
|
|
|
|
|
[e],
|
|
|
|
|
selected_as(
|
|
|
|
|
subquery(total_visitors(site, query)) +
|
|
|
|
|
subquery(Plausible.Stats.Imported.total_imported_visitors(site, query)),
|
|
|
|
|
:__total_visitors
|
|
|
|
|
)
|
|
|
|
|
)
|
|
|
|
|
end
|
|
|
|
|
|
|
|
|
|
defp total_visitors_subquery(site, query, false) do
|
|
|
|
|
dynamic([e], selected_as(subquery(total_visitors(site, query)), :__total_visitors))
|
|
|
|
|
end
|
|
|
|
|
|
|
|
|
|
def add_percentage_metric(q, site, query, metrics) do
|
|
|
|
|
if :percentage in metrics do
|
|
|
|
|
q
|
|
|
|
|
|> select_merge(
|
|
|
|
|
^%{__total_visitors: total_visitors_subquery(site, query, query.include_imported)}
|
|
|
|
|
)
|
|
|
|
|
|> select_merge(%{
|
|
|
|
|
percentage:
|
|
|
|
|
fragment(
|
|
|
|
|
"if(? > 0, round(? / ? * 100, 1), null)",
|
|
|
|
|
selected_as(:__total_visitors),
|
|
|
|
|
selected_as(:visitors),
|
|
|
|
|
selected_as(:__total_visitors)
|
|
|
|
|
)
|
|
|
|
|
})
|
|
|
|
|
else
|
|
|
|
|
q
|
|
|
|
|
end
|
|
|
|
|
end
|
|
|
|
|
|
|
|
|
|
# Adds conversion_rate metric to query, calculated as
|
|
|
|
|
# X / Y where Y is the same breakdown value without goal or props
|
|
|
|
|
# filters.
|
|
|
|
|
def maybe_add_conversion_rate(q, site, query, metrics, opts) do
|
|
|
|
|
if :conversion_rate in metrics do
|
|
|
|
|
include_imported = Keyword.fetch!(opts, :include_imported)
|
|
|
|
|
|
|
|
|
|
total_query = query |> Query.remove_event_filters([:goal, :props])
|
|
|
|
|
|
|
|
|
|
# :TRICKY: Subquery is used due to event:goal breakdown above doing an UNION ALL
|
|
|
|
|
subquery(q)
|
|
|
|
|
|> select_merge(
|
|
|
|
|
^%{total_visitors: total_visitors_subquery(site, total_query, include_imported)}
|
|
|
|
|
)
|
|
|
|
|
|> select_merge([e], %{
|
|
|
|
|
conversion_rate:
|
|
|
|
|
fragment(
|
|
|
|
|
"if(? > 0, round(? / ? * 100, 1), 0)",
|
|
|
|
|
selected_as(:__total_visitors),
|
|
|
|
|
e.visitors,
|
|
|
|
|
selected_as(:__total_visitors)
|
|
|
|
|
)
|
|
|
|
|
})
|
|
|
|
|
else
|
|
|
|
|
q
|
|
|
|
|
end
|
|
|
|
|
end
|
|
|
|
|
end
|