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.
- 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.
- 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.
- Add a second calculated column called "Week" with the following formula: =YEAR(#Created at)&"_"&WEEKNUM(#Created at)
- 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.
- In C3, let's write the following formula: =COUNTIF('Intercom contacts'!#Week,C2). Then copy and paste the formula on the whole row.
- 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.
- 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.