Create summary of Google Play Store reviews

{
  "id": "GE9244KEY5823lcB",
  "meta": {
    "instanceId": "fe52080916406a2a9812a7975b2d9b9e9838f3ce0f0b34614c611fb2ca1528d3"
  },
  "name": "Create summary of Google Play Store reviews",
  "tags": [],
  "nodes": [
    {
      "id": "976a0252-5685-4678-83d8-925d06853179",
      "name": "Split Out4",
      "type": "n8n-nodes-base.splitOut",
      "position": [
        288,
        -384
      ],
      "parameters": {
        "include": "=",
        "options": {
          "destinationFieldName": ""
        },
        "fieldToSplitOut": "reviews"
      },
      "typeVersion": 1
    },
    {
      "id": "9ad91b18-e9c3-4e43-b301-cc833747fbe2",
      "name": "Loop Over Items",
      "type": "n8n-nodes-base.splitInBatches",
      "onError": "continueRegularOutput",
      "position": [
        -112,
        -384
      ],
      "parameters": {
        "options": {
          "reset": false
        }
      },
      "typeVersion": 3
    },
    {
      "id": "fd00f503-709f-472d-b0c3-8e8d617f8506",
      "name": "Loop Over Items1",
      "type": "n8n-nodes-base.splitInBatches",
      "onError": "continueRegularOutput",
      "position": [
        -192,
        96
      ],
      "parameters": {
        "options": {
          "reset": false
        }
      },
      "typeVersion": 3
    },
    {
      "id": "c67fa141-8df9-4010-8d46-b038bc540381",
      "name": "AI Agent - Summary creator",
      "type": "@n8n/n8n-nodes-langchain.agent",
      "onError": "continueErrorOutput",
      "position": [
        32,
        96
      ],
      "parameters": {
        "text": "=Create a summary of store reviews submitted. Start your message with \"Hi! This is your Google Play Store AI agent for *{{$json.app_name}}*\". In your output, include the number of reviews you processed to create the summary. The summary should be split into two groups: a summary about positive reviews and a summary about negative reviews. In your summary include also a breakdown of star ratings, ie. how many reviews there were for each star rating. Also include the average star rating.",
        "options": {
          "systemMessage": "=Your job is to respond to prompts using the information you gather from the Google Play Store reviews of an app called {{$json.app_name}}. Your main role is to help your users to gain insights from store review without having to read reviews one by one. Store review usually have star ratings with 1 star being the lowest rating and 5 star being the highest rating. They can also have a text description where the player of the game write a review about their experience with the app. These can be negative reviews or positive reviews. The lower the star rating is the more likely that the review is negative."
        },
        "promptType": "define"
      },
      "typeVersion": 2
    },
    {
      "id": "83cf0fc5-66cc-4598-b354-6dd0f4f9ef1c",
      "name": "Set the bundle ids",
      "type": "n8n-nodes-base.set",
      "position": [
        -512,
        -384
      ],
      "parameters": {
        "options": {},
        "assignments": {
          "assignments": [
            {
              "id": "ed79836f-8032-4daa-afc1-d0170157c8a0",
              "name": "=apps",
              "type": "array",
              "value": "[\n  \"com.bundle.id1\",\n  \"com.bundle.id2\",\n  \"com.bundle.id3\"\n]"
            }
          ]
        }
      },
      "typeVersion": 3.4
    },
    {
      "id": "93bea9f6-775f-4232-be24-b33bba745393",
      "name": "Sticky Note",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -592,
        -576
      ],
      "parameters": {
        "color": 7,
        "width": 272,
        "height": 336,
        "content": "Define the list of bundle ids of the Google Play Store apps you want to summarise reviews for. Bundle ids can be found in the Google Play Store url of the app. For example, bundle id of the app with url https://play.google.com/store/apps/details?id=com.tripledot.blockbash is com.tripledot.blockbash"
      },
      "typeVersion": 1
    },
    {
      "id": "0bcfc993-e58e-49d6-aeba-ad7accb2087b",
      "name": "Daily Trigger",
      "type": "n8n-nodes-base.scheduleTrigger",
      "notes": "Daily at 10am ",
      "position": [
        -720,
        -384
      ],
      "parameters": {
        "rule": {
          "interval": [
            {
              "triggerAtHour": 10
            }
          ]
        }
      },
      "notesInFlow": true,
      "typeVersion": 1.2
    },
    {
      "id": "53df7cef-c399-48a5-bd10-82ec4547999a",
      "name": "Split Out",
      "type": "n8n-nodes-base.splitOut",
      "position": [
        -288,
        -384
      ],
      "parameters": {
        "options": {},
        "fieldToSplitOut": "apps"
      },
      "typeVersion": 1
    },
    {
      "id": "e1b05a7a-a9ce-4eed-89f9-c4162d76f1f0",
      "name": "Sticky Note1",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -304,
        -464
      ],
      "parameters": {
        "color": 7,
        "width": 320,
        "height": 224,
        "content": "Loop over bundle ids (apps) in the list"
      },
      "typeVersion": 1
    },
    {
      "id": "68a7de8f-ad17-4b8b-8a5e-fba73b66794f",
      "name": "HTTP Request",
      "type": "n8n-nodes-base.httpRequest",
      "position": [
        112,
        -384
      ],
      "parameters": {
        "url": "=https://androidpublisher.googleapis.com/androidpublisher/v3/applications/{{$json.apps}}/reviews",
        "options": {
          "pagination": {
            "pagination": {
              "parameters": {
                "parameters": [
                  {
                    "name": "token",
                    "value": "={{$response.body.tokenPagination.nextPageToken}}"
                  }
                ]
              },
              "maxRequests": 10,
              "requestInterval": 10,
              "limitPagesFetched": true,
              "completeExpression": "={{$response.body.tokenPagination.nextPageToken == null}}",
              "paginationCompleteWhen": "other"
            }
          }
        },
        "authentication": "predefinedCredentialType",
        "nodeCredentialType": "googleApi"
      },
      "credentials": {
        "googleApi": {
          "id": "Px4DvklLsQOnXqCH",
          "name": "Google Service Account - [email protected]"
        }
      },
      "typeVersion": 4.2,
      "alwaysOutputData": true
    },
    {
      "id": "9a2f22a9-294e-472f-862f-cad8f9209d60",
      "name": "Sticky Note2",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        80,
        -464
      ],
      "parameters": {
        "color": 7,
        "width": 336,
        "height": 224,
        "content": "Fetch reviews from Google with a service account"
      },
      "typeVersion": 1
    },
    {
      "id": "beb2b08e-f53d-4b36-82a8-ae282ae477ba",
      "name": "Filter",
      "type": "n8n-nodes-base.filter",
      "notes": "Fetch yesterday's reviews",
      "position": [
        496,
        -384
      ],
      "parameters": {
        "options": {},
        "conditions": {
          "options": {
            "version": 2,
            "leftValue": "",
            "caseSensitive": true,
            "typeValidation": "strict"
          },
          "combinator": "and",
          "conditions": [
            {
              "id": "e5cc101a-9e06-4f33-8578-63c4acc66042",
              "operator": {
                "type": "dateTime",
                "operation": "equals"
              },
              "leftValue": "={{ $json.reviews.comments[0].userComment.lastModified.seconds.toDateTime('s').toISODate() }}",
              "rightValue": "={{$today.minus({days: 1})}}\n"
            }
          ]
        }
      },
      "notesInFlow": true,
      "typeVersion": 2.2,
      "alwaysOutputData": true
    },
    {
      "id": "63dfdf05-c792-49a9-830a-ec21edf907e1",
      "name": "Sticky Note3",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        464,
        -464
      ],
      "parameters": {
        "color": 7,
        "width": 464,
        "height": 224,
        "content": "Store the previous day's reviews in the vector store. Create a different namespace for each app (bundle id). Clear the name space once a week to remove old reviews."
      },
      "typeVersion": 1
    },
    {
      "id": "3107ffd9-94ea-43ef-9fad-d0c8c75c32b4",
      "name": "Pinecone Vector Store",
      "type": "@n8n/n8n-nodes-langchain.vectorStorePinecone",
      "position": [
        688,
        -384
      ],
      "parameters": {
        "mode": "insert",
        "options": {
          "clearNamespace": "={{ new Date().getDay() === 6 }}",
          "pineconeNamespace": "=google_play_store_reviews_{{ $('Split Out').item.json.apps }}"
        },
        "pineconeIndex": {
          "__rl": true,
          "mode": "list",
          "value": "n8n-store-reviews",
          "cachedResultName": "n8n-store-reviews"
        }
      },
      "credentials": {
        "pineconeApi": {
          "id": "AhisTk5eS6H3HYiC",
          "name": "PineconeApi account"
        }
      },
      "typeVersion": 1.3
    },
    {
      "id": "a7611074-702b-496a-bef4-be4eeffda2b9",
      "name": "Embeddings OpenAI",
      "type": "@n8n/n8n-nodes-langchain.embeddingsOpenAi",
      "position": [
        672,
        -160
      ],
      "parameters": {
        "options": {}
      },
      "credentials": {
        "openAiApi": {
          "id": "iAPtqcVj4gp3rRy2",
          "name": "OpenAi account"
        }
      },
      "typeVersion": 1.2
    },
    {
      "id": "d6f865d0-fba7-456e-9bed-b531b11250c5",
      "name": "Default Data Loader",
      "type": "@n8n/n8n-nodes-langchain.documentDefaultDataLoader",
      "position": [
        864,
        -160
      ],
      "parameters": {
        "options": {
          "metadata": {
            "metadataValues": [
              {
                "name": "star_rating",
                "value": "={{ $json.reviews.comments[0].userComment.starRating }}"
              },
              {
                "name": "date",
                "value": "={{$json.reviews.comments[0].userComment.lastModified.seconds.toDateTime('s')}}\n"
              },
              {
                "name": "app_version",
                "value": "={{ $json.reviews.comments[0].userComment.appVersionName }}"
              },
              {
                "name": "language",
                "value": "={{ $json.reviews.comments[0].userComment.reviewerLanguage }}"
              },
              {
                "name": "review_id",
                "value": "={{ $json.reviews.reviewId }}"
              }
            ]
          }
        },
        "jsonData": "=comment: {{ $json.reviews.comments[0].userComment.text }} | date: {{ $json.reviews.comments[0].userComment.lastModified.seconds.toDateTime('s') }} | star rating: {{ $json.reviews.comments[0].userComment.starRating }} | app version: {{ $json.reviews.comments[0].userComment.appVersionName }} | language: {{ $json.reviews.comments[0].userComment.reviewerLanguage }}",
        "jsonMode": "expressionData"
      },
      "typeVersion": 1.1
    },
    {
      "id": "fd7a2f0c-69f4-446e-978c-1b67d0041f87",
      "name": "Set app details",
      "type": "n8n-nodes-base.set",
      "position": [
        -544,
        96
      ],
      "parameters": {
        "options": {},
        "assignments": {
          "assignments": [
            {
              "id": "ed79836f-8032-4daa-afc1-d0170157c8a0",
              "name": "=apps",
              "type": "array",
              "value": "=[\n  { \n    \"app\": \"com.bundle.id1\",\n    \"app_name\": \"App1\"\n  },\n  {\n    \"app\": \"com.bundle.id2\",\n    \"app_name\": \"App2\"\n  },\n  {\n    \"app\": \"com.bundle.id3\",\n    \"app_name\": \"App3\"\n  }\n]"
            }
          ]
        }
      },
      "typeVersion": 3.4
    },
    {
      "id": "d2a57fa1-b396-4132-9ab6-7316a20a11cd",
      "name": "Sticky Note4",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -384,
        16
      ],
      "parameters": {
        "color": 7,
        "width": 320,
        "height": 224,
        "content": "Loop over bundle ids (apps) in the list"
      },
      "typeVersion": 1
    },
    {
      "id": "e9baa7a4-d0bc-4dd1-90da-4677d75d6c75",
      "name": "Sticky Note5",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -576,
        16
      ],
      "parameters": {
        "color": 7,
        "width": 176,
        "height": 224,
        "content": "Define app bundle ids and names"
      },
      "typeVersion": 1
    },
    {
      "id": "3e88c0ea-e6d5-412c-9aae-a702220d626d",
      "name": "Split Out1",
      "type": "n8n-nodes-base.splitOut",
      "position": [
        -368,
        96
      ],
      "parameters": {
        "options": {},
        "fieldToSplitOut": "apps"
      },
      "typeVersion": 1
    },
    {
      "id": "4eac3a49-5126-47b6-b748-fb97770dbab8",
      "name": "OpenAI Chat Model1",
      "type": "@n8n/n8n-nodes-langchain.lmChatOpenAi",
      "position": [
        -32,
        320
      ],
      "parameters": {
        "model": {
          "__rl": true,
          "mode": "list",
          "value": "gpt-4.1-mini"
        },
        "options": {}
      },
      "credentials": {
        "openAiApi": {
          "id": "iAPtqcVj4gp3rRy2",
          "name": "OpenAi account"
        }
      },
      "typeVersion": 1.2
    },
    {
      "id": "044cd2a3-c3d4-41a2-8c46-4291b3ba0ac8",
      "name": "Embeddings OpenAI1",
      "type": "@n8n/n8n-nodes-langchain.embeddingsOpenAi",
      "position": [
        96,
        448
      ],
      "parameters": {
        "options": {}
      },
      "credentials": {
        "openAiApi": {
          "id": "iAPtqcVj4gp3rRy2",
          "name": "OpenAi account"
        }
      },
      "typeVersion": 1.2
    },
    {
      "id": "ec306b60-8dbb-4ea9-a4c3-fa5ce4807f1d",
      "name": "Pinecone Vector Store1",
      "type": "@n8n/n8n-nodes-langchain.vectorStorePinecone",
      "position": [
        96,
        304
      ],
      "parameters": {
        "mode": "retrieve-as-tool",
        "topK": 500,
        "options": {
          "pineconeNamespace": "=google_play_store_reviews_{{ $('Loop Over Items1').item.json.app }}"
        },
        "pineconeIndex": {
          "__rl": true,
          "mode": "list",
          "value": "n8n-store-reviews",
          "cachedResultName": "n8n-store-reviews"
        },
        "toolDescription": "This is the vector store for the Google Play reviews data you need to answer queries"
      },
      "credentials": {
        "pineconeApi": {
          "id": "AhisTk5eS6H3HYiC",
          "name": "PineconeApi account"
        }
      },
      "typeVersion": 1.3
    },
    {
      "id": "053e5a35-875e-4e16-a788-ec2c7eea8425",
      "name": "Send to Slack channel",
      "type": "n8n-nodes-base.slack",
      "position": [
        400,
        80
      ],
      "webhookId": "91dd6021-c694-4470-9013-53d5f2e213f8",
      "parameters": {
        "text": "={{ $json.output }}",
        "select": "channel",
        "channelId": {
          "__rl": true,
          "mode": "id",
          "value": "C1111"
        },
        "otherOptions": {}
      },
      "credentials": {
        "slackApi": {
          "id": "tVU8HVcP92PJGgqD",
          "name": "Slack account"
        }
      },
      "typeVersion": 2.3
    },
    {
      "id": "18bd7d4e-7893-4c36-85fa-300cfe55f941",
      "name": "Sticky Note6",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        0,
        16
      ],
      "parameters": {
        "color": 7,
        "width": 272,
        "height": 224,
        "content": "Generate Google Play Store review summary for the app"
      },
      "typeVersion": 1
    },
    {
      "id": "cc8b8188-9d76-4dc1-babc-37cfc0c6cd84",
      "name": "Sticky Note7",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        352,
        16
      ],
      "parameters": {
        "color": 7,
        "width": 192,
        "height": 224,
        "content": "Post the summary to a Slack channel"
      },
      "typeVersion": 1
    },
    {
      "id": "071bb344-81f1-45f6-bd41-b3df56e7ef4f",
      "name": "Weekly trigger",
      "type": "n8n-nodes-base.scheduleTrigger",
      "notes": "On Fridays at 11am",
      "position": [
        -736,
        96
      ],
      "parameters": {
        "rule": {
          "interval": [
            {
              "field": "weeks",
              "triggerAtDay": [
                5
              ],
              "triggerAtHour": 11
            }
          ]
        }
      },
      "notesInFlow": true,
      "typeVersion": 1.2
    },
    {
      "id": "89236196-49fa-4fc9-a9c1-b8388b0f83d7",
      "name": "Sticky Note8",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -768,
        16
      ],
      "parameters": {
        "color": 7,
        "width": 176,
        "height": 224,
        "content": "Prepare and send the summary once a week"
      },
      "typeVersion": 1
    },
    {
      "id": "109b52b1-1223-49f2-88d5-60dc2d2d2b80",
      "name": "Sticky Note9",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -1360,
        -608
      ],
      "parameters": {
        "width": 448,
        "height": 928,
        "content": "## Google Play Store Reviews Summary with Pinecone, OpenAI & Slack Notification\n\nThis workflow automatically fetches reviews for one or more Google Play Store apps, summarizes the feedback using OpenAI, stores and manages review data with Pinecone, and posts the summary to a Slack channel. It is designed for product teams, community managers, or anyone who wants to keep track of app sentiment and review trends without manually reading each review.\n\n**Features:**\n\n* Fetches daily reviews for specified Google Play Store app bundle IDs using a Google Service Account.\n* Stores reviews in a Pinecone vector database for efficient retrieval and summarization.\n* Uses OpenAI to generate a summary, including:\n  * Positive and negative review highlights\n  * Star rating breakdown and average rating\n  * Total number of reviews processed\n* Posts the summary to a Slack channel of your choice.\n* Supports both daily and weekly triggers.\n* Automatically clears old reviews from the vector store weekly to keep data fresh.\n\n\n**Setup Instructions:**\n\n1. Add your Google Play app bundle IDs in the “Set the bundle ids” node.\n2. Configure your Google Service Account, Pinecone, OpenAI, and Slack credentials.\n3. Set your preferred Slack channel in the “Send to Slack channel” node.\n4. Enable the workflow and customize the schedule as needed.\n\n\n**Use Cases:**\n\n* Monitor app sentiment and user feedback trends.\n* Share review insights with your team in Slack.\n* Automate reporting for product or support teams."
      },
      "typeVersion": 1
    },
    {
      "id": "f215d048-e697-4925-a471-260062de023c",
      "name": "Sticky Note10",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -784,
        -464
      ],
      "parameters": {
        "color": 7,
        "width": 176,
        "height": 224,
        "content": "Fetch reviews once a day"
      },
      "typeVersion": 1
    }
  ],
  "active": false,
  "pinData": {},
  "settings": {
    "executionOrder": "v1"
  },
  "versionId": "2b67c997-3545-4e0b-ab4c-4ea4f5d53535",
  "connections": {
    "Filter": {
      "main": [
        [
          {
            "node": "Pinecone Vector Store",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Split Out": {
      "main": [
        [
          {
            "node": "Loop Over Items",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Split Out1": {
      "main": [
        [
          {
            "node": "Loop Over Items1",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Split Out4": {
      "main": [
        [
          {
            "node": "Filter",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "HTTP Request": {
      "main": [
        [
          {
            "node": "Split Out4",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Daily Trigger": {
      "main": [
        [
          {
            "node": "Set the bundle ids",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Weekly trigger": {
      "main": [
        [
          {
            "node": "Set app details",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Loop Over Items": {
      "main": [
        [],
        [
          {
            "node": "HTTP Request",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Set app details": {
      "main": [
        [
          {
            "node": "Split Out1",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Loop Over Items1": {
      "main": [
        [],
        [
          {
            "node": "AI Agent - Summary creator",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Embeddings OpenAI": {
      "ai_embedding": [
        [
          {
            "node": "Pinecone Vector Store",
            "type": "ai_embedding",
            "index": 0
          }
        ]
      ]
    },
    "Embeddings OpenAI1": {
      "ai_embedding": [
        [
          {
            "node": "Pinecone Vector Store1",
            "type": "ai_embedding",
            "index": 0
          }
        ]
      ]
    },
    "OpenAI Chat Model1": {
      "ai_languageModel": [
        [
          {
            "node": "AI Agent - Summary creator",
            "type": "ai_languageModel",
            "index": 0
          }
        ]
      ]
    },
    "Set the bundle ids": {
      "main": [
        [
          {
            "node": "Split Out",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Default Data Loader": {
      "ai_document": [
        [
          {
            "node": "Pinecone Vector Store",
            "type": "ai_document",
            "index": 0
          }
        ]
      ]
    },
    "Pinecone Vector Store": {
      "main": [
        [
          {
            "node": "Loop Over Items",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Send to Slack channel": {
      "main": [
        [
          {
            "node": "Loop Over Items1",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Pinecone Vector Store1": {
      "ai_tool": [
        [
          {
            "node": "AI Agent - Summary creator",
            "type": "ai_tool",
            "index": 0
          }
        ]
      ]
    },
    "AI Agent - Summary creator": {
      "main": [
        [
          {
            "node": "Send to Slack channel",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}