Aggregating Account Level Sales Data

  • iPad
  • Online

Users can view aggregated account sales data in Fixed Reports. For example, if a customer wants to display sales of a clinic with eight associated HCPs, Fixed Reports can display the aggregate sales across those eight HCPs when viewing the clinic’s Account Summary report. The clinic’s aggregate sales also display in a row on Top Accounts reports, the Territory Sales Analysis report, and the Territory Comparison report.

Customers can also use this functionality to aggregate multiple identifiers for a single account. Sales data is aggregated for child accounts a user does not have visibility to. which ensures the data for the parent account is always accurate. Individual child account data is not available if the user does not have visibility to that child account.

There are two ways to use this functionality. The two options can be used together or separately:

  • Admins can upload a mapping file to the Fixed Reports database specifying which accounts roll up into parent accounts. The mapping file is a simple two column flat file: the first column is the ID of the parent while the second column holds all IDs to be aggregated (at any level of the hierarchy). This file is loaded and processed the same as a regular sales data file. Admins must create a Data_Map_Template_vod record for this file with a column label of ID_Map_vod. This indicates to Fixed Reports that the file is an ID mapping file and not a typical sales data file.
  • Customers using Veeva CRM’s Account Hierarchy can roll up data based on the hierarchies defined. By selecting the ENABLE_CHILD_ACCOUNTS_IN_ANALYTICS Veeva Setting check box, a nightly process runs against the Child_Account_vod object to create a flattened ID Map, where each parent account is mapped to its child accounts (up to ten levels deep). Fixed Reports uses this ID Map to calculate rollups for accounts.

For example, the following account data is loaded into Fixed Reports:

  • Barnert Hospital – ID: 00123
  • Barnert Cardiology (Child of Barnert Hospital) ID: 01111
  • Barnert Oncology (Child of Barnert Hospital) ID: 01112
  • Barnert Pediatrics (Child of Barnert Hospital) ID: 01113
  • Dr. Smith (works in the Cardiology Dept.) ID: 11111
  • Dr. Jones (works in Oncology Dept.) ID: 11112
  • Dr. Williams (works in Pediatrics) ID: 11113
  • Dr. Jackson (works in Pediatrics) ID: 11114

To view aggregated data for Barnert Hospital as a whole, the mapping file must contain the following information:

Parent ID

Child ID

00123

00123

00123

01111

00123

01112

00123

01113

00123

11111

00123

11112

00123

11113

00123

11114

This mapping file indicates to Fixed Reports that all sales made by any of the HCPs and departments count towards the total sales of Barnert Hospital. The parent itself needs to be mapped as a child (see the first row in the table). Additionally, if Dr. Smith’s sales count towards the Cardiology department, add this line to the mapping file:

Parent ID

Child ID

01111

11111

If an HCP account also has sales outside its parent account (the hospital department or the hospital in the above example), those sales are also aggregated as there is no way to distinguish which portion of the sales takes place at which location.