Ilkka Peltola

If you're in marketing (I'm not), you'd want to know how much bang-for-buck your campaigns are producing. Now I'm no Google Analytics expert, but I'm going to make a claim that you too might have an easy time measuring conversions across your campaigns, but not so much how your sign ups differ between them? Is one bringing in more high quality people than others? If you are running a web shop or some other transaction-based business then sure, you can set up Google Analytics Ecommerce  to follow up on customer life time value. But what if you are running a SaaS and you bill monthly? Or you have a freemium model and you hope to monetize the high volume users through some additional features, how would you compare your campaigns then? With only Google Analytics, it is tough to analyze how the users coming through different marketing campaigns differ from each other. What you need is raw data and to know the path each of your user has taken as they sign up to y

Have you ever struggled with calculating your active users per day, or month? How about your new activations or churned users? It's a common problem, but often the first solution you find will not scale. You should avoid sub queries, and use a window function instead. But that's only a part of the solution. Assuming your active user definition is "someone who has signed in within 7 days" and you have this data in a table with columns user_id , timestamp . The first thing you want to do is calculate when a user activates for the first time, when they churn and when they resurrect. CREATE OR REPLACE VIEW "vw_login" AS SELECT * , LEAST (LEAD("timestamp") OVER w, "timestamp" + 7) AS "activeExpiry" , CASE WHEN LAG("timestamp") OVER w IS NULL THEN true ELSE false AS "activated" , CASE WHEN LEAD("timestamp") OVER w IS NULL THEN true WHEN LEAD("timestamp") OVER w - "t

Ok ok I went along with Power BI but hear me out. A year ago I joined Zervant to be responsible for the company's BI and analytics. Pretty soon I had to make a choice: which BI package should we take into use. Before this, Zervant didn't use anything apart from Excel and a Geckoboard. From my previous positions at Nokia and Microsoft, I have experience in Tableau, QlikView  and Power BI, in that chronological order. It quickly came down to a choice between these three. Or to be honest - between Tableau and Power BI. Give a man a fish, and you feed him for a day. Teach a man to fish, and you feed him for a lifetime. I'm a big believer of self-service BI. I would rather build an analytics team that enables others solve their decision needs rather than  giving the right conclusions. Therefore, building something that can be used by anyone in the company was one of the goals. Also, joining a start-up meant cost was a concern. QlikView is in my experience complex

Back when we were deciding on an SQL flavor at Zervant , the fact that MySQL didn't have WINDOW functions was a deal breaker. We had plenty of MySQL experience and not that much in PostgreSQL but I had to go with PostgreSQL anyway - just because of WINDOW functions. But that's about to change, as MySQL is getting this support soon , too. Imagine you need to calculate the number of active users you have on any given day. Now imagine that the way you have defined an active user is as someone who has done something  on your service within e.g. 30 days. The something  might be e.g. uploaded something, downloaded something, made a purchase, edited their profile - doesn't matter. And imagine you want to split by dimensions. Without WINDOW functions you don't have good options. You can accomplish this by either sub queries, which gets very expensive: SELECT t.id, t.count, (SELECT SUM(t2.count) FROM t as t2 WHERE t2.id <= t.id) AS cumulative_