If you are sending Whatsapp template messages and would like to track the performance of the Campaigns, this solution will help you capture and record the status updates in a Google sheet. With the data in the Google sheet, you can later run reports to identify open and delivery rates.

Different Statuses
This solution will record statuses like Sent, Transmitted, Delivered, Read, and Rejected/ Failed. For more information on what each status means, please refer below:

Implementation Process

1. Download the flow template and import it into your workspace.
   
   
   
   
   
   
2. Download this spreadsheet to your computer and then import it (not upload) into Google Sheets. Once imported, make sure these tabs are at the bottom:
   
   
   
   
3. Link Google Sheets account from Integrations > Google Sheets > Configure.
   
   Select Link another account and follow the steps, click Save once it’s done.
   
   
4. Go to Flow Builder and configure the imported flow. Click Add row in Google Sheets step to connect the Google Sheets and define the variables for all the columns. Repeat the steps for each status.
   
   For sent, transmitted, delivered, and read statuses:
   
   Column A - Time - {{currentTime}}
   Column B - MessageID - {{message.id}}
   Column C - To - {{message.from}}
   Column D - To - {{message.to}}
   Column E - Template Name - {{message.content.hsm.templateName}}
   Column F - Status - {{message.status}}
   
   For rejected and error status:
   
   Column A - Time - {{currentTime}}
   Column B - MessageID - {{message.id}}
   Column C - To - {{message.from}}
   Column D - To - {{message.to}}
   Column E - Template Name - {{message.content.hsm.templateName}}
   Column F - Error Code - {{message.error.code}}
   Column G - Error Description - {{message.error.description}}
   
   
5. Publish the flow and copy the Flow webhook URL
   
   
   
   
   
6. You can create and enable the message updated webhook ONCE using Postman or Terminal after the above steps are done.
   
   Please refer to this video for better understanding - https://share.getcloudapp.com/qGuxzpNY
   

   
   curl --location --request POST 'https://conversations.messagebird.com/v1/webhooks' \
   --header 'Authorization: AccessKey your_API_KEY' \
   --header 'Content-Type: text/plain' \
   --data-raw '{
   
   "events": ["message.updated"],
   
   "channelId": "To be replaced with your Whatsapp Channel_ID",
   
   "url": "To be replaced with the WA Status Report flow webhook URL"
   }'

   Sample call from Postman:
   
   
   
   
   
   If you get 201 created as the response for the above call, it implies that the webhook is enabled successfully.
   
   If you receive 404 not found or any other error codes, reach out to the Support team or your MessageBird (TAM/CSM/TIM).
   
   
7. Once enabled, all message statuses from that channel ID will be automatically pushed to your Google Sheet. 

Note

• The final step [Step 6] needs to be done only once, and it can be done by yourself or the MessageBird team internally using Postman or Terminal. There is no need to make the webhook call after every campaign.
  
• In case the URL of the webhook flow has changed to a different URL, Step 6 needs to be done again using the updated webhook URL
  
• When a message is sent, it goes through different statuses, such as
• Transmitted →  Sent → Delivered → Read [or] 
• Transmitted → Failed
  
  So you can find a record for the same message with different statuses in each tab of the sheet.
  
• The timestamp displayed on the sheet is in UTC. With this solution, the time conversion to your specific timezone needs to be completed manually.

How to calculate the Delivery rate and Open rate

The Google sheet will have all the raw data of the different statuses the messages went through for all the Whatsapp template messages.

You can then use this raw data and derive on Delivery rate and Open rate using the following formulas:

Delivery rate = Number of messages delivered / Number of messages sent * 100

Open rate  = Number of messages read/ Number of messages sent or delivered * 100

Resources