Introduction
Operators are often very particular about the KPIs their directional partners create for them. Creating these can be time-consuming and difficult and operators are asking for these KPIs more frequently.
In this tips and tricks blog, we’ll walk through how to use Drakewell’s cross-job exports to make it easy to create popular KPIs. We hope this sparks your imagination and you find many other KPIs you can create using Drakewell.
Important Note!!! Since Cross Job Exports take advantage of Drakewell’s powerful searching and filtering system, there are thousands of ways you will be able to slice and dice this data.
Below are just a few examples of exporting your data and then building KPIs from it.
Summary of Exports
- 1. Revenue (Operator Cost) Per Foot for an Operator
- 2. Average ROP for a District
- 3. Average Days on Job (simple version) for a Play
- 4. Average Slide ROP for a Motor Manufacturer
- 5. Average Rotate ROP for a Motor Vendor
- 6. Rotate/Slide Footage Ratio for Owned Motors
- 7. Percent Rotate/Slide Footage Ratio for Owned Motors
- 8. Average Shipments from Districts to Jobs
Revenue (Operator Cost) Per Foot for an Operator
The Export
With your jobs filtered click the Export dropdown and select Export BHAs. You’ll receive an email shortly with a link to two different files, an Excel File and a Composite CSV file. In this case, we’ll use the Excel File. Click that link in the email and a download will kick off.
The KPI
Open the Excel file and then summarize the Distance Drilled and the Cost columns. Divide the Cost summary by the Distance Drilled summary. This gives you the average cost per foot in $/Foot for a particular Operator/Customer.
The Pseudo Formula
SUM(Cost Range)/SUM(Distance Drilled Range)
Average ROP for a District
The Filter
On the Job List Screen filter your jobs by Completed and Invoiced Jobs in the Status filter. If you’re following the recommended way to workflow job statuses, this will get you completed jobs (Completed) and jobs which have finalized invoicing (Invoiced). Then select a particular district from the District filter.
The Export
With your jobs filtered click the Export dropdown and select Export BHAs. You’ll receive an email shortly with a link to two different files, an Excel File and a Composite CSV file. In this case, we’ll use the Excel File. Click that link in the email and a download will kick off.
The KPI
Open the Excel file and then summarize the Distance Drilled and the Drilling Hours columns. Divide the Distance Drill Summary by the Drilling Hours Summary. That gives you the average ROP for the District in Feet/Drilling Hour.
The Pseudo Formula
SUM(Distance Drilled Range)/SUM(Drilling Hours Range)
Average Days on Job (simple version) for a Play
The Export
With your jobs filtered click the Export dropdown and select Export Jobs. You’ll receive an email shortly with a link to two different files, an Excel File and a Composite CSV file. Download the Excel file.
The KPI
Create a new column to the right of the Estimated End Date column. In the new column find the Days between the Start Date column and the Estimated End Date column (pseudo formula below). Summarize the days on the job and then divide by the total job count in the export (last row number minus one). That will give you the Average days on the job for the filtered range.
Pseudo Formula
Job Days Column – Days(Start Date, Estimated End Date) KPI - SUM(# of Job Days)/Job Count (Last row number minus one for the header)
Potential Additions or Improvements
You may want to determine Days on Job using active vs. standby days as tracked in Drakewell. Drakewell does track this information and makes it available in various forms throughout the system. In a future release, these numbers will be added to the Job Export. Be sure to keep up with our regular release notes for updates.
Average Slide ROP for a Motor Manufacturer
The Export
On the Asset List screen filter your assets by “Motor” under the Asset Type filter and then by the search box enter manufacturer_name:Your Manufacturer Name. Click Filter. Export these Assets and open the download. If you don’t see a column for Slide Hours, Slide Distance, or Slide ROP, ask your Drakewell account representative how you can add these to your asset configuration.
The KPI
Average the range of values for the entire Slide ROP column. This will give you the average Slide ROP for all of the filtered Assets in the export.
Pseudo Formula
AVERAGE(Slide ROP first row:Slide ROP last row)
Average Rotate ROP for a Motor Vendor
The Export
On the Asset List screen filter your assets by “Motor” under the Asset Type filter and then by the search box enter vendor_name:Your Vendor Name. Click Filter. Export these Assets and open the download. If you don’t see a column for Rotate Hours, Rotate Distance, or Rotate ROP, ask your Drakewell account representative how you can add these to your asset configuration.
The KPI
Average the range of values for the entire Rotate ROP column. This will give you the average Rotate ROP for all of the filtered Assets in the export.
Pseudo Formula
AVERAGE(Rotate ROP first row:Rotate ROP last row)
Rotate/Slide Footage Ratio for Owned Motors
The Export
On the Asset List screen filter your assets by “Motor” under the Asset Type filter and then select the Owned checkbox under the Rental filter. Click Filter. Export these Assets and open the download. If you don’t see a column for Slide Footage and Rotate Footage, ask your Drakewell account representative how you can add these to your asset configuration.
The KPI
Summarize the range of values for the entire Slide Footage and the entire Rotate Footage column. This will give you the total footage Slid and the total footage Rotated with those assets. Next, divide the total Rotate Footage by the total Slide Footage. This will give you the ratio between how many feet you rotate for every foot you slide. For example, if the total slide footage is 10 and the total rotate footage is 100 the above KPI calculation would give you 10. Meaning for every 10 feet your rotate, you slide only 1 foot or a 10:1 ratio for your owned motors. There are many interesting comparisons you can make using these ratios; Owner vs. Rented ratio, Vendor vs. Vendor ratio, etc.
Pseudo Formula
SUM(Rotate Footage Range)/SUM(Slide Footage Range)
Percent Hours Sliding for an Operator in a particular Play
The Export
For this KPI you will need to BHA Export again. Filter by the appropriate Operator under the Customer filter on the Job List page. Then filter by the appropriate Play under the Play filter. Open the Exports dropdown and select Export BHAs. Download the Excel export file for this KPI.
The KPI
In the BHAs tab for the Excel file summarize the values of the Drilling Hours column and summarize the values of the Sliding Hours column. Now divide the summarized Sliding Hours by the summarized Drilling Hours. Convert that cell into a percentage format and you’re done.
You now have the total % of Sliding hours for all the data you filtered.
Pseudo Formula
SUM(Sliding Hours Range)/SUM(Drilling Hours Range)
Average Shipments from Districts to Jobs
The Export
Navigate to Inventory -> Shipments in Drakewell. In the Shipments dashboard, select the All Shipments tab. This is a list of all the shipments complete or active. If you have a particular date range you want to analyze enter that in the filters.
The KPI
This particular KPI will use a bit more advanced Excel formulas. We will be using the Shipments tab for this KPI. The first is the UNIQUE keyword. We want to create a list of unique To Location names. In a cell under the data in the To Location column use the UNIQUE formula and select all the values in the To Location column. This will create a unique list of To Locations. While it sounds like complex Excel acrobatics, in practice it will only take you a few minutes to get this KPI.
Next, we’ll use the COUNTIFS formula keyword. COUNTIFS will allow us to count the number of rows (shipments in this case) for our unique list of To Locations. In the column to the right of the unique list of To Locations we’ll use the COUNTIFS formula with three different ranges and criteria.
COUNTIFS works by passing the formula range of values and then the criteria it will use to decide whether or not to count that the value for a row in that range.
The first range in COUNTIFS will be the To Location and for the criteria, we will reference the unique To Location value in the cell to the left. The second range will be the To Location Type and the criteria will be “Job”, as we only want to count shipments going to a job. The final range will be the From Location Type and the criteria will be “District”, as we only want to count shipments coming from a District.
Once the formula is created for the first cell, drag it down to duplicate that same formula for each of the unique To Locations. This will give you the number of shipments the particular To Location in each row which is both from a district and to a job.
Finally, we want to get the average of the count for each unique To Location. Since we filtered our COUNTIFS by job you will see 0s when one of the unique To Locations is not a Job. We can’t simply run an average on that entire column as those 0s will skew our results. In the column to the right of the counts, we will use one more formula, the IF formula keyword. We want the counted value only if the count is greater than 0, otherwise, we want the cell to be blank. Drag this formula all the way to the end of the unique To Location list. Finally, use the AVERAGE formula keyword to get the average of all the values in this last column.
With that you’ll have the average number of shipments sent from your Districts to your Jobs.
Pseudo Formula
Unique To Location Column – UNIQUE(To Location Range)
Count of Unique To Location Occurrences Column – COUNTIFS(To Location Range, Unique To Location Value, To Location Type Range, “Job”, From Location Type Range, “District”)
Final Count Column – IF(Count Column>0, Count Column, ””)
Average – AVERAGE(Final Count Column Range)
Note about the Future
We are constantly working on ways to help you gather data and to better understand your operations. We have a number of 2018 initiatives to build out new tools and techniques in Drakewell that will help our customers push the industry forward and create a better “Digital Oilfield”. This is just the beginning of what’s coming and we’re excited to continue to innovate with you.