DoubleClick Campaign Manager snapshot
A high-level look at Stitch's DoubleClick Campaign Manager integration, including release status, useful links, and the features supported in Stitch.
|Singer GitHub Repository|
|Configurable Replication Methods||
Connecting DoubleClick Campaign Manager
DoubleClick Campaign Manager Setup requirements
To set up DoubleClick Campaign Manager in Stitch, you need:
A Campaign Manager account. Refer to Google’s website for signup information.
API access. Most Campaign Manager accounts have this enabled by default. If you’re not sure, contact your DoubleClick representative or the Campaign Manager support team.
Access to the reports you want to replicate. Stitch will only be able to replicate the reports that the user who authorizes the integration has access to.
Step 1: Verify report access
Before you connect DoubleClick Campaign Manager to Stitch, you should verify that you have access to the reports you want to replicate. Stitch will only be able to replicate data for the same reports that you have access to in DoubleClick Campaign Manager.
- Sign into your DoubleClick Campaign Manager account and navigate to the Report Builder page.
- Under Reports, click My Reports.
A list of all the reports you have access to will display:
- Verify that the reports you want to replicate are listed on this page.
Step 2: Locate your DoubleClick Campaign Manager profile ID
- Sign into your DoubleClick Campaign Manager account.
- Click the user menu (your icon) in the top right corner.
- In the dropdown beneath the Sign out button will be a list of the profiles you have access to.
Locate the ID of the profile you want to connect. This will be a seven digit number next to the name of the profile. For example:
An example is highlighted in the image to the right.
- Copy the profile ID.
Keep this handy - you’ll need it in the next step.
Step 3: Add DoubleClick Campaign Manager as a Stitch data source
- Sign into your Stitch account.
On the Stitch Dashboard page, click the Add Integration button.
Click the DoubleClick Campaign Manager icon.
Enter a name for the integration. This is the name that will display on the Stitch Dashboard for the integration; it’ll also be used to create the schema in your destination.
For example, the name “Stitch DoubleClick Campaign Manager” would create a schema called
stitch_doubleclick_campaign_managerin the destination. Note: Schema names cannot be changed after you save the integration.
- In the Profile ID field, paste the ID of the DoubleClick Campaign Manager profile from Step 2. This value should be a seven digit number such as
Step 4: Create a replication schedule
In the Replication Frequency section, you’ll create the integration’s replication schedule. An integration’s replication schedule determines how often Stitch runs a replication job, and the time that job begins.
Stitch offers two methods of creating a replication schedule:
- Replication Frequency: This method requires selecting the interval you want replication to run for the integration. Start times of replication jobs are based on the start time and duration of the previous job. Refer to the Replication Frequency documentation for more information and examples.
Anchor scheduling: Based on the Replication Frequency, or interval, you select, this method “anchors” the start times of this integration’s replication jobs to a time you select to create a predictable schedule. Anchor scheduling is a combination of the Anchor Time and Replication Frequency settings, which must both be defined to use this method. Additionally, note that:
- A Replication Frequency of at least one hour is required to use anchor scheduling.
- An initial replication job may not begin immediately after saving the integration, depending on the selected Replication Frequency and Anchor Time. Refer to the Anchor Scheduling documentation for more information.
To help prevent overages, consider setting the integration to replicate less frequently. See the Understanding and Reducing Your Row Usage guide for tips on reducing your usage.
Step 5: Authorize Stitch to access DoubleClick Campaign Manager
- If you aren’t already signed into your Google account, you’ll be prompted for your credentials.
- After you sign in, you’ll see a list of the permissions requested by Stitch:
- View and manage DoubleClick for Advertisers reports - This is required to allow Stitch to view and run reports. Note: Stitch will not alter report settings, and will only ever read data. Refer to the Replication section below for more info.
- To grant access, click the Allow button.
- After you’ve granted access, you’ll be redirected back to Stitch to finish setting up the integration.
Step 6: Set tables to replicate
To complete the setup, you’ll need to select tables you want to replicate to your destination.
Check out the Schema section to learn more about the available tables in DoubleClick Campaign Manager and how they replicate.
- In the list of tables that displays - or in the Tables to Replicate tab, if you skipped this step during setup - locate a table you want to replicate.
To track a table, click the checkbox next to the table’s name. A green checkmark means the table is set to replicate.
- Repeat this process for all the tables you want to replicate.
- When finished, click the Finalize Your Selections button at the bottom of the screen to save your selections.
Note: If you change these settings while a replication job is still in progress, they will not be used until the next job starts.
Initial and historical replication jobs
After you finish setting up DoubleClick Campaign Manager, its Sync Status may show as Pending on either the Stitch Dashboard or in the Integration Details page.
For a new integration, a Pending status indicates that Stitch is in the process of scheduling the initial replication job for the integration. This may take some time to complete.
Initial replication jobs with Anchor Scheduling
If using Anchor Scheduling, an initial replication job may not kick off immediately. This depends on the selected Replication Frequency and Anchor Time. Refer to the Anchor Scheduling documentation for more information.
Free historical data loads
The first seven days of replication, beginning when data is first replicated, are free. Rows replicated from the new integration during this time won’t count towards your quota. Stitch offers this as a way of testing new integrations, measuring usage, and ensuring historical data volumes don’t quickly consume your quota.
DoubleClick Campaign Manager Replication
When Stitch replicates data from DoubleClick Campaign Manager, the process will look like this:
- Stitch runs the selected report using the report’s defined date range and included dimensions and metrics.
- DoubleClick Campaign Manager creates a CSV file of the report results.
- Stitch replicates the results from the CSV.
Each run of a report will have its own CSV file in DoubleClick Campaign Manager, which you can access by signing into your DoubleClick Campaign Manager account.
Report date ranges
When Stitch replicates data for a given report, it will use the date range currently defined in DoubleClick Campaign Manager for that report.
For example: The image below shows the configuration for a report named
ad_performance_report, where the date range is set to the last 30 days. When Stitch replicates data for this report, it will run the report using these settings:
Data loading and Append-Only Replication
When Stitch loads the extracted data into your destination, it will do so using Append-Only Replication. This is a type of Incremental Replication where existing rows aren’t updated, but appended to the end of the table.
Additionally, the number of rows loaded into the table during each replication job is dependent on the combination of unique values in the dimension columns you track. See the Column Selection and Statistic Aggregation section for more info and examples.
Let’s say these columns are tracking in the
date(dimension) - The date pertaining to the record.
campaignId(dimension) - This is the ID associated with a campaign. In this example, there are two campaigns:
device(dimension) - The device type. There are two values for this example:
impressions(metric) - The total number of impressions.
Every time Stitch replicates and loads data, a row for each unique combination of the dimension columns will be appended to the end of the table:
|2017-10-01 17:48:26.791||2017-09-10 00:00:00||929007494||Computer||61|
|2017-10-01 17:48:26.791||2017-09-10 00:00:00||929007494||Tablet||15|
|2017-10-01 17:48:26.791||2017-09-10 00:00:00||929599581||Computer||37|
|2017-10-01 17:48:26.791||2017-09-10 00:00:00||929599581||Tablet||9|
Query for the latest data
You may need to alter this query
The query below is meant to act as a foundation for writing your own. Depending on your SQL syntax and the dimensions contained in a given table, you may need to alter this query for your own usage.
When querying your DoubleClick Campaign Manager data, you’ll want to include the Dimensions you’re analyzing in the partition. For example: If you’re analyzing campaigns over time, you’d include columns like
If you want only the most recently replicated data for any DoubleClick Campaign Manager table, you can use the sample query below to account for the Append-Only Replication Stitch uses.
This query will use the report’s Dimensions and the
_sdc_report_datetime column to return the latest data:
SELECT * FROM ( SELECT *, RANK() OVER (PARTITION BY dimension_1, dimension_2, etc., ORDER BY _sdc_report_datetime DESC) FROM ad_performance_report ORDER BY dimension_1, dimension_2, etc. ASC ) AS latest WHERE latest.rank = 1
In this query:
- A subquery first uses a window function to create a ‘window’ of data for each combination of Dimensions,
- The values of the
_sdc_report_datetimecolumn are ranked within each window partition, and
- Then, in the outer query, only the rows with
_sdc_report_datetimevalues ranked as
1- which is equal to the maximum timestamp - are returned.
DoubleClick Campaign Manager table schemas
Table and column names in your destination
Depending on your destination, table and column names may not appear as they are outlined below.
For example: Object names are lowercased in Redshift (
customers), while case is maintained in PostgreSQL destinations (
CusTomERs). Refer to the Loading Guide for your destination for more info.
|Replication Method: Append-Only (Incremental)||
Replication Key :
Primary Key :
For each report you set to replicate, Stitch will create a table in your destination. The name of the report will be the name of the destination table, with some light transformations to ensure the table name adheres to your destination’s naming rules.
For example: A report named
Ad Performance Report in DoubleClick Campaign Manager may result in a table named
AD_PERFORMANCE_REPORT, depending on your destination.
Applicable only if Stitch doesn’t detect a Primary Key in the source table. Stitch will use this column to de-dupe data.
The start time of the Stitch replication job that replicated this record.
The Dimensions you include in your DoubleClick Campaign Manager report will display in the table, one column for each Dimension.
The Metrics you include in your DoubleClick Campaign Manager report will display in the table, one column for each Metric.