Mautic segment filters

Product Support
1

Your software
My Mautic version is:4.2.5
My PHP version is:8.0
My Database type and version is:MariaDB

Your problem
My problem is: I want to be able to create a custom segment filter where they can search for a specific page_hit and has an operator for the number of times. such as return a list of contacts that has page_hit = example.com and has a count greater than 2.

2

Hi, this filter is not available in Mautic right now.

3

No it does not, but mautic allows you to add custom filters via core events.

4

This is probably not recommended but this is what I done. But I will share for others that want to try it out.

I create a new filter which is a direct copy of hit_url_count and named it to what I wanted to do.

'hit_url_x_count' => [
                'label'      => 'Visited URL X Times',
                'properties' => ['type' => 'number'],
                'operators'  => $this->typeOperatorProvider->getOperatorsIncluding([
                    OperatorOptions::EQUAL_TO,
                    OperatorOptions::GREATER_THAN,
                    OperatorOptions::LESS_THAN,
                    OperatorOptions::GREATER_THAN_OR_EQUAL,
                    OperatorOptions::LESS_THAN_OR_EQUAL,
                ]),
                'object' => 'lead',
            ],

In the ContactSegmentDictionary I added a new array for my new filter which again is close to the original hit_url_count except instead of field => id i used field => url.

$this->filters['hit_url_x_count']                 = [
            'type'                => ForeignFuncFilterQueryBuilder::getServiceId(),
            'foreign_table'       => 'page_hits',
            'foreign_table_field' => 'lead_id',
            'table'               => 'leads',
            'table_field'         => 'id',
            'func'                => 'count',
            'field'               => 'url',
        ];

When I build my segment using the filter Visited X URL and then I use my new filter and it is working as expected. I can also add Visited URL (date) and filter by date too if needed,

Before I did this I did try to use Visit any URL x times and it did not do what I wanted. (had to try :slight_smile: )

Edit
This is the query it ends up building

SELECT
    COUNT(leadIdPrimary) AS count,
    MAX(leadIdPrimary) AS maxId,
    MIN(leadIdPrimary) AS minId
FROM (
    SELECT DISTINCT
        l.id AS leadIdPrimary
    FROM
        leads l
    WHERE
        (
            l.id IN (
                SELECT par1.lead_id
                FROM page_hits par1
                WHERE par1.url = 'url here'
                            )
        )
        AND (
            EXISTS (
                SELECT COUNT(DISTINCT par3.url)
                FROM page_hits par3
                WHERE l.id = par3.lead_id
                HAVING COUNT(DISTINCT par3.url) >= 1
            )
        )
        AND (
            l.id IN (
                SELECT par5.lead_id
                FROM page_hits par5
                WHERE par5.date_hit >= "2024-01-31 15:00"
            )
        )
        AND (
            l.id NOT IN (
                SELECT par6.lead_id
                FROM lead_lists_leads par6
                WHERE par6.leadlist_id = 3735
            )
        )
) sss;

Excuse the hardcoded parameters it was part of my testing