![]() Then we replace the utm_campaign= string with an empty string for the final result: SELECT dt, regexp_replace(substring(url from '(?!&)utm_campaign=*(?=&)'), To first extract utm_campaign, we use the query: SELECT dt, substring(url from '(?!&)utm_campaign=*(?=&)') Our regular expression is going to take the form: (?!&)utm_campaign=*(?=&) ![]() We can do this in one step, but for illustration purposes, we'll do it in two steps. We are going to make use the substring and regexp_replace functions to first extract a substring that matches the regular expression for campaign, source and medium and then replace the matched text to get what we want. Suppose we have a table named pageviews with the following schema: dtĪnd we want a result set that looks like this: dt You can use a handy tool like the Google Campaign URL Builder to build URLs like these. The parameters are attached as query parameters after the "?" and have standard definitions like source, medium, campaign, etc. A tracked URL will look like something like this: These are small bits of information attached to every URL to track campaign and channel effectiveness amongst others. If you've ever worked with marketers, you would have come across UTM parameters. On free_email_domains.domain = split_part(emails.email, Pattern Matching Google Analytics UTM parameters If the data for these services is in a table named "free_email_domains", a simple join with this table will classify an email as a business user or a consumer. We found a github project that has an actively maintained list of such services. Which gives us: next need a database of services that hand out free and disposable email addresses. If your database table named "emails" contains email addresses like these: the split_part function will split the email address to its respective parts: select email, We'll make an assumption that the character splits the email address to a name and domain name. Pattern Matching Business and Consumer Email AddressesĪ common analysis task is to count the number of emails in your database that are of commercial value, i.e., not using disposable email addresses or free providers like gmail or hotmail. We'll consider two examples that use regular expressions to pattern match.ġ. Finding Patterns & Matching Substrings using Regular ExpressionsĪlthough there are multiple ways to do pattern matching in SQL, we will look at the most powerful and flexible of them all – regular expressions.įortunately, PostgreSQL and Redshift support POSIX regular expressions out of the box.Using AWS Athena to understand your AWS billsĬanada Province & Census Division Shapefiles Modeling: Denormalized Dimension Tables with Materialized Views for Business Users Gap analysis to find missing values in a sequenceĮstimating Demand Curves and Profit-Maximizing Pricing Querying JSON (JSONB) data types in PostgreSQL Using SQL to analyze Bitcoin, Ethereum & Cryptocurrency Performance ![]() Multichannel Marketing Attribution ModelingĪnalyzing Net Promoter Score (NPS) surveys in SQL to improve customer satisfaction & loyalty SQL's NULL values: comparing, sorting, converting and joining with real values ![]() SQL Server: Date truncation for custom time periods like year, quarter, month, etc.įilling Missing Data & Plugging Gaps by Generating a Continuous Seriesįinding Patterns & Matching Substrings using Regular ExpressionsĬoncatenating Rows of String Values for Aggregation Redshift: Generate a sequential range of numbers for time series analysis MySQL: Generate a sequential range of numbers for time series analysis Understanding how Joins work – examples with Javascript implementation First steps with Silota dashboarding and chartingĬalculating Exponential Moving Average with Recursive CTEsĬalculating Difference from Beginning RowĬreating Pareto Charts to visualize the 80/20 principleĬalculating Summaries with Histogram Frequency DistributionsĬalculating Relationships with Correlation MatricesĪnalyzing Recency, Frequency and Monetary value to index your best customersĪnalyze Mailchimp Data by Segmenting and Lead scoring your email listĬalculating Top N items and Aggregating (sum) the remainder into "All other"Ĭalculating Linear Regression Coefficientsįorecasting in presence of Seasonal effects using the Ratio to Moving Average method
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |