How to search ECID creation date

[Mia-jm_Wa]

Hi

We'd like to know the current profiles(have ECID but without OCMDID), how many of them created the profile within October?

What's the query to search

[DavidRoss91]

@Mia-jm_Wa 

You could try adjusting the "Single Identity Metric" that is provided in the Profile Overview tab - I have adjusted here for 10/1 - 10/31

SELECT

qsaccel.profile_agg.adwh_dim_namespaces.namespace_description,

SUM(qsaccel.profile_agg.adwh_fact_profile_by_namespace_trendlines.count_of_Single_Identity_profiles) AS count_of_Single_Identity_profiles

FROM

qsaccel.profile_agg.adwh_fact_profile_by_namespace_trendlines

LEFT OUTER JOIN

qsaccel.profile_agg.adwh_dim_namespaces

ON qsaccel.profile_agg.adwh_fact_profile_by_namespace_trendlines.namespace_id = qsaccel.profile_agg.adwh_dim_namespaces.namespace_id

AND qsaccel.profile_agg.adwh_fact_profile_by_namespace_trendlines.merge_policy_id = qsaccel.profile_agg.adwh_dim_namespaces.merge_policy_id

WHERE

qsaccel.profile_agg.adwh_fact_profile_by_namespace_trendlines.merge_policy_id = 227808421

AND qsaccel.profile_agg.adwh_fact_profile_by_namespace_trendlines.date_key BETWEEN '2024-10-01' AND '2024-10-31'

GROUP BY

qsaccel.profile_agg.adwh_fact_profile_by_namespace_trendlines.merge_policy_id,

qsaccel.profile_agg.adwh_dim_namespaces.namespace_description;

[Mia-jm_Wa]

Thanks for your reply.

but below date_key is the total amount of single profile created by the day,  not the amount only on the day...

qsaccel.profile_agg.adwh_fact_profile_by_namespace_trendlines.date_key BETWEEN '2024-10-01' AND '2024-10-31'

I would like the query to see the counts of profiles that contain only the ECID as an identity and were created in a particular month

[DavidRoss91]

@Mia-jm_Wa Sorry, I'm not sure I understand the difference. This query is pulling in ECID only by the day. But totaling all days in October as you asked. Does this not provide the value you need?

[Mia-jm_Wa]

Hi @DavidRoss91  Sorry that I may not explain myself well. Actually, I wonder the current profiles only have ECID, when are they created, then I need to know the amount of the creation date within Oct. 

Like by Oct1, we can use your sql to get the amount and profiles, but what if the profiles login and stitch with other IDs, then they are not in current profiles only have ECID which I need.