Add support for relational tables

Ideas and Feature Requests
1

Hi All,

It would be really cool if Mautic could support the concept of relational tables. Basically, a relational table is a table that holds data that is related to a contact, in a one-to-many relationship. For example, a relational table can hold all the history of online purchases for a single contact. A user will be able to create and delete such tables through the UI, and connect the data to the contacts by a key of his choosing.

Altough this concept would probably require much more CPU resources from mautic, it will take Mautic’s capabilities in segmentation, personalization and analysis to a whole new level along with making it more releveant for B2C applications.

2

I think this should be done using tags and a bit of scripting on the e-commerce side.

The result would be, otherwise, a duplication of data that is useless.

Taking your e-commerce example, this table should hold products data along with orders, then relate with the contacts: what if a product is deleted on the e-commerce?
Or an order is withdrawn?

Maintain the two systems in synch will be very very hard.

With a tag, instead, you have complete freedom to add tags related to a category, a product type, a promotion, and add and remove them without any issue regarding the synch.

3

@aerendir maybe you could help me out - I’d also love this feature that @OmerGafny is requesting, as it would solve a lot of problems. If I can make it work with tags, fine; but I can’t quite get my head around how they would work. Take the following use case:

Customer purchases an online product on the 21st; I want to send a reminder email 7 days later if they still haven’t accessed it online.

Using a relational table, for each product that’s purchased you’d have one line, including purchase date & last access date. How would you make it work with tags, as you somehow need to make a relation between product purchased, date it was purchased & date it was accessed - bearing in mind the customer could have purchased multiple products.

4

I think that such a feature should be implemented in an extension, and not in the core: is too much tailored.

The recovery of abandoned cart is a very specific feature.

Anyway, the decision is not on my side but in the dev team one.

5

fair enough - I’ve not been involved with Mautic long enough to understand the direction the team want to go. I can say though that if they’re aiming at being a solution for any business with eCom functionality, this should be core. And at that moment, any business that isn’t eCom yet, will be scrambling to get eCom functionality live…

6

Being able to define segments based on multiple tables would be really valuable for B2B marketers, who know a lot more about their customers than just their birthday. For instance:

  • past product purchases
  • past service purchase, like training
  • whether a contact visited a booth at a trade show
  • whether a contact attended a conference/company event
  • …

I agree that simple cases can be handled with tags, but in my opinion this quickly becomes unmanageable when you add many dimensions to your customer profile. Example: I want to send a message to people who bought this particular consumable item several times in the last 24 months.

  • I have a catalog of 20,000 references. Do I have to use 20,000 different values for the purchase tag field ?
  • How do I capture the fact that a customer bought the same article 3 times in the last 24 months with a tag?

You really need the proper data structure here … which is a purchases table! Moreover with tagging, you have to anticipate all use cases and tag your contacts accordingly in advance. What if you discover new marketing ideas on the spot?

Having the whole 360° customer data available in a single database, usable from a marketing tool, is the holy grail of marketing/sales. The problem is that this customer data is generally scattered into multiple saas/internal apps/silos. The big advantage of an open source solution like Mautic is that integrating all these silos is possible without waiting for vendor’s, often half baked, integrations.

Support for multiple tables is also a solution around the mundane problem of having too many fields on the contact table, which breaks Mautic because of MySQL limitations. Hundreds of custom fields can be naturally organized into several smaller, yet more logical and easier to navigate tables.

One problem I see though, is expressing segments/queries joining several tables around a contact (typical star schema). Such a query will possibly aggregate several one-to-many relations around a contact, and will be complex to express with a graphical editor in my opinion. Use real SQL instead?

This feature would clearly be a very significant development. One first step in this direction, without going for the full multi-table stuff, could be to allow JSON values as a new data type for custom fields. You could then store the complete purchase history directly attached to a contact/customer in the appropriate JSON structure. However, the problem of easily expressing meaningful queries on such JSON arrays still remains.

If some other Mauticians are interested in moving this “multi table profile” idea forward, please let me know here.

1 Like
7

Hi,
there are any news about ? Is possible with Mautic create table and relation ?

8

I have seen at the Mautic Con that a Swiss Company has made a workaround to store the purchase history as form submissions. But a relational table with a similar structure to form submissions would be useful, so that things are not mixed up. It may be interesting to have this table structure in the core, so that conditional logic can be applied consistently. Now the feeding of the table could be done with plugins for specific use cases. Would make much sense to have something like this.