Retention ChartをPendo Aggregation APIで取得する方法

自分用メモシリーズ

過去六ヶ月のRetention Report取る方法
途中のselectで取得したいデータの選択が可能。

```json
"select": {
    "relativePeriods": "0",
    "metaInfo.cohort size": "possibleSizePerCohort",
    "metaInfo.month": "formatTime(\"January 2006\", beginningOfCohort)"
```

## クエリ構成：
```python
#prepare rest api call
import requests
import json
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import json

# pendo aggregation api
pendo_url = 'https://app.pendo.io/api/v1/aggregation/'
pendo_token = "xxxxxxxx"
pendo_headers = {'x-pendo-integration-key': pendo_token, 'content-type': 'application/json'}

# Retention Query
cohert_retention = {
  "reposnse": {},
  "request": {
    "name": "cohert",
    "pipeline": [
      {
        "source": {
          "timespan": {
            "kind": "visitor",
            "appId": "-323232"
          }
        }
      },
      {
        "identified": "visitorId"
      },
      {
        "group": {
          "group": [
            "visitorId"
          ],
          "fields": [
            {
              "activeDays": {
                "daysActiveUnion": "activeDays"
              }
            },
            {
              "firstTime": {
                "min": "firstTime"
              }
            },
            {
              "lastTime": {
                "max": "lastTime"
              }
            }
          ]
        }
      },
      {
        "retention": {
          "timeSeries": {
            "period": "monthRange",
            "first": "dateAdd(startOfPeriod(\"monthly\",now()),-6,\"months\")",
            "count": 7
          },
          "offset": 30,
          "cohortField": "beginningOfCohort",
          "firstvisitField": "firstvisit",
          "relativePeriodsField": "relativePeriods"
        }
      },
      {
        "filter": "!isNil(relativePeriods)"
      },
      {
        "filter": "beginningOfCohort >= startOfPeriod(\"monthRange\", dateAdd(startOfPeriod(\"monthly\",now()),-6,\"months\")) && beginningOfCohort < dateAdd(startOfPeriod(\"monthly\",now()),0,\"months\")"
      },
      {
        "fork": [
          [
            {
              "group": {
                "group": [
                  "beginningOfCohort"
                ],
                "fields": [
                  {
                    "possibleSizePerCohort": {
                      "count": "visitorId"
                    }
                  }
                ]
              }
            },
            {
              "sort": [
                "beginningOfCohort"
              ]
            },
            {
              "select": {
                "relativePeriods": "0",
                "metaInfo.cohort size": "possibleSizePerCohort",
                "metaInfo.month": "formatTime(\"January 2006\", beginningOfCohort)"
              }
            },
            {
              "group": {
                "group": [
                  "relativePeriods"
                ],
                "fields": [
                  {
                    "Results": {
                      "list": "metaInfo"
                    }
                  }
                ]
              }
            }
          ],
          [
            {
              "unwind": {
                "field": "relativePeriods"
              }
            },
            {
              "group": {
                "group": [
                  "relativePeriods"
                ],
                "fields": [
                  {
                    "Total visitors": {
                      "count": "visitorId"
                    }
                  }
                ]
              }
            }
          ]
        ]
      },
      {
        "join": {
          "fields": [
            "relativePeriods"
          ]
        }
      },
      {
        "sort": [
          "relativePeriods"
        ]
      }
    ]
  }
}
```

## きれいにする
```python
body = json.dumps(cohert_retention)
# send request as post
response = requests.post(pendo_url, headers=pendo_headers, data=body)
# display response as table


df = pd.DataFrame(response.json())
rslt_df = df['results'][0]['Results']

df1 = pd.DataFrame(columns=['Cohort Size', 'Month'])

for i in range(len(rslt_df)):
    df1.loc[i, 'Cohort Size'] = rslt_df[i]['cohort size']
    df1.loc[i, 'Month'] = rslt_df[i]['month']

display(df1)
```

## 結果:
![CleanShotHB1jrOHT.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/227518/868cc98e-246a-9409-b7f5-72a354c43a0f.png)