7 October 2013
Original publication date: 06/20/2013
Modified: 10/07/2013 (Change log)
Note: If you have questions about this article, use the tutorial feedback link at the bottom of the article. Please don't contact technical support with questions about Adobe Developer Connection articles.
The Consolidated Fulfillment Report on the DPS Dashboard gives publishers easy-to-understand details related to the downloads of their folio content. This report gives administrators the fulfillment data for all publications associated with their account. All report data is recorded daily and includes all data related to each publication's downloads, per issue, with a total for all downloads and a breakout showing web viewer downloads separately.
A pivot table helps data analysts explore and make sense of large amounts of numerical data. They are especially useful for moving rows of data into columns, sorting and grouping data, and collapsing and expanding levels of data to create a concise report. In this document we demonstrate pivot tables using the PivotTable feature of Microsoft® Excel.
The DPS Dashboard makes available two different types of reports to get information about billable downloads: an app-specific report available to every account with "Application Account" permissions (meaning this user can Publish content) and an account-wide consolidation report available only to Account Administrators. We are focusing on the latter of these reports, the "Consolidated Fulfillment Report", although the principles outlined here apply to the app-specific reports as well. Any Account Administrator can download the Consolidated Fulfillment Report by clicking the link immediately below the Download Counter on the main DPS Dashboard page.
The Consolidated Fulfillment Report contains a few "header" rows with account information followed by a line for each folio and each day where a download of that folio occurred. When the publication contains two different folios that represent different renditions for the same logical issue, there are two different lines for the downloads for a given day. The header rows include the following information:
The columns of daily download data are as follows:
In the definitions below, the phrase "Native Viewer" refers to your publication's custom application built using the App Builder tool. The phrase "Native Web Viewer" refers to the Adobe Content Viewer for Web running on an Apple iPad tablet. The phrase "Desktop Web Viewer" refers to the Adobe Content Viewer for Web running on a PC or Macintosh computer.
If you look at the raw data in the report you just downloaded, you see it is a jumble of things, rows upon rows of data that would be difficult to sort out. Pivot tables will help to make sense of this data.
Note: In the screenshot above, the Account ID and Folio ID columns are intentionally left blank. A real report includes these values.
Step 1: Download the Rollup Report if you haven't already done so.
Step 2: Decide if you want to build a report for any particular date range, or want a report since the beginning of time. There are two ways to narrow the date range, this method deletes data from the original spreadsheet.
Use your spreadsheet programs Sort feature to organize the rows from earliest to latest entries. Click on row 5, shift-click on the last row of data, then Sort based on Column K (Download_Date) in ascending order. You can now delete any data you don't want to include in your report. For instance, if you only want to build a report for calendar year 2012, delete all rows where Download_Date is before 1/1/2012 and after 12/31/2012.
We will discuss an alternate method for selecting date ranges that retains all the data in the spreadsheet shortly.
Step 3: Now we'll build a pivot table. (Note that these instructions are specific to Microsoft Excel, but the basic idea is true for other spreadsheet programs.) Select Row 5 and shift-select the last row of data in your spreadsheet. Then click the "Data > PivotTable…" menu item. This opens a dialog box that lets you change certain values, just click "OK" to open the PivotTable Builder panel. Now you are ready to begin.
Step 4: The PivotTable Builder panel lists all columns of data in the top part of the window, and then includes four drop zones where you can drag a column label from the top part of the panel. Drag the Publication_Title item from the top panel into the Row Labels well. Drag the Issue_Number item from the top panel into the Row Labels well below Publication Title. Drag the Download_Date item from the top panel into the Column Labels well. Finally, drag the Total_Billable_Fulfillments item from the top panel into the Values well. Your result should look like the following image.
The resulting pivot table shows you the daily downloads for each issue of each publication that has been published from one of your accounts. The very last row is a Grand Total for the number of downloads per day, the very last column is a Grand Total for the number of downloads per folio.
If you want to save your work, you will need to Save As… an Excel file. CSV files won't retain the metadata for the pivot table.
Step 5: Enhancing the report.
Setting Date Ranges. As mentioned above, there is more than one way to select a date range. Let's say you only want to view data for a single week, but your report captures an entire year. Here's how you filter the data to only include the one week. If you've followed these instructions so far, in Column B there will be a dropdown menu item called Column Labels. Click the little icon to open the Sort/Filter panel. Uncheck the Select All checkbox and then check only the dates you want to include in your report. As you select dates, the report data will dynamically reflect your selections.
Adding Breakout Data. As listed at the beginning of this document, the report includes breakout data for the Native_Viewer_Billable_Fulfillments, Native_Viewer_Article_Preview_Billable_Fulfillments, Native_Web_Viewer_Billable_Fulfillments, and Desktop_Web_Viewer_Billable_Fulfillments. You can build a pivot table that only shows desktop web viewer downloads by dragging Desktop_Web_Viewer_Billable_Fulfillments from the top of the Pivot Table Builder panel into the Value well. You can leave the Sum of Total_Billable_Fulfillments in the Value well or remove it. If you leave it in the Values well, your report will show both numbers. (Note that if you include both values in your report, the Grand Total numbers will be wrong since the web viewer fulfillments will be double counted). You can similarly build a breakout for any of the other fulfillment types.
Narrowing the Publications in the Report. If you would like to narrow the report to only show a subset of the publications associated with your account, click the small dropdown icon next to Row Labels entry in Column A. Check or uncheck the checkboxes that correspond to the publications you want to include or exclude from the report.
Breaking out fulfillments of different renditions. If you would like to break out download totals for each rendition of a multi-rendition folio, add the "Folio_ID" to the Row Labels well in the PivotTable Builder panel (below Issue_Number). The Issue_Number row aggregates the fulfillment of all renditions, and the Folio_ID rows will break out the downloads for each rendition.
This article just touches the surface of how to make use of pivot tables to analyze DPS fulfillment data. For additional information, consult the Help documentation for your favorite spreadsheet program.
Comments are currently closed as we migrate to a new commenting system. In the interim, please provide any feedback using our feedback form. Thank you for your patience.