Segment filter missing contacts

Product Support
1

Your software
My Mautic version is: 4.1.1
My PHP version is: 7.4
My Database type and version is: tidb

Your problem

I have a custom field ‘industries’ on companies. It is a “select multiple” type and contains values like “Consumer Goods”, “Government”, “Utilities” etc.

If I run this sql query:

select leads.id,copper_id, email,industries from leads,companies where leads.company = companies.companyname and (industries like '%Govern%' or industries like '%Utilit%') and email is not null;

I get 4000 leads.

If i create a segment with a filter:

“Company industries including Government, Utiltiies” i get ~500 contacts.
If i create a segment with a filter:
“Company industries including Government OR Company industries including Utilities” I get ~1600 contacts.

In all cases I am missing most.

When i run

/app/bin/mautic mautic:segments:update -i 210096 -vvv -b 4000

i don’t see any debug or trace or verbosity.

So.
a) how do i debug this?
b) what is wrong?
c) how can i work around it?

2022-01-22_22-30-screenshot
2022-01-22_22-30-screenshot1176×193 7.56 KB

2022-01-22_22-31-screenshot
2022-01-22_22-31-screenshot1196×258 10.3 KB

2

I’ve done some more diagnostics.

It appears that, for some leads, even tho the company name is correct, they are not associated.

When i Open companies/view/####, I see ‘0 contacts’.
When I compare the lead, i see ‘company’ field == ‘companyname’ of #####.

How is this association made? My sql query works showing the join is correct, but there must be some xref table managed by a cron?

3

ok i think i see the issue.

the API has a add-contact-to-company (and remove).
when you ‘create’ a contact, if the company name matches, it gets added.
if you update the company name, it doesn’t get added.

there is no API call to see which companies you are part of , meaning we cannot reconcile via API properly.

so i think there are a couple of flaws here:

  • update contact, change company name → doesn’t associate
  • no means of finding which companies a contact is associated with (can’t reconcile manually)
4

Hi, when you uploaded the original list - did you use company name as a key?
The person who has company name as primary company will be connected to the company with THAT name. And this company should have the rest of the info.

5

a ‘fix’:
begin;
delete from companies_leads;
insert into companies_leads select companies.id as company_id, leads.id as lead_id, now() as date_added, 1 as is_primary from leads, companies where leads.company = companies.companyname;
commit;

6

Can you explain why this solves the sutiation?
Thank you.

7

I am bidirectionally syncing my CRM (copper).

When i ‘create’ a user in mautic, i post something like:
companies: companyname, email_domain
leads:
First,Last,email,company

this associates the user to the company.

Later, my team correct ‘Company’ in Copper. Maybe the name was mispelled.
The sync will now update mautic company, and then update the lead, posting it with the corrected company name. This does not associate the user to the company.

Alternatively, i might create the user and then create the company. This does not associate the user.

it seems the user is associated only once, on create, if the company exists w/ match.

Failing that, once must manually associate.
But the api seems to give no way to see who is associated.

the above SQL is a cheat-mode, it takes the company field from the lead, assocaites as the sole company.

8

Okay cool thank you! One more question: did you push the contact in via API or another method?

9

its all via api