# Cohort Retention Investigation for `app_id = 1006`

Date: `2026-07-24`
Scope: Dashboard page, Cohort Retention widget vs payment graphs
Change status: Investigation only, no logic changed

## Summary

For `app_id = 1006`, the low counts shown in the Dashboard "Cohort Retention" chart are reproducible from both Elasticsearch and PostgreSQL when the same cohort metric is used.

The large success counts visible in other dashboard graphs are also correct, but they come from a different metric and mostly from a different package-id set.

`ALTT` and `Kutingg` are legacy/current names for the same package lineage. The rename is visible in the data, but it is not the primary reason the cohort chart looks low.

## Main Finding

The cohort widget is not counting payment successes.

It does two separate things:

1. Select top package ids from subscription activity.
2. For each month, count users where both:
   - the customer root `created_at` falls inside the bucket, and
   - the subscription `subscription_created_at` also falls inside the same bucket,
   - for the selected package id,
   - with subscription status in `(2,3,7)`.

That means the chart is effectively counting:

- new customers created in the same period
- who also got that subscription in the same period

This is much narrower than payment graphs, which count order events by `tbl_order.status` and `tbl_order.created_at`.

## Verified Dashboard Window

The investigation used the same range visible in the screenshots:

- `2026-04-24` to `2026-07-24`

## Live Dashboard Result Reproduced

The live Cohort Retention widget for `app_id = 1006` returned:

```json
{
  "Kutingg One Month (New)": [0, 0, 0, 0],
  "Kutingg 3 Months New": [0, 0, 0, 0],
  "ALTT 7 Days (New)": [0, 1, 0, 0],
  "Kutingg 5 Days": [0, 0, 0, 0],
  "ALTT Quarterly (IN)": [0, 0, 0, 0]
}
```

This matches the behavior seen on the dashboard screenshot.

## Package IDs Selected by Cohort Widget

For `app_id = 1006`, the cohort widget selected these top package ids:

- `10061773919443657168`
- `10061773919443480165`
- `10061773919443770170`
- `10061773919442993156`
- `1006177391942836053`

## Why Other Graphs Show Large Success Counts

Payment graphs are based on `tbl_order` and count order events like:

- `status = 2` as Success
- `status = 3` as Cancellation

For the same date range and `app_id = 1006`, PostgreSQL returned large payment counts, for example:

- `Razorpay / status 2 = 3611`
- `Razorpay / status 3 = 641`
- `itunes / status 2 = 211`

Those large counts are real, but they are driven mostly by package id:

- `10061773919445615201` -> `Kutingg 1 Month`

That package id does not appear in the cohort widget's selected top-5 package ids above.

## PostgreSQL Cross-Checks

### 1. Raw subscription volume for the cohort-selected ids is high

Within the same dashboard window:

- `10061773919443657168` -> `7640` subscription rows
- `10061773919443480165` -> `2176`
- `10061773919443770170` -> `8435`

So there is no general data absence for these package ids.

### 2. PostgreSQL equivalent of the current ES cohort metric is also low

When PostgreSQL is queried with the same logic as the ES cohort metric:

- Apr 2026: all `0`
- May 2026: only `10061773919443770170` returned `1`
- Jun 2026: all `0`
- Jul 2026: all `0`

So ES is not uniquely wrong here. The low result is consistent with the current cohort counting logic.

## Legacy Naming: `ALTT` vs `Kutingg`

The data confirms that `ALTT` is the older name and `Kutingg` is the newer name for the same lineage.

### Current package master (`tbl_packages`)

For `app_id = 1006`, `tbl_packages` currently labels these ids with Kutingg names:

- `10061773919443657168` -> `Kutingg One Month (New)`
- `10061773919443480165` -> `Kutingg 3 Months New`
- `10061773919443770170` -> `Kutingg 7 Days (New)`

### Subscription history (`tbl_customer_subscriptions`)

For the same ids, subscription history is still mostly stored under older ALTT names:

- `10061773919443657168`
  - `ALTT One Month (New)` -> `312563` rows
  - `Kutingg One Month (New)` -> `32` rows
- `10061773919443480165`
  - `ALTT 3 Months New` -> `100347` rows
  - `Kutingg 3 Months New` -> `29` rows
- `10061773919443770170`
  - `ALTT 7 Days (New)` -> `526399` rows
  - `Kutingg 7 Days (New)` -> `78` rows

This shows the rename history is present in stored data.

## Elasticsearch Cross-Checks

Direct ES document sampling for `app_id = 1006` and package id `10061773919443770170` returned order entries like:

- `orders.package_name = "ALTT 7 Days (New)"`

This confirms the mixed naming is already present in indexed ES documents and is not invented by the dashboard layer.

## Why The Cohort Legend Can Show Either Name

The cohort ES path gets display labels from a sampled nested order hit, not from a canonical package master lookup.

Because older and newer labels both exist for the same package id, the chart label can vary depending on which sampled order document is returned.

## Code References

### Cohort widget

- [app/Filament/Widgets/Dashboard/CohortRetentionChart.php](/var/www/php84/sms_admin_es/app/Filament/Widgets/Dashboard/CohortRetentionChart.php:246)
- ES package selection: [app/Services/Elasticsearch/DashboardPageElasticsearchService.php](/var/www/php84/sms_admin_es/app/Services/Elasticsearch/DashboardPageElasticsearchService.php:539)
- ES monthly count metric: [app/Services/Elasticsearch/DashboardPageElasticsearchService.php](/var/www/php84/sms_admin_es/app/Services/Elasticsearch/DashboardPageElasticsearchService.php:62)

### Payment graph

- [app/Filament/Widgets/Dashboard/PaymentHealthChart.php](/var/www/php84/sms_admin_es/app/Filament/Widgets/Dashboard/PaymentHealthChart.php:69)
- ES payment query: [app/Services/Elasticsearch/DashboardPageElasticsearchService.php](/var/www/php84/sms_admin_es/app/Services/Elasticsearch/DashboardPageElasticsearchService.php:227)

### PostgreSQL fallback path in cohort widget

- [app/Filament/Widgets/Dashboard/CohortRetentionChart.php](/var/www/php84/sms_admin_es/app/Filament/Widgets/Dashboard/CohortRetentionChart.php:565)

### ES sampled title extraction

- [app/Services/Elasticsearch/BaseElasticsearchQueryService.php](/var/www/php84/sms_admin_es/app/Services/Elasticsearch/BaseElasticsearchQueryService.php:85)

## Conclusion

For `app_id = 1006`, the low Cohort Retention chart is happening because:

- the widget is using a narrow "new customer + same-period subscription" metric,
- the selected cohort package ids are not the same ids that dominate payment success counts,
- and the package lineage carries legacy `ALTT` names and newer `Kutingg` names in historical data.

So the visible mismatch is:

- not a simple ES outage,
- not a simple PG mismatch,
- and not caused only by the rename,

but by the combination of:

- current cohort metric definition,
- package-id selection,
- and legacy/current naming stored across data sources.

## Investigation Status

- Verified in code
- Verified in Elasticsearch
- Verified in PostgreSQL
- No logic changed