Azure Marketplace Lead Management Using Azure Table and Flow

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.

1. Sign in to your Microsoft Flow account at https://flow.microsoft.com
   
2. On the left navigation bar, select
3. On the top navigation bar, select and then
4. In the form type a name for your Flow and configure the schedule. We will run our Flow every hour.
   
5. Click the button to create the Flow.
   
6. 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 select to add it to the Flow.
   
7. Configure the step to get the time one hour before the run.
   
8. 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 select to add it to the Flow.
   
9. 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.
10. After we create the connector, we will be able to select the Azure Table from the drop-down list.
    
11. 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 :
    

12. 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 select to add it to the Flow.
    
13. When you click in the first Choose a value field of the Condition control, a new pop-up will show. Select the tab 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 second Choose a value field. Your condition should look like the following:
    

14. 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.
15. In the If yes branch of the condition, click on the button to add a new action. Search for Select data operation and select  to add it to the Flow.
    
16. When you click in the From field, a new pop-up will show up. Choose the tab and select . That will create a list of all the entities from step 13 above.
    
17. 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']

    As you have guessed, you should type the value code in the field on the   tab.
    
18. 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 select  to add it to the Flow.
    
19. When you click in the From field, a new pop-up will show up. Choose the tab and select . That will select the HTML output from the previous step.
    
20. Click on the button to show the additional configuration options. Select Yes in the Include Headers field.
    
21. 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 select  to add it to the Flow.
    
22. 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.
23. In the email form, we will type the email to which we want to regularly send the leads in the To field.
    
24. 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., the Past time. Click in the Subject field and type “New Azure Marketplace Leads since.” Then, select from the tab in the pop-up.
    
25. Last, click in the Body field, and select from 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.