Generate Dynamic SPARKLINE Graphs in Google Sheets with SQL Queries in OWOX Reports

You can build reports like the one shown below using the OWOX Reports extension for Google Sheets:

SPARKLINE is a function in Google Sheets that allows you to create miniature graphs within a single cell. In OWOX Reports, you can create and run SQL queries to generate Google Sheets formulas with your data.

This approach simplifies creating reports and analyses, providing business users with ready-made reports featuring graphs that update automatically as the data changes—without manual effort.

NoteConnected Sheets from Google doesn't let you insert formulas directly from SQL queries. OWOX Reports provides more value from your data and enhances your reporting capabilities. Watch the video below.

 

SQL snippet

In this article, we'll use an example report based on public data from the bigquery-public-data project and the ga4_obfuscated_sample_ecommerce dataset.

You can copy this code, create a new Data Mart in OWOX Reports, and run it to see this report in action. 

Example SQL code for this Data Mart

WITH
  dates AS --here you can add start date and end date for period you would like to select data
  (
  SELECT
  FORMAT_DATE('%Y%m%d',DATE('{endDate default="2021-01-30" type="datetime"}')) AS e, --here you can set up end date of period which you would like to see data
  CAST({LookBackWindow default="30" pattern="^[0-9]+$" type="input"} AS INT64) l1, --here you can set up first previous period for data calculations
  ),
  initial_data_last_30_days AS --initial data from GA4 Export for last 30 days
  (
  SELECT
  PARSE_DATE('%Y%m%d',event_date) AS date,
  #Events trafficSource
  (SELECT MAX(IF(ep.key='medium', ep.value.string_value, null)) FROM UNNEST(event_params) AS ep) AS medium,
  (SELECT MAX(IF(ep.key='page_location', ep.value.string_value, null)) FROM UNNEST(event_params) AS ep) AS pageURL,
  i.item_id,
  i.item_name,
  i.item_brand,
  i.item_category,
  IF(event_name='view_item',i.item_id,null) AS item_views,
  IF(event_name='add_to_cart',i.item_id,null) AS item_adds_to_cart,
  IF(event_name='begin_checkout',i.item_id,null) AS item_begin_checkout,
  IF(event_name='purchase',i.item_id,null) AS item_purchase,
  FROM
  dates AS d, 
  `{project default="bigquery-public-data" pattern="^[a-z0-9-_]+$" type="input"}.{dataset default="ga4_obfuscated_sample_ecommerce" pattern="^[a-z0-9-_]+$" type="input"}.events_*`,
  UNNEST(items) AS i
  WHERE 
  _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d',DATE_SUB(PARSE_DATE('%Y%m%d',d.e), INTERVAL l1-1 DAY)) AND d.e
  AND
  event_name IN ('view_item','add_to_cart','begin_checkout','purchase')
  ),
  initial_data_last_60_days AS --initial data from GA4 Export for last 31-60 days
  (
  SELECT
  PARSE_DATE('%Y%m%d',event_date) AS date,
  #Events trafficSource
  (SELECT MAX(IF(ep.key='medium', ep.value.string_value, null)) FROM UNNEST(event_params) AS ep) AS medium,
  (SELECT MAX(IF(ep.key='page_location', ep.value.string_value, null)) FROM UNNEST(event_params) AS ep) AS pageURL,
  i.item_id,
  i.item_name,
  i.item_brand,
  i.item_category,
  IF(event_name='view_item',i.item_id,null) AS item_views,
  IF(event_name='add_to_cart',i.item_id,null) AS item_adds_to_cart,
  IF(event_name='begin_checkout',i.item_id,null) AS item_begin_checkout,
  IF(event_name='purchase',i.item_id,null) AS item_purchase,
  FROM
  dates AS d, 
  `{project default="bigquery-public-data" pattern="^[a-z0-9-_]+$" type="input"}.{dataset default="ga4_obfuscated_sample_ecommerce" pattern="^[a-z0-9-_]+$" type="input"}.events_*`,
  UNNEST(items) AS i
  WHERE 
  _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d',DATE_SUB(PARSE_DATE('%Y%m%d',d.e), INTERVAL l1*2-1 DAY)) AND FORMAT_DATE('%Y%m%d',DATE_SUB(PARSE_DATE('%Y%m%d',d.e), INTERVAL l1 DAY))
  AND
  event_name IN ('view_item','add_to_cart','begin_checkout','purchase')
  ),
  data_for_correction AS --correction of the data that 1 item_id should mutch exactly 1 category, brand, page
  ( 
  SELECT
  *
  FROM
  (
  SELECT
  item_id,
  MAX(IF(RN=1, item_brand, null)) OVER (PARTITION BY item_id) AS item_brand,
  MAX(IF(RN=1, item_category, null)) OVER (PARTITION BY item_id) AS item_category,
  MAX(IF(RN2=1, pageURL, null)) OVER (PARTITION BY item_id) AS pageURL, 
  FROM
  (
  SELECT
  item_id,
  item_brand,
  item_category,
  pageURL,
  ROW_NUMBER() OVER (PARTITION BY item_id ORDER BY item_views DESC) AS rn,
  ROW_NUMBER() OVER (PARTITION BY item_id ORDER BY pageURL DESC, item_adds_to_cart DESC) AS rn2
  FROM
  (
  SELECT
  *
  FROM
  initial_data_last_30_days
  UNION ALL
  SELECT
  *
  FROM
  initial_data_last_60_days
  ) 
  )
  )
  GROUP BY ALL 
  ), 
  agg_data_30_days AS --aggregating data for future operations
  (
  SELECT
  date,
  medium,
  d.pageURL,
  i.item_id,
  item_name,
  d.item_brand,
  d.item_category,
  COUNT(item_views) AS item_views,
  COUNT(item_adds_to_cart) AS item_adds_to_cart,
  COUNT(item_begin_checkout) AS item_begin_checkout,
  COUNT(item_purchase) AS item_purchase
  FROM
  initial_data_last_30_days AS i
  LEFT JOIN
  data_for_correction AS d
ON i.item_id=d.item_id
GROUP BY ALL
),
agg_data_60_days AS --aggregating data for future operations
(
SELECT
date,
medium,
d.pageURL,
i.item_id,
item_name,
d.item_brand,
d.item_category,
COUNT(item_views) AS item_views,
COUNT(item_adds_to_cart) AS item_adds_to_cart,
COUNT(item_begin_checkout) AS item_begin_checkout,
COUNT(item_purchase) AS item_purchase
FROM
initial_data_last_60_days AS i
LEFT JOIN
data_for_correction AS d
ON i.item_id=d.item_id
GROUP BY ALL
), 
create_date_array_30_days AS --generating date array for cases where for certain param was no data
(
SELECT
da AS date,
* EXCEPT(date_array, e, da, l1)
FROM
( 
SELECT
*,
GENERATE_DATE_ARRAY(DATE_SUB(PARSE_DATE('%Y%m%d',d.e), INTERVAL l1-1 DAY), PARSE_DATE('%Y%m%d',d.e), INTERVAL 1 DAY) AS date_array
FROM
(
SELECT DISTINCT
medium,
pageURL,
item_id,
item_name,
item_brand,
item_category
FROM
agg_data_30_days
),
dates AS d
) AS i,
UNNEST(date_array) AS da
), 
create_date_array_60_days AS --generating date array for cases where for certain param was no data
(
SELECT
da AS date,
* EXCEPT(date_array, e, da, l1)
FROM
( 
SELECT
*,
GENERATE_DATE_ARRAY(DATE_SUB(PARSE_DATE('%Y%m%d',d.e), INTERVAL l1*2-1 DAY), DATE_SUB(PARSE_DATE('%Y%m%d',d.e), INTERVAL l1 DAY), INTERVAL 1 DAY) AS date_array
FROM
(
SELECT DISTINCT
medium,
pageURL,
item_id,
item_name,
item_brand,
item_category
FROM
agg_data_30_days
),
dates AS d
) AS i,
UNNEST(date_array) AS da
),
remove_blanks_from_data_30 AS --adding rows for empty data
( 
SELECT
c.*,
s.* EXCEPT(date, medium, pageURL, item_id, item_name, item_brand, item_category), 
FROM
create_date_array_30_days AS c
LEFT JOIN
agg_data_30_days AS s
ON
c.date=s.date
AND
c.pageURL=s.pageURL
AND 
c.medium=s.medium
AND
c.item_id=s.item_id AND c.item_name=s.item_name AND c.item_brand=s.item_brand AND c.item_category=s.item_category 
),
remove_blanks_from_data_60 AS --adding rows for empty data
( 
SELECT
c.*,
s.* EXCEPT(date, medium, pageURL, item_id, item_name, item_brand, item_category), 
FROM
create_date_array_60_days AS c
LEFT JOIN
agg_data_60_days AS s
ON
c.date=s.date
AND
c.pageURL=s.pageURL
AND 
c.medium=s.medium
AND
c.item_id=s.item_id AND c.item_name=s.item_name AND c.item_brand=s.item_brand AND c.item_category=s.item_category 
), 
agg_data_step_2_30_days AS --preaggregation for final data
( 
SELECT
s.date,
s.medium,
s.pageURL,
s.item_name,
s.item_id,
s.item_brand,
s.item_category,
SUM(IFNULL(item_views,0)) AS item_views,
SUM(IFNULL(item_adds_to_cart,0)) AS item_adds_to_cart,
SUM(IFNULL(item_begin_checkout,0)) AS item_begin_checkout,
SUM(IFNULL(item_purchase,0)) AS item_purchase, 
FROM
remove_blanks_from_data_30 AS s
GROUP BY ALL
ORDER BY date ASC, item_purchase DESC
), 
agg_data_step_2_60_days AS --preaggregation for final data
( 
SELECT
s.date,
s.medium,
s.pageURL,
s.item_name,
s.item_id,
s.item_brand,
s.item_category,
SUM(IFNULL(item_views,0)) AS item_views,
SUM(IFNULL(item_adds_to_cart,0)) AS item_adds_to_cart,
SUM(IFNULL(item_begin_checkout,0)) AS item_begin_checkout,
SUM(IFNULL(item_purchase,0)) AS item_purchase, 
FROM
remove_blanks_from_data_60 AS s
GROUP BY ALL
ORDER BY date ASC, item_purchase DESC
),
prefinal_data AS --making data for 31-60 days
(
SELECT
medium,
pageURL,
item_id,
item_name,
item_brand,
item_category,
(SELECT SUM(a) FROM UNNEST(item_views_31_60_days) AS a) AS item_views_31_60_days,
(SELECT SUM(a) FROM UNNEST(item_adds_to_cart_31_60_days) AS a) AS item_adds_to_cart_31_60_days,
(SELECT SUM(a) FROM UNNEST(item_begin_checkout_31_60_days) AS a) AS item_begin_checkout_31_60_days,
(SELECT SUM(a) FROM UNNEST(item_purchase_31_60_days) AS a) AS item_purchase_31_60_days, 
FROM
(
SELECT
medium,
pageURL,
item_id,
item_name,
item_brand,
item_category, 
ARRAY_AGG(item_views) AS item_views_31_60_days,
ARRAY_AGG(item_adds_to_cart) AS item_adds_to_cart_31_60_days,
ARRAY_AGG(item_begin_checkout) AS item_begin_checkout_31_60_days,
ARRAY_AGG(item_purchase) AS item_purchase_31_60_days,
FROM
agg_data_step_2_60_days
GROUP BY ALL 
) 
), 
final_data AS --making of the final data
( 
SELECT
CONCAT('=HYPERLINK("', pageURL,'","', item_name, '")') `Product`,
item_brand `Brand`,
item_category `Category`,

CONCAT('=SPARKLINE({', 
IF(medium LIKE '{medium_1 default="cpc" pattern="^[a-z]+$" type="input"}', item_views_30_days, 0), 
',', IF(medium LIKE '{medium_2 default="organic" pattern="^[a-z]+$" type="input"}', item_views_30_days, 0),
',', IF(medium LIKE '{medium_3 default="referral" pattern="^[a-z]+$" type="input"}', item_views_30_days, 0), 
',', IF(medium NOT LIKE '{medium_1 default="cpc" pattern="^[a-z]+$" type="input"}' 
AND medium NOT LIKE '{medium_2 default="organic" pattern="^[a-z]+$" type="input"}'
AND medium NOT LIKE '{medium_3 default="referral" pattern="^[a-z]+$" type="input"}', item_views_30_days, 0),
'}, {"charttype","bar";"color1", "#ffcd38";"color2", "#4a9fea";"color3", "#fa9b4c";"color4", "silver"})') `Views By Medium`,

item_views_30_days `Views Last 30d`,

CONCAT('=SPARKLINE({', item_views_trends, '}, {"color", "', 
IF( item_views_30_days < item_views_31_60_days, "red", 
IF( item_views_30_days = 0 AND item_views_31_60_days = 0, "WhiteSmoke" ,"green")), '";"linewidth", 2})') `Views Trend`,

item_adds_to_cart_30_days `Adds to Cart Last 30d`,
CONCAT('=SPARKLINE({', item_adds_to_cart_trends, '}, {"color", "', 
IF( item_adds_to_cart_30_days < item_adds_to_cart_31_60_days, "red", IF( item_adds_to_cart_30_days = 0 AND item_adds_to_cart_31_60_days = 0, "WhiteSmoke" ,"green")), 
'";"linewidth", 2})') `Adds To Cart Trend`,

item_purchase_30_days `Purchases Last 30d`,
CONCAT('=SPARKLINE({', item_purchase_trends, '}, {"color", "', 
IF( item_purchase_30_days < item_purchase_31_60_days, "red", 
IF( item_purchase_30_days = 0 AND item_purchase_31_60_days = 0, "WhiteSmoke" ,"green") ), 
'";"linewidth", 2})') `Purchases Trend`,

FROM
(
SELECT
c.medium,
c.pageURL,
c.item_id,
c.item_name,
c.item_brand,
c.item_category,
(SELECT SUM(a) FROM UNNEST(c.item_views_30_days) AS a) AS item_views_30_days,
  (SELECT SUM(a) FROM UNNEST(c.item_adds_to_cart_30_days) AS a) AS item_adds_to_cart_30_days,
  (SELECT SUM(a) FROM UNNEST(c.item_begin_checkout_30_days) AS a) AS item_begin_checkout_30_days,
  (SELECT SUM(a) FROM UNNEST(c.item_purchase_30_days) AS a) AS item_purchase_30_days,
  IFNULL(l.item_views_31_60_days, 0) AS item_views_31_60_days,
  IFNULL(l.item_adds_to_cart_31_60_days, 0) AS item_adds_to_cart_31_60_days,
  IFNULL(l.item_begin_checkout_31_60_days, 0) AS item_begin_checkout_31_60_days,
  IFNULL(l.item_purchase_31_60_days, 0) AS item_purchase_31_60_days,
  c.item_views_trends,
  
  c.item_adds_to_cart_trends,
  c.item_begin_checkout_trends,
  c.item_purchase_trends
  FROM
  (
  SELECT
  medium,
  pageURL,
  item_id,
  item_name,
  item_brand,
  item_category, 
  ARRAY_TO_STRING(ARRAY_AGG(CAST(item_views AS STRING) ORDER BY date ASC),',') AS item_views_trends,
  ARRAY_TO_STRING(ARRAY_AGG(CAST(item_adds_to_cart AS STRING) ORDER BY date ASC),',') AS item_adds_to_cart_trends,
  ARRAY_TO_STRING(ARRAY_AGG(CAST(item_begin_checkout AS STRING) ORDER BY date ASC),',') AS item_begin_checkout_trends,
  ARRAY_TO_STRING(ARRAY_AGG(CAST(item_purchase AS STRING) ORDER BY date ASC),',') AS item_purchase_trends,
  ARRAY_AGG(item_views) AS item_views_30_days,
  ARRAY_AGG(item_adds_to_cart) AS item_adds_to_cart_30_days,
  ARRAY_AGG(item_begin_checkout) AS item_begin_checkout_30_days,
  ARRAY_AGG(item_purchase) AS item_purchase_30_days, 
  FROM 
  agg_data_step_2_30_days
  GROUP BY ALL
  ) AS c
  LEFT JOIN
  prefinal_data AS l
  ON 
  c.medium=l.medium
  AND
  c.pageURL=l.pageURL
  AND 
  c.item_id=l.item_id AND c.item_name=l.item_name AND c.item_brand=l.item_brand AND c.item_category=l.item_category
  ) 
  ) 
  SELECT 
  *
  FROM
  final_data
  ORDER BY 
  `Views Last 30d` DESC

Main SQL Commands for Creating SPARKLINE Formulas

To generate SPARKLINE formulas in SQL, use the following commands and functions:

1. CONCAT: Combines strings into one string.
2. ARRAY_AGG: Collects values into an array.
3. ARRAY_TO_STRING: Converts an array into a string with delimiters.
4. CAST: Converts data types (e.g., from numeric to string).
5. IF: Implements conditional logic.
6. CTE (Common Table Expressions): Allows organizing code into logical blocks for better readability and maintenance.

Step-by-Step Code Analysis

1. Collecting Data into Arrays Using ARRAY_AGG

To collect metric values (such as the number of views) into arrays for each product, use the ARRAY_AGG function:

ARRAY_AGG(item_views) AS item_views_30_days,

This gathers all item_views values over the last 30 days into an array.

2. Converting Arrays to Strings for the SPARKLINE Formula

Convert the arrays into strings for use in the SPARKLINE formula using ARRAY_TO_STRING:

ARRAY_TO_STRING(ARRAY_AGG(CAST(item_views AS STRING) ORDER BY date ASC), ',') AS item_views_trends,

Here:

• ARRAY_AGG with CAST converts numeric values into strings.
• The values are sorted by date.
• They are joined into a comma-separated string.

3. Forming the SPARKLINE Formula Using CONCAT

Create the SPARKLINE formula by combining parts using CONCAT:

CONCAT('=SPARKLINE({', item_views_trends, '}, {"color", "', 
IF( item_views_30_days < item_views_31_60_days, "red", 
IF( item_views_30_days = 0 AND item_views_31_60_days = 0, "WhiteSmoke" ,"green")), '";"linewidth", 2})') `Views Trend`,
item_adds_to_cart_30_days `Adds to Cart Last 30d`

In this part:

• Insert the generated string of values item_views_trends into the formula.
• Configure the sparkline parameters:
  • charttype: Type of graph (in this case, "line").
  • linewidth: Line thickness (2).
  • color: Line color, determined by conditional logic:
    • If item_views_30_days is less than item_views_31_60_days, the color will be "red" (indicating a decrease).
    • If both values are zero, the color will be "WhiteSmoke" (no change).
    • In other cases, the color will be "green" (indicating growth).

4. Other Metrics and Sparklines

Similarly, create formulas for other metrics, such as additions to the cart:

CONCAT('=SPARKLINE({', item_adds_to_cart_trends, '}, {"color", "', 
IF( item_adds_to_cart_30_days < item_adds_to_cart_31_60_days, "red", IF( item_adds_to_cart_30_days = 0 AND item_adds_to_cart_31_60_days = 0, "WhiteSmoke" ,"green")), 
'";"linewidth", 2})') `Adds To Cart Trend`,

5. Preparing the Final Data

In the final SELECT, select all the necessary fields and formulas:

SELECT
  CONCAT('=HYPERLINK("', pageURL, '", "', item_name, '")') AS `Product`,
  item_brand AS `Brand`,
  item_category AS `Category`,
  item_views_30_days AS `Views Last 30d`,
  /* Other metrics and sparklines */
FROM
  final_data
ORDER BY
  `Views Last 30d` DESC

As you can see in the example, you can use other formulas like =HYPERLINK. This formula turns text in a cell into a hyperlink. Here's how it works:

• Syntax: =HYPERLINK("URL", "Link Text").
• In this case, it creates a link to the product page (pageURL) displaying the product name (item_name).

Easy Customization of SQL Code Using Parameters in OWOX Reports

One of the advantages of using OWOX Reports is the ability to easily customize SQL code using the built-in parameter functionality. In the provided SQL code, seven parameters are used, which appear in the extension's sidebar:

• endDate: End date of the analysis period (default "2021-01-30").
• LookBackWindow: Size of the analysis window in days (default "30").
• project: Name of your BigQuery project (default "bigquery-public-data").
• dataset: Name of the dataset (default "ga4_obfuscated_sample_ecommerce").
• medium_1, medium_2, medium_3: Traffic channels for analysis (defaults cpc, organic, referral).

Using these parameters, you can quickly adapt the report to your needs without changing the main SQL code.

Scheduled Data Refreshes in OWOX Reports

To ensure your report is always up-to-date, you need to refresh the data regularly. In OWOX Reports, you can easily set up a schedule for data refreshes. Simply choose the frequency that suits you: hourly, daily, weekly, or monthly. You can specify the exact time and day of the week or month for the updates to run.

This will keep your reports always current, optimize data processing costs by avoiding unnecessary updates, and automate the update process without the need for manual actions.

 

We hope that the provided SQL code examples and the explanation of the algorithm for building reports with SPARKLINE graphs will inspire you to come up with your own ideas and discoveries. Share them in the comments to this article.

If you're not yet using this extension, you can install the OWOX Reports for free from the official Google Workspace Marketplace. No card required.

Install the extension and start improving your reports today!