Level 2

Solved

Number of unique IDs accumulated

Forum|Forum|6 years ago
July 31, 2019
9 replies
5922 views

Dear Community

I was asked to deliver the number of unique customer IDs year to date split by month, and I cannot myself figure out whether it is possible to calculate this number.

Example:

In January 2019 I had the following customer IDs visiting my website:

AB12

AC13

AD14

This give me 3 unique customers that month.

In February 2019 I had the following customer IDs visiting my website:

AB12

BA21

This give me 2 unique customers that month.

In March 2019 I had the following customer IDs visiting my website:

AB12

AC13

CA31

This give me 3 unique customers that month.

I am then asked to deliver a table that looks like this:

January 2019 3 unique customers

February 2019 4 unique customers

March 2019 5 unique customers

I am using the APPROXIMATE DISTINCT COUNT within Calculated Metrics to find the number of unique customer IDs in total but it does not work with the CUMULATIVE function to split it per month.

Any suggestions on how to solve this?

Thanks in advance!

Best regards

Jonas

Analytics

This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.

Best answer by Andrey_Osadchuk

Hi Jonas,

I think what you may want to do to get the desired result is to update the date ranges as follows:

Jan 2019
Jan-Feb 2019
Jan-Mar 2019

This would help you display cumulative number of visitors to a certain date (month).

Urs_Boller

Community Advisor

I would use the same function "approx distinct count" as well, it should work even with a month breakdown. maybe you need to count "minus 1" since it will count all "undefined" (users wothout customer id) as a new dimension item (and therefore adding an additional item to the count)

JonasLangAuthor

Level 2

Hi Urs

It is not working for me - If I do that my table will look like this:

January 2019 3 unique customers

February 2019 2 unique customers

March 2019 3 unique customers

I want an accumulated table where it is only adding new customers. I have added the time intervals that each month should be covering to explain it a bit further:

January 2019 (1/1-19 - 31/1-19) 3 unique customers

February 2019 (1/1-19 - 28/2-19) 4 unique customers

March 2019 (1/1-19 - 31/3-19) 5 unique customers

Best regards

Jonas

Andrey_OsadchukAccepted solution

Level 10

Hi Jonas,

I think what you may want to do to get the desired result is to update the date ranges as follows:

Jan 2019
Jan-Feb 2019
Jan-Mar 2019

This would help you display cumulative number of visitors to a certain date (month).

JonasLangAuthor

Level 2

Hi Andrey

I was hoping that it could be done in a "clever" way. Potentially we want to break it down by week or day as well. It is a lot of custom date ranges to create.

Best regards

Jonas

Urs_Boller

Community Advisor

I don't know of any fancy way to get de-duplicated cumulative sums of the distinct counts. means you either need to go with the solution described by Andrey Osadchuk or forget the idea to have an easy solution... sorry