About PickedQL
PickedQL is SQL over your Picked analytics data, but it is not only SQL. It expands what you write before it runs. Why add a layer at all, and not give you the tables?
The problem it answers
An A/B test question is short to ask, and the SQL behind it is not. Take one question: "How many users are in the control group?" It needs four things. It needs a filter on the variant, a count of distinct users, a join to each dimension in the group, and a tenant filter.
Write that by hand for every question, and most of each query is the same text again.
PickedQL lets you write the short version:
SELECT control_users(), variant_users()
FROM metrics
WHERE project_id = 'your-project-id'What it expands
Virtual tables. A name such as metrics maps to the real tables below it. You name
the thing that you ask about, and not the storage.
Virtual functions. control_users() expands to a full aggregate expression. The
definition lives in one place, so every report that counts control users counts them the
same way.
Automatic joins. Point at a dimension column such as device_category or
country_code and PickedQL adds the LEFT JOIN for you. This gives you the most: a
dimension that you did not join is the most common reason for a wrong answer that looks
correct.
SQL passthrough. Standard SQL still works next to a virtual function, and that
includes CASE WHEN, window functions, and subqueries. The layer adds; it does not
restrict.
Why every query must carry a tenant filter
Every query must carry a tenant filter in its WHERE clause. That filter is project_id
for most tables. The projects table uses id instead, because there the row ID is the
project ID.
PickedQL reads that filter and checks your permission for that project. The filter is therefore not a convention that you can forget. Without one, the query does not run. The isolation is a property of the language, and not of each author.
Which table to reach for
| Table | Use it for |
|---|---|
metrics | A dashboard. The data is aggregated first, so it is much faster than raw events. |
events | Anything deeper. Raw events let you build any breakdown or funnel. |
experience_journey | A sankey or a funnel chart. One line of SQL returns source, target, users. |
Note what experience_journey does for you. It builds the eight common table expressions
that a path analysis needs. Those remove the duplicates, rank the top paths, and call
lagInFrame. You pass your parameters in the WHERE clause.
Some reference columns are free on events. step_title, experience_title, and
project_name come from reference tables that PickedQL joins for you.
Related
- PickedQL — the language surface.
- Data sources — every table.
- Virtual functions — every function.
- How to run an analytics query — worked examples.