I have two data sets, one that has multiple words (tags) in the same cell / string and other with the catalogue of tags. I'm trying to calculate the occurrence of each tag in all the strings. Example:
Dataset 1 (Catalogue):
| Lvl 1 Tags | Lvl 2 Tags | Lvl 3 Tags |
|---|---|---|
| 101 Problema | 200 WhatsApp Web | 3035 Lentidão |
| 100 Dúvida | 200 WhatsApp Web | 3024 Histórico |
| 100 Dúvida | 208 WhatsBot | 3006 Configurar |
Dataset 2 (Chat entries with attributed tags):
| Chat ID | Tags |
|---|---|
| 220905338525 | 101 Problema, 200 WhatsApp Web, 3035 Lentidão |
| 220905338527 | 100 Dúvida, 208 WhatsBot, 3006 Configurar |
| 220905338527 | 100 Dúvida, 208 WhatsBot, 3006 Configurar |
Objective in data studio:
| Lvl 1 Tags | Lvl 2 Tags | Lvl 3 Tags | Tag Count DS2 |
|---|---|---|---|
| 101 Problema | 200 WhatsApp Web | 3035 Lentidão | 1 |
| 100 Dúvida | 200 WhatsApp Web | 3024 Histórico | 0 |
| 100 Dúvida | 208 WhatsBot | 3006 Configurar | 2 |
I have combined the datasets but I'm having trouble doing the formula. I tried REGEXP_CONTAINS as follows without success.
Example Data Studio Dashboard with attempts
Attempt 1:
REGEXP_CONTAINS(Lvl 1 Tags,Tags)
Attempt 2:
CASE
WHEN REGEXP_CONTAINS(Lvl 1 Tags,Tags) THEN 1
ELSE 0
END
Attempt 3:
REGEXP_CONTAINS(Tags,'100 Dúvida')
Note: Attempt 3 seems not viable since I have 600+ tags to analyse, creating a field for each would not be possible.
Note 2: I'm trying to calculate:
- How many times each of the unique tags presented on the catalogue were used in the Dataset 2; Answer:
| Lvl 1 Tags | Tag Count |
|---|---|
| 101 Problema | 1 |
| 100 Dúvida | 2 |
| Lvl 2 Tags | Tag Count |
|---|---|
| 200 WhatsApp Web | 1 |
| 208 WhatsBot | 2 |
| 242 Relatórios | 0 |
| Lvl 3 Tags | Tag Count |
|---|---|
| 3035 Lentidão | 1 |
| 3024 Histórico | 0 |
| 3006 Configurar | 2 |
- How many times the combination of level 1 and 2 tags were used
| Lvl 1 Tags | Lvl 2 Tags | Tag Count DS2 |
|---|---|---|
| 101 Problema | 200 WhatsApp Web | 1 |
| 100 Dúvida | 242 Relatórios | 0 |
| 100 Dúvida | 208 WhatsBot | 2 |
- how many times de combination of level 1, 2 and 3 tags were used
| Lvl 1 Tags | Lvl 2 Tags | Lvl 3 Tags | Tag Count DS2 |
|---|---|---|---|
| 101 Problema | 200 WhatsApp Web | 3035 Lentidão | 1 |
| 100 Dúvida | 200 WhatsApp Web | 3024 Histórico | 0 |
| 100 Dúvida | 208 WhatsBot | 3006 Configurar | 2 |
To add more context: Each combination of the 3 levels of tags represent a specif problem in a software which we provide costumer support for. The analyst identifies the problem, atributes the appropriate tags to the conversation, which results in a data base with all the conversations of the specified period (dataset 2). We analyse te number of occurrences of each problem for product feedback.
I managed to do the analysis in Google Sheets with the following formula:
=COUNTIFS(Data!$H:$H;"*"&A4&"*";Data!$H:$H;"*"&B4&"*";Data!$H:$H;"*"&C4&"*")
"Data!$H:$H" has the tag entries in a single string ("101 Problema, 200 WhatsApp Web, 3035 Lentidão)
A4, B4 and C4 have the lvl 1, 2 and 3 tags respectively.
Link to formula in Google Sheets
Is this possible in Data Studio?