Skip to main content

Assignment SQL

Assignment SQLs tell Eppo where to find a log of every time a subject (e.g., user) was enrolled into an experiment, the name of that experiment, and the variant that was assigned. Assignment SQLs can point to either logs from Eppo's SDK or from existing internal or third party randomization tools. You can also create multiple assignment sources if you use a combination of assignment methods.

Assignment SQLs can also include properties to use to filter or split results.

SELECT
    ts_assigned,
    experiment_name,
    variant_name,
    user_id,
    -- optional properties that may be later used to split results
    browser, 
    device_type
FROM mydb.myschema.assignments

In this example, user_id is assigned to experiment_name in the variant_name treatment group.

Creating an Assignment SQL​

  1. Navigate to Definitions and click Create Definition SQL

Create Definition SQL

  1. Click Assignment SQL

Create Assignment SQL

  1. Select the subject (randomized unit) of the Assignment SQL. To learn more about specifying multiple randomization units Eppo, see the entities page.

Select user as entity

  1. Name your Assignment SQL

  2. Write SQL in the SQL editor to pull assignments from your data warehouse and click Run

At a minimum, this query should return a unique identifier for the subject (e.g., user_id), a unique identifier for the experiment, the variant the subject received, and a timestamp. You can also add optional subject properties such as browser or country.

If you do not yet have assignment logs in your warehouse, see the Event Logging page.

Write Assignment SQL Query

Then click Run, and the rows from that assignment table should appear in the bottom left.

  1. Annotate the columns that you've selected from the data warehouse

In case there's any ambiguity as to which properties the columns correspond to, we annotate them here.

Annotate assignment SQL columns

  1. Adding optional properties

Your feature flag tooling may have logged additional data about the user, like what country they're from or which browser they're using. You can annotate these additional properties here, and they will show up under the Entity Property SQL tab.

Add Assignment SQL Properties

Holdout columns are also defined here. More information is available on the dedicated page.

Write Assignment SQL Query

  1. Click Save & Close

Handling duplicate assignments​

Eppo has built in filtering to handle scenarios where there are multiple assignment events for a subject with different data. Here are a few common scenariios that you may encounter and how Eppo handles them:

1. Subject has multiple assignment events where they received multiple variations.

Example: A subject received both the Control and Treatment within an experiment.

Eppo will automatically filter these subjects out of that experiment's results.

2. Subject has a property that is recorded in the assignment table change during the experiment.

Example: A subject in their first assignment has property called country that initially is recorded as Canada and in a subsequent assignment the country property is recorded as France.

Eppo will use the first recorded property in the experiment. When looking at an Explore chart using the example above, the user would show up under the Canada property value.