Intercom: analyze your leads pipeline comparing weekly leads vs qualified leads

Count your total number of leads, qualified leads and the percentage of qualified leads for each week

The process

Try It for Yourself

What do I get out of it?

Have a clear view of the top of your funnel and improve your sales performance.

How does it work?

Let's assume your company is building a mobile app that's for now only available in the US and only available on iPhone. A Qualified lead will be a person who is based in the US and who owns an iPhone.

  1. Import the table "Contacts" from Intercom to Actiondesk in a sheet you'll call "Intercom conversations". See how to import your data in Actiondesk here. In this example, we'll use the default columns Created at, Type and Country, along with a custom field called Mobile Device.
  2. Add a first calculated column called "Is qualified" with the formula: =IF(AND(#'Mobile Device'="Apple iPhone",#Type="Lead",#Country="United States"),"Yes","No"). This calculated column will return "Yes" if the lead is qualified, "No" if not.
  3. Add a second calculated column called "Week" with the following formula: =YEAR(#Created at)&"_"&WEEKNUM(#Created at)
  4. Now we're ready to build our dashboard! Add a new sheet called "Pipeline dashboard", in B3, write "# of leads", in B4, "# of qualified leads. In C2, write the first week for which you want the data with the format "YYYY_WW", add to the right other weeks (as many as relevant for you). For instance, 2020_23 to 2020_30.
  5. In C3, let's write the following formula: =COUNTIF('Intercom contacts'!#Week,C2). Then copy and paste the formula on the whole row.
  6. In C4, let's write the following formula: =countifs('Intercom contacts'!#Week,C2,'Intercom contacts'!#'Is qualified',"Yes"). Then copy and paste the formula on the whole row.
  7. Finally, in C5, you can calculate the ratio of qualified leads by writing =IFERROR(C4/C3,"NA"). Using the IFERROR formula ensures that you won't get ERROR messages if you're trying to divide a number by a 0. Then, apply the % format to it, removing the number of decimals you want. And finally, paste your formula to the right.

Feel free to add future weeks to the dashboard and then hide the columns. This way, as time goes by, you'll have the value for the upcoming weeks already calculated at your disposal. You'll only have to unhide these columns.

What do I get out of it?

Have a clear view of the top of your funnel and improve your sales performance.

How does it work?

Let's assume your company is building a mobile app that's for now only available in the US and only available on iPhone. A Qualified lead will be a person who is based in the US and who owns an iPhone.

  1. Import the table "Contacts" from Intercom to Actiondesk in a sheet you'll call "Intercom conversations". See how to import your data in Actiondesk here. In this example, we'll use the default columns Created at, Type and Country, along with a custom field called Mobile Device.
  2. Add a first calculated column called "Is qualified" with the formula: =IF(AND(#'Mobile Device'="Apple iPhone",#Type="Lead",#Country="United States"),"Yes","No"). This calculated column will return "Yes" if the lead is qualified, "No" if not.
  3. Add a second calculated column called "Week" with the following formula: =YEAR(#Created at)&"_"&WEEKNUM(#Created at)
  4. Now we're ready to build our dashboard! Add a new sheet called "Pipeline dashboard", in B3, write "# of leads", in B4, "# of qualified leads. In C2, write the first week for which you want the data with the format "YYYY_WW", add to the right other weeks (as many as relevant for you). For instance, 2020_23 to 2020_30.
  5. In C3, let's write the following formula: =COUNTIF('Intercom contacts'!#Week,C2). Then copy and paste the formula on the whole row.
  6. In C4, let's write the following formula: =countifs('Intercom contacts'!#Week,C2,'Intercom contacts'!#'Is qualified',"Yes"). Then copy and paste the formula on the whole row.
  7. Finally, in C5, you can calculate the ratio of qualified leads by writing =IFERROR(C4/C3,"NA"). Using the IFERROR formula ensures that you won't get ERROR messages if you're trying to divide a number by a 0. Then, apply the % format to it, removing the number of decimals you want. And finally, paste your formula to the right.

Feel free to add future weeks to the dashboard and then hide the columns. This way, as time goes by, you'll have the value for the upcoming weeks already calculated at your disposal. You'll only have to unhide these columns.

Read more
Create My First Report
see all Intercom use cases
The Advantages

Key Benefits

Eliminate the need for expensive third-party connectors and laborious copy-pasting.

Seamlessly import, organize, filter, and manipulate your Intercom data. Analyze it alongside data from other sources, and build custom Intercom dashboards and reports.

View and organize your Intercom data within a familiar spreadsheet interface.

Seamlessly import, organize, filter, and manipulate your Intercom data. Analyze it alongside data from other sources, and build custom Intercom dashboards and reports.

Feel confident in knowing that your data is always accurate and up-to-date.

Thanks to real-time cloud-based updates, any changes to your data made from within Intercom are automatically reflected in your Actiondesk spreadsheet.

Save time to analyze your data and make better decisions for your business
Create My First Report
Learn More

Our Use Cases

Create My First Report
X
Please wait...
Oops! Something went wrong while submitting the form.