Data Normalization
This document provides an insight on how ProKarma looks at the data and
what logics are applied by the data analysts for calendar normalized data.
Contents
Introduction........................................................................................................................................................................... 2
Raw Data............................................................................................................................................................................... 3
Consumption Month & Year..........................................................................................................................................4
Bill Type............................................................................................................................................................................. 5
Account Type.....................................................................................................................................................................6
Calendar Normalized Data.....................................................................................................................................................7
Raw Data Limitations.....................................................................................................................................................7
Calendar Normalization Example......................................................................................................................................8
Introduction
ProKarma's expense management solution "ProUtility" manages invoices for over 75 clients in the domains like Banking,
Grocery Stores, Industrial, Multi Family, Public Sector, Real Estate, Retail and Transportation etc. Our clients rely on our
SAS 70 Type II certified services to cut utility spending, manage budgets and work flows, implement environmental
conservation initiatives and contribute to efforts to channel renewable, sustainable resources.
In addition to providing standard analytic data, our ProUtility's budgeting/forecasting functionality, weather-normalized
reporting functionality and benchmarking capabilities enable our clients to effectively track and manage utility spending
on an ongoing basis.
On an average, our clients typically realize savings greater than 5% of their overall utility and telecom expenditure with
ProKarma's BPO.
Thanks to our experienced data analysts and smart brains behind the application; we are proud of our 99.67% accuracy
when it comes to processing the bills. With such perfection at data entry level, our data analysts also ensure that the
summaries and reports presented at ProUtility are no less in accuracy.
ProKarma is continuously evolving and updating itself to the new standards and increasingly helping the energy analysts
to save more.
This document underlines the business rules and logics applied to the raw data and what measures we take to ensure
there is no scope for any discrepancy at our Calendar/Weather Normalized Reports.
Raw Data
Raw data refers to the amount and usage values as seen on the utility bills. The following fields are a few amongst the
data that is captured at data entry level.
Bill #
Bill Date
Bill Received Date
Payment Due Date
Consumption Month & Year
Billing Period Start Date
Billing Period End Date
Number of Days
Previous Balance
Native Bill Amount
Reporting Bill Amount
Native Current Charges
Reporting Current Charges
Penalty Due Date
Amount After Due Date
Account Type
Bill Type
The information saved against these bills is displayed as is after applying Bill Type and Account Type priority. Before we
go further and understand what these are, let’s get to know how we display the reports with Raw Data. This is where the
Consumption Month & Year come into the picture.
For Raw Data reports, ProKarma considers the Billing Period Start Date & End Date which is stored as the Consumption
Month & Year.
Consumption Month & Year
Most of our customers receive bills across a broad range of periods. They can be classified into Semi-Weekly, Weekly, Bi-
Weekly, Monthly, Bi-Monthly, Quarterly, Half Yearly, Yearly, and Bi-Yearly periods. A majority of the bills fall within Bi-
Weekly – Bi-Monthly.
When saving a bill’s data we look at the Billing Period Start Date & End Date. We then consider the month which has the
maximum number of days in the billing period and save that month as the consumption month or consumption year.
Checkout the following example:
Bill Billing Period Dates Months Covered Consumption Month
31st January, 2025
To Jan – 01 Day(s)
February
st
01 March, 2025 Feb – 28 Day(s)
2025
(30 Days) Mar – 01 Day(s)
In the above example, the consumption month and year are recorded as February, 2025. Say the consumption in kWh is
1321 units and the total charges are $150.03; these values would be saved for the consumption month of February,
2025.
When a report that displays the raw data is run for the month of February, 2013; then these values will be shown.
Currently Cost Analysis & Utility Trend Reports are the only two Raw Data Reports under Trend & Comparison at
Analytics.
Before getting to understanding the normalized data, you need to get familiarized with something what we call as Bill
Type and Account Type Priority.
Bill Type
Bill Type is a reference to the bills or invoices which are used to record the meter reading information for a billing period
of any utility/commodity. The various kinds of Bill Types are mentioned below.
1. Vendor Actual (Vndr-Actual) – These are the original bills/invoices that are provided by the vendors by recording
the meter readings on a periodical basis and applying the necessary rates and taxes.
2. Vendor Estimated (Vndr-Estimated) – These are the bills/invoices that are not provided by the vendors directly.
In this case, there are no physical readings taken. These bills/invoices are calculated based on the information
provided by the client/partner.
3. Vendor Corrected (Vndr-Corrected) – These are the finalized bills/invoices that are provided by the vendors
after adjusting the actual readings with the estimated readings. The correction can be made at
Spend/Consumption/Usage level.
4. ProKarma Estimated (Pkrm-Estimated) – This is the bill/invoice generated by ProKarma in case the vendor fails
to provide a bill/invoice. These kinds of invoices are raised based on the previous/historical invoices.
5. ProKarma Corrected (Pkrm-Corrected) – These are additional bills/invoices created by ProKarma to compensate
and validate the discrepancies in between the ProKarma Estimated (Pkrm-Estimated) and Vendor Actual (Vndr-
Actual) bills/invoices.
While considering the data, ProKarma considers the following priority while looking at the data. The first preference is
given to the Vendor Corrected Bill Type and the least preference is given to ProKarma Estimated Bill Type. If we don’t
have the Vendor Corrected value, then we look at Vendor Actual and so on.
This Bill Type Priority is applied across all commodities/utilities.
Vendor Vendor ProKarma ProKarma
Corrected Vendor Actual
Estimated Corrected Estimated
Account Type
In our system, the originator of the bill is referred to as the Account Type: Utility Supplier (Supplier) and Utility Provider
(Utility). A utility supplier creates/generates the utility and the utility provider transmits the service.
At ProKarma, we track and measure the values of any utility/service availed for a period both from the supplier and the
provider to maintain consistency. We have applied two business rules based on the trends in the commodity industry
and customer feedback.
1. For Natural Gas, we would always consider the value or reading provided by the Utility Provider. In case, we
don’t receive a bill from the Utility Provider due to delay in the bill generation or for any other reason; then we
consider the value/bill provided by the Utility Supplier.
2. For Electricity and any other utility, we would look for the data from the bills provided by Utility Supplier and in
case we don’t have such information; we then tend towards the bill information from the Utility Provider.
Irrespective of the data type meaning raw data or calendar/weather normalized the Bill Type Priority business rule is
applied first followed by the Account Type Priority rule and the data is displayed at the reports.
The following image is a screenshot of a virtual bill (a keyed version of an original bill/invoice.) from the ProUtility
Enterprise edition. Check for the Consumption Month & Year, Account Type and Bill Type.
Calendar Normalized Data
Bill Billing Period Dates Months Covered Consumption Month
11th January
To
28th February
(49 Days)
Jan – 21 Day(s)
Feb – 28 Day(s)
February 2025
In the simplest example above, even though there has been consumption in the month of January and February; the bill
data is only displayed only when searched for February, 2025. The raw data has the following limitations:
Raw Data Limitations
1. The consumption month & year is always biased giving more weightage to a month which has more number of
days.
a. For e.g. if a billing period is from 11 th of January, 2025 - 28th of February, 2025; then the consumption
month is recorded as February, 2013.
b. This is because January 11th - 31st has 21 days where as February 01st – 28th has 28 days (more number of
days).
c. Say the Bill # is 1234567890 and the Total Charges (USD) is $100.00 and the consumption in kWh is .
Here we are looking at a Bi-Monthly bill with approximately 21 days of usage in January and 28 days in
February.
d. However the Bill # would show up only when a report is run for February, 2013.
e. Also the amount will be shown as $100.00 in February even though practically it should be around
$50.00 (approx.)
2. Based on the above example stated, the information could be misleading and it would be difficult for the
analysts and planners to devise a budget and forecast the expenses.
3. The information on a monthly level as well is too confusing. The raw data reports would work fine only if the
reports are run across a longer period like Half-Yearly or Yearly.
4. Also, the raw data is provided based on the Billing Period Start Date & Billing Period End Date. So, if an electricity
meter was working for 10 days and became dysfunctional; the computed value will be confusing as the
consumption observed for only 10 days will be reflected against the Billing Period which could be more i.e. 30
days etc.
To overcome such problems, ProKarma has developed a strategy to handle the misleading information captured at Raw
Data. This was achieved through Calendar Normalization.
At Calendar Normalization, the values of Spend & Consumption/Usage are divided in day level data and are summed up in
the Analytics Module.
Calendar Normalization Example
Check the same example below:
Bill Billing Period Dates Months Covered Consumption Month Total (USD) Total (kWh)
11th January, 2013
To Jan – 21 Day(s) February
th $180.96 1622
28 February, 2013 Feb – 28 Day(s) 2013
(49 Days)
1. Here the raw data would should an amount of $180.96 in February.
2. To calendar normalize the data, the Total (USD) & Total (kWh) is divided into Billing Period Days.
3. Meaning per day (USD) and per day (kWh) is calculated.
4. So, $180.96 and 1622 (kWh) for 49 days would approximately be:
Description Formulae Calculations Calendar Normalized Data
Total Days 49
Total (USD) $180.96
Total (kWh) 1622
Per Day (USD) Total (USD)/ Total Days 180.96/49 $3.6930
Per Day (kWh) Total (kWh)/ Total Days 1622/49 33.1020 (kWh)
January (USD) = Per Day (USD) X 21 Days 3.6930 X 21 $77.553
January (21 Days) 695.142 (kWh)
January (kWh) = Per Day (kWh) X 21 Days 33.1020 X 21
February (USD) = Per Day (USD) X 28 Days 3.6930 X 28 $103.404
February (28 Days) 926.856 (kWh)
February (kWh) = Per Day (kWh) X 28 Days 33.1020 X 28
5. The raw data report showed $180.96 and 1622 (kWh) in the month of February, 2013; but the calendar
normalized data shows the equally calculated amount and consumption for January and February respectively.
Note: This is a simple example to show how ProKarma calculates Calendar Normalized Data. This logic is applied to the
raw values recorded at an individual Meter # level after applying the Bill Type Priority & Account Type Priority. The
calendar normalization is done at a day level. And the days count is taken from the Meter Reading Start & End Dates
rather than the Billing Period Start & End Dates.
Appendix to Calendar Normalization
Background:
Lot of time vendor bills may contain over lapping dates with one day overlap.
One method is to treat this to discount the overlapping day at the head side [or]
The tail side of each such bill.
Different Approaches of Calendar Normalization
First Approach:
This Approach will present accurate per/day Cost and Usage. However this method fails in two situations.
A) Corrected Bill is issued by the vendor: Such bill may overlap more than one earlier actual bills issued
by the vendor; since the corrected bill is out of sequence and there is no continuity, leaving out the
head [or] tail side need special treatment on case to case, subjective basis.
B) Sometimes there may be missing bills which can come in to fill an existing gap.
In such situations, the missing bills with overlap onto existing adjacent bills will need special treatment
also. It can mean recursive [or] iterative recalculation of per/day Cost and Usage of adjacent bills.
Hence this approach will work ideally only where every bill has continuity and will fail in some special
situations.
Second Approach:
This treats every bill on its own merit without factoring in overlap.
In this method per/day cost and usage will be lesser if there is an overlap since we count both the head end
and the tail end. However this method does not overstate/understate total usage or spend since there is an
only change in the ratio. At the Macro level the values are very similar as compared with the first approach. If
this approach can cover all scenarios and does not need manual, subjective interpretation on case to case
basis.
Third Approach:
Here we shift the problem back to the data level ingestion itself. We do not allow overlapping dates to be
entered. This can be done through an exception process. The rules for exception resolution can be blanket
from the customer-analyst or can be resolved on case to case basis.