Call us at 970.744.3340|Login
Jan 28
Orchestrating a high-quality reporting solution for digital advertising

Orchestrating a high-quality reporting solution for digital advertising

An important feature we provide at Choozle is reporting on the performance of every advertising campaign created on our platform. This information is critical to marketers; without it, you’re unable to gauge the effectiveness of your advertising campaigns and make informed decisions about how to adjust your ad spend or other campaign parameters.

Building a solution to provide this reporting is not a simple task. From data source to dashboard, we use several software tools to create meaningful data representations you can use to optimize your future campaigns. We have developed an analytics reporting tool that:

Continually ingests data from our partners

Transforms data into a format optimized for reporting

Displays the data in an understandable and easy-to-digest format


Developing a robust reporting and data capture infrastructure is cumbersome and time-consuming. But having access to data engineers who understand how to collect, translate, and map your data into useful visualizations will set you apart from the competition and ensure you get everything you need to fully understand your campaigns’ results.

Below we summarize some of the common challenges we help our partners overcome when leveraging our detailed reports:

Lack of bandwidth or know-how to develop a robust performance framework

Disparate platforms that don’t provide a holistic view of omni-channel performance

Unable to set up a structure for managing and collecting conversion data

Providing return on investment (ROI) or ROAS (return on ad spend)

Understanding lead quality

Continuous monitoring of data downtime

Now let’s start to understand how our team of data engineering experts accomplishes these tasks and how we continue to build additional functionality into our architecture.


Choozle reporting architecture

Our approach to providing campaign performance analytics at scale has four main components. First, we store impression-level data from our providers into Snowflake, a secure data warehouse optimized for analytics. This allows us to perform any analysis we choose on the data. From there, we transform the data into views that provide the most value for tracking campaign performance. The data from these views is then fed into a business intelligence (BI) solution for visualization. Finally, the dashboards are created from our BI solution are served via our applications.

Ingesting impression-level data to power key insights

Our data ingest process can happen in several ways:

We leverage Snowflake to ingest data from each of these sources because it helps us securely and seamlessly integrate your most relevant data in real-time. Snowflake has a built-in orchestration framework that allows us to automatically schedule the extract, load, and transform (ELT) from each source into our collection of tables and views that matter. Snowflake handles all the infrastructure complexity so we can focus on transforming your data into something you can easily understand and use.

Monitoring data ingestion

A key aspect of our data ingestion process is auditing the health of our data tables and reducing data downtime. Observing the continuous flow of campaign performance data from multiple channels allows us to trust our data’s safety, reliability, and accuracy before it is transformed.

We leverage Monte Carlo, a machine learning based data observability platform, to monitor and evaluate data quality to ensure our reporting and visualizations are built on the most accurate data possible. Advanced analytics are used to raise alerts when anomalies occur (e.g., data not flowing, incorrect data, or too much data).

Transforming data into views that matter

In order to create the data visualizations quickly, we first transform it within Snowflake into tables and views that store the data in an optimized way. We organize metrics by campaign, geography, contract information, ad environment, ad format, device, fold position, site, and supplier to ensure that we can quickly display the data in our BI tool.

Snowflake’s built-in orchestration framework (task trees) is used to manage updating the tables and views that power our reporting. This framework can define root tasks and dependent tasks that run after the root task. The root tasks can be run on a schedule, ensuring your data is constantly updated.

Snowflake is a powerful partnership we leverage to ensure our data and analytics reporting platform is secure, accurate, scalable, and low maintenance. Once we have our data organized we visualize it to be easily digestible and useable for optimizing your campaigns.

Visualizing the data

We leverage Looker to visualize the data we store in our views that matter. Looker allows us to quickly develop and maintain dashboards that we can serve. Looker is highly responsive and adaptable tool that allows us to create a dashborad that can be considered a single source of truth for your campaign data so you can understand relevant data visualizations with clarity and use the freshest insights to optimize your campaigns. Some other advantages of Looker include:

LookML - Looker has a custom language called LookML that allows us to specify which tables in Snowflake we want to make available for visualization. This language allows us to specify which fields should be made available (called "dimensions") and any custom calculations (called "measures") to specify how tables should be joined. Looker uses the LookML code to generate the SQL that retrieves data for visualization in Snowflake.
Dashboards - Once the LookML has been defined, we create dashboards in Looker that can be served within our application. We are able to do this quickly since our LookML code has provided any necessary calculations, and we can instantly update the dashboard if needed. From a support perspective, this is a huge benefit—no need to rebuild or redeploy any services to update a Looker dashboard. In addition, the dashboards can be defined as code, which allows us to easily version control any changes. Debugging is also quick since we can easily see the underlying SQL used by any dashboard when it runs.

Serving data on Choozle applications

Once the dashboards have been tested and QA’ed they can be published to our applications and you can see them on your detailed reports dashboard. We utilize feature flagging to ensure you experience zero downtime while dashboards are updated.


Data flow

Below, you can see a high-level view of our architecture and data flow, from source data (The Trade Desk, Amazon, etc.) all the way to the Choozle application. Let’s have a closer look at this by breaking it down into four steps: initial ingest, transform, visualization, and publishing.

Step 1 – Initial ingest
Taking in data from every source is vital to representing your omnichannel marketing efforts, that’s why ingest data in several ways, including Snowflake S3 Stages, Snowflake Shares, and Fivetran.

We are also capable of ingesting data into Snowflake via a Google Cloud Storage integration or any other method in which Snowflake can ingest data. All the data we ingest is loaded into staging tables in our data warehouse, Snowflake.

Step 2 – Transform
After we have ingested the data, we make use of Snowflake’s built-in orchestration layer to transform the ingested data. The output of this process is the data that is loaded into tables and views within Snowflake that are optimized for your reporting needs. Note that steps one and two comprise an ELT process.

Step 3 – Visualization in Looker
Using LookML we configure Looker to query the optimized tables and views from step two in order to provide source data for our dashboards. Once this is done, we can build and test the dashboards themselves.

Step 4 – Publishing the dashboards to Choozle
Once the dashboards have been tested and QA’ed, they can be published to our applications to be seen by you. We deploy via feature flagging with zero downtime between dashboard versions so your update data is populated seamlessly. It is not necessary to build/test/deploy any backend services to enable new reporting features in the dashboards themselves.


Machine learning campaign predictions

With all the years of campaign data stored efficiently using the tools mentioned above, we are able to leverage this data further allowing machine learning algorithms to make predictions about future campaign performance. We take the past performance data for all campaigns and then fine-tune an algorithm to a specific campaign in order to predict their future conversions, clicks, and impressions across various budgets to allow you to determine how to best utilize your money.

The workflow of the machine learning algorithms follows some of the same steps as outlined above:

  • Read from data stored in Snowflake
  • Run machine learning algorithms using TensorFlow software
  • Export results into Snowflake
  • Visualize results in the Looker application

Analytics and reporting are the bedrock that marketers rest their campaign optimizations on. That’s why we are focused are creating the best reporting solutions for our partners. In order to give you access to the live data you need for every campaign you run, we ingest and process all of your data into tables and views that actually matter, ensuring every insight we provide is accurate and easy to understand. If you want to learn more about our detailed reporting dashboard reach out to your Account Manager to find out what tools and optimizations are available.


About the Authors:

Steven Cahail is a Senior Data Engineer at Choozle. Steven is a methodical problem solver who plays an integral part in creating the powerful tools that connect marketers to in-depth campaign results in Choozle.

Coleman Word is a Principal Software Engineer at Choozle. Coleman’s deep knowledge and attention to detail is the antidote to some of the deepest head-scratchers the engineering team faces.

Andrew’ Zap’ Zapotosky is a Senior Product Manager at Choozle. A seasoned project leader and curator of stories, Andrew works as an integral collaborator across Choozle as we build out our platform.

Callie Federer is Choozle’s Lead Data Scientist. She helps us predict the future with data. Her expertise allows marketers to make decisions by leveraging years of campaign data to predict outcomes.

About The Author