Reporting is an integral aspect of any PPC account manager’s role and the wide variety of tools at our disposal means that this has never been a more reliable, efficient and intuitive process. Whether you are well versed in DoubleClick Search Executive Reports, swear by AdWords’ automated reports or stay true to your in-house solution, we have the ability to pull the data we need and display it in a digestible format at our clients’ request.
But what if we needed to provide a report on data that spans, for example, multiple engine accounts and includes multiple custom formula columns? And what if that data has to be formatted in a bespoke reporting template in Excel that neither Exec. Reports, nor any AdWords functionality can satisfy? And what if this report has to be sent daily, weekly or monthly? Sounds like a lot of hassle doesn’t it…
Enter stage, DS Web Query reports.
What Are They?
At first glance, a web query report is no different to any typical report that you would manually download from within the DS interface, except for one critical detail: location. Rather than saving the data you are trying to access locally on your PC, the data is stored and automatically updated online, accessible through a unique URL that is generated upon initial set-up. The address of this URL is what Excel will use to locate the data that you want to report on. A secure connection is made between the query and Excel, and can be refreshed as often as necessary (more on this later).
Set-Up
Similar to the functionality of web query reports, the actual set-up process is almost identical to that of any regular report that you would pull from within the DS interface. There are a few noteworthy checks that need to be considered however:
- Current View: Ensure that all of the metrics you wish to report on are added as columns in your current view. It is always handy to save this view (with a relevant name), in the event of needing to replicate this particular web query again at any point.
- Time Frame: Ensure that your date-range matches the type of report you wish to create - I.e. if you want to be able to use this report for data in the future (days that have not yet passed), then non-static date ranges such as ‘This Year’ or ‘This Quarter’ would be most appropriate.
- Time Segmentation: Ensure that you have applied segmentation where appropriate for daily, weekly, monthly or bespoke (custom days) reporting.
Once you are happy with your set-up, you can generate your query by clicking the regular ‘download’ button in the interface, and selecting the ‘web query’ tab on the left:
Further segmentation is available here relating to the scope of your data, allowing you to report at more granular levels within the parent data-set that you have selected.
Once you click ‘create web query’, navigate back up to the top of your data where you will be able to fetch the URL that has been generated by clicking ‘Get web query URL’:
It is possible to preview the data within your query simply by pasting the URL into your browser’s navigation bar. This is a good way to check that you have included all of your required data and segmentation parameters before importing into Excel.
Once you are satisfied with how your data looks, it is time to perform the data import within Excel itself. This is done by opening up a new workbook, navigating to the data tab and selecting ‘From Web’:
In the browser window that appears, simply paste in the URL that was generated in DS into the navigation bar (note: you may be prompted to sign in to Google at this stage – see ‘Further Points’ below on how to set up query access for anyone else with a DS account). Once your data loads, click import and choose a location for your data to reside when prompted.
Et voila, your data should now be visible within your workbook, ready to link to any reports, charts and graphs that you want to set up.
Managing Your Query
Once you have set up your query, it is possible to access the ‘connections’ & ‘properties’ (in the data tab) where you can give the query a name and description – this is particularly useful if you have multiple queries linked to a single workbook. You can manually refresh your all of your data with the ‘Refresh All’ button (or specific queries if you have more than one), but it is also possible to define how often you want the data to automatically update within your workbook:
I have found the ‘refresh data when opening the file’ option to be the most useful so far, but you may have use for enabling the refresh every ‘x’ minutes option.
And that's all there is to it! See below for a typical example of usage, and some minor caveats and other points to consider. Otherwise, happy reporting!
Example Use
Brief
Set up a monthly report for core PPC metrics, which can run for the entire year without the need for manipulation.
In DS:
- Set up your view: Current View: Impressions, Clicks, CTR, Cost, Conversions, CPA
- Timeframe: This Year
- Segmentation: Monthly
- Generate Query URL
In Excel:
- Set-up your report:
- Import your query in a new sheet.
- Link your table to the query data.
- Define refresh frequency.
Caveats
- At the time of writing, it is currently not possible to link web queries to Google Sheets, although we are hoping that this feature will become available soon.
- A DS account is necessary for both creating and viewing web queries within Excel. To access a query created by somebody other than yourself, you will need to right click on the query, ‘edit query’ and then enter your DS credentials where prompted.
Further Points To Consider
- Web Query Reports can also be used to pull data from the LHS tabs such as budget pacing, landing page tests & labels etc.
- Excel 2010 is limited to 1,000,000 rows for reports & Excel 2007 is limited to 25,000.
