Users that didn't convert

A question frequently posed to our services teams is "I devote time and attention to learn all I can about users that convert, but what can I learn from users who do not convert?"

(Such questions regarding converting users tend to come up more among advertisers and agencies using DoubleClick for Advertisers, but even if you use Floodlight/Spotlight tags in DoubleClick for Publishers you've probably asked this same question.)

First, some ground rules about the term "non-converter".  We find that the term "non-converter" in practice refers to a number of different things; deciding which is most important to you is a good place to begin answering this question.  The following are groups that are commonly referred to as "non-converters":
  a) Users who saw your ads and visited your website, but did not convert.
  b) Users who saw your ads but did not visit your website (and thereby did not convert).
  c) Users who did not see your ads but did visit your website.

Data Transfer is especially good at helping you develop valuable insights about these groups.  Let's focus on the first two cases (however you can learn things about all three categories using DT).  How do you identify which exposure pathways or interaction patterns are truly valuable vs. those that are not?  How do you identify user interactions that tend to generate a large amount of conversions but also drive users away?  What patterns can be observed from those users who do not convert?  Your results may be quite surprising.  (Note, the following explanation requires that you receive and process all three Data Transfer types:  Impression, Click and Activity.)

Start by inventorying the user IDs from your Activity files over the exposure window of your preference, say 30 days.  Next, use these user IDs to compare against those found in your Impression and Click files over the same date range (you may choose to add one additional day to your Impression and Click files to account for visits that occur right after midnight).  At this point you're processing 60 days of Impression and Click files.  Now, identify those user IDs occurring in your Impression and Click files that either do not have an Event-ID value of '1' or '2' (the identifier signaling a click conversion or an impression conversion) -or - that do not appear in your Activity files at all.  Finally, sort the resulting Impression, Click and Activity records for each user ID in reverse chronological order by time.  The conclusion is a list of your users that were exposed to and may have interacted with your ads but, for whatever reason, did not convert.

It's at this point that you can take action such as using Remarketing, better content targeting, Creative Optimization to deliver a better message, or site optimization to improve user experience.  You may even opt to run a DFA Experiment which divides users into treatment and control segments for comparison of ad effectiveness.

--Matthew Trojanovich and Ryan DeVito, Data Transfer Team

Google BigQuery Service: Big data analytics at Google speed


(Cross-posted on the Google App Engine Blog the Google Enterprise Blog and the Official Google Blog) 


In a previous post we mentioned the Google BigQuery Service.  Development of this service has indeed progressed with Google speed; since that time we've introduced a graphical user interface, have made significant improvements in accessing the service programmatically through the API, and expanded the functionality of JOIN statements.  I thought you'd like to see a screen-shot.  

Please note that BigQuery is currently in preview and open to a limited number of enterprises and developers. Please sign up to get on the waitlist and be notified when you can start using BigQuery. For more information, take a look at the Getting Started document.


-- Matthew Trojanovich, Data Transfer Team

Google BigQuery and Data Transfer

Wouldn't it be great if you could program against your Data Transfer files without ever having to first download them from DoubleClick FTP, then transform them, then load them locally and only then analyze them?  Here's a sneak peek of something we're very excited about:  it's called BigQuery and it aspires all that and more using the cloud and Google's APIs.

Below is a snippet from a recent Google Code API post.  What would it mean for your business if all of your DT data were accessible in this way?

-----

In January of this year we launched BigQuery integration with Google Apps Script. What we didn’t mention was that we were building this on top of our Google APIs Discovery Service. Thanks to the ease and flexibility of writing clients based on this API, today we’re announcing integration with three more APIs, and revamping our BigQuery support.

As of now, we have also integrated the Tasks API, Prediction API, and URL Shortener API in addition to the BigQuery API. You can now include these APIs in your scripts, apps, and sites pages. As with other Apps Script services, we handle all of the server communications as well as authorization, which makes this a great way to build mashups and workflows using our APIs.

To get started, simply enable the APIs you’re interested in from the "Use Google API services" menu in the script editor. [...]

-- Matthew Trojanovich, Data Transfer Team

Tools to help you manipulate text data

You may be wondering what tools we recommend for working with Data Transfer and Match Tables. While there are many options, both commercial and otherwise, the following are some of our recommendations. What they all have in common is that they are tools designed for manipulation of large text files. These tools also share something else: they are all either free or open-source, so we hope that makes them even more useful for you. Some of these same tools are used by the DoubleClick Customization team - the DoubleClick team that builds the occasional DT-based custom report or application for clients.

So if you're looking for some alternatives to try out, we hope you find the following tools useful:

Crush-Tools - CRUSH (Custom Reporting Utilities for SHell) is a collection of tools for processing delimited-text data from the command line or in shell scripts. The tools are especially "Data Transfer aware" in that they understand DT headers, delimiters and column types. At the site find a tutorial and usergroup. A simple example of CRUSH is the following command line statement where familiar shell commands are enhanced:

gzdog <dt_filename> | grepfield -F Event-ID [12] | aggregate2 -K Site-ID,Activity-Sub-Type -S Revenue -L | pivot -P Site-ID,Activity-Sub-Type -A Revenue-Sum

"unzip and cat the DT file, select only post-click and post-impression activities, aggregate the revenue by Site and Floodlight tag, and pivot the results for each site/activity combination."

Google Refine - a power tool for working with messy data, cleaning it up, transforming it from one format into another, extending it with web services, and linking it to databases like Freebase. The tool can help make merging disparate data a breeze. Check out the engaging demo videos.

Google Fusion Tables (beta) - Upload small or large data sets from spreadsheets or CSV files. Visualize your data on maps, timelines and charts. Pick who can access your data; hide parts of your data if needed. Merge data from multiple tables. Discuss your data with others. Track changes and discussions. Drop a DT file into Fusion Tables and in short order you'll be able to graph and sift through the data through a spreadsheet like UI.

Google Visualization API - lets you access multiple sources of structured data that you can display, choosing from a large selection of visualizations. Thus you can create reports and dashboards as well as analyze and display your data through the wealth of available visualization applications. In terms of DT, once you've processed your files, make it visually useful!

-- Matthew Trojanovich, the DoubleClick Data Transfer Team