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.

Hi all ! A few weeks ago, Aero, our in-house campaign management tool got a huuge update, not only enabling razor-sharp tracking, but also improving communication with our external vendors, and clear repartition of roles along the creation of campaigns flow. Tracking enabled for groups of campaigns, and refined down to email creative precision, with each intermediate level tracked as well (campaign, ad, segment, slice). You can now know where do your customers come from at each level of grain !

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.

This article explains how to use the Red Zone Calendar in our in-house campaign management tool, Aero. A campaign is considered red zone if the estimated traffic is sufficiently high that it will be necessary to notify the tech team and allow them to scale in advance. Get the Red Zone Calendar Are you here to get the Red Zone Calendar generated by Aero ? You are on the right post, follow me !

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:

The spider’s web is growing! After the Spider’s dashboard, let’s introduce fresh audiences. How do I keep my audiences fresh? Everyday, Spider is updating more than 20 audiences in Facebook and Adwords. We are sending a list of users who belong to the segment to our marketing channels and it’s almost trivial to get those lists from Big Query. What if a user doesn’t belong to this segment anymore? Is he still in the audience?

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.

Facebook, Criteo, Adwords, Ematics, Dynamic Yield and many more. You might know well at least one of those companies with whom we work. Those companies can provide us great tools and data because we are sharing with them valuable data: our tracking data. That’s where marketing pixels come in! Marketing/Advertising pixels are small snippets placed on the website to collect information about the visitor and his online behaviour. From this data, you can set up your goals, evaluate your conversions and so on.

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.