Number of unique IDs accumulated

Avatar

Avatar

JonasLang

Avatar

JonasLang

JonasLang

31-07-2019

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

Accepted Solutions (1)

Accepted Solutions (1)

Avatar

Avatar

Andrey_Osadchuk

MVP

Avatar

Andrey_Osadchuk

MVP

Andrey_Osadchuk
MVP

01-08-2019

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).

Answers (8)

Answers (8)

Avatar

Avatar

kumararajak

Avatar

kumararajak

kumararajak

02-08-2019

The excel work will look like this

1805265_pastedImage_1.png

Avatar

Avatar

kumararajak

Avatar

kumararajak

kumararajak

02-08-2019

here the full function to call

1805289_pastedImage_0.png

Avatar

Avatar

JonasLang

Avatar

JonasLang

JonasLang

02-08-2019

Thanks kumararajak​!
I will consider doing it in Excel - I was hoping to have the entire dashboard in Analysis Workspace.

Best regards

Jonas

Avatar

Avatar

kumararajak

Avatar

kumararajak

kumararajak

02-08-2019

Dear Jona,

This is possible with Excel formula. Let me know if you work with excel and need help to sort this.

Regards,

Kumararaja K

Avatar

Avatar

ursboller

MVP

Total Posts

1.0K

Likes

486

Correct Answer

252

Avatar

ursboller

MVP

Total Posts

1.0K

Likes

486

Correct Answer

252
ursboller
MVP

01-08-2019

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

Avatar

Avatar

JonasLang

Avatar

JonasLang

JonasLang

01-08-2019

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

Avatar

Avatar

JonasLang

Avatar

JonasLang

JonasLang

31-07-2019

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

Avatar

Avatar

ursboller

MVP

Total Posts

1.0K

Likes

486

Correct Answer

252

Avatar

ursboller

MVP

Total Posts

1.0K

Likes

486

Correct Answer

252
ursboller
MVP

31-07-2019

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)