bigquery

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.

In performance marketing, attribution refers to repartition of revenue to marketing channels, for return-on-investment (ROI) calculations. While most attribution schemes are rule-based, we here present a data-driven scheme. Context The attribution problem was first presented in our previous article: Using alternative attribution models To sum it up, with increasingly numerous digital marketing channels (about 40 for Sephora Digital !), deriving ROI for each of them is a real hassle. In other terms, if a customer

BQ to GSheet connector Have you ever dreamt of connecting a GSheet document to BigQuery ? Being able to have data refreshed whenever you want just with the help of a SQL query ? If you recognize yourself, you are reading the right article! Here we go => The Ultimate Guide to Integrate A BQ Query Into A GSheet Create a Google Sheet Tool > Script Editor Choose a name for your project Resources > Advanced Google Services > Big Query Your are redirected to Google Cloud Platform > Enable Google Big Query API Copy / Paste this script in the editor:

Partitioned tables As you know, for cost effectiveness, some datasets are split in several tables in BigQuery. That’s particularly the case for the Google Analytics tables: ga_sessions_YYYYMMDD. The reason why these tables are split is that we can query only a subset of the whole data. Example: query to collect one week of visits per country: #standardSQL -- Query 1 SELECT date AS `date` , geoNetwork.country AS `country` , SUM(totals.visits) as `visits` FROM `XXXXXXXX.

When we implemented our ETL (Extract-Transform-Load) between Trello and BigQuery, we faced an issue: Trello wasn’t exposing the creation dates of the cards. After investigating in their documentation, they revealed that they were using MongoDB ID as the exposed IDs, and the timestamp is deductable from it. The rule to deduct the timestamp is not too complicated, and well explained in this article. From this rule, we wrote a BigQuery UDF as we might need it quite often for Trello Analysis.

Finally! Biguery released on December 2016 their hashed functions. This was a relief until we discovered that the only possible output codec was … BASE64 (This is how BYTES are represented in BQ). And yes, that was literally killing us as all our representations of these hashed were in hexadecimal! Our solution Of course there is no base conversion function integrated in BQ! So we found a way to create our own function, using BQ’s UDF (User Defined Functions), and we have to tell: it rocks!

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.