If you publish offerings to Azure Marketplace, you have several options to manage the leads that the Marketplace generates. You can choose from CRM systems like Salesforce, Dynamics and Marketo, custom HTTPS endpoint, or Azure Table. A lot of startups choose the Azure Table option because they do not pay for CRM and don’t want to spend the time implementing custom endpoints. Unfortunately, the Marketplace leads documentation for Azure Table provided by the Azure team is not perfect. It explains how to configure Microsoft Flow to regularly fetch the leads from Azure Table and send an email with the list, but there are some issues with the OData statements, and the email is not at all user-friendly. In this article, I will walk you through the steps to properly configure Microsoft Flow to fetch the leads from Azure Table and send them in a readable form via email.
Microsoft Flow has several pricing tiers, including a free one, that you can choose from. If this is the only thing you will use Flow for, then you are ok using the free tier. It allows you up to 2000 runs per month per user. If you check for leads every hour, you will use a bit over a third of the allowed runs.
Azure Marketplace Leads Table Structure
Before we jump into the steps to configure Flow, let’s look at how the Azure Table containing the leads looks like. When you set up Azure Marketplace to send the leads to Azure Table, you only need to provide the connection information to the Azure Storage account to use. Azure Marketplace automatically creates a table with the name MarketplaceLeads
. A record in that table has the following columns: PartitionKey
, RowKey
, Timestamp
, ProductId
, CustomerInfo
, LeadSource
, ActionCode
, PublisherDisplayName
, OfferDisplayName
, CreatedTime
, and Description
. Here is a screenshot of the Azure Table:
As you can see from the table structure, you can use the same table for multiple offers published on Azure Marketplace. In our Flow configuration below, we will not differentiate between offers and will not include the offer information in the email.
Also, note that two columns contain the record creation time: Timestamp
, and CreatedTime
. However, only the Timestamp
column uses a standard timestamp format (ISO 8601). It is automatically set to the time of record creation. If you look at the column types, Timestamp
is of type DateTime
while CreatedTime
is of type DateTime
String
. We will use the Timestamp
column for comparison because it does not require conversion.
Configuring Microsoft Flow with Azure Table
Here are the steps to configure Microsoft Flow to fetch the Azure Marketplace Leads from Azure Table and send them via e-mail. The steps below are an extension of the steps in the Microsoft documentation. We will add more visuals, give more details for some of the steps, and describe the available options.
- Sign in to your Microsoft Flow account at https://flow.microsoft.com
- On the left navigation bar, select
- On the top navigation bar, select
and then
- In the form type a name for your Flow and configure the schedule. We will run our Flow every hour.
- Click the
button to create the Flow.
- In this and the next steps, we will add an action to get the time one hour before the run. Click the
button to add a new step to the Flow. Search for
Get past time
action and selectto add it to the Flow.
- Configure the step to get the time one hour before the run.
- In the following few steps, we will add an action to fetch the leads from Azure Table. We will set up a filter that returns only the leads created in the last hour. Click the
button to add a new step to the Flow. Search for
Get entities
action and selectto add it to the Flow.
- In this step, we will need to create a Connector to the Azure Storage account before we can select the Azure Table. Type a user-friendly name for the Connector and enter the credentials for access to the storage account – those are the storage account name and one of the keys.
Click the
button to create the Connector. Once we create the connector, it will be available in the
list from the left bar navigation.
- After we create the connector, we will be able to select the Azure Table from the drop-down list.
- Next, we need to configure the filter that will return only the leads from the last hour. Click on the
button to show the additional configuration options. Click in the
Filter Query
field, and type the following:Timestamp gt datetime'@{body('Get_past_time')}'
The inside of the field will dynamically change – the
@{body('Get_past_time')}
part will turn into:
- In the next step, we need to check whether there are any new leads from the past hour, and if they are, we need to send them via email. Click on the
button to add a further step to the Flow. Search for
Condition
control and selectto add it to the Flow.
- When you click in the first
Choose a value
field of theCondition
control, a new pop-up will show. Select thetab in the pop-up and type the following expression in the
field:
length(body('Get_entities')?['value'])
Change the comparison to
is greater than
and add 0 in the secondChoose a value
field. Your condition should look like the following:
- You can leave the
If no
branch of the condition empty. While testing though, it may be helpful to add some action to notify you that this code path got executed. We normally configure an e-mail action to notify us that there are no leads from the past hour. How to configure the email is explained below.
Note: Microsoft’s documentation instructs you to send the email in the next step. Unfortunately, the email will contain some cryptic JSON that we will show at the end of the post. Therefore, we will go through a few additional steps to extract the lead information and format it properly. - In the
If yes
branch of the condition, click on thebutton to add a new action. Search for
Select
data operation and selectto add it to the Flow.
- When you click in the
From
field, a new pop-up will show up. Choose thetab and select
. That will create a list of all the entities from step 13 above.
- Next, we need to fill in the map for the selection. The only thing we are interested in is the
CustomerInfo
column of the leads table (see above) because this is the column that contains the customer information. What we will do is create a list that contains only the customer information and remove the non-relevant information that the entities list contains. To do that, you need to have the following map:
Key Value FirstName json(item()?['CustomerInfo'])?['FirstName']
LastName json(item()?['CustomerInfo'])?['LastName']
Email json(item()?['CustomerInfo'])?['Email']
Phone json(item()?['CustomerInfo'])?['Phone']
Title json(item()?['CustomerInfo'])?['Title']
Company json(item()?['CustomerInfo'])?['Company']
Country json(item()?['CustomerInfo'])?['Country']
field on the
tab.
- In this step, we will build an HTML table. Click on the
button to add a new action. Search for
Create HTML table
data operation and selectto add it to the Flow.
- When you click in the
From
field, a new pop-up will show up. Choose thetab and select
. That will select the HTML output from the previous step.
- Click on the
button to show the additional configuration options. Select
Yes
in theInclude Headers
field.
- Now, that we have created a nice HTML table, we can send the email. There are connectors for Gmail, Outlook.com, and Office 365 Outlook (use V2 whenever po). We will use Outlook.com for this example but configuring the other two is similar. Click on the
button to add new action. Search for
Outlook.com
data operation and selectto add it to the Flow.
- We will need to sign in to Outlook.com to configure the connector. The emails will be sent from the Outlook.com (respectively Gmail or Office 365) account that we sign in with in this step. Click on the
button to sign in.
You will need to agree to the consent for access that pops up. Once you sign in, the Flow will create a connector that you can re-use in future Flows.
- In the email form, we will type the email to which we want to regularly send the leads in the
To
field.
- In the
Subject
field, we can type a static text like “New Azure Marketplace Leads”. However, we would like to differentiate between the emails. Hence we will add the time at which we checked minus the hour, i.e., thePast time
. Click in theSubject
field and type “New Azure Marketplace Leads since.” Then, selectfrom the
tab in the pop-up.
- Last, click in the
Body
field, and selectfrom the
tab in the pop-up. That will add the HTML table to the body of the message.
Testing the Azure Marketplace Leads Flow
To test the Flow, click on the button in the upper right corner. The Flow will show a green checkmark on every step that passed succesfully.
The email with the leads information contains a nicely formatted table as shown in the picture below.
Azure Marketplace Leads Table JSON Output
As mentioned earlier, if you follow the Microsoft documentation, you will configure Flow to send the unformatted Table output to the recepient e-mail. That is the raw JSON that you will receive if you call the Azure Table API and request the data. It looks something like this:
[{"odata.etag":"W/\"datetime'2019-06-13T00%3A42%3A43.5739109Z'\"","PartitionKey":"6:2F12:2F2019","RowKey":"CRIMSONPINNACLE:2ECP:2DAWESOMETRAINING:2DV01:2DPREVIEW:5F6FAC2A0A:2D145F:2D4F8A:2DB449:2D520DE196C080","Timestamp":"2019-06-13T00:42:43.5739109Z","ActionCode":"MSFT_TEST_696887391853763413","CreatedTime":"6/12/19 23:47:58","CustomerInfo":"{\"FirstName\":\"Toddy\",\"LastName\":\"Mladenov\",\"Email\":\"toddysm_training@outlook.com\",\"Phone\":\"+1 (425) 555-6677\",\"Country\":\"United States of America\",\"Company\":\"Crimson Pinnacle LLC\",\"Title\":\"Owner\"}","Description":"MSFT_TEST_696887391853763413","LeadSource":"MSFT_TEST_696887391853763413-MSFT_TEST_696887391853763413|CP Awesome Training","OfferDisplayName":"CP Awesome Training","ProductId":"crimsonpinnacle.cp-awesometraining-v01","PublisherDisplayName":"Crimson Pinnacle LLC"},{"odata.etag":"W/\"datetime'2019-06-13T00%3A43%3A01.8988305Z'\"","PartitionKey":"6:2F12:2F2019","RowKey":"CRIMSONPINNACLE:2ECP:2DAWESOMETRAINING:2DV01:2DPREVIEW:5FAC952AEC:2D9B6A:2D4A2F:2DBF7D:2DCBED9019D139","Timestamp":"2019-06-13T00:43:01.8988305Z","ActionCode":"PTL","CreatedTime":"6/12/19 23:47:58","CustomerInfo":"{\"FirstName\":\"Toddy\",\"LastName\":\"Mladenov\",\"Email\":\"toddysm_training@outlook.com\",\"Phone\":\"+1 (425) 555-6677\",\"Country\":\"United States of America\",\"Company\":\"Crimson Pinnacle LLC\",\"Title\":\"Owner\"}","Description":"","LeadSource":"AzureMarketplace-PLT|CP Awesome Training","OfferDisplayName":"CP Awesome Training","ProductId":"crimsonpinnacle.cp-awesometraining-v01","PublisherDisplayName":"Crimson Pinnacle LLC"},{"odata.etag":"W/\"datetime'2019-06-13T00%3A43%3A13.8292378Z'\"","PartitionKey":"6:2F13:2F2019","RowKey":"CRIMSONPINNACLE:2ECP:2DAWESOMETRAINING:2DV01:5F1C0F6581:2D76DA:2D4379:2D90CC:2D916545FB8280","Timestamp":"2019-06-13T00:43:13.8292378Z","ActionCode":"MSFT_TEST_706838591092761218","CreatedTime":"6/12/19 23:47:58","CustomerInfo":"{\"FirstName\":\"Toddy\",\"LastName\":\"Mladenov\",\"Email\":\"toddysm_training@outlook.com\",\"Phone\":\"+1 (425) 555-6677\",\"Country\":\"United States of America\",\"Company\":\"Crimson Pinnacle LLC\",\"Title\":\"Owner\"}","Description":"MSFT_TEST_706838591092761218","LeadSource":"MSFT_TEST_706838591092761218-MSFT_TEST_706838591092761218|CP Awesome Training","OfferDisplayName":"CP Awesome Training","ProductId":"crimsonpinnacle.cp-awesometraining-v01","PublisherDisplayName":"Crimson Pinnacle LLC"},{"odata.etag":"W/\"datetime'2019-06-13T00%3A43%3A23.0647457Z'\"","PartitionKey":"6:2F13:2F2019","RowKey":"CRIMSONPINNACLE:2ECP:2DAWESOMETRAINING:2DV01:5F89898207:2DD294:2D4D04:2D97F7:2D755435F19CEB","Timestamp":"2019-06-13T00:43:23.0647457Z","ActionCode":"PTL","CreatedTime":"6/12/19 23:47:58","CustomerInfo":"{\"FirstName\":\"Toddy\",\"LastName\":\"Mladenov\",\"Email\":\"toddysm_training@outlook.com\",\"Phone\":\"+1 (425) 555-6677\",\"Country\":\"United States of America\",\"Company\":\"Crimson Pinnacle LLC\",\"Title\":\"Owner\"}","Description":"","LeadSource":"AzureMarketplace-PLT|CP Awesome Training","OfferDisplayName":"CP Awesome Training","ProductId":"crimsonpinnacle.cp-awesometraining-v01","PublisherDisplayName":"Crimson Pinnacle LLC"}]
It is not very readble and user-friendly way to communicate the leads information to non-technical users. However, for technical users, it gives good details of the format of the data and how can we further improve the Flow. Example scenarios are:
- Adding the Azure Marketplace product information to the email subject and/or body
- Sending emails to different teams based on product or offering
Using Auzre Table is a cost-effective way to collect Azure Marketplace leads. We hope the article helped you set up your leads generation Flow. For any feedback about this article, please don’t hesitate to use the comments section. You can rate the article using the voting buttons below.
You must log in to post a comment.