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

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