Data Studio aggregation

Have you ever run into this error calculated fields can't mix metrics (aggregated values) and dimensions (non-aggregated values) when trying to set up a calculated field in a Data Studio report? This is slightly article explains what's going on, but I wanted to show why this happens from the perspective of developing a Data Studio connector.

Let's assume your order API returns this (raw) data (it's actually JSON, but this csv format is easier to visualize):

    order_id,SKU,quantity,price,item_revenue
    122,abc,1,12.00,12.00
    122,xyz,2,4.00,8.00
    123,abc,2,5.50,11.0

Each line is a distinct item, so order 122 contains two items and order 123 has one item. If you wanted to calculate total quantity or total item_revenue you can just add up the lines and get the result directly, i.e. metrics are aggregated. (Note that price should not be a metric as it would not make sense to sum all individual prices).

What about number of orders? Of course you'd like to know how many orders you had in a given time frame. That's easy: just count the number of distinct order ids. The formula is literally COUNT_DISTINCT(order_id), but what we are doing is counting a dimension not a metric. Let's call this metric Number of Orders.

OK, but now I want to know average order value. Should be just SUM(item_revenue)/Number of Orders and Bob's your uncle, right? Not so fast, since this would result in the above error. Since order_id is a dimension you cannot sum it like a metric and it's not possible to figure out the number of orders while you go through each line to sum up item_revenue. Another way to understand this is that when you calculate Number of Orders all other information (eg quantity and item_revenue) is lost and you're just left with a single number.

So is there no way to get average order value? Au contraire, it just has be calculated at the connector level as a separate metric:

    {
        name: 'average_order_value',
        label: 'Average Order Value',
        description: 'Net Sales divided by Orders',
        dataType: 'NUMBER',
        formula: 'SUM(net_sales) / COUNT_DISTINCT(order_id)',
        semantics: {
        semanticType: 'NUMBER',
        semanticGroup: 'NUMERIC',
        conceptType: 'METRIC'
        }
    }