Posts

Showing posts from 2021

Best way to calculate your lifetime value

Image
When you talk with investors about what your SaaS business is worth, you need to calculate your customer lifetime value right. And while there are good shortcuts, using them might lead to a lower value than you want . In my role I've come to realize that the shortcuts rarely work well enough. In this article you'll learn the challenges in the common ways to calculate lifetime value and a concrete suggestion for a better one. Typically lifetime value is calculated by dividing your average monthly revenue per account (ARPA) by your monthly churn. You can get your ARPA by dividing your monthly recurring revenue (MRR) by the number of paying customers you have. And so, if your ARPA is 10 € and your monthly churn is 2%, your lifetime value would be 10/0.02 = 500. This is a great rule of thumb, but what if your churn fluctuates a lot month-over-month? If your business is seasonal, you might see your churn go up and down on a monthly basis. Can you just take the long term average? Can

Snowflake UPSERT operation (aka MERGE)

You want to insert data to a table, but if a corresponding row already exists (by some rule, e.g. unique key) you want to update that instead of adding a new row, keeping the dataset's unique requirements intact. That's an "UPDATE AND INSERT" operation, or UPSERT. Some SQL languages have native support for it.  PostgreSQL has UPSERT as native. Also MySQL supports the operation with INSERT and ON DUPLICATE KEY UPDATE. How do you do UPSERT on Snowflake? Here's how: Snowflake UPSERT i.e. MERGE operation Snowflake's UPSERT is called MERGE and it works just as conveniently. It just has a different name. Here's the simple usage: MERGE INTO workspace.destination_table d USING workspace.source_table s ON d.id = s.id AND d.val1 = s.val1 WHEN MATCHED THEN update SET d.val2 = s.val2, d.val3 = s.val3 WHEN NOT MATCHED THEN INSERT (id, val1, val2, val3) VALUES (s.id, s.val1, s.val2, s.val3); Here the destination_table and source_table are of similar form,

Popular posts from this blog

AWS Glue, Dev Endpoint and Zeppelin Notebook