Create report presentations from CRM data, enhanced with enrichment API, and summarized with AI tools

Imagine you give lots of data to an artificial intelligence (AI) tool. It analyzes the data, and you get a PowerPoint presentation with detailed charts and graphs, showing all the conclusions you need. Pretty cool, right? This is exactly what's happening now: AI is increasingly used to solve business tasks.

For example, AI is used in due diligence for mergers and acquisitions (M&A). Marc Vogelsang, a senior manager at Ernst & Young Switzerland, points out that AI tools are getting better at gathering public data, analyzing it, identifying risks, and making conclusions. These tools are expected to change how things are done. He provides a great example in his article "How AI will impact due diligence in M&A transactions":

The annual report mentions that the target has sold a real estate property in FY 2022. AI can pick this up autonomously and check whether all the documentation that is common in connection with such a sale is available. In seconds, the software could flag a missing notarial deed or a tax declaration where the purchase price does not match the amounts in the financial statements.

In this post, we'll look at a simpler example to help you start using AI for your reports. We'll create a presentation about customers and sales, including steps like:

• Pull data from CRM (Dynamics 365 in our case)
• Enrich it with publicly available data using the Clearbit API
• Feed enhanced data to an Artificial Intelligence tool (ChatGPT in our case)
• Populate data into a PowerPoint report using Plumsail Documents

We'll connect all the tools in Power Automate, but you can use any other integration platform.

 

  Further in the article:

1. CRM data overview
2. Set up the PowerPoint report generation process
3. Create and configure a flow in Power Automate
4. Enrich CRM data table with Clearbit
5. Use ChatGPT to analyze and summarize Data from Dataverse
6. Generate a PowerPoint presentation from an enriched Dataverse data table
7. Conclusion: Create advanced reports with artificial intelligence
8. Get started with PowerPoint report generation

 

1. CRM data overview

Before setting up workflows, let's examine the data we will be working with. The screenshot below displays a data table containing information about clients in Dynamics 365; this table is built based on data stored in Dataverse. Therefore, when creating an action in a Power Automate flow, we will directly access the Dataverse platform.

 

This table comprises the following columns:

• first_name
• last_name
• email
• domain
• sales

Additionally, there are empty columns that we intend to enhance with Clearbit data:

• company_name
• company_description
• company_employees
• company_location
• company_sector

Our database table has already been pre-configured to align with the specific data formats defined within each column. Make sure you go over your database tables closely, because if the data types don't match up, it can mess up when you're trying to load or get data out.

2. Set up the PowerPoint report generation process

Next, we will proceed with the setup and configuration of the PowerPoint report generation process within Plumsail Documents. This entails the completion of the following steps:

1. Add Process

• Go to the Documents app within your Plumsail account
• Add a new document generation process
• Select Start from blank
• Give the process a name "Customer Report" and select the PPTX template format

 

2. Configure Template

In this step, we will need to customize our template. You will find more detailed information on this action in the section Prepare and test a template of the Plumsail Documents Documentation.

Editor:

• Upload a pre-prepared template

Our ready-made PowerPoint template includes tokens. Tokens in a template serve as special placeholders or markers designed for dynamically inserting data into a document or presentation. When a program processes the template, it replaces these tokens with real data from a data source, such as a database, table, or any other provided information source.

Template tokens can be divided into three categories:

1. Slide tokens containing client information
2. Tokens for tables
3. Tokens for charts

 

Read more about PowerPoint template syntax in documentation.

Below is the JSON template.

{
  "Slide": [
    {
      "Name": "value1",
      "Sector": "value2",
      "Description": "value3",
      "Empl": "value4",
      "Location": "value5",
      "Sales": "value6"
    }
  ],
  "Company": [
    {
      "Name": "value1",
      "Sector": "value2",
      "Sales": "value3"
    }
  ],
  "chart": [
    {
      "it": "value1",
      "cd": "value2",
      "mat": "value3"
    }
  ],
  "Summary": "value1"
}

 

• Verify the template fields and click Save & Next

 

Configure outgoing file:

• Set the switch to the Production position.
• Confirm the file name and add tokens if necessary
• Choose the desired file format for the final document. Click Save & Next

 

You can read about this action in the Plumsail Documents Documentation.

3. Add Delivery

In this section, we must determine the method of delivering the document.

• Select Save to SharePoint from the available options
• If required, log in to your SharePoint account
• Choose the destination path
• Proceed to the next step by clicking Save & Next

 

For a thorough exploration, turn to the Documentation in section Deliver generated documents.

4. Start Process

Setting up the PowerPoint report generation process is complete. let's move on to setting up our flow in Power Automate.

 

3. Create and configure a flow in Power Automate

After our preparation is completed, we move on to the stage of creating a flow using Power Automate. We can conditionally divide our flow into 3 steps:

1. Enriching the data of our table in the database
2. Analysis of data using ChatGPT
3. Populating the results to the Plumsail Documents template and generating the document

 

For the convenience of reviewing the entire flow, we switched to the old interface design, then all screenshots will be made using the new design.

4. Enrich CRM data table with Clearbit

In order to enrich the database with data, we need an account on the Clearbit platform. Clearbit is a data enrichment platform that provides detailed information about people and companies, often used for marketing, sales, and recruitment purposes. It offers a suite of APIs that enable businesses to improve their customer interactions by providing more context about their users.

After logging into your Clearbit account, in the settings, you can find your individual API key. We need it to send HTTP requests from Power Automate.

 

We also need the request URL, which we can find in the Clearbit Documentation. The documentation provides a list of requests that allow you to access different data. Some of them are only available with a paid subscription, but there are also free ones.

 

Now we can return to Power Automate and begin setting up the steps in the first stage:

Trigger Setup:

• Create a new flow in Power Automate
• Choose the appropriate trigger. For instance, a manual trigger or a scheduled trigger depending on when and how you want the flow to run. In our case, it Manually trigger a flow.

Get data from Dataverse to HTTP request:

• Use the List rows present in a table action in Dataverse
• Select the Dataverse table where the data for Dynamics 365 is stored

 

Get Data from Clearbit:

• Action: Add the HTTP action
• Method: GET
• URL: https://autocomplete.clearbit.com/v1/companies/suggest?query=:domain

Using a domain name from the Dataverse table, we search for the company that owns it. Along with the company name, extracting additional information about it from Clearbit. We took the request URL from the Clearbit documentation, which contains a detailed description of it.

• Headers: In the first column we enter: Authorization, in the second: Your Bearer value.

You can also find more information about authentication method in the Сlearbit Documentation.

• Body request: Leave the field empty

 

Parse Clearbit Data:

• Add a Parse JSON action to handle the JSON response from Clearbit
• Provide the schema that corresponds to the Clearbit response

You can generate the scheme based on the examples given in the Clearbit Documentation in the request description.

 

Add Data to Dataverse:

• Use the Update a row action in Dataverse
• Select the specific table
• Map the fields from the Clearbit response to the columns in your Dataverse table

 

Setting up the table data enrichment process is complete. After launching the flow, our Dataverse table will be enriched with new data. At the same time, the data is updated in Dynamics 365

5. Use ChatGPT to analyze and summarize Data from Dataverse

Once we have augmented our CRM database with data, we proceed to analyze it using ChatGPT. Data analysis will involve the use of HTTP requests, for which we require an API key and a request URL.

To do this, we switch to the ChatGPT platform. Log in to your account. Click on the lock icon on the left panel and find yourself in the section with our API keys.

 

To get acquainted with the ChatGPT API requests in detail, you can return to the initial documentation page and click on the top tab API reference. In the Making requests you will find the requests. In addition, the documentation also has a section describing authentication methods when making a request. It's located right above Making requests.

 

NOTE To send API requests, you must have funds on your balance. You can check your balance in the Usage section.

1. Data analysis

Now we can go back to our flow in Power Automate and add new steps to analyze the data. Let's start by adding an HTTP request:

Add an HTTP action to analyze the data from Dataverse

• Action: Add the HTTP action
• Method: POST
• URI: https://api.openai.com/v1/chat/completions
• Headers: In the first column we enter: Authorization, in the second: Your Bearer value.
• Body request:

{
  "model": "gpt-3.5-turbo-0125",
  "messages": [
    {
      "role": "user",
      "content": "Analyze the data from @{body('List_rows')} and conclude company sales, write down the companies in which industries bring in the most profit and the companies in which industries have the least. Write industries and company names without additional characters. Present your answer in 2 sentences."
    }
  ],
  "temperature": 0.7
}

   

In the body of the request, we chose the gpt-3.5-turbo-0125 model because it allows us to increase the number of characters in the response. A list of all models and their limitations can be found in the ChatGPT Documentation.

We also need to record the ChatGPT response into a variable in order to later merge it with our data array from the database. The variable will need to be created before the request and assigned a string type. Also, before setting the value of the variable, we will need to do Parse JSON.

Parse ChatGPT Data:

• Add a Parse JSON action to handle the JSON response from ChatGPT
• Provide the schema that corresponds to the ChatGPT response

 

Create a variable to store the response:

• Click on + New step before HTTP request
• Choose Initialize variable
• Set the variable name: "gptSummary"
• Select the data type suitable for your response, such as String
• Leave the value field empty or set an initial value.

 

Assign a value to the variable:

• Add another step by selecting Set variable
• Choose the previously created stripe variable
• For the variable value, select the result from Parse JSON GPT ANALYSIS

 

Now information with analysis of the enriched Dataverse table is stored in the gptSummary variable.

2. Math calculations

The next step is to do some math using ChatGPT. We will ask the artificial intelligence to sum up the sales of each industry separately, then we will form a data array from the values and pass it to our template along with the rest of the data.

Our diagram contains 3 columns and for each of them we will need to create an HTTP request. And write them into variables:

• gptMat - Materials Industry
• gptCd - Consumer Discretionary
• gptIt - Information Technology

And then compose them.

Let's walk through the process of creating an industry variable using the gptMath variable as an example. To do this, we need to create an HTTP for the ChatGPT, similar to the previous request.

Add an HTTP action for mathematical calculations of variables for charts

• Action: Add the HTTP action
• Method: POST
• URI: https://api.openai.com/v1/chat/completions
• Headers: In the first column we enter: Authorization, in the second: Your Bearer value.
• Body request:

{
  "model": "gpt-3.5-turbo-0125",
  "messages": [
    {
      "role": "user",
      "content": "Based on the analysis of the table @{outputs('List_rows')?['body/value']}, Find all the sales in the sales column with name sales that correspond to the value in the Materials company_sector column and add them up. Write your answer as a number without using letters or words in the format: number. "
    }
  ],
  "temperature": 0.7
}

   

Parse ChatGPT Data:

• Add a Parse JSON action to handle the JSON response from ChatGPT
• Provide the schema that corresponds to the ChatGPT response

Create a variable to store the response:

• Click on + New step before HTTP request
• Choose "Initialize variable"
• Set the variable name: "gptMat"
• Select the data type suitable for your response, such as String
• Leave the Value field empty or set an initial value.

Assign a value to the variable:

• Add another step by selecting Set variable
• Choose the previously created stripe variable
• For the variable value, select the result from Parse JSON GPT CHART MAT

We do the same steps for the remaining two variables: "gptCd" and "gptIt". When all three variables are ready, you will need to connect them using the Compose action, according to the JSON schema for the generated document template.

Compose Data for chart

• Add a Compose action and give it a name "Compose GPT CHART"
• In the Inputs field we indicate the JSON scheme:

{
  "it": @{variables('gptIt')},
  "cd": @{variables('gptCd')},
  "mat": @{variables('gptMat')}
}

Setting up processing of mathematical actions using ChatGPT is complete. Now we can use the results to generate a presentation chart.

NOTE When creating requests to artificial intelligence, try to formulate the exact question and indicate in what format you want the answer.

6. Generate a PowerPoint presentation from an enriched Dataverse data table

At the final stage, we will need to create all the data we are missing and populate it to our template using Plumsail Documents

1. Create array variables

According to our template JSON, we need to have 3 data arrays:

• to fill out the table
• to create a diagram
• to create individual company slides

We have already prepared the data to create the chart in our previous step. Now we need to create variables for the table and slide arrays. Let's start by creating an array variable for the table:

Initialize variable for table

• Choose Initialize variable action
• Set the variable name: "table"
• Select the data type suitable for your response, such as Array
• Leave the Value field empty or set an initial value.

 

Compose Data for table

• Add Compose action
• In the Inputs line we indicate the JSON scheme to fill out our template:

{
  "Name": "@{items('Apply_to_each_TABLE')?['cr183_company_name']}",
  "Sector": "@{items('Apply_to_each_TABLE')?['cr183_company_sector']}",
  "Sales": "@{items('Apply_to_each_TABLE')?['cr183_sales']}"
}

 

Assign a value to the variable:

• Add action Set variable
• Choose the previously created stripe variable
• For the variable value, select the result from Compose TABLE

 

We will need to go through the same steps to create a variable for the data array to generate company slides. The only difference will be the parameters of the Compose action. In the Inputs field we will indicate:

{
   "Name": "@{items('Apply_to_each_SLIDE')?['cr183_company_name']}",
   "Sector": "@{items('Apply_to_each_SLIDE')?['cr183_company_sector']}",
   "Description": "@{items('Apply_to_each_SLIDE')?['cr183_company_description']}",
   "Empl": "@{items('Apply_to_each_SLIDE')?['cr183_company_employees']}",
   "Location": "@{items('Apply_to_each_SLIDE')?['cr183_company_location']}",
   "Sales": "@{items('Apply_to_each_SLIDE')?['cr183_sales']}"
}

 

We will also indicate the format of the new variable as an array with name: "slide".

2. Compose data and generate report

Next, we will populate the resulting data array to a PowerPoint template and generate our report using Plumsail Documents.

Compose of received data into an array

• Add Compose action
• In the Inputs line we indicate the JSON scheme to fill out our template:

{
  "Slide": @{variables('slide')},
  "Summary": @{variables('gptSummary')},
  "Company": @{variables('table')},
  "chart": @{outputs('Compose_GPT_CHART')}
}

 

Transfer data to a template and generate a PowerPoint document using Plumsail Documents

• Add the action Plumsail Documents --> Start document generation process
• Select the process we created in advance in the Process Name line
• In the Data/JsonContent field we insert the data array we received from the previous process

 

The settings for our flow are complete. Now, after launching the flow, a PowerPoint report will be generated using the summarized data from the enriched table and ChatGPT.

 

7. Conclusion: Create advanced reports with artificial intelligence

Now you've seen how to use Power Automate as a bridge to connect Clearbit and ChatGPT, enabling you to dive deeper into customer behavior and fine-tune your business strategy. This guide has highlighted how, through Power Automate, you can orchestrate the flow of data between tools for more comprehensive analysis and reporting. By leveraging Plumsail Documents within this ecosystem, you're equipped to automatically generate detailed reports and dynamic PowerPoint presentations. Improving the quality of CRM data and expanding analytical capabilities with artificial intelligence plays a critical role in making well-informed decisions.

This method can be applied to various business operations, providing a way to automate and optimize processes effectively.

A close collaboration between AI software and experienced humans will be vital to offer top-notch M&A due diligence services in the future.

This collaboration proves beneficial not only for M&A due diligence but also across a wide range of business processes.

8. Get started with PowerPoint report generation

Register an account and get a 30-day free trial to explore endless possibilities.

If you have an issue setting up automation by yourself, you can always book a free Intro call with Plumsail and tell us your pain points and we will give our best to make it happen for you. Start automation now!