LinkedIn
Twitter
Hello,
To provide some context, I’m looking to make an “ARPPU Cohort”, which looks like something like this ;
Basically, I’m grouping all the users who made a first payment on each months, and evaluate their revenue behavior over the next months. Then I’m divided the revenue value by the size (count of user), and I get an “ARPPU” value (this part is not in the previous image) “average revenue per paying user”
To do that, I have a payment table which looks like this :
| event_date | event_of_first_payment | amount |
|---|---|---|
| 2021-03-01 | 2021-01-01 | |
| 2021-03-02 | 2021-01-01 | |
| 2021-03-02 | 2021-01-03 | |
| 2021-03-03 | 2021-01-04 |
It’s basically a row per payment, with the date of the event, and the associated date of the first payment for the user concerned by the event. It allows me to calculate a “time since first payment” for each payment, which is usefull to make the cohort I need.
And a size table which looks like this
| date | size |
|---|---|
| 2021-01-01 | 100 |
| 2021-01-02 | 125 |
| 2021-01-03 | 120 |
| 2021-01-04 | 110 |
which is the users number, who made a first payment, for each date
And my model looks like this ;
explore: payments{
view_name: payments
persist_with: daily
join: size{
type: left_outer
sql_on: ${payments.event_of_first_payment_date} = ${size.event_date};;
relationship: many_to_one
}
The problem is : some “Size” values are missing because the joining is made on the event_of_first_payment date and sometime there aren’t a row for every “event_of_first_payment” date on the payment table.
Like on the previous example, there is no payment with a “event of first payment” on the “2021-01-02”. So I’ll miss the associated size : “120”.
So you can see in the following image that the issue on real values, with the inconsistent sizing depend on the month (should be the same size value for one row)
So I’ll rather need to get something like that for the joined table;
| event_date | event_of_first_payment | Size |
|---|---|---|
| 2021-03-01 | 2021-01-01 | 100 |
| 2021-03-02 | 2021-01-01 | 100 |
| null | 2021-01-02 | 125 |
| 2021-03-02 | 2021-01-03 | 120 |
| 2021-03-03 | 2021-01-04 | 110 |
I need to fill the payment table with the missing dates (but the combinaison of the two dates ! event_of_first_payment and event_date). In this way, when the joining is done, I’ll get all the “size” values. Taking in consideration that a “event of first payment” date is always lower or equal to the “event_date”.
Do you have any ideas of a better approach to fix this issue please ?
I already tried to put a full outer join instead of a left join on my model, but it didn’t work (And I really don’t know why)
Thank you
