Post Syndicated from rafaaws original https://aws.amazon.com/blogs/messaging-and-targeting/creating-a-costs-analytics-view-to-email-campaign-generated-by-amazon-pinpoint/

Introduction

Many companies have multiple departments using different campaigns in the same AWS account on Amazon Pinpoint and need to split costs at the end of each month between the owners of each campaign. To do this, companies need an easy way to find how much each campaign has generated of cost, since the Amazon Pinpoint console doesn’t have this information. To solution this, it is possible combine some AWS services to find out these costs.

In this post, I will demonstrate how AWS analytics and storage services such as Amazon Kinesis, AWS Glue, Amazon Athena, Amazon Quicksight and Amazon Simple Storage Service (Amazon S3) can help you create an analytical view of the costs generated by emails sent through each campaign on Amazon Pinpoint. Not include transactional and Amazon Journey e-mails on the example.

Amazon Pinpoint is an AWS service that helps companies to engage their customers across multiple channels. You can use the Amazon Pinpoint to send email, SMS, push notifications and voice messages to deliver one-off demands or across campaigns.

In this blog, you will learn how to create a dashboard with total cost of emails sent and MTA (Monthly Targeted Audience) by each campaign. With this information, you will be able to distribute costs internally to each department responsible for each campaign on Amazon Pinpoint.

Solution Overview

To create this dashboard, we will take advantage of the Digital User Engagement Events Database solution. We can use an AWS CloudFormation template that set up Amazon Pinpoint event flow. This solution uses the Amazon Kinesis to stream all events about campaigns to a bucket on Amazon S3. After that, a data processing task is performed by AWS Lambda and cataloged on AWS Glue. Some views will be created on Amazon Athena to organize all data and we will use them to calculating and analyzing the Pinpoint costs. For more information about the solution, architecture and how AWS CloudFormation template works to automate the deployment, please visit the Implementation Guide page.

During the deployment process, you will have the option to create a new Amazon Pinpoint project to manage your campaigns or use an existing one.

Prerequisites

1. Complete the Digital User Engagement Events Database implementation.

2. Have an e-mail campaign on Amazon Pinpoint created with some emails already sent.

Analytics View

All events created by Amazon Pinpoint through the campaigns after the Digital Use Engagement Events Database implementation is complete should be appearing in parquet file format in the Amazon S3. If your campaign did not generate any events after the AWS CloudFormation was completed, I recommend creating and executing a new email campaign just to test this solution. Any test email sent during the implementation of this solution will incur charges. The email costs will be explained during this blog.

After the entire Amazon Pinpoint event flow has worked correctly, some modifications on the views created in Amazon Athena must be made. These changes will help to access the information about the quantity of endpoints registered by each campaign of your project. For this, the following steps are required:

To create a new view

1. Open the Amazon Athena console.

2. Under Database, choose the database name created by AWS CloudFormation template.

You will notice a table called “all events” and some views, eg. campaign_send, email_open, email_send and others. These views are responsible to improve the organization of data sent by Amazon Pinpoint, eg. in the campaign_send view it is possible to see all informations about all events that Amazon Pinpoint sends across the campaign by multiple channels.

3. Choose Create view.

A tab will be added on the center page so you can add your command which will be responsible to created a new view.

4. Replace the existing text to the command below and choose Run query.

CREATE OR REPLACE VIEW endpoint_unit AS
SELECT DISTINCT
client.client_id endpoint_id
, "min"("from_unixtime"((event_timestamp / 1000))) event_timestamp
, "month"("from_unixtime"((event_timestamp / 1000))) month_data
, "year"("from_unixtime"((event_timestamp / 1000))) year_data
FROM
all_events
WHERE (event_type = '_campaign.send')
GROUP BY client.client_id, "month"("from_unixtime"((event_timestamp / 1000))), "year"("from_unixtime"((event_timestamp / 1000)))

In this command we are creating a new view, grouping all the endpoints already used by campaigns, along with the earliest date and time that the endpoint was registered. This command will help you identify the first campaign that used the endpoint in each month and year.

Once the new view is created, you will notice that it is listed in the views pane with the name endpoint_unit. You can run this view to check which values are returned.

5. Choose Preview in the endpoint_unit view to return the results.

Example:

The data displayed refer only to the information of the endpoints used in campaigns after the implementation of the Digital User Engagement Events Database.

Now is the time to create the analytical view in Amazon Quicksight.

To check Amazon Quicksight Region

1. Open the Amazon Quicksight console.

If this is your first time using Amazon Quicksight, a page will appear to subscribe to the services, feel free to choose the best option for your business.

Warning: Some costs might occur by using Amazon Quicksight. Check the Amazon Quicksight Pricing page for more information.

2. On top of the screen, choose <Role>/<Account-Name> and select the region where you have the Amazon Pinpoint project.

To check Amazon Quicksight Permissions

Check the permission of the Amazon Quicksight to enable the access to Amazon S3 bucket.

1. On top of the screen, choose <Role>/<Account-Name>, Manage Quicksight.

2. In navigation pane, choose Security & permissions.

3. Under QuickSight access to AWS services, choose Add or remove.

4. Inside the QuickSight access to AWS services table, under Amazon S3, choose Details.

5. Choose Select S3 buckets.

6. Check if the Amazon S3 bucket name checkbox containing all stream files is selected. If not, select the checkbox and choose Finish and Update.

Create a Dataset for Campaign cost

1. Back to the Amazon Quicksight main page.

2. In the navigation page, choose Datasets, New dataset.

3. Choose Athena.

Now, let’s add the table containing information regarding the number of emails sent per Amazon Pinpoint campaign.

4. In the New Athena data source dialog box, do the following:

a. For Data source name, type a name.

b. Choose Create data source.

5. In the Choose your table dialog box, do the following:

a. Choose Use custom SQL.

b. In first field, enter a name for the custom SQL.

c. In second field, paste the command below.

SELECT * FROM "due_eventdb"."campaign_send" where (message_tags['delivery_type'] = 'EMAIL')

This command is filtering only events of campaign based on emails.

d. Choose Confirm query.

6. In the Finish dataset creation dialog box, you will be asked to select between storing a copy of the data of this table in SPICE or performing a query directly from data source. Feel free to choose the best option for your business.

7. Choose Visualize.

After creating the dataset, you will be redirected to the Amazon Quicksight analytics creation page.

As the information sent by Amazon Pinpoint does not have the unit cost of each email sent, we will use three features called Parameters, Control and Calculated Field to calculate these amounts.

Parameters are named variables that can transfer a value for use by an action or an object. To make the parameters accessible to the dashboard viewer, you add a parameter control.

The calculated fields help you to transform your data by using one or more of the actions: Operators, Functions, Aggregate functions, Fields that contain data or other calculated fields.

Create an Amazon QuickSight parameter

1. In the navigation bar, choose + Add at the top of the screen.

2. Choose Add parameter.

3. In the Create new parameter dialog box, do the following:

a. For Name, type the name for the parameter, eg. Costemail.

b. For Data type, choose number.

c. For Values, select Single value.

d. For Static default value enter the unit cost of email. The cost of each Amazon Pinpoint email can be found on Amazon Pinpoint Pricing page.

e. Choose Create and Close.

Important: All costs in this blog will be calculated in USD.

After creating the first parameters, we need to create a manual control of these costs.

Create an Amazon QuickSight control

1. In the navigation pane, choose Parameters.

2. Under the name of parameters that you previously created, choose Add control.

3.  In the Add control dialog box, do the following:

a. For Display name, enter a name.

b. For Style, choose Text field.

c. Choose Add.

This control will help you in the future if you need to change the unit price without changing the Parameters configuration.

We now need to create the Calculate Field. It multiplying the total sent emails value by the unit costs.

Create an Amazon QuickSight calculated field

1. In the navigation bar, choose + Add at the top of the screen again.

2. Choose add calculated field.

3. In add name field, type a name.

4. Paste the expression below.

count({pinpoint_campaign_id}) * ${name_parameters}

5. Replace the “name_parameters” in the expression with the name of the Parameter you created earlier.

6. Choose Save.

We now have all fields available to create the chart on Quicksight.

Create an Amazon QuickSight dashboard

1. In the navigation page, choose Visualize.

2. Under Visual type, choose Vertical bar chart.

Note: If you prefer, you can change it later to other visual type.

3. Choose the fields pinpoint_campaign_id, calculated field that you just created and event_timestamp. Drag each field to X axis, value and group/color to create the chart.

Example:

In this example, you can see the cost in USD x Amazon Pinpoint Campaign ID between April and May 2021.

If you prefer, you can customize your chart in Format Visual. You can also build another view to show the amount of emails sent per campaign.

4. In the navigation bar, choose Share.

5. Choose Publish dashboard.

6. In the Publish a dashboard dialog box, under Publish new dashboard as, type a name.

7. Choose Publish dashboard.

If you want, you can share your dashboard with other username, group, or email address.

Now that we have the total costs of emails per each campaign, let’s create the chart for the total endpoint cost for each campaign.

Create a Dataset for MTA cost

1. Back to the Amazon Quicksight main page.

2. In the navigation page, choose Datasets, New dataset.

3. Choose Athena.

4. In the New Athena data source dialog box, do the following:

a. For Data source name, type a name.

b. Choose Create data source.

5. In the Choose your table dialog box, do the following:

a. Choose Use custom SQL.

b. In first field, enter a name for the custom SQL.

c. In second field, paste the command below.

SELECT distinct c.endpoint_id, e.pinpoint_campaign_id, c.event_timestamp
FROM "due_eventdb"."campaign_send" e
INNER JOIN "due_eventdb"."endpoint_unit" c ON c.event_timestamp = e.event_timestamp

d. Choose Confirm query.

6. In the Finish dataset creation dialog box, you will be asked to select between storing a copy of the data of this table in SPICE or performing a query directly from data source. Feel free to choose the best option for your business.

7. Choose Visualize.

This command is responsible to join the pinpoint_campaign view with the endpoint_unit view. This command will return the campaign ID responsible for contacting the endpoint for the first time on each month.

8. After the dataset is created, repeat the same steps to create a new Parameter, Control and Calculated Field on section Create an Amazon QuickSight parameter, Create an Amazon QuickSight control and Create an Amazon QuickSight calculated field, but when creating a new Parameter, you will use the unit cost of each endpoint, currently it is described on the Amazon Pinpoint Pricing page as Monthly Targeted Audience (MTA).

If you send messages from an Amazon Pinpoint campaign or journey, the unique endpoints you contact are known as a monthly targeted audience (MTA). You are charged on the number of MTAs targeted in a calendar month.

Important: In this calculation we are not considering the subtraction of the free tier values.

In the process of creating Calculated Field, use the following expression:

count({endpoint_id}) * ${name_parameters}

Also remember to replace the name_parameters field in the expression with the name of the parameter that you previously created in point 8 on Create a Dataset for MTA cost section to calculate the costs of endpoints.
In this expression you are calculating the MTA for the distinct endpoint contacted per month.

After this, you will also have all the required fields to create your total cost chart of endpoint per campaign. In this case, use the fields pinpoint_campaign_id, calculated field that you created earlier and event_timestamp.

Example:

In this example, you can see the MTA cost in USD x Amazon Pinpoint Campaign ID between April and May 2021.

Some customers usually have tens or hundreds of campaigns, on this case you can use the Filter option in the navigation pane to a specify a range of date.

Optional: If you prefer you can combine the email and MTA cost in the same Analyses and Dashboard.

Add more datasets in the same Analyses and Dashboard

1. Back to the Amazon Quicksight main page.

2. In the navigation page, choose Analyses.

3. Choose the email cost Analyses that you created on section Create an Amazon QuickSight dashboard.

4. Choose the pencil icon on Dataset option near to the Navigation page.

5. Choose add dataset.

6. In the Choose dataset to add dialog box, choose the dataset you created on section Create a Dataset for MTA cost.

7. Choose Select.

Now you can create each parameter, control and calculated field per dataset on the same Analyses and publish all charts on same dashboard.

Cleanup

To avoid incurring charges, navigate to AWS Cloudformation console and delete the stack that you used on Digital User Engagement Events Database solutions procedure.

After the stack is deleted, you also need to delete your dashboards, analyses and datasets on Amazon Quicksight. You can also delete the stream events data in your Amazon S3 bucket.

Conclusion

In this blog, we used the total cost of email sent and endpoints to create the charts, but it is possible to obtain several analyses in Quicksight using the views that became available in the Digital User Engagement Events Database solution, such as costs for push notifications and other types of channels.

Also try creating dashboards with other Amazon Pinpoint channels

To do this, use this same procedure, explore the view campaign_send to find all data about other channels and modify the SQL queries on Amazon Quicksight to create your dashboards.

---

About the Author

Rafael Rodrigues is an Enterprise Solution Architect for AWS based in Sao Paulo, Brazil. He helps customers innovate with modern IT architecture on cloud computing.