0

I don't know too much about SQL and REGEX, especially how they work together. But I've become responsible for using Chartio to visualise data at work and need some help.

In Google Analytics, under Search Terms we capture a date range. When I pull that into Chartio it's a string and unclean, which is almost unusable.

A few examples of how it appears in Google Analytics.

2018-01-08T12:00:00.000Z
2018-01-28T00:00:00.000Z
12-31-2018
Auckland
Christchurch

In Chartio I can create a Data Store where I take the data from Google Analytics and can manipulate it.

I can create a custom column in the schema to convert the string into a Date using this command, as suggested by Chartio here

CAST("Dates"."ga:searchKeyword" as date)

But I need to clean the data first so that I only valid dates. My poor attempt at creating a command looks like this

CASE WHEN REGEXP_SUBSTR("(19|20)\d\d[-/.](0[1-9]|1[012])[-/.](0[1-9]|[12][0-9]|3[01])") THEN CAST("Dates"."ga:searchKeyword") AS DATE

I know my attempt is wrong, because it doesn't work and also I don't know what I am doing.

Please help!

Steven
  • 3,526
  • 3
  • 18
  • 28
Jason
  • 1
  • What flavor of SQL are you using? REGEX isn't supported in all of them... – Zac Faragher Jan 05 '18 at 01:25
  • I wouldn't even know, I can't tell the difference. On Chartio is just has a field for SQL and doesn't give me any information I can see on what type. – Jason Jan 05 '18 at 02:25
  • How would we parse "Auckland" to a date? Why does such a value appear in a date column? – Robert Synoradzki Jan 05 '18 at 08:47
  • Because Google Analytics populates search terms from URL query parameters, which we leverage for more than just dates. So we need to do this to clean the data, then convert the string to date type for a specific report to make it useable. – Jason Jan 07 '18 at 19:30

0 Answers0