googlecloudplatform / analytics-componentized-patterns Goto Github PK
View Code? Open in Web Editor NEWLicense: Apache License 2.0
License: Apache License 2.0
Hi all,
I'm running the LTV lookalike notebook: https://github.com/GoogleCloudPlatform/analytics-componentized-patterns/blob/master/retail/ltv/bqml/notebooks/bqml_automl_ltv_activate_lookalike.ipynb on a Vertex AI user-managed notebook, but I got the error as written in the title.
I got the error when running the first cell of the Aggregate per day per customer chapter, i.e. the cell starting with this code:
%%bigquery --params $LTV_PARAMS --project $PROJECT_ID
DECLARE MAX_STDV_MONETARY INT64 DEFAULT @MAX_STDV_MONETARY;
DECLARE MAX_STDV_QTY INT64 DEFAULT @MAX_STDV_QTY;
CREATE OR REPLACE TABLE `ltv_ecommerce.20_aggred` AS
SELECT
customer_id,
order_day,
ROUND(day_value_after_returns, 2) AS value,
day_qty_after_returns as qty_articles,
day_num_returns AS num_returns,
CEIL(avg_time_to_return) AS time_to_return
FROM (
.....
Does anyone know how to fix this?
Here is the full error message I got:
Traceback (most recent call last):
File "/opt/conda/lib/python3.7/site-packages/google/cloud/bigquery/magics/magics.py", line 515, in _cell_magic
params_option_value, rest_of_args = _split_args_line(line)
File "/opt/conda/lib/python3.7/site-packages/google/cloud/bigquery/magics/magics.py", line 720, in _split_args_line
tree = scanner.input_line()
File "/opt/conda/lib/python3.7/site-packages/google/cloud/bigquery/magics/line_arg_parser/parser.py", line 203, in input_line
options = self.option_list()
File "/opt/conda/lib/python3.7/site-packages/google/cloud/bigquery/magics/line_arg_parser/parser.py", line 264, in option_list
option = self.params_option()
File "/opt/conda/lib/python3.7/site-packages/google/cloud/bigquery/magics/line_arg_parser/parser.py", line 319, in params_option
opt_value = self.py_dict()
File "/opt/conda/lib/python3.7/site-packages/google/cloud/bigquery/magics/line_arg_parser/parser.py", line 333, in py_dict
dict_items = self.dict_items()
File "/opt/conda/lib/python3.7/site-packages/google/cloud/bigquery/magics/line_arg_parser/parser.py", line 347, in dict_items
item = self.dict_item()
File "/opt/conda/lib/python3.7/site-packages/google/cloud/bigquery/magics/line_arg_parser/parser.py", line 371, in dict_item
value = self.py_value()
File "/opt/conda/lib/python3.7/site-packages/google/cloud/bigquery/magics/line_arg_parser/parser.py", line 423, in py_value
self.error(msg, exc_type=QueryParamsParseError)
File "/opt/conda/lib/python3.7/site-packages/google/cloud/bigquery/magics/line_arg_parser/parser.py", line 193, in error
raise exc_type(message)
QueryParamsParseError: Unexpected token type UNKNOWN at position 27.
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "/opt/conda/lib/python3.7/site-packages/IPython/core/interactiveshell.py", line 3457, in run_code
exec(code_obj, self.user_global_ns, self.user_ns)
File "/tmp/ipykernel_17444/3535280929.py", line 1, in <module>
get_ipython().run_cell_magic('bigquery', '--params $LTV_PARAMS --project $PROJECT_ID', '\nDECLARE MAX_STDV_MONETARY INT64 DEFAULT @MAX_STDV_MONETARY;\nDECLARE MAX_STDV_QTY INT64 DEFAULT @MAX_STDV_QTY;\n\nCREATE OR REPLACE TABLE `ltv_ecommerce.20_aggred` AS\nSELECT\n customer_id,\n order_day,\n ROUND(day_value_after_returns, 2) AS value,\n day_qty_after_returns as qty_articles,\n day_num_returns AS num_returns,\n CEIL(avg_time_to_return) AS time_to_return\nFROM (\n SELECT\n customer_id,\n order_day,\n SUM(order_value_after_returns) AS day_value_after_returns,\n STDDEV(SUM(order_value_after_returns)) OVER(PARTITION BY customer_id ORDER BY SUM(order_value_after_returns)) AS stdv_value,\n SUM(order_qty_after_returns) AS day_qty_after_returns,\n STDDEV(SUM(order_qty_after_returns)) OVER(PARTITION BY customer_id ORDER BY SUM(order_qty_after_returns)) AS stdv_qty,\n CASE\n WHEN MIN(order_min_qty) < 0 THEN count(1)\n ELSE 0\n END AS day_num_returns,\n CASE\n WHEN MIN(order_min_qty) < 0 THEN AVG(time_to_return)\n ELSE NULL\n END AS avg_time_to_return\n FROM (\n SELECT \n customer_id,\n order_id,\n -- Gives the order date vs return(s) dates.\n MIN(transaction_date) AS order_day,\n MAX(transaction_date) AS return_final_day,\n DATE_DIFF(MAX(transaction_date), MIN(transaction_date), DAY) AS time_to_return,\n -- Aggregates all products in the order \n -- and all products returned later.\n SUM(qty * unit_price) AS order_value_after_returns,\n SUM(qty) AS order_qty_after_returns,\n -- If negative, order has qty return(s).\n MIN(qty) order_min_qty\n FROM \n `ltv_ecommerce.10_orders`\n GROUP BY\n customer_id,\n order_id)\n GROUP BY\n customer_id,\n order_day)\nWHERE\n -- [Optional] Remove dates with outliers per a customer.\n (stdv_value < MAX_STDV_MONETARY\n OR stdv_value IS NULL) AND\n (stdv_qty < MAX_STDV_QTY\n OR stdv_qty IS NULL);\n\n\nSELECT * FROM `ltv_ecommerce.20_aggred` LIMIT 5;\n')
File "/opt/conda/lib/python3.7/site-packages/IPython/core/interactiveshell.py", line 2419, in run_cell_magic
result = fn(*args, **kwargs)
File "/opt/conda/lib/python3.7/site-packages/google/cloud/bigquery/magics/magics.py", line 521, in _cell_magic
raise rebranded_error from exc
File "<string>", line unknown
SyntaxError: --params is not a correctly formatted JSON string or a JSON serializable dictionary
Hi, I've tried quite a few permutations of google-cloud-bigquery, google-cloud-bigquery-storage, pyrarrow, google-cloud-core, and google-api-core without any success in making queries to bigquery. I've gotten grpc errors, then client_credential missing attribute errors, and so on. is there a requirements.tx showing what versions of packages were used to run the sample? the particular sample is: bqml_ga4_gaming_propensity_to_churn.ipynb
thanks,
jim
The public firebase-public-project.analytics_153293282.events_20181003 dataset is no longer available in the BigQuery
In the notebook, I saw "In our analysis, we used user_pseudo_id as the user identifier. However, ideally, your app should send back the user_id from your app to Google Analytics." I have a few questions:
Is user_pseudo_id automatically enabled?
If I am using Google Analytics data only (no other 1st party data) for model building and audience activation is via Google Ads, DV360, or SA360 (no CRM), user_id is not necessary, am I assuming correctly?
A declarative, efficient, and flexible JavaScript library for building user interfaces.
๐ Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. ๐๐๐
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google โค๏ธ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.