How does user interaction with social buttons affect the conversion rate?

NEW documentation: This article covers setup for OWOX BI. For the latest guides on setting up FREE connectors and Data Marts, visit the new OWOX Data Marts documentation.

The query shows average conversion rate for users that had at least one social interaction on a site.

Dynamic parameters

• dataSetName — name of Google BigQuery dataset with Google Analytics sessions data,
• tableName — name of a table with the data,
• startDate — start date of the analyzed period,
• endDate — end date of the analyzed period.

Visualization example

SQL Query

SELECT
  table1.sessionsWithSI / table1.transactionsWithSI AS conversionSI,
  table2.sessionsNoSI / table2.transactionsNoSI AS conversionNoSI
FROM (
  SELECT
    tab1.socialActions AS withSI,
    tab1.sessions AS sessionsWithSI,
    tab2.transactions AS transactionsWithSI
  FROM (
    SELECT
      1 AS socialActions,
      count (sessionId) sessions
    FROM
      TABLE_DATE_RANGE({dataSetName default="Streaming" type="input"}.{tableName default="session_streaming_" type="input"}, TIMESTAMP('{startDate default="2016-08-10" type="datetime"}'), TIMESTAMP('{endDate default="2016-08-13" type="datetime"}'))
    WHERE
      hits.social.socialInteractionAction IS NOT NULL) AS tab1
  JOIN (
    SELECT
      1 AS withTransctions,
      count (t2.transactions) AS transactions
    FROM (
      SELECT
        clientId
      FROM
        TABLE_DATE_RANGE({dataSetName}.{tableName}, TIMESTAMP('{startDate}'), TIMESTAMP('{endDate}'))
      WHERE
        hits.social.socialInteractionAction IS NOT NULL) AS t1
    JOIN (
      SELECT
        clientId,
        count (hits.transaction.transactionId) AS transactions
      FROM
        TABLE_DATE_RANGE({dataSetName}.{tableName}, TIMESTAMP('{startDate}'), TIMESTAMP('{endDate}'))
      WHERE
        hits.transaction.transactionId IS NOT NULL
      GROUP BY
        clientId) AS t2
    ON
      t1.clientId=t2.clientId) AS tab2
  ON
    tab1.socialActions=tab2.withTransctions
  GROUP BY
    withSI,
    sessionsWithSI,
    transactionsWithSI) AS table1
JOIN (
  SELECT
    tab1.noSI AS noSI,
    tab1.sessions AS sessionsNoSI,
    tab2.transactions AS transactionsNoSI
  FROM (
    SELECT
      1 AS noSI,
      count (sessionId) sessions
    FROM
      TABLE_DATE_RANGE({dataSetName}.{tableName}, TIMESTAMP('{startDate}'), TIMESTAMP('{endDate}'))
    WHERE
      hits.social.socialInteractionAction IS NULL) AS tab1
  JOIN (
    SELECT
      1 AS noSI,
      count (t2.transactions) AS transactions
    FROM (
      SELECT
        clientId
      FROM
        TABLE_DATE_RANGE({dataSetName}.{tableName}, TIMESTAMP('{startDate}'), TIMESTAMP('{endDate}'))
      WHERE
        hits.social.socialInteractionAction IS NULL) AS t1
    JOIN (
      SELECT
        clientId,
        count (hits.transaction.transactionId) AS transactions
      FROM
        TABLE_DATE_RANGE({dataSetName}.{tableName}, TIMESTAMP('{startDate}'), TIMESTAMP('{endDate}'))
      WHERE
        hits.transaction.transactionId IS NOT NULL
      GROUP BY
        clientId) AS t2
    ON
      t1.clientId=t2.clientId) AS tab2
  ON
    tab1.noSI=tab2.noSI
  GROUP BY
    noSI,
    sessionsNoSI,
    transactionsNoSI) AS table2
ON
  table1.withSI=table2.noSI
GROUP BY
  conversionSI,
  conversionNoSI