Use web queries

DoubleClick Search (DS) web queries make it easy to download the latest DS data directly from a Microsoft Excel spreadsheet instead of manually downloading and importing a file into Excel. Like a report that you view in DS, you can customize the scope, type, and range of data that appears in the report. But instead of requiring you to download a file, DS provides a URL that Excel can use to automatically download and refresh the report’s data. DS web queries:

  • Reduce the time you spend generating reports in DS.
  • Enable you to create common report templates in Excel and populate them with the latest DS data.

For example, if you've created a customized campaign-performance report that you send to advertisers or clients weekly, you can create one DS web query for each of your advertisers and clients. For each web query, you set up an Excel spreadsheet that automatically refreshes every week. When you open each report after the weekly refresh, the reports are ready for delivery.

Setting up a web query is simple:

  1. In DS, create a report and generate a web query URL.
  2. In Excel, enter the URL using the web query wizard.
  3. Update settings and format columns in Excel.

Click the links below for details.

Create a report and generate a web query URL

  1. In the DS UI, navigate to a reporting table.
    For example, navigate to a campaign and click the Keywords tab.

  2. Make sure the table contains the date range, columns, segments, filters, etc., that you want in your report.
    Learn how to customize the data in your tables and reports.

    When you use Excel 2010 or later to connect to a web query, the report can have a maximum of one million rows. For Excel 2007 users, please limit your reports to 25,000 rows to avoid Excel performance issues.
  3. Above the reporting table, click ▶ Download.
    The download settings appear.

  4. Click the Create web query tab.

  5. Enter the desired settings and click Create web query.
    A message above the table indicates that the web query was created.

  6. Next to the message, click Get web query URL and copy the URL that appears.

Enter the web query URL in Excel

After you create the report and web query URL in DS, you can connect to the web query in Excel, format your columns as needed, and choose how you want to keep the data up to date.

You can use DS web queries in Microsoft Office Excel 2007 for Windows (up to 25,000 rows) or Excel 2010 for Windows (up to one million rows).
  1. In Excel, click the Data menu and select From Web.
    The New Web Query pop-up appears.

  2. In the Address field, paste the web query URL from DS and click Go.

  3. The first time you enter a web query URL, you may get a Security Alert notice that You are about to view pages over a secure connection.
    Select the check box and click OK.

  4. If prompted, sign in to DS.

  5. Select the XML table: Click the yellow arrow icon in the upper righthand corner of the code box.

  6. Click Import.

  7. The first time you enter a web query URL, you may get a Microsoft Office Excel notice that The specified XML source does not refer to a schema.
    Select the check box and click OK.

  8. In the Import Data pop-up, for Where do you want to put the data?, select XML table in existing worksheet and click OK.
    The DS data appears in the worksheet.

  9. Use the data any way you want in Excel: create pivot tables, generate reports, etc.

  10. Keep the data up to date:
    • Cell: Select a cell and click the refresh button in the toolbar.
    • Report: Click the Data menu and select Connections. Select the connection and click the Refresh button, or click Properties and choose from the automatic refresh options.
If you refresh an existing web query in Excel and authorization fails, an error message will appear. You'll need to go back to the Excel web query configuration settings and sign back in to DS.

Manage existing web queries

You can see a list of existing web queries, copy the web query URL, or delete a web query.

  1. Navigate to an advertiser.
  2. In the left nav, click Downloads.
  3. Click the Web queries tab above the table.
    The list of existing web queries appears. You can do any of the following:
    • Copy a web query URL: Click a Get URL link in the Web query URL column.

    • Delete a web query: Select the check box next to a web query name and click Delete above the table.

Known issues

  • You can’t use multi-agency reports as web queries.

  • Budget pacing report web queries are only supported on a per-advertiser basis.

  • You can't download an editable web query report, or upload web query reports back into DS.

  • An XML import error message may appear upon refreshing the web query in Excel when your Temporary Internet Files cache is full. To resolve this issue, clear the files in your Temporary Internet Files folder, reload the web query, and sign in to DS if needed (using the steps under Connect to the web query in Excel). You can also increase the size of the cache by adjusting your settings in Internet Explorer.

Report issues

To report issues with web queries, please click the Help link in the upper righthand corner of the UI, and then click Contact us. Make sure to include the following:

  • Operating system (e.g., Windows XP, Windows 7)

  • Internet Explorer version

  • Excel version

  • If applicable, the web query URL for the web query you're having problems with.

This will make it easier for support to try to reproduce and confirm issues that you may have.