Indian Oil Lucky Draw Automation Case Study

Introduction

During an Indian Oil lucky draw campaign, customers were required to manually scan a QR code and submit their details to participate. In real petrol pump environments, customers were often in a hurry and skipped this step, making it difficult for pump staff to meet submission targets.

EGMA built an Excel-driven automation system where customer transaction data — including Customer Name, Mobile Number, Bill Number, Fuel Amount, and Vehicle Type (2-wheeler / 3-wheeler / 4-wheeler) — is uploaded via Excel, automatically submitted to the lucky draw form, and then recorded into structured Excel reports for both successful and failed submissions.

This eliminated manual form filling, improved accuracy, and ensured full campaign compliance.

---

Client Background and Challenges

Indian Oil petrol pumps serve high volumes of customers daily. The lucky draw campaign required every eligible transaction to be submitted online, but manual QR-based participation failed under real-world conditions.

Key Challenges

1. Low Customer Participation

   Customers avoided scanning QR codes and filling forms due to time constraints.

2. Operational Burden on Staff

   Pump attendants were forced to manually submit entries, slowing operations and increasing workload.

3. High Error Rates

   Manual data entry caused incorrect mobile numbers, bill IDs, and amounts.

4. No Centralized Submission Tracking

   There was no structured way to track which entries succeeded or failed.

5. Vehicle Category Requirement

   The campaign required capturing vehicle type (2-wheeler, 3-wheeler, or 4-wheeler), adding another validation layer to the form.

---

Solutions Implemented

1. Excel-Based Input System

EGMA designed a standardized Excel template containing:

• Customer Name
• Mobile Number
• Bill Number
• Fuel Amount
• Vehicle Type (2W / 3W / 4W)

Staff simply upload/export billing data into this format, and the automation processes each row automatically.

---

2. Automated Form Filling & Submission

For every Excel row, the automation:

• Opens the official lucky draw form
• Maps Excel columns to corresponding fields
• Selects the correct vehicle type option (2-wheeler / 3-wheeler / 4-wheeler)
• Submits the form automatically
• Waits for confirmation or failure response

No customer interaction is required.

---

3. Excel-Based Success & Failure Reporting

After execution, two Excel files are generated:

✅ Successful Submissions Excel

• Customer Name
• Mobile Number
• Bill Number
• Fuel Amount
• Vehicle Type
• Submission Timestamp
• Confirmation / Transaction ID

❌ Failed Submissions Excel

• Customer Name
• Mobile Number
• Bill Number
• Fuel Amount
• Vehicle Type
• Failure Reason
• Retry Status

These reports provide full operational transparency and compliance proof.

---

4. Automatic Retry for Failed Records

If submissions fail due to network or validation issues, the system retries automatically. Only unresolved failures remain in the failed Excel file for review.

---

5. Zero Disruption to Pump Operations

The automation runs independently and does not affect billing or fueling workflows, ensuring smooth on-ground operations.

---

Technologies Used

Automation & Processing

• Python-based automation scripts
• Headless browser automation for form submission
• Excel processing using Pandas / OpenPyXL

Reporting

• Excel file generation and export

Infrastructure

• Lightweight server deployment
• Scheduled batch processing

---

Key Features Developed

1. Excel-Driven Bulk Input

   • Processes hundreds of customer records at once
   • Includes vehicle type classification (2W / 3W / 4W)

2. Automated Lucky Draw Form Submission

   • Zero manual entry
   • Automatic dropdown/option selection

3. Excel-Based Compliance Reporting

   • Separate success and failure reports
   • Transaction ID capture

4. Failure Detection & Retry Engine

   • Automatic retries for transient errors
   • Persistent failure classification

5. High-Accuracy Data Validation

   • Mobile number format checks
   • Amount and bill number validation
   • Vehicle type normalization

---

Results and Impact

1. 100% Campaign Compliance

   Every eligible Excel record resulted in a lucky draw entry.

2. Eliminated Manual Form Work

   No QR scanning or manual typing required.

3. Near-Zero Error Rates

   Structured Excel input eliminated typos and invalid entries.

4. Improved Operational Visibility

   Managers could instantly see submission success/failure via Excel reports.

5. Scalable Batch Processing

   Hundreds of submissions processed in minutes.

---

Lessons Learned

1. Excel Is the Best Interface for Field Operations

   Using Excel as both input and output made the system instantly usable by non-technical teams.

2. Batch Automation Outperforms Real-Time User Interaction

   Removing customer dependency dramatically improved success rates.

3. Retry Logic Is Essential in Field Networks

   Network instability at petrol pumps required resilient retry handling.

4. Data Normalization Prevents Campaign Failures

   Standardizing vehicle type (2W / 3W / 4W) avoided invalid submissions.

---