Hi there,
I want to calculate the lifetime value of any customer, our organisation makes money per yearly subscriptions. Im using a ‘subscription table’ and a ‘product price table’ for this, to combine the right price with the right sub. For a lifetime value I need the ‘average amount of subs per user’ x ‘the average price of a sub’.
the average amount of subs per user = total amount of subs [130k] / total amount of unique [61,5k] users.
the average price of a sub is the sum of all subscriptions [1.5m] / total amount of subs [130k]
therefore if I delete the 130k’s i have to divide 1.5m by 61,5 and get the same result = 24,23. Now I would like to do this in Metabase
- so when i calculate 1,5m I’ve got the following sql, source price and source count are calculations I’ve made in the editor. I haven’t included the ‘group/order by’ ;
SELECT sum((“source”.“price” * “source”.“count”)) AS “total_amnt”
FROM (SELECT “public”.“subscriptions”.“product_price_id” AS “product_price_id”, " Product Prices".“price” AS “price”, count(*) AS “count” FROM “public”.“subscriptions”
LEFT JOIN “public”.“product_prices” “Product Prices” ON “public”.“subscriptions”.“product_price_id” = “Product Prices”.“id”
- 61,5 is done by the sql code
SELECT count(distinct “public”.“subscriptions”.“person_id”) AS “count”
FROM “public”.“subscriptions”
Now I’m having trouble combining these through both sql and in the editor. I’ve tried the editor by re-adding the subscriptions table and then i do get the right number for the amount of unique users, but i get a multiplied number of the total amount of al subs. My biggest issue is that i don’t understand when I’ve used a joined table and trying to combine it again with an aggregation of unique values of one of those same tables. When I’ve tried the sql my code is like this, but i keep on getting syntax errors…
SELECT sum(“Product Prices”.“price” * “subscriptions” “count”) / count(distinct “public”.“subscriptions”.“person_id”) AS “lifetimevalue”
FROM “public”.“subscriptions”
LEFT JOIN “public”.“product_prices” “Product Prices” ON “public”.“subscriptions”.“product_price_id” = “Product Prices”.“id”
GROUP BY “public”.“subscriptions”.“product_price_id”, “Product Prices”.“price”
ORDER BY “public”.“subscriptions”.“product_price_id” ASC, “Product Prices”.“price” ASC
Hope explanation is clear. Thanks in Advance.