sql

Hi everyone, Wether you are newbie SQL writer, an experimented BigQuery novelist with a volatile memory, or a visitor in quest of good practices, this article is for you ! So here is the situation: after hours of thinking and writing and testing, you have came up with a cool query that you are super proud of, a query that shows exactly the insights you were looking for, a query to make queries great again.

Hey you, do you like big queries ? Big big queries, so big they can’t even fit in a CSV file ? We, in the data team, know the trouble. And we have a solution. As you may know, on Google BigQuery, queries that have more than 10.000 rows cannot be directly exported as CSV files. On top of that, if your query includes a nested field, it won’t be able to export the result as a CSV file regardless of its size.

Update: added model_40_20_40_landing_page table This article is following this previous article: Using alternative attribution models We’re now using the 40-20-40 attribution model, and to help your daily attribution calculations, we integrated 2 new tables in BigQuery: ga.model_40_20_40 ga.model_40_20_40_campaign ga.model_40_20_40_landing_page These tables are refreshed every day, and are representing the score to attribute to each channel (for the first table), to each campaign (for the 2nd table), and to each landing page (for the 3rd table) for each order.

Working with absolute values (like net revenues) can be good indicators, but they only depend on the context. If yesterday’s revenue was $ 1M: was it a good or a bad day? If the daily revenue is usually $ 455k, it was a pretty good one. On the opposite, if usually the revenue is around $ 3M, we definitely missed something yesterday! Knowing that, a good solution would be to manipulate relative numbers: yesterday’s revenue divided by the day before, or even better, yesterday’s revenue divided by an average of N previous days.