Build Metrics for Experimentation about metricflow HOT 3 OPEN

What would need to change in metricflow code so this join can be generated?

FROM metricflow.user_assignments ua
LEFT JOIN fact.transactions e
ON ua.subject_id = e.subject_id AND  e.created_at BETWEEN ua.first_assigned_at and {{ analysis_date }}

I’m not sure where to start looking into this.

I played with metricflow as a new user a bit and I can kind of get to that SQL, except the join order I have is wrong: transaction on the left but I want assignments, so assigned customer with no transactions can have a null or 0 in the output.

from metricflow.

@tlento 👋 @Jstein77 said you would be a good person to look at this.

from metricflow.

Thanks for commenting, @TristanBoudreault!

I have a couple of thoughts:

1. Have you checked out the logic for SCD-style joins? The code is here. This is a similar pattern to what you're after, where assigned_at is the start of the validity window and analysis_date would be the end of the window. One difference is that the end of the validity window is not dynamic, so that might be an issue.
2. The way the joins are currently rendered is the inverse of what we need for experimentation. We need to LEFT OUTER JOIN the metrics table onto the assignment table.

Right now:

SELECT b.condition AS user__condition, SUM(1)
FROM transactions a
LEFT OUTER JOIN user_assignments b
ON a.user_id = b.user_id AND {{ date range stuff }}

What we need:

SELECT a.condition AS user__condition, SUM(1)
FROM user_assignments a
LEFT OUTER JOIN transactions b
ON a.user_id = b.user_id AND {{ date range stuff }}

We should probably add first class support for this type of join since it's used in any kind of custom sample tracking analysis where you want to always include all members of the in sample group and nobody else i.e experimentation, cohort metrics.

from metricflow.