Customer segments aren't the most straight forward concept. It should be and works fine but the back-end handling seems to be a set of different solutions put together to make the whole.
First of we have the customer segments created as member groups. These groups have names and some small settings but only a few is saved in the actual mbrgrp tables. We have the old functionality from the Accelerator saved in mbrgrp tables like mbrgrpcond where you save a configuration for the member group. A set of conditions that needs to be met in order to put a member into a member group. And you also have the possibility to put someone implicitly into an member group or exclude them from a member group. The inclusions or exclusions is done in the mbrgrpmbr table.
The priority of these functions is exclusions then inclusions and lastly the condition configuration is validated.
These three functionalities works fine and has presentations inside of the Management Center so you can see who is in a member group or what conditions needs to be met in order for someone to be put into this member group.
Then we come to the disconnect. When you choose the checkbox to add or remove customers to a segment using marketing activities then the condition configuration signals that the data for the customers inside a segment will be saved in a separate table.
select cast(conditions as varchar(20000)) from mbrgrpcond where mbrgrp_id = ?;
<?xml version="1.0" encoding="UTF-8"?> <profile> <simpleCondition> <variable name="marketingPopulates"/> <operator name="="/> <value data="T"/> </simpleCondition> </profile>
So when a customer is added this way then the system will add the PERSONALIZATIONID of the current session into the DMMBRGRPPZN table.
This means that in that setup the priority will change to exclusions then inclusions and lastly the users added via a marketing activity.
Another thing to keep in mind is that the first tables all use member ID as there reference for inclusion and exclusion but the last solution in the DMMBRGRPPZN uses PERSONALIZATIONID. This is because marketing activities could target users who isn't logged in to the commerce site, and a PERSONALIZATIONID is saved in the session and could therefor be targeted against guest users as well as registered customers. The PERSONALIZATIONID is saved in the users table for the registered users so you could find the connection to an member groups registered members by using
select * from USERS as u, DMMBRGRPPZN as mgp where u.personalizationid = mgp.personalizationid and mgp.mbrgrp_id = ?;