Data Feed Operations

A data feed's columns say what each column holds; its
ops say what happens to the rows. ops is an ordered array of
operations. The feed evaluates its columns, runs each op in turn over the
result, and the rows left at the end are the feed's data. That data is what
metrics built on the feed read.

Use ops to:

• Join another data feed and bring its columns in as new columns of this
  one. This is how a metric gets a dimension, or a value, that lives in a
  different view or a different source. See Joining another feed.
• Filter rows out.
• Group, aggregate, sort or keep the top/bottom N rows.
• Unpivot columns into rows, or split one column into several.

"ops": [
  { "type": "resolve", "dims": ["e190a3817d86438da7e45315321b8572"] },
  { "type": "format" },
  { "type": "join", "join_type": "LEFT_LOOKUP",
    "dims": ["newcolumnTicketId", "newcolumnReopens"],
    "join_keys": [{ "left": "e190a3817d86438da7e45315321b8572", "right": "newcolumnTicketId" }],
    "modelId": "733715a57c24cf19eb7a90fe4dc114fc" },
  { "type": "format" }
]

Every column an op names is a columns[].id of this feed. That includes
the columns a join brings in, which are this feed's own columns (see
below). Formulas, types and aggregations stay on the columns; an op
never carries a formula of its own.

Reading and writing ops

GET /data_feeds/:feedId returns ops when the feed has any (GET /data_feeds leaves them out). A feed saved in
the product almost always has at least [{ "type": "format" }].

PUT /data_feeds/:feedId takes ops like columns:

• Sending ops replaces the whole array. Read the feed immediately before,
  edit the array, and send it back in full.
• Leaving ops out keeps the stored ops. A PUT that only renames the feed
  does not touch them.
• [] clears them. This also removes the implicit format a feed without
  ops runs, so date columns stop being converted to their output format. To
  remove everything else, send [{ "type": "format" }] instead.
• A write that sends is_locked: true cannot change the ops. That write
  only changes the name and description, so a changed ops alongside it is
  refused with 400. Leave is_locked out of the request instead: the ops
  are written and the feed stays locked. (Sending is_locked: false would
  also unlock the feed, for good.)

New columns an op names

To add a column and an op that uses it (typically a join and the columns it
brings in), send both in one request. Give the new column an id of your
own, name it in the op, and the server replaces it in both places with the
column's permanent id. Read the feed back afterwards to learn that id.

For that replacement to happen:

• Every column in the request carries an id. Existing columns keep the
  ones a GET returned them with.
• Column names are unique. New columns are matched to the ids they were
  sent with by name.

The replacement is a plain find-and-replace, over the whole of the ops and
over every column's formula, and it runs for every new column that carries
an id, whether an op names it or not. So each new id must be:

• letters and digits only, and
• found nowhere except where it names that column: in an op's column
  reference, or in a formula as #<id>;. An id like type would strip every
  op of its type; A would rewrite every __source__@A:A;; one that occurs
  inside another id would corrupt it.

Use something distinctive, as the product does: newcolumn followed by a
random suffix, such as newcolumn7f3a91c2.

The API refuses a request that breaks any of these with 400, before
anything is written.

What the API checks

Before anything is written, a PUT that sends ops is refused with 400 when:

• an op's type is not one of those below, or it lacks a property its type
  requires;
• an op names a column that is not in the feed's columns (as the request
  leaves them);
• there are more than 4 joins, or a join names the feed being written;
• a join names a feed that does not exist or that you cannot view. Every
  feed a new join reads is read with your credentials first;
• the ops change which feeds are joined but the request carries no
  columns. The feeds a join reads are recorded (in their joined_to_ids)
  only by a write that includes the columns.

A PUT that sends columns without ops is checked against the stored ops,
which keep running: removing a column one of them names is refused with
400. Keep the column, or send ops without it.

A reference the stored ops already made to a column the feed no longer has
is let through: sending the ops back unchanged is not what broke it.

If the feed's current ops cannot be read, a PUT that sends ops is refused
with 503 rather than replacing ops it cannot see. Try again later.

The API checks the order of ops in one respect only: a column must not be
evaluated before a column of the feed it reads (see
A column that reads another column).
It does not check that the order otherwise makes sense, or that a filter
predicate means what you intended. Read the feed's data
(GET /data_feeds/:feedId/data) after changing its ops.

How ops run

Ops run in array order, but columns are evaluated before any op runs:

• A column that no resolve, join, unpivot or split names is
  evaluated before the first op, over all the source's rows.
• A column one of those ops does name is evaluated when that op runs,
  over the rows left by the ops before it.

So a filter at the start of ops removes rows from every up-front column,
and a column resolved after the filter is computed from the filtered rows.

A column that reads another column

A column whose formula reads another column of the same feed
(<feedId>#<columnId>;) must be evaluated no earlier than that column, or it
reads nothing and is blank on every row.

A column that no op names in dims - no resolve, join, unpivot or
split - is evaluated with the others before the ops run, so a feed with none of
those ops can have a column read another with nothing added. Many feeds - those
generated from a service connection, and some uploads - start with a resolve of
their source columns instead, and on those a column you add that reads a resolved
column needs a resolve of its own after it:

"ops": [
  { "type": "resolve", "dims": ["e190a3817d86438da7e45315321b8572", "2e06aa1c3fed4935ad1c8e26a1d6beb3"] },
  { "type": "format" },
  { "type": "resolve", "dims": ["newcolumn7f3a91c2"] },
  { "type": "format" }
]

Naming it in the same resolve as the column it reads works too. Two
arrangements do not: leaving the new column out of every op (it is then
evaluated before the first op, ahead of the op it reads from), and naming it in
an op that runs before its source's. A column is evaluated at the first op
that names it, so moving one later means taking it out of the earlier op's
dims, not adding a later resolve alongside.
PUT /data_feeds/:feedId refuses a write that leaves a column evaluated before a
column it reads with 400, naming both and the op to add. A column new in the
same request needs an id of your own (such as newcolumn7f3a91c2) to be named
in dims; the server replaces it. An order already stored is left alone, so a
GET → edit → PUT that does not touch it still saves.

format converts date and duration columns from their input format to
their output format. The product keeps one after each resolve, join,
unpivot and split; do the same - columns evaluated with no format after
them keep their raw input values, so dates never convert. PUT /data_feeds/:feedId
refuses ops in which a resolve, unpivot or split has no format after it
with 400, saying where to insert it. A join is the exception: the columns it
brings in arrive already formatted by the feed they come from, so one after a
join is not required - but it is the safer shape, and the one the product
writes. An op already stored without one is left alone, so a GET → edit →
PUT that keeps it still saves. A feed with no ops at all runs a single
format.

Operation reference

Every op has a type, written in lower case. Properties the product adds
for its own editors (a filter's uiFilterKey, a join's modelName) are
accepted and kept, so a GET's ops can always be sent back as they are.

resolve

{ "type": "resolve", "dims": ["date", "supportRep", "tickets"] }

format

No properties. Required after every resolve, unpivot and split, before the
next one or the end of ops, and recommended after a join. See
How ops run.

join

The _LOOKUP types never multiply rows, which is what you want when the
joined feed has at most one row per key (one metrics row per ticket, one
account per id). The others behave like SQL joins.

See Joining another feed for how to set one up.

filter

A predicate is a tree of nodes. Each node's type says what it is:

• "op": compare a column.
  { "type": "op", "dim": "<columnId>", "op": ">=", "value": 10 }. op is
  one of =, !=, >, <, >=, <=. On a text column a string value
  is quoted for you: write "value": "open", not "\"open\"", and it cannot
  itself contain a ". value may also be a node, such as
  { "type": "f", "f": "blank" } to compare with a blank. For a date column,
  add "valueFmt": { "type": "DATE" } and give value as MM/dd/yyyy; add
  "useEOP": true inside valueFmt to compare against the end of that day.
• "f": call a function.
  { "type": "f", "f": "<function>", "args": [<node>, ...] }. Combine
  conditions with and, or and not, any number of args, nested as deep
  as you need. Any formula function
  that returns a boolean works, for example contains_insensitive,
  starts_with_insensitive, ends_with_insensitive, isblank, and in
  with dst_array.
• No type (a literal). { "dim": "<columnId>" } is the column, and
  { "value": "\"text\"" } is a value written as it appears in a formula.
  A string literal here keeps its quotes.
• "relativeDateRange": a date window.
  { "type": "relativeDateRange", "dim": "<dateColumnId>", "op": "in_the_last", "relativeUnit": "5", "relativeAmount": 7 }.
  op is today, yesterday, this_n, in_the_last or last_full.
  relativeUnit is 1 year, 2 quarter, 3 month, 4 week, 5 day,
  6 hour, 7 minute, 8 second.

Status is not "closed", and the subject does not contain "test":

{
  "type": "filter",
  "p": {
    "type": "f", "f": "and",
    "args": [
      { "type": "op", "dim": "7a1c0f", "op": "!=", "value": "closed" },
      { "type": "f", "f": "not", "args": [
        { "type": "f", "f": "contains_insensitive", "args": [{ "dim": "9b2e44" }, { "value": "\"test\"" }] }
      ] }
    ]
  }
}

Units blank, or between 2 and 10:

{
  "type": "filter",
  "p": { "type": "f", "f": "or", "args": [
    { "type": "op", "dim": "units", "op": "=", "value": { "type": "f", "f": "blank" } },
    { "type": "f", "f": "and", "args": [
      { "type": "op", "dim": "units", "op": ">=", "value": 2 },
      { "type": "op", "dim": "units", "op": "<=", "value": 10 }
    ] }
  ] }
}

The product's editor writes one filter per column and keeps them after the
other ops. You can instead write a single filter with an and at its root.

group

Every other column is aggregated by its own aggregation (sum,
average, median, count, countdistinct, min, max, first, last,
join, or auto, which picks by the column's format). To group by several
columns, add a column that combines them and group by that.

{ "type": "group", "groupBy": "product" }

A metric already aggregates the feed's rows by its own dimensions, so a
feed that feeds metrics rarely needs group. Keep the rows, and let the
metric aggregate.

aggregate

No properties. Collapses the feed to a single row, each column aggregated by
its aggregation.

sort

{ "type": "sort", "sortBy": [{ "dim": "created", "dir": 2 }] }

top_bottom

{ "type": "top_bottom", "count": 10, "orderBy": [{ "dim": "revenue", "dir": 2 }] }

unpivot

Each row becomes one row per unpivoted column. The two dims are new columns
you add to columns with "formula": ""; the op fills them.

{ "type": "unpivot", "columns": ["canada", "us", "mexico"], "dims": ["country", "sales"] }

split

As with unpivot, the dims are new columns with "formula": "".

{ "type": "split", "column": "fullName", "delimiter": " ", "dims": ["firstName", "lastName"] }

remove_n_rows and frah

Written by the product for a feed whose source has header rows. They take no
properties, and how many rows go follows from the feed's header setting.
Keep them where they are. frah is the older form of remove_n_rows.

ops is replaced whole, so a write that leaves one out removes it, and the
header row comes back as the first row of data - in every column, including
calculated ones reading it. The write is not refused. When you rewrite ops,
start from the array GET /data_feeds/:feedId returns and keep these.

Joining another feed

A feed built from one view, one query or one upload carries only what that
source has. When a metric needs a value or a dimension from somewhere else,
join the feed that has it. The joined values become new columns of this feed,
and a metric on this feed can use them like any other column.

When to join: the dimension or measure you need is in a different view
(Zendesk TicketMetrics rather than Tickets), or a different source (a
spreadsheet mapping account ids to regions), and both have a column you
can match rows on (ticket id, account id). If there is no such column, a join
cannot help.

A join has three parts, all sent in one PUT to the feed you are adding
columns to:

1. A new column per joined value, whose formula reads the joined feed's
   column by id: <joinedFeedId>#<joinedColumnId>;
   (see Another column in the formula reference).
   Include one for the joined feed's key.
2. A join op with those new columns as its dims, and a join_keys
   pair of this feed's key column (left) and the new key column (right).
3. A resolve of this feed's own columns before the join, and a format
   after each. This is what the product writes, and it keeps the two sides
   apart.

Name no joined column in an earlier resolve: a column evaluated before the
join is treated as part of this feed rather than the joined one. The left
key, like every column of this feed, must be evaluated before the join. It
is, unless an op after the join names it.

The joined feed's own joined_to_ids then lists this feed. It reads "feeds
that join this one", not "feeds this one joins", and the product uses it to
track which feeds depend on the joined one.

Example: a Zendesk metric that needs two views

Tickets has each ticket's assignee but not how often it was reopened;
TicketMetrics has Reopens but not the assignee. Both have the ticket id.
To chart reopens by assignee:

1. Have a feed for each view. Generate the missing one from its view as in
   Creating a metric from a service, with the
   ticket id among its fields.

2. Read the TicketMetrics feed (GET /data_feeds/{metricsFeedId}) for its
   id and the ids of its Ticket ID and Reopens columns.

3. Read the Tickets feed (GET /data_feeds/{ticketsFeedId}) for its
   columns and ops.

4. PUT the Tickets feed with its columns plus two new ones, and ops that
   resolve its own columns, then join. If its stored ops hold more than a
   format (a filter, say), keep those as well, after the final format:

   JSON

   {
     "columns": [
       { "id": "e190a3817d86438da7e45315321b8572", "name": "Ticket ID", "type": "TEXT", "formula": "__source__@A:A;" },
       { "id": "2e06aa1c3fed4935ad1c8e26a1d6beb3", "name": "Assignee", "type": "TEXT", "formula": "__source__@B:B;" },
       { "id": "newcolumnTicketId7f3a", "name": "Metrics Ticket ID", "type": "TEXT",
         "formula": "733715a57c24cf19eb7a90fe4dc114fc#641b58722863c003fefeb7861177b9b6;" },
       { "id": "newcolumnReopens7f3a", "name": "Reopens", "type": "NUMERIC", "aggregation": "sum",
         "formula": "733715a57c24cf19eb7a90fe4dc114fc#f210d320aaeecb09cf40eafa733161ff;" }
     ],
     "ops": [
       { "type": "resolve", "dims": ["e190a3817d86438da7e45315321b8572", "2e06aa1c3fed4935ad1c8e26a1d6beb3"] },
       { "type": "format" },
       { "type": "join", "join_type": "LEFT_LOOKUP", "modelId": "733715a57c24cf19eb7a90fe4dc114fc",
         "dims": ["newcolumnTicketId7f3a", "newcolumnReopens7f3a"],
         "join_keys": [{ "left": "e190a3817d86438da7e45315321b8572", "right": "newcolumnTicketId7f3a" }] },
       { "type": "format" }
     ]
   }

   LEFT_LOOKUP keeps every ticket and takes its one metrics row; a ticket
   with no metrics row gets an empty Reopens.

5. Read the Tickets feed again for the permanent id of Reopens, then
   build the metric on it as in Creating a metric.
   The metric's measure is the Reopens column and its dimension the
   Assignee column, both of the Tickets feed.

The same steps bring in a dimension instead: join the feed that has it,
and name the new column in the metric's dimensions.

Enriching without a join op

LOOKUP(values, keys, results) in a column formula also reads another feed's
columns (Lookup), but those columns are
only available to a feed that joins that feed. Add the join as above, and use
LOOKUP for a column the join itself does not give you.