Purchase Data Analysis

Solution

To quickly check for potential data entry errors or anomalies in purchase amounts from Swiggy orders (e.g., from restaurants like Paradise Biryani, Chutneys, Bawarchi in areas like Gachibowli and KPHB), I would compute the following descriptive statistics first:

• 1. Minimum Value:
  • This will immediately reveal if there are any negative purchase amounts (which are impossible for actual sales) or values like zero (which might indicate cancelled orders before payment, free promotional items without a main purchase, or data errors).
  • It will also quickly show if there are values below the expected ₹100 minimum mentioned by Kiran Kumar from Manikonda (after the "TELANGANA25" discount).
• 2. Maximum Value:
  • This helps identify unusually high purchase amounts. For example, a single food order worth lakhs of rupees would be highly suspicious and likely a data entry error (e.g., extra zeros).
• 3. Range (Max - Min):
  • While the min and max are key, the range gives a quick sense of the overall spread. An extremely large range often signals the presence of outliers on one or both ends.
• 4. Frequency Counts (especially for specific suspicious values):
  • Count the occurrences of purchase amounts equal to zero.
  • Count occurrences of purchase amounts between ₹1 and ₹99.99 to see how many fall below the expected minimum.
  • If specific problematic values are found (e.g., a common typo like ₹10 instead of ₹1000), checking their frequency can indicate a systematic error.
• 5. Count of Missing Values (NaNs):
  • Determine if there are any orders where the purchase amount is not recorded at all.
• 6. Basic Central Tendency and Dispersion (as secondary checks):
  • Mean and Median: A mean significantly different from the median can suggest skewness often caused by outliers.
  • Standard Deviation: A very high standard deviation relative to the mean can also indicate the presence of extreme values. However, min/max and frequencies are more direct for the specific error types mentioned.

These initial statistics, particularly min, max, and frequency counts around zero and below ₹100, are the fastest way to flag the most obvious data quality issues related to purchase amounts for Raghavendra Analytics' work with Swiggy.

Solution

Finding unusual values like ₹50 (from Pragathi Tiffins in Ameerpet) or -₹1000 (from Madhapur) requires careful investigation before deciding on cleaning. Here are possible explanations and my approach for Lakshmi Devi, the regional operations head:

Possible Explanations for ₹50 (from Pragathi Tiffins, Ameerpet):

• Special Low-Value Item/Offer: The restaurant might have had a promotional item (e.g., a single idli or a tea during a Sankranti special) priced very low, and this specific transaction somehow bypassed or was exempt from the usual ₹100 minimum order value (MOV).
• Incorrect MOV Application: The system might have failed to apply the ₹100 MOV rule after the "TELANGANA25" discount or other discounts. For example, an item was ₹125, "TELANGANA25" made it ₹100, then another specific item discount brought it to ₹50.
• Partial Order Fulfillment/Partial Refund (at source): Perhaps only part of a larger order was fulfilled and charged, or a partial refund was processed directly by the restaurant, leading to a net ₹50 transaction.
• Data Entry Error: A manual adjustment or entry error at some point in the data pipeline.
• Test Orders: Internal test orders by Swiggy or Raghavendra Analytics staff might use small, nominal amounts.

Possible Explanations for -₹1000 (from Madhapur):

• Full Refund: Most likely, this represents a full refund issued to the customer for a previous order of ₹1000. This could be due to order cancellation (perhaps a large Ugadi festival order that was cancelled), incorrect item delivery, poor quality, or other customer complaints.
• Chargeback: The customer might have disputed the charge with their bank, leading to a chargeback.
• Credit Note/Adjustment: A significant credit adjustment made to the customer's account for a major service issue.
• Data Entry Error: An erroneous negative sign or a mistyped refund amount.
• Fraudulent Activity Reversal: Though less common for this value, it could be a reversal of a fraudulent transaction.

Approach to Cleaning/Understanding for Lakshmi Devi:

1. Isolate and Flag: First, I would isolate all such anomalous records (e.g., purchase amount < ₹100, purchase amount < 0, or extremely high values).
2. Investigate with Contextual Data:
   • Check associated data for these orders: timestamp (to see if it aligns with Sankranti/Ugadi festival periods for cancellations/special offers), restaurant name (Pragathi Tiffins, Paradise Biryani, etc.), customer ID, order ID, any recorded discount codes (like "TELANGANA25"), delivery location (Ameerpet, Madhapur), and any notes or status flags (e.g., "cancelled," "refunded," "complaint").
   • Specifically for -₹1000, I'd look for a corresponding positive transaction of a similar amount from the same customer in Madhapur or related order IDs.
3. Consult with Swiggy Operations/Support:
   • Share the flagged order IDs with Kiran Kumar (Swiggy account manager from Manikonda) or Swiggy's operations/finance team to get internal explanations. They might have records of refunds, specific low-value promotions, or known system overrides.
   • For amounts like ₹50, check if Pragathi Tiffins ran any specific promotions that could explain it.
4. Categorize Anomalies: Based on the investigation, categorize each anomaly:
   • Verified Data Entry Error
   • Confirmed Refund/Chargeback (Legitimate Negative Value)
   • Confirmed Valid Low-Value Sale (e.g., special offer, specific item)
   • Test Order
   • Unexplained / Needs Further System Check
5. Treatment Strategy for Report:
   • Data Entry Errors: If possible, correct them by finding the true value. If not correctable, consider removing them from summary statistics like mean/median purchase amount, but document their removal and potential impact.
   • Refunds/Chargebacks (-₹1000): These are not "sales." They should be excluded from calculations of average sales amount but should be tracked separately as a cost or revenue deduction. Their frequency and reasons are critical for Lakshmi Devi to understand operational issues.
   • Valid Low-Value Sales (₹50): If confirmed as legitimate (e.g., a specific small item from Pragathi Tiffins, or a promotional offer that bypassed MOV), they should generally be kept in the dataset for sales analysis. However, their presence should be noted if they significantly impact average order value, especially if the goal is to understand typical spending on full meals.
   • Test Orders: Should typically be excluded from general sales analysis.
6. Documentation for Lakshmi Devi: In the report, I would:
   • Clearly list the types and counts of anomalies found.
   • Explain the investigation process and the likely cause for each type.
   • Detail the cleaning actions taken (e.g., "X records removed due to being verified refunds," "Y records below ₹100 investigated; Z were valid promotions").
   • Present summary statistics both before and after cleaning critical errors if the impact is significant, to show transparency.
   • Highlight the importance of tracking refunds and understanding reasons for low-value orders as separate operational metrics.

Solution

Different data cleaning approaches for Swiggy's purchase amounts have distinct tradeoffs, and the communication strategy needs to be tailored for stakeholders like Prasad Reddy (Telangana Restaurant Association President) and Anand Sharma (Swiggy's South India Director). My approach would also vary when analyzing data from different restaurant segments.

Tradeoffs of Different Cleaning Approaches:

1. Approach 1: Strict Removal of Anomalies (e.g., all values < ₹100, negative values, extreme highs)
   • Pros:
     • Produces "cleaner" summary statistics (mean, median AOV) that might better reflect typical full meal orders.
     • Simpler for some high-level trend analysis if the goal is to only look at standard transactions.
   • Cons:
     • Loss of Information for Prasad Reddy: Removing all sub-₹100 orders might hide valid business models of small tiffin centers (e.g., in Dilsukhnagar) or specific low-cost items from popular restaurants like Pragathi Tiffins. He needs to understand the full spectrum of restaurant operations. Negative values, if refunds, represent a cost/issue for restaurants.
     • Masks Operational Issues for Anand Sharma: Removing sub-₹100 orders hides non-compliance with the minimum order policy (after "TELANGANA25" discount). Removing negative values (refunds from Madhapur customers) hides customer service issues or operational failures that Swiggy needs to address.
     • Can introduce bias if the "anomalies" are actually legitimate, albeit different, types of transactions.
2. Approach 2: Keep All Data, Report with Caveats
   • Pros:
     • Full transparency; no data is hidden from stakeholders.
     • Reflects the raw operational reality, including errors and exceptions.
   • Cons:
     • Summary statistics (especially mean AOV) can be heavily skewed and misleading if not properly contextualized for both Prasad Reddy and Anand Sharma.
     • May make it harder to discern underlying trends in "standard" purchase behavior.
     • Could lead to misinterpretation if stakeholders focus only on aggregate numbers without understanding the data composition.
3. Approach 3: Segmented Analysis & Conditional Cleaning (Recommended)
   • Strategy:
     • Correct obvious data entry errors where possible (e.g., if -₹1000 was clearly meant to be a refund for a +₹1000 order).
     • Isolate and analyze "problematic" categories separately:
       • Track refunds/negative values as a distinct operational metric (crucial for Anand Sharma).
       • Analyze orders below ₹100 separately to understand MOV compliance and the nature of low-value orders (e.g., from Pragathi Tiffins vs. Paradise Biryani).
     • For main AOV analysis, potentially exclude confirmed errors and refunds, but clearly state this. For low-value valid orders, decide on inclusion based on the analysis goal (e.g., include for overall GMV, exclude for "typical meal AOV").
   • Pros:
     • Provides a nuanced view for both stakeholders. Prasad Reddy can see the performance of different restaurant types. Anand Sharma gets insights into both typical customer spending and operational issues/exceptions (refunds, MOV non-compliance after "TELANGANA25" use).
     • Reduces distortion of key metrics while retaining valuable information from non-standard transactions.
     • Facilitates targeted actions.
   • Cons:
     • More complex to execute and explain. Requires clear definitions of segments and cleaning rules.

Differing Approach for Upscale Banjara Hills Restaurants vs. Small Dilsukhnagar Tiffin Centers:

• Upscale Restaurants (e.g., in Banjara Hills, Jubilee Hills, near Gachibowli):
  • An order of ₹50 would be highly unusual and more likely an error, a tiny add-on to a missed larger order, or a very specific small promotional item. I would be more inclined to investigate such values individually and potentially exclude them from AOV calculations if they are confirmed errors or non-representative.
  • The expectation is higher AOV, so small values are more anomalous.
  • Impact of "TELANGANA25" might be less significant as a percentage of total bill.
• Small Tiffin Centers / QSRs (e.g., in Dilsukhnagar, Ameerpet, KPHB):
  • An order of ₹50 (like from Pragathi Tiffins) could be a legitimate single meal or a common snack. Removing all such orders would severely misrepresent their business model and contribution.
  • Here, it's more important to understand the volume of such orders and their compliance with the ₹100 MOV (especially post-discount).
  • The analysis might focus on the frequency of sub-₹100 orders and the reasons, rather than just AOV. Perhaps special offers like Sankranti or Ugadi deals genuinely result in lower valid order values that need to be understood.

When presenting to Prasad Reddy and Anand Sharma, transparency is key. I would clearly state the raw data characteristics, the cleaning/segmentation approach taken, and the rationale. For Prasad Reddy, the focus might be on how different restaurant segments (like Chutneys vs. local tiffin centers) perform and the impact of Swiggy's policies. For Anand Sharma, the focus might be on overall AOV trends, operational efficiencies (refund rates, MOV compliance), and identifying areas for improvement (e.g., why are orders from Manikonda customers below MOV, or why high refunds in Madhapur).