How do I move a prospect to a customer base after sign-up?

Could you give a little bit more info? I guess you have a Customer database, where a custumer is listed after he signes up. Do you register prospects in another database?

Yes. Currently. I’m wondering if I should put all in one database and adjust their status as an alternative. But, yes, at the moment, I’m trying to maintain 2 databases. If someone from the Prospect database signs up, I’m looking for a way to move them from the Prospect database to the Customer database automatically.

Maybe Zapier is the right tool, but I don’t know enough about that.

Why do you have those Tables in separated Bases? They seem very related to me.

Originally, I was thinking that the prospects would come in from a lead form and then purchase through a different POS system, like Square. As I try to put this together, it’s starting to look like they may all go into a contact base and get categorized to one table or the other. After I set-up how and where they get entered, I do expect to use Zapier in some capacity.

I do a very similar thing. Basically I have set a cron job on our webhost (since webhooks are not available, but hopefully in the pipeline) to check via the Airtable API whether there are records with a certain value in a certain field (e.g. a signed up field). If the records meets the condition then it is copied to another base and removed from the original base – thus it is moved.

I put together a dummy prospect→customer base as a test. It doesn’t actually do anything differently than what has been discussed, but I think it may do so a little more gracefully.

The base contains two tables, [Prospects] and [Customers]. ([Prospects] is loaded with 250 fake records from that terrific prototyper’s tool, fakenamegenerator.com.) In the demo, the conversion of a prospect to customer status is signified by the selecting of the {Prospects::Customer} checkbox, which in turn leads to the {Customer Name} field being populated.

In my envisioned scenario, these newly flagged customers are used to generate new, linked [Customer] records at some point in the process. This might be done manually, either record-by-record or batched, or it could be done by Zapier, et al., on either a triggered or interval basis.

To do so manually takes only three keystrokes (and maybe a mouseclick):

1. With the targeted {Customer Name} selected, press Ctrl-C to copy the cell.
2. Press ►, the Right Arrow key, to move to the adjacent {Customer Link} field.
3. Press Ctrl-V to paste the value into the {Customer Link}.

This will cause a new [Customers] record to be created with its primary field set to {Prospects::Customer Name}.

Over in the [Customers] table, the generation of a new, linked record results in the population of a Linkup field, {DataLU}, which retrieves from the [Prospects] table a portmanteau value consisting of a number of [Prospects] fields, tagged and concatenated. A series of Formula fields deconstructs this composite field into its component parts, breaking out the customer name, address, and so on.

If you look at any of these Formula fields, you will notice they all consist of various MID() and FIND() statements in which the source or searched string is in turn represented by an IF() statement:

IF(CustomerData,CustomerData,ARRAYJOIN(DataLU))

This preferentially selects the {CustomerData} Single-line text field over the Lookup text field, {DataLU}, and allows for the decoupling of customer and prospect data — for instance, to allow for a table of purchased prospect names to be deleted to make way for a new set of prospects.

The mechanism for doing so is another copy/paste three-step:

1. Copy the {DataLU} cell.
2. Move to the adjacent {CustomerData} cell.
3. Paste the copied data.

Once the looked-up data is replicated as a local text field, the link between the records may be broken without corrupting [Customers] data.

I use Airtable’s Pro-level record coloring function to flag [Prospects] records where {Customer} has been checked but no corresponding linked record created, and [Customers] records where {CustomerData} has yet to be replicated from {DataLU}.

This base gives you the ability, then, to generate new customer records automatically when a prospect converts and, later, to decouple the generated record from the impermanent prospect data store. In both instances, the effort required to effect such changes is a 3-keystroke sequence; in both instances, said manual action can easily be replaced with middleware automation.

(Note this implementation assume — and requires — a unique identifier be used for the [Customers] table primary field; otherwise, the first copy/paste sequence above will simply create an additional link to an existing [Customers] record.)

Personally, I think I would have a single table for Customers and Prospects (using a status to differentiate) - unless there’s a very good reason NOT to. Filtered views then provide the degree of separation needed.

This then means that any attached records in other tables are maintained etc.

I think the OP didn’t want to maintain a large datastore of largely irrelevant information. For instance, he purchases a set of prospect IDs and contact info from, say, a related company, some of whose customers had ticked the opt-in box authorizing them to receive information from third-party providers. Even a well-crafted campaign for a significant product or service would manage to convert no more than a few percent of total prospects. Once that campaign is over and the successful conversions have been made, there is little reason to maintain such a large amount of rapidly obsolescent data — other than as a gift to Airtable’s monthly figures. :winking_face: