There are many pre-built customer activity, service and marketing reports available in Oracle CRM On Demand. Managers and other CRM professionals can use these reports to track, measure and analyze marketing effectiveness, customer activities and service requests. Find out how to use reports successfully in this chapter excerpt.
Oracle CRM On Demand Reporting
Chapter 1, Getting Started with Answers On Demand
Oracle CRM On Demand Reporting, Chapter 1
Table of contents:
Getting started with Oracle CRM Answers On Demand
Pre-built CRM On Demand reports for sales
Customer activity and marketing reports in CRM On Demand
A customer relationship management system is all about knowing your customer. How many of your customers have purchased your products or services? How many customers do you have? How many contacts have you made? Who are your best customers? You are likely to have some reporting needs regarding customer activity. The prebuilt reports in this area provide a great starting point for this type of report.
Number of Accounts Opportunity Analysis
The Number of Accounts Opportunity Analysis report provides a count of accounts that have one or more related opportunities. A column selector allows you to select from a number of groupings by which to view the data. Without modification, you are able to view this data by Annual Revenue Tier, Region, State, Industry, and Account Type. In many cases, companies will not track all of these data points, and often create custom fields on their accounts. By modifying the Column Selector view, you can add your custom columns and remove any columns you are not using. Bear in mind that this report draws from the Account History Analytics subject area, so custom fields will not be available until the day after you create them in CRM On Demand.
Also, notice that the first column in the report is set to navigate to the Opportunities By Account report from the Quick Lists section. The value of the column passes to the target report when clicked. Interestingly, you can change this column using the column selector, so the value passed to the target report is from the active column selected from the column selector.
Number of Accounts Analysis
The Number of Accounts Analysis report is exactly like the Number of Accounts Opportunity Analysis report, only this report counts all accounts rather than just those with related opportunities. This report also allows for navigation to another report. In this case, clicking the first column value in either the table or the chart navigates you to the Accounts By Sales Rep report in the Quick Lists section. The filter value passed to the target report depends on the value clicked and the active column selected in the column selector.
Contact Analysis By Opportunity
The Contact Analysis By Opportunity report provides a count of contacts that have one or more related opportunities. Like the Number of Accounts Opportunity Analysis report, a column selector allows you to select from a number of groupings by which to view the data. In this case, you can choose from Sales Stage, Deal Size, and (Opportunity) Status. By modifying the Column Selector view, you can add your custom columns and remove any columns you are not using. This report also uses the Opportunity History Analytics subject area, so custom fields are not available until the day after you create them in CRM On Demand. The report data is accurate as of the previous day's analytics refresh. Clicking the data in this report takes you to the Contact Mailing List report in the Quick Lists section.
Contact Analysis By Account
The Contact Analysis By Account report provides a count of contacts for each of your accounts. You are able to see these counts by State, Region, or Industry. Clicking one of these columns in this report takes you to the Contact Mailing List report in the Quick Lists section.
Top 10 Customers
The Top 10 Customers report is another simple ranking report. The layout includes a horizontal bar chart and a basic table with Account Name, Revenue Tier, Account Type, and Closed Revenue columns. The RANK function is on the Closed Revenue column. (Chapter 6 provides more detail on the RANK function.) The Account Name column is an action link that navigates to the detail window for the selected account.
The Account Analysis report includes a Column Selector view that allows you to select the columns you want to analyze with this report. You may choose to view the number of accounts with opportunities or closed revenue by selecting one of these columns in the Column Selector view. You can also choose the column by which you want to group the measurement. The columns available are State, Account Type, Region, Industry, and Annual Revenue Tier. With just two columns in the column selector, this report will produce ten combinations. Adding to this is as simple as adding additional columns to the report. Clicking one of the hyperlinks in the report will navigate you to the Opportunities By Account report.
Closed Revenue By Account Analysis
The Closed Revenue By Account Analysis report gives you the breakdown of closed revenue across one of five account variables. You can see the closed revenue by Account Type, State, Region, Industry, or Annual Revenue Tier. This report also provides navigation to the Account By Sales Rep report.
The prebuilt reports in the Service section examine the Service Request object in CRM On Demand. You will find that the design of these reports is very similar in concept to the reports in the other sections of the prebuilt reports. There is a heavy use of the column selector view, and the overall design of the reports is simple and quite focused.
The Service Analysis report is one of the many simple yet extremely insightful reports among the prebuilt reports. The report itself contains only two columns. The metric column that this report analyzes is the number of service requests, and with the column selector view, you are able to view this data by User Name, Industry, Area, Source, Region, or Priority. This report uses the active Service Request History Analytics subject area, so the data returned is current as of the previous day.
Service Report List
This is a quick list report that provides a table displaying SR Number, Source, Status, Priority, Account Name, Date, and Fiscal Year for each user. You will find this report listed in the Service section on the Reports tab, but if you want to open the report in Answers, you need to look in the Quick Lists folder.
This report would be a good candidate for a pivot table. Of course, you might want to add a metric column. You can use a pivot table view, for example, to group the data differently or to provide counts for each user across source. Pivot tables add an almost infinite set of possibilities. Learn more about pivot tables in Chapter 12.
Current Service Request Aging Analysis
The Current Service Request Aging Analysis report provides a visual analysis of how long the currently open service requests have been open. You can see this data grouped by source, priority, or area. I would like to use this report to illustrate a couple of points. First, notice that the metric column in this report is Avg Open SR Age. This column does not exist in the functional CRM On Demand database. You will not see this column exposed on any layout in the application. The column exists only in Answers as a calculated field.
In addition, if you examine the filter used on this report you will see a session variable there named NQ_SESSION.SR_OPEN. This system variable is one of many. You will not find a complete list documented in the Online Help. Having command of all the system variables is not necessary to be a successful reports developer. You can calculate all the system variable values using other functions in column formulas. You may see system variables in predefined reports, and I document some of them for you in this book.
Open Service Request Analysis
The Open Service Request Analysis report is similar to the Current Service Request Aging Analysis report, except that this report is concerned with the number of open service requests rather than how long they have been open. You can see how many open service requests there are by User Name, Source, Priority, or Area. This report also uses the NQ_SESSION.SR_OPEN system variable. You can just as easily filter on the Status column, including only the open service requests.
Number of Service Request Analysis
The Number of Service Request Analysis report is almost exactly like the Service Analysis report. This report also contains two columns. The metric column that this report analyzes is also the number of service requests, and with the column selector view, you are able to view this data by User Name, Status, Area, Source, or Priority. This report draws from the active Service Request History subject area, so the data returned is current as of the previous day.
Team Service Analysis
Like the other "Team" reports, this one provides managers a means for examining a number of different metrics for each of their subordinates. You may need to remove the Manager Email column filter to see data in this report if you are not a service manager. Managers are able to see the average age of open service requests, the number of open service requests, the number of closed service requests, the number of pending service requests, the number of cancelled service requests, and the number of service requests regardless of status for each of their subordinates.
Notice that the report filters to show only direct reports by including only those records where the reporting level is "0."
You use the prebuilt reports in this final group to analyze your marketing data. These reports focus primarily on campaign records, but rely on relationships between campaigns and leads and opportunities to determine how effective campaigns are. The Marketing Effectiveness reports are also a treasure trove of interesting report elements. Dig into them and you will see many undocumented session variables, use of images with conditional data, multiple layers of navigation, and the use of narrative report views.
You will find that beyond the nine reports listed on the Reports tab, when you look inside the Marketing Effectiveness folder from Answers, there are a dozen additional reports. These reports make up the dashboards targeted by the navigation links in the listed reports. I encourage you to explore these reports from within the CRM On Demand user interface as well as with Answers.
Active Campaign Status
The Active Campaign Status report examines all of your currently active campaigns and allows you to view statistics on those campaigns. There is a wealth of information available through this report. The metrics available here are Return on Investment (ROI), Cost Per Lead, Lead Conversion Rate, Cost Per Closed Sale, Opportunity Win Rate, Opportunity Revenue, Closed Revenue, Number of Wins, Number of Opportunities, and Number of Leads.
This report also has navigation built into the Campaign Name column that takes you to a dashboard containing a number of other campaign reports. You will also notice that the filters include a session variable named NQ_SESSION.ACTIVE_CAMPAIGN. This variable obviously limits the data to only those campaigns that are active. You can easily accomplish this with a filter on the status column that identifies all of the appropriate active status values.
Another interesting feature of this report is the dynamic sort ability on the ROI column. You can provide users with this dynamic sorting ability on columns in a table by enabling the sort on the Table View.
Completed Campaign Results
The Completed Campaign Results report examines all of your closed campaigns and allows you to view statistics on those campaigns. This is the same report as the Active Campaign Status report for your closed campaigns. The metrics included in this report, like the previous report, are Return on Investment (ROI), Cost Per Lead, Lead Conversion Rate, Cost Per Closed Sale, Opportunity Win Rate, Opportunity Revenue, Closed Revenue, Number of Wins, Number of Opportunities, and Number of Leads.
This report also navigates to a dashboard containing a number of other campaign reports. One of the filters in this report compares the Status field with the session variable NQ_SESSION.COMPLETED_CAMPAIGN. This variable is the inverse of the variable used in the active report, and limits the data to only those campaigns that are not active.
Campaign Effectiveness By Campaign Name
The report that you are able to open in Answers is a simple table report that lists completed activities organized by type and shows metrics for each. The metrics include Actual Cost, Number of Leads, Number of Opportunities, Number of Wins, Closed Revenue, Cost Per Lead, Cost Per Closed Sale, and Return on Investment. Clicking a campaign name in this report will navigate you to the same dashboard that the previous two reports target.
If you run this same report from the Reports tab, you will notice that there is more than meets the eye compared to looking at the design of the report. Running this report exposes a number of additional embedded reports, many of which are described individually further down this list. The report makes use of a dashboard view that is currently only available on the Dashboard tab for your custom dashboards.
Campaign Effectiveness By Campaign Type
This report is the same as the Campaign Effectiveness By Campaign Name report with the Campaign Name column removed. The navigation on this report is on the Campaign Type column. This report is also quite different when run from the Reports tab.
Lead Followup Analysis
Use this report to analyze the status of your leads dated within the last 90 days. The report displays the number of leads within each stage by Fiscal Year, Fiscal Half Year, Fiscal Quarter and Year, Fiscal Month and Year, Fiscal Week and Year, Lead Owner, or Sales Person based on a selection from the column selector.
Examine the filter on this prebuilt report and you will see a great example of the TIMESTAMPADD function along with the CAST and CURRENT_DATE functions. Detailed information on these functions is provided in upcoming chapters, but in summary, this filter limits data to dates between the current date minus 90 days and the current date.
The data table on the report is not a regular table. This report uses a pivot table to group the measurements into columns for each status value. The pivot table view is the most versatile of all views in Answers On Demand, which you will discover in Chapter 12.
Lead Source Analysis
The Lead Source Analysis report displays the number of leads dated within the last 90 days organized by source, lead owner, sales person, or campaign name. This report also uses the TIMESTAMPADD filter to limit data to the past 90 days.
Opportunity Source Analysis By Close Date
The Opportunity Source Analysis By Close Date report analyzes opportunities that closed in the previous 90 days. You are able to see the number of opportunities by lead source, campaign name, owner, or territory.
Opportunity Source Analysis By Create Date
The Opportunity Source Analysis By Create Date report analyzes opportunities with a create date within the previous 90 days. You are able to see the number of opportunities by lead source, campaign name, owner, or territory.
You will not find the Projected Revenue report on the Reports tab. This report is a subreport that is part of another report. This report shows revenue data based on leads created in the last 90 days, and compares lead potential revenue to actual closed opportunity revenue.
The report filter uses the TIMESTAMPDIFF function to limit the number of days between lead creation and the last data refresh.
This report provides a line graph of number of leads, number of new opportunities, and number of wins by fiscal week. You will find this chart embedded in other prebuilt reports; it is visible when you run those reports from the Reports tab.
This small report simply calculates the average time it takes to convert a lead and close an opportunity and the average revenue amount. The interesting element in this report is the use of conditional formatting to include a small flag icon next to the values in the columns.
Campaign Detailed Results
This report is nothing more than a narrative view designed to display a campaign name as part of one of the dashboard-style reports described earlier.
This report provides a bar chart that graphs the number of leads by lead status. This report is also part of the dashboard reports described earlier.
The Campaign Opportunities report is another one of those simple reports that serves as an element of the dashboard reports. This report contains a bar chart that graphs the number of opportunities by sales stage.
The Campaign Revenue report contains a bar chart that graphs revenue by opportunity sales stage.
Campaign Performance Summary—Averages
This report provides 16 separate averages using session variables in the formula for each column. The metrics provided are averages of actual campaign cost, budgeted campaign cost, percentage of budget, number of leads, lead conversion rate, cost per lead, number of leads targeted, percentage of leads achieved, revenue target, percentage of revenue target achieved, opportunity revenue, closed revenue, return on investment percentage, number of wins, opportunity win rate, and cost per closed sale.
Lead Conversion Metrics
The Lead Conversion Metrics report provides a table with conditional formatting that displays the number of accounts, contacts, leads, and opportunities for campaigns.
Accounts List, Contact List, Lead Lists, and Opportunity Lists
The List reports in the Marketing Effectiveness folder provide basic tables designed to list the accounts, contacts, leads, or opportunities associated with a particular campaign. You will find these reports embedded in other dashboard-style reports.
Download Chapter 1, Getting Started with Answers On Demand
Read other excerpts and download more sample chapters from our CRM and call center bookshelf
This was first published in November 2008