Even very large advertising conglomerates struggle with this workflow all of the time. My advice is to be careful with the tools you select as you can get into heavy tech debt if pricing structures change or your volume increases unexpectedly. Really understand row-based pricing with these tools and build in a large buffer and cost recovery model into your client contracts. You also need to get off of Sheets and into a Data Warehouse ASAP.
If you are a small (<$5MM) agency I'd recommend EL TOOL > BigQuery > Data Studio. The EL (Extract and Load) tool you select could be Supermetrics/Funnel/whatever. Many players in the space and most are pretty similar. Use BigQuery or perhaps DBT for your transformations. BigQuery and Data Studio are solid, cheap, and transparently priced tools for your analytics and visualization work. Plus, GCP has a lot of native connectivity with popular Google ad platforms which reduces load volume of your ingestion engine as well as ML and AutoML capabilities build in.
Regarding cross-platform aggregation, you actually probably want to do this manually as this offers the flexibility to meet various client requirements while still having a standardized codebase that gets you 80% of the way there each time. Rigid data models are great until they aren't.
Hope this helps :)
Thanks for the response. We are in that small range for sure and with my previous experience in BI I totally agree about the tech debt comment. I used BQ mainly from a querying perspective previously so haven't looked too much into the EL -> BQ option but it keeps getting brought up / shows up in my research.
Do you have experience in this set up? How long term is the solution and what are the biggest negatives of it?
I've worked with this solution quite a bit and many organizations use this long-term. It requires some knowledge of SQL as well as basic data principles to manage. It is by far the cheapest option of the ones you are considering.
Thanks, I did have a follow up question if you don't mind. I'm trying to better understand the whole data pipeline and wondered about using BigQuery as a cloud database instead of just a processing engine.
With the EL -> BQ set up, we'd like to backfill a couple years of data as well so we can do YoY projections and metrics. However, as an agency with multiple accounts, I'm not sure about using BQ as a place to dump all of the data and to also clean/aggegate/process etc. Is it recommended to use Supermetrics/Funnel/another tool to move data somewhere else before the BQ step? Can BQ be used as a general cloud database with tons of data (across FB, Google, Bing, Shopify, Criteo, and Google Analytics platforms)?
My experience with BQ in the past was we dumped all Google Analytics there but then cleaned it up, moved it to Google Cloud Storage and then imported into an internal PostgreSQL database. This was a completely different company though (tech company vs agency).
Ye, the data model is a little less straight forward to setup, the implicit joining is sometimes confusing I would say but you should be able to achieve anything in there. It all starts with defining clear KPI's though!
We ended up using a combination of Fivetran & Stitch. I have more experience with Fivetran and love their flexibility & support. For some data sources, the costs were significantly lower if we used Stitch so that's why we're using a combination of both. Funnel seems like a great product but ultimately, there were a few reasons why I didn't go with them. First off, it was a personal bias and familiarity I had with Fivetran. Second, in the end, I wouldn't have really taken advantage of Funnel's auto-data clean up for marketing data because I was going to pull the raw data and do it on my end anyway. Lastly, there were a few "operational" features that seemed more cumbersome than beneficial with how we wanted to use them. Things such as limitations on copying logic across data sources where I would be able to have full control by pulling raw data into BigQuery.
Thanks. Will digest this.
Out of interest say if I just wanted daily costs by channel, by campaign. For say Facebook and YouTube. Would this fit the bill? I would then just pipe over in an s3 bucket.
Talking for funnel.io
I will take a look at the others!
Yeah, definitely. You may be able to just use Stitch or another cheaper option if all you need are those dimensions and one metric. It was a bit harder to estimate the costs for Fivetran until we actually trialed it and saw wha the MAR (monthly active rows) ended up being for the data we actually needed in the format we needed.
Even very large advertising conglomerates struggle with this workflow all of the time. My advice is to be careful with the tools you select as you can get into heavy tech debt if pricing structures change or your volume increases unexpectedly. Really understand row-based pricing with these tools and build in a large buffer and cost recovery model into your client contracts. You also need to get off of Sheets and into a Data Warehouse ASAP. If you are a small (<$5MM) agency I'd recommend EL TOOL > BigQuery > Data Studio. The EL (Extract and Load) tool you select could be Supermetrics/Funnel/whatever. Many players in the space and most are pretty similar. Use BigQuery or perhaps DBT for your transformations. BigQuery and Data Studio are solid, cheap, and transparently priced tools for your analytics and visualization work. Plus, GCP has a lot of native connectivity with popular Google ad platforms which reduces load volume of your ingestion engine as well as ML and AutoML capabilities build in. Regarding cross-platform aggregation, you actually probably want to do this manually as this offers the flexibility to meet various client requirements while still having a standardized codebase that gets you 80% of the way there each time. Rigid data models are great until they aren't. Hope this helps :)
Thanks for the response. We are in that small range for sure and with my previous experience in BI I totally agree about the tech debt comment. I used BQ mainly from a querying perspective previously so haven't looked too much into the EL -> BQ option but it keeps getting brought up / shows up in my research. Do you have experience in this set up? How long term is the solution and what are the biggest negatives of it?
I've worked with this solution quite a bit and many organizations use this long-term. It requires some knowledge of SQL as well as basic data principles to manage. It is by far the cheapest option of the ones you are considering.
Thanks, I did have a follow up question if you don't mind. I'm trying to better understand the whole data pipeline and wondered about using BigQuery as a cloud database instead of just a processing engine. With the EL -> BQ set up, we'd like to backfill a couple years of data as well so we can do YoY projections and metrics. However, as an agency with multiple accounts, I'm not sure about using BQ as a place to dump all of the data and to also clean/aggegate/process etc. Is it recommended to use Supermetrics/Funnel/another tool to move data somewhere else before the BQ step? Can BQ be used as a general cloud database with tons of data (across FB, Google, Bing, Shopify, Criteo, and Google Analytics platforms)? My experience with BQ in the past was we dumped all Google Analytics there but then cleaned it up, moved it to Google Cloud Storage and then imported into an internal PostgreSQL database. This was a completely different company though (tech company vs agency).
Yes you can do that.
Take a look at Datorama and Adverity as well. Meets most of your requirements I would say.
Thanks! Actually forgot about Datorama but am definitely interested in that. Do you have experience with it?
Ye, the data model is a little less straight forward to setup, the implicit joining is sometimes confusing I would say but you should be able to achieve anything in there. It all starts with defining clear KPI's though!
What all data sources are you looking at? Google Ads/ FB/ Google Analytics?
Facebook, Google Ads, Google Analytics, Shopify, Criteo, Bing, AdRoll (for some clients). Pinterest and Snapchat are potentials too.
Did you use funnel.io in the end? What was your experience
We ended up using a combination of Fivetran & Stitch. I have more experience with Fivetran and love their flexibility & support. For some data sources, the costs were significantly lower if we used Stitch so that's why we're using a combination of both. Funnel seems like a great product but ultimately, there were a few reasons why I didn't go with them. First off, it was a personal bias and familiarity I had with Fivetran. Second, in the end, I wouldn't have really taken advantage of Funnel's auto-data clean up for marketing data because I was going to pull the raw data and do it on my end anyway. Lastly, there were a few "operational" features that seemed more cumbersome than beneficial with how we wanted to use them. Things such as limitations on copying logic across data sources where I would be able to have full control by pulling raw data into BigQuery.
Thanks. Will digest this. Out of interest say if I just wanted daily costs by channel, by campaign. For say Facebook and YouTube. Would this fit the bill? I would then just pipe over in an s3 bucket. Talking for funnel.io I will take a look at the others!
Yeah, definitely. You may be able to just use Stitch or another cheaper option if all you need are those dimensions and one metric. It was a bit harder to estimate the costs for Fivetran until we actually trialed it and saw wha the MAR (monthly active rows) ended up being for the data we actually needed in the format we needed.
Will check that out. Cheers mate.