Does anyone know how to convert Google Analytics "Date Hour and Minute"?

Canioagain
Canioagain Contributor
edited February 2022 in Magic ETL

Does anyone have a solution they are using in a Magic ETL to easily convert Google Analytic's Date Hour and Minute output into something resembling an actual date time?

datetime googlle.png

?

Tagged:

Best Answer

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    You can use a formula tile to splice out the different parts and assemble it as a timestamp:

    TIMESTAMP(CONCAT (
  LEFT(`Date Hour and Minute`, 4), '-',
  SUBSTRING(`Date Hour and Minute`, 5, 2), '-',
  SUBSTRING(`Date Hour and Minute`, 7, 2), ' ',
  SUBSTRING(`Date Hour and Minute`, 9, 2), ':',
  SUBSTRING(`Date Hour and Minute`, 1, 2)))


    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Answers

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    You can use a formula tile to splice out the different parts and assemble it as a timestamp:

    TIMESTAMP(CONCAT (
  LEFT(`Date Hour and Minute`, 4), '-',
  SUBSTRING(`Date Hour and Minute`, 5, 2), '-',
  SUBSTRING(`Date Hour and Minute`, 7, 2), ' ',
  SUBSTRING(`Date Hour and Minute`, 9, 2), ':',
  SUBSTRING(`Date Hour and Minute`, 1, 2)))


    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • Canioagain
    Canioagain Contributor

    @GrantSmith Awesome!

Categories