Tracking Referrals from Referrals

[HeathBiller]

Hi all. I’ve created a Customer Relationship Management (CRM) system using Appsheet. Currently under the household section, I have a data row for “Referred By”. This does a great job for me to create charts and dashboards to track the first-level referrals. I would love to be able to track second, third, etc level of referrals so I can create some charts and dashboards for those too. I am stuck trying to think of the best way to do that, if it is possible.

Example: If client A refers client B, I can easily track that. If client B then refers client C, I can also easily track that. I would like to set it up where I can have it show that client A referred client B but also show any clients that client B refers as flowing back up to client A. That way, I can see everything that came from the initial client A referral.

Any thoughts or suggestions would be appreciated.

[WillowMobileSys]

The best way is to build these lists as they are added, rather than trying to construct them later as needed.

I would add 2 columns to allow flexibility in obtaining direct versus indirect referrals:

1. Direct Referral - a Ref column assigned the Client who referred the new client. This automatically creates the [Related Direct Referrals] column for each Client.

2. Indirect referrals - an EnumList assigned the LIST of Clients by expression in hierarchy of referrals.

When a new row is added, you only need to look at the current Direct Referral row to build the Indirect Referrals:

1. Direct Referral = assigned the Client who made the referral.

2. Indirect Referrals = LIST([Direct Referral].[Indirect Referrals] + [Direct Referral].[Direct Referral])

Now, to get ALL referrals - direct and indirect - initiated from Client A, you would add the [Related Direct Referrals] column to all Clients that INCLUDE Client A in their [Indirect Referals] column. It would be an expression something like:

[Related Direct Referrals]
+
SELECT(Clients[Client ID], IN([_THISROW].[Client ID], [indirect Referrals]))

I hope this helps!!

[HeathBiller]

That is very helpful, thank you. A couple of questions.

1. The direct referral would just be added in manually by the user, which makes sense. For the indirect referrals though, will that formula (or a similar formula) be able to pull in all of the indirect referrals to add them automatically, or is that something that would have to still be added in manually? Ideally I want to set it up so that would pull in automatically.

2. For the all referrals part. Would it make sense to add that in as a 3rd column or is there a better way you’d recommend?

3. Ideally I want to be able to create charts and dashboards to be able to filter or show how many referrals came from a given individual so I want to make sure I set up my data well so that it will be easy to create those charts in AppSheet. Any additional recommendations would be great.

[WillowMobileSys]

@HeathBiller

pull in all of the indirect referrals to add them automatically, or is that something that would have to still be added in manually?

Automatically…the expression I provided should be assigned to the [Indirect Referrals] column and would be updated each time you add or edit the Client row.

@HeathBiller

For the all referrals part. Would it make sense to add that in as a 3rd column or is there a better way you’d recommend?

This depends on how you intend to use it which is why I was vague. If your plan is to view this list within the app, then I would add it as a Virtual column so that it stays updated with any data additions or updates.

If you expect to utilize this list only in a report, e.g. a PDF generated report, then I would only implement the expression in the PDF template so it doesn’t contribute to app performance.

@HeathBiller

Ideally I want to be able to create charts and dashboards to be able to filter or show how many referrals came from a given individual

Yes, one of the reasons I suggested separate columns. I don’t know what statistics you might report on but at a minimal it could be count of direct referrals and count of indirect referrals. You would need to add additional columns for these numeric columns to visualize counts on a chart. Any other stats you want to capture would be additional columns.

I hope this helps!

[Suvrutt_Gurjar]

It sounds that you are in a way trying to build a chain of referrals. So if A refers B and B refers C, then C is also linked to A .

If it is so, you could possibly get some ideas from using this sample app

Create hierarchy views using table views

There is a feature request for Hierarchy views and occasional posts wanting to have some sort of organizational chart. https://www.googlecloudcommunity.com/gc/Feature-Ideas/Hierarchy-Views/idi-p/261848 The below tip implements the hierarchy view using a table view, of course with some limitations listed below. The main idea is to selectively show the table rows based on user selection. The tip basically visually extends the concept described in the “Team Directory” sample app (https://www.app…

The sample app is about building hierarchy charts. However the concept is based on a hierarchy rank that links the employees in the reporting structure. You could possibly use it to link referral chain.

[HeathBiller]

Thanks! I appreciate all of the thoughts and suggestions.

[HeathBiller]

Great, thank you! I will look over this sample app to get some ideas.

[Suvrutt_Gurjar]

Hi @HeathBiller ,

I played with the Hierarchy Charts sample app and created the following sample app where the customer referral names are linked in an hierarchical way. The sample app can show upto 4 levels of linked referrals which I believe is good enough in any practical scenario.

AppSheet : mobile apps from spreadsheets : Template: Other for the industry

The table view that displays the client referral links looks like below.

Hope you find the sample app useful.

[HeathBiller]

Thank you! That is very helpful. I will start working on adding something similar to this into my CRM appsheet. I appreciate it.