Hitachi Vantara Pentaho Community Forums
Results 1 to 3 of 3

Thread: Dynamic start / end dates for Google Analytics step

  1. #1
    Join Date
    May 2014
    Posts
    4

    Default Dynamic start / end dates for Google Analytics step

    Hi everyone

    I really need some help with this.
    I've been all over the Internet (these forums, Stack Exchange, etc) and have seen some examples, but I still can't get it to work.

    I have a GA step all working fine, but only when I setup the Start and End dates as hardcoded values.

    But I've been tearing my hair out trying to pass dynamic dates to it, such as using ${STARTDATE} and ${ENDDATE} etc in the GA step.

    My problem is how to pass these dates from a prior step to the GA step?

    Even some of the examples that people have so generously provided don't work (for me).

    I've tried:
    Get System Info | Get Variables | Format Dates | Google Analytics


    Here's the particular line in the step logging:

    2014/12/19 18:01:31 - Google Analytics.0 - <?xml version="1.0" encoding="UTF-8"?><errors xmlns="http://schemas.google.com/g/2005"><error><domain>GData</domain><code>invalidParameter</code><location type="parameter">end-date</location><internalReason>Invalid value ''. Values must match the following regular expression: '[0-9]{4}-[0-9]{2}-[0-9]{2}'</internalReason></error><error><domain>GData</domain><code>invalidParameter</code><location type="parameter">start-date</location><internalReason>Invalid value ''. Values must match the following regular expression: '[0-9]{4}-[0-9]{2}-[0-9]{2}'</internalReason></error></errors>

    Included here is my transformation (sorry the attach files thing doesn't work for me)


    Code:
    <?xml version="1.0" encoding="UTF-8"?><transformation>
      <info>
        <name>Google Analytics</name>
        <description/>
        <extended_description/>
        <trans_version/>
        <trans_type>Normal</trans_type>
        <trans_status>0</trans_status>
        <directory>&#x2f;</directory>
        <parameters>
        </parameters>
        <log>
    <trans-log-table><connection/>
    <schema/>
    <table/>
    <size_limit_lines/>
    <interval/>
    <timeout_days/>
    <field><id>ID_BATCH</id><enabled>Y</enabled><name>ID_BATCH</name></field><field><id>CHANNEL_ID</id><enabled>Y</enabled><name>CHANNEL_ID</name></field><field><id>TRANSNAME</id><enabled>Y</enabled><name>TRANSNAME</name></field><field><id>STATUS</id><enabled>Y</enabled><name>STATUS</name></field><field><id>LINES_READ</id><enabled>Y</enabled><name>LINES_READ</name><subject/></field><field><id>LINES_WRITTEN</id><enabled>Y</enabled><name>LINES_WRITTEN</name><subject/></field><field><id>LINES_UPDATED</id><enabled>Y</enabled><name>LINES_UPDATED</name><subject/></field><field><id>LINES_INPUT</id><enabled>Y</enabled><name>LINES_INPUT</name><subject/></field><field><id>LINES_OUTPUT</id><enabled>Y</enabled><name>LINES_OUTPUT</name><subject/></field><field><id>LINES_REJECTED</id><enabled>Y</enabled><name>LINES_REJECTED</name><subject/></field><field><id>ERRORS</id><enabled>Y</enabled><name>ERRORS</name></field><field><id>STARTDATE</id><enabled>Y</enabled><name>STARTDATE</name></field><field><id>ENDDATE</id><enabled>Y</enabled><name>ENDDATE</name></field><field><id>LOGDATE</id><enabled>Y</enabled><name>LOGDATE</name></field><field><id>DEPDATE</id><enabled>Y</enabled><name>DEPDATE</name></field><field><id>REPLAYDATE</id><enabled>Y</enabled><name>REPLAYDATE</name></field><field><id>LOG_FIELD</id><enabled>Y</enabled><name>LOG_FIELD</name></field><field><id>EXECUTING_SERVER</id><enabled>N</enabled><name>EXECUTING_SERVER</name></field><field><id>EXECUTING_USER</id><enabled>N</enabled><name>EXECUTING_USER</name></field><field><id>CLIENT</id><enabled>N</enabled><name>CLIENT</name></field></trans-log-table>
    <perf-log-table><connection/>
    <schema/>
    <table/>
    <interval/>
    <timeout_days/>
    <field><id>ID_BATCH</id><enabled>Y</enabled><name>ID_BATCH</name></field><field><id>SEQ_NR</id><enabled>Y</enabled><name>SEQ_NR</name></field><field><id>LOGDATE</id><enabled>Y</enabled><name>LOGDATE</name></field><field><id>TRANSNAME</id><enabled>Y</enabled><name>TRANSNAME</name></field><field><id>STEPNAME</id><enabled>Y</enabled><name>STEPNAME</name></field><field><id>STEP_COPY</id><enabled>Y</enabled><name>STEP_COPY</name></field><field><id>LINES_READ</id><enabled>Y</enabled><name>LINES_READ</name></field><field><id>LINES_WRITTEN</id><enabled>Y</enabled><name>LINES_WRITTEN</name></field><field><id>LINES_UPDATED</id><enabled>Y</enabled><name>LINES_UPDATED</name></field><field><id>LINES_INPUT</id><enabled>Y</enabled><name>LINES_INPUT</name></field><field><id>LINES_OUTPUT</id><enabled>Y</enabled><name>LINES_OUTPUT</name></field><field><id>LINES_REJECTED</id><enabled>Y</enabled><name>LINES_REJECTED</name></field><field><id>ERRORS</id><enabled>Y</enabled><name>ERRORS</name></field><field><id>INPUT_BUFFER_ROWS</id><enabled>Y</enabled><name>INPUT_BUFFER_ROWS</name></field><field><id>OUTPUT_BUFFER_ROWS</id><enabled>Y</enabled><name>OUTPUT_BUFFER_ROWS</name></field></perf-log-table>
    <channel-log-table><connection/>
    <schema/>
    <table/>
    <timeout_days/>
    <field><id>ID_BATCH</id><enabled>Y</enabled><name>ID_BATCH</name></field><field><id>CHANNEL_ID</id><enabled>Y</enabled><name>CHANNEL_ID</name></field><field><id>LOG_DATE</id><enabled>Y</enabled><name>LOG_DATE</name></field><field><id>LOGGING_OBJECT_TYPE</id><enabled>Y</enabled><name>LOGGING_OBJECT_TYPE</name></field><field><id>OBJECT_NAME</id><enabled>Y</enabled><name>OBJECT_NAME</name></field><field><id>OBJECT_COPY</id><enabled>Y</enabled><name>OBJECT_COPY</name></field><field><id>REPOSITORY_DIRECTORY</id><enabled>Y</enabled><name>REPOSITORY_DIRECTORY</name></field><field><id>FILENAME</id><enabled>Y</enabled><name>FILENAME</name></field><field><id>OBJECT_ID</id><enabled>Y</enabled><name>OBJECT_ID</name></field><field><id>OBJECT_REVISION</id><enabled>Y</enabled><name>OBJECT_REVISION</name></field><field><id>PARENT_CHANNEL_ID</id><enabled>Y</enabled><name>PARENT_CHANNEL_ID</name></field><field><id>ROOT_CHANNEL_ID</id><enabled>Y</enabled><name>ROOT_CHANNEL_ID</name></field></channel-log-table>
    <step-log-table><connection/>
    <schema/>
    <table/>
    <timeout_days/>
    <field><id>ID_BATCH</id><enabled>Y</enabled><name>ID_BATCH</name></field><field><id>CHANNEL_ID</id><enabled>Y</enabled><name>CHANNEL_ID</name></field><field><id>LOG_DATE</id><enabled>Y</enabled><name>LOG_DATE</name></field><field><id>TRANSNAME</id><enabled>Y</enabled><name>TRANSNAME</name></field><field><id>STEPNAME</id><enabled>Y</enabled><name>STEPNAME</name></field><field><id>STEP_COPY</id><enabled>Y</enabled><name>STEP_COPY</name></field><field><id>LINES_READ</id><enabled>Y</enabled><name>LINES_READ</name></field><field><id>LINES_WRITTEN</id><enabled>Y</enabled><name>LINES_WRITTEN</name></field><field><id>LINES_UPDATED</id><enabled>Y</enabled><name>LINES_UPDATED</name></field><field><id>LINES_INPUT</id><enabled>Y</enabled><name>LINES_INPUT</name></field><field><id>LINES_OUTPUT</id><enabled>Y</enabled><name>LINES_OUTPUT</name></field><field><id>LINES_REJECTED</id><enabled>Y</enabled><name>LINES_REJECTED</name></field><field><id>ERRORS</id><enabled>Y</enabled><name>ERRORS</name></field><field><id>LOG_FIELD</id><enabled>N</enabled><name>LOG_FIELD</name></field></step-log-table>
    <metrics-log-table><connection/>
    <schema/>
    <table/>
    <timeout_days/>
    <field><id>ID_BATCH</id><enabled>Y</enabled><name>ID_BATCH</name></field><field><id>CHANNEL_ID</id><enabled>Y</enabled><name>CHANNEL_ID</name></field><field><id>LOG_DATE</id><enabled>Y</enabled><name>LOG_DATE</name></field><field><id>METRICS_DATE</id><enabled>Y</enabled><name>METRICS_DATE</name></field><field><id>METRICS_CODE</id><enabled>Y</enabled><name>METRICS_CODE</name></field><field><id>METRICS_DESCRIPTION</id><enabled>Y</enabled><name>METRICS_DESCRIPTION</name></field><field><id>METRICS_SUBJECT</id><enabled>Y</enabled><name>METRICS_SUBJECT</name></field><field><id>METRICS_TYPE</id><enabled>Y</enabled><name>METRICS_TYPE</name></field><field><id>METRICS_VALUE</id><enabled>Y</enabled><name>METRICS_VALUE</name></field></metrics-log-table>
        </log>
        <maxdate>
          <connection/>
          <table/>
          <field/>
          <offset>0.0</offset>
          <maxdiff>0.0</maxdiff>
        </maxdate>
        <size_rowset>10000</size_rowset>
        <sleep_time_empty>50</sleep_time_empty>
        <sleep_time_full>50</sleep_time_full>
        <unique_connections>N</unique_connections>
        <feedback_shown>Y</feedback_shown>
        <feedback_size>50000</feedback_size>
        <using_thread_priorities>Y</using_thread_priorities>
        <shared_objects_file/>
        <capture_step_performance>N</capture_step_performance>
        <step_performance_capturing_delay>1000</step_performance_capturing_delay>
        <step_performance_capturing_size_limit>100</step_performance_capturing_size_limit>
        <dependencies>
        </dependencies>
        <partitionschemas>
        </partitionschemas>
        <slaveservers>
        </slaveservers>
        <clusterschemas>
        </clusterschemas>
      <created_user>-</created_user>
      <created_date>2014&#x2f;12&#x2f;16 09&#x3a;41&#x3a;58.452</created_date>
      <modified_user>-</modified_user>
      <modified_date>2014&#x2f;12&#x2f;16 15&#x3a;23&#x3a;04.958</modified_date>
      </info>
      <notepads>
      </notepads>
      <order>
      <hop> <from>Get System Info</from><to>Get Variables</to><enabled>Y</enabled> </hop>
      <hop> <from>Get Variables</from><to>format dates</to><enabled>Y</enabled> </hop>
      <hop> <from>format dates</from><to>Google Analytics</to><enabled>Y</enabled> </hop>
      </order>
      <step>
        <name>Get System Info</name>
        <type>SystemInfo</type>
        <description/>
        <distribute>N</distribute>
        <custom_distribution/>
        <copies>1</copies>
             <partitioning>
               <method>none</method>
               <schema_name/>
               </partitioning>
        <fields>
          <field>
            <name>STARTDATE</name>
            <type>yesterday start</type>
            </field>
          <field>
            <name>ENDDATE</name>
            <type>today start</type>
            </field>
          </fields>
         <cluster_schema/>
     <remotesteps>   <input>   </input>   <output>   </output> </remotesteps>    <GUI>
          <xloc>94</xloc>
          <yloc>112</yloc>
          <draw>Y</draw>
          </GUI>
        </step>
    
    
      <step>
        <name>Get Variables</name>
        <type>GetVariable</type>
        <description/>
        <distribute>N</distribute>
        <custom_distribution/>
        <copies>1</copies>
             <partitioning>
               <method>none</method>
               <schema_name/>
               </partitioning>
        <fields>
          <field>
            <name>STARTDATE</name>
            <variable>&#x24;&#x7b;STARTDATE&#x7d;</variable>
            <type>String</type>
            <format>yyyy-MM-dd</format>
            <currency/>
            <decimal/>
            <group/>
            <length>10</length>
            <precision>-1</precision>
            <trim_type>none</trim_type>
          </field>
          <field>
            <name>ENDDATE</name>
            <variable>&#x24;&#x7b;ENDDATE&#x7d;</variable>
            <type>String</type>
            <format>yyyy-MM-dd</format>
            <currency/>
            <decimal/>
            <group/>
            <length>10</length>
            <precision>-1</precision>
            <trim_type>none</trim_type>
          </field>
        </fields>
         <cluster_schema/>
     <remotesteps>   <input>   </input>   <output>   </output> </remotesteps>    <GUI>
          <xloc>258</xloc>
          <yloc>67</yloc>
          <draw>Y</draw>
          </GUI>
        </step>
    
    
      <step>
        <name>Google Analytics</name>
        <type>TypeExitGoogleAnalyticsInputStep</type>
        <description/>
        <distribute>Y</distribute>
        <custom_distribution/>
        <copies>1</copies>
             <partitioning>
               <method>none</method>
               <schema_name/>
               </partitioning>
        <user>xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx</user>
        <pass>Encrypted 2be98afc86aa7f287aa0aa6238dc1fc89</pass>
        <appName>type-exit.org kettle plugin</appName>
        <apiKey>Encrypted 41497a615379444a50462d4c75363557564f57786c5846eccc9a9e1ef29790f232e33d8b9e9dff</apiKey>
        <profileName>ga&#x3a;xxxxx - profile&#x3a; All Mobile App Data</profileName>
        <profileTableId>ga&#x3a;xxxxx</profileTableId>
        <customTableId/>
        <useCustomTableId>N</useCustomTableId>
        <startDate>&#x24;&#x7b;STARTDATE&#x7d;</startDate>
        <endDate>&#x24;&#x7b;ENDDATE&#x7d;</endDate>
        <dimensions>ga&#x3a;screenName</dimensions>
        <metrics>ga&#x3a;sessions</metrics>
        <filters/>
        <sort/>
        <useSegment>Y</useSegment>
        <useCustomSegment>N</useCustomSegment>
        <customSegment/>
        <segmentId>gaid&#x3a;&#x3a;-1</segmentId>
        <segmentName>All Visits</segmentName>
        <rowLimit>0</rowLimit>
          <feedField>
            <feedFieldType>Dimension</feedFieldType>
            <feedField>ga&#x3a;screenName</feedField>
            <outField>ga&#x3a;screenName</outField>
            <type>String</type>
            <conversionMask/>
          </feedField>
          <feedField>
            <feedFieldType>Metric</feedFieldType>
            <feedField>ga&#x3a;sessions</feedField>
            <outField>ga&#x3a;sessions</outField>
            <type>Integer</type>
            <conversionMask>&#x23;&#x3b;-&#x23;</conversionMask>
          </feedField>
         <cluster_schema/>
     <remotesteps>   <input>   </input>   <output>   </output> </remotesteps>    <GUI>
          <xloc>632</xloc>
          <yloc>192</yloc>
          <draw>Y</draw>
          </GUI>
        </step>
    
    
      <step>
        <name>format dates</name>
        <type>SelectValues</type>
        <description/>
        <distribute>N</distribute>
        <custom_distribution/>
        <copies>1</copies>
             <partitioning>
               <method>none</method>
               <schema_name/>
               </partitioning>
        <fields>      <field>        <name>STARTDATE</name>
            <rename/>
            <length>10</length>
            <precision>-2</precision>
          </field>      <field>        <name>ENDDATE</name>
            <rename/>
            <length>10</length>
            <precision>-2</precision>
          </field>        <select_unspecified>N</select_unspecified>
          <meta>        <name>STARTDATE</name>
            <rename>STARTDATE</rename>
            <type>String</type>
            <length>10</length>
            <precision>-2</precision>
            <conversion_mask>yyyy-MM-dd</conversion_mask>
            <date_format_lenient>false</date_format_lenient>
            <date_format_locale/>
            <date_format_timezone/>
            <lenient_string_to_number>false</lenient_string_to_number>
            <encoding/>
            <decimal_symbol/>
            <grouping_symbol/>
            <currency_symbol/>
            <storage_type/>
          </meta>      <meta>        <name>ENDDATE</name>
            <rename>ENDDATE</rename>
            <type>String</type>
            <length>10</length>
            <precision>-2</precision>
            <conversion_mask>yyyy-MM-dd</conversion_mask>
            <date_format_lenient>false</date_format_lenient>
            <date_format_locale/>
            <date_format_timezone/>
            <lenient_string_to_number>false</lenient_string_to_number>
            <encoding/>
            <decimal_symbol/>
            <grouping_symbol/>
            <currency_symbol/>
            <storage_type/>
          </meta>    </fields>     <cluster_schema/>
     <remotesteps>   <input>   </input>   <output>   </output> </remotesteps>    <GUI>
          <xloc>420</xloc>
          <yloc>116</yloc>
          <draw>Y</draw>
          </GUI>
        </step>
    
    
      <step_error_handling>
      </step_error_handling>
       <slave-step-copy-partition-distribution>
    </slave-step-copy-partition-distribution>
       <slave_transformation>N</slave_transformation>
    
    
    </transformation>
    If you could substitute your credentials for mine, and run it, could you tell me where I'm going wrong?

    Any help would be hugely appreciated.

    Thank you
    Brad
    Last edited by bradzo; 12-19-2014 at 04:24 AM.

  2. #2
    Join Date
    Jun 2012
    Posts
    5,534

    Default

    You must provide your variables outside of your transformation.

    Use a Kettle Job to accomplish this, either by introducing a separate transformation (Get System Info / Select Values - Change Metadata) / Set Variables) or by using a JavaScript job entry.
    So long, and thanks for all the fish.

  3. #3
    Join Date
    May 2014
    Posts
    4

    Default

    Quote Originally Posted by marabu View Post
    You must provide your variables outside of your transformation.

    Use a Kettle Job to accomplish this, either by introducing a separate transformation (Get System Info / Select Values - Change Metadata) / Set Variables) or by using a JavaScript job entry.
    Thank you Marabu - the "Set Variables" step was what I needed.

    So, for others looking for this, here's what I did.

    2 transformations
    1 job

    Transformation 1
    Get System Info > Get Variables > Format Dates > Copy rows to result

    Transformation 2
    Get rows from result > Set Variables > Google Analytics > Text file output (for testing, will output to mySQL for reporting)

    Job 1
    Start > Transformation 1 > Transformation 2 > Success

    Thanks once again
    Brad

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.