Google's DoubleClick Data Transfer Service

Users that didn't convert

noreply@blogger.com (Anonymous) — Tue, 28 Feb 2012 14:35:00 -0500

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

noreply@blogger.com (Anonymous) — Thu, 17 Nov 2011 05:23:00 -0500

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

noreply@blogger.com (Anonymous) — Fri, 16 Sep 2011 11:43:00 -0400

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

NOTICE - Doubleclick Data Transfer files for 2011-08-09 should be re-downloaded

noreply@blogger.com (mtroj_google) — Fri, 12 Aug 2011 14:11:00 -0400

We have experienced an issue with the processing of Data Transfer files for August 09, 2011. The originally posted files may contain incorrect or incomplete data. All files have been corrected and re-uploaded to the FTP server as of 1:30 PM ET, August 10, 2011. All files downloaded prior to this time should be re-downloaded to ensure accurate data. Please note that any custom reports using Data Transfer should also be re-downloaded. Our technology teams are monitoring the system to ensure this issue does not reoccur.

We thank you for your patience and apologize for any inconveniences this may have caused.

Regards,
DoubleClick Media & Platforms Solutions

Upgraded Match Tables

noreply@blogger.com (mtroj_google) — Tue, 19 Jul 2011 13:46:00 -0400

We've recently updated the available Match Tables for DFA, DFP and Rich Media. If you'd like to upgrade to the "latest and greatest" we encourage you to get in touch with your Account Manager to make a request. Upgrades to Match Tables incur no cost and can usually be turned around in just a few days.

What updates have we made?
Primarily we've added new fields to existing Match Tables. We've also added a few new Match Tables and, in a small number of cases, removed defunct or rarely used fields. With the additional files and fields you gain access to new product features and improved visibility of existing product features.

Specifically, these are the changes:
For DFA - new ad_comments file, new mobile_keyvalues file, new page_cost file, new page_flight_cost file; updated fields in the advertiser, campaign, page and creative files; updated all date fields to be expressed in the same format mask as your DT events (MM-DD-YYYY); removed unused creative fields; improved format request form.

For DFP - new ad_comments file, new mobile_keyvalues file, new system_defaults file; updated fields in the advertiser, order, order_pricing, ad and creative files; updated all date fields to be expressed in the same format mask as your DT events (MM-DD-YYYY); removed unused creative fields; improved format request form.

For Rich Media - Improved format request form.

How can you tell if you are already using the new files?
You can request a copy of the current DT/MT setup form from your Account Manager. Compare the files and fields therein looking for the above changes. We tried to bundle as many changes as we could into this new MT version so that you don't have to worry about repeated MT changes. Finally, to be clear, no MT changes are made to your files without you first requesting that change from us.

We hope you find the improvements helpful to your data analysis efforts.

-- Matthew Trojanovich, Data Transfer Team

NOTICE - DoubleClick FTP audit and clean-up, part 2

noreply@blogger.com (mtroj_google) — Fri, 15 Jul 2011 15:09:00 -0400

About a month ago we announced that we were conducting a clean-up with regard to DoubleClick FTP login accounts, files and folders. We're happy to report that work was completed without any noticeable affect to you.

We'll be completing an additional phase of this clean-up on Tuesday, July 19th. On that date we'll be updating select user accounts which have 'delete' or 'modify' privileges on files and folders.

The reason for this change is to protect your files from inadvertently being deleted by, well, you. We do our best to re-post files in such cases, but this is not always possible. We believe this measure ultimately protects your valuable business data and is, on the balance, the best course of action.

If you have concerns with this action, please contact your Support rep through the usual channels:

dfa-support@google.com
dfp-support@google.com

Please include your FTP username (but not password) in that email.

-- Matthew Trojanovich, Data Transfer Team

NOTICE - DoubleClick FTP audit and clean-up

noreply@blogger.com (Anonymous) — Fri, 10 Jun 2011 13:57:00 -0400

Do you pick up files (DT files, Match Tables, Custom Reports, etc) from the DoubleClick FTP server?
If so, then this notice is for you.

During the week of June 13th - 17th we'll be doing a bit of clean-up with regard to files and folders. Specifically, we've completed a review of read/write/delete privileges and will be taking action on those user accounts which do not have approval for write and/or delete. If you have write and/or delete privileges but are not sure whether you have approval for them get in touch with your local support rep through the usual channels:

dfa-support@google.com

dfp-support@google.com

Please include your account/username (but not your password) in that email.

And, as a general note, we strongly recommend that you not share your DoubleClick FTP account information with any outside party. (Refer to our previous Secure Your Login post).

-- Matthew Trojanovich, the DoubleClick Data Transfer Team

Tools to help you manipulate text data

noreply@blogger.com (mtroj_google) — Thu, 21 Apr 2011 16:45:00 -0400

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

Aggregated performance reporting for DFA

noreply@blogger.com (mtroj_google) — Tue, 15 Mar 2011 13:49:00 -0400

Today we're happy to announce version 1.13 of the DoubleClick for Advertisers (DFA) API.
Why are we mentioning it here in the Data Transfer blog? Well, if you use our DFA product then it's likely that you use some combination of event level data mixed with aggregated data to generate your digital advertising insights. We'll discuss why v1.13 is notable as well as how you can make greater use of the aggregated reports from the API.

The DFA report service

The release marks the first time that a web services API interface is available for DFA's Report Central. Previously, if you wanted to programmatically extract reports you had to either pick up file attachments from scheduled email reports or use the Java-only DFA API reporting interface. For automated systems and ETLs, the email attachments approach is only really suitable as a fall-back mechanism. And while the Java interface offers a full-featured programming interface (including gets, sets, creates and deletes), it can result in more effort on your part having to develop both in Java and whatever other language you use to interact with the DFA trafficking web services interface.

To help solve these problems the DFA API v1.13 release includes a new report service that allows you to generate and retrieve reports from predefined queries. Check on them periodically to see when they have finished being compiled and retrieve a URL where you can download the completed report.

A report produced by the API works like any other in the DFA reporting interface; like your Data Transfer files, it can be published every morning, local time. Typically you'll be able to retrieve your report at the same time that your DT Match Tables are done.

Using aggregated performance data to compliment DT event data

So when can it be useful or even required to use aggregated reporting data? There are two general cases: (1) letting DoubleClick do the simple aggs for you, (2) the metric you're interested in isn't offered within Data Transfer. For the first, we see customers using aggregations to provide ready-made reports for their users. This can free-up your time to focus on higher value reports - typically those that are user (User-ID/cookie) based. We also see customers using aggregations to test and validate against event level DT data. This involves examining a previous day's aggregated click counts or a previous month's aggregated matched conversions (referred to as "post-click" and "post-impression" in DFA reporting parlance). And in perhaps the most interesting case related to the API, some customers use the web services interface to expand DFA IDs that either haven't come through in Match Tables or to gather dimensions not delivered in Match Tables. Let's discuss this last case a little more...

We strive to provide ID lookups in your daily Match Tables but sometimes a dimension is missing. This can happen when, for example, a dynamic Click Ad is reactivated after having been dormant for more than 30 days. A small number (perhaps the first 0.05%) of requests for that Ad may lack a Buy-ID. Since we generate most of your Match Tables daily you might otherwise have to wait a full day to resolve the name value. Interrogating the API (trafficking/UI in this case) can be a better way to resolve the name value in near real time.

Regarding dimensions not included in your Match Tables, there are two ways to resolve this: request a newer Match Table or a custom Match Table, or expand your IDs by querying the API. To request new/custom Match Tables (have you checked if you are receiving the "latest and greatest" in a while? We don't change your Match Tables until you ask us to.), you can ask your Account Manager to start a DT audit. You can also expand your IDs directly by making batch requests to the API (either reporting or trafficking services, as necessary). For example, some clients may want to periodically pull the Placement-Comments for one particular advertiser. Doing this with the API is a snap. Another useful application, if you're a DoubleClick Search customer, is to use the reporting service to capture search engine click stats since only the DFA equivalent click stats appear in Data Transfer.

-- Matthew Trojanovich, the DoubleClick Data Transfer Team

5 tips for consuming Data Transfer files

noreply@blogger.com (mtroj_google) — Tue, 26 Oct 2010 13:03:00 -0400

Every day we receive various support tickets asking for help with existing Data Transfer configurations. Sometimes the answer to a question can be found within the DT FAQs in the Help Center (DFA | DFP | Rich Media) and other times it requires personal assistance. We regularly review both sources of information in an effort to improve your Data Transfer experience. With that in mind, we've identified a number of tips that will help improve your work with DT.

1) Download Daily
DT files are published daily* according to your account's timezone. Correspondingly, we suggest that you download your new DT files every day. While you can download files every other day, only on weekdays, or even once a week, downloading every day ensures that you see your data when it is fresh. It also means that you can identify issues while they are still recent. DT files stay on the server, by default, for 30 days from the date of post - but don't wait until the files are aged to pick them up! Purged DT files cannot be recovered.

2) Secure your login
If your files are used by different parties, it's best to limit access to your FTP account. For instance, say you have a reporting team, an analytics team and a 3rd party consulting team all consuming your DT files. Suppose that each is located in a different office or is part of a different company. Sharing your login means that you cannot control which group sees what data. If one of these parties should need to have access restricted (filtered) or to revoked, it's difficult to do with a shared login. Some DT (and Match Table) data can be quite sensitive. The best approach is to download all of your files locally and then to distribute as needed. While this requires more effort on your part, it's almost always well worth it. We get requests to build custom scripts to help clients distribute their DT files, but such scripts are "black box", limited in their business rules, and are charged at an additional fee.
If you do need to reset your FTP login, please contact your support team and they'll be happy to assist.

3) Save for a rainy day
We recommend that you save 60 days of DT files in their raw, un-aggregated format. Storage these days is relatively cheap. Storing your files in their raw un-aggregated format means that you can re-load them into your data store easily if needed. Once files have aged off of the DoubleClick FTP server, they cannot be regenerated. DT files are special since, among other reasons, they contain your non-sampled User-ID ad events. We sometimes hear about clients who discover a clever (but legal!) new use for their event files - and frequently that new use requires granular values like User-ID. Moreover, depending upon your system, it's possible to query flat files just like querying a database. There are a number of open-source options to achieve this.

4) Identifying DT fields
DT file types (eg, impression, click and activity) change formats very infrequently. In fact, if there is a change in format, it's likely because you have asked us to add or remove a field. In short, you can depend on both the file format (gzipped text) and the structure (column A, column B and so on). From time to time, however, you may want to change the order of your columns. To ensure that your ETL system is flexible, identify DT columns based on column name rather than on column position. Identifying a field by name means that changes to DT format require no further programming on your part. Indexing columns by their name is dependable; fields like Time, User-ID, Ad-ID, Site-ID, etc, have never changed. When we build custom reports based on DT, we always index on column name.

5) Use .done files
.done files are a somewhat under-used free feature of the Match Tables (MT) system. .done files are produced daily and provide you with information that can be very useful in ensuring smooth ETL processing. A .done file is produced once all dependencies have been met for that day. By default the dependencies are your newly updated MT files. Thus, if a .done file is posted for a given day, it means that you can start downloading your MT files and begin identifying event dimensions (names). .done files also provide you with SHA checksums as well as a filename list and filesize list. Note: if you have network level Activities, you'll also receive a .done file. This file however is different than the MT .done file.
To request .done files for your account, contact your account manager or support team.

6) Use open source tools
Processing your DT and MT files doesn't have to involve expensive ETL resources. There are a number of open-source ETL tools (as well as open-source databases such as MySQL) which can do a fine job while at the same time helping you manage operation costs. One particular tool is Crush-Tools. We designed Crush-Tools internally and released it to the open-source community a few years back. Crush-Tools is a unix-based library of utilities that are specifically designed to consume, manipulate and operate on DoubleClick Data Transfer files. For instance, via pipes and redirects you can filter, aggregate, calculate and produce reports all directly from DT flat files. We even use Crush-Tools extensively ourselves! Read more about Crush-Tools here.

We hope these tips will prove helpful to you.

--Matthew Trojanovich, the Data Transfer Service Team

* Some clients have asked to receive their DT files more frequently. Our product team is indeed working on this. Dates cannot be given yet, but stay tuned to this channel for more.

NOTICE - change to Rich Media (MET) DT header field on 2010-09-20

noreply@blogger.com (mtroj_google) — Wed, 15 Sep 2010 10:18:00 -0400

This post is a follow-up to the 2010-08-26 note in which we announced an upcoming change to your Rich Media Data Transfer header. This notice applies to DFA and DFP customers alike.

So again, if you take advantage of the Rich Media (Met/RMDT) Data Transfer service then this update is for you. If you do not use RMDT and would like more information, please visit our online resources or speak with your Account team.

The change...
On 2010-09-20 your RMDT header will change slightly. The field "Met-Event-Type-Id" will be changed to "Met-Event-Type-ID". Note the capitalization. No other changes will take place including column order, field values or publish time. We're making this change so that the header format will be consistent across all Data Transfer services including DT for DFA, DT for DFP, DT for Search and, now, DT for Rich Media.

--Matthew Trojanovich, the Data Transfer Service Team

NOTICE - upcoming change to Rich Media (MET) DT header field

noreply@blogger.com (mtroj_google) — Thu, 26 Aug 2010 13:08:00 -0400

If you take advantage of our Rich Media (Met) Data Transfer feed then this update is for you.

If you do not yet use this feed and would like more information, I encourage you to visit our online resources where you can learn about some of the advanced Rich Media reporting metrics made possible by this feed. Just search "data transfer" from within the Rich Media Help Center. Some examples of interesting metrics you can formulate are User Rich Media Dwell Time and Rich Media Visit Type By Geography.

On to the announcement for which this post is intended...
In the upcoming months we will be making a small change to the header of your Rich Media DT file. We're going to change the case of the field "Met-Event-Type-Id" to instead be "Met-Event-Type-ID". I know, it's not terribly exciting, but we do want to give advance notice. Another notice, announcing the exact date of the change, will be posted. No data or other fields will be affected by this change.

--Matthew Trojanovich, the Data Transfer Service Team

Data Transfer case study

noreply@blogger.com (mtroj_google) — Thu, 15 Jul 2010 12:53:00 -0400

The following is taken from a post on the DoubleClick Advertiser blog. It concerns a Data Transfer case study. It's just one example of what can be done with the Data Transfer service.

----------

"Here is a great case study about DoubleClick's relationship with Havas Digital and how it has translated into Havas driving better results for its clients. For more than 10 years, Havas Digital has used DoubleClick services to help deliver and tweak hundreds of online advertising campaigns.

...

The best data available
With DoubleClick’s data transfer solution, selected DoubleClick ad server log files are available every morning for download by clients. These files contain the best data available to help analyze online consumer behavior, including detailed information on an advertiser’s impressions, clicks or conversions (or Spotlight events).

After this data is fed into Artemis, seasoned analysts from Havas use Artemis’ automated Advanced Reports to help optimize the media spend for hundreds of name-brand advertisers. Often this involves making recommendations on tricky issues like optimal ad reach and frequency, streamlining the conversion process, attribution weighting for search vs. display, and syncing up numerous channels of online and offline advertising.

Read the full case study to learn more about Havas Digital’s success with clients’ campaigns using Artemis and DoubleClick data transfer.

..."

The road ahead

noreply@blogger.com (mtroj_google) — Tue, 29 Jun 2010 09:11:00 -0400

We'll be up and running soon! In the meantime, refer to Useful Links, to the DoubleClick Learn Center, and to the DFA and DFP Support websites for information geared towards DoubleClick Data Transfer users.