How we use Dataform to monitor campaign conversions at Outshine | Dataform
Guide

How we use Dataform to monitor campaign conversions at Outshine

How Outshine use assertions in Dataform to monitor conversions for their clients.

Data modelingAnalytics
picture of author

Erin Hayes @ Outshine on February 17, 2020

Successful paid marketing campaigns rely on conversion tracking in ad platforms and reporting systems. Tracking not only provides a feedback loop to marketers to demonstrate which campaigns are most effective, this data is also essential for the machine learning algorithms powering ad platforms to learn and serve more relevant ads to users.

However, conversion tracking is notoriously hard to implement correctly.

Because most conversions are configured client-side (as opposed to server-side), conversions can behave unpredictably when changes are made to HTML markup or user experience. And if you’re responsible for monitoring the health of conversions across multiple campaigns and accounts, it’s nearly impossible to do this manually.

The negative impact of broken conversion tracking can’t be understated.

It can range from marketers having low confidence in campaign performance reports, to Google Ads proactively pausing bidding for placements if the algorithms determine that campaigns are failing to convert. That means wasted ad budgets and time.

Identifying issues with conversion tracking early—before they can impact campaign

Get To Issues Early

At Outshine, we’ve developed systems to ETL data from ad accounts and Google Analytics to data warehouses. While the main purpose of these warehouses is to enable accurate reporting, we realized that the conversion data could also be used to generate alerts when issues with conversions arise. In addition to ETL pipelines, we now use a data transformation platform called Dataform to model and maintain data in our warehouses.

Here’s a guide on how to use a key feature of Dataform—assertions—to alert you when issues with conversion tracking arise.

The Method

Assertions in Dataform are SQL queries that check the state of your data based on pre-defined logic. In its simplest form, the example below alerts a user when a specific metric exceeds a threshold (in this case when ad spend exceeds $1,000):

SELECT campaign
,spend
FROM `table`
WHERE spend > 1000

If the query returns any rows (i.e. the WHERE condition has been met), the assertion has failed, and the user will be alerted. Within Dataform, assertions can be scheduled to run daily (or whatever frequency you choose), and users will be notified by email or Slack if the assertion fails.

To monitor conversion tracking, we wrote assertions to alert us if Google Analytics goal completions drop below a certain threshold within any given day. To do so, we use SQL’s date_diff function to report on the previous day’s goal completion counts.

config {
 type: "assertion"
}

WITH aggregate_goals_by_day AS (
 SELECT
   date,
   SUM(goal_completion_1) AS content_download,
   SUM(goal_completion_2) AS demo_request,
   SUM(goal_completion_3) AS webinar
 FROM
   datasource
 WHERE
   DATE_DIFF(CURRENT_DATE, date, DAY) = 1
 GROUP BY
   1
)
SELECT
 *
FROM
 aggregate_goals_by_day
WHERE
 content_download < 1
 OR demo_request < 1
 OR webinar < 1

The beauty of assertions in Dataform is they are scalable across multiple clients and accounts. Assertions save analysts valuable time because they remove the need to manually monitor metrics within the ad platforms, and push notifications to us only when something has gone wrong.

Assertions can be customized to meet the needs of the client or account and can use whatever thresholds, metrics, or groupings that require monitoring.

Other Use Cases

Monitoring key metrics:

  • Conversion rates between marketing funnel stage (lead to MQL, for example)
  • Violations in service level agreements
  • Ad spend
  • Changes in sales pipeline metrics (MRR, new pipeline generated)

Building QA checks into your data modeling & reporting process:

  • Detecting duplicates or nulls in data
  • Checking key uniqueness

Creating dependencies in the data modeling process:

  • Preventing other jobs from running if assertions fail

The Rise Of Push vs Pull Analytics

We believe that the developments in the world of business intelligence in 2019 mean that more and more data teams will be moving to “push” models. Creating systems that push insights to people, rather than relying on data professionals to pull information from technology.

Monitoring conversion tracking using a tool like Dataform is a perfect example of where the modern data team doesn’t passively wait for marketing, product, or management teams to ask for answers to questions. Instead, we can proactively bring solutions to problems a team may not even know they had.

More content from Dataform

Dataform is joining Google Cloud

Announcement

Dataform is joining Google Cloud

Today we're excited to announce that we've joined Google Cloud. Over the course of the past several months, our partnership with Google Cloud has deepened and we believe that our new combined efforts can make our customers and partners even more successful.
Learn more
Using Javascript to Create an Acquisition Funnel Dataset illustration
Tutorial

Using Javascript to Create an Acquisition Funnel Dataset

How to use a combination of SQL and Javascript to create an acquisition funnel dataset.
Learn more
How to add a new warehouse adapter to Dataform illustration
Guide

How to add a new warehouse adapter to Dataform

I recently wrote a Presto adapter for Dataform during a week long quarterly hackathon. Here's a guide on how I'd do it again.
Learn more

Learn more about the Dataform data modeling platform

Dataform brings open source tooling, best practices, and software engineering inspired workflows to advanced data teams that are seeking to scale, enabling you to deliver reliable data to your entire organization.